Включение журнала для показа медленных запросов в MySQL / MariaDB
Обновлено:
Опубликовано:
Тематические термины: MySQL, MariaDB.
Данный журнал помогает найти узкие места в работе СУБД MySQL или MariaDB и повысить ее производительность. Инструкция написана на примере Linux CentOS и будет немного отличаться в зависимости от используемой операционной системы, но принцип остается такой же.
Настройка СУБД для хранения медленных запросов
SQL-запросы для включения логирования длительных запросов
Как смотреть лог
Логирование всех запросов
Возможные проблемы
Включение лога длительных запросов
Для включения возможности показа медленных запросов открываем один из файлов конфигурации сервера баз данных:
vi /etc/my.cnf
vi /etc/mysql/mariadb.conf.d/50-server.cnf
vi /etc/my.cnf.d/server.cnf
* файл my.cnf может находиться в каталоге /etc, как в данном примере или в каталоге с базами (его можно посмотреть командой select @@datadir в командной оболочке СУБД).
Добавляем в открытый файл (секцию mysqld) две строчки.
MySQL (5.6 и позднее) или MariaDB:
[mysqld]
slow_query_log = 1
long_query_time = 5
slow_query_log_file = /var/log/mysql/mysql-slow.log
log-queries-not-using-indexes = 1
* где slow_query_log включает лог; long_query_time указывает, как долго должен выполняться запрос, чтобы считаться долгим; slow_query_log_file — путь до файла, в который будет записываться лог; log-queries-not-using-indexes — учитывать только те запросы, которые выполняют запрос без индекса.
MySQL (версия < 5.6):
[mysqld]
long_query_time = 30
log-slow-queries = /var/log/mysql/mysql-slow.log
* при такой настройке SQL-запросы, которые по длительности превысят 30 секунд попадут в файл /var/log/mysql/mysql-slow.log.
Создаем каталог для журнала:
mkdir /var/log/mysql
Создаем файл лога:
touch /var/log/mysql/mysql-slow.log
Задаем владельца mysql для созданного файла:
chown mysql:mysql /var/log/mysql/mysql-slow.log
И перезагружаем сервер MySQL:
systemctl restart mysqld || systemctl restart mariadb
Включение лога без перезагрузки сервера
Создаем каталог для лога, сам лог и зададим владельца:
mkdir /var/log/mysql
touch /var/log/mysql/mysql-slow.log
chown mysql:mysql /var/log/mysql/mysql-slow.log
Открываем командную оболочку mysql:
mysql
Если консоль выдаст ошибку аутентификации, то подключаемся с вводом пароля:
mysql -uroot -p
Выполняем последовательно команды:
> SET GLOBAL slow_query_log = 'ON';
> SET GLOBAL long_query_time = 2;
> SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';
> FLUSH LOGS;
* где:
- slow_query_log — включает или выключает лог медленных запросов;
- long_query_time — задает время, при превышении которого запрос будет считаться медленным;
- slow_query_log_file — путь до файла, в который будут записываться все медленные запросы.
Просмотр примененных параметров (выполняется в командной оболочке mysql):
> SHOW VARIABLES LIKE '%slow%';
Для выхода вводим:
> quit
Переходим к просмотру лога.
Просмотр лога
Есть несколько способов наблюдать за логом.
Использование tail
Данный метод универсальный и подойдет для любой системы и СУБД. Для просмотра запросов, открываем наш файл в режиме непрерывного наблюдения:
tail -f /var/log/mysql/mysql-slow.log
И ждем появления медленных SQL-запросов.
С помощью mysqldumpslow
Более удобный формат для просмотра логов — данная утилита показывает медленные запросы с количеством встречаемости. Но команда не будет работать для Percona Server.
Вводим:
mysqldumpslow /var/log/mysql/mysql-slow.log
Percona Toolkit
В соответствии с названием, данный метод нужно использовать для Percona. Утилита, также как mysqldumpslow, показывает лог в более удобном формате.
По умолчанию, набор утилит не установлены. Делаем это.
а) Для систем на базе DEB (Debian / Ubuntu / Astra Linux):
apt update
apt install percona-toolkit
б) Для систем на базе RPM (Rocky / РЕД ОС):
yum install percona-toolkit
Percona Toolkit установлен.
Теперь лог можно посмотреть командой:
pt-query-digest /var/log/mysql/mysql-slow.log
Включение аудита всех запросов
Рассмотрим дополнительно ведение лога всех запросов. Это может оказаться полезным, если медленные запросы ничего не показывают, а мы хотим разобраться, какие запросы утилизируют ресурсы. Или просто хотим вести журнал всех запросов.
Открываем командную оболочку mysql:
mysql
Если консоль выдаст ошибку аутентификации, то подключаемся с вводом пароля:
mysql -uroot -p
Выполняем последовательно команды:
> SET GLOBAL general_log = 'ON';
> SET GLOBAL log_output = 'file';
> SET GLOBAL general_log_file = '/var/log/mysql/query.log';
Готово. Теперь можно включить непрерывный просмотр лога:
tail -f /var/log/mysql/query.log
Для отключения логирования в консоли SQL вводим:
> SET GLOBAL general_log = 'OFF';
Возможные ошибки
ERROR 7033 (HY000)
При включении логирования медленных запросов мы можем получить ошибку:
ERROR 7033 (HY000): The --secure-file-path is configured, --slow-query-log-file must be set accordingly.
Причина: на сервере включен режим безопасности secure-file-priv Из-за этого MySQL запрещает сохранять файлы (включая логи) в случайные папки.
Решение: путь до файла лога должен соответствовать значению директивы secure_file_priv. Смотрим ее:
> SHOW VARIABLES LIKE 'secure_file_priv';
Мы увидим что-то на подобие:
+------------------+-----------------------+
| Variable_name | Value |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
Используя путь, задаем новое значение для slow_query_log_file:
SET GLOBAL slow_query_log_file = '/var/lib/mysql-files/slow.log';
После можно включать и лог:
SET GLOBAL slow_query_log = 'ON';
