Журнал медленных запросов MySQL: настройка и анализ

Логотип MySQL

Быстрые ссылки

Включение журнала медленных запросов
Использование конфигурационного файла MySQL
Настройка содержимого журнала
Интерпретация журнала медленных запросов
Журнал медленных запросов и бэкапы
Дополнительные инструменты и методика расследования
Рекомендации по безопасности и приватности
Краткое резюме

Неожиданно медленные запросы — одна из самых частых причин проблем с производительностью MySQL. Запрос, который адекватно работает в разработке, может замедлиться в продакшене под нагрузкой. Крупное приложение может выполнять сотни уникальных SQL-запросов при обработке одного HTTP-эндпойнта, что затрудняет поиск тех, которые реально тормозят ответ сервера.

Журнал медленных запросов (slow query log) — встроенный инструмент для фиксации «долгоиграющих» SQL-операций. Он предоставляет точный список SQL-запросов и метрик времени, с которых удобно начинать расследование причин замедления.

Включение журнала медленных запросов

Журнал отключён по умолчанию. Для однократного включения (выполняется немедленно без перезагрузки сервера) запустите команды из административного MySQL-терминала:

SET GLOBAL slow_query_log_file='/var/log/mysql/mysql-slow.log';
SET GLOBAL slow_query_log=1;

После этого медленные запросы будут записываться в

/var/log/mysql/mysql-slow.log

MySQL считает запрос «медленным», если время выполнения превышает значение переменной long_query_time. По умолчанию это 10 секунд — слишком долго для большинства пользовательских веб-приложений. Измените порог так:

SET GLOBAL long_query_time=1;

Значение long_query_time задаёт минимальную длительность, при которой запрос будет считаться медленным. Подходите к выбору порога с практической точки зрения: слишком высокий порог так и не уловит запросы, влияющие на UX; слишком низкий — создаст «шумный» лог.

Советы по настройке порога:

Начните с времени, близкого к реально замечаемой задержке (например, 1–2 с для веб-интерфейсов).
Воспроизведите нагрузку (стейдж/копия продакшена), затем уменьшайте порог, пока в логе не появятся управляемые наборы запросов.
Для аналитических или фоновых задач можно использовать более высокий порог.

Использование конфигурационного файла MySQL

Если вы планируете постоянно собирать slow query лог, включите его в конфиг-файле, чтобы он включался автоматически после перезапуска сервера.

Файлы конфигурации чаще всего находятся по путям:

/etc/mysql/my.cnf

или

/etc/mysql/mysql.conf.d/mysqld.cnf

Добавьте или отредактируйте следующие строки в секции [mysqld]:

slow_query_log=1
slow_query_log_file=/var/log/mysql/mysql-slow.log
long_query_time=1

После сохранения перезапустите службу:

$ sudo service mysql restart

Теперь журнал будет активен при старте сервера.

Важно: в системах, где MySQL запускается под управлением systemd, убедитесь, что у процесса есть права записи в указанный файл и что ротация логов настроена (logrotate), иначе файл может быстро раздуваться.

Настройка содержимого журнала

По умолчанию в лог попадают клиентские SQL-запросы, которые превышают порог. Административные операции (например, создание индексов, OPTIMIZE TABLE) и реплицированные запросы часто исключены — их можно добавить при необходимости:

log_slow_admin_statements = 1 — включает административные запросы (ALTER TABLE, CREATE INDEX, OPTIMIZE TABLE). Полезно, если приложение динамически меняет структуру БД, но обычно приводит к «шуму», поскольку такие операции выполняются в миграциях/маинтенансе.
log_slow_replica_statements = 1 — включает логирование медленных запросов на реплике. В старых версиях MySQL встречалось имя log_slow_slave_statements.
log_queries_not_using_indexes = 1 — логирует запросы, которые не используют индексы и поэтому, как правило, делают полный просмотр таблицы. Это помогает обнаружить отсутствующие индексы, но может сильно увеличить объём лога.

Чтобы ограничить «шум» от запросов без индексов, используйте:

log_throttle_queries_not_using_indexes=10

Это ограничит число записей такого рода до 10 в минуту (максимум 10 событий в 60-секундном окне). Значение настраивается под нагрузку вашей системы.

Примечание: для некоторых задач полный просмотр таблицы (full table scan) ожидаем и допустим — лог будет постоянно наполняться такими записями, даже если они нормальны с архитектурной точки зрения.

Интерпретация журнала медленных запросов

Запись в лог обычно содержит набор строк с метаданными, затем сам SQL. Типичный пример:

# Time: 2022-07-12T19:00:00.000000Z
# User@Host: demo[demo] @ mysql [] Id: 51
# Query_time: 3.514223 Lock_time: 0.000010 Rows_sent: 5143 Rows_examined: 322216
SELECT * FROM slow_table LEFT JOIN another_table ...

Комментарий с Query_time показывает длительность выполнения (в секундах), Rows_examined — сколько строк было просмотрено, Rows_sent — сколько строк отправлено клиенту. В приведённом примере запрос занял ~3.5 с и просмотрел ~322 тысячи строк, в то время как вернул только 5 143 строки — это часто признак отсутствия подходящего индекса.

Дополнительные параметры: включив log_slow_extra = 1, вы получите больше данных — идентификатор потока (thread id), байты отправленные/полученные, число строк, использованных при сортировке и дополнительные счётчики для операций. Эти данные помогают понять, где именно происходят задержки (I/O, сортировка, блокировки).

Критерии приёмки журналов и что смотреть в первую очередь:

Сортировать записи по Query_time и Rows_examined чтобы найти «дорогие» запросы.
Обращать внимание на запросы с большим расхождением между Rows_examined и Rows_sent — повод проверить индексирование и условия WHERE.
Проверять частоту повторяемых запросов: редкий тяжёлый запрос может быть менее критичен, чем краткий, но часто встречающийся.

Important: журнал содержит полные SQL-запросы с подставленными значениями параметров — это может быть чувствительная пользовательская информация. Ограничьте доступ к файлу журнала и учитывайте требования по конфиденциальности.

Журнал медленных запросов и бэкапы

Другая распространённая проблема — путаница между медленными запросами приложения и запросами инструментов бэкапа, таких как mysqldump. Дамп выполняет длительные SELECT-операции, которые будут попадать в slow log и «засорять» его.

Простой подход — временно отключать лог перед созданием дампа и включать его обратно после:

#!/bin/bash

# Временно отключить журнал медленных запросов
mysql -uUser -pPassword -e "SET GLOBAL slow_query_log=0";

# Сделать бэкап
mysqldump -uUser -pPassword --single-transaction databaseName | gzip > backup.bak

# Включить журнал снова
mysql -uUser -pPassword -e "SET GLOBAL slow_query_log=1"

Альтернативы и замечания:

Лучше запускать регулярные бэкапы на реплике/резервной ноде, чтобы не мешать основному серверу и не смешивать логи производительности приложения с логами операций бэкапа.
Если вы используете централизованную систему логирования, можно настроить фильтрацию по пользователю/хосту, чтобы исключать записи от бэкап-скрипта.

Когда журнал медленных запросов не поможет

Когда узкая часть — не в SQL, а в сети, драйверах или в приложении (неправильная логика кэширования, блокировки в коде). Лог покажет только SQL и связанные метрики, но не причину в приложении.
Короткие, но очень частые запросы: если они очень быстрые по отдельности, но совокупно создают нагрузку, они могут не попасть в журнал, если порог long_query_time настроен слишком высоко.
Задачи, связанные с I/O или конфигурацией сервера (напр., медленный диск), — лог укажет увеличенное время, но не подскажет оптимальную настройку ОС/дисковой подсистемы.

В таких случаях комбинируйте slow query лог с профильными инструментами (performance_schema, системным мониторингом, трассировками приложений).

Альтернативные инструменты и интеграция

EXPLAIN и EXPLAIN ANALYZE — даёт план выполнения запроса и показывает, какие индексы используются.
performance_schema — внутренний профиль MySQL, даёт подробные метрики по времени исполнения, блокировкам и I/O.
pt-query-digest (Percona Toolkit) — анализатор slow log, который агрегирует записи, группирует похожие запросы и выдаёт отчёт с приоритетами оптимизации.
Мониторинговые решения (Prometheus + Grafana, MySQL Enterprise Monitor) — для долгосрочного наблюдения метрик и оповещений.

Комбинация: используйте slow query log как источник «сырых» запросов, pt-query-digest для агрегации и ранжирования, а EXPLAIN/ANALYZE для проверки конкретных проблемных запросов.

Методика расследования (мини-методология)

Воспроизведите проблему в контролируемой среде или включите лог в продакшене с адекватным порогом.
Соберите и проанализируйте лог — агрегируйте похожие запросы и отсортируйте по суммарному времени.
Для каждого приоритетного запроса выполните EXPLAIN/EXPLAIN ANALYZE и изучите план.
Ищите полные просмотры таблиц, файлы сортировок на диске (filesort), временные таблицы и отсутствие индексов по условиям WHERE/JOIN.
Предложите изменения: добавить индекс, изменить JOIN или условия, ограничить выборку, добавить пагинацию, внедрить кэш.
Протестируйте изменения на реплике/стейдже, измерьте улучшение (время, Rows_examined).
Разверните в продакшне с откатом и мониторингом.

Чек-листы по ролям

DBA:

Включить slow query лог в конфиге с ротацией логов.
Настроить права на файл и ограничить доступ.
Автоматизировать архивацию и анализ (pt-query-digest).

Разработчик:

Использовать EXPLAIN и профилировщик при добавлении новых запросов.
Проверять различие между Rows_examined и Rows_sent.
Внедрять пагинацию и лимиты для пользовательских запросов.

SRE/инженер по эксплуатации:

Настроить мониторинг метрик MySQL и оповещения (высокая нагрузка, рост задержек).
Убедиться, что бэкапы не засоряют slow log или выполняются на реплике.

План отката и инцидентный сценарий

Если исправление ухудшило ситуацию или привело к проблемам:

Откатить изменения схемы/индекса (если применимо) по заранее подготовленному скрипту.
Вернуть предыдущую версию запроса/приложения через CI/CD.
Временно повысить long_query_time, чтобы уменьшить нагрузку на диск/CPU и дать время на диагностику.
Проанализировать recent slow log и метрики мониторинга для поиска новых симптомов.
Документировать причинно-следственные связи и обновить playbook.

Безопасность и приватность

Журнал содержит полнотекстовые SQL-запросы со значениями параметров — это часто персональные данные или конфиденциальные значения. Рекомендации:

Ограничьте доступ к файлу журнала и его резервным копиям только для администраторов и доверенных разработчиков.
Используйте ротацию логов и шифрование архива (при длительном хранении).
Если в логе попадают персональные данные, согласуйте хранение и доступ с требованиями законодательства (GDPR, локальные правила), применяйте политики минимизации данных.
При возможности диагностируйте и репродуцируйте проблемы на обезличенных копиях данных.

Факт-бокс

Значение long_query_time по умолчанию: 10 секунд.
По умолчанию slow_query_log выключен.
log_throttle_queries_not_using_indexes ограничивает число записей запросов без индексов за 60 сек.

Краткий глоссарий

long_query_time — порог в секундах, дольше которого запрос считается медленным.
Rows_examined — число строк, просмотренных исполнителем запроса.
Rows_sent — число строк, отправленных клиенту.
EXPLAIN — команда для получения плана выполнения запроса.

Дополнительные рекомендации и хорошие практики

Используйте pt-query-digest для периодических отчётов и чтобы упростить приоритизацию оптимизаций.
Работайте с репликами: нагрузочные операции и бэкапы лучше выполнять на вторичных нодах.
Держите порог long_query_time достаточно низким для пользовательских операций и выше — для фоновых.
Включайте только те дополнительные лог-опции (log_queries_not_using_indexes, log_slow_admin_statements), которые приносят ценность — чтобы не перегружать аналитику.

Резюме

Журнал медленных запросов MySQL — очень полезный первый шаг при поиске проблем производительности: он показывает точный SQL, время выполнения и простые метрики, помогающие найти потенциально неиндексированные или неоптимальные запросы. Включайте его в конфиг для постоянного сбора, подбирайте порог long_query_time под характер приложения, комбинируйте с EXPLAIN, performance_schema и инструментами анализа (pt-query-digest). Не забывайте о безопасности логов и о том, что лог сам по себе не подскажет все ответы — он даёт отправную точку для систематического улучшения.