Всем привет! В нашем предыдущем посте про облачный сервис Cackle мы рассказали об архитектуре, технологиях и нагрузках в целом. Сегодня хотим поделиться о том, как в условиях столь сильных нагрузок и уже накопленной информации (30 000 000 комментариев с 2011 года) мы сделали подробную аналитику для системы комментариев Cackle. Наш метод сбора статистики универсальный и думаю будет интересен, в плане практического применения, всем тем, кто столкнулся с задачей разработки аналитики, но пока не совсем представляет с чего начать.

Не много исходных данных

За время работы системы комментариев Cackle, с 2011 года, было накоплено порядка 30 миллионов комментариев. В сутки публикуется около 100 000, в пики, в секунду, доходит до 1000 одновременных.

Все это в БД хранится в таблице comment размером 15Гб. Таблица проиндексирована по полю site_id, который является идентификатором сайта клиента. Всего зарегистрировано 35 000 сайтов. Самые крупные по числу комментариев это rusvesna.su (9 млн), svpressa.ru (800 тыс), 3dnews.ru (500 тыс), carambatv.ru (450 тыс).

Требования к аналитике — это сбор и обновление дневной статистики для каждого сайта:

• Комментариев (всего, опубликованных, в ожидании, спам, удаленных);
• Лайков и дизлайков;
• Комментариев от социальных провайдеров, анонимных и SSO (авторизованных через механизм единой авторизации).

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

Серверная часть — PostgreSQL

Как мы уже писали ранее, в качестве БД у нас PostgreSQL с репликацией в несколько ЦОДов распределенных по России и Европе. PostgreSQL отлично справляется с нагрузкой и именно он был выбран как основной компонент бизнес-логики сбора статистики комментариев.

Если вкратце, то сбор делается в два этапа.

Допустим, клиент зашел в панель администрирования, выбрал один из своих сайтов и перешел в Аналитику:
1. Если у выбранного сайта есть комментарии и нет статистики по ним, то в PostgreSQL выполняется SQL запрос первоначального сбора статистики по всем необходимым параметрам;
2. Далее этот же SQL запрос будет поставлен в скедул и выполняться каждые 15 минут для обновления данных.

1. Первоначальный сбор статистики по всем комментариям

Итак нам надо собрать дневную статистику комментариев за все время для: выбранного сайта (site_id), по всем (total), одобренным (approved), в ожидании (pending), спам (spam), удаленным (deleted) комментариям, лайкам (up), дизлайкам (down), а так же по каждому социальному провайдеру (vk, ok, fb, tw, gp, и т.д.), анонимному (anonym) или SSO (sso) юзеру из таблицы comment и mc_user (таблица юзеров). Поле comment.created — время создания комментария.

SELECT
DATE_TRUNC('day', t.created) as day,
:siteId as site_id,
COUNT(t) as total,
SUM(CASE WHEN t.status = 1 THEN 1 ELSE 0 END) as approved,
SUM(CASE WHEN t.status = 0 THEN 1 ELSE 0 END) as pending,
SUM(CASE WHEN t.status = 3 THEN 1 ELSE 0 END) as spam,
SUM(CASE WHEN t.status = 2 THEN 1 ELSE 0 END) as deleted,
SUM(CASE WHEN t.rating > 0 THEN 1 ELSE 0 END) as up,
SUM(CASE WHEN t.rating < 0 THEN 1 ELSE 0 END) as down,
SUM(CASE WHEN t.provider = 'vkontakte' THEN 1 ELSE 0 END) as vk,
SUM(CASE WHEN t.provider = 'odnoklassniki' THEN 1 ELSE 0 END) as ok,
SUM(CASE WHEN t.provider = 'facebook' THEN 1 ELSE 0 END) as fb,
SUM(CASE WHEN t.provider = 'twitter' THEN 1 ELSE 0 END) as tw,
...
SUM(CASE WHEN t.provider = 'sso' THEN 1 ELSE 0 END) as sso,
SUM(CASE WHEN t.anonym > 0 THEN 1 ELSE 0 END) as anonym
FROM (
  SELECT c.id, c.created, c.status, c.rating, c.anonym, u.provider FROM comment c
  LEFT JOIN mc_user u ON c.author = u.id
  WHERE c.site_id = :siteId
) t
GROUP BY DATE_TRUNC('day', t.created) ORDER BY DATE_TRUNC('day', t.created);

Распределение времени выполнения запроса зависит от числа комментариев сайта и выглядит примерно так:
1. Комментариев до 100 000 — время запроса до 5 сек;
2. Комментариев до 1 000 000 — время запроса до 1 мин;
3. Комментариев до 9 000 000 — время запроса до 2 мин;

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

CREATE TABLE comment_stats
(
  day date NOT NULL,
  site_id bigint NOT NULL,
  total integer,
  approved integer,
  pending integer,
  spam integer,
  deleted integer,
  up integer,
  down integer,
  vk integer,
  ok integer,
  fb integer,
  tw integer,
  ...
  sso integer,
  anonym integer,
  CONSTRAINT comment_stats_pkey PRIMARY KEY (day, site_id)
);

Далее в первый SQL запрос добавляем INSERT INTO comment_stats.

INSERT INTO comment_stats
SELECT
DATE_TRUNC('day', t.created) as day,
:siteId as site_id,
COUNT(t) as total,
SUM(CASE WHEN t.status = 1 THEN 1 ELSE 0 END) as approved,
...

Теперь данные аналитики мы будем получать прямо из таблицы comment_stats:
select * from comment_stats where site_id = :siteId.

2. Обновление данных

С обновлением данных возникла проблема. Нельзя просто взять и выполнять SQL первоначального сбора статистики постоянно, так как если аналитику подключат несколько крупных сайтов то, перформанс БД уйдет на нет.

Самое простое и эффективное решение, добавить в таблицу comment_stats новое поле comment_id, которое хранит максимальный id комментария за каждый день. При обновлении данных, сбор статистики будет начинаться именно с этого id. Учитывая все это, модифицируем первоначальный запрос:

INSERT INTO comment_stats
SELECT
DATE_TRUNC('day', t.created) as day,
:siteId as site_id,

-- берем максимальный id комментария за каждый день
MAX(t.id) as comment_id,

COUNT(t) as total,
SUM(CASE WHEN t.status = 1 THEN 1 ELSE 0 END) as approved,
...
SUM(CASE WHEN t.anonym > 0 THEN 1 ELSE 0 END) as anonym
FROM (
  SELECT c.id, c.created, c.status, c.rating, c.anonym, u.provider FROM comment c
  LEFT JOIN mc_user u ON c.author = u.id
  WHERE c.site_id = :siteId

    -- берем комментарии с id больше чем последний comment_id из таблицы comment_stats
    AND c.id > (SELECT COALESCE(MAX(comment_id), 0) FROM comment_stats WHERE site_id = :siteId)
) t
GROUP BY DATE_TRUNC('day', t.created) ORDER BY DATE_TRUNC('day', t.created);

Если кто заметил, то вот такая конструкция COALESCE(MAX(comment_id), 0) позволяет сделать один запрос как для первоначального сбора статистики, так как для обновления данных. То есть, если в comment_stats ничего нет, то возвращаем 0 и сбор идет по все таблице comment для site_id, если данные есть, то сбор идет начиная только с последнего comment_id.

Теперь все ок, за исключением того, что в таком виде запрос обновления данных работать не будет. Так как при первом же вызове для уже собранной статистики произойдет исключение вызванное попыткой вставки данных с уже существующим приватным ключом comment_stats_pkey. Другими словами, мы собрали первоначальную статистику, прошло 15 минут, запустился скедул обновления данных с условием id > последнего comment_id из comment_stats и если кто-то за это время опубликовал новые комментарии, то наш запрос попытается вставить данные с тем же day и site_id.

Есть очень простое решение (без всяких Rules on INSERT) — перед запросом обновления данных удалить последнею строчку в таблице comment_stats:

DELETE FROM comment_stats
WHERE
  site_id = :siteId
  AND
  day IN (SELECT day FROM comment_stats WHERE site_id = :siteId ORDER BY day DESC LIMIT 1)

Описанный механизм очень прост и эффективен. У него простая логика, которая подойдет для сбора практически любой статистики объектов в реляционной БД. Например у нас, используя все те же 2 SQL запроса (удаление, сбор), идет сбор статистики модераторов, публикаций (постов) и совсем скоро появится такая же аналитика для системы отзывов Cackle Reviews.

Клиентская часть — HighCharts

В браузере клиента для отображения графиков мы используем HighCharts. Это платная библиотека (для коммерческих проектов) построения графиков. Скажу сразу, что перед выбором HighCharts мы посмотрели много подобных Framework-ов и ни один не был лучше.

Из того, что понравилось больше всего: отсутствие лагов даже при 33 000 тыс. точек, мобильная адаптивность, умное сужение временных интервалов, простота интеграции, хорошее API. Кстати, для стартапов у них есть скидка, можно попросить её в письме.


Несколько скриншотов как это в конечном итоге выглядит.

Если у вас появятся дополнительные вопросы по вышеизложенной технологии или по нашей системе с радостью на них ответим hi@cackle.me.
Спасибо за внимание!