Optimizing MySQL Performance on Your Linux server

Learn how to tune MySQL/MariaDB for optimal performance on your Linux server with practical configuration tips.

Why Optimize MySQL?

Database performance directly impacts:

• Application response time
• Server resource usage
• User experience
• Cost efficiency

Step 1: Check Current Performance

Monitor MySQL Status

mysql -u root -p -e "SHOW STATUS;"

Check Current Configuration

mysql -u root -p -e "SHOW VARIABLES;"

Use MySQLTuner

wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
chmod +x mysqltuner.pl
./mysqltuner.pl

MySQLTuner analyzes your configuration and suggests improvements.

Step 2: Configure InnoDB Buffer Pool

The InnoDB buffer pool caches data and indexes. For a dedicated database server, set to 70-80% of RAM.

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

[mysqld]
innodb_buffer_pool_size = 2G  # For 4GB RAM Linux server

For application + database on same Linux server:

• 2GB RAM: Set to 512M - 1G
• 4GB RAM: Set to 1G - 2G
• 8GB RAM: Set to 3G - 5G

Step 3: Optimize Connection Settings

Max Connections

max_connections = 50

Calculate based on: (Total RAM - OS overhead) / per-connection memory

Connection Timeout

wait_timeout = 300
interactive_timeout = 300

Step 4: Query Cache (MySQL 5.7 and Earlier)

Note: Query cache is removed in MySQL 8.0

query_cache_type = 1
query_cache_size = 128M
query_cache_limit = 2M

Step 5: Optimize Table Settings

Key Buffer Size (MyISAM)

key_buffer_size = 256M

Table Cache

table_open_cache = 2000
table_definition_cache = 1000

Step 6: Logging Configuration

Disable query log in production (impacts performance):

general_log = 0

Enable slow query log to find problematic queries:

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

Step 7: Optimize Queries

Identify Slow Queries

mysqldumpslow -t 10 /var/log/mysql/slow-query.log

Use EXPLAIN

EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';

Add Indexes

CREATE INDEX idx_email ON users(email);

Step 8: Regular Maintenance

Optimize Tables

OPTIMIZE TABLE users;

Analyze Tables

ANALYZE TABLE users;

Check and Repair

mysqlcheck -u root -p --auto-repair --optimize --all-databases

Recommended Configuration (4GB RAM Linux server)

[mysqld]
# InnoDB Settings
innodb_buffer_pool_size = 2G
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT

# Connection Settings
max_connections = 50
wait_timeout = 300
interactive_timeout = 300

# Query Cache (MySQL 5.7 only)
query_cache_type = 1
query_cache_size = 128M
query_cache_limit = 2M

# Table Cache
table_open_cache = 2000
table_definition_cache = 1000

# Temporary Tables
tmp_table_size = 64M
max_heap_table_size = 64M

# Logging
slow_query_log = 1
long_query_time = 2

Apply Changes

After editing configuration:

sudo systemctl restart mysql

Monitor Performance

Check Buffer Pool Usage

SHOW STATUS LIKE 'Innodb_buffer_pool_%';

Connection Stats

SHOW STATUS LIKE 'Threads_%';
SHOW STATUS LIKE 'Max_used_connections';

Conclusion

Proper MySQL tuning significantly improves database performance. Start with conservative settings and adjust based on monitoring. Gumpbox helps you monitor database resources in real-time.

Next Steps

• Monitoring Server Resources: CPU, Memory, and Disk
• Automating Linux server Backups with Cron Jobs