Тюнінг продуктивності MySQL: налаштування InnoDB, кешів та запитів
Уявіть: інтернет-магазин на 50 000 замовлень на день, MySQL з дефолтною конфігурацією — innodb_buffer_pool_size = 128M, увімкнений query_cache, max_connections = 151. Результат: аварії кожні 2 години, p95 latency 450 мс. Після тюнінгу — 85 мс, у 5,3 рази швидше. Наші інженери з 10-річним досвідом і 500+ проектами налаштовують InnoDB, кеші та запити під ваше навантаження. p95 latency знизився з 450 мс до 85 мс — в 5,3 рази. Правильне налаштування buffer pool підвищує hit rate до 99,4%. В одному проекті продуктивність запису зросла в 3 рази після збільшення redo log.
Як налаштувати InnoDB buffer pool?
InnoDB — основний двигун MySQL. Його buffer pool — головний кеш сторінок даних та індексів. Як зазначено в MySQL Performance Tuning Guide, він має займати 70–80% RAM на виділеному сервері. Приклад конфігурації для сервера з 32 ГБ RAM:
[mysqld] innodb_buffer_pool_size = 24G innodb_buffer_pool_instances = 24 innodb_buffer_pool_dump_at_shutdown = ON innodb_buffer_pool_load_at_startup = ON innodb_log_file_size = 1G innodb_log_files_in_group = 2 innodb_log_buffer_size = 64M innodb_flush_method = O_DIRECT innodb_read_io_threads = 8 innodb_write_io_threads = 8 innodb_io_capacity = 2000 innodb_io_capacity_max = 4000 innodb_adaptive_flushing = ON innodb_use_native_aio = ON max_connections = 500 thread_cache_size = 50 thread_stack = 256K wait_timeout = 300 open_files_limit = 65535 table_open_cache = 4000 table_definition_cache = 2000 Перевірка ефективності через hit rate:
SELECT (1 - (SELECT variable_value FROM information_schema.global_status WHERE variable_name = 'Innodb_buffer_pool_reads') / (SELECT variable_value FROM information_schema.global_status WHERE variable_name = 'Innodb_buffer_pool_read_requests')) * 100 AS hit_rate_pct; Ціль: hit rate >99%. Якщо нижче — збільшуємо buffer pool або оптимізуємо індекси. У типовому проекті hit rate підіймається з 87% до 99.4%.
Чому query cache потрібно вимкнути?
query_cache в MySQL 5.7 і нижче — це м'ютекс на весь кеш при будь-якому записі. На high-load сайтах до 40% часу CPU йде на query_cache_mutex. У MySQL 8.0 його видалили. Кешування на рівні застосунку (Redis, Memcached) — правильне рішення. Вимкнути в конфігу:
query_cache_type = 0 query_cache_size = 0 Як знаходити та оптимізувати повільні запити?
Увімкніть slow query log і аналізуйте через pt-query-digest. Типові проблеми: full table scan, відсутність індексів, сортування без індексу. У slow log потрапляють запити, що виконуються довше 1 секунди.
slow_query_log = ON slow_query_log_file = /var/log/mysql/slow.log long_query_time = 1 log_queries_not_using_indexes = ON min_examined_row_limit = 100 Команда для аналізу:
pt-query-digest /var/log/mysql/slow.log --limit 20 --output report > /tmp/slow_report.txt Використовуйте EXPLAIN FORMAT=JSON для аналізу. Шукайте "access_type": "ALL" (full table scan) і "using_filesort": true. Створіть складовий індекс, наприклад: ALTER TABLE orders ADD INDEX idx_status_date (status, created_at DESC);.
Як налаштувати redo log для максимальної продуктивності запису?
Збільшення innodb_log_file_size (або innodb_redo_log_capacity в MySQL 8.0) зменшує частоту checkpoint, що підвищує пропускну здатність запису. На high-load сайтах це знижує затримки та збільшує throughput транзакцій. В одному з проектів після збільшення log file size з 256M до 1G продуктивність запису зросла в 3 рази.
Порівняння до і після тюнінгу
| Параметр | До тюнінгу | Після тюнінгу |
|---|---|---|
| innodb_buffer_pool_size | 128M | 24G |
| Hit rate | 87% | 99.4% |
| Query cache | Увімкнено (40% CPU на mutex) | Вимкнено |
| Slow queries >1s / min | 300 | 5 |
| p95 latency API | 450 ms | 85 ms |
| Тип запиту | До тюнінгу | Після тюнінгу |
|---|---|---|
| SELECT heavy join >1M rows | 12 с | 0,7 с |
| INSERT з тригерами | 200 оп/с | 1 200 оп/с |
Процес тюнінгу: як ми працюємо
- Аудит конфігурації: перевірка my.cnf, buffer pool, redo log, буферів, I/O. Фіксуємо поточні показники через Performance Schema.
- Аналіз slow log: завантажуємо логи за тиждень, прогоняємо через pt-query-digest, вибираємо топ-20 за часом. Для кожного будуємо EXPLAIN.
- Налаштування параметрів: зміна buffer pool, redo log, буферів, підключення, I/O. Перезапуск MySQL у вікно обслуговування.
- Оптимізація запитів: створення/доробка індексів, переписування важких JOIN, додавання кешування.
- Моніторинг: розгортання Performance Schema, налаштування сповіщень по slow queries і hit rate. Документація зі звітом.
Що входить у роботу
- Аудит конфігурації: перевірка mysql config, buffer pool, redo log, буферів, I/O.
- Аналіз slow log: виявлення топ-20 важких запитів, рекомендації щодо індексів.
- Налаштування продуктивності: зміна параметрів, оптимізація індексів, реструктуризація запитів.
- Моніторинг: розгортання Performance Schema, скрипти для регулярного контролю.
- Документація: звіт зі змінами, обґрунтуванням і результатами замірів.
- Підтримка: 2 тижні після тюнінгу для коригування.
Економія від тюнінгу
У середньому клієнти заощаджують суттєву суму на місяць після оптимізації. Це не лише зниження витрат на інфраструктуру, але й зростання швидкості роботи застосунку, що прямо впливає на конверсію. Наші інженери розрахують економію для вашого проекту персонально.
Для отримання аналогічного результату зв'яжіться з нами — ми проведемо тюнінг вашого MySQL. Замовте оптимізацію — ми гарантуємо вимірний результат. Отримайте консультацію: ми оцінимо ваш проект за 1-2 дні.