Lesson 20 advanced

Server Administration and Monitoring

Run MySQL day to day: option files and SET PERSIST, global and session system variables, sizing the InnoDB buffer pool and redo log, innodb_dedicated_server, the error, slow and general logs, watching connections with SHOW PROCESSLIST and KILL, the status counters and sys schema views worth graphing, table sizes from information_schema, ANALYZE, CHECK and OPTIMIZE TABLE, mysqlcheck and mysqladmin.

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

SettingDefaultGuidance
innodb_buffer_pool_size128 MBThe cache for data and indexes. On a dedicated server, 50 to 75% of RAM
innodb_redo_log_capacity100 MBSpace for the redo log. Larger values smooth heavy write loads; 1 to 4 GB is common
max_connections151Size to your connection pools, not to “as many as possible”
innodb_flush_log_at_trx_commit11 = fully durable commits. 2 trades up to a second of data on an OS crash for speed
sync_binlog1Keep 1 for durable binary logs, which replication and recovery rely on
binlog_expire_logs_seconds2592000 (30 days)Long enough to cover your backup interval
table_open_cache4000Raise if Opened_tables keeps growing
tmp_table_size / max_heap_table_size16 MBSize 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

LogContainsEnable / locate
Error logStartup, shutdown, crashes, warnings, deadlocks if configuredAlways on; SELECT @@log_error;
Slow query logStatements slower than long_query_timeslow_query_log = ON (lesson 13)
General query logEvery statement from every clientgeneral_log = ON; only briefly, for debugging
Binary logEvery data change, for replication and recoveryOn 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):

MetricWatch for
Threads_connected, Threads_runningRunning threads far above CPU count means queuing
Max_used_connections vs max_connectionsApproaching the limit; “Too many connections” errors
Questions, Com_select, Com_insert, …Throughput and its mix
Slow_queriesSudden increases after deploys
Innodb_row_lock_waits, Innodb_row_lock_timeLock contention
Innodb_buffer_pool_readsWorking set outgrowing memory
Created_tmp_disk_tablesQueries spilling temporary tables to disk
Aborted_connectsWrong passwords, network problems or attacks
Replication lagLesson 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:

StatementDoesWhen
ANALYZE TABLE t;Refreshes index statisticsAfter large data changes or when plans look wrong
CHECK TABLE t;Checks the table for corruptionAfter hardware problems or crashes
OPTIMIZE TABLE t;Rebuilds the table, reclaiming free spaceAfter deleting a large share of rows
REPAIR TABLE t;Repairs MyISAM, ARCHIVE and CSV tablesNot 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

TaskCommand
Show a settingSHOW VARIABLES LIKE 'innodb_buffer_pool_size';
Change and keep a settingSET PERSIST name = value;
Server countersSHOW GLOBAL STATUS LIKE 'Threads%';
ConnectionsSHOW FULL PROCESSLIST; / SELECT * FROM sys.session;
Cancel / killKILL QUERY id; / KILL id;
Recent errorsSELECT * FROM performance_schema.error_log ORDER BY LOGGED DESC LIMIT 20;
Table sizesinformation_schema.TABLES (DATA_LENGTH, INDEX_LENGTH, DATA_FREE)
Refresh statisticsANALYZE TABLE t; / mysqlcheck --analyze db
Health checkmysqladmin ping

Exercise

You run SET GLOBAL max_connections = 500 and restart the server a week later. What is the value after the restart?

  1. Whatever my.cnf or the default says, because SET GLOBAL does not survive a restart
  2. 500, because global settings are always saved
  3. 500, but only for the root account
  4. The server refuses to start until the setting is confirmed
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?

  1. 128 MB, the default
  2. 1 GB
  3. About 10 to 12 GB
  4. 16 GB, all of the memory
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?

  1. KILL 41
  2. KILL QUERY 41
  3. mysqladmin shutdown
  4. FLUSH TABLES
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.cnf or with SET PERSIST; SET GLOBAL is lost at restart.
  • Size innodb_buffer_pool_size to 50 to 75% of RAM on a dedicated server; consider innodb_dedicated_server.
  • Keep the error and slow query logs on; use the general log only briefly.
  • SHOW PROCESSLIST, KILL QUERY and the sys schema answer most “what is happening?” questions.
  • InnoDB needs little maintenance: ANALYZE TABLE regularly, OPTIMIZE TABLE only 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.

↑ ↓ navigate Enter open Esc close