Я буду время от времени писать интересные технические заметки, мало ли разработчики тоже читают наш блог. 🙂 Итак, сегодня попытаемся решить очень простую задачку – организовать счетчики в MySQL. Для чего это нужно? Примеров использования очень много: нужно для каждой страницы сайта хранить количество обращений, при обработке большого объема данных нужно посчитать частоты встречаемости элементов, нужно контролировать количество запросов для каждого пользователя в системе… В общем, задача частенько встречается.

Используем “ненадежные” хранилища

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

• Храните все счетчики в памяти и периодически сбрасывайте контрольные значения на диск в инкрементальные файлы с указанием временной метки. Такой способ позволяет потерять только часть данных от последнего сохранения.
• Используйте Redis с его возможностью ведения счетчиков. Работает очень быстро и достаточно надежно. Но, если у вас еще не используется Redis, то добавлять его только для счетчиков достаточно спорно. И вторая проблема заключается в транзакционности – счетчик нужно обновлять при успешном прохождении транзакции (в идеале в рамках транзакции), а иначе иногда при падениях или остановках системы счетчик может рассинхронизироваться с реалиями.
• Используйте ZooKeeper с его распределенными счетчиками. Он предлагает функциональности больше чем вам нужно, но в некоторых случаях работает очень даже неплохо. Снова таки, вводить ZooKeeper только для счетчиков я не рекомендую. И при большой нагрузке он может стать узким местом, так как работает в один поток.

Во всех этих вариантах добиться идеальной точности и избежать потенциальной потери данных или двойного подсчета не удастся. Но для многих задач этого хватает с головой.

Тупое решение в лоб

Вам все таки нужны точные счетчики, привязанные к бизнес-транзакции и вы решили реализовать их на уровне БД. От этой точки и до конца статьи все примеры будут приводиться для MySQL. Решение напрашивается, потому что БД у вас уже есть и почему бы не использовать ее и для этой цели. Первым решением, которое приходит в голову, будет создание отдельной таблички:

CREATE TABLE resource_counter (
   resource_id BIGINT NOT NULL,
   count_of INT UNSIGNED NOT NULL,
   PRIMARY KEY (resource_id)
) ENGINE=InnoDB;

Ну и чтобы обновить счетчик, нужно вызвать банальный код:

UPDATE resource_counter SET count_of = count_of + :delta WHERE resource_id = :resourceId;

У этого решения есть несколько “сюрпризов”. Первый из них очень простой и понятен каждому, кто знает как работают базы данных. В случае обновления счетчика из нескольких потоков все они будут заблокированы и выполнятся в порядке очереди. Поэтому производительность будет не совсем радостной. Но есть и еще один интересный “сюрприз” – наличие deadlock-ов в банальном коде. Они могут проявляться сразу в нескольких случаях:

• Вы в одной транзакции обновляете сразу несколько счетчиков. Параллельно подобных транзакций может выполняться несколько. Если вы не контролируете порядок resource_id, то в силу специфики захвата lock-ов по индексу (в примере он PK, но в любом случае он понятное дело у вас должен быть, чтобы поиск счетчика по resource_id работал быстро) вы получите deadlock.
• Еще один сценарий deadlock-а появится, если вы будете производить действия с самой таблицей ресурса в той же транзакции для другой записи (например, дочерней или родительской) а вместо простого индекса будете использовать FK на таблицу ресурса.

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

Только вставки, ничего кроме вставок

И тут вам приходит в голову оптимизация. Вы вспоминаете, что вставки практически не блокируют друг друга, в отличие от обновлений данных. Поэтому вы решаете убрать PK с resource_id и вместо обновлений счетчика добавлять новые данные. Для подсчета же общего значения счетчика вы будете просто использовать следующий запрос:

SELECT sum(count_of) FROM resource_counter WHERE resource_id = :resourceId;

Некоторое время все даже будет работать достаточно быстро, но со временем производительность будет потихоньку деградировать, а общее количество “мусорных” исторических данных будет все время расти. И вы начинаете думать дальше…

Подсчитываем промежуточные итоги

Чтобы избавиться от “мусорных” исторических данных существует множество однотипных простых техник. Нужно время от времени агрегировать данные и удалять или “виртуально удалять” старые. Давайте рассмотрим немного детальнее. Вы запускаете job в отдельном потоке либо на уровне БД либо на уровне вашего приложения. Этот job бежит по таблице счетчиков, подсчитывает сумму по каждому ресурсу и вместо набора записей оставляет ровно одну со значением суммы. Как это реализовать технически:

• Блокировать доступ к определенному ресурсу на время проведения операции. Проблема заключается в том, что другие потоки продолжают вставлять значения. А это значит, что без блокировки вы удалите старые записи, среди которых могут быть и новые. Тем самым рискуете поломать счетчик. Блокировку можно делать как на уровне БД так и в коде. Способ не очень хороший, потому что блокировки всегда замедляют работу.
• Второй способ заключается в добавлении новой колонки с временем добавления записи (тип TIMESTAMP). Теперь вы можете безопасно посчитать сумму за 5 минут в прошлое и удалить записи, которые уже не нужны в той же транзакции. Сумма добавляется как новая запись за текущее время. На самом деле такой способ тоже чреват дополнительными блокировками при удалении и deadlock-ами при параллельной вставке и удалении по индексу, который у вас есть на resource_id.
• Третий способ заключается в том, чтобы использовать отдельный поток для чистки, а агрегированные записи помечать специальным маркером. В этом случае правильное значение счетчика будет равно сумме всех записей, начиная с последнего агрегированного значения. Исторические данных могут удаляться по одной или блоками в любое время безо всякого риска.

Третий способ, пожалуй, является самым быстрым и безопасным из перечисленных в плане блокировок. Подобное решение является “виртуальным удалением” и часто используется для подсчета баланса за определенные периоды. Агрегированные записи могут как удаляться так и оставаться в системе для построения временных графиков. Подсчет значения счетчика более-менее фиксирован по времени и зависит от периода агрегации.

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

Чистим за собой быстро

Чтобы избежать удаления данных, нужно немного напрячься и вспомнить об операции TRUNCATE TABLE. Она очищает данные очень быстро. Но не все так просто, придется немного изменить алгоритм обновления значений счетчиков.

Для этого нам понадобятся вместо одной сразу 2 или 3 таблички одинаковой структуры: resource_counter, resource_counter_shadow, resource_counter_total (эта табличка является необязательной). Каждая из них будет поддерживать только вставки. Ваше приложение пишет все изменения значения счетчика в виде дополнительных записей в таблицу resource_counter и только в нее. Параллельно работает отдельный поток, который раз в определенное время производит замену таблиц:

RENAME TABLE resource_counter TO resource_counter_tmp, 
           resource_counter_shadow TO resource_counter,
                resource_counter_tmp TO resource_counter_shadow;

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

INSERT INTO resource_counter (resource_id, count_of) 
SELECT resource_id, sum(count_of) FROM resource_counter_shadow 
GROUP BY resource_id ORDER BY NULL;

Работать подобный запрос будет достаточно быстро, потому что таблица resource_counter_shadow маленькая и контролируется интервалом агрегации. Можно также использовать необязательную таблицу resource_counter_total, которая заведена для оптимизации (чтобы избежать переливания данных из одной таблички в другую, если они не меняются). Сделать это можно следующим запросом:

UPDATE TABLE resource_counter_total rct INNER JOIN 
(SELECT resource_id, sum(count_of) AS delta FROM resource_counter_shadow 
GROUP BY resource_id ORDER BY NULL) deltas ON rct.resource_id = deltas.resource_id
SET rct.count_of = rct.count_of + deltas.delta;

Есть еще более симпатичная версия этого же запроса:

INSERT INTO resource_counter_total (resource_id, count_of) 
SELECT resource_id, sum(count_of) FROM resource_counter_shadow rcs 
GROUP BY resource_id ORDER BY NULL
ON DUPLICATE KEY UPDATE resource_counter_total.count_of = resource_counter_total.count_of + rcs.count_of;

Ну и конечно же, после использования таблицы resource_counter_shadow она очищается. За исключением необязательной таблицы, данный подход требует минимальное количество блокировок, но повышает количество “переливаний данных”.

Применяем мульти-счетчик

В какой-то момент времени вы задумаетесь, а не слишком ли все стало сложно и обратитесь к изначальной проблеме. Она заключалась в том, что запись в таблице счетчика блокируется при обновлении. Но в то же время, формирование отдельных записей для каждого обновления приводит к росту их количества и необходимости чистить данные. Тогда надо использовать золотую середину. Для этого в таблицу счетчика добавляется новая колонка counter_index, которая включается в состав PK:

CREATE TABLE resource_counter (
   resource_id BIGINT NOT NULL,
   counter_index INT UNSIGNED NOT NULL,
   count_of INT UNSIGNED NOT NULL,
   PRIMARY KEY (resource_id, counter_index)
) ENGINE=InnoDB;

Теперь наша цель распределить обновление по нескольким записям таблицы, сделав возможность блокировки менее вероятной. От качества распределения будет зависеть количество блокировок по одному счетчику. Для простенькой версии можно использовать следующий запрос:

INSERT INTO resource_counter (resource_id, counter_index, count_of) 
VALUES (resource_id, rand() * 10, :countOf) 
WHERE resource_id = :resourceId
ON DUPLICATE KEY UPDATE count_of = count_of + :countOf;

Для получения значения счетчика по определенному ресурсу по-прежнему нужно будет использовать сумму, но по фиксированному количеству строк (максимум 10 в нашем примере):

SELECT sum(count_of) FROM resource_counter WHERE resource_id = :resourceId;

Работает быстро как на обновление так и на получение значения. Балансировать можно количеством счетчиков на один ресурс.

Как видите, решений такой простенькой задачи достаточно много, если знать предметную область и тщательно тестировать ваши решения. Надеюсь, это сэкономит кому-то время. 🙂

P.S. Все запросы писались напрямую в текстовом редакторе, поэтому мелкие ошибки и опечатки просьба указывать в комментариях, но в нежной форме. 😉

Не хочешь пропускать ничего интересного? Подпишись на ленту RSS или следи за нами в Twitter!