Як увімкнути журнал повільних запитів у MySQL та MariaDB

Журнал повільних запитів — це вбудована діагностична функція MySQL і MariaDB, яка записує кожен SQL-оператор, час виконання якого перевищує налаштований поріг. Вона фіксує тривалість запиту, час блокування, кількість перевірених рядків, кількість надісланих рядків і повний текст SQL — надаючи адміністраторам баз даних і розробникам точний файловий журнал аудиту кожного запиту, що погіршує продуктивність застосунку.

Увімкнення цієї функції є одним із найефективніших кроків під час налаштування продуктивності бази даних. На відміну від загальних інструментів моніторингу, журнал повільних запитів точно визначає оператори, відповідальні за затримки, що робить його незамінним для оптимізації індексів, реструктуризації запитів і планування ресурсів на будь-якому сервері — від однокористувацького середовища VPS Hosting до багатовузлового виділеного кластера баз даних.

Чому журнал повільних запитів важливіший за базовий моніторинг

Більшість команд звертаються до EXPLAIN або SHOW PROCESSLIST реактивно, після того як користувачі повідомляють про повільну роботу. Журнал повільних запитів працює проактивно: він накопичує дані протягом годин або днів реального трафіку, фіксуючи нерегулярні порушники, які ніколи не з’являються під час ручної перевірки.

Ключові операційні переваги включають:

• Ізоляція вузьких місць — розрізняє повне сканування таблиць, обмежене CPU, від проблем конкуренції блокувань за допомогою співвідношень Query_time та Lock_time
• Аналіз прогалин в індексах — прапорець log_queries_not_using_indexes виявляє кожен запит, що виконує повне сканування, незалежно від часу виконання
• Виявлення регресій — порівняння знімків журналу до і після розгортання показує, чи не запровадив новий код повільніші шаблони запитів
• Докази для планування ресурсів — значення Rows_examined, що на порядки перевищують Rows_sent, вказують на відсутні або неправильно використовувані індекси, які посилюються під навантаженням

MySQL проти MariaDB: порівняння функцій журналу повільних запитів

Обидва рушії використовують однакову базову інфраструктуру журналу повільних запитів, успадковану від MySQL 5.1, але MariaDB розширила її кількома суттєвими способами.

Параметри log_slow_verbosity та log_slow_rate_limit у MariaDB особливо цінні у високонавантажених виробничих середовищах, де саме журналювання кожного повільного запиту може стати проблемою продуктивності.

Крок 1: Знайдіть файл конфігурації

MySQL і MariaDB зчитують конфігурацію з різних шляхів за замовчуванням залежно від дистрибутива та методу встановлення.

MySQL:

/etc/my.cnf (на основі RPM: RHEL, CentOS, AlmaLinux, Rocky Linux)
/etc/mysql/my.cnf (Debian/Ubuntu)
/etc/mysql/mysql.conf.d/mysqld.cnf (Ubuntu з пакетом mysql-server)

MariaDB:

/etc/my.cnf.d/server.cnf (на основі RPM)
/etc/mysql/mariadb.conf.d/50-server.cnf (Debian/Ubuntu)
/etc/mysql/mariadb.cnf (старіші макети Debian)

Якщо ви не впевнені, який файл активний, зробіть запит до запущеного процесу:
mysqld --verbose --help 2>/dev/null | grep -A1 "Default options"
Це виводить точний упорядкований список файлів, які демон зчитує під час запуску, включаючи будь-які каталоги !includedir.
Відкрийте основний файл конфігурації у вашому улюбленому редакторі:
sudo nano /etc/my.cnf
Крок 2: Додайте директиви журналу повільних запитів до [mysqld]
Усі параметри журналу повільних запитів належать до розділу [mysqld]. Якщо розділ не існує, створіть його на початку файлу.
[mysqld]
# Core slow query log settings
slow_query_log          = 1
slow_query_log_file     = /var/log/mysql/slow-query.log
long_query_time         = 1

# Log queries that skip index usage entirely
log_queries_not_using_indexes = 1

# Avoid flooding the log with index warnings on low-traffic tables
min_examined_row_limit  = 100

# Log slow administrative statements (ALTER TABLE, OPTIMIZE TABLE, etc.)
log_slow_admin_statements = 1
Опис параметрів:

slow_query_log = 1 — активує функцію; встановіть 0 для вимкнення без видалення блоку
slow_query_log_file — абсолютний шлях до файлу журналу; системний користувач процесу MySQL/MariaDB (mysql) повинен мати права на запис у батьківський каталог
long_query_time = 1 — поріг у секундах, приймає десяткові значення (наприклад, 0.5 для 500 мс); значення за замовчуванням 10 секунд майже завжди є надто м’яким для веб-застосунків
log_queries_not_using_indexes — записує запити з повним скануванням незалежно від long_query_time; поєднуйте з min_examined_row_limit для придушення шуму від малих таблиць
min_examined_row_limit — запит повинен перевірити щонайменше стільки рядків, щоб відповідати критеріям журналювання за log_queries_not_using_indexes; запобігає забрудненню журналу тривіальними пошуками одного рядка
log_slow_admin_statements — фіксує операції на рівні схеми, що блокують таблиці та часто залишаються поза увагою як джерела затримок

Специфічні для MariaDB доповнення, варті увімкнення у виробництві:
# MariaDB only — extended per-query statistics in the log
log_slow_verbosity      = query_plan,explain

# MariaDB only — log only 1 in every N qualifying queries (rate limiting)
log_slow_rate_limit     = 10
log_slow_verbosity = query_plan,explain додає план виконання оптимізатора безпосередньо до кожного запису журналу, усуваючи необхідність повторного запуску EXPLAIN вручну після факту — значна економія часу при діагностиці запитів, що з’являються лише під виробничими шаблонами навантаження.
Крок 3: Створіть файл журналу та встановіть дозволи
Якщо цільовий каталог не існує, створіть його та призначте власника перед перезапуском служби. Пропуск цього кроку є однією з найпоширеніших причин, через які журнал повільних запитів мовчки не активується.
sudo mkdir -p /var/log/mysql
sudo touch /var/log/mysql/slow-query.log
sudo chown mysql:mysql /var/log/mysql/slow-query.log
sudo chmod 640 /var/log/mysql/slow-query.log
На системах із примусовим SELinux (RHEL, CentOS, AlmaLinux) також необхідно правильно встановити контекст файлу:
sudo semanage fcontext -a -t mysqld_log_t "/var/log/mysql(/.*)?"
sudo restorecon -Rv /var/log/mysql
Невстановлення правильного контексту SELinux призводить до того, що демон успішно запускається, але мовчки пропускає запис у файл журналу — неприємний граничний випадок, який не генерує жодної очевидної помилки в /var/log/messages.
Крок 4: Перезапустіть службу бази даних
Застосуйте зміни конфігурації, перезапустивши службу. На дистрибутивах на основі systemd (стандарт на будь-якому сучасному Linux-сервері):
# MySQL
sudo systemctl restart mysqld

# MariaDB
sudo systemctl restart mariadb
На старіших системах на основі init.d:
# MySQL
sudo service mysqld restart

# MariaDB
sudo service mariadb restart
Після перезапуску перевірте, що служба запустилася коректно:
sudo systemctl status mysqld    # or mariadb
sudo journalctl -u mysqld -n 50 --no-pager
Будь-яка неправильна конфігурація в my.cnf перешкодить запуску та відобразиться у виводі журналу.
Крок 5: Увімкніть журнал повільних запитів під час виконання (без перезапуску)
Для виробничих серверів, де перезапуск є небажаним, MySQL і MariaDB підтримують динамічне увімкнення журналу повільних запитів через SET GLOBAL. Зміни, внесені таким чином, набувають чинності негайно, але не зберігаються після перезапуску служби, якщо їх також не записано до my.cnf.
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow-query.log';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 1;
SET GLOBAL min_examined_row_limit = 100;
Це правильний підхід для екстреної діагностики на живій системі — увімкніть, зберіть зразок за 15–30 хвилин під час пікового трафіку, потім вимкніть, не торкаючись файлу конфігурації та не перезапускаючи демон.
Крок 6: Перевірте конфігурацію
Підключіться до клієнта MySQL або MariaDB:
mysql -u root -p
Потім виконайте пошук за шаблоном у таблиці системних змінних:
SHOW VARIABLES LIKE '%slow_query%';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'log_queries_not_using_indexes';
Очікуваний вивід для правильно налаштованого екземпляра:
+-------------------------------+-------------------------------+
| Variable_name                 | Value                         |
+-------------------------------+-------------------------------+
| slow_query_log                | ON                            |
| slow_query_log_file           | /var/log/mysql/slow-query.log |
+-------------------------------+-------------------------------+

+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| long_query_time | 1.000 |
+-----------------+-------+

+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | ON    |
+-------------------------------+-------+
Ви також можете підтвердити, що журнал записується, перевіривши лічильник повільних запитів:
SHOW GLOBAL STATUS LIKE 'Slow_queries';
Цей лічильник збільшується щоразу, коли запит перевищує long_query_time, незалежно від того, чи активне журналювання у файл — корисно для підтвердження того, що повільні запити дійсно відбуваються, перш ніж витрачати час на аналіз порожнього файлу журналу.
Крок 7: Читання та інтерпретація необробленого журналу
Використовуйте tail для моніторингу журналу в реальному часі під час навантажувального тестування або вікна пікового трафіку:
sudo tail -f /var/log/mysql/slow-query.log
Типовий запис журналу виглядає так:
# Time: 2024-10-11T12:45:23.489187Z
# User@Host: app_user[app_user] @ 10.0.1.45 []  Id: 1042
# Query_time: 4.561529  Lock_time: 0.000115  Rows_sent: 1  Rows_examined: 847293
# Bytes_sent: 512
SET timestamp=1697030723;
SELECT * FROM orders WHERE customer_email = 'user@example.com' ORDER BY created_at DESC;
Що означає кожне поле:

Query_time — загальний час виконання за астрономічним годинником у секундах
Lock_time — час очікування на отримання блокувань таблиць або рядків; високе співвідношення Lock_time до Query_time вказує на конкуренцію, а не на відсутній індекс
Rows_sent — рядки, повернуті клієнту
Rows_examined — рядки, які рушій зберігання просканував для отримання результату; співвідношення Rows_examined / Rows_sent вище 100:1 є сильним сигналом про відсутній або погано вибірковий індекс
Bytes_sent — присутній у розширеному режимі MariaDB; корисний для виявлення запитів, що повертають надмірно великі набори результатів

У наведеному прикладі запит перевірив 847 293 рядки, щоб повернути 1 рядок. Додавання індексу на customer_email зменшить Rows_examined приблизно до 1, скоротивши час виконання з 4,5 секунди до субмілісекундного.
Крок 8: Аналіз журналу за допомогою mysqldumpslow та pt-query-digest
Читання необробленого файлу журналу є непрактичним у великих масштабах. Два інструменти агрегують і ранжують повільні запити за загальним впливом.
Використання mysqldumpslow (входить до складу MySQL/MariaDB)
# Top 10 queries by total execution time
sudo mysqldumpslow -s t -t 10 /var/log/mysql/slow-query.log

# Top 10 queries by average execution time
sudo mysqldumpslow -s at -t 10 /var/log/mysql/slow-query.log

# Top 10 queries by rows examined
sudo mysqldumpslow -s r -t 10 /var/log/mysql/slow-query.log
mysqldumpslow нормалізує параметри запиту (замінюючи літеральні значення на N або S), щоб структурно ідентичні запити з різними значеннями параметрів групувалися разом — це необхідно для виявлення шаблонів з високою частотою.
Використання pt-query-digest (Percona Toolkit — рекомендовано для виробництва)
# Install Percona Toolkit (Debian/Ubuntu)
sudo apt-get install percona-toolkit

# Install Percona Toolkit (RHEL/CentOS/AlmaLinux)
sudo yum install percona-toolkit

# Generate a full digest report
sudo pt-query-digest /var/log/mysql/slow-query.log

# Show only the top 5 queries by total time
sudo pt-query-digest --limit 5 /var/log/mysql/slow-query.log

# Output to a file for later review
sudo pt-query-digest /var/log/mysql/slow-query.log > /tmp/slow_query_report.txt
pt-query-digest формує ранжований звіт, що показує відбиток кожного запиту, загальний час виконання, середній час, кількість викликів і розподіл за перцентилями. Він значно потужніший за mysqldumpslow і є стандартним інструментом, який використовують професійні DBA для аналізу журналу повільних запитів.
Крок 9: Налаштуйте ротацію журналу за допомогою logrotate
Без ротації журнал повільних запитів зростає безмежно. На завантаженому сервері з long_query_time, встановленим на 1 секунду, файл може досягти кількох гігабайт протягом кількох днів.
Створіть спеціальну конфігурацію logrotate:
sudo nano /etc/logrotate.d/mysql-slow
/var/log/mysql/slow-query.log {
    daily
    rotate 14
    missingok
    notifempty
    compress
    delaycompress
    sharedscripts
    postrotate
        /usr/bin/mysqladmin flush-logs 2>/dev/null || true
    endscript
}
Пояснення ключових директив:

rotate 14 — зберігає 14 днів стиснутих архівів; налаштуйте відповідно до вашого дискового бюджету та вимог аудиту
compress / delaycompress — стискає ротовані файли за допомогою gzip, але затримує стиснення на один цикл, щоб уникнути стиснення файлу, який демон може ще мати відкритим
postrotate — запускає mysqladmin flush-logs після ротації, що сигналізує демону закрити поточний дескриптор файлу журналу та відкрити новий; без цього MySQL/MariaDB продовжує записувати у перейменований файл до наступного перезапуску

Примусово виконайте ручну ротацію для перевірки конфігурації:
sudo logrotate -f /etc/logrotate.d/mysql-slow
Крок 10: Вимкніть журнал повільних запитів, коли він більше не потрібен
Безперервне журналювання повільних запитів із низьким порогом (наприклад, 0,5 секунди) на сервері з високим трафіком додає відчутне навантаження на I/O. Вимкніть його після збору достатніх даних:
Через файл конфігурації (постійно):
[mysqld]
slow_query_log = 0
Потім перезапустіть службу:
sudo systemctl restart mysqld   # or mariadb
Через змінну під час виконання (негайно, непостійно):
SET GLOBAL slow_query_log = 'OFF';
Метод під час виконання є кращим у виробничі години — він набуває чинності за мілісекунди без жодного простою.
Додатково: використання performance_schema як доповнення
Журнал повільних запитів фіксує запити, що перевищують часовий поріг. Таблиця performance_schema events_statements_summary_by_digest фіксує агреговану статистику для кожного окремого шаблону запиту, незалежно від часу виконання. Використання обох разом дає повну картину.
SELECT
    DIGEST_TEXT,
    COUNT_STAR,
    ROUND(SUM_TIMER_WAIT / 1e12, 3)     AS total_time_sec,
    ROUND(AVG_TIMER_WAIT / 1e12, 6)     AS avg_time_sec,
    SUM_ROWS_EXAMINED,
    SUM_ROWS_SENT
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;
Цей запит виявляє 10 найбільш часозатратних шаблонів запитів у всій історії операторів — включаючи швидкі запити, що виконуються мільйони разів і в сукупності домінують за часом CPU, які журнал повільних запитів ніколи б не зафіксував.
Міркування щодо середовища хостингу
Оптимальний поріг long_query_time значною мірою залежить від ролі сервера та профілю ресурсів:

Середовища спільного хостингу — зазвичай немає прямого доступу до my.cnf; використовуйте SET GLOBAL, якщо постачальник хостингу надає привілей SUPER або SYSTEM_VARIABLES_ADMIN, або запитайте доступ до журналу повільних запитів через панель керування
Середовища VPS — повний root-доступ означає повний контроль над усіма параметрами конфігурації; встановлення VPS з cPanel надає доступ до налаштувань журналу повільних запитів через редактор конфігурації MySQL у WHM, який записує безпосередньо до my.cnf

• Виділені сервери — на Виділеному сервері з базою даних з високим трафіком розгляньте встановлення long_query_time настільки низьким, як 0.1 секунди, та використання log_slow_rate_limit (MariaDB) або вибірки на рівні застосунку для контролю обсягу журналу
• Аналітичні навантаження з прискоренням GPU — на вузлах GPU Hosting, що виконують аналітичні запити до великих наборів даних, поріг 5–10 секунд може бути доречним, оскільки тривалі аналітичні запити є очікуваною поведінкою, а не дефектом

Якщо ваш стек застосунків включає веб-інтерфейс, керований через Панель керування VPS, зіставлення часових міток журналу повільних запитів із часовими мітками журналу HTTP-доступу вашого застосунку є ефективним методом відстеження затримок бази даних до конкретних запитів користувачів.

Практична матриця рішень: вибір правильного порогу

Технічний контрольний список та ключові висновки

Перш ніж завершити розслідування повільних запитів, перевірте кожен із наступних пунктів:

• Розділ [mysqld] у my.cnf містить slow_query_log = 1, дійсний шлях slow_query_log_file та long_query_time, відповідний вашому профілю трафіку
• Файл журналу та його батьківський каталог належать системному користувачу mysql з правами на запис; на системах SELinux контекст файлу встановлено як mysqld_log_t

SHOW VARIABLES LIKE '%slow_query%' підтверджує slow_query_log = ON та правильний шлях до файлу після перезапуску служби
SHOW GLOBAL STATUS LIKE 'Slow_queries' показує ненульовий і зростаючий лічильник, підтверджуючи, що відповідні запити дійсно відбуваються
log_queries_not_using_indexes увімкнено та поєднано з min_examined_row_limit для запобігання переповненню журналу тривіальними пошуками одного рядка
log_slow_admin_statements увімкнено для фіксації ALTER TABLE, OPTIMIZE TABLE та подібних DDL-операцій, які є поширеними джерелами несподіваних блокувань таблиць
Конфігурація logrotate налаштована з хуком postrotate, що викликає mysqladmin flush-logs

• Ви запустили pt-query-digest або mysqldumpslow для агрегування журналу та визначили топ 3–5 запитів за загальним часом виконання
• Кожен виявлений запит проаналізовано за допомогою EXPLAIN (або EXPLAIN ANALYZE у MySQL 8.0+) та додано відповідні індекси або реструктуровано логіку запиту
• Журнал повільних запитів вимкнено або long_query_time підвищено після завершення циклу оптимізації для мінімізації поточного навантаження на I/O

FAQ

Чи впливає увімкнення журналу повільних запитів на продуктивність бази даних?

При порозі 1 секунда або вище на типовому виробничому навантаженні накладні витрати є незначними — зазвичай менше 1% від загального часу виконання запитів. Накладні витрати стають відчутними лише тоді, коли long_query_time встановлено нижче 0,1 секунди або коли log_queries_not_using_indexes увімкнено на схемі з багатьма малими неіндексованими таблицями. Використовуйте log_slow_rate_limit (MariaDB) або підвищте min_examined_row_limit для пом’якшення цього.

Чи можна увімкнути журнал повільних запитів без перезапуску MySQL або MariaDB?

Так. Використовуйте SET GLOBAL slow_query_log = 'ON' та SET GLOBAL long_query_time = 1 з будь-якого сеансу клієнта MySQL з привілеєм SUPER або SYSTEM_VARIABLES_ADMIN. Зміна набуває чинності негайно. Запишіть ті самі значення до my.cnf, щоб зробити їх постійними після перезапуску.

У чому різниця між Query_time та Lock_time у журналі повільних запитів?

Query_time — це загальний час за астрономічним годинником від моменту отримання запиту сервером до надсилання останнього рядка клієнту. Lock_time — це частина цього загального часу, витрачена на очікування отримання блокувань таблиць або рядків. Запит із Lock_time, близьким до Query_time, є проблемою конкуренції блокувань, а не проблемою індексу — виправлення передбачає проектування транзакцій або зменшення обсягу блокування, а не додавання індексів.

Чому мій файл журналу повільних запитів порожній, хоча slow_query_log = ON?

Найпоширеніші причини: (1) жоден запит насправді не перевищив long_query_time — перевірте за допомогою SHOW GLOBAL STATUS LIKE 'Slow_queries'; (2) шлях до файлу журналу не існує або користувач mysql не має прав на запис; (3) на системах SELinux контекст файлу неправильний; (4) змінна slow_query_log_file вказує на інший шлях, ніж файл, який ви перевіряєте — підтвердьте за допомогою SHOW VARIABLES LIKE 'slow_query_log_file'.

Як знайти найбільш шкідливий запит у журналі повільних запитів?

Запустіть pt-query-digest та відсортуйте за R/Call (кількість перевірених рядків на виклик) або Response time (загальний накопичений час). Запит на вершині рейтингу Response time споживає найбільше сукупного часу бази даних і повинен бути першою ціллю для аналізу EXPLAIN та оптимізації індексів. Якщо pt-query-digest недоступний, використовуйте mysqldumpslow -s t -t 1 для вилучення єдиного запиту з найвищим загальним часом.