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

0
10621
Обложка статьи: Оптимизация производительности 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. Есть запись, что меняли и зачем.

Источники

Комментарии закрыты.