MySQL Performance Schema: мониторинг узких мест сервера

MySQL Performance Schema

Performance Schema — встроенный инструмент диагностики MySQL, работающий на уровне движка. В отличие от SHOW STATUS, он позволяет отслеживать конкретные запросы, потоки, блокировки и потребление ресурсов в реальном времени.

Включение Performance Schema

# Проверить статус
mysql -e "SELECT * FROM performance_schema.setup_instruments LIMIT 5;"

# В my.cnf (включено по умолчанию в MySQL 5.6+)
[mysqld]
performance_schema=ON

Топ медленных запросов

-- Топ-10 запросов по суммарному времени выполнения
SELECT
  DIGEST_TEXT,
  COUNT_STAR AS exec_count,
  ROUND(SUM_TIMER_WAIT/1e12, 3) AS total_sec,
  ROUND(AVG_TIMER_WAIT/1e12, 6) AS avg_sec,
  SUM_ROWS_EXAMINED AS rows_examined
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

Анализ блокировок

-- Текущие блокировки InnoDB
SELECT
  r.trx_id waiting_trx_id,
  r.trx_mysql_thread_id waiting_thread,
  r.trx_query waiting_query,
  b.trx_id blocking_trx_id,
  b.trx_mysql_thread_id blocking_thread,
  b.trx_query blocking_query
FROM information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

Мониторинг памяти

-- Потребление памяти по компонентам
SELECT
  EVENT_NAME,
  CURRENT_NUMBER_OF_BYTES_USED / 1024 / 1024 AS current_mb,
  HIGH_NUMBER_OF_BYTES_USED / 1024 / 1024 AS peak_mb
FROM performance_schema.memory_summary_global_by_event_name
WHERE CURRENT_NUMBER_OF_BYTES_USED > 0
ORDER BY CURRENT_NUMBER_OF_BYTES_USED DESC
LIMIT 10;

Статистика I/O по таблицам

-- Таблицы с наибольшей нагрузкой на чтение/запись
SELECT
  OBJECT_SCHEMA,
  OBJECT_NAME,
  COUNT_READ,
  COUNT_WRITE,
  COUNT_FETCH,
  SUM_TIMER_WAIT / 1e12 AS total_wait_sec
FROM performance_schema.table_io_waits_summary_by_table
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

Sys Schema — удобная обёртка

-- sys schema предоставляет читаемые представления
-- Топ запросы по задержке
SELECT * FROM sys.statements_with_runtimes_in_95th_percentile LIMIT 10;

-- Таблицы без индексов
SELECT * FROM sys.schema_tables_with_full_table_scans;

-- Неиспользуемые индексы
SELECT * FROM sys.schema_unused_indexes;

Частые ошибки при работе с Performance Schema

ERROR 1146 (42S02): Table 'performance_schema.events_statements_history_long' doesn't exist

Empty set (0.00 sec) -- при запросе к events_statements_summary_by_digest

ERROR 1227 (42000): Access denied; you need the PROCESS privilege

events_statements_history: Truncated due to table size limit

Performance Schema против SHOW STATUS и Slow Query Log

Когда Performance Schema не подходит

• Сервер работает на минимальном тарифе с жёстким лимитом памяти — таблицы Performance Schema в памяти сами занимают заметный объём при включении всех инструментов сразу
• Нужен репрезентативный профиль нагрузки за длительный период (недели) — данные в памяти вытесняются по кольцевому буферу, для долгой истории нужен внешний экспорт в мониторинг
• Требуется профилирование конкретного одного запроса в изоляции — здесь проще и нагляднее обычный EXPLAIN ANALYZE, чем агрегированная статистика по digest
• СУБД — не MySQL/MariaDB, а, например, PostgreSQL — там своя система pg_stat_statements, устроенная иначе

Проверка, что нужные данные действительно собираются

-- Убедиться, что инструменты и потребители включены
SELECT * FROM performance_schema.setup_instruments WHERE NAME LIKE 'statement/%' AND ENABLED='NO' LIMIT 5;
SELECT * FROM performance_schema.setup_consumers WHERE ENABLED='NO';

-- Счётчик накопленных событий растёт при реальной нагрузке
SELECT COUNT(*) FROM performance_schema.events_statements_summary_by_digest;

1. Выполните тестовый запрос к базе и убедитесь, что он появился в events_statements_summary_by_digest — если таблица пуста, инструменты выключены
2. Проверьте, что setup_instruments и setup_consumers не содержат нужных строк со значением ENABLED='NO'
3. Сравните нагрузку CPU до и после включения полного набора инструментов — просадка больше нескольких процентов означает, что стоит включать только нужные instruments, а не всё сразу
4. Убедитесь, что план сбора данных переживает перезапуск сервера — если инструменты включались вручную через UPDATE, а не в my.cnf, после рестарта они вернутся к значениям по умолчанию

Более подробный разбор медленных запросов через классический механизм — в статье Slow Query Log, а чтение плана выполнения конкретного запроса — в материале чтение плана запроса EXPLAIN. Про сами индексы, которые часто оказываются причиной проблем, найденных через Performance Schema, — статья MySQL индексы: типы и оптимизация.

Частые вопросы

Почему таблица со статистикой запросов оказывается пустой, хотя Performance Schema включена в конфигурации?

Пустой результат почти всегда значит, что Performance Schema включена в целом, но конкретные инструменты для статистики запросов по умолчанию отключены. Нужно отдельно включить нужные instruments и consumers через обновление системных таблиц настройки, иначе события просто не фиксируются, даже если сам механизм диагностики формально активен на сервере.

Насколько сильно включение Performance Schema влияет на производительность самого сервера?

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

Какие права нужны учётной записи, чтобы читать данные Performance Schema?

Учётной записи нужно право читать системные представления производительности — без него сервер сразу откажет с сообщением о нехватке привилегии. Право выдают отдельной командой на уровне пользователя базы и ограничивают только нужной учётной записью для мониторинга, не раздавая его всем подряд, чтобы доступ к внутренней статистике сервера оставался под контролем.

Стоит ли включать сразу все инструменты и consumers или лучше только нужные?

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