Існує два типи «проблем з продуктивністю» баз даних: ті, що можна відтворити, і ті, що зникають щойно ви відкриваєте тікет. Журнал повільних запитів перетворює другий тип на перший — бо він записує те, що дійсно сталося, а не те, у що хтось вірить.
Якщо ви можете витратити одну сфокусовану годину з slow log і вийти з прискоренням у 2×, це зазвичай не тому, що ви чаклун. Це тому, що продакшн терпеливо робив ту саму дурну дорогу операцію весь день, а ніхто не зробив її видимою.
MySQL vs MariaDB: що насправді різниться для slow log
На папері MySQL і MariaDB обидва пропонують журнал повільних запитів, і ви вмикаєте його однаково: ставите поріг, логируєте порушників і потім аналізуєте. На практиці деталі мають значення, бо «той самий» запит може з’явитися з різним таймінгом, різним шумом та різною видимістю залежно від дефолтів рушія, інструментації та поведінки оптимізатора.
Що однаково
- Семантика slow query log: запис операторів, що перевищують поріг часу (або відповідають іншим критеріям, наприклад «не використовують індекси», якщо ви оберете цей шлях).
- Основні налаштування: slow_query_log, slow_query_log_file, long_query_time, log_queries_not_using_indexes (з зауваженнями), log_slow_admin_statements.
- Підхід до аналізу: групувати за fingerprint, сортувати за сумарним часом, потім переслідувати найгірших виконавців за допомогою EXPLAIN/EXPLAIN ANALYZE, змін у схемі/індексах та перезаписів запитів.
Що відрізняється достатньо, щоб підвести вас
1) Різниці в оптимізаторі й виконанні проявляються як різні «топ-запити» в slow-log. MariaDB і MySQL розійшлися з часом: стратегії оптимізатора, обробка похідних таблиць і дефолти фіч можуть призводити до різних планів. Це означає, що ви не можете сліпо застосувати «фікс, який працював на MySQL» для MariaDB (або навпаки) без валідації планів і оцінок рядків.
2) Шляхи інструментації різняться. MySQL 8 активно використовує Performance Schema і представлення sys для аналізу. MariaDB теж має Performance Schema, але в деяких розгортаннях він вимкнений або менш використовуваний; у MariaDB також є власні помічники діагностики. У будь-якому випадку slow log — це базова істина, але супровідна телеметрія може відрізнятися.
3) Дефолти версій важливіші за бренд. MySQL 5.7 і MySQL 8.0 можуть поводитися значно відмінніше, ніж уявна різниця між MySQL і MariaDB. Те саме для MariaDB 10.3 і 10.11. Для slow log важливо: чи є точні мітки часу, чи ви захоплюєте достатній контекст і чи можна пов’язати повільну команду з ресурсними вузькими місцями?
Ось неприємна правда продакшну: база, яку ви маєте, — це та, яку потрібно налаштовувати. Починайте з логів, а не з ідеології.
Швидкий план діагностики: знайдіть вузьке місце раніше, ніж воно знайде вас
Це потік «в мене 20 хвилин до наступного інцидент-колу». Він не вичерпний. Він покликаний швидко знайти домінантне вузьке місце і не дозволити вам гнатися за привидами.
Спочатку: підтвердьте, що проблема в часі виконання запитів, а не в підключеннях
- Перевірте, чи повільні запити через повільні виконання, чи через насиченість потоків, чи через очікування блокувань.
- Шукайте симптоми: зростання «Threads_running», «Questions» вирівнюється при зростанні латентності, спайки в «Innodb_row_lock_time», або багато станів «Sending data» в processlist.
По-друге: ідентифікуйте головного порушника за сумарним часом, а не за найгіршим одиночним запитом
- Запит, що займає 1.2s раз на годину — це не ваш інцидент. Запит, що займає 80ms і виконується 50k разів — це він.
- Сортуйте за sum Query_time у звіті slow log, потім за кількістю викликів.
По-третє: класифікуйте вузьке місце
Використайте патерн порушника, щоб помістити його в категорію:
- CPU/оптимізатор: багато rows examined, погане використання індексів, неправильний порядок з’єднань, filesort, temp tables.
- I/O: пропуски в buffer pool, підсилення читань, тимчасові таблиці на диску, повільна диск-латентність.
- Блокування: високий час блокувань, «Waiting for … lock», довгі транзакції, «гарячі» рядки.
- Поведіка застосунку: N+1 запити, балакучі ORM-патерни, відсутність пагінації, «SELECT *» у циклах.
По-четверте: оберіть найменш ризикове, найбільш продуктивне виправлення
- Створіть або підправте індекс, що відповідає патерну WHERE + JOIN + ORDER BY.
- Перепишіть запит, щоб уникнути сканування, зменшити ширину рядка або попередньо агрегувати.
- Зменшіть область блокувань (коротші транзакції, послідовний порядок доступу, кращі режими ізоляції).
- Лише потім розглядайте зміни конфігурації. Конфіг-правки реальні, але їх найпростіше піплити механічно.
Парафразована ідея (приписано): Gene Kim часто підкреслює, що покращення приходить від зроблення роботи видимою і зменшення витрат на навчання з продакшну.
Годинний робочий процес для slow log, що дає реальні результати
Одна година — достатньо, щоб отримати 2× прискорення, якщо ви робите менше «аналізу» і більше «тріажу». Мета не в ідеальному розумінні. Мета — одна домінантна клас запитів виправлена безпечно.
Хвилина 0–10: переконайтеся, що ви логируєте те, що треба
Почніть з підтвердження, що slow log увімкнений, поріг розумний, а формат логів парсабельний. Якщо поріг занадто високий, ви не побачите «смерть від тисячі дрібниць». Якщо він занадто низький, ви потонете в шумі й диск вирішить повстати.
Рекомендовані початкові пороги для продакшну:
- long_query_time: 0.1–0.5s для OLTP, 1–2s для змішаних навантажень. Якщо вже горить, почніть з 0.2s і підлаштуйтесь.
- log_queries_not_using_indexes: зазвичай вимкнений спочатку. Він дає багато хибних спрацьовувань (невеликі таблиці, легітимні скани). Увімкніть його тимчасово, якщо точно знаєте, що робите.
- log_slow_admin_statements: увімкнений, бо «ALTER TABLE» може бути мовчазним убийцею.
Хвилина 10–25: зробіть звіт top-N і оберіть одну жертву
Згенеруйте зведений звіт (pt-query-digest — робоча конячка). Сортуйте за сумарним часом. Оберіть топ-клас запитів, що (a) частий, (b) дорогий, (c) виправний без переписування половини застосунку.
Саме тут люди саботують себе: вони вибирають найскладніший запит, бо він виглядає «важливим». Вам потрібен нудний запит, що виконується постійно.
Хвилина 25–45: валідуйте за допомогою EXPLAIN ANALYZE і зробіть одну цільову зміну
Візьміть репрезентативний зразок запиту (не найгірший викид) і запустіть EXPLAIN та, якщо доступно, EXPLAIN ANALYZE. Ви шукаєте невідповідність між тим, що ви думаєте, і тим, що насправді: сканування таблиць, неправильний порядок з’єднань, вибух rows examined, temp tables, filesorts або використання неправильного індексу.
Потім зробіть рівно одну зміну:
- Створіть один індекс (або підправте порядок одного композитного індексу).
- Перепишіть один предикат, щоб він став sargable (наприклад, уникайте обгортання індексованих стовпців у функції).
- Додайте LIMIT/стратегію пагінації або розбийте запит на дешевий попередній фільтр плюс join.
Хвилина 45–60: доведіть, що стало краще, і що нічого не поламано
Перезапустіть EXPLAIN ANALYZE. Порівняйте slow log до/після (навіть коротке вікно допомагає). Спостерігайте p95 латентність та показники CPU/IO. Якщо запит став швидшим, але система погіршилась, ви ймовірно перенесли навантаження кудись ще (блокування, tmp tables, реплікація, диск).
Жарт №1: Журнал повільних запитів — єдиний колега, що пам’ятає, що сталося вчора вночі, і він ніколи «не забуває» записати це.
Практичні завдання (команди, виводи, рішення)
Нижче — практичні, виконувані завдання. Кожне містить: команду, приклад того, як виглядає вивід, що це означає і яке рішення ви приймаєте. Використовуйте їх як чекліст, а не як ритуал. Мета — скоротити час до істини.
Task 1: Confirm slow log is enabled and where it’s writing
cr0x@server:~$ mysql -e "SHOW VARIABLES LIKE 'slow_query_log'; SHOW VARIABLES LIKE 'slow_query_log_file'; SHOW VARIABLES LIKE 'long_query_time';"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| slow_query_log| ON |
+---------------+-------+
+---------------------+---------------------------+
| Variable_name | Value |
+---------------------+---------------------------+
| slow_query_log_file | /var/log/mysql/slow.log |
+---------------------+---------------------------+
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| long_query_time | 0.200 |
+-----------------+-------+
Що це означає: Логування активне, поріг 200ms, шлях до файлу відомий.
Рішення: Якщо slow_query_log = OFF — увімкніть його (тимчасово, якщо треба). Якщо long_query_time = 10s — ви не спостерігаєте реальне робоче навантаження.
Task 2: Enable slow log dynamically (safe, reversible)
cr0x@server:~$ mysql -e "SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 0.2; SET GLOBAL log_slow_admin_statements = 'ON';"
Що це означає: Ви вмикаєте захоплення без перезапуску.
Рішення: Робіть це під час інциденту, якщо у вас немає даних. Але також заплануйте зміну конфігурації пізніше, щоб вона зберігалася після рестарту.
Task 3: Confirm you’re not accidentally logging everything (file growth sanity check)
cr0x@server:~$ sudo ls -lh /var/log/mysql/slow.log
-rw-r----- 1 mysql adm 1.8G Dec 29 10:12 /var/log/mysql/slow.log
Що це означає: Файл великий. Можливо це нормально. Можливо він зжирає диск.
Рішення: Якщо він росте надто швидко, трохи підніміть long_query_time або робіть вибірку під час піку, потім ротувати.
Task 4: Rotate the slow log without restarting mysqld
cr0x@server:~$ mysql -e "FLUSH SLOW LOGS;"
Що це означає: MySQL/MariaDB закриває і відкриває файл slow log, дозволяючи logrotate робити свою справу.
Рішення: Використайте це перед початком однієї-годинного збору, щоб ваш набір даних був «чистим».
Task 5: Quick skim: top offenders with mysqldumpslow
cr0x@server:~$ mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
Reading mysql slow query log from /var/log/mysql/slow.log
Count: 842 Time=0.38s (319s) Lock=0.00s (0s) Rows=1.0 (842), root[root]@10.0.2.15
SELECT * FROM orders WHERE user_id=N AND status='open' ORDER BY created_at DESC LIMIT N
Count: 55 Time=2.12s (116s) Lock=0.01s (1s) Rows=30000.0 (1650000), app[app]@10.0.3.9
SELECT customer_id, SUM(amount) FROM invoices WHERE created_at >= 'S' GROUP BY customer_id
Що це означає: Перший запит частий і помірно повільний; другий важкий, але менш частий. Сумарний час важливий: 319s проти 116s у цьому зразку.
Рішення: Спочатку досліджуйте верхній за сумарним часом. Запит з 842 викликами — імовірний кандидат на 2× виграш.
Task 6: Proper grouping and ranking with pt-query-digest
cr0x@server:~$ pt-query-digest /var/log/mysql/slow.log | sed -n '1,120p'
# 320s total, 842 queries, 0.38s avg, 0.05s 95% 0.89s max
# Rank Query ID Response time Calls R/Call V/M Item
# ==== ================== ============= ===== ====== ===== ============
# 1 0x8F3A1C9E6B0A2C11 319.1240 99.7% 842 0.3790 1.02 SELECT orders
# 2 0xB11D0E2A6F9C0D22 116.6042 36.4% 55 2.1201 1.40 SELECT invoices
# ...
Що це означає: Запит №1 домінує за часом стіни. Виправлення його дасть найбільший виграш.
Рішення: Витягніть точний зразок запиту з секції pt-query-digest і візьміть його до EXPLAIN.
Task 7: Find the exact table/index situation (SHOW CREATE TABLE)
cr0x@server:~$ mysql -e "SHOW CREATE TABLE orders\G"
*************************** 1. row ***************************
Table: orders
Create Table: CREATE TABLE `orders` (
`id` bigint NOT NULL AUTO_INCREMENT,
`user_id` bigint NOT NULL,
`status` varchar(16) NOT NULL,
`created_at` datetime NOT NULL,
`total` decimal(10,2) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_status` (`status`),
KEY `idx_user` (`user_id`)
) ENGINE=InnoDB
Що це означає: Немає композитного індексу, що відповідає (user_id, status, created_at). Сортування по created_at ймовірно спричиняє додаткову роботу.
Рішення: Додайте композитний індекс, вирівняний під WHERE і ORDER BY.
Task 8: EXPLAIN ANALYZE the slow query (reality check)
cr0x@server:~$ mysql -e "EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id=123 AND status='open' ORDER BY created_at DESC LIMIT 20\G"
*************************** 1. row ***************************
EXPLAIN: -> Limit: 20 row(s) (actual time=210.112..210.140 rows=20 loops=1)
-> Sort: orders.created_at DESC (actual time=210.109..210.114 rows=3200 loops=1)
-> Index lookup on orders using idx_user (user_id=123) (actual time=0.210..205.900 rows=3200 loops=1)
Що це означає: Використовується idx_user, потім сортується 3200 рядків, щоб вибрати 20. Там і йде час.
Рішення: Додайте індекс, щоб задовольнити фільтрацію і сортування, тоді рушій зможе зупинитися раніше.
Task 9: Create the index safely (and know what “online” means)
cr0x@server:~$ mysql -e "ALTER TABLE orders ADD INDEX idx_user_status_created (user_id, status, created_at DESC);"
Query OK, 0 rows affected (12.41 sec)
Records: 0 Duplicates: 0 Warnings: 0
Що це означає: Індекс створено. Час і поведінка блокувань залежать від версії і налаштувань; деякі збірки роблять це «на місці», але це все одно споживає IO і може затримати через metadata locks.
Рішення: Проводьте під час низької активності, якщо таблиця велика. Моніторте на предмет очікувань metadata lock.
Task 10: Re-run EXPLAIN ANALYZE to verify early exit
cr0x@server:~$ mysql -e "EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id=123 AND status='open' ORDER BY created_at DESC LIMIT 20\G"
*************************** 1. row ***************************
EXPLAIN: -> Limit: 20 row(s) (actual time=1.432..1.455 rows=20 loops=1)
-> Index lookup on orders using idx_user_status_created (user_id=123, status='open') (actual time=0.210..1.410 rows=20 loops=1)
Що це означає: Крок сортування зник, торкаються лише 20 рядків. Ось як ви повертаєте латентність.
Рішення: Якщо план не змінився, досліджуйте чому (неправильний порядок індексу, невідповідність колації/типів або преференція оптимізатора).
Task 11: Check for lock contention in the slow log and runtime counters
cr0x@server:~$ mysql -e "SHOW GLOBAL STATUS LIKE 'Innodb_row_lock_time%'; SHOW GLOBAL STATUS LIKE 'Threads_running';"
+----------------------------+--------+
| Variable_name | Value |
+----------------------------+--------+
| Innodb_row_lock_time | 184223 |
| Innodb_row_lock_time_max | 12000 |
| Innodb_row_lock_waits | 912 |
+----------------------------+--------+
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| Threads_running | 48 |
+-----------------+-------+
Що це означає: Є відчутні очікування на блокування. Threads_running високий, що вказує на тиск конкурентності.
Рішення: Якщо час блокувань — велика частка Query_time у записях slow log, зосередьтесь на просторі транзакцій і «гарячих» рядках, а не на індексах.
Task 12: Inspect live queries and spot “Waiting for … lock” quickly
cr0x@server:~$ mysql -e "SHOW FULL PROCESSLIST;" | sed -n '1,18p'
Id User Host db Command Time State Info
1209 app 10.0.3.9:53122 prod Query 12 Waiting for table metadata lock ALTER TABLE orders ADD COLUMN note varchar(64)
1211 app 10.0.3.7:60111 prod Query 9 Sending data SELECT * FROM orders WHERE user_id=123 AND status='open' ORDER BY created_at DESC LIMIT 20
1220 app 10.0.3.6:42290 prod Query 8 Sending data SELECT * FROM orders WHERE user_id=456 AND status='open' ORDER BY created_at DESC LIMIT 20
Що це означає: ALTER TABLE блокує. Це не «тюнінг повільних запитів»; це управління змінами.
Рішення: Зупиніть DDL (якщо безпечно) або перенесіть у інший час. Запобігайте повторенню дисциплінованими практиками міграцій.
Task 13: Check InnoDB buffer pool health (are we reading from disk?)
cr0x@server:~$ mysql -e "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';"
+---------------------------------------+-----------+
| Variable_name | Value |
+---------------------------------------+-----------+
| Innodb_buffer_pool_read_requests | 982341234 |
| Innodb_buffer_pool_reads | 19348231 |
+---------------------------------------+-----------+
Що це означає: Читання з диска не тривіальні. Важливіший не сирий номер, а співвідношення; багато дискових читань може бути нормальним для великого робочого набору, але також може означати, що ваш buffer pool замалий або запити сканують таблиці.
Рішення: Якщо робочий набір вміщується в пам’ять, але ви все одно читаєте багато — ймовірно погані індекси або патерни доступу. Якщо ж набір реально перевищує пам’ять — пріоритетно тюнити найбільші скани й розглянути розмір пам’яті.
Task 14: Check temporary tables and whether they spill to disk
cr0x@server:~$ mysql -e "SHOW GLOBAL STATUS LIKE 'Created_tmp%tables';"
+-------------------------+--------+
| Variable_name | Value |
+-------------------------+--------+
| Created_tmp_disk_tables | 48291 |
| Created_tmp_tables | 90210 |
+-------------------------+--------+
Що це означає: Багато тимчасових таблиць потрапляють на диск. Це корелює з GROUP BY/ORDER BY по неіндексованих стовпцях, широкими рядками або недостатніми налаштуваннями пам’яті для tmp table.
Рішення: Спочатку віддайте перевагу виправленням запитів і індексів. Якщо запит за своєю природою важкий — регулюйте tmp_table_size/max_heap_table_size обережно й валідно плануйте пам’ять.
Task 15: Validate storage latency during the slow window (don’t guess)
cr0x@server:~$ iostat -xz 1 5
Linux 6.5.0 (server) 12/29/2025 _x86_64_ (8 CPU)
avg-cpu: %user %nice %system %iowait %steal %idle
22.14 0.00 7.12 18.33 0.00 52.41
Device r/s rkB/s rrqm/s %rrqm r_await rareq-sz w/s wkB/s w_await aqu-sz %util
nvme0n1 322.0 18232.0 0.0 0.00 18.40 56.60 90.0 9120.0 4.10 6.32 78.00
Що це означає: r_await ~18ms свідчить, що читання зі сховища не «швидкі». Якщо ваше навантаження очікує низькомілісекундні читання, це прямо впливає на час запитів при пропусках buffer pool.
Рішення: Якщо латентність висока, ви все ще можете отримати 2× виграш, зменшивши кількість читань (індексація, коректне використання LIMIT). Також перевірте «шумних сусідів», деградацію RAID, fsync-сторм і поведінку контрольних точок.
Task 16: Check binary log and replication lag signals (fixes can move pain)
cr0x@server:~$ mysql -e "SHOW MASTER STATUS\G; SHOW SLAVE STATUS\G" | sed -n '1,40p'
*************************** 1. row ***************************
File: mysql-bin.002341
Position: 91822310
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
*************************** 1. row ***************************
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 47
Що це означає: Репліка відстає. Важкі запити або DDL можуть погіршити відставання. Деякі «оптимізації» підвищують ампліфікацію записів (нові індекси) і тимчасово роблять реплікацію повільнішою.
Рішення: Плануйте побудову індексів акуратно і спостерігайте lag. Якщо lag критичний для бізнесу, розгляньте обмеження міграцій або використання онлайн-інструментів для зміни схеми.
Шаблони у slow log, що зазвичай дають 2×
Більшість 2× виграшів не екзотичні. Це — усунення випадкової роботи. Ось шаблони, які з’являються в slow log по обох флотах MySQL і MariaDB.
1) «ORDER BY … LIMIT» без підходящого індексу
Класика: фільтрація по деяких стовпцях, сортування по timestamp, LIMIT 20. Без композитного індексу, що відповідає фільтру + сортуванню, рушій читає багато рядків і сортує їх. У slow log ви бачите помірний час на виклик, масивну кількість викликів і Rows_examined значно більше, ніж Rows_sent.
Виправлення: Композитний індекс з equality-предикатами на початку, потім стовпець сортування. Використовуйте DESC індекси там, де підтримується і це має сенс; інакше рушій може читати в зворотному порядку, але не слід цього передбачати. Перевіряйте через EXPLAIN ANALYZE.
2) Непошукові предикати (non-sargable)
Якщо у вас WHERE DATE(created_at)=… або WHERE LOWER(email)=…, ви примусите оптимізатор у кут. Він не зможе ефективно використати індекс, бо ви обгорнули індексований стовпець у функцію.
Виправлення: Перепишіть у діапазон (created_at >= … AND created_at < …) або зберігайте нормалізовані значення у індексованій генерованій колонці (залежить від версії), або нормалізуйте при записі.
3) Великі списки IN() та «запити, згенеровані аплікацією»
Slow log любить показувати запити типу WHERE id IN (…тисячі…). Часто це патерн ORM або спроба батчінгу, що вийшла з-під контролю. Це може викликати великий наклад при парсингу, погані плани і великі тимчасові структури.
Виправлення: Використайте тимчасову таблицю, join проти реальної таблиці або переробіть патерн доступу. Якщо потрібно, обмежте розмір батчу і вимірюйте.
4) Оновлення «гарячих» рядків і очікування блокувань
Проблеми з блокуваннями виглядають як «випадкова повільність», поки ви не подивитесь на Lock_time у записах slow log і стани в processlist. Один гарячий лічильник може уповільнити весь сервіс.
Виправлення: Зменшіть конкуренцію: шардируйте лічильники, використовуйте INSERT … ON DUPLICATE KEY на розподілених ключах або перенесіть «гарячий» шлях у сервіс, спроєктований для великої кількості записів. Також скоротіть транзакції і забезпечте послідовний порядок блокувань.
5) GROUP BY, що виливається на диск
Якщо ви агрегуєте великі набори і тимчасові таблиці потрапляють на диск, латентність збережеться на рівні дискової латентності. У slow log ви бачите великий Query_time при відносно низькому CPU, і лічильники tmp disk tables ростуть.
Виправлення: Додайте індекси, що підтримують групування, зменшіть ширину рядка, попередньо агрегуйте або виносьте звітні навантаження з основного OLTP.
6) «SELECT *» на широких таблицях
Читання широких рядків коштує пам’яті, buffer pool, мережі й CPU. Вони також підривають покривні індекси. У slow log ви бачите запити, що витягують стовпці, які аплікація ніколи не використовує.
Виправлення: Обирайте лише потрібні стовпці. Якщо хочете швидкості — перестаньте тягати меблі, коли вам потрібні тільки ключі.
Три корпоративні міні-історії з фронту
Міні-історія 1: Інцидент, викликаний неправильною асумпцією
Середня компанія вела e‑commerce з MySQL primary і кількома репліками. Команда аплікації додала «останні замовлення» на інформаційну панель користувача. Здавалося нешкідливо: фільтр по user_id, status, order by created_at, limit 20. У staging це було швидко, бо staging мав малі таблиці і теплі кеші — як усі staging середовища, це була приємна брехня.
У продакшні p95 латентності подвоїлися під час піку. Інженер на чергуванні робив звичне: масштабував поди аплікації, перезапускав декілька речей, дивився, як стає гірше. CPU на БД піднявся, але не до межі. Диск не був завантажений. Було «таємничо».
Неправильна асумпція була проста: «LIMIT 20 означає, що читаються лише 20 рядків». Без відповідного композитного індексу рушій читав тисячі рядків на користувача, сортував їх і потім повертав 20. Це повторювалося для тисяч користувачів, тисячі разів. Slow log показував запит, що не виглядав окремо жахливо — ~350ms — але він виконувався постійно.
Вони додали композитний індекс (user_id, status, created_at). Запит упав до кількох мілісекунд. Інцидент закінчився не героїчним тюнінгом, а одним індексом і нагадуванням: SQL не читає ваші думки.
Дальша дія була цікавіша: вони змінили свій чекліст деплою, щоб вимагати захоплення 15‑хвилинного зразку slow log після будь‑якої фічі, що додає новий шлях запитів. Не тому, що вони люблять процес — тому що вони люблять спати.
Міні-історія 2: Оптимізація, що вистрілила собі в ногу
Інша організація працювала на MariaDB для білінгу. Запити звітності били по primary у робочий час. Хтось запропонував швидкий фікс: «Увімкнемо log_queries_not_using_indexes і додамо індекси для всього, що з’явиться». Логіка звучала чітко. Вона також перетворила slow log на пожежний шланг.
За день вони створили кілька нових індексів, включно з широкими композитами на мало селективних колонках. Записи сповільнилися. Буферний пул почав інтенсивно змінюватися. Зміни також збільшили replication lag, бо кожен insert/update тепер підтримував більше індексів. Тим часом повільні запити мало покращилися — бо найгірші були легітимними повними сканами за місячними партіціями для фінансових зведень. Лог «not using indexes» просто звинувачував базу у виконанні саме того, що запит просив.
Провал був тонким: команда ганялася за «втраченими індексами» замість того, щоб вирішувати форму навантаження. Правильне рішення — перемістити звітування на репліку (або виділити аналітичний шлях), додати кілька прицільних індексів і змінити кілька запитів на попередню агрегацію.
Вони зрештою відкатали кілька індексів, що завжди весело, бо видалення індексів також потребує часу й IO. Урок: не дозволяйте діагностичному прапору стати продуктовою вимогою.
Жарт №2: Увімкнення всіх опцій логування «для видимості» — це як замінити ваш детектор диму на генератор туману.
Міні-історія 3: Нудна, але правильна практика, що врятувала день
SaaS компанія використовувала MySQL 8 для мульти‑тентних навантажень. У них була практика, про яку ніхто не хвалився: slow query log завжди увімкнений на помірному порозі, ротований щогодини і підсумований щодня. Без драм. Без «увімкнемо, коли потрібно». Він просто був, тихо захоплюючи істину.
Одного дня латентність підійшла. Не катастрофа, але помітно. На чергуванні не гадали. Вони витягли останню годину slow log, запустили pt-query-digest і побачили новий fingerprint на вершині. Це був ORM-згенерований join з несподіваним предикатом на колонці з малою кардинальністю, що призводило до вибуху рядків.
Оскільки у них були базові щоденні підсумки, вони могли довести, що запит новий, і кількісно оцінити вплив без спекуляцій. У них також була політика: будь‑яка зміна індекса вимагала EXPLAIN ANALYZE до/після і швидку перевірку replication lag. Ця політика не модна. Вона ефективна.
Вони випустили невеликий перепис запиту і вузький композитний індекс. Латентність повернулася до норми. Пост‑інцидентна нота була короткою: «slow log зловив; фікс валідували; без побічних ефектів». Ось мрія — нудно, правильно, повторювано.
Поширені помилки: симптом → корінь → виправлення
Цей розділ навмисно конкретний. Якщо ви можете швидко зіставити симптом з коренем, ви повертаєте своє життя.
1) Симптом: slow log показує високий Query_time, але низький Rows_examined
Корінь: Очікування, а не сканування — блокування, fsync‑тиск або мережеві затримки. Часто Lock_time високий або спайки латентності диску.
Виправлення: Перевірте Lock_time у записах slow log, SHOW PROCESSLIST стани та InnoDB лічильники блокувань. Для IO корелюйте з iostat і контрольними точками. Не додавайте індекси «бо повільно».
2) Симптом: запити швидкі на репліці, повільні на primary
Корінь: Primary виконує додаткову роботу: записи, fsync, churn у buffer pool або конкуренція блокувань. Також можливі відмінності в апараті або дрейф конфігів.
Виправлення: Порівняйте змінні конфігу і статистики buffer pool. Перевірте навантаження: чи запускають звіти на primary? Припиніть це в першу чергу.
3) Симптом: Топ повільний запит — простий SELECT з ORDER BY + LIMIT
Корінь: Відсутній композитний індекс, що відповідає фільтру і сортуванню, спричиняючи filesort і сканування.
Виправлення: Додайте індекс з equality-предикатами на початку, потім колонку сортування. Підтвердіть через EXPLAIN ANALYZE, що крок сортування зник і кількість торканих рядків впала.
4) Симптом: Раптовий сплеск повільних запитів після деплою, але лише для деяких користувачів/тенантів
Корінь: Скос даних. План нормальний для типової вибірки, але жахливий для «великих» тenant‑ів. Статистика може вводити оптимізатор в оману.
Виправлення: Тестуйте на репрезентативних розмірах тенантів. Розгляньте кращі індекси, зміни в запитах або розбиття великих тенантів. Оновіть статистику і валідуйте плани на зіскових випадках.
5) Симптом: Багато Created_tmp_disk_tables і повільний GROUP BY
Корінь: Тимчасові таблиці на диск через недостатні індекси, широкі рядки, великі результати або обмеження пам’яті.
Виправлення: Зменшіть ширину рядків, додайте підтримуючі індекси, попередньо агрегуйте і лише потім розглядайте tuning tmp_table_size/max_heap_table_size з бюджетуванням пам’яті.
6) Симптом: slow log файл величезний, аналіз болісний, диск росте
Корінь: long_query_time надто малий для завжди увімкненого режиму, або повний потік запитів, або відсутність ротації.
Виправлення: Запровадьте ротацію і політику збереження; робіть вибірки під час піку; налаштуйте поріг. Використовуйте pt-query-digest на обмеженому часовому вікні.
7) Симптом: Після додавання індексу читання стали швидшими, але реплікація відстала
Корінь: Додаткове утримання індексів підвищило вартість записів. На репліках застосування подій стало важчим.
Виправлення: Тримайте індекси мінімальними і цілеспрямованими. Додавайте лише той індекс, що відповідає вашому топ-порушнику. Для міграцій обмежуйте швидкість і моніторьте lag; розгляньте планування або вивантаження навантаження.
Контрольні списки / покроковий план
Чекліст: захопити одну годину корисних slow log (безпечно для продакшну)
- Підтвердьте, що slow log увімкнений і шлях до файлу (Task 1).
- Встановіть long_query_time на корисний поріг для вашого навантаження (Task 2).
- FLUSH SLOW LOGS, щоб почати зі свіжого файлу (Task 4).
- Збирайте під час репрезентативної «зайнятої» години, а не під час затишшя.
- Ротувати в кінці (знову Task 4), щоб аналіз був обмежений.
- Записати контекст: обсяг трафіку, версії релізів, будь‑які міграції в процесі.
Чекліст: перетворити годину на ранжований список дій
- Запустіть pt-query-digest і сортуйте за сумарним часом відповіді (Task 6).
- Оберіть один топ fingerprint запиту, що частий і піддається виправленню.
- Витягніть схему і існуючі індекси для залучених таблиць (Task 7).
- Запустіть EXPLAIN ANALYZE на репрезентативному екземплярі запиту (Task 8).
- Класифікуйте вузьке місце: сканування, сортування, тимчасові таблиці, блокування, IO.
- Запропонуйте одну мінімальну зміну: один індекс або один перепис запиту.
- Реалізуйте обережно; слідкуйте за metadata locks і replication lag (Tasks 12 і 16).
- Повторно запустіть EXPLAIN ANALYZE і порівняйте ранжування у slow log після зміни (Task 10 і знову digest).
Чекліст: запобіжні заходи (бо ви будете спокушені)
- Не додавайте «covering indexes» за замовчуванням. Широкі індекси роздувають buffer pool і гальмують записи.
- Не тюньте глобальні ручки до того, як не визначили одного винуватця.
- Не довіряйте продуктивності staging, якщо розподіл даних у продакшні відрізняється.
- Не виконуйте DDL під час піку, якщо у вас немає доказів online‑поведінки для вашої версії й форми таблиці.
Цікаві факти та історичний контекст
Трохи контексту допомагає, бо люди повторюють міфи з впевненістю, а продакшн карає за цю звичку.
- MariaDB почалася як форк MySQL після придбання Sun компанією Oracle (2009). Наразі твердження «це те саме» було правдою на початку; відмінності реальні зараз.
- Performance Schema MySQL значно дозрів у останніх версіях. Сучасний MySQL опирається на нього для діагностики; старі розгортання часто більше покладалися на slow log і зовнішні інструменти.
- Журнал повільних запитів передує нинішній спалі на спостережуваність. Це одна з найстаріших фіч в екосистемі MySQL: «просто запиши правду».
- pt-query-digest (Percona Toolkit) став популярним, бо slow logs багатослівні, але не одразу дієві. Digest перетворює щоденник у ранжований список завдань.
- MySQL видалив застарілий Query Cache у 8.0. Він був відомий тим, що допомагав бенчмаркам і шкодив реальній конкурентності. Якщо хтось радить його «увімкнути для швидкості», спитайте, в якому він році живе.
- InnoDB давно став дефолтним рушієм зберігання для MySQL, замінивши MyISAM у серйозних розгортаннях. Багато фіксів для slow-query припускають транзакційну семантику і блокування на рівні рядка.
- EXPLAIN ANALYZE — відносно недавній подарунок порівняно з простим EXPLAIN. Він вимірює реальне виконання, що безцінно, коли оцінки оптимізатора впевнено помиляються.
- Metadata locks дивують команди, бо вони не «повільні запити», доки не стають такими. DDL може блокувати читання/записи так, що це виглядає як повільні запити аплікації.
FAQ
1) Чим користуватися: slow query log чи Performance Schema?
Використовуйте slow query log як ground truth для «що було повільно». Використовуйте Performance Schema для «чому було повільно» (waits, stages, CPU‑похідні сигнали). Якщо можна вибрати лише одне — спочатку вибирайте slow log.
2) Який long_query_time ставити для OLTP?
Почніть з 0.2s у завантаженій системі, якщо хочете швидко отримувати дієві дані. Якщо це надто шумно — перейдіть до 0.5s. Якщо поставите 2–10s, ви зловите лише катастрофи, а не хронічні витрати.
3) Чи варто вмикати log_queries_not_using_indexes?
Як постійне налаштування: зазвичай ні. Як короткий, продуманий експеримент: інколи. Він сигналізує про легітимні повні скани і заохочує до «індексного спаму». Використовуйте, коли ви вже знаєте, що скани випадкові, а не коли ще діагностуєте.
4) Чому «той самий» запит показує різну продуктивність на MySQL і MariaDB?
Різні рішення оптимізатора, різні дефолти, різна поведінка статистик і різні поєднання версій рушіїв. Ставтесь до кожного сервера як до окремої системи: перевіряйте плани, не покладайтеся на племінну пам’ять.
5) Чи може індекс зробити запит повільнішим?
Так. Оптимізатор може обрати гірший індекс, або підтримка індексу може уповільнити записи настільки, що сумарна латентність зросте. Тому ви валідуєте через EXPLAIN ANALYZE і спостерігаєте метрики системи, а не лише час одного запиту.
6) Що робити, якщо slow log заповнений адмінськими командами на кшталт ALTER TABLE?
Тоді ваш вузький місце — це управління змінами. Плануйте DDL, використовуйте online‑техніки, відповідні для вашої версії, і запобігайте несподіваним міграціям під час піку. Виправлення — операційна дисципліна.
7) Як зрозуміти: проблема в дисковому IO чи в поганих запитах?
Корелюйте: високі Innodb_buffer_pool_reads разом з високими storage await вказують на чутливість до IO. Навіть тоді, найкраще перше виправлення — зменшити читання через індекси і форму запитів. Оновлення апаратури — валідне, але не замінює усунення марної роботи.
8) Як перетворити одну годину логів у надійне прискорення 2×?
Ранжуйте за сумарним часом, оберіть одного частого порушника, валідуйте план через EXPLAIN ANALYZE, застосуйте одну мінімальну зміну (індекс/перепис), потім переміряйте. Уникайте «тюнінгу за відчуттями» (декілька одночасних змін без доказів).
9) Чи шкодить увімкнення slow query log продуктивності?
Воно додає наклад, але для більшості систем це керовано при розумних налаштуваннях. Більший ризик — ріст диску, якщо ви логируєте занадто багато без ротації. Якщо продуктивність надчутлива, робіть вибірки під час піку і ротування агресивно.
10) Усі мої повільні запити в processlist мають статус «Sending data». Що робити?
«Sending data» часто означає, що запит читає рядки і обробляє їх (не обов’язково мережеву відправку). Подивіться на Rows_examined, використання індексів і чи скануєте/сортуєте. Це також може приховувати очікування IO при читанні сторінок.
Наступні кроки, які можна зробити сьогодні
Зробіть нудну настройку раз і потім збирайте виграші постійно:
- Увімкніть журнал повільних запитів на розумному порозі і тримайте його увімкненим з ротацією. Робіть це частиною системи, а не аварійною важелем.
- Автоматизуйте щоденний дайджест (навіть якщо це просто cron, що запускає pt-query-digest і зберігає топ‑20 fingerprint). Тренди важливі.
- Прийміть правило «одна зміна під час інциденту»: один індекс або один перепис запиту, валідуваний EXPLAIN ANALYZE і швидка перевірка replication/lock.
- Зробіть звичкою «rows examined vs rows returned». Це найшвидший індикатор марної роботи.
- Запишіть ваші топ‑5 fingerprints і ставтесь до них як до продакшн-залежностей. Бо вони такими й є.
Якщо ви хочете прискорення 2×, не шукайте магічних налаштувань. Відкрийте slow log, знайдіть домінантний клас запитів за сумарним часом і приберіть його випадкову роботу. Продакшн не потребує героїв. Він потребує менше безглуздих читань.