Where the my.cnf File Is Located

MySQL/MariaDB reads its configuration from several locations. Load order: /etc/mysql/my.cnf → /etc/mysql/mysql.conf.d/*.cnf → ~/.my.cnf. For MariaDB the main file is /etc/mysql/mariadb.conf.d/50-server.cnf.

# Check which files MySQL reads:
mysqld --verbose --help 2>/dev/null | grep "Default options" -A 1

Key InnoDB Parameters

Parameter	Recommendation	Description
`innodb_buffer_pool_size`	60–75% RAM	The main parameter — buffer for InnoDB data and indexes
`innodb_log_file_size`	256M–1G	Transaction log size (larger = faster writes)
`innodb_flush_log_at_trx_commit`	1 (safe) / 2 (fast)	1 = full durability, 2 = faster but risks losing 1 second
`innodb_flush_method`	O_DIRECT	Avoids double caching with the OS

Optimal my.cnf by RAM Size

For 2 GB 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

For 8 GB RAM:

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

Checking InnoDB Buffer Pool Usage

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 and Older)

Query Cache was removed in MySQL 8.0. In MySQL 5.7 and MariaDB its use is debatable: under heavy write load it creates lock contention. For high-traffic sites Redis/Memcached is recommended instead of Query Cache.

Applying the Settings

sudo systemctl restart mysql
# or
sudo systemctl restart mariadb

# Verify
mysql -u root -p -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"

Use MySQLTuner — the script automatically analyzes your server and gives specific tuning recommendations: wget http://mysqltuner.pl/ -O mysqltuner.pl && perl mysqltuner.pl

Errors After Changing my.cnf

Error	Cause	Fix
[ERROR] InnoDB: Cannot allocate memory for the buffer pool	`innodb_buffer_pool_size` is set higher than the RAM actually available (not accounting for other processes)	Lower the value to 60-75% of total RAM, leaving room for the OS and other services
[ERROR] Too many connections	`max_connections` is set too low for the real load, or the application's connection pool is not closing sessions	Compare `SHOW STATUS LIKE 'Max_used_connections';` against the limit and the application
mysqld: unknown variable 'query_cache_size=...'	Query Cache settings were left in my.cnf after upgrading to MySQL 8.0, where Query Cache was removed entirely	Remove all `query_cache_*` lines from the configuration

InnoDB vs MyISAM: Why the Parameters Are Not Universal

None of the innodb_* parameters in this article affect MyISAM tables — it has no buffer pool, no transaction log, and no foreign key support. If the database still has MyISAM tables (often legacy system tables or an engine chosen specifically for full-text search), their performance depends on key_buffer_size, not innodb_buffer_pool_size. Mixed databases need both buffers tuned, and extra memory allocated to key_buffer_size when there are no MyISAM tables is memory taken away from the InnoDB buffer for no benefit.

When Tuning my.cnf Will Not Help

If the bottleneck is not memory or disk but the query pattern itself (missing indexes, SELECT * on wide tables, N+1 queries from an ORM), raising innodb_buffer_pool_size or tmp_table_size will only mask the symptom temporarily. The gap between a good and a bad query plan is orders of magnitude larger than the gap between good and mediocre my.cnf settings. Before tuning the configuration, run the slow queries through EXPLAIN and confirm there is nothing left to optimize at the SQL level.

Verifying the Effect After Changing Parameters

# Confirm the value actually took effect (and was not left unchanged due to a typo in the section)
mysql -u root -p -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"
mysql -u root -p -e "SHOW VARIABLES LIKE 'max_connections';"

# Buffer pool cache hit ratio — should be close to 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), closer to 1 is better

# Full InnoDB status report
SHOW ENGINE INNODB STATUS\G

If Innodb_buffer_pool_reads (physical reads from disk) stays high relative to Innodb_buffer_pool_read_requests after increasing the buffer pool, it means the working data set still does not fit in memory — either raise the parameter further or reconsider which data actually needs to stay hot. Diagnosing specific slow queries is covered in the article on Slow Query Log, and the engine difference is covered in the article on InnoDB and MyISAM.