Де знаходиться файл my.cnf

MySQL/MariaDB читає конфігурацію з кількох місць. Порядок завантаження: /etc/mysql/my.cnf → /etc/mysql/mysql.conf.d/*.cnf → ~/.my.cnf. Для MariaDB основний файл: /etc/mysql/mariadb.conf.d/50-server.cnf.

# Перевірити, які файли читає MySQL:
mysqld --verbose --help 2>/dev/null | grep "Default options" -A 1

Ключові параметри InnoDB

Параметр	Рекомендація	Опис
`innodb_buffer_pool_size`	60–75% RAM	Головний параметр — буфер даних та індексів InnoDB
`innodb_log_file_size`	256M–1G	Розмір журналу транзакцій (більший = швидший запис)
`innodb_flush_log_at_trx_commit`	1 (safe) / 2 (fast)	1 = повна надійність, 2 = швидше, але ризик втрати 1 сек
`innodb_flush_method`	O_DIRECT	Виключає подвійне кешування з OS

Оптимальний my.cnf за обсягом RAM

Для 2 ГБ RAM:

[mysqld]
innodb_buffer_pool_size = 1G
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
max_connections = 100
thread_cache_size = 10
table_open_cache = 2000
tmp_table_size = 64M
max_heap_table_size = 64M
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

Для 8 ГБ RAM:

innodb_buffer_pool_size = 5G
innodb_buffer_pool_instances = 4
innodb_log_file_size = 512M
max_connections = 300
tmp_table_size = 128M

Перевірка використання InnoDB buffer pool

mysql -u root -p -e "
SELECT 
  FORMAT(@@innodb_buffer_pool_size/1024/1024/1024, 2) AS 'Buffer Pool (GB)',
  FORMAT(Innodb_buffer_pool_pages_data * @@innodb_page_size/1024/1024/1024, 2) AS 'Used (GB)'
FROM information_schema.GLOBAL_STATUS
WHERE Variable_name = 'Innodb_buffer_pool_pages_data';"

Query Cache (MySQL 5.7 і старіші)

У MySQL 8.0 Query Cache вилучено. У MySQL 5.7 та MariaDB його використання суперечливе: за високої інтенсивності запису він створює конкуренцію за блокування. Для високонавантажених сайтів рекомендується Redis/Memcached замість Query Cache.

Застосування налаштувань

sudo systemctl restart mysql
# або
sudo systemctl restart mariadb

# Перевірка
mysql -u root -p -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"

Використовуйте MySQLTuner — скрипт автоматично аналізує ваш сервер і дає конкретні рекомендації з налаштування: wget http://mysqltuner.pl/ -O mysqltuner.pl && perl mysqltuner.pl

Помилки після зміни my.cnf

Помилка	Причина	Рішення
[ERROR] InnoDB: Cannot allocate memory for the buffer pool	`innodb_buffer_pool_size` виставлено більшим, ніж реально доступно RAM (без урахування інших процесів)	Знизити значення до 60–75% від загальної RAM із запасом на ОС та інші сервіси
[ERROR] Too many connections	`max_connections` занижений відносно реального навантаження, або пул з'єднань застосунку не закриває сесії	Звірити `SHOW STATUS LIKE 'Max_used_connections';` з лімітом і застосунком
mysqld: unknown variable 'query_cache_size=...'	Параметр Query Cache залишено в my.cnf після оновлення на MySQL 8.0, де Query Cache повністю видалено	Видалити всі рядки `query_cache_*` з конфігурації

InnoDB проти MyISAM: чому параметри не універсальні

Усі параметри innodb_* у цій статті не діють на таблиці MyISAM — у нього немає буфера пулу, немає журналу транзакцій і немає підтримки зовнішніх ключів. Якщо в базі лишилися таблиці MyISAM (часто це системні таблиці старих версій або спеціально обраний рушій для повнотекстового пошуку), їхня продуктивність залежить від параметра key_buffer_size, а не від innodb_buffer_pool_size. Змішані бази вимагають налаштування обох буферів, і зайва пам'ять, виділена під key_buffer_size за відсутності таблиць MyISAM, — це пам'ять, відібрана у буфера InnoDB без користі.

Коли тюнінг my.cnf не дасть ефекту

Якщо вузьке місце — не пам'ять і не диск, а сама схема запитів (відсутні індекси, SELECT * на широких таблицях, N+1 запити з ORM), збільшення innodb_buffer_pool_size або tmp_table_size дасть лише тимчасове полегшення симптому. Різниця між хорошим і поганим планом запиту на порядки більша, ніж різниця між хорошими і посередніми налаштуваннями my.cnf. Перед тюнінгом конфігурації варто прогнати повільні запити через EXPLAIN і переконатися, що оптимізувати нема чого на рівні SQL.

Перевірка ефекту після зміни параметрів

# Звірити, що значення реально застосувалося (а не лишилося старим через помилку в секції)
mysql -u root -p -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"
mysql -u root -p -e "SHOW VARIABLES LIKE 'max_connections';"

# Відношення cache hit ratio для buffer pool — має бути близько до 100%
mysql -u root -p -e "
SHOW STATUS LIKE 'Innodb_buffer_pool_read_requests';
SHOW STATUS LIKE 'Innodb_buffer_pool_reads';"
-- hit ratio = 1 - (reads / read_requests), ближче до 1 — краще

# Повний звіт про стан InnoDB
SHOW ENGINE INNODB STATUS\G

Якщо Innodb_buffer_pool_reads (фізичні читання з диска) лишається високим відносно Innodb_buffer_pool_read_requests після збільшення buffer pool, це означає, що робочий набір даних усе ще не вміщується в пам'ять — параметр варто збільшувати далі або переглянути, які дані взагалі потрібно тримати в гарячому доступі. Діагностика конкретних повільних запитів розібрана у статті про Slow Query Log, а різниця рушіїв — у статті про InnoDB і MyISAM.