Skip to content
Tutorials Center
My learning

Topics

Lesson 49 of 75 ยท Indexes and Performance

Server Configuration and Tuning

Tune a MySQL server sensibly: innodb_buffer_pool_size, redo log capacity, flush settings, max_connections, temporary tables, my.cnf, SET PERSIST and sysbench.

MySQL's defaults are chosen to start on almost any machine, including a laptop with 2 GB of memory. That makes them safe and small. On a production server with 64 GB of RAM, the default 128 MB buffer pool leaves nearly all of that memory unused, and no amount of clever SQL will fix it.

Tuning means telling the server what hardware it has and how much durability you need. This lesson covers the handful of settings that matter in nearly every deployment, how to change them (my.cnf and SET PERSIST), and how to measure the effect with sysbench. Everything about the server is read-only here. The two live examples use plain SQL in the SQLite stand-in as calculators, so you can practice the arithmetic behind the settings.

Change one setting at a time, measure, and keep notes. Most "tuning guides" that list fifty variables to change are wrong for your workload. Good queries and good indexes beat configuration tweaks, so work through the slow query lesson first.

The settings that matter

Buffer pool RAM that caches table and index pages.
Redo log Space for changes not yet in data files.
Connections Each one costs memory, so limit them.
Flush settings Trade durability against write speed.

Four areas cover most real-world gains.

1 / 2

The InnoDB buffer pool

InnoDB reads and writes data in 16 KB pages, and it keeps recently used pages in memory in the buffer pool. A read served from the pool is thousands of times faster than a read from disk. The size is set by innodb_buffer_pool_size:

SQL
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SET GLOBAL innodb_buffer_pool_size = 24 * 1024 * 1024 * 1024;   -- 24 GB, resized online

The default is 128 MB. On a server dedicated to MySQL, a common starting point is 50% to 75% of RAM, leaving room for the operating system, per-connection memory and any other processes. If the server is shared with a web application, use less. The pool can be resized while the server runs (in chunks, set by innodb_buffer_pool_chunk_size), but changes can take a while on a busy server.

Since MySQL 8.0.3 there is also innodb_dedicated_server = ON, which sizes the buffer pool and redo log automatically from the machine's memory. It assumes that MySQL is the only major thing on the host, so use it only then.

How do you know if the pool is big enough? Compare the number of page requests with the number that missed memory and went to disk:

SQL
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
TEXT
+---------------------------------------+------------+
| Variable_name                         | Value      |
+---------------------------------------+------------+
| Innodb_buffer_pool_read_requests      | 9120450388 |
| Innodb_buffer_pool_reads              | 3512604    |
+---------------------------------------+------------+

read_requests counts logical reads and reads counts those that had to hit the disk. The values above are illustrative. Try the arithmetic yourself:

SQL Buffer pool hit ratio and a sizing rule of thumb

Press Run (or Ctrl + Enter) to see the result here.

A ratio above 99% is typical for a healthy working set. A low ratio right after a restart is normal, because the pool is cold. If the ratio stays low and you have spare RAM, grow the pool. If it stays low and the data simply does not fit, you are looking at more memory, better indexes or less data.

The redo log and durability

Before InnoDB changes a data file, it writes the change to the redo log, which is sequential and fast, and only later flushes the data pages. After a crash, MySQL replays the redo log to recover. Its size limits how much unflushed work can pile up, and a log that is too small causes stalls under heavy writes.

Since MySQL 8.0.30 you set one dynamic value:

SQL
SET GLOBAL innodb_redo_log_capacity = 8 * 1024 * 1024 * 1024;   -- 8 GB

It replaces the older innodb_log_file_size and innodb_log_files_in_group, which you may still see in older guides and configuration files. The default is 100 MB, which is small for a write-heavy server. A few GB is common, and bigger gives smoother write bursts at the cost of a longer crash recovery.

Two settings decide how safe each commit is:

Setting Value Meaning
innodb_flush_log_at_trx_commit 1 (default) Flush the redo log to disk at every commit. Fully durable.
2 Write to the OS cache at commit, flush about once per second. An OS crash can lose about a second of commits.
0 Write and flush about once per second. A mysqld crash can lose about a second.
sync_binlog 1 (default) Sync the binary log at every commit.

For data that matters, keep both at 1. Lowering them makes writes faster, but you lose the guarantee that a committed transaction survives a power cut. Only relax them where losing a second of data is acceptable (a rebuildable cache, a replica, a bulk load) and you have decided so on purpose. Related: on Linux, innodb_flush_method = O_DIRECT is the common recommendation (the default is fsync), because it avoids caching data twice, in InnoDB and in the operating system.

Connections and per-connection memory

SQL
SHOW VARIABLES LIKE 'max_connections';          -- default 151
SHOW GLOBAL STATUS LIKE 'Max_used_connections'; -- high-water mark since start
SHOW GLOBAL STATUS LIKE 'Threads_connected';

Raising max_connections to 5,000 "to fix" the error Too many connections is usually a mistake. Each connection allocates buffers (sort, join, read, network) on demand, and a few thousand busy connections can use more memory than the buffer pool. They also compete for CPU, so throughput often drops. A better answer is a connection pool in the application (or ProxySQL) that keeps the number of active connections near the number of CPU cores times a small factor.

Per-session buffers such as sort_buffer_size and join_buffer_size are allocated per query, so enlarging them globally multiplies the cost by the number of connections. Leave them alone unless a specific query needs more, and then raise them for that session only:

SQL
SET SESSION sort_buffer_size = 8 * 1024 * 1024;

Estimate a worst case before you change anything:

SQL A rough worst-case memory budget

Press Run (or Ctrl + Enter) to see the result here.

The total is about 28 GB (28,288 MB) before the operating system, which tells you a 32 GB machine is already tight with these numbers. Real connections rarely all use their full buffers at once, but a budget like this keeps you honest.

Temporary tables and other limits

Queries with GROUP BY, DISTINCT, UNION or some sorts build internal temporary tables. Small ones stay in memory, and large ones spill to disk, which is slow. The relevant settings are tmp_table_size and max_heap_table_size (both 16 MB by default, and the smaller one wins), and in MySQL 8.0 the default in-memory engine is TempTable, limited by temptable_max_ram. Check how often disk tables happen:

SQL
SHOW GLOBAL STATUS LIKE 'Created_tmp%tables';

If Created_tmp_disk_tables is a large share of Created_tmp_tables, first look for the offending queries with sys.statements_with_temp_tables and fix them (a better index often removes the need). Raising the limits is the second step.

Some other settings worth knowing:

  • innodb_io_capacity and innodb_io_capacity_max tell InnoDB how many I/O operations per second the storage can handle for background flushing. The default of 200 suits spinning disks, and fast SSDs and NVMe usually warrant a much higher value.
  • table_open_cache should exceed the number of tables your workload touches at once. Watch Opened_tables for growth.
  • The query cache was removed in MySQL 8.0, so guides that tell you to tune query_cache_size are out of date.

Where settings live: my.cnf and SET PERSIST

There are three ways to set a variable, and mixing them up is a common source of confusion:

INI
# /etc/mysql/my.cnf  (location varies by platform)
[mysqld]
innodb_buffer_pool_size = 24G
innodb_redo_log_capacity = 8G
max_connections = 300
slow_query_log = ON
long_query_time = 0.5
  1. Option file (my.cnf): read at startup. Changes need a restart (or a matching SET GLOBAL). This is the file you keep in version control.
  2. SET GLOBAL name = value: changes the running server at once and is lost on restart.
  3. SET PERSIST name = value (8.0+): changes the running server and writes the value to mysqld-auto.cnf in the data directory, so it survives restarts. SET PERSIST_ONLY writes the file without changing the running value, which is for settings that need a restart. RESET PERSIST name removes an entry.
SQL
SET PERSIST max_connections = 300;
SELECT variable_name, variable_value, set_time
FROM performance_schema.persisted_variables;
SELECT variable_name, variable_source FROM performance_schema.variables_info
WHERE variable_name = 'max_connections';

variables_info shows where each value came from (COMPILED, GLOBAL, PERSISTED, ...), which is the first thing to check when a setting does not behave as you expect. Many teams manage my.cnf through configuration management and use SET PERSIST only for emergencies, which keeps one source of truth.

Benchmarking with sysbench

Do not guess whether a change helped. sysbench is a standard load generator that creates test tables, runs a transactional workload and reports throughput and latency:

TEXT
$ sysbench oltp_read_write --mysql-host=127.0.0.1 --mysql-user=bench --mysql-password=... \
    --mysql-db=sbtest --tables=8 --table-size=1000000 prepare

$ sysbench oltp_read_write --mysql-host=127.0.0.1 --mysql-user=bench --mysql-password=... \
    --mysql-db=sbtest --tables=8 --table-size=1000000 --threads=16 --time=120 run

SQL statistics:
    transactions:                        48210  (401.6 per sec.)
    queries:                             964200 (8032.0 per sec.)
    95th percentile latency (ms):        52.89

$ sysbench oltp_read_write ... cleanup

The figures are illustrative. Run the same test before and after a change, on the same data, a few times each, because single runs vary. Use a data size larger than the buffer pool if you want to test disk behavior, and keep it smaller if you want to test CPU and locking. A synthetic benchmark never replaces testing with your own queries, but it is good at catching a regression and at comparing two settings fairly.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. A server dedicated to MySQL has 64 GB of RAM and the default innodb_buffer_pool_size. What is the usual first fix?

    The 128 MB default leaves memory unused. Sizing the buffer pool to a large share of RAM is the biggest single win. The query cache does not exist in MySQL 8.0.
  2. 2. Which statement changes a variable now AND keeps the value after a restart?

    SET PERSIST applies the value immediately and writes it to mysqld-auto.cnf. SET GLOBAL is lost on restart.
  3. 3. What does innodb_flush_log_at_trx_commit = 2 risk?

    With 2, the log is written to the OS cache at commit but flushed to disk about once per second, so a crash of the whole machine can lose the last second.
  4. 4. Why is raising max_connections to a very large number often a bad fix for 'Too many connections'?

    A connection pool in the application or a proxy keeps the number of active connections modest, which is usually faster than allowing thousands.

Summary

  • Defaults are tiny. Set innodb_buffer_pool_size to roughly 50% to 75% of RAM on a dedicated server and check the hit ratio with the Innodb_buffer_pool_read% counters.
  • From 8.0.30, innodb_redo_log_capacity sets the redo log size. A few GB suits write-heavy servers.
  • Keep innodb_flush_log_at_trx_commit = 1 and sync_binlog = 1 for data you cannot lose, and relax them only on purpose.
  • Limit connections and use a pool. Per-session buffers multiply by the number of connections.
  • Watch disk temporary tables, and remember that the query cache is gone in 8.0.
  • Put settings in my.cnf, use SET PERSIST for persistent runtime changes, and check performance_schema.variables_info for a value's source.
  • Benchmark with sysbench before and after, one change at a time.

Next: Partitioning, where we split big tables into smaller physical pieces.