Оптимизация MySQL 8 и MariaDB, MySQLTuner

Производительность MySQL и MariaDB на типичном сервере с сайтами упирается в несколько параметров. Главный из них — innodb_buffer_pool_size: сколько памяти InnoDB отдаёт под кэш данных и индексов. По умолчанию это 128 МБ. На сервере с 4–8 ГБ памяти база с таким кэшем постоянно читает диск. Дальше по важности идут размер redo-лога, max_connections, временные таблицы и журнал медленных запросов.

Самое частое решение: выделить отдельный файл с настройками, поднять buffer pool до разумной доли памяти, включить slow query log и через сутки работы прогнать MySQLTuner. Его советы — подсказки, а не приказ. Часть из них на современных версиях вредна, ниже разобрано, какие.

Статья написана для MySQL 8.0 и 8.4 LTS, MariaDB 10.11 и 11.x на Debian 12/13, Ubuntu 24.04 и AlmaLinux / Rocky Linux 9 и 10. Старые советы из эпохи MySQL 5.x вроде query_cache_size и thread_concurrency на MySQL 8 не работают вовсе.

Какая СУБД стоит и где её конфиги

Сначала узнайте, что именно установлено. От этого зависят имена параметров и пути.

mysql --version          # или mariadb --version
sudo systemctl status mariadb mysql mysqld 2>/dev/null | grep -E 'Loaded|Active'

Система	СУБД из штатных репозиториев	Где настраивать сервер	Служба
Debian 12	MariaDB 10.11 (MySQL в Debian нет)	`/etc/mysql/mariadb.conf.d/50-server.cnf`	`mariadb`
Debian 13	MariaDB 11.8	`/etc/mysql/mariadb.conf.d/50-server.cnf`	`mariadb`
Ubuntu 24.04	MySQL 8.0 (`mysql-server`) или MariaDB 10.11 (`mariadb-server`)	`/etc/mysql/mysql.conf.d/mysqld.cnf` или `/etc/mysql/mariadb.conf.d/50-server.cnf`	`mysql` / `mariadb`
AlmaLinux / Rocky 9	MariaDB 10.5 или MySQL 8.0; новые версии — модулями	`/etc/my.cnf.d/mariadb-server.cnf` или `/etc/my.cnf.d/mysql-server.cnf`	`mariadb` / `mysqld`
AlmaLinux / Rocky 10	MariaDB 10.11 или MySQL 8.4	`/etc/my.cnf.d/`	`mariadb` / `mysqld`

В Debian и Ubuntu главный файл /etc/mysql/my.cnf — ссылка через alternatives на mariadb.cnf или mysql.cnf. Сам он почти пустой и подключает каталоги conf.d и mariadb.conf.d (или mysql.conf.d). В AlmaLinux /etc/my.cnf подключает /etc/my.cnf.d/. Если MySQL поставлен из репозитория Oracle, настройки обычно лежат прямо в /etc/my.cnf.

Штатные файлы лучше не править. При обновлении пакета менеджер спросит, что делать с изменённым конфигом, и правки легко потерять. Создайте свой файл, например /etc/mysql/mariadb.conf.d/90-tuning.cnf или /etc/my.cnf.d/90-tuning.cnf, с секцией [mysqld]. MariaDB понимает и секцию [mariadbd], но [mysqld] работает везде.

Если один параметр задан в нескольких файлах, действует последнее прочитанное значение. Проверить, что в итоге получит сервер:

# MariaDB
mariadbd --print-defaults
# MySQL
mysqld --print-defaults

Команда выводит все опции в порядке чтения. Если нужный параметр встречается дважды, сработает тот, что правее. Окончательная проверка — в самом сервере: SHOW VARIABLES LIKE 'innodb_buffer_pool_size';.

С чего начать: память и объём данных

Настройка без цифр — гадание. Нужны три числа: сколько на сервере памяти, сколько её едят остальные службы и сколько весят данные InnoDB.

free -h
sudo mysql -e "SELECT ROUND(SUM(data_length + index_length)/1024/1024) AS innodb_mb
  FROM information_schema.tables WHERE engine = 'InnoDB';"

На сервере с сайтами база делит память с Nginx или Apache, PHP-FPM, Redis. Посмотрите, сколько реально занимают пулы PHP-FPM в пике, и оставьте запас под кэш файловой системы. Как разложить память на VPS под WordPress, разобрано в статье Настройка VPS для быстрой работы сайта WordPress.

innodb_buffer_pool_size — главный параметр

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

Ориентиры:

выделенный сервер только под СУБД — 60–75% памяти;
сервер «всё в одном» (веб + PHP + база) — 25–40% памяти, но не больше, чем реально останется после PHP-FPM;
если данные InnoDB весят меньше этих цифр, хватит объёма данных плюс 20–30% на рост.

[mysqld]
innodb_buffer_pool_size = 2G

Параметр можно менять без перезапуска, сервер изменит размер в фоне:

SET GLOBAL innodb_buffer_pool_size = 2147483648;
SHOW STATUS LIKE 'Innodb_buffer_pool_resize_status';

Значение из SET GLOBAL живёт до перезапуска, поэтому продублируйте его в конфиге. В MySQL 8 есть ещё SET PERSIST: он записывает значение в mysqld-auto.cnf в каталоге данных. Это удобно, но потом трудно понять, откуда взялась настройка. На сервере, который настраивают руками, лучше держать всё в одном файле.

Проверить, хватает ли кэша:

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';

Innodb_buffer_pool_reads — чтения, которые пришлось делать с диска. Innodb_buffer_pool_read_requests — все логические чтения. Если доля первых после прогрева больше 1%, кэша мало или есть запросы, которые читают таблицы целиком.

В MySQL есть innodb_dedicated_server = ON: сервер сам выставит buffer pool и redo-лог от объёма памяти. Включайте его только там, где кроме MySQL ничего нет. На сервере с сайтами он заберёт слишком много.

Redo-лог: innodb_redo_log_capacity и innodb_log_file_size

Redo-лог — журнал изменений InnoDB. Изменения сначала пишутся в него, а страницы в табличные файлы сбрасываются позже. Маленький лог заставляет сервер часто делать принудительные checkpoint, и на записи появляются провалы производительности.

Имена параметров зависят от СУБД и версии:

MySQL 8.0.30 и новее, 8.4 — innodb_redo_log_capacity, по умолчанию 100 МБ. Меняется на лету через SET GLOBAL. Старые innodb_log_file_size и innodb_log_files_in_group с 8.0.30 считаются устаревшими. Если они заданы, а новый параметр нет, ёмкость считается как их произведение.
MySQL до 8.0.30 — innodb_log_file_size × innodb_log_files_in_group.
MariaDB 10.5 и новее — один файл ib_logfile0, размер задаёт innodb_log_file_size (по умолчанию 96 МБ). В свежих версиях его тоже можно менять без перезапуска.

Практическое правило: лога должно хватать примерно на час записи в пиковое время. Измерьте, сколько пишется за минуту:

SHOW GLOBAL STATUS LIKE 'Innodb_os_log_written';
-- подождать 60 секунд
SHOW GLOBAL STATUS LIKE 'Innodb_os_log_written';

Разницу умножьте на 60. Для сайта-визитки или небольшого магазина значения по умолчанию обычно хватает. Для базы с активной записью ставят 512 МБ–2 ГБ. Чем больше лог, тем дольше восстановление после аварийной остановки, так что гигантские значения без нужды не ставьте.

# MySQL 8.0.30+ / 8.4
innodb_redo_log_capacity = 512M

# MariaDB 10.11 / 11.x
innodb_log_file_size = 512M

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

max_connections и память на соединение

По умолчанию max_connections = 151. Ошибка Too many connections соблазняет поставить 1000. Но каждое соединение может занять память под свои буферы: sort_buffer_size, join_buffer_size, read_buffer_size, read_rnd_buffer_size, временные таблицы. При всплеске нагрузки 1000 активных соединений легко съедят всю память, и к серверу придёт OOM killer.

Смотрите, сколько соединений было на самом деле:

SHOW GLOBAL STATUS LIKE 'Max_used_connections';
SHOW GLOBAL STATUS LIKE 'Threads_%';

Если Max_used_connections упирается в лимит, причина обычно не в лимите. Чаще это медленные запросы, которые держат соединения, или слишком большие пулы PHP-FPM: каждый воркер открывает своё соединение. Число воркеров PHP-FPM на всех сайтах плюс запас 20–30% — разумный потолок для max_connections.

thread_cache_size из старых руководств по-прежнему существует. В MySQL 8 он подбирается автоматически, в MariaDB по умолчанию достаточно большой. Трогать его стоит, только если Threads_created быстро растёт при стабильной нагрузке.

Для простоя есть wait_timeout: по умолчанию 28800 секунд (8 часов). Если приложение не закрывает соединения, уменьшите его до 300–600 секунд. Это лечит симптом, а не причину, но снимает лишние «спящие» сессии.

tmp_table_size и временные таблицы

Для GROUP BY, DISTINCT, UNION и сортировок сервер создаёт внутренние временные таблицы. Пока таблица маленькая, она живёт в памяти. Когда вырастает — уходит на диск, и запрос замедляется в разы.

SHOW GLOBAL STATUS LIKE 'Created_tmp%';

Если Created_tmp_disk_tables составляет больше 10–20% от Created_tmp_tables, есть смысл разбираться. Механизм в MySQL и MariaDB разный:

MySQL 8 использует для внутренних таблиц движок TempTable. Лимит одной таблицы в памяти — tmp_table_size. Общий лимит на все такие таблицы — temptable_max_ram: 1 ГБ в 8.0, в 8.4 — 3% памяти (от 1 до 4 ГБ). max_heap_table_size на внутренние таблицы TempTable не влияет, он ограничивает таблицы движка MEMORY.
MariaDB держит внутренние таблицы в движке MEMORY, а на диске — в Aria. Лимит — меньшее из tmp_table_size и max_heap_table_size. Поднимать нужно оба сразу.

tmp_table_size      = 64M
max_heap_table_size = 64M

Не ставьте здесь сотни мегабайт. Лимит действует на каждую временную таблицу, а их одновременно может быть много. Частая причина дисковых временных таблиц — не размер, а запросы без индексов или выборки с TEXT/BLOB. MEMORY в MariaDB такие столбцы держать не умеет, и таблица сразу уходит на диск. Лечится это правкой запроса и индексами.

Query cache: в MySQL 8 его нет, в MariaDB лучше не включать

Кэш запросов удалён в MySQL 8.0. Если перенести старый my.cnf со строкой query_cache_size на MySQL 8, сервер не запустится и напишет в журнал примерно так:

[ERROR] [MY-000067] [Server] unknown variable 'query_cache_size=64M'.

Уберите из конфига все query_cache_*.

В MariaDB query cache остался, но выключен по умолчанию: query_cache_type = OFF. Все обращения к нему идут через одну блокировку. На многоядерном сервере с параллельными запросами она тормозит сильнее, чем помогает кэш, к тому же любая запись в таблицу сбрасывает все кэшированные запросы к ней. Оставьте его выключенным:

query_cache_type = 0
query_cache_size = 0

Кэшировать выгоднее на уровне приложения: объектный кэш в Redis, страничный кэш, OPcache для PHP.

Журнал медленных запросов

Ни один параметр не спасёт от запроса, который читает миллион строк без индекса. Slow query log показывает такие запросы. По умолчанию он выключен.

# MySQL 8.0 / 8.4
slow_query_log      = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time     = 1

# MariaDB 10.11 / 11.x: новые имена, старые тоже работают
log_slow_query      = 1
log_slow_query_file = /var/log/mysql/mariadb-slow.log
log_slow_query_time = 1

Каталог для журнала должен существовать и принадлежать пользователю mysql. В AlmaLinux это /var/log/mariadb/ или /var/log/mysql/, при включённом SELinux свой путь потребует правильного контекста. Проще оставить журнал в штатном каталоге логов.

Включить без перезапуска, чтобы быстро посмотреть картину:

SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 1;

Новое значение long_query_time действует только для новых соединений. Сводку по журналу даёт mysqldumpslow (в MariaDB — mariadb-dumpslow):

sudo mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log

Дальше каждый тяжёлый запрос разбирают через EXPLAIN. Параметр log_queries_not_using_indexes включайте ненадолго: на CMS он забивает журнал тысячами мелких запросов.

Что из старых советов уже не нужно

skip-external-locking — давно включено по умолчанию, писать в конфиг незачем.
thread_concurrency — удалён ещё в MySQL 5.7, в MariaDB объявлен устаревшим. MySQL 8 с этой строкой не запустится.
low_priority_updates — влияет только на движки с табличными блокировками (MyISAM, MEMORY). Для InnoDB бесполезен.
key_buffer_size — кэш индексов MyISAM. Если таблиц MyISAM нет, хватит 8–32 МБ.
table_cache — давно переименован в table_open_cache. Повышать его стоит, если быстро растёт Opened_tables.
skip-innodb — совет из старых версий MySQLTuner. Сейчас InnoDB — основной движок, системные таблицы MySQL 8 сами хранятся в InnoDB.

MySQLTuner: установка и запуск

MySQLTuner — Perl-скрипт, который читает переменные и статистику сервера и выдаёт рекомендации. Он ничего не меняет сам.

Установка из репозиториев:

# Debian, Ubuntu
sudo apt install mysqltuner

# AlmaLinux / Rocky: пакет в EPEL
sudo dnf install epel-release
sudo dnf install mysqltuner

О подключении EPEL подробнее — в статье Репозитории EPEL и Remi для AlmaLinux и Rocky Linux. В репозиториях дистрибутивов версия скрипта часто старая и не знает про новые MySQL и MariaDB. Свежую берут из репозитория проекта:

wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
perl mysqltuner.pl --help | head

Запуск. В Debian и Ubuntu root входит в базу через сокет без пароля, поэтому достаточно sudo:

sudo mysqltuner
# или скачанная версия
sudo perl mysqltuner.pl
# если root с паролем
perl mysqltuner.pl --user root --pass 'пароль'

Пароль в командной строке попадёт в историю shell и будет виден в списке процессов. Лучше положите его в ~/.my.cnf с правами 600, скрипт его прочитает.

Запускайте не раньше, чем через 24 часа после перезапуска СУБД, а лучше через несколько дней. Сразу после старта статистика пустая, и скрипт честно предупредит: MySQL started within last 24 hours - recommendations may be inaccurate.

Как читать советы MySQLTuner

Вывод разбит на блоки. Строки с [OK] — норма, [!!] — проблема, [--] — информация. Сначала смотрите блок о памяти, примерно так:

[--] Physical Memory     : 7.8G
[OK] Maximum reached memory usage: 2.9G (37.4% of installed RAM)
[!!] Maximum possible memory usage: 9.1G (117.0% of installed RAM)

«Maximum possible» — это расчёт на случай, если все max_connections одновременно займут все буферы. Больше 100% — повод снизить max_connections или буферы на соединение. Реальный пик — строка «Maximum reached».

В конце идут блоки General recommendations и Variables to adjust. Как к ним относиться:

Совет	Что значит	Что делать
`innodb_buffer_pool_size (>= 4G)`	Данные не помещаются в кэш	Поднять, если позволяет память. Если нет — сначала разобраться с лишними данными и индексами
`join_buffer_size (> 256.0K, or always use indexes with JOINs)`	Есть JOIN без индексов	Буфер не трогать. Найти запросы в slow log и добавить индексы
`tmp_table_size (> 16M)`, `max_heap_table_size (> 16M)`	Много временных таблиц на диске	Поднять до 32–64M, затем смотреть запросы с TEXT/BLOB и без индексов
`query_cache_type (=0)`	В MariaDB включён query cache	Согласиться и выключить
Ratio InnoDB log file size / buffer pool	Скрипт хочет redo-лог около 25% от buffer pool	Ориентир, а не правило. Считайте по объёму записи
`Run OPTIMIZE TABLE to defragment tables`	В таблицах есть пустое место	Для InnoDB это полное перестроение таблицы с нагрузкой на диск. Делать точечно для таблиц после массового удаления, в тихое время
`Reduce your overall MySQL memory footprint`	Возможный расход памяти больше доступной	Снизить `max_connections` и буферы на соединение
`Restrict Host for 'user'@'%'`, анонимные пользователи	Учётки открыты для любого адреса	Ограничить хосты, удалить лишних пользователей
`table_open_cache (> 4000)`	Сервер часто открывает таблицы заново	Поднимать постепенно, проверить `open_files_limit`

Правило работы: одно изменение за раз, перезапуск или SET GLOBAL, несколько дней наблюдения, снова MySQLTuner. Если поменять десять параметров сразу, вы не узнаете, какой помог, а какой навредил. Советы по безопасности из того же вывода разобраны в статье Настройка минимальной безопасности MySQL после установки.

Пример: VPS 4 ГБ с сайтами и базой

Отправная точка для сервера «всё в одном» с WordPress или другой CMS. Это не готовый ответ, а значения, от которых удобно отталкиваться.

# /etc/mysql/mariadb.conf.d/90-tuning.cnf (MariaDB 10.11 / 11.x)
[mysqld]
innodb_buffer_pool_size = 1G
innodb_log_file_size    = 256M
max_connections         = 100
tmp_table_size          = 64M
max_heap_table_size     = 64M
table_open_cache        = 2000
query_cache_type        = 0
query_cache_size        = 0
log_slow_query          = 1
log_slow_query_time     = 1

# /etc/mysql/mysql.conf.d/zz-tuning.cnf (MySQL 8.0 / 8.4 на Ubuntu)
[mysqld]
innodb_buffer_pool_size  = 1G
innodb_redo_log_capacity = 256M
max_connections          = 100
tmp_table_size           = 64M
max_heap_table_size      = 64M
table_open_cache         = 2000
slow_query_log           = 1
long_query_time          = 1

Применение и проверка

# проверить синтаксис и итоговые опции
sudo mariadbd --print-defaults     # или mysqld --print-defaults

# перезапуск
sudo systemctl restart mariadb     # Debian, Ubuntu, AlmaLinux с MariaDB
sudo systemctl restart mysql       # Ubuntu с MySQL
sudo systemctl restart mysqld      # AlmaLinux с MySQL

# если не стартует
sudo journalctl -u mariadb -n 50 --no-pager

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

sudo mysql -e "SHOW VARIABLES WHERE Variable_name IN
  ('innodb_buffer_pool_size','max_connections','tmp_table_size','long_query_time');"

Неизвестный параметр или опечатка в имени — самая частая причина, почему служба не стартует после правки. Сообщение об этом будет в журнале systemd или в файле ошибок СУБД.

Симптомы и решения

Симптом	Вероятная причина	Решение
Сайт тормозит, диск постоянно занят чтением	Мал `innodb_buffer_pool_size`	Поднять buffer pool, проверить `Innodb_buffer_pool_reads`
Периодические провалы при массовой записи	Мал redo-лог	Увеличить `innodb_redo_log_capacity` или `innodb_log_file_size`
`Too many connections`	Медленные запросы держат соединения, большие пулы PHP-FPM	Slow log, согласовать `max_connections` с воркерами PHP-FPM
MySQL убит OOM killer	Buffer pool плюс буферы соединений больше памяти	Снизить buffer pool или `max_connections`, проверить отчёт MySQLTuner
Не стартует после переноса конфига на MySQL 8	`query_cache_*`, `thread_concurrency` и другие удалённые параметры	Убрать их, смотреть `journalctl`
Много `Created_tmp_disk_tables`	Запросы с TEXT/BLOB, нет индексов, мал лимит	Индексы, правка запросов, затем `tmp_table_size`

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

Сколько памяти отдать под innodb_buffer_pool_size?

На выделенном сервере базы — 60–75% памяти. На сервере, где рядом работают веб-сервер и PHP-FPM, — столько, сколько остаётся после них с запасом, обычно 25–40%. Если все данные InnoDB занимают меньше, хватит их объёма плюс запас на рост.

Как включить query cache в MySQL 8?

Никак: кэш запросов удалён в MySQL 8.0, и строки query_cache_* в конфиге не дадут серверу запуститься. Кэшировать нужно на уровне приложения. В MariaDB он есть, но под параллельной нагрузкой обычно вредит, поэтому выключен по умолчанию.

Можно ли применять все советы MySQLTuner подряд?

Нет. Скрипт видит статистику, но не видит запросы и приложение. Советы поднять join_buffer_size или сделать OPTIMIZE TABLE часто маскируют отсутствие индексов. Меняйте по одному параметру и проверяйте результат.

Где лежит my.cnf в Debian и Ubuntu?

/etc/mysql/my.cnf — ссылка на общий файл, который подключает каталоги. Настройки сервера MariaDB — в /etc/mysql/mariadb.conf.d/50-server.cnf, MySQL в Ubuntu — в /etc/mysql/mysql.conf.d/mysqld.cnf. Свои параметры удобнее держать в отдельном файле в том же каталоге.

Нужно ли перезапускать сервер после изменения параметров?

Многие параметры, включая innodb_buffer_pool_size, max_connections, long_query_time и innodb_redo_log_capacity в MySQL 8.0.30+, меняются через SET GLOBAL на лету. Но такое значение живёт до перезапуска, поэтому его надо продублировать в конфиге. Статические параметры вступают в силу только после перезапуска.

Чем MySQLTuner отличается от pt-query-digest?

MySQLTuner смотрит на сервер целиком: память, кэши, счётчики. Утилиты разбора slow log, такие как mysqldumpslow или pt-query-digest из Percona Toolkit, показывают конкретные тяжёлые запросы. Для настройки нужны оба взгляда.