MySQL/MariaDB Optimization: Tuning my.cnf for Maximum Performance

MySQL / MariaDB · 19.04.2026

Where is my.cnf Located

MySQL/MariaDB reads config from /etc/mysql/my.cnf and /etc/mysql/mysql.conf.d/*.cnf. Check which files are loaded: mysqld --verbose --help 2>/dev/null | grep "Default options" -A 1

Key InnoDB Parameters

Parameter	Recommendation	Description
`innodb_buffer_pool_size`	60–75% RAM	Main parameter — data and index buffer
`innodb_log_file_size`	256M–1G	Transaction log size (larger = faster writes)
`innodb_flush_method`	O_DIRECT	Avoids double-caching with OS

Optimal my.cnf for 2 GB RAM

[mysqld]
innodb_buffer_pool_size = 1G
innodb_log_file_size = 256M
innodb_flush_method = O_DIRECT
max_connections = 100
tmp_table_size = 64M
slow_query_log = 1
long_query_time = 2

Apply and Verify

sudo systemctl restart mysql
mysql -u root -p -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"

Use MySQLTuner for automated analysis: wget http://mysqltuner.pl/ -O mysqltuner.pl && perl mysqltuner.pl