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

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

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

[mail.log] af bf

<a href=... <img src=... /?message_id=5af96...

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

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

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

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

Таблица событий 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)

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

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

<a href=... <img src=... [log.csv] af

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 Поиск статусов сообщения

Аналитика эффективности 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

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

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

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