Система комментариев Cackle: как мы делали аналитику

    Всем привет! В нашем предыдущем посте про облачный сервис 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.
    Спасибо за внимание!
    Cackle
    Компания

    Похожие публикации

    AdBlock похитил этот баннер, но баннеры не зубы — отрастут

    Подробнее
    Реклама

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

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

      Хм. Не знал, что она платная. Я пользуюсь бесплатно
        +1
        Спасибо за уточнение.
        Добавили «для коммерческих проектов».
        –4
        Я постоянно забываю, чем вы занимаетесь. Услуги клининга и сопровождения общественных туалетов? Хотя «каккл.ми» выглядит крайне подозрительно…
          +1
          Чтоже вы постоянно всякую ерунду несете, причем почти в каждом нашем посте =)
          Видимо это болезнь…
            +1
            Видимо, это неблагозвучность названия, которое очень режет взгляд. И да, можно приводить миллион аргументов, объясняющих, почему «какл» — это не дефекация, а что-то большое, возвышенное и требующее всеобщего внимания, но из языка слово не выбросишь…
              –1
              Так они вроде и не отрицают откуда пошло название.
          +1
          Буду минусовать каждый ваш пост, пока не смените название на более благозвучное для русскоговорящих людей.
          Пока вы «Какл», никакой пиар на Хабре не поможет вашей ЦА.
            0
            Что такое ЦА? =)
              –3
              цыпленок абака, а для тех кто не в курсе — целевая аудитория, учите матчасть
            0
            «около 100 000 в сутки» * 365 дней в году = 36 500 000, а вы говорите про 30 000 000 с 2011 года, если честно, то это вообще не «в условиях столь сильных нагрузок», ну правда
              +1
              Вы думаете, что в 2011 у нас зарегистрировалось сразу 35 000 сайтов и все они стали публиковать 100 000 в сутки? :))

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

              «Я эту книгу не читал, но знаю, что она плохая!» ;)
                –2
                Я «эту» книгу не читал, я читаю эту, и её данные не сходятся.
                Расскажите, а в чем конкретно заключается сложность в хранении 100к комментов в сутки или создать табличку для аналитики это сейчас now how?
                  –3
                  Прочитал предыдущий пост, много думал, круто.
                +2
                Всегда интересно читать ваши посты
                  +3
                  Фактически, Cackle является самой адекватной системой комментариев, что бы там не говорили про его название. Выбрал себе для блога из десятка других. Только в последних версиях не работает синхронизация комментариев с вордпрессом. Интересно, у меня локальная проблема или у всех так.
                    –1
                    Спасибо за комментарий.
                    А вы обновили плагин до последней версии 4.09 wordpress.org/plugins/cackle?
                      –1
                      Да, последняя версия. Запустил синхронизацию еще раз и снова новых комментов в WP нет.
                        –1
                        Напишите пожалуйста в support@cackle.me.
                    +1
                    Согласен, Cackle самые адекватные. Именно по этому в своё время продавил их использования на actualidad.rt.com :)
                    Теперь работаю в Postgres Pro и рад, что Cackle так же использует Postgres. ^_^

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

                    Самое читаемое