Как интерпретировать медленную информацию журнала запросов, сгенерированную MySQL?

Итак, мое понимание медленного журнала запросов заключается в том, что он регистрирует информацию обо всех тех запросах, которые заняли >= время (в секундах), которое мы установили в файле my.conf.

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

QUERY I: Query_time: 32.937667 Lock_time: 0.000081 Rows_sent: 343 Rows_examined: 12714043

QUERY II: Query_time: 12.937667 Lock_time: 0.000081 Rows_sent: 43 Rows_examined: 714043

QUERY III: Query_time: 42.937667 Lock_time: 0.000081 Rows_sent: 18 Rows_examined: 483

Для меня оба QUERY я и QUERY II выглядят как возможные случаи плохого запроса или плохой индексации (или отсутствия индексации) или фрагментированных данных таблицы и т.д. (что-нибудь еще, что я мог пропустить?), который пользователь может попытаться улучшить время выполнения запроса.

Но для QUERY III я не могу опустить голову, я имею в виду то, что действительно может быть неправильно с БД, что требуется 42 секунды, чтобы просто изучить 483 строки и отправил обратно 18 из них (с небрежной блокировкой время). Это становится еще более запутанным, когда я вижу, что это происходит с перерывами.

Так что я действительно хочу спросить здесь:

  • Как мне интерпретировать информацию о времени блокировки? Означает ли это, что запрос должен был ждать столько секунд, прежде чем он начнет выполняться? Если да, то в моем примере запрос III фактически занял 42 секунды, чтобы исследовать 483 строки и отправил обратно 18 из них?
  • если время блокировки является пренебрежимым, но все же время запроса супер огромно, и только несколько сотен строк проверяются и отправляются обратно, где я должен искать проблемы?
  • Может ли быть так, что запрос тратит много времени в какой-то основной активности ввода-вывода? скажем, протоколирование или ведение журнала.
  • Насколько сильно размер таблицы влияет на производительность запроса? например мы можем сказать, что MySQL достаточно хорош, чтобы обрабатывать таблицу с 200 + миллионами строк.
  • Есть ли какой-либо лучший инструмент или способ отслеживать активность БД, специально для того, чтобы изобразить фоновую активность БД? Короче говоря, чтобы проверить, где этот запрос тратит большую часть времени.

На такие медленные запросы может быть много факторов, поэтому, если вы почувствуете, что вам нужна дополнительная информация из стороны, чтобы помочь мне, пожалуйста, сообщите мне.

Ответы

Ответ 1

  • Время блокировки - это время, потраченное до начала выполнения запроса. I.e., время, ожидающее, пока другие потоки откажутся от своих блокировок по данным, которые должен блокировать текущий запрос.

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

    Если ваш запрос сортируется на диске для заданного запроса, он будет медленнее, если он рассмотрит несколько строк.

    Если ваша система ввода/вывода перенапряжена, вы можете получить прерывистую медлительность. Это также может произойти с виртуализированным вводом-выводом (например, дешевые экземпляры AWS). Или, если ваши диски начинают терпеть неудачу, они могут периодически получать ошибки.

    Мониторинг iostat и посмотреть длину очереди, средний срок ожидания и время обслуживания. Посмотрите, есть ли периоды медлительности, или если производительность и пропускная способность более или менее согласованы.

  • Проверенные строки не отражают несколько операций ввода-вывода, необходимых для получения заданной строки. Например, если в строке много больших столбцов BLOB/TEXT/VARCHAR, хранящихся на страницах переполнения. Или, если транзакция должна посещать сегмент отката для извлечения старых версий некоторых строк, если они были изменены с момента начала этой транзакции.

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

    Не видя запросов и их отчета EXPLAIN, трудно обобщить объяснение медлительности, учитывая только те числа из медленного журнала запросов.

MySQL, безусловно, может хранить 200 миллионов строк в таблице, но в этом масштабе вы начинаете получать проблемы с производительностью, даже если индекс может уменьшить поиск до 483 рассмотренных строк. Это связано с тем, что глубина индекса B-дерева и размер индексированного столбца напрямую связаны с количеством операций ввода-вывода, необходимых для поиска эти 483 строки. Чем больше операций ввода-вывода, тем дольше это требуется, и это не отражается на проверенных строках. Время запроса включает время ввода-вывода, но не ясно, сколько времени запроса связано с I/O.

Несколько других мест для поиска более подробной диагностики:

Ответ 2

Query_time: 12.937667 Lock_time: 0.000081 Rows_sent: 43 Rows_examined: 714043

Query Time: Total time including lock time query has taken

Lock_Time: Total query query was in a locked state

Rows sent: Total rows sent by server to client

Rows examined: Total rows scanned by a MySQL server for a query