Оптимизация производительности MySQL / MariaDB

MySQL/MariaDB тормозит из‑за буфера InnoDB, диска и запросов, а не потому что «в статье 2021 было 8G». Сначала измерьте, потом меняйте один параметр. Числа ниже — примеры, не норма для вашего железа.
1. Версия и что сейчас
mysql --version
mysql -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"
mysql -e "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';"
mysql -e "SHOW GLOBAL STATUS LIKE 'Threads_running';"
Ожидание: мажор 8.0 / 8.4 / MariaDB 10.11+, не 5.5. Документация своей ветки: MySQL 8.4 system variables или MariaDB KB.
2. Смотреть нагрузку, не копировать my.cnf
mysql -e "SHOW FULL PROCESSLIST;"
# медленные запросы — если включён slow log:
sudo tail -n 30 /var/log/mysql/mariadb-slow.log
Часто «тормозит база» — один запрос без индекса или диск на 100%. Тогда innodb_buffer_pool_size не спасёт. Путь slow log и имя файла зависят от пакета — смотрите SHOW VARIABLES LIKE 'slow_query%'.
3. Менять один параметр
Типичный рычаг — innodb_buffer_pool_size. На выделенном сервере БД его часто ставят порядка половины–трёх четвертей RAM, но только если на машине нет тяжёлого PHP/Java. На том же хосте, что и приложение, не отдавайте базе всю память.
sudo cp /etc/mysql/my.cnf /etc/mysql/my.cnf.bak.$(date +%F)
# или /etc/my.cnf.d/server.cnf на RHEL
В секции [mysqld] одна строка, пример:
innodb_buffer_pool_size = 2G
sudo systemctl restart mysql
# или mariadb / mysqld — как зовут юнит
mysql -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"
Ожидание: значение стало тем, что задали (в байтах). Не копируйте чужой файл целиком: имена переменных и умолчания 5.7/2021 на 8.4 не переносятся.
Включить журнал медленных запросов
Без него вы не узнаете, какой запрос замедляет систему. В секции [mysqld]:
slow_query_log = 1
long_query_time = 1
log_queries_not_using_indexes = 0
Порог в секунду подходит для начала, потом уменьшайте. Включение log_queries_not_using_indexes заваливает журнал, используйте его только временно. Вывод разбирайте утилитой mysqldumpslow или pt-query-digest: они группируют одинаковые запросы.
Понять, почему запрос медленный
EXPLAIN SELECT * FROM orders WHERE customer_id = 42 AND status = 'new';
Смотрите в вывод колонки type и key. Значение ALL означает полный перебор таблицы: нужен индекс по условиям отбора. Создавайте индекс под реальные запросы, а не «на всякий случай»: лишние индексы замедляют запись.
CREATE INDEX idx_orders_customer_status ON orders (customer_id, status);
Создание индекса на большой таблице блокирует или нагружает базу. Делайте его вне пика и на копии сначала.
Соединения
SHOW VARIABLES LIKE 'max_connections';
SHOW GLOBAL STATUS LIKE 'Max_used_connections';
SHOW GLOBAL STATUS LIKE 'Threads_connected';
Если Max_used_connections приближается к max_connections, приложение не закрывает соединения или нужен пул соединений. Простое увеличение лимита забирает память и маскирует проблему.
Надёжность и параметры записи
innodb_flush_log_at_trx_commit = 1(по умолчанию) гарантирует сохранность подтверждённых транзакций при сбое питания. Значения 0 и 2 быстрее, но допускают потерю последних секунд данных: меняйте только осознанно.- Резервная копия без остановки базы:
mysqldump --single-transaction --routines --triggers db > db.sqlдля InnoDB. Копирование файлов базы «на лету» даёт неработающую копию: резервное копирование. - Порт 3306 не должен быть открыт в интернет:
bind-address = 127.0.0.1или внутренний адрес, а доступ ограничьте firewall.
Следите за базой через мониторинг: число соединений, медленные запросы, свободное место: диски в Zabbix.
Как проверить
mysql -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"
systemctl is-active mysql
mysql -e "SHOW GLOBAL STATUS LIKE 'Threads_running';"
Служба жива, параметр тот, что выставили, медленный запрос либо исчез, либо вы его видите в PROCESSLIST. Есть запись, что меняли и зачем.
Источники
- MySQL 8.4, Server System Variables. URL: https://dev.mysql.com/doc/refman/8.4/en/server-system-variables.html.
- MariaDB KB. URL: https://mariadb.com/kb/en/server-system-variables/.













