Архитектура базы данных для миллионов новых строк в день

Мне нужно внедрить специально разработанную службу веб-аналитики для большого количества веб-сайтов. Ключевыми объектами здесь являются:

Сайт
для посетителей

Каждый уникальный посетитель будет иметь одну строку в базе данных с информацией, такой как целевая страница, время суток, ОС, браузер, реферер, IP и т.д.

Мне нужно будет делать агрегированные запросы в этой базе данных, такие как "COUNT" всех посетителей, у которых есть ОС Windows, и вышла из Bing.com "

У меня есть сотни сайтов для отслеживания, а количество посетителей для этих веб-сайтов варьируется от нескольких сотен в день до нескольких миллионов в день. В целом, я ожидаю, что эта база данных вырастет примерно на миллион строк в день.

Мои вопросы:

1) Является ли MySQL хорошей базой данных для этой цели?

2) Что может быть хорошей архитектурой? Я думаю о создании новой таблицы для каждого веб-сайта. Или, возможно, начните с одной таблицы, а затем создайте новую таблицу (ежедневно), если количество строк в существующей таблице превышает 1 миллион (это мое предположение правильно). Мое единственное беспокойство заключается в том, что, если таблица становится слишком большой, SQL-запросы могут значительно замедляться. Итак, каково максимальное количество строк, которые я должен хранить в таблице? Более того, существует ли ограничение на количество таблиц, которые может обрабатывать MySQL.

3) Целесообразно ли выполнять агрегированные запросы по миллионам строк? Я готов подождать пару секунд, чтобы получить результаты для таких запросов. Является ли это хорошей практикой или существует какой-либо другой способ выполнения агрегированных запросов?

В двух словах, Я пытаюсь создать крупномасштабный тип хранилища данных, который будет писать тяжелый. Если вы знаете о каких-либо опубликованных тематических исследованиях или отчетах, это будет здорово!

Ответ 1

Если вы говорите о больших объемах данных, посмотрите Разделение MySQL. Для этих таблиц раздел по данным/времени, безусловно, будет способствовать повышению производительности. Там есть достойная статья о разделении здесь.

Посмотрите на создание двух отдельных баз данных: один для всех необработанных данных для записей с минимальной индексацией; второй - для отчетности с использованием агрегированных значений; либо пакетный процесс для обновления базы данных отчетов из базы данных необработанных данных, либо используйте репликацию, чтобы сделать это для вас.

ИЗМЕНИТЬ

Если вы хотите быть очень умным в своих отчетах об агрегации, создайте набор таблиц агрегации ( "сегодня", "неделя до настоящего времени", "месяц до даты", "по годам" ). Совокупность от необработанных данных до "сегодня" либо ежедневно, либо в режиме реального времени; совокупность от "по дням" до "недели до настоящего времени" на ночной основе; от "недели до настоящего времени" до "месяца к настоящему времени" на еженедельной основе и т.д. При выполнении запросов присоедините (UNION) соответствующие таблицы для интересующих диапазонов дат.

РЕДАКТИРОВАТЬ № 2

Вместо одной таблицы на клиента мы работаем с одной схемой базы данных для каждого клиента. В зависимости от размера клиента мы можем иметь несколько схем в одном экземпляре базы данных или отдельный экземпляр базы данных для каждого клиента. Мы используем отдельные схемы для сбора необработанных данных и для агрегации/отчетности для каждого клиента. Мы запускаем несколько серверов баз данных, ограничивая каждый сервер единственным экземпляром базы данных. Для обеспечения устойчивости базы данных реплицируются на нескольких серверах и сбалансированы по нагрузке для повышения производительности.

Ответ 2

Некоторые предложения в агностической базе данных.

Самым простым способом является разграничение между интенсивными чтениями и интенсивными таблицами. Вероятно, это хорошая идея создать две параллельные схемы дневной/недельной схемы и схему истории. Разделение может быть выполнено соответствующим образом. Можно подумать о пакетном задании для обновления схемы истории с данными из ежедневной/недельной схемы. В схеме истории вы можете создавать отдельные таблицы данных на веб-сайт (на основе объема данных).

Если вас интересует только статистика агрегации (что может быть не). Рекомендуется иметь сводные таблицы (ежемесячные, ежедневные), в которых сводка хранится как общие посетители без очереди, повторные посетители и т.д.; и эти сводные таблицы должны быть обновлены в конце дня. Это позволяет на лету вычислять статистику без ожидания обновления базы данных истории.

Ответ 3

Вы должны обязательно рассмотреть возможность разделения данных по сайтам между базами данных или схемами - это не только упрощает резервное копирование, удаление и т.д. отдельного сайта/клиента, но также устраняет большую часть проблем, связанных с тем, что ни один клиент не может видеть какие-либо другие данные клиентов по ошибке или плохое кодирование и т.д. Это также означает, что проще сделать выбор в отношении разбиения на разделы, помимо табличного уровня таблицы данных на время или на клиенте и т.д.

Кроме того, вы сказали, что объем данных составляет 1 миллион строк в день (что не особенно тяжело и не требует огромной громовой силы для регистрации/хранения, а также для сообщения (хотя, если вы создавали 500 отчетов в полночь, вы могли бы logjam). Однако вы также сказали, что на некоторых сайтах ежедневно было 1 м посетителей, поэтому, возможно, вы слишком консервативны?

Наконец, вы не сказали, хотите ли вы, чтобы в режиме реального времени сообщалось о la chartbeat/opentracker и т.д. или циклическом обновлении, таком как google analytics, - это будет иметь большое значение для модели вашего хранилища с первого дня.

Ответ 4

Вы действительно должны проверить свой путь вперед, имитируя окружающую среду как можно ближе к живой среде, с "настоящими поддельными" данными (правильный формат и длина). Контрольные запросы и варианты табличных структур. Поскольку вы, похоже, знаете MySQL, начинайте там. Это не должно занять много времени, чтобы настроить несколько сценариев, бомбардирующих вашу базу данных запросами. Изучение результатов вашей базы данных с вашими данными поможет вам понять, где будут возникать узкие места.

Не решение, но, надеюсь, какая-то помощь на пути, удачи:)