Тюнинг производительности MySQL (innodb_buffer_pool, query_cache)

Тюнинг производительности MySQL (innodb_buffer_pool, query_cache)

MySQL с дефолтной конфигурацией — это innodb_buffer_pool_size = 128M на сервере с 64 ГБ ОЗУ. Добавляется убитый query_cache (который в MySQL 8.0 удалён полностью), max_connections = 151 при сотнях активных пользователей. Тюнинг — это последовательная работа с памятью, дисковым I/O и планировщиком запросов.

InnoDB Buffer Pool: главный параметр

Buffer pool — это кэш страниц данных и индексов InnoDB в памяти. Аналог shared_buffers в PostgreSQL. Чем больше рабочий набор данных помещается в память, тем реже MySQL идёт на диск.

# /etc/mysql/conf.d/performance.cnf
[mysqld]

# 70-80% RAM для выделенного сервера
# Для сервера 32 GB RAM:
innodb_buffer_pool_size = 24G

# Несколько инстансов buffer pool — снижают contention при параллельном доступе
# 1 инстанс на каждый 1 ГБ, минимум 1, максимум 64
innodb_buffer_pool_instances = 24

# Прогрев buffer pool при перезапуске (MySQL 5.7+)
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup  = ON

Проверка эффективности buffer pool:

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';

-- Ключевые метрики:
-- Innodb_buffer_pool_reads       — чтения с диска (хочем минимума)
-- Innodb_buffer_pool_read_requests — всего запросов к buffer pool
-- Hit rate = (1 - reads/read_requests) * 100
-- Цель: > 99%

SELECT
    (1 - (variable_value / (
        SELECT variable_value
        FROM information_schema.global_status
        WHERE variable_name = 'Innodb_buffer_pool_read_requests'
    ))) * 100 AS hit_rate_pct
FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_reads';

InnoDB Redo Log

# Большие redo log файлы = реже checkpoint = выше пропускная способность записи
# MySQL 5.7 и ниже: изменение требует остановки, удаления старых файлов, перезапуска
innodb_log_file_size = 1G    # 1-4 GB для высоконагруженных серверов
innodb_log_files_in_group = 2
innodb_log_buffer_size = 64M

# MySQL 8.0.30+: динамический redo log, innodb_redo_log_capacity
innodb_redo_log_capacity = 4G

Query Cache (MySQL 5.7 и ниже)

# Query cache — ОТКЛЮЧИТЬ. Это мьютекс на весь кэш при любой записи в таблицу.
# При высоком concurrency Query Cache становится узким местом.
query_cache_type = 0
query_cache_size = 0

В MySQL 8.0 query_cache удалён. Правильный кэш для веб-приложений — на уровне приложения (Redis, Memcached), а не на уровне MySQL.

Sort и Join буферы

# Буфер для файловой сортировки (ORDER BY без индекса)
sort_buffer_size = 4M

# Буфер для JOIN без индексов (избегать таких запросов!)
join_buffer_size = 4M

# Temp таблицы в памяти
tmp_table_size    = 256M
max_heap_table_size = 256M

# Буфер для чтения при full table scan
read_buffer_size       = 2M
read_rnd_buffer_size   = 4M

sort_buffer_size выделяется на каждое соединение при необходимости сортировки — не устанавливать слишком большим при большом max_connections.

Соединения и потоки

# Максимум соединений
max_connections = 500

# Кэш потоков — избегаем создания нового потока при каждом соединении
thread_cache_size = 50

# Размер стека потока (обычно не менять)
thread_stack = 256K

# Таймауты неактивных соединений
wait_timeout         = 300   # 5 минут
interactive_timeout  = 300

# Открытые файлы (таблицы, файлы данных)
open_files_limit      = 65535
table_open_cache      = 4000
table_definition_cache = 2000

Настройка I/O для SSD

# Метод flush — O_DIRECT обходит OS page cache (не двойное кэширование)
innodb_flush_method = O_DIRECT

# Параллельный I/O
innodb_read_io_threads  = 8
innodb_write_io_threads = 8
innodb_io_capacity      = 2000   # IOPS диска (SSD ~10000, NVMe ~100000)
innodb_io_capacity_max  = 4000

# Adaptive flushing — MySQL сам решает, когда агрессивнее сбрасывать dirty pages
innodb_adaptive_flushing = ON

# Native AIO (Linux)
innodb_use_native_aio = ON

Медленные запросы: включение логирования

# Медленные запросы
slow_query_log           = ON
slow_query_log_file      = /var/log/mysql/slow.log
long_query_time          = 1      # секунды (1 = запросы > 1 сек)
log_queries_not_using_indexes = ON  # даже быстрые без индексов
min_examined_row_limit   = 100    # игнорировать запросы, просмотревшие < 100 строк

Анализ через pt-query-digest:

pt-query-digest /var/log/mysql/slow.log \
  --limit 20 \
  --output report > /tmp/slow_report.txt

Планировщик: EXPLAIN FORMAT=JSON

EXPLAIN FORMAT=JSON
SELECT o.*, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'pending'
  AND o.created_at > DATE_SUB(NOW(), INTERVAL 7 DAY)
ORDER BY o.created_at DESC\G

-- Ищем: "access_type": "ALL" — full table scan, нужен индекс
-- "rows": 1000000 — большое число просмотренных строк
-- "using_filesort": true — сортировка без индекса

Оптимальный составной индекс для запроса выше:

ALTER TABLE orders
  ADD INDEX idx_status_date (status, created_at DESC);
-- Покрывает фильтр по status И сортировку по created_at

Мониторинг через Performance Schema

-- Топ запросов по суммарному времени выполнения
SELECT digest_text,
       count_star,
       ROUND(avg_timer_wait / 1e12, 3) AS avg_sec,
       ROUND(sum_timer_wait / 1e12, 3) AS total_sec
FROM performance_schema.events_statements_summary_by_digest
WHERE schema_name = 'mydb'
ORDER BY sum_timer_wait DESC
LIMIT 10;

-- Таблицы с наибольшим числом full scan
SELECT object_schema, object_name,
       count_read,
       count_full_scan
FROM performance_schema.table_io_waits_summary_by_table
WHERE object_schema = 'mydb'
ORDER BY count_full_scan DESC
LIMIT 10;

Типичный результат тюнинга

На сервере 32 ГБ ОЗУ с проектом Laravel (MySQL 5.7), до тюнинга:

• innodb_buffer_pool_size = 128M — hit rate 87%
• query_cache = ON — 40% времени CPU на mutex
• slow queries > 1s: 200–400 в минуту

После тюнинга (buffer pool до 24G, query_cache OFF, индексы по slow log):

• hit rate 99.4%
• slow queries > 1s: 3–7 в минуту
• p95 latency API снизился с 450ms до 85ms