Как хранить и быстро обрабатывать большое количество (> 10-100M) статистики рекламной системы?

Question

[zozo30]

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

• уникальный идентификатор пользователя (строка 20 символов)
  
• номер рекламного блока (что показали)
  
• номер рекламного места (где был показ)
  
• страна (откуда был посетитель)
  
• тип устройства (дестктоп/мобильник/планшен)
  
• дата/время
  
• был ли клик (этот флаг обновляется в true, если был клик на рекламе)
  

Нужна возможность получить репорты с фильтрацией и суммированием по любому из полей (ну кроме идентификатора и флагу был ли клик). В репорте нужно иметь количество показов (хитов), количество уникальных пользователей (уников) и кол-во кликов.

Сейчас кладу все это в обычную табличку (PostgreSQL) и формирую репорт SQL-запросом. Уников считаю как count(DISTINCT user_id). Все хорошо, только показов получается больше миллиона в день уже сейчас и через неделю статистика формируется уже пол минуты, т.е. оооочень медленно, а это только тестовая эксплуатация, т.е. данных нужно уместить в сотню раз больше.

Рассуждал в сторону как-то "плющить" эту статистику, но не понятно, как тогда считать уников.

Большое спасибо за помощь.

Answer 1

[D']

Однажды делал подобный проект. Вся суть сводилась к следующему:

Была основная таблица, куда кидались все логи по трафику.
Раз в 10-20мин бежал крон, и собирал из этой таблицы все необходимые репорты, и записывал их в отдельные таблички по дням\часам.
В 00 по серверу статистика морозилась, вся статистика из основной таблицы за день скидывалась в архив, и таблица обнулялась (truncate).

В итоге все работало как надо, и всегда можно было уточнить конкретные детали из архивной таблицы.

Т.е по сути имеем такие таблицы:

• statistics - Тут вся статистика за день
  
• *_daily_reports - Тут репорты (суммированные) по дням.
  

Где *_ - тип репорта.

PS
Можно конечно напрямую писать в *_daily_report, если очень нужна realtime стата, но тогда на каждый запрос будет +N обновление таблиц, что так же скажется на производительности. Решать вам.

Comments

[zozo30]

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

Answer 2

[Сергей]

Еще как вариант, хранить текущую аггрегированную статистику за 10-20 мин в памяти, по необходимости скидывая это дело в базу. Т.о. вы можете получить аггрегированные данные в реальном времени.

Основная проблема в том, что пользователи таких систем хотят получить:
1. Данные в реальном времени (с минимальной задержкой)
2. Получить аггрегированные данные в различных разрезах (заранее определенных)
3. Строить доп разрезы на основе исторических данных (приходится обрабатывать колосальные объемы за предыдущие периоды)

Comments

[zozo30]

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

[Сергей]

строить аггрегаты заранее. Почитайте про OLAP.

Answer 3

[lega]

Я делал примерно так:
Каждый час я формировал чанки с несколькими разрезами, в вашем случае можно взять например "дата время" и "номер рекламного места" (зависит от запросов), в итоге 1 "колонка" "исчезнет", вместо даты можно хранить diff от начала часа.
Чанки максимально сжимались и разливались по шардингу.
В результате хранимый объем был в 5-10 раз меньше исходного.

Формирование отчета: Сервис на питоне, из БД за период доставались чанки, распаковывались передавались в расширение на C который обсчитывал и возвращал результат.
По скорости выходило примерно 250 млн "событий" доставались и обсчитывались за 2-4 сек на одном ядре виртуалки от DO.

Каждый час (период времени) для каждого отчета формировались результаты/кешы во всех разрезах - это позволило выдавать результаты клиентам моментально. Весили они мало если сравнивать с весом исходных данных.

Т.е. каждый час формировались свежие данные, но кроме этого ещё были реал-тайм отчеты - копия входного потока уходила на этот сервер который "считал цифры" и обнулял их каждый час.