Monitor and maintain MySQL: Performance Schema, slow queries and OPTIMIZE

A MySQL in production that slowly degrades: queries going from 2 ms to 200 ms, connections piling up, InnoDB tables growing for no apparent reason. This is the classic scenario when nobody is watching. MySQL ships a complete instrumentation system, Performance Schema, the sys schema and the slow query log, which can diagnose every problem, but you still need to know what to look at and when to act.

This guide follows an observe → identify → explain → fix approach. The commands were replayed on October 20, 2026 (except those marked NOT TESTED) on MySQL 8.4.11 (see the box below for the exact environment), on a lab_mysql database with customers, orders and logs tables. You will be able to read the Performance Schema views, interpret an execution plan, detect fragmented tables and use OPTIMIZE TABLE, ANALYZE TABLE and CHECK TABLE at the right time.

Version and environment of the measurements

This guide relies on MySQL 8.4 LTS. Two features that are useful here were verified on this version: EXPLAIN INTO, which stores a plan in a variable, and the automatic update of histograms (AUTO UPDATE).

The outputs of this page were copied from a server, not reconstructed. Version measured: MySQL 8.4.11 (MySQL Community Server - GPL), official image mysql:8.4@sha256:0f51e63e537a1f176423fa03ef559aabe6fa4372a53160a44bc7e75da7d15364, built on Oracle Linux 9.8, in a Docker container started with --network none. The default values quoted (innodb_buffer_pool_size 134217728, long_query_time 10, innodb_redo_log_capacity 104857600, innodb_adaptive_hash_index OFF) are those of this image.

This container has no systemd. Neither systemctl, nor restarting the service, nor the log file of a Debian or Red Hat package were replayed. The mysqldumpslow, mysqlcheck and innochecksum tools are not shipped in the Docker image: their commands were not run. On a server installed from a package, your paths and your timings will differ.

The following are therefore NOT TESTED and given as documentation facts: systemctl and the persistence of SET PERSIST across a restart, mysqldumpslow, mysqlcheck, innochecksum, mysqld_exporter and its metrics, SHOW REPLICA STATUS on a real replica (on this server without replication, the command returns no row), the lock taken by ANALYZE TABLE and the server shutdown caused by CHECK TABLE. The durations quoted come from a single run on a small container: read them as orders of magnitude, not as references.

What you will learn

• Diagnose current activity with SHOW PROCESSLIST and performance_schema.processlist, and find the session that blocks a row with sys.innodb_lock_waits
• Identify slow queries with the slow query log and sys.statement_analysis
• Interpret an EXPLAIN ANALYZE plan and understand ALL, ref, range, Index Scan
• Monitor InnoDB: buffer pool hit ratio, redo log, transactions and purge lag
• Maintain performance with OPTIMIZE TABLE, ANALYZE TABLE and CHECK TABLE
• Integrate MySQL into Prometheus/Grafana monitoring

In what context?

You need to monitor and maintain MySQL in these situations:

• Your application slows down gradually and you suspect the database
• You want to set up proactive monitoring before problems appear
• Slow queries show up in the application logs but you do not know which ones on the database side
• You must identify the most expensive queries to know where to optimize (missing indexes, full table scans)
• You are preparing a maintenance plan for a production database (fragmentation, stale statistics)
• You are debugging a locking problem or connections piling up

What this guide does not cover

Three neighbouring subjects have their own page, to avoid dealing here with settings that have nothing to do with observation. Consult them if your need is about sizing, backup or access rights rather than performance diagnosis.

Sizing (buffer pool, connections, binary log, SET PERSIST) is covered in the configuration guide. Backup (mysqldump, MySQL Shell, XtraBackup, PITR) has its page: backup and restore, to be read before any rebuild operation. Access rights (caching_sha2_password, TLS, roles, audit) belong to securing MySQL.

Prerequisites

• MySQL 8.4 installed and the service running, see the installation guide
• Access to the MySQL root account
• Know the basics of the mysql client, see the getting started guide
• A database with data so there is something to observe (this guide's examples use lab_mysql with the clients, commandes, logs tables; the lab at the end of the page provides its own database)

The approach: observe → identify → explain → fix

Diagnosing a MySQL performance problem always follows the same order:

1. Observe, what is happening right now? (SHOW PROCESSLIST, connections, locks)
2. Identify, which queries consume the most? (slow query log, sys.statement_analysis)
3. Explain, why is this query slow? (EXPLAIN ANALYZE, execution plan)
4. Fix, add an index, rewrite the query, or maintain (OPTIMIZE TABLE, ANALYZE TABLE)

Do not skip steps. An OPTIMIZE TABLE run blindly on a large production table rebuilds the whole table and consumes disk space and I/O, with no guarantee of gain. An index added without reading the plan can make the situation worse.

Observe server activity

This first step answers a single question: what is the server doing right now? All the commands in this section read an instantaneous state and change nothing, so you can run them in production without risk. They fall into two families: the connection views, which show who is there and for how long, and the global counters, accumulated since the server started, which must therefore be read as trends and not as instantaneous values.

SHOW PROCESSLIST and performance_schema.processlist

SHOW PROCESSLIST is the first reflex to see all active connections:

SHOW PROCESSLIST;

+----+-----------------+-----------+-----------+---------+------+------------------------+------------------+
| Id | User            | Host      | db        | Command | Time | State                  | Info             |
+----+-----------------+-----------+-----------+---------+------+------------------------+------------------+
|  5 | event_scheduler | localhost | NULL      | Daemon  |   34 | Waiting on empty queue | NULL             |
| 11 | root            | localhost | lab_mysql | Query   |    0 | init                   | SHOW PROCESSLIST |
+----+-----------------+-----------+-----------+---------+------+------------------------+------------------+

Limitation: SHOW PROCESSLIST truncates the Info column to 100 characters. Use SHOW FULL PROCESSLIST or the Performance Schema view to see complete queries:

SELECT ID,
       USER,
       HOST,
       DB,
       COMMAND,
       TIME AS time_sec,
       STATE,
       LEFT(INFO, 80) AS query
FROM performance_schema.processlist
WHERE COMMAND <> 'Daemon';

Column	Meaning
Id	Connection identifier (usable with KILL)
Command	Type of operation (Query, Sleep, Connect, Daemon)
Time	Seconds since the start of the current state
State	Internal step (sending data, executing, Waiting for lock…)
Info	Query currently running

Swipe to see more

Sleep connections: the common problem

Counting connections by state gives you the health of the application pool in a single query. A majority of Sleep is not abnormal in itself, but their number must stay stable over time: if it rises without coming back down, the connections are not being returned.

SELECT COMMAND, COUNT(*) AS cnt
FROM performance_schema.processlist
GROUP BY COMMAND;

Command	Meaning	Action if too many
Query	Running a query	Normal, check whether it runs too long
Sleep	Connected, doing nothing	Misconfigured connection pool or wait_timeout too high
Daemon	Internal process (event_scheduler)	Normal

Swipe to see more

Sleep connections pile up

An application that opens connections without closing them fills max_connections with Sleep connections. Reduce wait_timeout (default: 28800 seconds = 8 hours!) to clean up automatically:

SET PERSIST wait_timeout = 300; -- 5 minutes
SET PERSIST interactive_timeout = 300;

SET PERSIST only changes the global value: sessions already open keep their wait_timeout of 28800 s, only new connections inherit 300 s. Measured right after the command: @@session.wait_timeout is 28800 and @@global.wait_timeout is 300. The Sleep connections already in place therefore do not disappear by themselves; you have to KILL them.

Kill a query or a connection

The identifier to pass is the one from the Id column read just before. The difference between the two forms matters: KILL QUERY interrupts the query while leaving the session open, KILL CONNECTION closes the session, which causes a rollback of the current transaction and an error on the application side.

-- Cancel the running query (connection stays open)
KILL QUERY 42;

-- Kill the whole connection
KILL CONNECTION 42;

-- Short synonym
KILL 42;

Measured trap: KILL QUERY does not release locks. A session that ran an UPDATE without COMMIT and then went idle (state Sleep) executes no query any more: KILL QUERY finds nothing to interrupt and the row stays locked. In our test, the blocked query of the other session kept waiting after KILL QUERY, and only got its lock after KILL <id>, which closes the connection and cancels the transaction.

Find who is blocking a row

When a query stays frozen in the updating or Waiting for lock state, the sys.innodb_lock_waits view points to the waiting session and the blocking one, with the KILL commands ready to use. To reproduce, open two sessions: the first runs BEGIN; UPDATE clients SET nom='A' WHERE id=1; SELECT SLEEP(7);, the second UPDATE clients SET nom='B' WHERE id=1;, then a third queries the view:

SELECT wait_age, locked_table, waiting_pid, waiting_query,
       blocking_pid, blocking_query,
       sql_kill_blocking_query, sql_kill_blocking_connection
FROM sys.innodb_lock_waits\G

*************************** 1. row ***************************
                    wait_age: 00:00:02
                locked_table: `lab_mysql`.`clients`
                 waiting_pid: 52
               waiting_query: UPDATE clients SET nom='B' WHERE id=1
                blocking_pid: 51
              blocking_query: SELECT SLEEP(7)
     sql_kill_blocking_query: KILL QUERY 51
sql_kill_blocking_connection: KILL 51

Two traps. First, blocking_query shows the blocker's query in progress (or NULL if the session is idle), not the one that took the lock: here it is the SLEEP, while the lock comes from the previous UPDATE. Second, the Innodb_row_lock_time counter stays at 0 during the wait and is only updated when it ends (17,284 ms after our tests): it is not used to detect a blocking in progress, use Innodb_row_lock_current_waits or this view.

SHOW GLOBAL STATUS: the key counters

SHOW GLOBAL STATUS exposes hundreds of cumulative counters. Here are the most useful:

SHOW GLOBAL STATUS WHERE Variable_name IN (
  'Threads_connected', 'Threads_running',
  'Max_used_connections', 'Connections',
  'Slow_queries', 'Questions',
  'Innodb_buffer_pool_read_requests', 'Innodb_buffer_pool_reads',
  'Innodb_row_lock_waits', 'Innodb_row_lock_time'
);

Counter	Meaning
Threads_connected	Connections currently open
Threads_running	Connections currently executing a query
Max_used_connections	Peak of simultaneous connections since startup
Slow_queries	Total number of slow queries detected
Innodb_buffer_pool_read_requests	Logical reads (from the buffer pool)
Innodb_buffer_pool_reads	Physical reads (from disk)
Innodb_row_lock_waits	Row lock waits
Innodb_row_lock_time	Total time spent waiting for locks (ms)

Swipe to see more

The buffer pool hit ratio is computed like this:

SELECT
  (1 - (
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status
     WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads') /
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status
     WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests')
  )) * 100 AS buffer_pool_hit_ratio;

On the lab, right after loading the tables, the query returns:

+-----------------------+
| buffer_pool_hit_ratio |
+-----------------------+
|     99.64990124540518 |
+-----------------------+

A status counter is accumulated since startup: this ratio smooths the whole history and hides a recent problem. Slow_queries also counts the queries recorded by log_queries_not_using_indexes, even fast ones (see the section on the slow query log). A ratio above 99% is normal for an OLTP workload. Below 95%, increase innodb_buffer_pool_size, see the configuration guide.

SHOW ENGINE INNODB STATUS: the instant diagnosis

SHOW ENGINE INNODB STATUS\G displays a complete report on the internal state of InnoDB: active transactions, locks, buffer pool, I/O, redo log. It is one of MySQL's richest diagnostic tools:

SHOW ENGINE INNODB STATUS\G

The key sections of the report:

Section	What it shows
SEMAPHORES	Waits on internal locks (mutex, rw-lock)
TRANSACTIONS	Active transactions, history, purge
FILE I/O	I/O threads, pending operations
BUFFER POOL AND MEMORY	Pool size, free/dirty/data pages, hit rate
ROW OPERATIONS	INSERT/UPDATE/DELETE per second
LOG	Redo log position, LSN sequence
LATEST DETECTED DEADLOCK	Last deadlock detected by InnoDB (the section does not exist until a deadlock has occurred)

Swipe to see more

Deadlock history

SHOW ENGINE INNODB STATUS only shows the last deadlock. To record all deadlocks in the error log, enable innodb_print_all_deadlocks = ON, essential in production to analyze recurring deadlocks.

The slow query log

The slow query log records all queries whose execution time exceeds a threshold. It is MySQL's equivalent of PostgreSQL's log_min_duration_statement: a log file, not aggregated statistics.

Enable the slow query log

SET PERSIST applies the setting immediately and writes it into mysqld-auto.cnf, which makes it permanent without touching the main configuration file or restarting the service. The log file must be writable by the mysql system account, hence the choice of the data directory.

-- Enable without restarting (SET PERSIST to survive a reboot)
SET PERSIST slow_query_log = ON;
SET PERSIST slow_query_log_file = '/var/lib/mysql/slow.log';
SET PERSIST long_query_time = 1; -- threshold in seconds

By default, the log is disabled (slow_query_log is OFF) and the threshold is 10 seconds. The default file carries the machine name (<host>-slow.log in the data directory); we set it here so we can find it. The configuration guide gives the recommended production values: this page concentrates on using the log.

Check. Beware of the trap: SET PERSIST long_query_time changes the global value, but not that of your current session. A plain SHOW VARIABLES (session scope) still answers 10.000000 right after, and your test queries in that same session are not judged against the new threshold. Read the global value, and open a new session for your tests:

SHOW GLOBAL VARIABLES LIKE 'slow_query%';
SHOW GLOBAL VARIABLES LIKE 'long_query_time';

+---------------------+-------------------------+
| Variable_name       | Value                   |
+---------------------+-------------------------+
| slow_query_log      | ON                      |
| slow_query_log_file | /var/lib/mysql/slow.log |
+---------------------+-------------------------+

+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+

The settings made with SET PERSIST can be read in performance_schema.persisted_variables. The server restart, which is what proves persistence, was not replayed (container without systemd).

long_query_time: the threshold

This threshold, expressed in seconds, accepts decimals. It alone determines the volume of the log file, and a setting that is too low on a busy database can saturate the disk within a few hours: start high, then go down.

Value	Use
10 (default)	Too high, only captures catastrophic queries
1	Good starting point for production
0.5	For an in-depth audit
0	Captures all queries, beware of disk space and I/O

Swipe to see more

log_queries_not_using_indexes

Enables the capture of queries that use no index, even if they are fast:

SET PERSIST log_queries_not_using_indexes = ON;

-- Limit the number of logs per minute to avoid an explosion
SET PERSIST log_throttle_queries_not_using_indexes = 10;

It is a good way to detect full table scans on tables that will grow: a query that is fast today on 1,000 rows will become slow on 1 million.

Measured trap: with this option, a query is logged even if it lasts a few hundred microseconds, regardless of long_query_time. On our server, a simple read of performance_schema.persisted_variables (5 rows) entered the slow log with Query_time: 0.000365, and the Slow_queries counter went up with it. On a busy database, the log fills with unimportant queries that drown the real slow ones: keep log_throttle_queries_not_using_indexes low, and leave the option active only during an audit.

Analyze the logs with mysqldumpslow

mysqldumpslow aggregates the slow log entries and ranks them by frequency, average time or total time:

# Top 10 queries by total time
mysqldumpslow -s t -t 10 /var/lib/mysql/slow.log

# Top 10 by number of calls
mysqldumpslow -s c -t 10 /var/lib/mysql/slow.log

# Top 10 by average time
mysqldumpslow -s at -t 10 /var/lib/mysql/slow.log

NOT TESTED: mysqldumpslow is not shipped in the Docker image used, so these three commands were not run and their output is not reproduced here. What the tool reads, however, was measured: here is a real entry from the slow.log file, produced by a query that scans the whole logs table (90,000 rows):

01.494966Z

# User@Host: root[root] @ localhost []  Id:    12
# Query_time: 0.014073  Lock_time: 0.000002 Rows_sent: 1  Rows_examined: 90000
SET timestamp=1792511521;
SELECT COUNT(*) FROM logs WHERE action = 'login';

Reading: Query_time is the duration (14 ms), Rows_examined the number of rows read (90,000) against Rows_sent, the rows returned (1). A large gap between the two points to a query that needs an index. mysqldumpslow groups these entries by normalized query; without it, grep and sort on these Query_time lines already give a first idea.

Difference with PostgreSQL

PostgreSQL uses pg_stat_statements (in-memory extension, real-time aggregated statistics). MySQL uses the slow query log (file on disk) + mysqldumpslow for analysis. The in-memory equivalent on the MySQL side is performance_schema.events_statements_summary_by_digest.

Performance Schema

The slow query log only sees what exceeds a threshold, and only for queries. Performance Schema covers far more and works in memory, without writing to disk. It is the data source on which the sys schema and most metrics exporters then rely: understanding its organization into instruments and consumers avoids looking in the wrong place.

Principle and activation

Performance Schema is MySQL's internal instrumentation system. It collects statistics on everything: queries, locks, I/O, memory, threads, internal stages. Enabled by default since MySQL 5.6, its memory consumption depends on the number of instruments and consumers enabled, check with SHOW ENGINE PERFORMANCE_SCHEMA STATUS.

SHOW VARIABLES LIKE 'performance_schema';

+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| performance_schema | ON    |
+--------------------+-------+

Instruments and consumers

Performance Schema works in two layers:

• Instruments: the measurement points (queries, locks, I/O, memory…)
• Consumers: the tables that store the collected data

-- Number of available instruments
SELECT COUNT(*) FROM performance_schema.setup_instruments;
-- 1260 instruments on MySQL 8.4.11

-- Instruments for statements (queries)
SELECT NAME, ENABLED, TIMED
FROM performance_schema.setup_instruments
WHERE NAME LIKE 'statement/sql/%'
LIMIT 5;

The number of instruments depends on the version: the COUNT(*) query returned 1260 on 8.4.11. Without an ORDER BY, the order of rows is not guaranteed; here is what the server returned:

+----------------------------+---------+-------+
| NAME                       | ENABLED | TIMED |
+----------------------------+---------+-------+
| statement/sql/select       | YES     | YES   |
| statement/sql/create_table | YES     | YES   |
| statement/sql/create_index | YES     | YES   |
| statement/sql/alter_table  | YES     | YES   |
| statement/sql/update       | YES     | YES   |
+----------------------------+---------+-------+

The events_statements_* tables

Performance Schema collects queries in several tables:

Table	Content
events_statements_current	Queries currently running
events_statements_history	Latest queries per thread
events_statements_history_long	Extended history (configurable)
events_statements_summary_by_digest	Aggregated statistics by type of query (normalization)
events_statements_summary_by_user_by_event_name	Per user

Swipe to see more

sys.statement_analysis: the top queries

The sys.statement_analysis view is MySQL's equivalent of PostgreSQL's pg_stat_statements top 10, ready to use, with no installation:

SELECT query,
       exec_count,
       avg_latency,
       total_latency,
       rows_sent_avg,
       rows_examined_avg,
       full_scan
FROM sys.statement_analysis
LIMIT 10;

Real output, right after loading the lab (each query was run only once). Note that the queries are normalized: values become ?, keywords and sometimes table names turn uppercase (LOGS, ACTION), and long queries are truncated in the middle with ....

+-------------------------------------------------------------------+------------+-------------+---------------+---------------+-------------------+-----------+
| query                                                             | exec_count | avg_latency | total_latency | rows_sent_avg | rows_examined_avg | full_scan |
+-------------------------------------------------------------------+------------+-------------+---------------+---------------+-------------------+-----------+
| SELECT `SLEEP` (?)                                                |          1 | 1.20 s      | 1.20 s        |             1 |                 1 |           |
| INSERT INTO LOGS ( `client_id` ...  ? ) SELECT `n` FROM `s` ) `t` |          1 | 526.59 ms   | 526.59 ms     |             0 |             90000 | *         |
| CREATE TABLE `commandes` ( `id ... ` ( `id` ) ) ENGINE = `InnoDB` |          1 | 58.06 ms    | 58.06 ms      |             0 |                 0 |           |
| CREATE TABLE `clients` ( `id`  ... RACTER (?) ) ENGINE = `InnoDB` |          1 | 43.62 ms    | 43.62 ms      |             0 |                 0 |           |
| CREATE TABLE LOGS ( `id` INTEG ... EFAULT NOW ) ENGINE = `InnoDB` |          1 | 34.26 ms    | 34.26 ms      |             0 |                 0 |           |
| SELECT COUNT ( * ) FROM LOGS WHERE ACTION = ?                     |          1 | 14.10 ms    | 14.10 ms      |             1 |             90000 | *         |
| INSERT INTO `clients` ( `nom`  ... il` ) VALUES (...) /* , ... */ |          1 | 11.55 ms    | 11.55 ms      |             0 |                 0 |           |
| INSERT INTO `commandes` ( `cli ...  ? ) SELECT `n` FROM `s` ) `t` |          1 | 9.60 ms     | 9.60 ms       |             0 |               200 | *         |
| CREATE SCHEMA `lab_mysql`                                         |          1 | 8.80 ms     | 8.80 ms       |             0 |                 0 |           |
| SET PERSIST `slow_query_log_file` = ?                             |          1 | 8.48 ms     | 8.48 ms       |             0 |                 0 |           |
+-------------------------------------------------------------------+------------+-------------+---------------+---------------+-------------------+-----------+

Reading these lines: the SLEEP is the longest (1.20 s) but reads nothing, it is a test artifact. The SELECT COUNT ( * ) FROM LOGS WHERE ACTION = ? query is the real alert: 90,000 rows examined for 1 returned, with full_scan = *. The INSERT ... SELECT on LOGS (526.59 ms) is also flagged full_scan because it reads its source.

What to look at first:

• High total_latency: the query that consumes the most server time in total, priority no. 1
• High avg_latency + low exec_count: individually slow query (missing index?)
• full_scan = *: the query does a full table scan, missing index
• rows_examined_avg >> rows_sent_avg: MySQL reads far too many rows to return few

sys.innodb_buffer_stats_by_table

This view breaks down the buffer pool occupancy table by table, which lets you check that memory benefits the hot tables. It is, however, very expensive, as the warning after the block says:

SELECT object_schema, object_name,
       allocated, data, pages
FROM sys.innodb_buffer_stats_by_table
WHERE object_schema NOT IN ('mysql', 'sys', 'performance_schema')
LIMIT 5;

+---------------+-------------+-----------+----------+-------+
| object_schema | object_name | allocated | data     | pages |
+---------------+-------------+-----------+----------+-------+
| lab_mysql     | logs        | 3.50 MiB  | 3.18 MiB |   224 |
| lab_mysql     | commandes   | 32.00 KiB | 8.79 KiB |     2 |
| InnoDB System | SDI_2       | 16.00 KiB | 1.32 KiB |     1 |
| InnoDB System | SDI_3       | 16.00 KiB | 1.44 KiB |     1 |
| InnoDB System | SDI_4       | 16.00 KiB | 1.37 KiB |     1 |
+---------------+-------------+-----------+----------+-------+

Here, the logs table occupies 224 pages (3.50 MiB) of the buffer pool: it is the table that has just been loaded, so the most present in memory. The InnoDB System rows are internal pages, not lab tables.

View that is expensive to query

sys.innodb_buffer_stats_by_table relies on INFORMATION_SCHEMA.INNODB_BUFFER_PAGE, whose querying can have a measurable impact on performance. Avoid this view in production except for an occasional, controlled need.

The sys schema: ready-to-use views

The sys schema is a collection of views, functions and procedures that simplify the use of Performance Schema. Installed by default since MySQL 5.7.

sys.processlist: an alternative to SHOW PROCESSLIST

This view reuses the usual columns and adds measurements that SHOW PROCESSLIST does not expose. The filter on conn_id IS NOT NULL discards background threads, but not the server's Daemon threads, which otherwise clutter the reading: you must add AND command <> 'Daemon'.

SELECT thd_id, conn_id, user, db,
       command, state, time,
       LEFT(current_statement, 60) AS query,
       trx_latency, lock_latency
FROM sys.processlist
WHERE conn_id IS NOT NULL
  AND command <> 'Daemon';

With the conn_id IS NOT NULL filter alone, we got three rows, two of which are internal threads:

+--------+---------+-------------------------+------+---------+------------------------+------+--------------------------------------------------------------+-------------+--------------+
| thd_id | conn_id | user                    | db   | command | state                  | time | query                                                        | trx_latency | lock_latency |
+--------+---------+-------------------------+------+---------+------------------------+------+--------------------------------------------------------------+-------------+--------------+
|     45 |       5 | sql/event_scheduler     | NULL | Daemon  | Waiting on empty queue |   12 | NULL                                                         | NULL        | NULL         |
|     48 |       7 | sql/compress_gtid_table | NULL | Daemon  | Suspending             |   12 | NULL                                                         | 14.19 us    | NULL         |
|     55 |      14 | root@localhost          | NULL | Query   | NULL                   |    0 | SET @sys.statement_truncate_le ... ('statement_truncate_len' | 11.65 ms    | 21.00 us     |
+--------+---------+-------------------------+------+---------+------------------------+------+--------------------------------------------------------------+-------------+--------------+

With AND command <> 'Daemon', only the users' connections remain (here, the session running the query).

The advantage over SHOW PROCESSLIST: the view adds trx_latency (transaction duration) and lock_latency (time spent waiting for a lock).

sys.schema_table_statistics

This view consolidates activity per table: how many rows were read, inserted, modified or deleted, and what volume of input/output that represents. It is a direct way to know which tables are really working, before deciding where to put an index or what to maintain.

SELECT table_schema, table_name,
       rows_fetched, rows_inserted, rows_updated, rows_deleted,
       io_read, io_write
FROM sys.schema_table_statistics
WHERE table_schema = 'lab_mysql';

+--------------+------------+--------------+---------------+--------------+--------------+------------+------------+
| table_schema | table_name | rows_fetched | rows_inserted | rows_updated | rows_deleted | io_read    | io_write   |
+--------------+------------+--------------+---------------+--------------+--------------+------------+------------+
| lab_mysql    | logs       |        90014 |         90000 |            0 |            0 | 0 bytes    | 11.72 MiB  |
| lab_mysql    | commandes  |            0 |           200 |            0 |            0 | 0 bytes    | 352.00 KiB |
| lab_mysql    | clients    |            1 |             6 |            0 |            0 | 0 bytes    | 320.00 KiB |
+--------------+------------+--------------+---------------+--------------+--------------+------------+------------+

The logs table concentrates the activity: 90,000 rows inserted, 90,014 read, 11.72 MiB written. io_read is 0 bytes because everything was read from the buffer pool, without touching the disk. These counters are accumulated since the server started.

sys.schema_redundant_indexes

Detects redundant indexes (an index that is a prefix of another):

SELECT table_schema, table_name,
       redundant_index_name, redundant_index_columns,
       dominant_index_name, dominant_index_columns
FROM sys.schema_redundant_indexes
WHERE table_schema = 'lab_mysql';

This view returns nothing as long as no index is redundant. To see it react, create an index on action, while a composite index (action, client_id) already covers it:

CREATE INDEX idx_logs_action_client ON logs (action, client_id);
CREATE INDEX idx_logs_action ON logs (action);

Rerun the query on sys.schema_redundant_indexes above: it then returns a row, which designates idx_logs_action as the redundant index and idx_logs_action_client as the dominant index.

+--------------+------------+----------------------+-------------------------+------------------------+------------------------+
| table_schema | table_name | redundant_index_name | redundant_index_columns | dominant_index_name    | dominant_index_columns |
+--------------+------------+----------------------+-------------------------+------------------------+------------------------+
| lab_mysql    | logs       | idx_logs_action      | action                  | idx_logs_action_client | action,client_id       |
+--------------+------------+----------------------+-------------------------+------------------------+------------------------+

Then clean up the test, so that the EXPLAIN section starts again from a table without indexes: DROP INDEX idx_logs_action ON logs; then DROP INDEX idx_logs_action_client ON logs;.

A redundant index consumes space and slows down writes (INSERT, UPDATE, DELETE) without bringing any benefit to reads. Remove it after checking.

sys.host_summary and sys.user_summary

These two views aggregate the same activity along two different axes, the client host and the MySQL account. The table_scans column is particularly telling there: it points to the application or account to talk to when full table scans multiply. The columns have precise names (statement_latency, statement_avg_latency, table_scans): a similar name such as total_latency or full_scans does not exist in these two views and causes the error 1054 Unknown column.

-- By host
SELECT host, statements, statement_latency, statement_avg_latency, table_scans
FROM sys.host_summary;

-- By user
SELECT user, statements, statement_latency, statement_avg_latency, table_scans
FROM sys.user_summary;

+-----------+------------+-------------------+-----------------------+-------------+
| host      | statements | statement_latency | statement_avg_latency | table_scans |
+-----------+------------+-------------------+-----------------------+-------------+
| localhost |        156 | 4.05 s            | 25.97 ms              |          46 |
+-----------+------------+-------------------+-----------------------+-------------+

+-----------------+------------+-------------------+-----------------------+-------------+
| user            | statements | statement_latency | statement_avg_latency | table_scans |
+-----------------+------------+-------------------+-----------------------+-------------+
| root            |         79 | 2.03 s            | 25.68 ms              |          24 |
| background      |          0 | 0 ps              | 0 ps                  |           0 |
| event_scheduler |          0 | 0 ps              | 0 ps                  |           0 |
+-----------------+------------+-------------------+-----------------------+-------------+

Here, 46 full scans of tables over 156 queries from localhost: all the traffic comes from the root account, which is normal for a lab. On a real server, a high table_scans for an application account points to the application to examine.

Analyze queries with EXPLAIN

The previous sections say which query is expensive. EXPLAIN answers the next question: why. The tool exposes the plan chosen by the optimizer, that is, the order of accesses and the indexes retained. Two variants coexist and are not used under the same conditions: plain EXPLAIN executes nothing and is safe in production, whereas EXPLAIN ANALYZE really runs the query to measure each step.

EXPLAIN: the execution plan

EXPLAIN shows the plan MySQL intends to use to run a query, without running it:

EXPLAIN SELECT * FROM logs WHERE action = 'login' AND client_id = 3;

+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | logs  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 90315 |     1.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+

type = ALL means a full table scan: MySQL reads the whole table (rows is 90,315, an estimate: the table contains exactly 90,000 rows) to filter out a few. The filtered column (1.00%) is the share of rows that the optimizer believes it will keep. This is the worst possible case.

EXPLAIN ANALYZE: the plan with real timings

EXPLAIN ANALYZE really runs the query and measures the time of each step:

EXPLAIN ANALYZE SELECT * FROM logs WHERE action = 'login' AND client_id = 3\G

-> Filter: ((`logs`.client_id = 3) and (`logs`.`action` = 'login'))  (cost=9088 rows=903) (actual time=0.243..15.8 rows=5000 loops=1)
    -> Table scan on logs  (cost=9088 rows=90315) (actual time=0.237..11.8 rows=90000 loops=1)

Reading: MySQL scans the 90,000 rows (Table scan) in 11.8 ms, then filters to keep only 5,000 rows in 15.8 ms in total. The estimator predicted 903 rows, 5.5 times fewer than reality. This is not a sign of stale statistics: ANALYZE TABLE had just been run and the estimate stayed the same. On a non-indexed column, the optimizer has no distribution statistic and applies a default estimate. Only a histogram corrects this (see the "Histograms" subsection of the table maintenance part). The exact estimate varies from one load to the next (866 to 3,345 in our tests), and so do the durations (11 to 24 ms for the same scan).

EXPLAIN FORMAT=JSON and FORMAT=TREE

The default tabular format becomes unreadable as soon as there are joins or subqueries. FORMAT=TREE renders the hierarchy of operations and is read from the inside out; FORMAT=JSON contains the same information with the detailed costs, in a structure intended for tools. The \G at the end of the line replaces the column display with a vertical display, which is indispensable here.

-- Tree format (more readable, recommended since 8.0)
EXPLAIN FORMAT=TREE SELECT * FROM logs WHERE action = 'login' AND client_id = 3\G

-- JSON format (full detail, useful for tools)
EXPLAIN FORMAT=JSON SELECT * FROM logs WHERE action = 'login' AND client_id = 3\G

Real output of FORMAT=TREE: same plan as EXPLAIN ANALYZE, without the measured durations, since the query is not run:

-> Filter: ((`logs`.client_id = 3) and (`logs`.`action` = 'login'))  (cost=9088 rows=903)
    -> Table scan on logs  (cost=9088 rows=90315)

EXPLAIN INTO (MySQL 8.4+)

EXPLAIN ... INTO stores the plan in a variable you can use afterwards. Measured on 8.4.11: EXPLAIN INTO requires FORMAT=JSON (without it, error 6006 EXPLAIN INTO does not support implicit FORMAT) and is not compatible with EXPLAIN ANALYZE (syntax error 1064):

EXPLAIN FORMAT=JSON INTO @plan
SELECT * FROM logs WHERE action = 'login';

SELECT JSON_PRETTY(@plan)\G
SELECT JSON_EXTRACT(@plan, '$.query_block.cost_info.query_cost') AS cost;

The second SELECT extracts the query's estimated cost: it returned "4684.45" on our table without an index. Since the variable is JSON, JSON_EXTRACT is enough to compare plans programmatically.

Fix it: add an index and compare

The index is composite and the order of the columns follows that of the WHERE: MySQL can only use the columns of a composite index from left to right, so an index (client_id, action) would not serve a query that filters only on action. The comparison of the two plans, before and after, is the only proof that the index is of any use.

CREATE INDEX idx_logs_action_client ON logs (action, client_id);

EXPLAIN ANALYZE SELECT * FROM logs WHERE action = 'login' AND client_id = 3\G

-> Index lookup on logs using idx_logs_action_client (action='login', client_id=3)  (cost=669 rows=5000) (actual time=0.625..6.68 rows=5000 loops=1)

The estimated cost drops from 9088 to 669, and the row estimate becomes exact (5000). In terms of time, we measured 15.8 ms (full table scan) against 6.68 ms (index lookup), a gain of about 2.4 times. This is a single measurement per plan: two more runs of the index gave 6.87 and 5.99 ms, and the same scan varied from 11 to 24 ms across tests. The gain stays modest here because the query returns 5,000 of the 90,000 rows: the more selective a query, the more the index pays off.

The access types to understand

The type column of EXPLAIN indicates the access mode, from worst to best:

Type	Meaning	Performance
ALL	Full table scan, reads all rows	Worst
index	Full index scan, walks the whole index	Poor
range	Partial index scan (BETWEEN, <, >, IN)	Acceptable
ref	Lookup through a non-unique index	Good
eq_ref	Lookup through a unique index (PK join)	Very good
const	Lookup through the primary key, single result	Optimal
system	Single-row table	Optimal

Swipe to see more

Rule: if you see ALL on a table of more than 10,000 rows with a WHERE, it is most likely a missing index.

Monitor InnoDB

A query can be perfectly indexed and still be slow if the storage engine is under strain. InnoDB exposes four areas to watch, in this order of importance: the memory cache, the transaction journal, the purge of old row versions and, more marginally, the adaptive hash index. All of them can be read in the same SHOW ENGINE INNODB STATUS report, which explains why it comes back in every section.

Buffer pool: hit rate and dirty pages

The buffer pool is InnoDB's memory cache, and its hit rate is a central performance metric:

SHOW ENGINE INNODB STATUS\G

Extract from the BUFFER POOL AND MEMORY section:

Buffer pool size   8192
Free buffers       1829
Database pages     6363
Old database pages 2366
Modified db pages  0
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000

This reading comes from the idle server, after the page's tests: 8192 pages of 16 KB (that is 128 MB, the default value) of which 6363 are occupied by data, and no modified page waiting to be written. Right after loading logs, the same report gave Free buffers 6675, Database pages 1517, Modified db pages 270 (dirty pages not yet written) and a Buffer pool hit rate 997 / 1000.

Metric	Meaning
Buffer pool size	Total pages in the pool (× 16 KB)
Free buffers	Free pages available
Database pages	Pages containing data
Modified db pages	Dirty pages (modified, not yet written to disk)
Buffer pool hit rate	1000/1000 = 100%, perfect. Below 950/1000 (95%), increase the buffer pool

Swipe to see more

For automated monitoring:

SELECT
  (SELECT VARIABLE_VALUE FROM performance_schema.global_status
   WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests') AS logical_reads,
  (SELECT VARIABLE_VALUE FROM performance_schema.global_status
   WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads') AS disk_reads,
  (SELECT VARIABLE_VALUE FROM performance_schema.global_status
   WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_dirty') AS dirty_pages,
  (SELECT VARIABLE_VALUE FROM performance_schema.global_status
   WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_total') AS total_pages;

Redo log: checkpoint and write

The redo log (InnoDB's transaction journal) guarantees the durability of data. If the redo log fills up faster than checkpoints free it, MySQL slows down:

SHOW ENGINE INNODB STATUS\G

LOG section:

Log capacity                 104857600
Log capacity used            104857600
Log sequence number          309288386
Log buffer assigned up to    309288386
Log buffer completed up to   309288386
Log written up to            309288386
Log flushed up to            309288386
Added dirty pages up to      309288386
Pages flushed up to          309288386
Last checkpoint at           309288386

If Log sequence number is far ahead of Last checkpoint at, the redo log is under pressure, writes could be slowed down. Here, at rest, the two values are identical. Right after loading logs, they were not: LSN 38127893 against checkpoint 29516832, about 8.6 MB ahead. Do not rely on Log capacity used alone: it was 104857600 (the entire capacity) in both readings although the server was not under pressure.

-- Check the size of the redo log
SHOW VARIABLES LIKE 'innodb_redo_log_capacity';

+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| innodb_redo_log_capacity | 104857600 |
+--------------------------+-----------+

100 MB by default in MySQL 8.4. For a database with many writes, increase it to 256 MB or 512 MB:

SET PERSIST innodb_redo_log_capacity = 268435456; -- 256 MB

Transactions: history and purge lag

InnoDB maintains a transaction history for MVCC (consistent reads without locking). If the undo log grows, the purge is late:

SHOW ENGINE INNODB STATUS\G

TRANSACTIONS section:

Trx id counter 2025
Purge done for trx's n:o < 2025 undo n:o < 0 state: running but idle
History list length 8

Metric	Meaning	Alert threshold
History list length	Transactions waiting to be purged	> 10,000

Swipe to see more

A rising History list length indicates that the purge thread cannot keep up with the pace of writes. Possible causes: very long SELECT queries that hold an old MVCC view, or insufficient innodb_purge_threads.

Adaptive hash index: useful or not?

The adaptive hash index (AHI) is an automatic InnoDB mechanism that builds an in-memory hash index to speed up frequent lookups:

SHOW VARIABLES LIKE 'innodb_adaptive_hash_index';

+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_adaptive_hash_index | OFF   |
+----------------------------+-------+

In MySQL 8.4, the AHI is disabled by default (it was enabled in 8.0). If you enabled it manually and want to check whether it is useful, consult SHOW ENGINE INNODB STATUS, section INSERT BUFFER AND ADAPTIVE HASH INDEX:

• High hash searches/s and low non-hash searches/s: the AHI is effective, keep it
• Low or 0 hash searches/s: the AHI uses memory for nothing, disable it

-- Enable to test (dynamic, no restart)
SET PERSIST innodb_adaptive_hash_index = ON;

-- Disable if monitoring shows no benefit
SET PERSIST innodb_adaptive_hash_index = OFF;

Table maintenance

Observing is not enough: some degradations are fixed by explicit maintenance operations, which MySQL does not trigger on its own. Three commands cover the common needs, and they have neither the same cost nor the same effect. ANALYZE TABLE refreshes statistics and stays light, OPTIMIZE TABLE rebuilds the table and needs disk space, CHECK TABLE merely checks without ever repairing. Back up before any rebuild operation.

ANALYZE TABLE: update statistics

MySQL's query optimizer uses statistics on the distribution of data to choose the best plan. If these statistics become stale (after a large load or mass deletions), the optimizer makes bad choices.

ANALYZE TABLE lab_mysql.logs;

+----------------+---------+----------+----------+
| Table          | Op      | Msg_type | Msg_text |
+----------------+---------+----------+----------+
| lab_mysql.logs | analyze | status   | OK       |
+----------------+---------+----------+----------+

ANALYZE TABLE is generally fast on InnoDB, but it would take a read lock during the analysis (documentation fact, NOT TESTED here: this lock was not observed). Preferably schedule it off-peak if the table is heavily written to. Run it after any mass load.

Persistent statistics by default

innodb_stats_persistent is 1 by default (measured on 8.4.11): statistics are stored on disk and survive a restart.

Histograms (MySQL 8.0+)

Histograms give the optimizer a detailed view of the distribution of values in a column, not just the cardinality. Particularly useful for columns with a non-uniform distribution:

ANALYZE TABLE lab_mysql.logs UPDATE HISTOGRAM ON action, client_id;

+----------------+-----------+----------+------------------------------------------------------+
| Table          | Op        | Msg_type | Msg_text                                             |
+----------------+-----------+----------+------------------------------------------------------+
| lab_mysql.logs | histogram | status   | Histogram statistics created for column 'action'.    |
| lab_mysql.logs | histogram | status   | Histogram statistics created for column 'client_id'. |
+----------------+-----------+----------+------------------------------------------------------+

Effect measured on the query of the EXPLAIN section (without an index): the estimate goes from 903 rows to 5,017 for 5,000 real ones. This is the correction of the bad estimate that ANALYZE TABLE alone does not fix.

Check the histograms created:

SELECT SCHEMA_NAME, TABLE_NAME, COLUMN_NAME,
       JSON_EXTRACT(HISTOGRAM, '$.\"histogram-type\"') AS type,
       JSON_EXTRACT(HISTOGRAM, '$.\"number-of-buckets-specified\"') AS buckets
FROM information_schema.COLUMN_STATISTICS
WHERE SCHEMA_NAME = 'lab_mysql';

+-------------+------------+-------------+-------------+---------+
| SCHEMA_NAME | TABLE_NAME | COLUMN_NAME | type        | buckets |
+-------------+------------+-------------+-------------+---------+
| lab_mysql   | logs       | action      | "singleton" | 100     |
| lab_mysql   | logs       | client_id   | "singleton" | 100     |
+-------------+------------+-------------+-------------+---------+

The "singleton" type means one bucket per distinct value; the maximum is 100 buckets (default value).

Automatic histogram update

The AUTO UPDATE clause makes the histogram update automatic (it works on 8.4.11, measured; the version that introduced it was not verified). When InnoDB's persistent statistics are recalculated, histograms marked AUTO UPDATE are refreshed automatically. Create them with:

ANALYZE TABLE lab_mysql.logs UPDATE HISTOGRAM ON action AUTO UPDATE;

The auto-update field of the histogram's JSON confirms it: true for action, false for client_id (created without AUTO UPDATE). Read it with JSON_EXTRACT(HISTOGRAM, '$."auto-update"') on information_schema.COLUMN_STATISTICS.

OPTIMIZE TABLE: reclaim space

After many DELETE or UPDATE operations, InnoDB tables keep free space in their pages. OPTIMIZE TABLE rebuilds the table and the indexes to reclaim the space:

OPTIMIZE TABLE lab_mysql.logs;

+-----------------+----------+----------+-------------------------------------------------------------------+
| Table           | Op       | Msg_type | Msg_text                                                          |
+-----------------+----------+----------+-------------------------------------------------------------------+
| lab_mysql.logs  | optimize | note     | Table does not support optimize, doing recreate + analyze instead  |
| lab_mysql.logs  | optimize | status   | OK                                                                |
+-----------------+----------+----------+-------------------------------------------------------------------+

OPTIMIZE TABLE for InnoDB

The "Table does not support optimize" message is normal for InnoDB. MySQL internally runs an ALTER TABLE ... FORCE followed by an ANALYZE TABLE, which amounts to a complete rebuild. The operation creates a temporary copy of the table, so:

• Measured: an INSERT run during an OPTIMIZE TABLE on a table of 500,000 rows went through without waiting for the end of the rebuild
• On this table, data_length went from 96.6 MB to 55.6 MB after deleting half of the rows and then running OPTIMIZE TABLE
• Measurement trap no. 1: information_schema.tables is cached (information_schema_stats_expiry is 86400 s, that is 24 h). Before measuring a size, run SET SESSION information_schema_stats_expiry = 0;, otherwise you will read stale values (we first read data_length as 0 MB)
• Measurement trap no. 2: data_free is a poor indicator of fragmentation. It was 7 MB before as well as after a DELETE of half the table, while the space actually recoverable was several tens of MB: compare data_length before and after rather than relying on data_free
• Plan for free disk space: about the size of the table
• The operation takes a metadata lock but allows DML reads/writes during the rebuild (Online DDL)
• On a table of several GB, the operation can take a long time

CHECK TABLE and mysqlcheck

CHECK TABLE checks the integrity of a table:

CHECK TABLE on InnoDB

On a large InnoDB table, CHECK TABLE can block other threads. In case of corruption, it can also cause a server shutdown to prevent the error from spreading. To fully check an .ibd file offline, use innochecksum instead.

CHECK TABLE lab_mysql.clients, lab_mysql.commandes, lab_mysql.logs;

+---------------------+-------+----------+----------+
| Table               | Op    | Msg_type | Msg_text |
+---------------------+-------+----------+----------+
| lab_mysql.clients   | check | status   | OK       |
| lab_mysql.commandes | check | status   | OK       |
| lab_mysql.logs      | check | status   | OK       |
+---------------------+-------+----------+----------+

The risk of server shutdown and the role of innochecksum cited in the box above are documentation facts, NOT TESTED here. Likewise, mysqlcheck (NOT TESTED: absent from the image) lets you check, optimize and analyze in bulk:

# Check all the tables of a database
mysqlcheck -u root -p lab_mysql

# Optimize all the tables
mysqlcheck -u root -p --optimize lab_mysql

# Analyze all the tables of all databases
mysqlcheck -u root -p --all-databases --analyze

Integrate MySQL into monitoring

Everything above is run by hand, when the problem is already there. Continuous monitoring aims at the opposite: detecting the degradation before users call. The principle is simple: an exporter periodically queries the same Performance Schema views, Prometheus stores the history and Grafana displays the trends. What remains is choosing the metrics and thresholds, which are the subject of the next three sections.

Prometheus metrics (mysqld_exporter)

For continuous monitoring, install mysqld_exporter (Prometheus):

# Create a dedicated user for the exporter
mysql -u root -p -e "
  CREATE USER 'exporter'@'localhost'
    IDENTIFIED BY 'exporter_password'
    WITH MAX_USER_CONNECTIONS 3;
  GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter'@'localhost';
"

The .my.cnf configuration file for the exporter:

[client]
user=exporter
password=exporter_password

Measured: the CREATE USER and GRANT statements above go through as is on 8.4.11. NOT TESTED: mysqld_exporter itself and the metric names in the following table were neither installed nor queried; they are given according to the exporter's documentation.

Essential metrics exposed by mysqld_exporter:

Prometheus metric	MySQL source	Alert threshold
mysql_global_status_threads_connected	Threads_connected	> 80% of max_connections
mysql_global_status_slow_queries	Slow_queries	Sudden increase
mysql_global_status_innodb_buffer_pool_reads	Innodb_buffer_pool_reads	Hit ratio < 95%
mysql_global_status_innodb_row_lock_waits	Innodb_row_lock_waits	> 0 sustained
mysql_slave_status_seconds_behind_master	Seconds_Behind_Source	> 60 seconds

Swipe to see more

Grafana dashboards

A community Grafana dashboard for MySQL: MySQL Overview (ID 7362 on grafana.com). It displays the buffer pool hit ratio, connections, QPS, replication lag and InnoDB metrics.

Critical alerts to configure

An alert is only worth having if someone can act on it, hence the criticality column. The entries marked Critical justify a night-time wake-up: connection saturation, broken replication or a full disk all lead to unavailability. Warning entries are handled during business hours and read as drifts, not as incidents.

Alert	Condition	Criticality
Connections close to the limit	Threads_connected > 80% max_connections	Critical
Low buffer pool hit ratio	< 95% for 5 min	Warning
Replication lag	Seconds_Behind_Source > 60 s	Critical
Broken replication	Replica_IO_Running = No or Replica_SQL_Running = No	Critical
Slow queries on the rise	+50% compared with the baseline	Warning
Disk space	< 20% free on the datadir	Critical
Frequent deadlocks	> 10 per hour	Warning

Swipe to see more

Troubleshooting

This table lists eight typical situations that lead you to open this guide. Each row follows the approach from the top of the page: the symptom is what you observe, the likely cause is what you must confirm with the observation commands, and the fix only applies after that confirmation.

Symptom	Likely cause	Fix
Query blocked by a lock	Long uncommitted transaction	Identify with SELECT * FROM sys.innodb_lock_waits\G, close the blocking session with KILL <blocking_pid> (KILL QUERY does not release the lock)
Buffer pool hit ratio < 95%	innodb_buffer_pool_size too small	Increase it, see the configuration guide
Slow queries suddenly increasing	Index dropped or stale statistics	ANALYZE TABLE, check the indexes with sys.schema_redundant_indexes
"Too many connections"	Pool not closed or wait_timeout too high	Reduce wait_timeout, use a connection pooler (ProxySQL)
Table growing after DELETE/UPDATE	Space not reclaimed at file level	OPTIMIZE TABLE (releases the space)
EXPLAIN shows ALL on a large table	Missing index on the filtered columns	Add a composite index on the WHERE columns
Rising History list length	Purge thread behind, very long SELECTs	Identify the long transactions, increase innodb_purge_threads
Frequent deadlocks	Inconsistent order of access to tables/rows	Always access in the same order, short transactions, innodb_print_all_deadlocks = ON

Swipe to see more

Put it into practice

The lab provides an appli database whose evenements table received 200,000 log rows, then a purge of nine rows out of ten. 20,000 remain, yet the table still occupies its size from before the purge, and a dashboard query reads it in full although an index already exists: that index does not serve this query, which SHOW INDEX does not tell you. The lab runs on a single VM.

The validation looks at no typed command, it queries the state of the server. The plan announced by EXPLAIN FORMAT=JSON must no longer scan the whole table (access_type other than ALL) and must examine fewer than half of the rows; the index it uses must have as its first column the one the query filters on, because an index that exists without being used does not count. The size of the table, measured by data_length + index_length after an ANALYZE TABLE, must drop below 20 MB with the 20,000 rows intact: emptying or recreating the table would give a fast plan and a small size for the wrong reason. The two tasks are independent: creating an index reclaims no byte, and rebuilding the table indexes nothing.

It also requires that the dashboard query be findable in sys.statement_analysis under the name of its database, which is easy to forget. Measured on MySQL 8.4, a query run without a default database is recorded there with a NULL database and escapes any filter on db: run it with mysql -D appli or after USE appli. A short report to fill in, /home/student/rapport-observation.txt, is finally checked against the server: access type of the plan, megabytes of the table and number of remaining rows.

Guided lab Practice

Learn how to do it

Make the plan stop reading the table whole, and reclaim what a purge did not free

$ dsoxlab run mysql-06-observer

Environment: VM·Duration: ~40 min

How it is checked: the state of the system is checked after a reboot, not the command you typed.

First time? What DSOXLab is and how to install it

DSOXLab is the command line that prepares a disposable environment (an isolated shell or a VM), runs the lab scenario, then checks the real state of the system and computes a score. Nothing is installed or broken on your workstation.

It needs Python 3.11 or later, uv or pipx, and git. The installation guide details these prerequisites; on Windows, or on a Mac without Python, use the appliance instead.

This lab starts virtual machines: it also needs ansible-playbook, a hypervisor (KVM or Incus), Terraform and hardware virtualization (/dev/kvm), which inside a VirtualBox VM means nested virtualization. The first time, dsoxlab start mysql-06-observer checks all of it before creating the machines. Prepare your machine for vm labs.

Install the CLI and add the catalogue, once:

uv tool install dsoxlab   # or: pipx install dsoxlab
dsoxlab catalog add https://github.com/stephrobert/bdd-dsoxlab-training

Before you run the lab, check:

• DSOXLab installed
• catalogue available
• compatible environment (isolated shell or VM)

To check, nothing is probed from this page: run dsoxlab doctor. Read the diagnosis

Run this lab, then validate your work:

dsoxlab run mysql-06-observer
dsoxlab check mysql-06-observer

See every lab: dsoxlab list-labs. Track your progress: dsoxlab progress. The full dsoxlab guide covers scoring and the pitfalls.

Key takeaways

• SHOW PROCESSLIST (or sys.processlist) is the first reflex: who is connected, what are they doing, for how long.
• sys.innodb_lock_waits points to the session that blocks a row. KILL QUERY does not release locks: only KILL <id> closes the connection and cancels the transaction.
• sys.statement_analysis identifies expensive queries: total_latency, full_scan, rows_examined vs rows_sent, MySQL's equivalent of pg_stat_statements.
• EXPLAIN ANALYZE compares the estimated plan with the time actually measured, and therefore shows why a query is slow: look at type = ALL (full scan) and rows examined >> rows sent.
• The slow query log captures slow queries on disk, enable log_queries_not_using_indexes to detect full scans before they become a problem, knowing that it also logs queries of less than a thousandth of a second.
• The buffer pool hit ratio (>99% in OLTP) is a central InnoDB metric, if it drops, increase innodb_buffer_pool_size.
• ANALYZE TABLE updates the optimizer's statistics, run it after any mass load. Histograms improve the estimate for non-uniform distributions.
• OPTIMIZE TABLE rebuilds the InnoDB table and reclaims space, needs temporary disk space, prefer off-peak hours.
• CHECK TABLE checks integrity without repairing, first diagnosis when corruption is suspected.
• In production, install mysqld_exporter + Grafana for continuous monitoring: buffer pool, connections, replication lag, slow queries.
• One alert never to miss: broken replication (Replica_IO_Running = No), every minute without replication is a minute of unprotected data.

×

See in the glossary →