Server Settings That Matter

Most MySQL slowness comes from queries and indexes, not from server settings. Still, a handful of settings matter, and the defaults are conservative. The query cache no longer exists: it was removed in MySQL 8.0, so advice about query_cache_size is out of date.

1. innodb_buffer_pool_size: the most important setting

The buffer pool is InnoDB’s cache of table and index pages in memory. When a page is in the pool, a read costs microseconds. When it is not, MySQL reads from disk (milliseconds).

1

SELECT @@innodb_buffer_pool_size / 1024 / 1024 AS buffer_pool_mb;

1
2
3
4
5
6

+----------------+
| buffer_pool_mb |
+----------------+
|    64.00000000 |
+----------------+
1 row in set

(The MySQL default is 128 MB, which is small for a real server. The value printed here comes from the small test server used for this course. Yours will differ.)

• On a dedicated database server, set it to about 50 to 70 percent of RAM (leave room for the operating system, per-connection memory and other processes).
• On a shared development machine, 1 to 2 GB is plenty.
• You can change it online: SET GLOBAL innodb_buffer_pool_size = 4 * 1024 * 1024 * 1024; (it resizes in chunks). Make it permanent in my.cnf.
• Check the hit ratio: Innodb_buffer_pool_reads (disk reads) compared with Innodb_buffer_pool_read_requests. A hit ratio above 99 percent is healthy.

1

SHOW GLOBAL STATUS WHERE Variable_name IN ('Innodb_buffer_pool_reads', 'Innodb_buffer_pool_read_requests');

1
2
3
4
5
6
7

+----------------------------------+----------+
| Variable_name                    | Value    |
+----------------------------------+----------+
| Innodb_buffer_pool_read_requests | 20765375 |
| Innodb_buffer_pool_reads         | 5610     |
+----------------------------------+----------+
2 rows in set

(The counters come from the test server and grow while it runs.) Divide the two numbers: Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests. A result near 0.001 or below (a hit ratio of 99.9 percent) is healthy.

2. Redo log size

The redo log records changes so that InnoDB can recover after a crash. A log that is too small forces InnoDB to flush data pages more often, which slows write-heavy workloads.

1

SELECT @@innodb_redo_log_capacity / 1024 / 1024 AS redo_log_mb;

1
2
3
4
5
6

+--------------+
| redo_log_mb  |
+--------------+
| 100.00000000 |
+--------------+
1 row in set

In MySQL 8.0.30 and later, you set innodb_redo_log_capacity (default 100 MB), and can change it online. For write-heavy servers, 1 to 8 GB is common. (Older versions used innodb_log_file_size and innodb_log_files_in_group.)

3. Durability: innodb_flush_log_at_trx_commit

Keep 1 for anything important (money, orders, accounts). 2 is a reasonable choice for data that can be recreated. This is a trade-off, not a free speed-up, and replicas and backups change the equation.

4. max_connections and memory per connection

1

SELECT @@max_connections AS max_connections, @@sort_buffer_size / 1024 AS sort_buffer_kb, @@join_buffer_size / 1024 AS join_buffer_kb, @@tmp_table_size / 1024 / 1024 AS tmp_table_mb;

1
2
3
4
5
6

+-----------------+----------------+----------------+--------------+
| max_connections | sort_buffer_kb | join_buffer_kb | tmp_table_mb |
+-----------------+----------------+----------------+--------------+
|             200 |       256.0000 |       256.0000 |  16.00000000 |
+-----------------+----------------+----------------+--------------+
1 row in set

• max_connections (default 151): the number of simultaneous clients. Each open connection uses memory (buffers are allocated per connection and per operation), and a very high number leads to out-of-memory problems and context-switch overhead. A web application with 20 PHP workers does not need 1,000 connections. Use connection pooling and keep the number low.
• sort_buffer_size, join_buffer_size, tmp_table_size are per session, and allocated when needed. Raising them globally to hundreds of MB multiplied by hundreds of connections is a classic way to run out of memory. Increase them for one session or one statement (SET_VAR hint) when a specific query needs it.

1

SHOW GLOBAL STATUS WHERE Variable_name IN ('Threads_connected', 'Max_used_connections', 'Aborted_connects', 'Connection_errors_max_connections');

1
2
3
4
5
6
7
8
9

+-----------------------------------+-------+
| Variable_name                     | Value |
+-----------------------------------+-------+
| Aborted_connects                  | 1     |
| Connection_errors_max_connections | 0     |
| Max_used_connections              | 1     |
| Threads_connected                 | 1     |
+-----------------------------------+-------+
4 rows in set

(Numbers from the test server.) Max_used_connections shows the peak since startup: set max_connections somewhat above it.

5. Temporary tables

GROUP BY, DISTINCT and sorts use internal temporary tables. They live in memory (the TempTable engine, limited by temptable_max_ram, 1 GB by default) and then spill to disk. Watch:

1

SHOW GLOBAL STATUS WHERE Variable_name IN ('Created_tmp_tables', 'Created_tmp_disk_tables');

1
2
3
4
5
6
7

+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 18    |
| Created_tmp_tables      | 2848  |
+-------------------------+-------+
2 rows in set

Many disk temporary tables point to queries that group or sort big result sets. Fix the queries and indexes, not the memory limits.

6. Other useful settings

How to change settings

• Dynamic variables: SET GLOBAL name = value; takes effect immediately for new connections, and is lost at restart.
• Permanent: edit my.cnf (Linux) or my.ini (Windows), then restart, or use SET PERSIST name = value; (MySQL 8), which writes it to mysqld-auto.cnf and applies it now:

1

SET PERSIST max_connections = 200;

• Read-only variables need a restart.
• See what is set where: SELECT * FROM performance_schema.variables_info WHERE variable_name = 'max_connections';

Do not copy configurations blindly

Tuning guides and “my.cnf generators” give numbers for somebody else’s server. Check each setting against the manual and your workload. Start with the buffer pool and the connections, measure, and change one thing at a time.

Tuning order

1. Queries and indexes (the biggest effect by far).
2. Schema (types, keys).
3. Buffer pool size.
4. Redo log size for write-heavy workloads.
5. Connections and pooling.
6. Everything else, only if you measured a problem.