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

Неожиданно медленные запросы — одна из самых распространенных проблем с производительностью MySQL. Запрос, который работает приемлемо в процессе разработки, может давать сбои, когда он перегружен рабочей нагрузкой.

Большие приложения могут запускать сотни уникальных запросов к базе данных каждый раз при попадании в конечную точку. Это затрудняет выделение запросов, вызывающих задержки ответа сервера. Журнал медленных запросов MySQL — это параметр отладки, который может помочь вам выявить подозрительные операторы SQL и стать отправной точкой для ваших расследований.

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

Журнал — это встроенный механизм записи длительных 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 считает запрос «медленным», если его выполнение занимает более 10 секунд. Это ограничение обычно слишком слабое для веб-приложений, ориентированных на пользователя, где ожидаются почти мгновенные ответы. Вы можете изменить ограничение, задав переменную long_query_time:

SET GLOBAL long_query_time=1;

Значение устанавливает минимальную продолжительность медленных запросов. Важно найти баланс, который подходит для вашего собственного приложения. Слишком высокое пороговое значение будет исключать запросы, которые действительно влияют на производительность. И наоборот, очень низкие значения могут привести к тому, что будет захвачено слишком много запросов, что создаст чрезмерно зашумленный журнал.

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

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

Расположение файла конфигурации может варьироваться в зависимости от дистрибутива платформы. Обычно это /etc/mysql/my.cnf или /etc/mysql/mysql.conf.d/mysqld.cnf. Добавьте следующие строки, чтобы воспроизвести настройки, которые были динамически включены выше:

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

Перезапустите MySQL, чтобы применить изменения:

$ sudo service mysql restart

Журнал медленных запросов теперь будет активен при каждом запуске сервера MySQL.

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

Журнал обычно включает только SQL-запросы, исключающие «медленный» порог и отправленные клиентскими приложениями. Это исключает любые медленные административные операции, такие как создание индексов и оптимизация таблиц, а также запросы, которые потенциально могут выполняться медленно в будущем.

Вы можете расширить журнал, включив в него эту информацию, внеся следующие изменения в файл конфигурации:

log_slow_admin_statements=1 — включает административные операторы SQL, такие как ALTER TABLE, CREATE INDEX, УДАЛИТЬ ИНДЕКС и ОПТИМИЗИРОВАТЬ ТАБЛИЦУ. Это редко бывает желательным, поскольку эти операции обычно выполняются во время сценариев обслуживания и миграции. Тем не менее этот параметр может быть полезен, если ваше приложение также динамически выполняет эти задачи.
log_slow_replica_statements=1 — этот параметр включает ведение журнала медленных запросов для реплицированных запросов на серверах-репликах. Это отключено по умолчанию. Вместо этого используйте log_slow_slave_statements для версий MySQL 8.0.26 и старше.
log_queries_not_using_indexes=1 — если этот параметр включен, запросы, которые должны получить все записи из целевой таблицы или представления, будут регистрироваться, даже если они этого не сделали. Не исключайте порог медленного запроса. Это может помочь определить, когда в запросе отсутствует индекс или он не может его использовать. Запросы, для которых доступен индекс, по-прежнему будут регистрироваться, если в них отсутствуют ограничения, ограничивающие количество извлекаемых строк.

Регистрация запросов, не использующих индексы, может значительно повысить уровень детализации. Могут быть ситуации, в которых ожидается или необходимо полное сканирование индекса. Эти запросы будут продолжать отображаться в журнале, даже если они не могут быть разрешены.

Вы можете ограничить количество запросов без индексов, задав переменную log_throttle_queries_not_using_indexes. Это определяет максимальное количество журналов, которые будут записаны за 60-секундный период. Значение 10 означает, что в минуту будет записываться до 10 запросов. После десятого события запросы больше не будут регистрироваться, пока не откроется следующее 60-секундное окно.

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

Каждый запрос, попадающий в журнал медленных запросов, будет отображать набор строк, похожих на следующие:

# 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 ...

Строки с комментариями над запросом содержат время его выполнения, пользователя MySQL, под которым подключился клиент, а также статистику, показывающую продолжительность и количество отправленных строк. Приведенный выше пример занял 3,5 секунды и рассмотрел более 320 000 строк, прежде чем отправить клиенту только 5 143 строки. Это может быть признаком того, что отсутствующие индексы заставляют MySQL проверять слишком много записей.

При желании вы можете включить дополнительную информацию в журнал, установив системную переменную log_slow_extra=1 в файле конфигурации. Это добавит идентификатор потока, количество полученных и отправленных байтов и количество строк, рассматриваемых для сортировки, а также счетчики запросов для конкретных операторов, которые обеспечивают представление о том, как MySQL обработал запрос.

С файлом журнала следует обращаться осторожно, так как его содержимое может быть конфиденциальным. Запросы отображаются полностью, без маскирования значений параметров. Это означает, что пользовательские данные будут присутствовать, если вы используете журнал медленных запросов на рабочем сервере. Доступ должен быть ограничен разработчиками и администраторами баз данных, которые настраивают операторы SQL.

Регистрация медленных запросов и резервное копирование

Одна из распространенных проблем с медленным журналом запросов возникает, когда вы также используете MySQLDump для создания резервных копий базы данных. Будут выполняться длительные запросы SELECT * FROM ... для извлечения данных из ваших таблиц и передачи их в резервную копию. Они будут включены в журнал медленных запросов, как и любой другой оператор SQL. Это может загрязнить журнал, если вы регулярно делаете резервные копии.

Вы можете решить эту проблему, временно отключив журнал медленных запросов перед запуском mysqldump. Вы можете повторно активировать журнал после завершения резервного копирования. Настройте сценарий резервного копирования, чтобы он выглядел примерно так:

#!/bin/bash

# Temporarily disable slow query logging
mysql -uUser -pPassword -e "SET GLOBAL slow_query_log=0";

# Run mysqldump
mysqldump -uUser -pPassword --single-transaction databaseName | gzip > backup.bak

# Enable the slow query log again
mysql -uUser -pPassword -e "SET GLOBAL slow_query_log=1"

Это предотвратит активность MySQLDump в журнале медленных запросов, что упростит концентрацию на SQL, выполняемом вашим приложением.

Краткое содержание

Журнал медленных запросов MySQL — один из наиболее эффективных способов точно определить причину проблем с производительностью. Начните с оценки задержки, с которой вы столкнулись, и используйте это значение в качестве long_query_time. Уменьшите значение, если после воспроизведения проблемы в журнале ничего не появляется.

Журнал медленных запросов не скажет вам, как именно исправить замедление. Однако возможность просмотра точного SQL, полученного сервером, позволяет повторять неэффективные операторы, а затем оценивать эффект оптимизации. Добавление индекса или отсутствующее ограничение может быть разницей между запросом, который затрагивает тысячи строк, и запросом, который работает с горсткой строк.