Тюнинг производительности 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 дня.