И швец, и жнец, и на дуде игрец
0,0
рейтинг
3 июня 2007 в 03:02

Разработка → Как выявить медленные 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: это не значит, что в статье описаны какие-то ложные действия. Просто как приятный бонус (:
Перевод: Джастин Сильвертон (Justin Silverton)
[BlockDog] @blockdog
карма
22,6
рейтинг 0,0
И швец, и жнец, и на дуде игрец
Реклама помогает поддерживать и развивать наши сервисы

Подробнее
Реклама

Самое читаемое Разработка

Комментарии (57)

  • 0
    Если вы нашли в тексте неточность, опечатку, или ошибку, пожалуйста, сообщите об этом в комментариях.
  • 0
    вот хорошая тема в продолжение http://www.mysqlperformanceblog.com/2007…
    • 0
      Спасибо, почитаю.
  • 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
      Угу.
      • 0
        И я свою про PHP/Java
  • 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
    спасибо. авось пригодится... буду знать что такое есть.

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