Monitoring and Performance Basics

Chapter 7 — Monitoring and Performance Basics

A slow website often traces back to a slow database query. A crashed application often traces back to MySQL running out of connections. These problems are invisible until they become incidents — unless you have monitoring in place. MySQL ships with extensive built-in observability tools. This chapter covers the queries you'll run regularly to understand what MySQL is doing, where time is being spent, and where to look when things go wrong.

What this chapter covers: SHOW STATUS — the key variables and calculated health metrics (buffer pool hit ratio, temp table ratio, slow query rate). SHOW PROCESSLIST in depth — states explained, identifying blocked queries, KILL. Slow query log analysis and acting on findings. performance_schema — finding your top queries by digest, wait events, file I/O, memory. The sys schema — the human-readable layer over performance_schema. SHOW ENGINE INNODB STATUS for deep-dive diagnostics. Database and table size queries. mysqladmin for shell monitoring. A practical daily monitoring checklist.

SHOW STATUS — The Server Health Dashboard

MySQL exposes hundreds of internal counters via SHOW STATUS. The counters are cumulative since the last server restart — they tell you the total work done, not a rate. To get a rate, divide by Uptime, or compare two snapshots taken seconds apart.

# Global status (since last restart) — most useful for baseline checks
mysql> SHOW GLOBAL STATUS LIKE 'pattern';

# Session status (since this connection opened)
mysql> SHOW SESSION STATUS LIKE 'pattern';

# Quick server summary
mysql> SHOW GLOBAL STATUS WHERE variable_name IN (
    ->   'Uptime', 'Questions', 'Threads_connected',
    ->   'Threads_running', 'Max_used_connections', 'Slow_queries'
    -> );
+----------------------+---------+
| Variable_name        | Value   |
+----------------------+---------+
| Max_used_connections | 12      |
| Questions            | 4193820 |
| Slow_queries         | 47      |
| Threads_connected    | 4       |
| Threads_running      | 1       |
| Uptime               | 604800  |
+----------------------+---------+
# Uptime 604800 = 7 days. 47 slow queries in 7 days — low enough.
# Threads_running=1 = only one query executing right now (the SHOW STATUS itself)

The variables that matter most

Variable	What it tells you	Concern if…
Uptime	Seconds since last restart. Divide other counters by this to get per-second rates.	Unexpected recent restart
Threads_connected	Current open connections.	> 80% of max_connections
Threads_running	Connections actively executing a query (not sleeping). Normal is 1–5.	> 20 sustained
Max_used_connections	Peak simultaneous connections since restart. Use to right-size max_connections.	Close to max_connections
Slow_queries	Total queries exceeding long_query_time.	Growing rapidly
Questions	Total statements executed (includes stored procedure statements).	—
Com_select / Com_insert / Com_update / Com_delete	Count of each DML type — shows your read/write ratio.	—
Aborted_connects	Connections that failed to authenticate. Spikes indicate attacks or misconfigured apps.	Rapid increase
Aborted_clients	Connections closed without a clean disconnect (app crashed, network timeout).	High sustained count
Handler_read_rnd_next	Number of full row-by-row table scans. High = many queries missing indexes.	Very high vs Questions
Created_tmp_disk_tables	Temp tables that spilled to disk (too big for RAM). Expensive operation.	> 25% of Created_tmp_tables
Table_locks_waited	Times a query had to wait for a table lock. Should be near zero for InnoDB.	Any significant value
Innodb_buffer_pool_reads	Pages read from disk (not in buffer pool). Compare to read_requests for hit ratio.	Hit ratio below 95%

Calculated health metrics

# ── Buffer pool hit ratio (should be > 99%) ───────────────────────
mysql> SELECT
    ->   FORMAT(
    ->     (1 - (Innodb_reads / Innodb_read_requests)) * 100, 2
    ->   ) AS buffer_pool_hit_ratio_pct
    -> FROM (
    ->   SELECT
    ->     VARIABLE_VALUE AS Innodb_reads
    ->   FROM performance_schema.global_status
    ->   WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads'
    -> ) r,
    -> (
    ->   SELECT
    ->     VARIABLE_VALUE AS Innodb_read_requests
    ->   FROM performance_schema.global_status
    ->   WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
    -> ) rr;
+---------------------------+
| buffer_pool_hit_ratio_pct |
+---------------------------+
| 99.97                     |
+---------------------------+
# 99.97% — excellent. If < 95%, innodb_buffer_pool_size needs increasing.

# ── Temp table disk spill ratio (should be < 25%) ────────────────
mysql> SHOW GLOBAL STATUS WHERE variable_name
    ->   IN ('Created_tmp_tables', 'Created_tmp_disk_tables');
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 8     |
| Created_tmp_tables      | 1042  |
+-------------------------+-------+
# 8/1042 = 0.8% spill to disk — fine. If > 25%, increase tmp_table_size.

# ── Connection headroom ───────────────────────────────────────────
mysql> SELECT
    ->   @@max_connections AS max_connections,
    ->   Max_used_connections,
    ->   ROUND(Max_used_connections / @@max_connections * 100) AS pct_used
    -> FROM (
    ->   SELECT VARIABLE_VALUE AS Max_used_connections
    ->   FROM performance_schema.global_status
    ->   WHERE VARIABLE_NAME = 'Max_used_connections'
    -> ) m;
+-----------------+---------------------+----------+
| max_connections | Max_used_connections| pct_used |
+-----------------+---------------------+----------+
| 75              | 12                  | 16       |
+-----------------+---------------------+----------+
# 16% peak usage — plenty of headroom. Alert at > 80%.

Buffer pool hit ratio

Target: > 99%

1 - (reads / read_requests). Below 95% means the buffer pool is too small and MySQL is reading from disk constantly.

Temp table disk spill

Target: < 25%

Created_tmp_disk_tables / Created_tmp_tables. High ratio means complex queries need more RAM for temp tables — increase tmp_table_size.

Connection usage

Alert at: > 80%

Max_used_connections / max_connections. At 80%, start planning to increase max_connections before the server begins refusing new connections.

Slow query rate

Target: < 0.1%

Slow_queries / Questions. For most sites a handful of slow queries per hour is acceptable; a rising rate suggests a new code deployment added a bad query.

SHOW PROCESSLIST — Seeing What MySQL Is Doing Right Now

# Standard view (truncates queries at 100 chars)
mysql> SHOW PROCESSLIST;
+----+--------------+-----------+----------+---------+------+------------------------+------------------+
| Id | User         | Host      | db       | Command | Time | State                  | Info             |
+----+--------------+-----------+----------+---------+------+------------------------+------------------+
|  1 | event_sched  | localhost | NULL     | Daemon  | 3600 | Waiting on empty queue | NULL             |
|  8 | root         | localhost | NULL     | Query   |    0 | starting               | SHOW PROCESSLIST |
| 11 | bookshop_app | localhost | bookshop | Sleep   |   42 |                        | NULL             |
| 14 | bookshop_app | localhost | bookshop | Query   |  18  | Sending data           | SELECT * FROM ... |
+----+--------------+-----------+----------+---------+------+------------------------+------------------+

# FULL version — shows complete query text (no truncation)
mysql> SHOW FULL PROCESSLIST;

# Kill a specific connection (use the Id from SHOW PROCESSLIST)
mysql> KILL 14;
Query OK, 0 rows affected (0.00 sec)

# Kill only the current query (not the connection) — KILL QUERY
mysql> KILL QUERY 14;

Understanding the State column

Sleep

Idle connection waiting for the next query. Normal. If many connections sit in Sleep for a long time, reduce wait_timeout.

starting

Just started executing a query — initializing. Seen briefly on every query, not a concern.

Sending data

Reading rows and sending results to client. Large result sets or slow full scans show up here. The most common active state.

Sorting result

ORDER BY is being applied. If sustained, the query may be sorting a large result set — check whether an index covers the sort column.

Creating sort index

Building a temporary index for sorting. Common with filesort — indicates the query can't use an existing index for the ORDER BY.

Waiting for lock

Blocked waiting for a row lock or table lock held by another transaction. Multiple queries in this state indicate a lock contention problem.

Locked

Waiting for a table lock (MyISAM). Should not appear with InnoDB tables — if it does, check storage engine with SHOW CREATE TABLE.

Copying to tmp table

Building a temporary table — a GROUP BY, DISTINCT, or complex JOIN that MySQL can't resolve with indexes. Look for missing indexes.

Finding blocked queries — the lock detection query

# Show all queries waiting for a lock, and what's blocking them
mysql> SELECT
    ->   r.trx_id AS waiting_trx,
    ->   r.trx_mysql_thread_id AS waiting_thread,
    ->   r.trx_query AS waiting_query,
    ->   b.trx_id AS blocking_trx,
    ->   b.trx_mysql_thread_id AS blocking_thread,
    ->   b.trx_query AS blocking_query
    -> FROM information_schema.innodb_lock_waits w
    -> JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
    -> JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id\G
*************************** 1. row ***************************
  waiting_trx:     421938
  waiting_thread:  14
  waiting_query:   UPDATE books SET price = 9.99 WHERE id = 42
  blocking_trx:    421930
  blocking_thread: 11
  blocking_query:  NULL (transaction is idle / doing something else)
# Thread 11 holds a lock on a row that thread 14 needs.
# Thread 11 is idle (NULL query) — it may have forgotten to COMMIT.
# Fix: KILL 11; or ask the app to commit its transaction.

Slow Query Log — Analysis and Action

The slow query log (enabled in Chapter 4) captures queries that exceeded long_query_time. Reading individual entries is useful for one-off investigation, but the real power comes from aggregating the log to find the worst offenders across thousands of queries.

# Summarise the slow log — top 10 by total time spent
$ sudo mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
Count: 1823  Time=4.21s (7675s)  Lock=0.00s  Rows=1.0 (1823)
  SELECT * FROM books WHERE title LIKE '%S%'

Count: 340   Time=2.84s (965s)   Lock=0.00s  Rows=87.2 (29641)
  SELECT o.*, b.title FROM orders o JOIN books b ON o.book_id = b.id WHERE o.customer_id = N

Count: 12    Time=11.7s (140s)   Lock=0.00s  Rows=128430.0 (1541160)
  SELECT * FROM orders WHERE created_at BETWEEN 'S' AND 'S'

# First result: ran 1823 times, total 7675 seconds — this is the #1 priority to fix
# The leading % in LIKE means no index can be used — consider FULLTEXT index
# Third result: 128,430 rows examined each time — needs index on created_at

# mysqldumpslow sort options:
#   -s t  = sort by total time (usually most useful)
#   -s at = sort by average time per query
#   -s c  = sort by count (most frequent)
#   -s r  = sort by rows examined
#   -t N  = top N results

The action after finding a slow query: take the query to Chapter 7 of the Advanced SQL course — EXPLAIN and EXPLAIN ANALYZE will tell you exactly why it's slow. The fix is almost always one of: add an index on the filtered/sorted columns, rewrite the query to avoid a leading wildcard, or break up a massive result set with pagination (LIMIT/OFFSET).

performance_schema — The Query-Level Microscope

The slow query log shows individual bad queries. performance_schema goes further — it aggregates statistics across all queries, normalised by pattern (a "digest"), so you can find the query type that's costing the most time even if each individual execution is fast.

Top queries by total execution time

# The most useful single query in performance_schema
# Find top 10 queries by total time spent (across all executions)
mysql> SELECT
    ->   SCHEMA_NAME AS db,
    ->   DIGEST_TEXT AS query_pattern,
    ->   COUNT_STAR AS executions,
    ->   ROUND(SUM_TIMER_WAIT / 1e12, 2) AS total_sec,
    ->   ROUND(AVG_TIMER_WAIT / 1e12, 4) AS avg_sec,
    ->   ROUND(MAX_TIMER_WAIT / 1e12, 2) AS max_sec,
    ->   SUM_ROWS_EXAMINED AS rows_examined,
    ->   SUM_ROWS_SENT AS rows_sent
    -> FROM performance_schema.events_statements_summary_by_digest
    -> WHERE SCHEMA_NAME IS NOT NULL
    -> ORDER BY SUM_TIMER_WAIT DESC
    -> LIMIT 10\G
*************************** 1. row ***************************
         db: bookshop
query_pattern: SELECT * FROM `books` WHERE `title` LIKE ?
  executions: 1823
   total_sec: 7674.82
     avg_sec: 4.2100
     max_sec: 9.34
rows_examined: 234563590
   rows_sent: 1823
# rows_examined / rows_sent = 128,680 : 1 — classic full table scan
# This one query has burned 7,674 seconds of server time

What MySQL is waiting on

# Top 10 wait events — what is MySQL spending time waiting for?
mysql> SELECT
    ->   EVENT_NAME,
    ->   COUNT_STAR AS count,
    ->   ROUND(SUM_TIMER_WAIT / 1e12, 2) AS total_wait_sec
    -> FROM performance_schema.events_waits_summary_global_by_event_name
    -> WHERE COUNT_STAR > 0
    -> ORDER BY SUM_TIMER_WAIT DESC
    -> LIMIT 10;
+--------------------------------------+----------+----------------+
| EVENT_NAME                           | count    | total_wait_sec |
+--------------------------------------+----------+----------------+
| wait/io/file/innodb/innodb_data_file | 24891034 | 1842.34        |
| wait/io/file/sql/FRM                 | 1200443  | 12.44          |
| wait/synch/mutex/innodb/...          | 4923122  | 3.21           |
+--------------------------------------+----------+----------------+
# innodb_data_file at the top means most waits are disk I/O
# → buffer pool is too small, frequently reading from disk
# Solution: increase innodb_buffer_pool_size (Chapter 4)

Memory usage breakdown

# How much memory is MySQL allocating, broken down by component
mysql> SELECT
    ->   EVENT_NAME,
    ->   CURRENT_NUMBER_OF_BYTES_USED / 1024 / 1024 AS mb_used
    -> FROM performance_schema.memory_summary_global_by_event_name
    -> WHERE CURRENT_NUMBER_OF_BYTES_USED > 0
    -> ORDER BY CURRENT_NUMBER_OF_BYTES_USED DESC
    -> LIMIT 10;
+---------------------------------------+---------+
| EVENT_NAME                            | mb_used |
+---------------------------------------+---------+
| memory/innodb/buf_buf_pool            | 1024.00 |
| memory/performance_schema/...         | 87.32   |
| memory/sql/Filesort_buffer::buffer    | 14.11   |
+---------------------------------------+---------+
# buf_buf_pool = InnoDB buffer pool (expected — should be your biggest user)
# Large Filesort_buffer = many in-progress sort operations

# Reset all performance_schema statistics (counters restart from zero)
mysql> TRUNCATE TABLE performance_schema.events_statements_summary_by_digest;

The sys Schema — Human-Readable Diagnostics

The sys schema wraps performance_schema in views with human-readable formatting — times as "2.3s" instead of picoseconds, sizes as "1.2 MiB" instead of raw bytes. It's the first place to look for diagnostic information.

sys.statement_analysis

Top queries by total latency, with count, average latency, rows examined, full scans, and tmp tables. The most useful diagnostic view in MySQL.

sys.processlist

Active connections with human-readable durations, current statement, and wait info. Filters out Sleep connections by default.

sys.schema_table_statistics

I/O statistics per table — reads vs writes, total wait times. Shows which tables are the busiest.

sys.schema_unused_indexes

Indexes that have never been used since the server started. Candidates for removal — unused indexes waste write performance and storage.

sys.schema_redundant_indexes

Indexes that duplicate or overlap another index on the same table. MySQL uses only one — the duplicate is pure overhead.

sys.io_global_by_file_by_bytes

Which files MySQL reads and writes most, with total bytes. Shows if a specific table's data file is causing I/O pressure.

sys.memory_by_user_by_current_bytes

RAM allocated per MySQL user. Useful for finding which application is using the most memory.

# Top 5 queries by total time — clean, human-readable output
mysql> SELECT query, exec_count, total_latency, rows_examined_avg, full_scans
    -> FROM sys.statement_analysis
    -> ORDER BY total_latency DESC
    -> LIMIT 5\G
*************************** 1. row ***************************
            query: SELECT * FROM `books` WHERE `title` LIKE ?
       exec_count: 1823
    total_latency: 2.12 h
 rows_examined_avg: 128430
       full_scans: 1823

# Indexes that have never been used since last restart
mysql> SELECT table_schema, table_name, index_name
    -> FROM sys.schema_unused_indexes
    -> WHERE table_schema NOT IN ('performance_schema', 'sys')
    -> ORDER BY table_schema, table_name;
+--------------+------------+-----------------+
| table_schema | table_name | index_name      |
+--------------+------------+-----------------+
| bookshop     | books      | idx_isbn        |
| bookshop     | orders     | idx_shipped_at  |
+--------------+------------+-----------------+
# These indexes are never queried — they slow every INSERT/UPDATE on those tables
# Consider dropping them: ALTER TABLE books DROP INDEX idx_isbn;

# Redundant indexes (duplicates)
mysql> SELECT table_schema, table_name, redundant_index_name, dominant_index_name
    -> FROM sys.schema_redundant_indexes
    -> WHERE table_schema NOT IN ('sys', 'mysql')\G

# Active connections right now (excluding Sleep)
mysql> SELECT user, db, command, time, state, current_statement
    -> FROM sys.processlist
    -> WHERE command != 'Sleep'
    -> ORDER BY time DESC;

SHOW ENGINE INNODB STATUS — The Deep Diagnostic

When something's wrong and other tools haven't pinpointed it, SHOW ENGINE INNODB STATUS dumps a detailed internal snapshot of InnoDB's state — active transactions, lock waits, buffer pool internals, I/O threads, and the all-important deadlock history.

mysql> SHOW ENGINE INNODB STATUS\G
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
2026-06-15 14:30:01 0x7f3abc INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 38 seconds

----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 142
Mutex spin waits 0, rounds 0, OS waits 0
# Semaphores: internal locking overhead. High numbers = CPU contention.

------------
TRANSACTIONS
------------
Trx id counter 421942
Purge done for trx's n:o < 421930 undo n:o < 0
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421938, ACTIVE 18 sec starting index read
2 lock struct(s), heap size 1136, 1 row lock(s)
LOCK WAIT 1 lock struct(s)
MySQL thread id 14, OS thread handle ..., query id 9834 localhost bookshop_app
UPDATE books SET price = 9.99 WHERE id = 42
# A transaction has been waiting 18 seconds for a row lock — indicates contention

----------------------
BUFFER POOL AND MEMORY
----------------------
Buffer pool size   65536        ← pages (65536 × 16 KB = 1 GB)
Free buffers       12481
Database pages     52847        ← pages currently holding data
Modified db pages  234          ← dirty pages (unflushed to disk)
Pages read ahead   0.00/s, evicted without access 0.00/s
Buffer pool hit rate 9997 / 10000  ← 99.97% hit rate

--------------
ROW OPERATIONS
--------------
Number of rows inserted 28442, updated 9123, deleted 1204, read 14523891

The LATEST DETECTED DEADLOCK section in SHOW ENGINE INNODB STATUS shows the most recent deadlock — the two transactions, the rows they were fighting over, and which one was rolled back. If you're seeing unexpected rollbacks in your application, this is where to look.

Database and Table Sizes

# Size of each database in MB
mysql> SELECT
    ->   table_schema AS database_name,
    ->   ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) AS size_mb
    -> FROM information_schema.tables
    -> GROUP BY table_schema
    -> ORDER BY size_mb DESC;
+--------------------+---------+
| database_name      | size_mb |
+--------------------+---------+
| bookshop           |   284.5 |
| mysql              |     2.4 |
| performance_schema |     0.0 |
| sys                |     0.0 |
+--------------------+---------+

# Largest tables in a specific database
mysql> SELECT
    ->   table_name,
    ->   ROUND(data_length / 1024 / 1024, 1) AS data_mb,
    ->   ROUND(index_length / 1024 / 1024, 1) AS index_mb,
    ->   ROUND((data_length + index_length) / 1024 / 1024, 1) AS total_mb,
    ->   table_rows AS approx_rows
    -> FROM information_schema.tables
    -> WHERE table_schema = 'bookshop'
    -> ORDER BY (data_length + index_length) DESC;
+------------+---------+----------+----------+-------------+
| table_name | data_mb | index_mb | total_mb | approx_rows |
+------------+---------+----------+----------+-------------+
| orders     |   198.3 |     42.1 |    240.4 |     1284300 |
| books      |    32.4 |      9.2 |     41.6 |      128430 |
| customers  |     2.1 |      0.8 |      2.9 |       28442 |
+------------+---------+----------+----------+-------------+

# Actual disk usage of the MySQL data directory
$ sudo du -sh /var/lib/mysql/
312M    /var/lib/mysql/

# Per-database disk usage
$ sudo du -sh /var/lib/mysql/*/
284M    /var/lib/mysql/bookshop/
2.4M    /var/lib/mysql/mysql/

mysqladmin — Quick Shell Monitoring

# Quick one-line server summary (no SQL required)
$ sudo mysqladmin status
Uptime: 604800  Threads: 4  Questions: 4193820  Slow queries: 47  Opens: 1284  Flush tables: 3  Open tables: 1282  Queries per second avg: 6.938

# See all status variables (equivalent to SHOW GLOBAL STATUS)
$ sudo mysqladmin extended-status | grep -E "Threads|Slow|Question|Conn"
| Connections              | 8432                |
| Max_used_connections     | 12                  |
| Questions                | 4193820             |
| Slow_queries             | 47                  |
| Threads_connected        | 4                   |
| Threads_running          | 1                   |

# Poll status every 5 seconds — like vmstat for MySQL
$ sudo mysqladmin extended-status -i 5 -r | grep -E "Threads_running|Questions|Slow"
# -i 5 = interval 5 seconds  |  -r = show differences (deltas) from last poll

# Show processlist from the shell (no MySQL client needed)
$ sudo mysqladmin processlist

# Ping MySQL (exit code 0 = alive, non-zero = down)
$ sudo mysqladmin ping
mysqld is alive

Practical Monitoring Checklist

📋

Daily — check backup log: tail -5 /var/log/mysql_backup.log — confirm last night's backup completed successfully and the file size looks right.

📋

Daily — check slow query count: mysqladmin status | grep -i slow — if Slow_queries is rising faster than usual, something changed (new code, more traffic, missing index on a new table).

📋

Daily — check error log: sudo tail -30 /var/log/mysql/error.log — look for [ERROR] lines. InnoDB recovery messages, disk full warnings, or authentication failures all appear here first.

📅

Weekly — top slow queries: sudo mysqldumpslow -s t -t 5 /var/log/mysql/slow.log — identify whether the same queries keep appearing or if new ones have emerged. Investigate any query with total_time > 1 hour.

📅

Weekly — disk space: df -h /var/lib/mysql and sudo du -sh /var/lib/mysql/*/ — databases that grow without bound eventually fill the disk and crash MySQL. Alert at 75% disk usage.

📅

Weekly — connection headroom: SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='Max_used_connections'; compared to @@max_connections — alert at 80%.

🚨

Alert immediately — Aborted_connects rising: repeated authentication failures may indicate a brute-force attempt against MySQL accounts. Check SHOW GLOBAL STATUS LIKE 'Aborted_connects' compared to yesterday's value.

🚨

Alert immediately — Threads_running > 20: many simultaneously executing queries usually means a slow query is holding locks and others are piling up behind it. Run SHOW FULL PROCESSLIST to identify the blocker.

Troubleshooting

performance_schema tables return empty results

The performance_schema may be disabled. Check: SHOW VARIABLES LIKE 'performance_schema'; — should be ON. If OFF, enable it by adding performance_schema = ON to mysqld.cnf and restarting. Note: performance_schema uses memory (typically 80–100 MB) — on very small servers (under 512 MB RAM total) it may have been disabled to save RAM. Also check that the relevant instruments are enabled: SELECT NAME, ENABLED FROM performance_schema.setup_instruments WHERE NAME LIKE 'statement/%' LIMIT 5;

High CPU but SHOW PROCESSLIST shows mostly Sleep connections

If CPU is high but queries look idle, check for InnoDB background activity: SHOW ENGINE INNODB STATUS\G — look at the I/O section for pending reads/writes and the BUFFER POOL section for modified (dirty) pages being flushed. High dirty page count means InnoDB is aggressively flushing to disk. Also run SELECT * FROM sys.statement_analysis ORDER BY total_latency DESC LIMIT 5 — a fast but very frequent query can consume CPU without appearing in SHOW PROCESSLIST long enough to be seen.

Many threads in "Waiting for lock" state

A transaction is holding a row lock and not releasing it. Run the lock detection query from this chapter to identify the blocking thread ID, then SHOW FULL PROCESSLIST to see what it's doing (or if it's idle/stuck). If the blocking thread is an idle Sleep connection, it opened a transaction without committing. KILL <thread_id> forces a rollback and releases the locks. Long-term fix: ensure application code always commits or rolls back transactions, and reduce wait_timeout to evict stale connections.

sys schema views return "Table 'sys.xxx' doesn't exist"

The sys schema wasn't installed or was accidentally dropped. Reinstall it: locate the mysql_sys_schema.sql file (on Ubuntu: find /usr/share/mysql -name 'mysql_sys_schema.sql'; for MySQL 8.0 it may be in the shell package). Apply it: sudo mysql < /path/to/mysql_sys_schema.sql. Alternatively, on Ubuntu/Debian: sudo apt install --reinstall mysql-server (the sys schema is bundled with the server package).

Quick Reference — Chapter 7

Command / Query	Purpose
SHOW GLOBAL STATUS WHERE variable_name IN (...);	Check specific health counters — Threads_connected, Slow_queries, Uptime
SHOW FULL PROCESSLIST;	All active connections with full query text — first stop when the site is slow
KILL <Id>;	Terminate a stuck connection and roll back its open transaction
SELECT ... FROM performance_schema.events_statements_summary_by_digest ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;	Top queries by total time — finds the queries costing the most cumulatively
SELECT ... FROM sys.statement_analysis ORDER BY total_latency DESC LIMIT 10;	Same as above but with human-readable time values
SELECT ... FROM sys.schema_unused_indexes;	Indexes never used — candidates for removal to improve write performance
SELECT ... FROM sys.schema_redundant_indexes;	Duplicate indexes — pick the dominant one, drop the redundant
SHOW ENGINE INNODB STATUS\G	Deep InnoDB diagnostics — transactions, lock waits, deadlocks, buffer pool stats
sudo mysqladmin status	One-line server summary from the shell — no MySQL session needed
sudo mysqladmin extended-status -i 5 -r	Poll all status counters every 5 seconds, showing deltas
sudo mysqldumpslow -s t -t 10 /var/log/mysql/slow.log	Top 10 slow queries by total time spent
SELECT table_schema, ROUND(SUM(data_length+index_length)/1024/1024,1) FROM information_schema.tables GROUP BY table_schema;	Database sizes in MB
TRUNCATE TABLE performance_schema.events_statements_summary_by_digest;	Reset query statistics so you get a clean baseline after fixing a bad query