Tuning MySQL for Performance: The Variables That Actually Matter

There is a special kind of boredom that only database people know. The kind where you stare at a server humming along and think, surely there is something here I can tune. Good news: there is.

This post walks through the most important MySQL variables to tune for performance, why they matter, and when touching them helps versus when it quietly makes things worse. This is written with InnoDB-first workloads in mind, because let’s be honest, that’s almost everyone.

1. innodb_buffer_pool_size

Real metrics to watch

Before touching this variable, look at these:

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';

Key fields:

• Innodb_buffer_pool_reads – physical reads from disk
• Innodb_buffer_pool_read_requests – logical reads

Rule of thumb: If reads / read_requests > 1–2%, your buffer pool is too small.

Example graph

Plot Innodb_buffer_pool_reads over time. A healthy system shows a flat or gently rising line. Spikes that look like a city skyline usually mean memory pressure or a cold cache.

If MySQL performance had a crown jewel, this would be it.

What it does

The InnoDB buffer pool caches table data and indexes in memory. Reads served from RAM are fast. Reads from disk are… character building.

How to tune it

• Dedicated DB server: 60–75% of system RAM
• Shared server: be conservative and leave memory for the OS and other services

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

Pro tip

If your working set fits in the buffer pool, MySQL feels magical. If it doesn’t, no amount of query tuning will save you.

2. innodb_buffer_pool_instances

This one matters once memory gets big.

What it does

Splits the buffer pool into multiple instances to reduce internal mutex contention.

How to tune it

• Only relevant if buffer pool is ≥ 1GB
• Rule of thumb: 1 instance per 1–2GB, max 8

SHOW VARIABLES LIKE 'innodb_buffer_pool_instances';

Gotcha

More is not always better. Too many instances wastes memory and can hurt performance.

3. innodb_log_file_size

Real metrics to watch

SHOW GLOBAL STATUS LIKE 'Innodb_log%';

Pay attention to:

• Innodb_log_waits
• Innodb_log_write_requests

If Innodb_log_waits is non-zero, redo logs are too small for your write rate.

Example graph

Graph Innodb_log_waits as a rate per second. Ideally, this line hugs zero like it’s afraid of heights.

This variable controls how calmly MySQL handles write-heavy workloads.

What it does

Defines the size of redo logs. Larger logs mean fewer checkpoints and smoother writes.

How to tune it

• OLTP workloads: 1–4GB total redo log is common
• Large transactions benefit from larger logs

SHOW VARIABLES LIKE 'innodb_log_file_size';

Warning

Changing this requires a restart. Plan accordingly or accept the wrath of your on-call future self.

4. innodb_flush_log_at_trx_commit

Real metrics to watch

SHOW GLOBAL STATUS LIKE 'Innodb_os_log_fsyncs';

Switching from 1 to 2 often reduces fsyncs by orders of magnitude.

Example graph

Overlay two lines:

• Transactions per second
• Innodb_os_log_fsyncs per second

On busy systems, this graph alone can justify the change to skeptical auditors.

Performance versus durability, the eternal duel.

What it does

Controls how often redo logs are flushed to disk.

Common values

• 1 – Safest, slowest (flush every commit)
• 2 – Very popular compromise
• 0 – Fast, risky

SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';

Reality check

For many production systems, 2 delivers massive performance gains with acceptable risk, especially with reliable storage.

5. innodb_flush_method

This decides how MySQL talks to your disks.

What it does

Controls whether MySQL uses OS cache or bypasses it.

Recommended

innodb_flush_method=O_DIRECT

This avoids double-buffering between MySQL and the OS page cache.

Caveat

Some filesystems and older kernels behave differently. Always test.

6. max_connections

This is not a performance knob. It is a damage limiter.

What it does

Caps the number of concurrent client connections.

Why it matters

Each connection consumes memory. Too many and MySQL dies spectacularly.

SHOW VARIABLES LIKE 'max_connections';

Advice

• Set it realistically
• Use connection pooling
• Monitor Threads_connected

7. thread_cache_size

Real metrics to watch

SHOW GLOBAL STATUS LIKE 'Threads%';

Key fields:

• Threads_created
• Connections

If Threads_created / Connections stays above a few percent, your cache is undersized.

Example graph

Graph Threads_created as a counter. A healthy system shows a curve that flattens over time, not a staircase.

Small change, measurable win.

What it does

Caches threads so MySQL doesn’t constantly create and destroy them.

How to tune

Watch:

SHOW STATUS LIKE 'Threads_created';

If it keeps climbing, increase thread_cache_size.

8. table_open_cache and table_definition_cache

Metadata matters more than people expect.

What they do

Cache open tables and table definitions to avoid repeated filesystem access.

Symptoms of being too low

• High Opened_tables
• Metadata lock waits

SHOW VARIABLES LIKE 'table_open_cache';
SHOW VARIABLES LIKE 'table_definition_cache';

9. tmp_table_size and max_heap_table_size

Real metrics to watch

SHOW GLOBAL STATUS LIKE 'Created_tmp%';

Watch:

• Created_tmp_tables
• Created_tmp_disk_tables

If disk temp tables exceed 5–10% of total temp tables, queries are spilling to disk.

Example graph

Stacked area chart:

• In-memory temp tables
• Disk-based temp tables

Disk usage creeping upward usually points to reporting queries pretending to be OLTP.

Disk-based temp tables are silent performance killers.

What they do

Limit how large in-memory temp tables can grow.

How to tune

Set both to the same value:

tmp_table_size=256M
max_heap_table_size=256M

Reality

This helps complex queries, but bad queries still need fixing.

10. slow_query_log and long_query_time

Not a performance variable, but a performance revelation.

Why it matters

You cannot tune what you cannot see.

slow_query_log=ON
long_query_time=1

This turns guesswork into evidence.

A Note on Graphing These Metrics

You don’t need exotic tools. These work well:

• performance_schema
• sys schema views
• Prometheus + mysqld_exporter
• Percona Monitoring and Management (PMM)

Golden rule: Always graph rates, not raw counters.

Final Thoughts

Tuning MySQL is less about endless knobs and more about understanding pressure points:

• Memory first
• I/O second
• Concurrency third

Most performance wins come from a handful of variables, not heroic config files full of folklore.

If you tune one thing today, make it the buffer pool. If you tune two, add redo logs. Everything else is refinement.

And if you’re bored again tomorrow, congratulations. You’re officially a database person. ∎