Slow query log, Performance Schema, sys schema, анализ проблем производительности
Без мониторинга вы не узнаете, что индексы деградировали, пока пользователи не начнут жаловаться. Системный подход к диагностике — основа стабильной работы.
Лог медленных запросов — первый источник информации о проблемах:
-- Включение slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1; -- запросы дольше 1 секунды
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
-- Логировать запросы без индексов (даже если быстрые)
SET GLOBAL log_queries_not_using_indexes = 'ON';Постоянная настройка (my.cnf):
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1Анализ slow query log:
# Встроенная утилита — топ-10 медленных запросов
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
# Percona Toolkit — детальный анализ
pt-query-digest /var/log/mysql/slow.logpt-query-digest показывает:
Performance Schema — встроенная подсистема мониторинга с детальными метриками:
-- Проверка статуса
SHOW VARIABLES LIKE 'performance_schema';
-- ON — включена (по умолчанию в MySQL 5.6+)Использование индексов:
-- Статистика по каждому индексу
SELECT
object_schema AS db_name,
object_name AS table_name,
index_name,
count_read,
count_write,
count_fetch,
count_insert,
count_update,
count_delete
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'your_db'
ORDER BY count_read DESC;Неиспользуемые индексы:
-- Через sys schema (удобное представление)
SELECT * FROM sys.schema_unused_indexes
WHERE object_schema = 'your_db';Топ таблиц по I/O:
-- Наибольшее время ожидания I/O
SELECT * FROM sys.io_global_tables_by_wait_latency
LIMIT 10;sys Schema — набор представлений поверх Performance Schema для удобного анализа:
-- Топ медленных запросов
SELECT * FROM sys.statement_analysis ORDER BY avg_latency DESC LIMIT 10;
-- Таблицы с наибольшим количеством full scan
SELECT * FROM sys.schema_tables_with_full_table_scans;
-- Индексы с наибольшим количеством записи
SELECT * FROM sys.schema_index_statistics ORDER BY rows_selected DESC LIMIT 10;
-- Автогенерированные рекомендации по индексам
SELECT * FROM sys.schema_table_lock_waits LIMIT 10;EXPLAIN ANALYZE — основной инструмент диагностики конкретного запроса:
-- Фактическое выполнение с метриками
EXPLAIN ANALYZE
SELECT o.id, o.total, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.created_at > '2024-01-01'
ORDER BY o.total DESC
LIMIT 100;Ключевые метрики для анализа:
Сравнение с EXPLAIN: если rows в EXPLAIN значительно отличается от actual rows в ANALYZE — статистика устарела.
Уровень 1: Alerting (реальное время)
Уровень 2: Ежедневный обзор
Уровень 3: Еженедельный анализ
Уровень 4: Ежеквартальный аудит
Симптом: API endpoint отвечает 5+ секунд (ранее 50 мс)
Шаг 1: Найти проблемный запрос
-- Проверить slow query log
pt-query-digest /var/log/mysql/slow.log | grep -A 20 "SELECT.*orders"
Шаг 2: Проанализировать план выполнения
EXPLAIN ANALYZE <проблемный запрос>;
Шаг 3: Определить причину
-- type: ALL на orders → нет индекса
-- rows: 5000000 → full table scan 5 млн строк
-- Extra: Using filesort → сортировка без индекса
Шаг 4: Создать индекс
CREATE INDEX idx_orders_user_created ON orders(user_id, created_at);
Шаг 5: Проверить результат
EXPLAIN ANALYZE <тот же запрос>;
-- Сравниваем время: было 5000 мс → стало 50 мс
| Инструмент | Назначение |
|---|---|
| Slow query log | Логирование медленных запросов |
| mysqldumpslow | Базовый анализ slow log |
| pt-query-digest | Детальный анализ slow log |
| Performance Schema | Детальные метрики I/O, блокировок |
| sys Schema | Удобные представления для анализа |
| EXPLAIN / EXPLAIN ANALYZE | План выполнения конкретного запроса |
| Prometheus + Grafana | Визуализация метрик, alerting |