How to Optimize MySQL Performance

Tuning MySQL configuration can dramatically improve database performance.

Key Settings

Edit /etc/mysql/mysql.conf.d/mysqld.cnf:

[mysqld]
# InnoDB buffer pool (set to 50-70% of available RAM)
innodb_buffer_pool_size = 512M

# Log file size
innodb_log_file_size = 128M

# Query cache (disable for write-heavy workloads)
query_cache_type = 0

# Max connections
max_connections = 100

# Temp table size
tmp_table_size = 64M
max_heap_table_size = 64M

# Slow query log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

Apply

systemctl restart mysql

Monitor

mysqladmin -u root -p status
mysqladmin -u root -p processlist