A MySQL server that is set up sensibly and watched a little needs remarkably little care. This lesson covers the settings that matter most, where to look when something is wrong and the routine maintenance worth doing.
Configuration files and system variables
The server reads option files at start, in a fixed order (mysqld --verbose --help | grep -A1 'Default options' prints it). On Linux the main one is /etc/my.cnf or /etc/mysql/my.cnf, often including a directory of extra files. Settings go under [mysqld]:
[mysqld]
innodb_buffer_pool_size = 4G
max_connections = 300
slow_query_log = ON
long_query_time = 0.5
binlog_expire_logs_seconds = 604800
Every setting is a system variable. Many can be changed at runtime:
SHOW VARIABLES LIKE 'max_connections';
SET GLOBAL max_connections = 500; -- until restart, for new connections
SET SESSION sort_buffer_size = 4194304; -- this connection only
SET PERSIST max_connections = 500; -- now and after restarts
SET PERSIST writes the value to mysqld-auto.cnf in the data directory, which overrides my.cnf at the next start. That is convenient, but it means the value in my.cnf is no longer the whole truth. RESET PERSIST max_connections; removes a persisted setting, and performance_schema.variables_info shows where each value came from (VARIABLE_SOURCE).
Variables are global (the server default), session (one connection, initialised from the global value) or both. Changing a global value does not affect connections that are already open.
The settings that matter most
| Setting | Default | Guidance |
|---|---|---|
innodb_buffer_pool_size | 128 MB | The cache for data and indexes. On a dedicated server, 50 to 75% of RAM |
innodb_redo_log_capacity | 100 MB | Space for the redo log. Larger values smooth heavy write loads; 1 to 4 GB is common |
max_connections | 151 | Size to your connection pools, not to “as many as possible” |
innodb_flush_log_at_trx_commit | 1 | 1 = fully durable commits. 2 trades up to a second of data on an OS crash for speed |
sync_binlog | 1 | Keep 1 for durable binary logs, which replication and recovery rely on |
binlog_expire_logs_seconds | 2592000 (30 days) | Long enough to cover your backup interval |
table_open_cache | 4000 | Raise if Opened_tables keeps growing |
tmp_table_size / max_heap_table_size | 16 MB | Size of in-memory temporary tables before they spill |
On a server that runs nothing but MySQL, innodb_dedicated_server = ON sizes the buffer pool and redo log automatically from the machine’s memory and CPUs. It is off by default.
Note: MySQL 8.4 changed several InnoDB defaults to suit modern hardware, for example innodb_io_capacity from 200 to 10000, innodb_adaptive_hash_index and innodb_change_buffering off, and innodb_flush_method = O_DIRECT on Linux. Lesson 22 lists them. If you carry a my.cnf over from 8.0, review values you copied from old tuning guides.
Is the buffer pool big enough? Compare reads from disk with logical reads:
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
Innodb_buffer_pool_reads (from disk) should be a tiny fraction of Innodb_buffer_pool_read_requests. If it is not, and the working set is larger than the pool, add memory.
Logs
| Log | Contains | Enable / locate |
|---|---|---|
| Error log | Startup, shutdown, crashes, warnings, deadlocks if configured | Always on; SELECT @@log_error; |
| Slow query log | Statements slower than long_query_time | slow_query_log = ON (lesson 13) |
| General query log | Every statement from every client | general_log = ON; only briefly, for debugging |
| Binary log | Every data change, for replication and recovery | On by default (lessons 19 and 21) |
The error log is the first place to look whenever the server will not start, restarts unexpectedly or behaves strangely. Since 8.0 it is also queryable: SELECT * FROM performance_schema.error_log ORDER BY LOGGED DESC LIMIT 20;.
Who is connected and what are they doing?
SHOW PROCESSLIST;
+----+-----------------+-----------------+-------+------+---------+-----------------------------+---------------------------------------------------------------------------------------------+
| Id | User | Host | db | Time | Command | State | Info |
+----+-----------------+-----------------+-------+------+---------+-----------------------------+---------------------------------------------------------------------------------------------+
| 5 | event_scheduler | localhost | NULL | 3812 | Daemon | Waiting for next activation | NULL |
| 41 | orbit_app | 10.0.1.12:51344 | orbit | 0 | Query | executing | SELECT id, title, created_at FROM notes WHERE user_id = 2 ORDER BY created_at DESC LIMIT 20 |
| 42 | orbit_app | 10.0.1.13:40218 | orbit | 187 | Sleep | | NULL |
| 48 | root | localhost | NULL | 0 | Query | init | SHOW PROCESSLIST |
+----+-----------------+-----------------+-------+------+---------+-----------------------------+---------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)
SHOW FULL PROCESSLIST shows complete statements. Time is seconds in the current state: a Query with a large time is a slow query, and a Sleep connection is idle (normal for connection pools, suspicious if it holds an open transaction, lesson 14). The richer source is performance_schema.processlist or sys.session, which add memory use, lock waits and the current statement’s progress.
Stop a runaway query or connection:
KILL QUERY 41; -- cancel the statement, keep the connection
KILL 41; -- close the connection, rolling back its open transaction
A killed large UPDATE must roll back everything it changed, which can take as long as the update ran. Do not restart the server to “speed it up”: the rollback simply continues during crash recovery.
Key metrics to watch
Graph these counters from SHOW GLOBAL STATUS (or let a monitoring agent such as Prometheus’ mysqld_exporter, Percona Monitoring and Management, Datadog or New Relic collect them):
| Metric | Watch for |
|---|---|
Threads_connected, Threads_running | Running threads far above CPU count means queuing |
Max_used_connections vs max_connections | Approaching the limit; “Too many connections” errors |
Questions, Com_select, Com_insert, … | Throughput and its mix |
Slow_queries | Sudden increases after deploys |
Innodb_row_lock_waits, Innodb_row_lock_time | Lock contention |
Innodb_buffer_pool_reads | Working set outgrowing memory |
Created_tmp_disk_tables | Queries spilling temporary tables to disk |
Aborted_connects | Wrong passwords, network problems or attacks |
| Replication lag | Lesson 21 |
The sys schema turns performance_schema data into answers:
SELECT * FROM sys.host_summary\G
SELECT * FROM sys.statements_with_full_table_scans LIMIT 5;
SELECT * FROM sys.schema_table_statistics WHERE table_schema = 'orbit';
SELECT * FROM sys.memory_global_total;
How big are my tables?
SELECT TABLE_NAME,
TABLE_ROWS AS approx_rows,
ROUND(DATA_LENGTH / 1024 / 1024, 1) AS data_mb,
ROUND(INDEX_LENGTH / 1024 / 1024, 1) AS index_mb
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'orbit'
ORDER BY DATA_LENGTH + INDEX_LENGTH DESC;
TABLE_ROWS is an estimate for InnoDB. DATA_FREE shows space that is allocated but unused, for example after mass deletes. The statistics behind these columns are cached; ANALYZE TABLE refreshes them.
Table maintenance
InnoDB needs far less maintenance than old MyISAM tables, but a few statements are useful:
| Statement | Does | When |
|---|---|---|
ANALYZE TABLE t; | Refreshes index statistics | After large data changes or when plans look wrong |
CHECK TABLE t; | Checks the table for corruption | After hardware problems or crashes |
OPTIMIZE TABLE t; | Rebuilds the table, reclaiming free space | After deleting a large share of rows |
REPAIR TABLE t; | Repairs MyISAM, ARCHIVE and CSV tables | Not supported for InnoDB |
For InnoDB, OPTIMIZE TABLE runs ALTER TABLE ... FORCE, an online rebuild that still needs time, I/O and temporary disk space as large as the table. Do it only when DATA_FREE shows a lot of reclaimable space.
mysqlcheck runs these statements from the shell for many tables at once:
$ mysqlcheck --analyze orbit
$ mysqlcheck --check --all-databases
mysqladmin
mysqladmin performs administrative tasks from the command line, handy in scripts and health checks:
$ mysqladmin ping
mysqld is alive
$ mysqladmin status
Uptime: 86400 Threads: 4 Questions: 1520033 Slow queries: 12 Opens: 312 Flush tables: 3 Open tables: 245 Queries per second avg: 17.592
$ mysqladmin processlist
$ mysqladmin -i 5 extended-status | grep Threads_running
$ mysqladmin shutdown
Starting, stopping and the data directory
Use the service manager (systemctl start|stop|restart mysql) rather than killing processes. A clean shutdown flushes everything; after a crash InnoDB replays its redo log on the next start, which can take a while for a large redo log. Never delete or edit files in the data directory by hand, including ib_logfile*, #innodb_redo and undo_* files, while or after the server runs: that is the most reliable way to lose data.
Quick reference
| Task | Command |
|---|---|
| Show a setting | SHOW VARIABLES LIKE 'innodb_buffer_pool_size'; |
| Change and keep a setting | SET PERSIST name = value; |
| Server counters | SHOW GLOBAL STATUS LIKE 'Threads%'; |
| Connections | SHOW FULL PROCESSLIST; / SELECT * FROM sys.session; |
| Cancel / kill | KILL QUERY id; / KILL id; |
| Recent errors | SELECT * FROM performance_schema.error_log ORDER BY LOGGED DESC LIMIT 20; |
| Table sizes | information_schema.TABLES (DATA_LENGTH, INDEX_LENGTH, DATA_FREE) |
| Refresh statistics | ANALYZE TABLE t; / mysqlcheck --analyze db |
| Health check | mysqladmin ping |
Exercise
You run SET GLOBAL max_connections = 500 and restart the server a week later. What is the value after the restart?
Show answer
Answer: A. SET GLOBAL changes the running server only. Use SET PERSIST, or edit my.cnf, to keep a value across restarts.
Exercise
On a dedicated 16 GB database server, what is a sensible starting value for innodb_buffer_pool_size?
Show answer
Answer: C. The buffer pool should hold as much of the working set as possible, typically 50 to 75% of RAM on a dedicated server, leaving room for connections, the OS and other buffers.
Exercise
Which statement cancels a long-running query but keeps the client’s connection open?
Show answer
Answer: B. KILL QUERY stops the current statement only. KILL without QUERY closes the connection and rolls back its open transaction.
Key takeaways
- Configure in
my.cnfor withSET PERSIST;SET GLOBALis lost at restart. - Size
innodb_buffer_pool_sizeto 50 to 75% of RAM on a dedicated server; considerinnodb_dedicated_server. - Keep the error and slow query logs on; use the general log only briefly.
SHOW PROCESSLIST,KILL QUERYand the sys schema answer most “what is happening?” questions.- InnoDB needs little maintenance:
ANALYZE TABLEregularly,OPTIMIZE TABLEonly after large deletes.
FAQ
What causes “Too many connections”?
Usually connection leaks or pools that are too large for the number of application instances, not a limit that is too low. Check SHOW PROCESSLIST for many Sleep connections. MySQL keeps one extra connection for accounts with CONNECTION_ADMIN so administrators can still log in.
Is the query cache still a thing?
No. The query cache was removed in MySQL 8.0 because it did not scale on multi-core servers. Cache in the application or with a dedicated cache such as Redis where needed.
How do I see which tables use the most buffer pool memory?
SELECT * FROM sys.innodb_buffer_stats_by_table ORDER BY allocated DESC LIMIT 10; Note that this view scans the buffer pool and can be expensive on very large servers.
Should I tune dozens of variables from online guides?
No. MySQL 8.4 and 9.7 defaults are good. Set the buffer pool, redo log capacity, connection limits and logging, measure, and change other settings only when a metric points at them.