Як зробити швидкі лічильники в MySQL - XP Injection

Я час від часу писатиму цікаві технічні нотатки, чи мало розробники теж читають наш блог. 🙂 Отже, сьогодні спробуємо вирішити дуже просте завдання - організувати лічильники MySQL. Для чого це потрібно? Прикладів використання занадто багато: необхідно кожної сторінки сайту зберігати кількість звернень, під час обробки великого обсягу даних необхідно порахувати частоти встречаемости елементів, необхідно контролювати кількість запитів кожного користувача системі… Загалом, завдання часто зустрічається.

Використовуємо "ненадійні" сховища

Якщо точність результату вам не є особливо важливою і втрата частини даних не є критичною проблемою, то моя вам рекомендація зберігати дані не в БД. Варіантів організації лічильника дуже багато, якісь трохи швидші, якісь трохи повільніші. Ось лише кілька з них:

Зберігайте всі лічильники в пам'яті та періодично скидайте контрольні значення на диск в інкрементальні файли із зазначенням тимчасової мітки. Такий спосіб дозволяє втратити лише частину даних від останнього збереження.
Використовуйте Redis з можливістю ведення лічильників. Працює дуже швидко та досить надійно. Але якщо у вас ще не використовується Redis, то додавати його тільки для лічильників досить спірно. І друга проблема полягає в транзакційності - лічильник потрібно оновлювати при успішному проходженні транзакції (в ідеалі в рамках транзакції), інакше іноді при падіннях або зупинках системи лічильник може розсинхронізуватися з реаліями.
Використовуйте ZooKeeper із його розподіленими лічильниками. Він пропонує функціональності більше, ніж вам потрібно, але в деяких випадках працює дуже навіть непогано. Знову ж таки, вводити ZooKeeper тільки для лічильників я не рекомендую. І при великому навантаженні він можестати вузьким місцем, тому що працює в один потік.

У всіх цих варіантах досягти ідеальної точності та уникнути потенційної втрати даних або подвійного підрахунку не вдасться. Але для багатьох завдань цього вистачає із головою.

Тупе рішення в лоб

Вам все ж потрібні точні лічильники, прив'язані до бізнес-транзакції і ви вирішили продати їх на рівні БД. Від цієї точки до кінця статті всі приклади будуть наводитися дляMySQL. Рішення напрошується, тому що БД у вас уже є і чому б не використовувати її і для цієї мети. Першим рішенням, яке спадає на думку, буде створення окремої таблички:

Ну і щоб оновити лічильник, потрібно викликати банальний код:

Це рішення має кілька “сюрпризів”. Перший дуже простий і зрозумілий кожному, хто знає як працюють бази даних. У разі оновлення лічильника з кількох потоків усі вони будуть заблоковані та виконаються в порядку черги. Тому продуктивність буде не зовсім радісною. Але є ще один цікавий “сюрприз” – наявність deadlock-ів у банальному коді. Вони можуть виявлятися відразу в кількох випадках:

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

Загалом варіантрішення так собі і однозначно підійде лише за слабкого навантаження.

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

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

Деякий час все навіть працюватиме досить швидко, але згодом продуктивність потихеньку деградуватиме, а загальна кількість “сміттєвих” історичних даних весь час зростатиме. І ви починаєте думати далі…

Підраховуємо проміжні підсумки

Щоб позбутися "сміттєвих" історичних даних існує безліч однотипних простих технік. Потрібно час від часу агрегувати дані та видаляти чи “віртуально видаляти” старі. Давайте розглянемо трохи детальніше. Ви запускаєте job в окремому потоці або на рівні БД або на рівні вашої програми. Цей job біжить по таблиці лічильників, підраховує суму кожного ресурсу і замість набору записів залишає рівно одну зі значенням суми. Як це реалізувати технічно:

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

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

І тут вам на думку спадає спогад, що видаляти дані з таблиці взагалі не дуже кошерна операція і її краще уникати. Як же бути?

Чистимо за собою швидко

Щоб уникнути видалення даних, потрібно трохи напружитися і згадати операціюTRUNCATE TABLE. Вона очищує дані дуже швидко. Але не все так просто доведеться трохи змінити алгоритм оновлення значень лічильників.

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

Запит є атомарним і змінює місцями таблиці resource_counter і resource_counter_shadow. Виходить, що із таблицею resource_counter_shadow ніхто не працює і можна швидко зробити агрегацію даних у ній. Отримані результати можна додати до таблиці resource_counter запитом:

Працювати такий запит буде досить швидко, тому що таблиця resource_counter_shadow невелика і контролюється інтервалом агрегації. Можна також використовувати необов'язкову таблицю resource_counter_total, яка заведена для оптимізації (щоб уникнути переливання даних з однієї таблички до іншої, якщо вони не змінюються). Зробити це можна таким запитом:

Є ще більш симпатична версія цього ж запиту:

Ну і звичайно, після використання таблиці resource_counter_shadow вона очищається. За винятком необов'язкової таблиці, цей підхід потребує мінімальної кількості блокувань, але підвищує кількість “переливань даних”.

Застосовуємо мульти-лічильник

У якийсь момент часу ви замислитеся, а чи не надто все стало складно і звернетеся до початкової проблеми. Вона полягала в тому, що запис таблиці лічильника блокується при оновленні. Але в той же час формування окремих записів для кожного оновлення призводить до зростання їх кількості та необхідності чистити дані. Тоді треба використати золоту середину. Для цього до таблиці лічильника додається нова колонка counter_index, яка включається до складу PK:

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

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

Працює швидко як на оновлення, так і на отримання значення. Балансувати можна кількістю лічильників однією ресурс.

Як бачите, рішень такого простенького завдання досить багато, якщо знати предметну область та ретельно тестувати ваші рішення. Сподіваюся, це заощадить комусь час. 🙂

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