Оптимизация MySQL: тюнинг my.cnf под ресурсы VPS

MySQL и MariaDB при установке используют скромные лимиты памяти, рассчитанные на любое железо. На VPS с NVMe это означает, что движок InnoDB упирается в диск там, где мог бы работать из RAM. Пройдёмся по ключевым параметрам my.cnf и настроим сервер под реальную конфигурацию.

Где искать конфиг и как проверить движок

MySQL читает несколько файлов конфигурации. Посмотреть порядок и активные пути можно так:

mysqld --verbose --help | grep -A1 'Default options'
# частые пути: /etc/mysql/my.cnf, /etc/mysql/mysql.conf.d/, /etc/my.cnf

Свои правки кладите в отдельный файл, чтобы обновление пакета не затёрло их:

sudo nano /etc/mysql/mysql.conf.d/tuning.cnf

Убедитесь, что таблицы используют InnoDB — именно его буферы мы будем настраивать. MyISAM в 2020-х уместен разве что для узких сценариев.

SELECT table_name, engine FROM information_schema.tables
WHERE table_schema = DATABASE();

Главный параметр: innodb_buffer_pool_size

Buffer pool — это кэш данных и индексов InnoDB в памяти. Он даёт самый заметный прирост: чем больше горячих данных помещается в RAM, тем реже сервер идёт на диск.

• Для выделенного под БД сервера — 60–70% RAM.
• Если на сервере ещё крутится веб-приложение — оставьте запас, начните с 40–50%.

Для VPS на 8 ГБ рабочая отправная точка:

# /etc/mysql/mysql.conf.d/tuning.cnf
[mysqld]
innodb_buffer_pool_size = 5G
innodb_buffer_pool_instances = 4
innodb_log_file_size = 1G
innodb_log_buffer_size = 32M

Разбиение пула на instances уменьшает конкуренцию за блокировки при многопоточной нагрузке, а увеличенный log_file_size снижает частоту сброса журнала.

Запись, flush и NVMe

Параметр innodb_flush_log_at_trx_commit управляет компромиссом надёжность/скорость. Значение 1 — максимальная надёжность (fsync на каждый коммит), 2 — заметно быстрее ценой возможной потери последней секунды при отказе ОС.

innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000

Соединения, кэши и таблицы

Каждое соединение потребляет память под буферы. Не раздувайте max_connections без нужды — лучше пул на стороне приложения.

max_connections = 150
thread_cache_size = 16
table_open_cache = 4000
tmp_table_size = 64M
max_heap_table_size = 64M

Важно: query cache в MySQL 8 удалён, а в старых версиях и MariaDB его чаще стоит отключить (query_cache_type = 0) — на нагруженной записи он превращается в узкое место из-за глобальной блокировки.

thread_cache_size хранит потоки соединений для повторного использования: при частых коротких подключениях это экономит время на их создании. table_open_cache задаёт, сколько дескрипторов таблиц держится открытыми — на схеме с сотнями таблиц заниженное значение приводит к постоянному переоткрытию файлов и росту нагрузки. tmp_table_size и max_heap_table_size стоит держать равными: если временная таблица в памяти превышает меньший из лимитов, MySQL сбрасывает её на диск, и запросы с GROUP BY и сложными сортировками резко замедляются.

Применение и диагностика

После правок перезапустите сервер и проверьте, что значения применились:

sudo systemctl restart mysql
mysql -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"

Включите лог медленных запросов — это главный источник данных для дальнейшей оптимизации схемы и индексов. Всё, что выполняется дольше long_query_time секунд, попадёт в лог, а разобрать его удобно утилитой mysqldumpslow, которая группирует похожие запросы:

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1

sudo mysqldumpslow -s t /var/log/mysql/slow.log | head -20

Помните, что тюнинг конфига даёт эффект лишь до определённого предела: если запрос делает полный скан таблицы без индекса, никакой размер buffer pool это не спасёт. Сначала находите тяжёлые запросы через slow log и команду EXPLAIN, добавляете нужные индексы и только потом крутите параметры памяти под реальный профиль нагрузки.

• buffer_pool на всю RAM — не остаётся памяти под соединения и ОС, приходит OOM.
• innodb_flush_log_at_trx_commit = 0 на проде — риск потерять до секунды транзакций.
• Высокий io_capacity на медленном диске — лишняя нагрузка без пользы.

Где развернуть MySQL

MySQL с InnoDB чувствителен к скорости диска и задержке fsync. NVMe у MAATRIX на процессорах AMD EPYC даёт быстрый flush журнала и прогрев buffer pool, а локации UK, США и РФ удобны для зарубежных проектов. Оплата картой РФ, СБП, криптой или токеном MAAT решает проблему с иностранными провайдерами, ежедневные бэкапы страхуют базу, а root-доступ даёт полный контроль над my.cnf.

Частые вопросы