Server-level tuning means changing how mysqld itself uses memory, disk and connections, as opposed to rewriting individual queries. Be clear about one thing before you open my.cnf: no setting will rescue a query that scans ten million rows because an index is missing. Find slow queries first (step 1 below), fix the ones you can with query-level optimisation, and then size the server for the workload that is left.
The defaults quoted on this page are MySQL 8.0 values, checked on 8.0.42. Check your own server, because hosting providers often change them.
Step 0: see what you are running with
SELECT @@innodb_buffer_pool_size/1024/1024 AS buffer_pool_mb,
@@max_connections,
@@innodb_flush_log_at_trx_commit,
@@innodb_redo_log_capacity/1024/1024 AS redo_mb,
@@tmp_table_size/1024/1024 AS tmp_mb,
@@slow_query_log, @@long_query_time;
| buffer_pool_mb | @@max_connections | @@innodb_flush_log_at_trx_commit | redo_mb | tmp_mb | @@slow_query_log | @@long_query_time |
|---|---|---|---|---|---|---|
| 128.00000000 | 151 | 1 | 100.00000000 | 16.00000000 | 0 | 10.000000 |
That is an untouched 8.0 install: a 128 MB buffer pool, 151 connections, the slow log off, and a 10-second threshold for "slow". The 128 MB buffer pool is fine on a laptop and far too small for a production server with several GB of RAM.
Step 1: turn on the slow query log
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1; -- seconds; applies to NEW connections
SELECT @@slow_query_log_file;
One detail catches people out: long_query_time is copied into each session when it connects. Your current session still shows 10 until you reconnect. Leave the log on for a normal business day. Then summarise it with mysqldumpslow (bundled with MySQL) or Percona's pt-query-digest. Fix the top few statements before you change anything else. Finding and fixing slow queries walks through that process, and the EXPLAIN guide shows how to read their plans.
The built-in sys schema can also point at problems without any log file:
SELECT query, exec_count, total_latency
FROM sys.statements_with_full_table_scans
ORDER BY exec_count DESC LIMIT 10;
SELECT * FROM sys.schema_unused_indexes; -- indexes that cost writes but are never read
Read schema_unused_indexes with care. It counts only since the last restart, and on a fresh test server it listed foreign-key indexes, which InnoDB needs even if no query reads them.
Step 2: size the InnoDB buffer pool
The buffer pool caches table and index pages in memory, and it is the single most important memory setting. The MySQL manual suggests that a dedicated database server can give it up to about 80% of RAM. Leave room for per-connection memory, the OS and anything else on the machine. On a shared VM running the app as well, aim much lower.
SET PERSIST innodb_buffer_pool_size = 268435456; -- 256 MB, resized online
SELECT @@innodb_buffer_pool_size/1024/1024 AS buffer_pool_mb;
| buffer_pool_mb |
|---|
| 256.00000000 |
SET PERSIST changes the running server and writes the value to mysqld-auto.cnf, so it survives a restart. The value is rounded to a multiple of the chunk size. Check whether the pool is big enough by comparing logical reads with reads that had to go to disk:
SELECT VARIABLE_NAME, VARIABLE_VALUE
FROM performance_schema.global_status
WHERE VARIABLE_NAME IN ('Innodb_buffer_pool_read_requests','Innodb_buffer_pool_reads');
Innodb_buffer_pool_reads counts requests that missed the cache. Measure the change over an hour of normal traffic, not since startup: a freshly started server always shows misses while the cache warms up. If misses stay high and the server has free RAM, grow the pool. On a machine that hosts nothing but MySQL, innodb_dedicated_server = ON (set in the config file) lets MySQL size the pool and redo log automatically. Its sizing rules changed in 8.4, so read the manual for your version. The buffer pool and caching guide goes deeper.
Step 3: give writes enough redo log
Heavy insert or update workloads can stall when the redo log fills and InnoDB has to flush pages in a hurry. From 8.0.30 one variable controls the total size, innodb_redo_log_capacity (default 100 MB), and you can change it online. Older 8.0 releases used innodb_log_file_size × innodb_log_files_in_group. Increase it if write-heavy periods show stalls; there is little benefit on a read-mostly server.
Step 4: choose durability deliberately
innodb_flush_log_at_trx_commit | Behaviour | What a crash can lose |
|---|---|---|
| 1 (default) | Redo log written and flushed to disk at every commit | Nothing committed |
| 2 | Written at commit, flushed about once per second | About the last second, on an OS crash or power cut |
| 0 | Written and flushed about once per second | About the last second, even if only mysqld crashes |
Setting 2 can speed up bulk loads noticeably. Use it for a staging import or an analytics copy you can rebuild. Never use it for a payments or orders database where every committed row matters.
Step 5: connections, threads and temporary tables
- max_connections: compare it with
Max_used_connectionsin global status. "Too many connections" errors usually mean the app opens connections and does not close them. A connection pool in the application helps more than raising the limit, because each connection reserves its own buffers. - Temporary tables: if
Created_tmp_disk_tablesgrows quickly compared withCreated_tmp_tables, largeGROUP BY,DISTINCTorUNIONresults are spilling to disk. Fix the query or index first; raisetmp_table_sizeonly if that is not enough. - Per-session buffers (
sort_buffer_size,join_buffer_size): leave them at the defaults unless you have measured a reason. They are allocated per connection, so large values multiply quickly.
Advice you can ignore on MySQL 8
- The query cache was removed in MySQL 8.0. Old tuning guides that tell you to set
query_cache_sizeno longer apply. - Switching tables to MyISAM for speed: InnoDB is the default for good reasons (transactions, crash recovery, row locks). See InnoDB vs MyISAM.
- Copy-pasting a "perfect my.cnf": settings depend on RAM, disk type, and whether the workload is read-heavy or write-heavy. Change one variable at a time and measure the effect.