Включение журнала для показа медленных запросов в 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 slow_launch_time = 2;
> SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';
> FLUSH LOGS;
* где:
- slow_query_log — включает или выключает лог медленных запросов;
- slow_launch_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';
