Как мы ClickHouse для рассылок внедряли Рассолов Дмитрий - презентация

1 Как мы ClickHouse для рассылок внедряли Рассолов Дмитрий

2 Site & Mailing новая ставка рекомендации... $message = new AttackNotificationMail; $message->setBodyByUserId((int)$this->user_id); $status = (new Messenger()) ->send Message($message);...

3

4

5 Зачем информация о каждом сообщении новая ставка рекомендации аналитика конверсии и оттока законодательство

6

7 [mail.log] af bf

8

9

10 Проблемы 1,5 года, ~ 50M записей, 20 Гб выборка CTR по типам сообщений за 1 квартал ~ 1 час новые гипотезы: выборки по цепочкам, сравнение эффективности цепочек и т.д.

11 Агрегация статистики в MySQL CREATE TABLE `sum_m` AS SELECT sum(open)...; CREATE TRIGGER … +известное решение -гибкость

12 Колоночные базы -мало примеров внедрения +гибкость

13 Прототипирование Нет официальных драйверов, библиотек, но есть неофициальные (у нас smi2/phpClickHouse) GUI: был только tabix Нет UPDATE, но есть DETACH

14 Таблица событий CREATE TABLE IF NOT EXISTS `message_log` ( `event_date` DEFAULT toDate (`event_datetime`),`event_datetime` DateTime,`log_datetime` DEFAULT now (),`message_id` FixedString (23),`exim_message_id` FixedString (16) DEFAULT '',`transport` UInt8 DEFAULT 0,`type` UInt16,` ` String,`status` UInt8,`conversion_id` UInt8 ) ENGINE = MergeTree (`event_date`, (`event_date`,`log_datetime`, `event_datetime`, `message_id`,`exim_message_id`, `type`,`transport`), 8192)

15 Развертывание ZooKeeper? Кворум? Один официальный пакет под Ubuntu Бэкап пишем сами Вставка большими пачками (100k строк)

16 Сбор событий [log.csv] 5af9602aeb ,,2,0,,4,0 5af9602aeb ,,2,0,,5,6... smi2/phpClickHouse (curl)

17

18 SELECT `event_datetime`,`log_datetime`, `message_id`,`exim_message_id`,`type`,` `, `status` FROM `message_log` WHERE `event_date` BETWEEN ' ' AND ' ' AND `transport` = 2 AND `message_id` IN ( SELECT `message_id` FROM `message_log` WHERE ` ` IN AND `transport` = 2 AND `event_date` BETWEEN ' ' AND ' ' ) ORDER BY `event_datetime` DESC LIMIT 0,40 Поиск статусов сообщения

19 Аналитика эффективности SELECT type,uniq (message_id) AS sent,uniqIf (message_id, status = 4) AS opened,uniqIf (message_id, status = 5) AS clicked,uniqIf (message_id, conversion_id = 1) AS goal_1,uniqIf (message_id, conversion_id = 7) AS unsubscribed FROM `message_log` WHERE event_date BETWEEN ' ' AND ' ' AND type IN (1,22) AND transport = 2 GROUP BY type

20 Было vs стало в числах Было данных за 1,5 года, ~ 50M записей, 20 Гб храним только последнее событие выборка CTR по типам сообщений за 1 квартал ~ 1 час Стало за 3 года ~ 500M статусов, 7Гб храним все события, ~13M в месяц выборка CTR по типам сообщений за 1 год 3-5 секунд

21

22 Итоги Не все так гладко update, join, insert большими пачками ZooKeeper, официально только deb пакеты Но оно стоило того CTR за год считается ~3-5 с. прирост в 1000 раз аналитик самостоятельно работает в Grafana

23 Спасибо! Рассолов Дмитрий