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

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

Скорее всего, виною этому служат неоптимизированные запросы. Чтобы их увидеть, достаточно включить лог медленных запросов MySQL.

Чтобы включить лог медленных запросов, достаточно открыть конфигурационный файл MySQL my.cnf:

sudo nano /etc/mysql/my.cnf

и раскомменитровать/добавить там строки:

slow-query-log      = 1
slow-query-log-file = /var/log/mysql/mysql-slow.log
long_query_time     = 3
log-queries-not-using-indexes

• в опции slow_query_log указывать путь к файлу, в котором будет храниться лог запросов
• в опции long_query_time указывать минимальное время выполнения запроса, после которого он считается медленным в секундах

Далее необходимо перезапустить MySQL-сервер:

/etc/init.d/mysql restart

Теперь в файл /var/log/mysql/mysql-slow.log начнут записываться медленные запросы, которые можно наблюдать в режиме реального времени при помощи утилиты tail:

tail -f /var/log/mysql/mysql-slow.log

Ротация логов

Со временем лог медленных запросов увеличиться и забьет собой всё свободное пространство.

Чтобы этого не произошло, необходимо cделать ежесуточную ротацию при помощи утилиты logrotate, которая сгруппирует логи по времени и сожмёт их архиватором, что значительно уменьшит конечный размер файлов журнала.

Для настройки logrotate добавьте в файл конфигурации /etc/logrotate.d/mysql-server следующий код:

/var/log/mysql/mysql-slow.log {
	daily
	rotate 7
	missingok
	create 640 mysql adm
	compress
	sharedscripts
	postrotate
	test -x /usr/bin/mysqladmin || exit 0
	# If this fails, check debian.conf!
	MYADMIN="/usr/bin/mysqladmin --defaults-file=/etc/mysql/debian.cnf"
	if [ -z "`$MYADMIN ping 2>/dev/null`" ]; then
		# Really no mysqld or rather a missing debian-sys-maint user?
		# If this occurs and is not an error please report a bug.
		#if ps cax | grep -q mysqld; then
		if killall -q -s0 -umysql mysqld; then
			exit 1
		fi
	else
		$MYADMIN flush-logs
	fi
endscript
}

Анализ логов

Проанализировать полученные логи можно при помощи утилиты pt-query-digest из пакета percona-toolkit.

Устанавливаем пакет percona-toolkit:

sudo apt-get install percona-toolkit

После накопления достаточного для анализа количества запросов, можно использовать утилиту pt-query-digest:

pt-query-digest /var/log/mysql/mysql-slow.log

Можно также результат анализа сохранить в файл для последующего разбора полётов:

pt-query-digest /var/log/mysql/mysql-slow.log > slowlog.txt