На любой рекламной бирже есть система ведения и отображения статистики, без неё никак. В нашем случае система работает на MySQL и логирует каждый показ или клик баннера (действие), а затем агрегирует действия по группам раз в минуту. Выглядит это примерно так. Само действие (упрощённо, полей намного больше):
datepartner_idsub_idcountry_idsite_idformat_idimpressionsclicks01012014140000bill0ussite1.com320x2001001012014140002joegooglerusite2.com468x6001
Действия кладутся как есть, один за другим в промежуточную ненормализованную таблицу типа MEMORY. Затем, по крону, раз в минуту, они группируются по разным критериям и кидаются в соответствующие таблицы:
по дате:
datepartner_idsub_idimpressionsclicks01012014140000billtest430150
по стране:
datepartner_idsub_idcountry_idimpressionsclicks01012014140000billtestus520123
по сайту:
datepartner_idsub_idsite_idimpressionsclicks01012014140000billtestsite1.com230153
по формату баннера и так далее. Всего около 15 группировок. На каждую группировку соответственно идёт отдельная таблица. В итоге, чтобы выбрать статистику по датам, мы делаем:
1.
SELECT * FROM stats_bydate WHERE user_id='bill' AND date>=... and date<=...
для статистики по странам:
1.
SELECT * FROM stats_bycountry WHERE user_id='bill' AND date>=... and date<=...
И так далее. Всё довольно очевидно, просто и надёнжо. Индексы покрывают все возможные комбинации запросов и всё работает вполне шустро. Правда, таблицы со временем получаются очень жирными (ну представьте, 1к партнёров, у каждого по 1к сабаккаунтов, в итоге добавляется по 1млн новых записей в сутки в каждую из 15и таблиц).
Теперь о том, что реально не устраивает. Для начала, не устраивает промежуточная таблица, из которой данные батчем раскидываются по крону. Хотелось бы статистики в реальном времени. Но для этого нужно на каждое действие делать по 15 запросов INSERT ... ON DUPLICATE ENTRY UPDATE — на каждую таблицу группировки по запросу. Может кто знает, возможно ли это в теории при таких требованиях: 50-100млн действий в сутки, максимальное время обработки действия не более 15-30мс ? То это получается 50-100млн * 15 запросов.
Что ещё не устраивает — это размеры таблиц, которые постоянно растут. И выходит так, что за счёт большого количества группировок, общих данных в таблице не намного меньше общего количества совершенных действий. Тогда уж проще хранить все действия как есть, в сыром виде. И делать интересные выборки. Типа таких:
1.
SELECT * FROM events WHERE user_id='bill' AND sub_id='test' AND date>=... AND date<=... AND country_id=(ru,us,it) AND format_id=(320x200,468x60) ORDER BY country_id
джойны я писать не стал, итак понятно, что я имею ввиду. Разумеется, тут необходим кластер для map-reduce. Насколько я знаю, подобные вещи принято делать на noSQL базах данных.
Ну а в итоге мне хочется Business Intelligence с красивыми графиками и всевозможными выборками (не многомерные кубы, разумеется, но комбинации различных характеристик действия) на большом количестве данных и чтобы они отрабатывали быстро (менее секунды) для, к примеру, 50-100 выборок в секунду. Возможно с какими-то упрощениями (делаем партиции по дням, но лучше по неделям, и с ними работаем, как с горячими данными)
Можно ли как-то прикинуть необходимое под такую задачу количество нод? Может есть калькулятор? И кто какой стек заюзал бы под такую систему аналитики? Я имею ввиду БД, агрегатор. Или идея утопична настолько, что я несу полнейший бред, раз даже у Google Analytics обновление статистики делается минимум раз в час? Признатья, и сам я не видел таких проектов — либо всё сводилось к банальной статистике "по дням", "по сайтам" и т.п., либо объёмы данных небольшие.
У меня пока крутятся идеи насчёт Cassandra или Elasticsearch или Amazon Redshift, но всё это нужно тестировать, а это сразу большие инвестиции. Информации в интернете просто море, в котором можно утонуть.
