Профилирование запросов MySQL
Профилирование запросов MySQL – это полезный метод анализа общей производительности приложений на основе БД. При разработке средних и больших приложений, как правило, сотни запросов распределяются по большой базе кода, и БД обрабатывает множество запросов в секунду. Без профилирования запросов становится очень трудно определить нахождение и причины возникновения узких мест приложений. В этом мануале описаны некоторые полезные методы профилирования запросов с помощью встроенных инструментов MySQL.
Лог медленных запросов MySQL
Лог медленных запросов MySQL (или slow query log) – это лог, в который MySQL отправляет медленные и потенциально проблемные запросы.
Эта функция поставляется с MySQL, но по умолчанию отключена. MySQL определяет, какие запросы нужно внести в этот лог, с помощью специальных переменных, которые позволяют профилировать запрос на основе требований к производительности приложения. Обычно в этот лог вносятся запросы, обработка которых занимает больше времени, и запросы, которые неправильно индексы.
Переменные профилирования
Базовыми серверными переменными для настройки лога медленных запросов MySQL являются:
slow_query_log глобальная
slow_query_log_file глобальная
long_query_time глобальная/сессионная
log_queries_not_using_indexes глобальная
min_examined_row_limit глобальная/сессионная
slow_query_log – логическая переменная для включения и выключения лога медленных запросов.
slow_query_log_file – абсолютный путь файла лога запросов. Каталог файла должен принадлежать пользователю mysqld и иметь соответствующие права на чтение и запись. Демон mysql, скорее всего, будет запущен как mysql, но чтобы убедиться в этом, запустите команду в терминале Linux:
ps -ef | grep bin/mysqld | cut -d' ' -f1
Вывод покажет текущего пользователя и пользователя mysqld.
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 обычно находится в /etc/my.cnf или /etc/mysql/my.cnf. Чтобы найти конфигурационный файл, введите (возможно, необходимо расширить поиск на другие корневые каталоги):
find /etc -name my.cnf
find /usr -name my.cnf
Найдя конфигурационный файл, добавьте требуемые переменные в раздел [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 заключается в том, что переменные будут потеряны при перезагрузке сервера. Потому все важные переменные, которые необходимо сохранить, следует добавить в файл.
Примечание: Синтаксис динамической установки переменных с помощью SET и размещения их в конфигурационном файле немного отличается (например, в первом случае используется slow_query_log, а во втором – slow-query-log). Больше об этом модно узнать в документации 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 и запустите:
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. Затем следует проверить, нет ли на сервере других настроек медленных запросов, которые переопределяют ваши параметры. Сбросьте значения по умолчанию, чтобы удалить все переменные медленных запросов из конфигурационного файла, и перезагрузите сервер. Также можно динамически установить глобальным переменным их стандартные значения. Если вы вносите изменения динамически, выйдите из системы и войдите в 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';
Эта запись отображает:
- Время выполнения запроса
- Кто его отправил
- Как долго обрабатывался запрос
- Длину
- Сколько строк было возвращено
- Сколько строк было проанализировано
Это полезно, потому что любой запрос, который нарушает указанные в переменных требования к производительности, попадает в лог. Это позволяет разработчику или администратору быстро отслеживать нерабочие запросы. Кроме того, данные профилирования запроса помогут вам определить, какие обстоятельства понижают производительность приложений.
Использование 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
…
В выводе отражены такие данные:
- Count: сколько раз запрос попадал в лог.
- Time: среднее и общее время запроса (в скобках).
- Lock: время блокирования таблицы.
- Rows: количество возвращенных строк.
Команда исключает числовые и строковые значения, потому одинаковые запросы с разными условиями WHERE воспринимаются как одинаковые. Инструмент mysqldumpslow устраняет необходимость постоянно просматривать лог медленных запросов, вместо этого позволяя проводить регулярные автоматические проверки. Параметры команды mysqldumpslow позволяют выполнять сложные выражения.
Разбивка запросов
Еще один инструмент профилирования, о котором следует помнить, – это инструмент для сложной разбивки запросов. Он позволяет определить проблемные запросы в логе медленных запросов и запустить его в 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 |
| … | … | … |
STATE – это шаг в процессе выполнения запроса, а DURATION – это время, необходимое для завершения этого шага в секундах. Это не очень полезный инструмент, но он может помочь определить, какая часть выполнения запроса вызывает наибольшую задержку.
Примечание: Этот инструмент не следует использовать в среде производства.
Производительность лога медленных запросов
Осталось только разобраться, как лог медленных запросов влияет на производительность. В целом запускать лог медленных запросов в производственной среде безопасно; ни CPU, ни I/O не должны пострадать. Тем не менее, у вас должна быть стратегия мониторинга размера лога, чтобы лог не стал слишком большим для файловой системы. Кроме того, при запуске лога медленных запросов в среде производства следует установить long_query_time значение 1 или выше.
Примечание: Не рекомендуется использовать инструмент профилирования, SET profiling=1, и регистрировать все запросы в среде с высокой рабочей нагрузкой.
Заключение
Лог медленных запросов может помочь определить проблемные запросы и оценить общую производительность запросов. При этом разработчик может получить подробное представление о том, как в приложении выполняются запросы MySQL. Инструмент mysqldumpslow позволяет управлять логами медленных запросов и легко включать их в процесс разработки. Определив проблемные запросы, вы можете оптимизировать обработку запросов для повышения производительности.
Tags: MySQL