MySQLTuner Guide: Optimize MySQL Server Performance

Before You Begin

For accurate recommendations, MySQL should ideally be running under normal production load for at least 24 hours before running MySQLTuner.

Step 1 — Download MySQLTuner

wget https://github.com/major/MySQLTuner-perl/releases/download/v2.8.45/mysqltuner.pl

chmod +x mysqltuner.pl

Step 2 — Run MySQLTuner

./mysqltuner.pl

Enter your MySQL administrative credentials when prompted.

Example Output

[OK] Maximum reached memory usage: 5.8G
[!!] Query cache should be disabled
[!!] Temporary tables created on disk: 28%
[OK] InnoDB buffer pool / data size: 8.0G / 6.2G
[!!] Slow queries: 5%
[OK] Table cache hit rate: 99%
[OK] Connections usage: 12%

Understanding The Results

Step 3 — Check Current MySQL Variables

mysql -u root -p

SHOW VARIABLES;

Step 4 — Backup Configuration

sudo cp /etc/mysql/mysql.conf.d/mysqld.cnf /etc/mysql/mysql.conf.d/mysqld.cnf.backup

InnoDB Buffer Pool Optimization

The InnoDB Buffer Pool is one of the most important MySQL settings. It caches data and indexes in memory, reducing disk reads and dramatically improving performance.

Example: 16GB RAM Server

innodb_buffer_pool_size = 12G

Check Current Buffer Pool

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

Step 5 — Optimize InnoDB Log Files

Larger redo logs improve write performance and reduce checkpoint pressure on busy databases.

innodb_log_file_size = 1G

innodb_log_buffer_size = 64M

Step 6 — Disable Query Cache (MySQL 5.7)

MySQLTuner frequently recommends disabling query cache because it often becomes a bottleneck on busy servers.

query_cache_type = 0

query_cache_size = 0

Step 7 — Optimize Max Connections

Many servers waste memory because max_connections is configured too high.

Check Usage

SHOW STATUS LIKE 'Max_used_connections';

Example

Max_used_connections = 85

max_connections = 500

If your peak usage is 85, setting 500 connections wastes RAM unnecessarily.

Recommended

max_connections = 150

Step 8 — Temporary Table Optimization

MySQLTuner often reports excessive temporary tables being written to disk.

Check Statistics

SHOW GLOBAL STATUS LIKE 'Created_tmp%';

Improve Memory Limits

tmp_table_size = 256M

max_heap_table_size = 256M

Step 9 — Table Cache Optimization

If MySQL repeatedly opens tables, increasing table cache can improve performance.

SHOW GLOBAL STATUS LIKE 'Opened_tables';

Example Setting

table_open_cache = 4000

Example Production my.cnf

Example for a 16GB dedicated MySQL server.

[mysqld]

innodb_buffer_pool_size = 12G
innodb_log_file_size = 1G
innodb_log_buffer_size = 64M

max_connections = 150

tmp_table_size = 256M
max_heap_table_size = 256M

table_open_cache = 4000

innodb_flush_method = O_DIRECT

skip-name-resolve

Validate Configuration

mysqld --verbose --help > /dev/null

Restart MySQL

sudo systemctl restart mysql

Verify Server Health

sudo systemctl status mysql

Slow Query Analysis

MySQLTuner frequently reports slow query percentages. Optimizing slow queries often provides greater performance improvements than increasing server resources.

Step 10 — Enable Slow Query Log

Add these settings to your MySQL configuration.

slow_query_log = 1

slow_query_log_file = /var/log/mysql/mysql-slow.log

long_query_time = 1

log_queries_not_using_indexes = 1

Restart MySQL

sudo systemctl restart mysql

Verify Settings

SHOW VARIABLES LIKE '%slow_query%';

Step 11 — Analyze Slow Query Log

mysqldumpslow -s t /var/log/mysql/mysql-slow.log

This displays the slowest queries first.

Top 20 Slow Queries

mysqldumpslow -s t -t 20 /var/log/mysql/mysql-slow.log

Step 12 — Install Percona Toolkit

Percona Toolkit provides deeper analysis than mysqldumpslow.

sudo apt update

sudo apt install percona-toolkit -y

Analyze Slow Queries

pt-query-digest /var/log/mysql/mysql-slow.log

Step 13 — Find Missing Indexes

Missing indexes are one of the most common causes of poor database performance.

SHOW INDEX FROM users;

Example Slow Query

SELECT *
FROM users
WHERE email = '[email protected]';

Add Index

CREATE INDEX idx_users_email
ON users(email);

Step 14 — Use EXPLAIN

EXPLAIN shows how MySQL executes queries.

EXPLAIN
SELECT *
FROM users
WHERE email = '[email protected]';

Good Result

type: ref

key: idx_users_email

rows: 1

Bad Result

type: ALL

key: NULL

rows: 500000

Step 15 — Monitor InnoDB Health

SHOW ENGINE INNODB STATUSG

This provides valuable information about locks, transactions and buffer pool performance.

Step 16 — Check Buffer Pool Efficiency

SHOW GLOBAL STATUS
LIKE 'Innodb_buffer_pool_read%';

Target

Buffer Pool Hit Ratio > 99%

High CPU Troubleshooting

Memory Usage Formula

One of the most useful MySQLTuner outputs is maximum potential memory usage.

Total Memory ≈

Global Buffers

+

(max_connections × per-thread buffers)

Example

Buffer Pool = 12G

max_connections = 150

Per Thread = 4M

Total ≈ 12.6G

Production Optimization Checklist

Use this checklist after running MySQLTuner to ensure your MySQL server is optimized for production workloads.

Recommended MySQL Settings By Server Size

WordPress & WooCommerce Optimization

WooCommerce stores generate many database queries, especially during checkout, cart updates and product searches.

Laravel MySQL Optimization

Laravel applications often benefit from query analysis, Redis caching and proper indexing.

php artisan optimize

php artisan config:cache

php artisan route:cache

php artisan view:cache

Magento MySQL Optimization

Magento is database intensive and benefits significantly from proper MySQL tuning.

Example High Performance MySQL 8 Configuration

[mysqld]

skip-name-resolve

max_connections = 200

innodb_buffer_pool_size = 12G

innodb_log_file_size = 1G

innodb_log_buffer_size = 64M

innodb_flush_method = O_DIRECT

tmp_table_size = 256M

max_heap_table_size = 256M

table_open_cache = 4000

thread_cache_size = 100

performance_schema = ON

slow_query_log = ON

long_query_time = 1

Frequently Asked Questions

Related Articles