Тюнінг продуктивності 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 при сотнях активних користувачів. Тюнінг — це послідовна робота з пам'яттю, дисковим вводом-виводом та планувальником запитів.

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

Buffer pool — це кеш сторінок даних та індексів InnoDB у пам'яті. Аналог shared_buffers у PostgreSQL. Чим більше робочий набір даних вміщується в пам'ять, тим рідше MySQL звертається до диска.

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

# 70-80% ОЗУ для виділеного сервера
# Для сервера з 32 ГБ ОЗУ:
innodb_buffer_pool_size = 24G

# Кілька інстансів buffer pool — знижують конфлікти при паралельному доступі
# 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 файли = рідше контрольні точки = вища пропускна здатність запису
# MySQL 5.7 та нижче: зміни потребують зупинки, видалення старих файлів, перезавантаження
innodb_log_file_size = 1G    # 1-4 ГБ для високонавантажених серверів
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 — ВИМКНУТИ. Це мьютекс на весь кеш при будь-якому записі у таблицю.
# При високому паралелізмі 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

# Тимчасові таблиці в пам'яті
tmp_table_size    = 256M
max_heap_table_size = 256M

# Буфер для читання під час повного сканування таблиці
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

Тюнінг вводу-виводу для SSD

# Метод flush — O_DIRECT обходить OS page cache (немає подвійного кеширування)
innodb_flush_method = O_DIRECT

# Паралельний вхід-вихід
innodb_read_io_threads  = 8
innodb_write_io_threads = 8
innodb_io_capacity      = 2000   # IOPS диска (SSD ~10000, NVMe ~100000)
innodb_io_capacity_max  = 4000

# Адаптивне очищення — MySQL вирішує, коли агресивніше очищати dirty pages
innodb_adaptive_flushing = ON

# Рідне 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" — повне сканування таблиці, потрібен індекс
-- "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;

-- Таблиці з найбільшою кількістю повних сканувань
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 на мьютекс
• повільні запити > 1s: 200–400 за хвилину

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

• hit rate 99.4%
• повільні запити > 1s: 3–7 за хвилину
• p95 latency API впав з 450ms до 85ms