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

Итоговый код
Таблица comment_stats содержит всю статистику по всем сайтам:
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)
);


Единый запрос первоначального сбора или обновления статистики:
INSERT INTO comment_stats
SELECT
DATE_TRUNC('day', t.created) as day,
:siteId as site_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.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 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);


Перед обновлением необходимо запустить:
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. Кстати, для стартапов у них есть скидка, можно попросить её в письме.

JavaScript код интеграции
new Highcharts.Chart({
    chart: {
        type: 'line',

        //контейнер <div id="chart"></div>
        renderTo: 'chart'
    },
    title: {
        text: '',
        style: {
            //title не нужен
            display: 'none' 
        }
    },
    xAxis: {
        //переменная days это массив полученный с сервера (таблица comment_stats)
        //примерно в таком виде ['2015-05-03', '2015-05-04', ... '2015-06-03']
        categories: days
    },
    yAxis: {
        min:0,
        title: {
            //название Y ординаты
            text: MESSAGES.comments
        }
    },
    legend: {
        layout: 'vertical',
        align: 'right',
        verticalAlign: 'middle',
        borderWidth: 0
    },

    //series массив содержащий данные статистики разных параметров:
    //все комментарии, одобренные, лайки, комментарии от ВК, анонимные и т.д.
    series: series

    //убрать копирайт
    credits: {
        enabled: false
    }
});

//Заполнение series
//Params  - все параметры
var Params = [
    {
        name: 'total',
        index: 3,
        color: '#999'
    }, {
        name: 'approved',
        index: 4,
        color: '#9edd69'
    }, {
        name: 'pending',
        index: 5,
        color: '#ffbb3d'
    }, {
        name: 'spam',
        index: 6,
        color: '#ff95af'
    }, {
        name: 'deleted',
        index: 7,
        color: '#666'
    }, {
        name: 'up',
        index: 8,
        color: '#239600'
    }, {
        name: 'down',
        index: 9,
        color: '#ff2f2f'
    }, {
        name: 'vk',
        index: 10,
        color: '#6383a8'
    }, {
        name: 'ok',
        index: 11,
        color: '#eb722e'
    }, {
        name: 'fb',
        index: 13,
        color: '#4e69a2'
    }, {
        name: 'tw',
        index: 14,
        color: '#55acee'
    },
    ...
    {
        name: 'sso',
        index: 22,
        color: '#17c200'
    }, {
        name: 'anonym',
        index: 23,
        color: '#c6cde0'
    }
];

for (var p in Params) {
    //stats объект содержащий статистику разных параметров полученную с сервера
    //например: stats.total, stats.spam, stats.vk, stats.anony и т.д.
    var param = Params[p], stat = stats[param.name];
    if (stat) {
        series.push({
            nick: param.name,
            name: MESSAGES[param.name],  //название графика
            data: stat,
            color: param.color
        });
    }
}


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

Cackle система комментариев аналитика

Cackle система комментариев аналитика

Cackle система комментариев аналитика

Cackle система комментариев аналитика

Cackle система комментариев аналитика

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

Комментарии (19)


  1. samodum
    03.06.2015 11:42
    +1

    >"… мы используем HighCharts. Это платная библиотека построения графиков..."

    Хм. Не знал, что она платная. Я пользуюсь бесплатно


    1. e-commerce Автор
      03.06.2015 11:46
      +1

      Спасибо за уточнение.
      Добавили «для коммерческих проектов».


  1. amarao
    03.06.2015 14:53
    -4

    Я постоянно забываю, чем вы занимаетесь. Услуги клининга и сопровождения общественных туалетов? Хотя «каккл.ми» выглядит крайне подозрительно…


    1. javist
      03.06.2015 14:59
      +1

      Чтоже вы постоянно всякую ерунду несете, причем почти в каждом нашем посте =)
      Видимо это болезнь…


      1. amarao
        03.06.2015 15:39
        +1

        Видимо, это неблагозвучность названия, которое очень режет взгляд. И да, можно приводить миллион аргументов, объясняющих, почему «какл» — это не дефекация, а что-то большое, возвышенное и требующее всеобщего внимания, но из языка слово не выбросишь…


        1. fenixlz
          04.06.2015 07:58
          -1

          Так они вроде и не отрицают откуда пошло название.


  1. mvs
    03.06.2015 18:15
    +1

    Буду минусовать каждый ваш пост, пока не смените название на более благозвучное для русскоговорящих людей.
    Пока вы «Какл», никакой пиар на Хабре не поможет вашей ЦА.


    1. javist
      03.06.2015 23:27

      Что такое ЦА? =)


      1. tsmar
        03.06.2015 23:34
        -3

        цыпленок абака, а для тех кто не в курсе — целевая аудитория, учите матчасть


  1. tsmar
    03.06.2015 23:32

    «около 100 000 в сутки» * 365 дней в году = 36 500 000, а вы говорите про 30 000 000 с 2011 года, если честно, то это вообще не «в условиях столь сильных нагрузок», ну правда


    1. javist
      03.06.2015 23:47
      +1

      Вы думаете, что в 2011 у нас зарегистрировалось сразу 35 000 сайтов и все они стали публиковать 100 000 в сутки? :))

      По поводу высокой нагрузки, чусло комментариев тут не при чём, поэтому мы и упоминали свой предыдущий пост.

      «Я эту книгу не читал, но знаю, что она плохая!» ;)


      1. tsmar
        03.06.2015 23:59
        -2

        Я «эту» книгу не читал, я читаю эту, и её данные не сходятся.
        Расскажите, а в чем конкретно заключается сложность в хранении 100к комментов в сутки или создать табличку для аналитики это сейчас now how?


      1. tsmar
        04.06.2015 00:07
        -3

        Прочитал предыдущий пост, много думал, круто.


  1. ZOXEXIVO
    04.06.2015 10:24
    +2

    Всегда интересно читать ваши посты


  1. mureevms
    04.06.2015 12:52
    +3

    Фактически, Cackle является самой адекватной системой комментариев, что бы там не говорили про его название. Выбрал себе для блога из десятка других. Только в последних версиях не работает синхронизация комментариев с вордпрессом. Интересно, у меня локальная проблема или у всех так.


    1. javist
      04.06.2015 16:02
      -1

      Спасибо за комментарий.
      А вы обновили плагин до последней версии 4.09 wordpress.org/plugins/cackle?


      1. mureevms
        04.06.2015 16:23
        -1

        Да, последняя версия. Запустил синхронизацию еще раз и снова новых комментов в WP нет.


        1. javist
          04.06.2015 17:52
          -1

          Напишите пожалуйста в support@cackle.me.


  1. stalkerg
    05.06.2015 12:09
    +1

    Согласен, Cackle самые адекватные. Именно по этому в своё время продавил их использования на actualidad.rt.com :)
    Теперь работаю в Postgres Pro и рад, что Cackle так же использует Postgres. ^_^