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, які реально потрібні для поточного завдання діагностики. Увімкнення всього набору одразу помітно збільшує накладні витрати і обсяг даних у пам'яті, тоді як точкове увімкнення дає потрібну картину майже без втрат продуктивності і не роздуває таблиці історії понад міру.