MySQL / MariaDB грузит процессор на 100%: практический поиск тяжелых запросов, оптимизация индексов и тюнинг my.cnf

Ситуация, когда процесс mysqld утилизирует все доступные ядра процессора на 100%, парализует веб-приложения: страницы интернет-магазинов начинают открываться по 15–30 секунд, Nginx возвращает ошибки 504 Gateway Time-out, а очередь клиентских соединений лавинообразно растет.

В 90% случаев причина 100% CPU кроется не в нехватке физических ядер VDS, а в нескольких неотфильтрованных тяжелых запросах, выполняющих полное сканирование таблиц (Full Table Scan), неоптимальных сортировках во временных файлах на диске (Using filesort) или дефолтных параметрах my.cnf, рассчитанных на серверы двадцатилетней давности.

1. Симптомы 100% CPU и экстренная реанимация сервера (First Aid)

Когда нагрузка на процессор достигает 100%, в первую очередь необходимо локализовать, какие именно потоки выполняются прямо сейчас и вызывают ли они блокировку строк.

Экспресс-диагностика через консоль Linux:

# Смотрим распределение потоков процесса mysqld по ядрам
pidstat -t -p $(pidof mysqld) 1 5

# Проверяем задержки ввода-вывода (I/O Wait)
iostat -xz 1 5

Просмотр активных запросов в MySQL CLI:

-- Заходим в консоль MySQL
SHOW FULL PROCESSLIST;

-- Либо выборка только активных запросов, выполняющихся дольше 2 секунд:
SELECT id, user, host, db, command, time, state, LEFT(info, 100) AS query 
FROM information_schema.processlist 
WHERE command != 'Sleep' AND time > 2 
ORDER BY time DESC;

Экстренное завершение зависшего запроса (Kill Query):

Если один «дикий» аналитический запрос положил весь продакшен, снимите его выполнение по ID из Processlist:

-- Прервать выполнение только запроса (соединение остается живым):
KILL QUERY 148290;

-- Либо принудительно разорвать соединение с клиентом:
KILL 148290;

2. Включение и глубокий анализ Slow Query Log

Чтобы не ловить медленные запросы вручную в реальном времени, настройте встроенный Slow Query Log. Он с миллисекундной точностью протоколирует все запросы, превышающие заданный порог времени или не использующие индексы.

Конфигурация журнала в my.cnf:

Откройте конфигурационный файл /etc/mysql/mysql.conf.d/mysqld.cnf (или /etc/my.cnf в CentOS/AlmaLinux) и добавьте в секцию [mysqld]:

[mysqld]
# Включение журнала медленных запросов
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log

# Порог срабатывания: логировать все запросы дольше 0.5 секунды
long_query_time = 0.5

# Логировать запросы без индексов (даже если они выполняются быстрее порога)
log_queries_not_using_indexes = 1

# Игнорировать таблицы с количеством строк менее 100
min_examined_row_limit = 100

Включение на лету без перезапуска службы MySQL:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.5;
SET GLOBAL log_queries_not_using_indexes = 'ON';

Агрегация и поиск главных виновников через mysqldumpslow:

Сырой лог может содержать миллионы строк. Используйте утилиту mysqldumpslow для группировки одинаковых запросов по шаблону:

# Топ-10 самых частых медленных запросов (сортировка по количеству вызовов)
mysqldumpslow -s c -t 10 /var/log/mysql/mysql-slow.log

# Топ-10 запросов, съевших больше всего суммарного процессорного времени
mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log

# Топ-10 запросов с наибольшим средним числом просканированных строк
mysqldumpslow -s r -t 10 /var/log/mysql/mysql-slow.log

3. Чтение плана выполнения EXPLAIN: поиск Full Table Scan

Получив проблемный SQL-запрос, подставьте перед ним команду EXPLAIN (а в MySQL 8.0+ — EXPLAIN ANALYZE), чтобы увидеть, как оптимизатор СУБД строит план выполнения.

EXPLAIN SELECT o.id, o.created_at, u.name 
FROM orders o 
JOIN users u ON o.user_id = u.id 
WHERE o.status = 'paid' AND o.created_at >= '2026-01-01' 
ORDER BY o.created_at DESC LIMIT 20;

Ключевые колонки вывода EXPLAIN:

Типичные ошибки в SQL-запросах, убивающие индексы:

• Обертывание индексированного поля в функцию:
  ❌ WHERE DATE(created_at) = '2026-08-23' (индекс B-Tree не используется, так как функция вычисляется для каждой строки).
  ✅ WHERE created_at >= '2026-08-23 00:00:00' AND created_at <= '2026-08-23 23:59:59'.
• Неявное приведение типов (Type Casting):
  ❌ WHERE phone = 79991234567 (если поле phone имеет тип VARCHAR, MySQL преобразует каждую строку таблицы в число, отключая индекс).
  ✅ WHERE phone = '79991234567'.
• Поиск по подстроке с ведущим процентом:
  ❌ WHERE title LIKE '%сервер%' (B-Tree индекс ищет только по началу строки).
  ✅ Использовать полнотекстовый индекс FULLTEXT(title) и конструкцию MATCH(title) AGAINST('сервер').

4. Проектирование правильных B-Tree и покрывающих индексов

Если запрос фильтрует по нескольким полям и сортирует результат, одиночных индексов на каждую колонку недостаточно. Требуется составной индекс (Composite Index), созданный по правилу левого префикса:

Формула составного индекса: (Равенства ➔ Диапазоны ➔ Сортировка)

Пример оптимизации медленного запроса заказов:

-- Запрос:
SELECT id, user_id, amount, created_at 
FROM orders 
WHERE status = 'completed' AND created_at >= '2026-01-01' 
ORDER BY created_at DESC;

-- ❌ Плохой индекс (одиночный):
ALTER TABLE orders ADD INDEX idx_status (status);

-- ✅ Идеальный составной покрывающий индекс:
ALTER TABLE orders ADD INDEX idx_status_created_covering (status, created_at, user_id, amount, id);

5. Тюнинг my.cnf под объем RAM: буферы InnoDB и временные таблицы

После оптимизации структуры запросов необходимо выделить СУБД достаточный объем оперативной памяти. Главное правило: активные данные и индексы должны целиком помещаться в RAM.

Эталонные параметры для файла /etc/mysql/mysql.conf.d/mysqld.cnf:

[mysqld]
# -------------------------------------------------------------
# 1. ПУЛ БУФЕРОВ INNODB (Ключевой параметр производительности)
# Выделяйте 60-70% от всей RAM для выделенного сервера БД,
# либо 40-50%, если на VDS также работают Nginx и PHP-FPM.
# -------------------------------------------------------------
innodb_buffer_pool_size = 4G          # Для сервера с 8 GB RAM
innodb_buffer_pool_instances = 4      # 1 инстанс на каждый 1 GB пула
innodb_log_file_size = 512M           # 25% от размера buffer_pool
innodb_log_buffer_size = 64M

# -------------------------------------------------------------
# 2. ОПТИМИЗАЦИЯ ДИСКОВОГО ВВОДА-ВЫВОДА (I/O)
# -------------------------------------------------------------
# Значение 2 дает прирост скорости записи в 5-10 раз на NVMe VDS:
# сброс на диск происходит раз в секунду, а не на каждую транзакцию.
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_io_capacity = 2000             # Для быстрых NVMe SSD
innodb_io_capacity_max = 4000
innodb_file_per_table = 1

# -------------------------------------------------------------
# 3. ВРЕМЕННЫЕ ТАБЛИЦЫ В ПАМЯТИ (Предотвращение Disk I/O)
# -------------------------------------------------------------
tmp_table_size = 128M
max_heap_table_size = 128M

# -------------------------------------------------------------
# 4. БУФЕРЫ НА СОЕДИНЕНИЕ (Осторожно, умножаются на max_connections!)
# -------------------------------------------------------------
max_connections = 150
join_buffer_size = 2M
sort_buffer_size = 2M
read_rnd_buffer_size = 1M

# -------------------------------------------------------------
# 5. КЭШ ОТКРЫТЫХ ТАБЛИЦ И ПОТОКОВ
# -------------------------------------------------------------
table_open_cache = 4000
table_definition_cache = 2000
thread_cache_size = 50

Сводная таблица сайзинга innodb_buffer_pool_size под тариф VDS:

6. Автоматический аудит базы через MySQLTuner

Перед ручной правкой конфига рекомендуется дать серверу проработать под рабочей нагрузкой минимум 24–48 часов, после чего запустить скрипт MySQLTuner:

# Скачивание и запуск официального скрипта аудита
wget http://mysqltuner.pl/ -O mysqltuner.pl
chmod +x mysqltuner.pl
perl mysqltuner.pl --user root --password "ВАШ_ПАРОЛЬ"

На какие секции отчета обратить особое внимание:

• Storage Engine Statistics: убедитесь, что все таблицы переведены в InnoDB. Если остались устаревшие MyISAM — конвертируйте их через ALTER TABLE tbl_name ENGINE=InnoDB;.
• InnoDB Buffer Pool Hitrate: должен быть выше 99.5%. Если он ниже — объем буферного пула недостаточен и запросы постоянно уходят в чтение с диска.
• Temporary Tables: процент временных таблиц, созданных на диске (Created_tmp_disk_tables), должен быть менее 15%. Если больше — увеличивайте tmp_table_size и max_heap_table_size.
• Table Cache Hitrate: если показатель ниже 80% — увеличивайте table_open_cache.

7. Когда оптимизации мало: требования к vCPU и дискам VDS

Если база данных превышает 50–100 ГБ, а поток транзакций превышает 500+ запросов в секунду, даже идеально оптимизированные B-Tree индексы требуют высокой параллельности вычислений и быстрого дискового ввода-вывода.

8. Итоговый чек-лист оптимизации MySQL/MariaDB