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

http://www.whenpenguinsattack.com/2007/05/16/how-to-find-slow-mysql-queries/
  • Перевод
Это случалось с каждым из нас при разработке веб-сайтов или приложений, использующих 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: это не значит, что в статье описаны какие-то ложные действия. Просто как приятный бонус (:
Поделиться публикацией
AdBlock похитил этот баннер, но баннеры не зубы — отрастут

Подробнее
Реклама
Комментарии 57
  • 0
    Если вы нашли в тексте неточность, опечатку, или ошибку, пожалуйста, сообщите об этом в комментариях.
    • 0
      вот хорошая тема в продолжение http://www.mysqlperformanceblog.com/2007…
    • 0
      Не нужно бояться "долгих" запросов. Нужно дружиться с кешированием и InnoDB.
      • 0
        разве долгие запросы - не сигнал о том, что "что-то не в порядке с индесами и запросами"?
        имхо цель кеширования - не залатать проблемы с неумением работать с индексами, а увеличить производительность и так уже оптимизированной бд
        • 0
          Не сигнал. А кто вообще кэшированием латает пробллемы с БД? Я говорю о тех случаях, когда медленных запросов не избежать, предполагая, что разработчик БД не ленив и потратил нужное время на её проектирование и осмысление.
          • 0
            имхо, данные советы отночятся как раз к этапу "разработчик БД не ленив и потратил нужное время на её проектирование и осмысление". т.е. в процессе "осмысления" подобные рекомендации ему могут пригодиться :)
        • 0
          Согласен, если долгие запросы выполняются с Вашего ведома - ничего страшного в этом нет. Но все же - бояться не нужно, нужно знать ;)
          • 0
            Да что Вы говорите? А может приведёте пример тестов, где ИнноДБ на селектах явно превосходит МайИсам?
            • 0
              Иногда альтернативы InnoDB нет.
              • 0
                Ну альтернативы-то есть всегда. Только не в рамках MySQL. Но на БД в основном с селектами MyISAM — вполне себе альтернатива для InnoDB
                • 0
                  причем тут селекты, разве преимущество иннодб в первую очередь не в поддержке транзакций?
                  иногда они необходимы и тогда майизам теряет смысл.
              • 0
                Я говорю о поддержке транзакций (к скорости это отношения не имеет) и каскадных операциях (вот тут уже стоит задуматься).
                InnoDB проиграет в скорости на примитивном селекте MyISAM, но когда понимаешь, что удаляя, либо изменяя некую строку в одной таблице, можно каскадно удалить/изменить данные в сколь угодно многих свзянных таблицах всего лишь одним запросом с гарантированной надёжностью, то становятся очевидными приимущества InnoDB.
                Потому я и говорил выше InnoDB + вменяемое кэширование == Крест на MyISAM.
                P.S. Я долго сидел на MyISAM и в действительности убеждён, что это самый быстрый тип БД MySQL. Но времена и требования от проекта к проекту меняются. Изменились и мои предпочтения.
                • 0
                  Просто, IMHO, если вы работаете в MyISAM и у вас долго выполняются запросы, значит:
                  а. Вы просто напортачили в планировании БД
                  б. Вы не напортачили с БД, но просто такой рисковый парень, что готовы потерять данные, используя тип БД без поддержки транзакций.
                  • 0
                    Я вообще Мускул не использую, больно убогая БД.
                    И подходы к медленности запросов у нас с Вами разные
                    • 0
                      В последнее время не использую
                      • 0
                        Буду рад, если вы в пм отпишите, с чем работать стоит. Без иронии и шуток.
                        • 0
                          А можно мне продублировать, тоже интересно? :)
                          • 0
                            Те же Постгресс и Оракл. Транзакции у них, ессесн, есть, а возможности в плане выполнения выборок все ж помощнее Мускуловых. Про другие промышленные СУБД ничего сказать не могу — не использовал. Сравнение с Мускулом в рамках треда не распишу, не та тематика, да и статей на эту тему полно.
                            • 0
                              Оракл... Оно платное. Я видел как оно работает. Страшно. Я трудностей не боюсь, но симбиоза живого организма и компьютерной программы (да, программы) — опасаюсь (:
                              • +1
                                MySQL тоже платная для коммерческого использования (сюрприз, да?).

                                Для изучения Оракл вполне бесплатен и скачивается непосредственно с их сайта. Другое дело, что всякие патчи и апдейты недоступны. Но поиграться даже в серьёзные вещи вполне можно. И не так страшен Оракл, как его малюют. Хотя после Мускула первое время будет непривычно, да.
                                • 0
                                  с каких это пор у мускула кроме платной поддержки появилась еще и платная эксплуатация !? всех хостеров арестовываем за контрафакт, ага?
                                  • 0
                                    Мне приводить здесь тексты лицензий или сами «асилите» в каких случаях используется GPL, а в каких коммерческая лицензия?
                                    • 0
                                      перечитал FAQ по лицензированию MySQL, так и не понял, где именно "MySQL тоже платная для коммерческого использования (сюрприз, да?)."

                                      вы ничего не путаете? а то словами типа GPL вы бросаться научились, а ссылками нет. жаль ...
                    • 0
                      очень простой тест - 100 потоков делают SELECT, INSERT, UPDATE в одну таблицу.
                      в это случае MyISAM большинство времени будет простаивать на блокировке таблицы, а InnoDB в свою очередь будет простаивать только на блокировке строки или блокировки части индекса, в зависимости от того, какой уровень сериализации у вас.
                      Второй момент, чем плох MyISAM - часто бьются таблицы при большой нагрузке, что вообще неприемлемо для некоторых задач.
                      • 0
                        Я спрашивал про Селекты, а не еще и Инсерты / Апдейты.
                        Так то прекрасно знаю, что такое локи в MyISAM.
                        • 0
                          проглядел :) в случае когда данных больше чем кеша и фиксированный размер строки в myisam это верно. я никогда не делал тестов когда индексы/данные полностью помещаются в кеш mysql. подозреваю, что тогда разница не очень большой будет.
                  • +1
                    Надеюсь в следующей серии прочитать подробное описание EXPLAIN
                    • 0
                      При разработке ПО можно использовать встроенный в приложение профайлер БД. Например, в Zend Framework есть замечательный довольно гибкий профайлер.

                      • 0
                        Да вообще скорость выполнения любого запроса (пример PHP) можно замерить самому:

                        $startTime = mktime();
                        mysql_query('somequery', $db);
                        $endTime = mktime();
                        echo($endTime - $startTime); //Время, потраченное на операцию (включая и отработку PHP и скорость формирования результата мускулом) в ms.

                        Куда уж проще и надёжней?
                        • 0
                          Но тут, как вы заметили, мы прибавляем еще и время обработки пхп + доставка данных (если сервер не локальный). А сам мускул может дать время чистого запроса.
                          • 0
                            Ну, не будем кривить душой, представленный php код наиболее близок к тому, что покажет мускл.. тут нет, как вы выразились «обработки php» и «доставки данных».
                            Чтобы добавить это самое время, нужно изменить код до следующего (допустим, что выбираем одну строку в ассоциативный массив):
                            $startTime = mktime();
                            $result = mysql_query('somequery', $db);
                            $row = mysql_fetch_assoc($result);
                            $endTime = mktime();
                            echo($endTime - $startTime);

                            В этом случае первый выделенный фрагмент как раз будет обработкой php, а второй — доставкой данных.
                            Справедливости ради могу отметить, что в коде при выборке как раз используется мой вариант. Так что, все, что выводится внизу форумных движков — это как раз время с учетом обработки и доставки. Но, доставки не всегда, т.к. чаще всего код преобразуется до следующего:
                            $startTime = mktime();
                            $result = mysql_query('somequery', $db);
                            $endTime = mktime();
                            $row = mysql_fetch_assoc($result);
                            echo($endTime - $startTime);

                            т.е. доставка не входит в считаемое время..
                            Хотя, я лично видел несколько «общественных» скриптов, где запрос выполнялся два раза, одна раз как в первом варианте, а второй раз без подсчета времени. Т.е. выводились данные, никак не соответствующие реальности.
                            • 0
                              Только не понимаю, почему вы используете функцию mktime(); ? Все таки измерять скорость выполнения запросов к БД измерять в секундах не очень правильно и microtime() будет более уместной.
                          • 0
                            Можно и так считать, вопрос в том, что дальше с этой цифрой делать? ;) Надо же все вместе собрать и проанализировать.
                        • 0
                          Это ты с PHPinside ссылку взял? :)
                        • 0
                          Лог медленных запроов, безусловно, полезная штука. Но он не решает всех проблем - хотя бы потому, что минимальное время выполнения "медленного" запроса - 1 скеунда. Т.е. никак не заставишь mysql писать в него запросы, которые выполняются более 0.1 или 0.5 секунд. В то же время обычно наибольшее суммарное время приходится именно на такие запросы (а вове не на медленные). Обычно это довольно простые запросы, которые выполняются неэффективно из-за неправильно построенных индексов или неправильно формулированных запросов (и тем не менее, успевают отработать за секунду). Их можно (и нужно) отследить только аггрегируя полный лог запросов.
                          • 0
                            Действительно, long_query_time может быть только целочисленное.
                            • 0
                              Всё так.
                              • 0
                                в mysqlperfomanceblog.com (на который я давал ссылку выше), помнится был патч для mysql, позволяющих рулить данным параметром с точностью до 0.001с.
                                прямую ссылку не дам, но точно было, полистай
                              • +1
                                Извиняюсь за некропостинг.
                                Начиная с версии mysql 5.1.21 можно писать не целочисленное значение, типа long_query_time=0.5
                                • 0
                                  Да, спасибо за уточнение. И тем не менее, это все равно не позволит выявить проблемы из-за миллионов очень коротких запросов.
                              • 0
                                # вызвать mysqld со следующими параметрами:

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

                                опечатка. скобку уберите? :)
                                • 0
                                  оупс, извините, туплю :)
                                  • 0
                                    Это необязательный параметр.
                                    Если он не указан, то лог будет сохраняться, если я не ошибаюсь в папке data/
                                  • 0
                                    Счастливые люди — медленные запросы 1–10 сек.
                                    У меня в разряд медленных попадают запросы, которые фетчатся более 0.02 секунд. Для них ничего нет?
                                    • 0
                                      В большинстве слуаев есть возможность воспользоваться профилировщиками со стороны клиента (правда возможно, придется написать собственный).
                                      • 0
                                        фетчатся или всёж "куэрятся"? )
                                        • 0
                                          Я сейчас говорю только про селекты, поэтому «фетчатся». То есть, само выполнение SELECT'а + вывод выборки. А UPDATE-INSERT «куэрятся» быстро, ибо OLTP-приложение. В тех, местах, где они выполняются долго, это оправдано и некритично. Скорость SELECT'ов критична у меня везде.
                                        • 0
                                          http://www.mysqlperformanceblog.com/2006…

                                          Но непонятно, зачем вы спрашиваете, если с MySQL уже не работаете.
                                        • +1
                                          Postgres людям очень сильно рекомендую http://pgfouine.projects.postgresql.org/
                                        • 0
                                          Хочу заметить, что если файл, в который будет писаться лог, класть не в /tmp, а, скажем, в /var/log, где он, по-моему, уместнее, то перед запуском mysqld файл уже должен существовать, а пользователь, от имени которого запускается mysqld - иметь к нему доступ на запись.

                                          Добиться этого можно, например, так:

                                          touch /var/log/mysql_slow_queries.log
                                          chown mysql.mysql -R /var/log/mysql_slow_queries.log
                                          • 0
                                            Большое спасибо за статью!
                                            • 0
                                              спасибо. авось пригодится... буду знать что такое есть.

                                              Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.