Server Variable Tuning

Beyond the InnoDB buffer pool, a handful of server variables account for most of the remaining tuning work on a typical MySQL 8.4/9.x instance. This page covers what each controls and a safe starting point – treat these as starting points to measure from, not fixed targets; the right value depends on workload and hardware.

innodb_log_file_size / innodb_redo_log_capacity

InnoDB's redo log buffers changes before they're flushed to the tablespace, so a larger log absorbs more write burst before forcing extra checkpoint I/O, at the cost of longer crash recovery time. In MySQL 8.0.30+, the older pair innodb_log_file_size × innodb_log_files_in_group was replaced by a single innodb_redo_log_capacity variable (the older variables still work for compatibility but are deprecated):

SET GLOBAL innodb_redo_log_capacity = 2 * 1024 * 1024 * 1024; -- 2GB total, MySQL 8.0.30+

Dynamic and online – unlike the old innodb_log_file_size, which required stopping the server and removing the old log files to resize. A starting point of 1–4GB total suits many write-moderate workloads; write-heavy workloads with large transactions benefit from more.

innodb_flush_log_at_trx_commit

Controls the durability/performance tradeoff for every commit:

1 (default)	Log flushed and fsynced to disk on every transaction commit. Full ACID durability – survives an OS crash or power loss with no committed transaction lost.
0	Log written and flushed to disk roughly once per second by a background thread, not on commit. Up to one second of committed transactions can be lost on a crash.
2	Log written on every commit but only fsynced to disk roughly once per second. Survives a mysqld crash (data reaches the OS) but not an OS crash or power loss during that window.

Leave this at 1 for anything holding data that must not be lost. 2 is a reasonable compromise for workloads that can tolerate losing a second of commits in exchange for meaningfully less per-commit I/O; 0 is rarely worth its risk on a primary database.

max_connections

Caps how many simultaneous client connections the server accepts (default 151). Each connection reserves per-connection memory (sort_buffer_size, join_buffer_size, thread stack, etc. – not the buffer pool, which is shared), so max_connections × per-connection memory must fit comfortably alongside innodb_buffer_pool_size. Application connection pooling that caps concurrent connections well below this limit is usually a better fix for "too many connections" errors than simply raising the limit – a database rarely benefits from thousands of truly concurrent active connections, and a pool avoids per-connection setup overhead too.

SET GLOBAL max_connections = 500; SHOW STATUS LIKE 'Threads_connected'; SHOW STATUS LIKE 'Max_used_connections';

Max_used_connections shows the historical peak, useful for judging whether the current limit has real headroom or is close to being hit.

tmp_table_size / max_heap_table_size

Internal temporary tables (built for some GROUP BY, DISTINCT, UNION, and derived-table operations – visible as Using temporary in EXPLAIN, see EXPLAIN and Execution Plans) are created in memory first, and spill to an on-disk temporary table once they exceed the smaller of tmp_table_size and max_heap_table_size. On-disk temporary tables (using the InnoDB on-disk internal temporary table engine since MySQL 8.0) are far slower than in-memory ones, so a query that regularly spills is a common cause of "this query is randomly slow."

SET GLOBAL tmp_table_size = 64 * 1024 * 1024; SET GLOBAL max_heap_table_size = 64 * 1024 * 1024; SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables'; SHOW GLOBAL STATUS LIKE 'Created_tmp_tables';

Both variables should generally be raised together since the effective limit is the smaller of the two. A high ratio of Created_tmp_disk_tables to Created_tmp_tables signals it's worth raising both – or better, rewriting the offending query to need less intermediate storage (fewer selected columns, a supporting index for the GROUP BY/ORDER BY).

sort_buffer_size

Memory allocated per connection, per sort operation, when a query needs one that isn't satisfied by an index (Using filesort in EXPLAIN). Like join_buffer_size, it's allocated per-need rather than shared, so raising the global default affects every connection's worst case, not just one query. Default is 256KB as of MySQL 8.0.

SET SESSION sort_buffer_size = 4 * 1024 * 1024;

Raise for a session running a known large, unavoidable sort; prefer adding an index that provides the required order over routinely raising this globally – see Index Strategy.

thread_cache_size

Number of idle connection threads MySQL keeps around to reuse for new connections instead of spawning a new OS thread each time. On a workload with frequent short-lived connections (no connection pooling), a too-small thread cache shows up as a high Threads_created relative to Connections, meaning most new connections pay full thread-creation cost.

SET GLOBAL thread_cache_size = 100; SHOW STATUS LIKE 'Threads_created'; SHOW STATUS LIKE 'Connections';

If Threads_created keeps climbing roughly in step with Connections, the cache is too small (or effectively unused because a connection pool already keeps connections open, in which case this variable matters less).

For the largest single memory allocation on the server, see InnoDB Buffer Pool and Caching; for diagnosing which queries are actually driving these numbers, see Performance Schema and Sys Schema.

-- Redo log capacity (MySQL 8.0.30+), online and dynamic
SET GLOBAL innodb_redo_log_capacity = 2 * 1024 * 1024 * 1024; -- 2GB

-- Durability vs. commit-time I/O tradeoff (leave at 1 unless you understand the risk)
SET GLOBAL innodb_flush_log_at_trx_commit = 1;

-- Connection ceiling and current usage
SET GLOBAL max_connections = 500;
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Max_used_connections';

-- In-memory temporary table ceiling before spilling to disk
SET GLOBAL tmp_table_size = 64 * 1024 * 1024;
SET GLOBAL max_heap_table_size = 64 * 1024 * 1024;
SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables';
SHOW GLOBAL STATUS LIKE 'Created_tmp_tables';

-- Per-session filesort buffer, for a query known to need a large sort
SET SESSION sort_buffer_size = 4 * 1024 * 1024;

-- Reuse of idle connection threads
SET GLOBAL thread_cache_size = 100;
SHOW STATUS LIKE 'Threads_created';
SHOW STATUS LIKE 'Connections';

-- Redo log capacity (MySQL 8.0.30+), online and dynamic
SET GLOBAL innodb_redo_log_capacity = 2 * 1024 * 1024 * 1024; -- 2GB

-- Durability vs. commit-time I/O tradeoff (leave at 1 unless you understand the risk)
SET GLOBAL innodb_flush_log_at_trx_commit = 1;

-- Connection ceiling and current usage
SET GLOBAL max_connections = 500;
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Max_used_connections';

-- In-memory temporary table ceiling before spilling to disk
SET GLOBAL tmp_table_size = 64 * 1024 * 1024;
SET GLOBAL max_heap_table_size = 64 * 1024 * 1024;
SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables';
SHOW GLOBAL STATUS LIKE 'Created_tmp_tables';

-- Per-session filesort buffer, for a query known to need a large sort
SET SESSION sort_buffer_size = 4 * 1024 * 1024;

-- Reuse of idle connection threads
SET GLOBAL thread_cache_size = 100;
SHOW STATUS LIKE 'Threads_created';
SHOW STATUS LIKE 'Connections';

MENU