Мониторинг производительности MySQL: Использование SHOW STATUS и SHOW PROCESSLIST

Когда приложение на MySQL замедляется, SHOW STATUS и SHOW PROCESSLIST — это самые быстрые встроенные проверки, которые можно выполнить до открытия панели мониторинга. Они не объяснят каждую проблему сами по себе, но отвечают на два практических вопроса: что делал сервер и что выполняется прямо сейчас?

Понимание состояния системы в реальном времени с помощью SHOW STATUS

Команда SHOW STATUS, часто используемая как синоним SHOW GLOBAL STATUS или SHOW SESSION STATUS, предоставляет множество информации об активности сервера с момента последнего перезапуска или с начала текущего сеанса. Эти переменные состояния действуют как счетчики, отслеживая все: от попыток подключения до эффективности кэша и ожиданий блокировок.

Глобальный vs. Сессионный статус

При выполнении этой команды важно понимать область действия:

SHOW GLOBAL STATUS: Показывает счетчики, накопленные с момента запуска экземпляра сервера MySQL. Это дает общий обзор состояния сервера и долгосрочных тенденций.
SHOW SESSION STATUS: Показывает счетчики, специфичные только для текущего соединения (сеанса). Это полезно для изоляции влияния на производительность конкретных транзакций.

Ключевые показатели производительности (KPI) из SHOW GLOBAL STATUS

Хотя SHOW GLOBAL STATUS возвращает сотни переменных, несколько из них критически важны для начальной диагностики производительности. Обычно вывод передается в grep или используется предложение WHERE для фильтрации по релевантности.

1. Мониторинг соединений и потоков

Эти переменные помогают понять нагрузку соединений:

Имя переменной	Описание
`Threads_connected`	Количество текущих открытых соединений (клиентов).
`Threads_running`	Количество активных потоков, выполняющих запросы (обычно должно быть низким).
`Max_used_connections`	Максимальное количество одновременных соединений с момента запуска сервера. Полезно для настройки `max_connections`.

Пример: Проверка активных соединений:

SHOW GLOBAL STATUS LIKE 'Threads_%';

2. Кэширование запросов и эффективность

Если вы используете устаревший кэш запросов (доступен в старых версиях MySQL, устарел/удален в новых), эти метрики важны:

Qcache_hits: Количество раз, когда запрос был обслужен из кэша.
Qcache_lowmem_prunes: Количество запросов, которые привели к удалению старых записей из кэша из-за нехватки памяти.

3. Метрики движка InnoDB (наиболее важные для современного MySQL)

Для современных развертываний с использованием движка InnoDB отслеживайте активность буферного пула:

Innodb_buffer_pool_read_requests: Общее количество запросов на чтение.
Innodb_buffer_pool_reads: Количество физических чтений с диска (высокое соотношение физических чтений к запросам указывает на необходимость увеличения буферного пула).

Практический совет: Чтобы быстро оценить эффективность буферного пула, рассчитайте коэффициент попаданий: (Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests.

4. Временные таблицы и сортировки

Они показывают, сколько внутренней обработки выполняет MySQL:

Created_tmp_tables: Количество созданных временных таблиц в памяти.
Created_tmp_disk_tables: Количество временных таблиц, которые пришлось записать на диск (медленно).

Если Created_tmp_disk_tables высок, возможно, потребуется увеличить tmp_table_size или max_heap_table_size.

Диагностика активной рабочей нагрузки с помощью SHOW PROCESSLIST

В то время как SHOW STATUS сообщает, что произошло, SHOW PROCESSLIST сообщает, что происходит прямо сейчас. Он отображает информацию о потоках, выполняющихся в данный момент на сервере, позволяя выявить долго выполняющиеся или заблокированные запросы.

Структура списка процессов

Команда выводит несколько столбцов, каждый из которых предоставляет контекст об активном соединении:

Столбец	Описание
Id	Уникальный идентификатор соединения (используется для завершения процесса).
User	Подключенная учетная запись пользователя.
Host	Хост, с которого установлено соединение.
db	База данных, используемая потоком в данный момент.
Command	Тип выполняемой команды (например, Query, Sleep, Connect).
Time	Количество секунд, в течение которых поток находится в текущем состоянии.
State	Конкретное действие, выполняемое потоком (например, Sending data, Copying to tmp table).
Info	Фактический выполняемый SQL-запрос (или усеченный, если длинный).

Фильтрация и интерпретация вывода

Для больших производственных систем полный список процессов может быть перегружен. Стандартной практикой является использование ключевого слова FULL, чтобы увидеть полный текст запроса, а затем фильтрация по столбцам Time или State.

1. Просмотр полного текста команды

Всегда используйте FULL, если подозреваете медленные запросы, так как стандартный вывод часто усекает поле Info:

SHOW FULL PROCESSLIST;

2. Выявление блокирующих или медленных запросов

Отслеживайте столбцы Time и Command:

Высокое значение Time: Любой запрос, выполняющийся в течение длительного времени (например, более 10 секунд, в зависимости от вашего SLA), требует немедленного расследования. Проверьте соответствующий столбец Info, чтобы увидеть SQL.
Command = 'Sleep': Эти соединения простаивают, но все еще потребляют ресурсы. Если они накапливаются чрезмерно, рассмотрите возможность настройки переменной wait_timeout.
Command = 'Query': Это активно выполняющиеся операторы. Обратите пристальное внимание на их State.

3. Выявление проблем с блокировками

Когда запросы застревают в ожидании ресурсов, столбец State часто указывает на это:

Waiting for table metadata lock
Waiting for table lock
Waiting for lock

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

Действие: Завершение процесса

Если вы обнаружите вышедший из-под контроля запрос, который серьезно ухудшает производительность, вы можете завершить его с помощью команды KILL, за которой следует идентификатор процесса Id:

KILL 12345; -- Замените 12345 на фактический Id из списка процессов

Предупреждение: Используйте KILL с осторожностью. Завершение активной транзакции может оставить базу данных в несогласованном состоянии, если транзакция была на полпути сложной операции записи. По возможности сначала попытайтесь выявить и оптимизировать запрос.

Объединение информации о статусе и процессах для устранения неполадок

Эффективный мониторинг MySQL часто включает корреляцию между этими двумя командами:

Первичная проверка: Выполните SHOW FULL PROCESSLIST. Отметьте любые запросы с большим временем или чрезмерные соединения.
Проверка контекста: Проверьте количество соединений с помощью SHOW GLOBAL STATUS LIKE 'Threads_connected'. Вы столкнулись с потоком или всего одним плохим запросом?
Глубокое погружение: Если конкретный запрос медленный, проанализируйте его влияние на счетчики ресурсов, проверив Innodb_buffer_pool_reads или скорость создания временных таблиц во время выполнения запроса (требуется сравнение с базовым уровнем).

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

Реалистичная процедура триажа

Хороший первый проход занимает меньше минуты. Начните со списка процессов:

SHOW FULL PROCESSLIST;

Просмотрите на наличие кучи активных запросов, длительных значений Time, ожиданий блокировок и множества простаивающих соединений Sleep. Один медленный отчетный запрос обрабатывается иначе, чем сотни веб-соединений, ожидающих одну и ту же блокировку таблицы.

Затем проверьте счетчики потоков:

SHOW GLOBAL STATUS WHERE Variable_name IN (
  'Threads_connected',
  'Threads_running',
  'Max_used_connections'
);

Threads_connected показывает, сколько клиентов подключено. Threads_running обычно более важен во время замедления, поскольку показывает, сколько потоков активно выполняют работу. Много подключенных спящих клиентов может быть расточительно, но много работающих потоков может означать, что сервер находится под реальным давлением.

Затем проверьте, создает ли рабочая нагрузка временные таблицы на диске:

SHOW GLOBAL STATUS WHERE Variable_name IN (
  'Created_tmp_tables',
  'Created_tmp_disk_tables',
  'Sort_merge_passes'
);

Эти счетчики накапливаются с момента запуска, поэтому один снимок может ввести в заблуждение. Сделайте два снимка с интервалом в несколько минут во время инцидента. Если дисковые временные таблицы быстро увеличиваются, проверьте запросы с GROUP BY, ORDER BY, большими объединениями, текстовыми столбцами или отсутствующими индексами. Увеличение tmp_table_size может помочь в некоторых случаях, но лучший запрос или индекс часто является более чистым исправлением.

Анализ нагрузки на InnoDB

Большинство современных развертываний MySQL используют InnoDB, поэтому счетчики InnoDB заслуживают внимания:

SHOW GLOBAL STATUS WHERE Variable_name LIKE 'Innodb_buffer_pool_read%';

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

Ожидания блокировок — еще один распространенный источник проблем:

SHOW GLOBAL STATUS WHERE Variable_name LIKE 'Innodb_row_lock%';

Растущие ожидания блокировок строк не автоматически означают, что InnoDB сломан. Обычно это означает, что транзакции удерживают блокировки дольше, чем ожидает приложение. Ищите открытые транзакции, медленные обновления или пути кода, которые начинают транзакцию, вызывают внешние службы и фиксируют гораздо позже.

Для более детальной информации о блокировках и транзакциях SHOW ENGINE INNODB STATUS\G может помочь, но его вывод плотный. Используйте его, когда список процессов показывает ожидания блокировок, и вам нужно определить шаблон транзакций, стоящий за ними.

Безопасное использование `KILL`

KILL полезен, но это не кнопка очистки. Если вы убьете соединение, выполняющее большую транзакцию, MySQL может потребоваться откатить работу, а откат может занять время. В некоторых инцидентах это все равно правильное решение, но принимайте решение осознанно.

Предпочтительно сначала убить запрос, если ваша версия MySQL и разрешения поддерживают это:

KILL QUERY 12345;

Это пытается остановить текущий оператор, сохраняя соединение активным. Если клиент ведет себя некорректно или соединение должно быть разорвано, используйте:

KILL CONNECTION 12345;

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

Распространенные состояния списка процессов и что они означают

Sending data не всегда означает, что MySQL отправляет строки по сети. Часто это означает, что сервер читает, фильтрует, сортирует или подготавливает строки. Если запрос проводит много времени в этом состоянии, выполните EXPLAIN для оператора и поищите сканирования таблиц, плохой порядок соединений или отсутствующие индексы.

Copying to tmp table или Creating sort index часто указывает на дорогостоящую сортировку или группировку. Проверьте, может ли индекс поддерживать шаблон WHERE и ORDER BY. Иногда запрос делает именно то, что запросил продукт, но он принадлежит асинхронному отчету, а не пути запроса.

Waiting for table metadata lock часто появляется, когда DDL и обычные запросы конфликтуют. Казалось бы, простой ALTER TABLE может ждать за открытой транзакцией, в то время как более поздние запросы накапливаются за ожидающим DDL. В этом случае убийство самого старого блокирующего может быть безопаснее, чем убийство каждого ожидающего запроса.

Превращение счетчиков в полезные доказательства

Поскольку значения SHOW STATUS в основном являются счетчиками, скорости более полезны, чем необработанные числа. Захватите одни и те же переменные дважды:

SHOW GLOBAL STATUS LIKE 'Questions';
SHOW GLOBAL STATUS LIKE 'Slow_queries';

Подождите шестьдесят секунд, затем выполните их снова. Разница покажет скорость в течение этой минуты. Это та же идея, которую используют панели мониторинга, но ручное выполнение полезно, когда у вас есть только доступ к терминалу.

Ведите заметки во время инцидентов. «Threads_running подскочило с 8 до 90, список процессов показал 70 запросов, ожидающих блокировку метаданных таблицы orders, а Max_used_connections не изменилось» — это полезный диагноз. «MySQL был медленным» — нет.

Когда этих команд недостаточно

SHOW STATUS и SHOW PROCESSLIST — это инструменты первой реакции. Они не заменяют журнал медленных запросов, Performance Schema, планы запросов или метрики на уровне хоста. Если та же проблема возвращается, включите или просмотрите журнал медленных запросов и проверьте худшие операторы с помощью EXPLAIN.

Для повторяющихся всплесков соединений посмотрите на настройки пула приложений и поведение развертывания. Увеличение max_connections может выиграть время, но также может позволить серверу принять больше работы, чем он может фактически выполнить. Для повторяющихся ожиданий блокировок проверьте границы транзакций в приложении. Транзакция, которая остается открытой, пока код вызывает внешний API, может блокировать несвязанные запросы и заставлять MySQL выглядеть медленнее, чем он есть.

Также проверьте хост. Если задержка диска высока, ЦП насыщен, память подкачивается или шумный сосед крадет ресурсы, счетчики MySQL покажут симптомы, но не всю причину. Хороший диагноз сочетает команды базы данных с системными метриками.

SHOW STATUS дает вам счетчики и контекст. SHOW FULL PROCESSLIST дает вам живую рабочую нагрузку. Используемые вместе, они помогают вам различать давление соединений, один плохой запрос, конкуренцию блокировок, тяжелую дисковую временную работу и давление кэша InnoDB.