Оптимизация MySQL/MariaDB: настройка my.cnf для максимальной производительности

Где находится файл 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

Оптимальный 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 и старше)

Применение настроек

sudo systemctl restart mysql
# или
sudo systemctl restart mariadb

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