Как да активирате журнала за бавни заявки в MySQL и MariaDB

Журналът за бавни заявки е вградена диагностична функция на MySQL и MariaDB, която записва всеки SQL израз, чието време за изпълнение надвишава конфигурируем праг. Той улавя продължителността на заявката, времето за заключване, прегледаните редове, изпратените редове и пълния SQL текст — предоставяйки на администраторите на бази данни и разработчиците прецизен, базиран на файлове одитен запис на всяка заявка, която влошава производителността на приложението.

Активирането му е едно от действията с най-висока ефективност, които можете да предприемете по време на настройка на производителността на базата данни. За разлика от общите инструменти за наблюдение, журналът за бавни заявки посочва точните изрази, отговорни за латентността, което го прави незаменим за оптимизация на индекси, преструктуриране на заявки и планиране на капацитет на всеки сървър — от единична VPS Хостинг среда до многовъзлов клъстер от специализирани бази данни.

Защо журналът за бавни заявки е важен извън основното наблюдение

Повечето екипи прибягват до 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

Ако вашият стек от приложения включва уеб фронтенд, управляван чрез 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

ЧЗВ

Влияе ли активирането на журнала за бавни заявки върху производителността на базата данни?

При праг от 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 за извличане на единствената заявка с най-високо общо време.