Как использовать профилирование запросов MySQL

Введение

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

Что такое журнал медленных запросов MySQL?

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

Настройка переменных профилирования

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

slow_query_log			G 
slow_query_log_file			G 
long_query_time			G / S
log_queries_not_using_indexes	G
min_examined_row_limit		G / S

ПРИМЕЧАНИЕ. (G) глобальная переменная, (S) переменная сеанса

slow_query_log — логическое значение для включения и выключения журнала медленных запросов.

slow_query_log_file — абсолютный путь к файлу журнала запросов. Каталог файла должен принадлежать пользователю mysqld и иметь правильные разрешения для чтения и записи. Демон mysql, скорее всего, будет работать как \\mysql\\, но для проверки выполните в терминале Linux следующее:

 ps -ef | grep bin/mysqld | cut -d' ' -f1

Вывод, скорее всего, будет отображать текущего пользователя, а также пользователя mysqld. Пример установки пути к каталогу /var/log/mysql:

cd /var/log
mkdir mysql
chmod 755 mysql
chown mysql:mysql mysql

long_query_time — время в секундах для проверки длины запроса. При значении 5 любой запрос, выполнение которого занимает более 5 секунд, будет регистрироваться.

log_queries_not_using_indexes — логическое значение, следует ли регистрировать запросы, не попадающие в индексы. При анализе запросов важно регистрировать запросы, которые не обращаются к индексам.

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

Переменные сервера MySQL могут быть установлены в файле конфигурации MySQL или динамически через графический интерфейс MySQL или командную строку MySQL. Если переменные установлены в файле conf, они будут сохранены при перезапуске сервера, но также потребуют перезапуска сервера, чтобы стать активными. Файл конфигурации MySQL обычно находится в \\/etc или /usr\\, обычно \\/etc/my.cnf\\ или \\/etc/mysql/my.cnf\\. Чтобы найти файл conf (возможно, придется расширить поиск до большего количества корневых каталогов):

find /etc -name my.cnf
find /usr -name my.cnf

Как только файл conf будет найден, просто добавьте нужные значения под заголовком [mysqld]:

[mysqld]
….
slow-query-log = 1
slow-query-log-file = /var/log/mysql/localhost-slow.log
long_query_time = 1
log-queries-not-using-indexes

Опять же, изменения вступят в силу только после перезапуска сервера, поэтому, если изменения необходимы немедленно, задайте переменные динамически:

mysql> SET GLOBAL slow_query_log = 'ON';
mysql> SET GLOBAL slow_query_log_file = '/var/log/mysql/localhost-slow.log';
mysql> SET GLOBAL log_queries_not_using_indexes = 'ON';
mysql> SET SESSION long_query_time = 1;
mysql> SET SESSION min_examined_row_limit = 100;

Чтобы проверить значения переменных:

mysql> SHOW GLOBAL VARIABLES LIKE 'slow_query_log';
mysql> SHOW SESSION VARIABLES LIKE 'long_query_time';

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

ПРИМЕЧАНИЕ. Синтаксис для динамической установки переменных через SET и помещения их в файл conf немного отличается, например. \\slow_query_log\\ против \\slow-query-log\\. Просмотрите страницу динамических системных переменных MySQL для различных синтаксисов. Формат Option-File — это формат файла conf, а System Variable Name — это имя переменной для динамической установки переменных.

Генерация данных профиля запроса

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

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

$> mysql -u  -p

mysql> CREATE DATABASE profile_sampling;
mysql> USE profile_sampling;
mysql> CREATE TABLE users ( id TINYINT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(255) );
mysql> INSERT INTO users (name) VALUES ('Walter'),('Skyler'),('Jesse'),('Hank'),('Walter Jr.'),('Marie'),('Saul'),('Gustavo'),('Hector'),('Mike');

mysql> SET GLOBAL slow_query_log = 1;
mysql> SET GLOBAL slow_query_log_file = '/var/log/mysql/localhost-slow.log';
mysql> SET GLOBAL log_queries_not_using_indexes = 1;
mysql> SET long_query_time = 10;
mysql> SET min_examined_row_limit = 0;

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

cd /var/log/mysql
ls -l

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

mysql> USE profile_sampling;
mysql> SELECT * FROM users WHERE id = 1;

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

mysql> SELECT * FROM users WHERE name = 'Jesse';

Этот запрос выполнялся для неиндексированного столбца — имя. В этот момент в журнале будет запрос со следующей информацией (может быть не совсем такой):

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

# Time: 140322 13:54:58
# User@Host: root[root] @ localhost []
# Query_time: 0.000303  Lock_time: 0.000090 Rows_sent: 1  Rows_examined: 10
use profile_sampling;
SET timestamp=1395521698;
SELECT * FROM users WHERE name = 'Jesse';

Запрос был успешно зарегистрирован. Еще один пример. Увеличьте минимальное количество проверяемых строк и выполните аналогичный запрос:

mysql> SET min_examined_row_limit = 100;
mysql> SELECT * FROM users WHERE name = 'Walter';

Данные не будут добавлены в журнал, так как минимум 100 строк не были проанализированы.

ПРИМЕЧАНИЕ. Если в журнал не заносятся данные, можно проверить несколько вещей. Во-первых, разрешения каталога, в котором создается журнал. Владелец/группа должны быть такими же, как у пользователя mysqld (см. выше, например), а также иметь правильные разрешения, chmod 755, чтобы быть уверенным. Во-вторых, возможно, существуют существующие конфигурации переменных медленного запроса, которые мешают этому примеру. Сбросьте значения по умолчанию, удалив все медленные переменные запроса из файла conf и перезапустив сервер, или динамически верните глобальные переменные к их значениям по умолчанию. Если изменения вносятся динамически, выйдите из системы и снова войдите в MySQL, чтобы глобальные обновления вступили в силу.

Анализ информации профиля запроса

Глядя на данные профиля запроса из приведенного выше примера:

# Time: 140322 13:54:58
# User@Host: root[root] @ localhost []
# Query_time: 0.000303  Lock_time: 0.000090 Rows_sent: 1  Rows_examined: 10
use profile_sampling;
SET timestamp=1395521698;
SELECT * FROM users WHERE name = 'Jesse';

В записи отображается:

Время выполнения запроса
Кто управлял
Сколько времени занял запрос
Длина замка
Сколько строк было возвращено
Сколько строк было проверено

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

Использование mysqldumpslow

В более реалистичном примере профилирование будет включено в приложении, управляемом базой данных, обеспечивая умеренный поток данных для профилирования. Журнал будет постоянно записываться, вероятно, чаще, чем кто-либо будет смотреть. По мере роста размера журнала становится сложно анализировать все данные, а проблемные запросы легко теряются в журнале. MySQL предлагает другой инструмент, mysqldumpslow, который помогает избежать этой проблемы, разбивая журнал медленных запросов. Бинарный файл связан с MySQL (в Linux), поэтому для его использования просто запустите команду и укажите путь к журналу:

mysqldumpslow -t 5 -s at /var/log/mysql/localhost-slow.log

Существуют различные параметры, которые можно использовать с командой для настройки вывода. В приведенном выше примере будут отображаться 5 лучших запросов, отсортированных по среднему времени запроса. Результирующие строки более удобочитаемы, а также сгруппированы по запросам (этот вывод отличается от примера тем, что демонстрирует высокие значения):

Count: 2  Time=68.34s (136s)  Lock=0.00s (0s)  Rows=39892974.5 (79785949), root[root]@localhost
  SELECT PL.pl_title, P.page_title
  FROM page P
  INNER JOIN pagelinks PL
  ON PL.pl_namespace = P.page_namespace
  WHERE P.page_namespace = N
…

Отображаемые данные:

Подсчет — сколько раз запрос был зарегистрирован.
Время – как среднее, так и общее время в()
Блокировка – время блокировки таблицы.
Строки — число возвращенных строк.

Команда абстрагирует числа и строки, поэтому одни и те же запросы с разными предложениями WHERE будут считаться одним запросом (обратите внимание на page_namespace=N). Наличие такого инструмента, как mysqldumpslow, избавляет от необходимости постоянно следить за журналом медленных запросов, вместо этого позволяя выполнять периодические или автоматические проверки. Параметры команды mysqldumpslow допускают сопоставление некоторых сложных выражений, что помогает детализировать различные запросы в журнале.

Также доступны сторонние инструменты анализа журналов, которые предлагают различные представления данных, популярным из которых является pt-query-digest.

Разбивка запроса

Последний инструмент профилирования, о котором следует знать, — это инструмент, который позволяет выполнять сложную разбивку запроса. Хорошим примером использования этого инструмента является захват проблемного запроса из журнала медленных запросов и запуск его непосредственно в MySQL. Сначала должно быть включено профилирование, затем выполняется запрос:

mysql> SET SESSION profiling = 1;
mysql> USE profile_sampling;
mysql> SELECT * FROM users WHERE name = 'Jesse';
mysql> SHOW PROFILES;

После включения профилирования SHOW PROFILES покажет таблицу, связывающую Query_ID с оператором SQL. Найдите Query_ID, соответствующий выполненному запросу, и выполните следующий запрос (замените # на свой Query_ID):

mysql> SELECT * FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID=#;

Пример вывода:

SEQ	STATE	DURATION
1	starting	0.000046
2	checking permissions	0.000005
3	opening tables	0.000036
...	...	...

СОСТОЯНИЕ — это «шаг» в процессе выполнения запроса, а ПРОДОЛЖИТЕЛЬНОСТЬ — это продолжительность выполнения этого шага в секундах. Это не слишком полезный инструмент, но он интересен и может помочь определить, какая часть выполнения запроса вызывает наибольшую задержку.

Подробное описание различных столбцов:

Подробный обзор различных «шагов»:

ПРИМЕЧАНИЕ. Этот инструмент НЕ следует использовать в рабочей среде, а только для анализа определенных запросов.

Низкая производительность журнала запросов

Последний вопрос, который необходимо решить, — как журнал медленных запросов повлияет на производительность. Как правило, безопасно запускать журнал медленных запросов в производственной среде; ни ЦП, ни нагрузка ввода-вывода не должны вызывать беспокойства ². Тем не менее, должна быть некоторая стратегия для мониторинга размера журнала, чтобы гарантировать, что размер файла журнала не становится слишком большим для файловой системы. Кроме того, хорошее эмпирическое правило при запуске журнала медленных запросов в производственной среде — оставлять значение long_query_time равным 1 с или выше.

ВАЖНО. Не рекомендуется использовать инструмент профилирования SET profiling=1, а также регистрировать все запросы, т. е. переменную general_log, в производственной среде с высокой рабочей нагрузкой.

Заключение

Журнал медленных запросов чрезвычайно полезен для выявления проблемных запросов и профилирования общей производительности запросов. При профилировании запросов с помощью журнала медленных запросов разработчик может получить более глубокое представление о том, как выполняются запросы приложений MySQL. Используя такой инструмент, как mysqldumpslow, мониторинг и оценка журнала медленных запросов становятся управляемыми и могут быть легко включены в процесс разработки. Теперь, когда проблемные запросы определены, следующим шагом будет настройка запросов для достижения максимальной производительности.