Значення MySQL за замовчуванням розраховані на те, що сервер ділить машину з іншими програмами: буферний пул 128 МБ, журнал повтору 100 МБ. Для виділеного сервера бази це в рази менше, ніж треба.
innodb_dedicated_server = ON (у файлі конфігурації, не на льоту) - InnoDB сам визначає ключові параметри за ресурсами машини. У MySQL 8.4 це два параметри:
innodb_buffer_pool_size:
| Пам'ять сервера | Буферний пул |
|---|---|
| менше 1 ГБ | 128 МБ |
| 1-4 ГБ | 50% пам'яті |
| понад 4 ГБ | 75% пам'яті |
innodb_redo_log_capacity = (кількість логічних процесорів / 2) ГБ, максимум 16 ГБ.
У MySQL 8.0 ця опція ще й встановлювала innodb_flush_method, у 8.4 - вже ні. Якщо якийсь із параметрів явно задано в конфігурації, явне значення має пріоритет.
Пастка контейнерів. Опція бачить пам'ять, яку повідомляє система. У контейнері з лімітом пам'яті (Docker, Kubernetes) MySQL може побачити пам'ять хоста, і 75% від неї перевищить ліміт контейнера - OOM-kill. У контейнерах параметри краще задавати явно.
Що ще варто переглянути на виділеному сервері:
max_connections- за реальною кількістю процесів застосунку з запасом, не «про всяк випадок» тисячі;innodb_io_capacity/innodb_io_capacity_max- під можливості дисків (у 8.4 типове значення вже розраховане на SSD);innodb_flush_log_at_trx_commit = 1іsync_binlog = 1- лишити такими на основному сервері: це гарантія стійкості;binlog_expire_logs_seconds- щоб покривав інтервал між бекапами, але не з'їв диск;long_query_timeіslow_query_log- увімкнути з порогом, що має сенс для застосунку;max_execution_time- запобіжник дляSELECT, якщо застосунок не ставить власних тайм-аутів;table_open_cache,table_definition_cache- якщо таблиць тисячі (багатоорендні схеми);- пам'ять на з'єднання (
sort_buffer_size,join_buffer_size) - не збільшувати глобально без потреби: вони множаться на кількість активних з'єднань.
Як перевіряти ефект. Змінювати по одному параметру й вимірювати на реалістичному навантаженні. Популярні «оптимізовані конфіги» з інтернету часто містять параметри для старих версій, які на сучасному MySQL шкодять або вже видалені.
Залишок пам'яті після буферного пулу потрібен ОС (файловий кеш для бінарних журналів), з'єднанням, тимчасовим таблицям (TempTable), Performance Schema. Якщо на тому ж сервері ще й PHP чи Redis, innodb_dedicated_server вмикати не можна - він вважає, що вся пам'ять належить MySQL.
Докладніше в документації: Автоконфігурація для виділеного сервера