Как выявить медленные SQL запросы?

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

Как узнать, какие из запросов выполняются дольше всего?
В MySQL есть встроенный функционал для ведения логов медленных запросов.

Для включения этого функционала необходимо произвести одно из действий:

1. добавить следующие строки в /etc/my.cnf:
   

   log-slow-queries=/tmp/slow_queries.log
long_query_time=10

2. вызвать mysqld со следующими параметрами:
   

   –log-slow-queries[=/tmp/slow_queries.log]

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

Другие связанные опции:

–log-slow-admin-statements

Записывать в лог медленные административные операторы такие, как OPTIMIZE TABLE, ANALYZE TABLE, и ALTER TABLE.

–log-queries-not-using-indexes

Если вы используете эту опцию вместе с –log-slow-queries, запросы, которые не используют индексы, будут записываться в лог медленных запросов.

 
Если ведение лога медленных запросов было успешно включено, вы увидите «ON» в столбце «Value» для строки «log_slow_queries» (как показано на рисунке выше).

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

Вы можете столкнуться с ситуацией, когда запрос выполняется медленно только при определенных условиях (когда вы записываете его в лог), но при ручном запуске запрос отрабатывает нормально:

• Таблица может быть заблокирована (locked), ставя, таким образом, запрос в очередь ожидания. В таком случае lock_time определяет, когда таблица будет разблокирована, и как долго будет обрабатываться запрос.
• Данные и индексы не были занесены в кэш памяти. Это обычно случается, когда MySQL запускается в первый раз, или когда таблицы не были оптимизированы.
• Был запущен сторонний процесс, замедляющий работу диска.
• Сервер перегружен другими запросами в это время, и не хватает ресурсов CPU для эффективной работы.

Анализ лога

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

——————————————————————————————

Справедливости ради, хочу добавить от себя пару слов.
Поскольку это все-таки перевод, а не своя статья, я старался максимально точно перевести то, что написал автор.
Но для тех, кто заинтересовался этой маленькой статьей, я хочу порекомендовать прочитать об этом функционале в официальном мануале MySQL.
Ссылки:
dev.mysql.com/doc/refman/5.0/en/slow-query-log.html — версия на английском языке
www.mysql.ru/docs/man/Slow_query_log.html — версия на русском языке
PS: это не значит, что в статье описаны какие-то ложные действия. Просто как приятный бонус (: