How to Diagnose MySQL High CPU Usage

Q: CPU is high but `SHOW PROCESSLIST` shows only `Sleep` threads. What's happening?

You may be checking between bursts. Use performance_schema.events_statements_summary_by_digest which accumulates over time. Also check Threads_running vs. Threads_connected — a sustained gap indicates steady active load.

MySQL CPU usage spikes when the server performs more computational work per second than the available cores can handle. The work is almost always dominated by query execution — specifically by inefficient query plans that scan far more rows than necessary. Identifying and fixing those queries is the primary lever.

Impact

High CPU usage causes query latency to increase across the board as queries wait for CPU time. At saturation, the MySQL thread scheduler cannot keep up, connection queues grow, and the application starts seeing timeouts and error 1040 (too many connections). Unlike I/O-bound slowdowns, CPU-bound problems worsen rapidly under load and provide little warning.

Common Causes

Missing indexes causing full table scans on large tables under concurrent load
A sudden increase in query rate (traffic spike, new feature, cronjob firing)
Suboptimal query plans: nested-loop joins over large row sets, ORDER BY with filesort, GROUP BY with temp tables
High-cardinality IN (...) lists or repeated single-row lookups that should be batched
Excessive SELECT * returning large rows that must be serialized, compared, and sorted
AUTO_INCREMENT mutex contention on bulk insert workloads
A SHOW STATUS / SHOW VARIABLES query in an application health check running at high frequency
Sort and hash operations for analytics queries competing with OLTP workloads
Binary log encryption or compression with binlog_encryption=ON on high-write workloads
A long-running query holding the thread while repeatedly scanning the same data (e.g., repeated subquery evaluation)

Troubleshooting and Resolution Steps

Identify CPU usage at the OS level first:
```
# Overall CPU breakdown
top -p $(pgrep mysqld | head -1)

# Per-thread CPU (Linux)
top -H -p $(pgrep mysqld | head -1)

# Detailed CPU profile with perf
perf top -p $(pgrep mysqld | head -1)
```
High %us (user-space CPU) points to query execution or in-memory operations. High %sy (kernel) can indicate excessive context switching (too many threads) or I/O system calls.

Identify the top CPU-consuming queries right now:

-- Queries currently running that have been running longest
SELECT
    id,
    user,
    host,
    db,
    command,
    time,
    state,
    LEFT(info, 200) AS query
FROM information_schema.PROCESSLIST
WHERE command NOT IN ('Sleep', 'Binlog Dump')
ORDER BY time DESC
LIMIT 20;

Find the top CPU-consuming query patterns historically:

SELECT
    DIGEST_TEXT,
    COUNT_STAR AS calls,
    ROUND(SUM_TIMER_WAIT / 1e12, 2) AS total_s,
    ROUND(AVG_TIMER_WAIT / 1e12, 4) AS avg_s,
    ROUND(SUM_CPU_TIME / 1e12, 2) AS cpu_total_s,
    SUM_ROWS_EXAMINED,
    SUM_ROWS_SENT,
    ROUND(SUM_ROWS_EXAMINED / NULLIF(SUM_ROWS_SENT, 0), 0) AS rows_per_row_sent
FROM performance_schema.events_statements_summary_by_digest
ORDER BY cpu_total_s DESC
LIMIT 10;

The SUM_CPU_TIME column is available in MySQL 8.0.28+. On earlier versions, sort by SUM_TIMER_WAIT as a proxy.

Run EXPLAIN on the top CPU consumers:
```
EXPLAIN FORMAT=TREE SELECT ...;
-- or
EXPLAIN ANALYZE SELECT ...;
```
Look for type: ALL (full scan), Using filesort, Using temporary, and large rows estimates relative to the actual rows needed.
Enable the slow query log to catch CPU-heavy queries over time:
```
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;
SET PERSIST slow_query_log = ON;
SET PERSIST long_query_time = 1;
```
Then aggregate with pt-query-digest to find the worst patterns by CPU time.

Add indexes for the highest-scan-ratio queries. The ratio SUM_ROWS_EXAMINED / SUM_ROWS_SENT from step 3 is the clearest indicator: a ratio of 1000 means the query reads 1,000 rows to return 1:

-- Example: query filters on status and created_at
EXPLAIN SELECT id FROM orders WHERE status = 'pending' AND created_at > '2025-01-01';

-- Add a composite index
ALTER TABLE orders ADD INDEX idx_status_created (status, created_at);

Kill runaway queries that are consuming excessive CPU right now:
```
KILL QUERY <process_id>;
```
Then investigate why the query is running and fix it before it recurs.
Reduce max_connections if thread-scheduler contention is the issue. More threads than CPU cores means constant context switching:
```
SHOW VARIABLES LIKE 'max_connections';
SHOW GLOBAL STATUS LIKE 'Threads_running';
```
Threads_running (actively executing, not sleeping) above 2–4× the CPU core count indicates contention. Lower max_connections and route traffic through a connection pool to reduce active thread count.
Use the thread pool plugin (MySQL Enterprise or Percona/MariaDB) to cap active threads: The thread pool limits the number of simultaneous executing threads regardless of connected clients, preventing the CPU oversubscription that causes spikes under connection storms. On Percona Server:
```
[mysqld]
plugin-load-add = thread_pool.so
thread_pool_size = 16    # number of CPU cores
```
Offload analytics queries to a read replica or a dedicated analytics database. Aggregate queries with GROUP BY / ORDER BY on large data sets are inherently CPU-intensive. Running them on the primary competes directly with OLTP:
- Route read-only analytic queries via a separate DSN pointing to a replica
- Schedule heavy reports outside peak hours
- Use MySQL 8.0's window functions and CTEs to reduce result set size before aggregation

Check for implicit conversion causing full scans. This is the easiest CPU multiplier to miss:

-- If status is ENUM and the query passes a number:
WHERE status = 1        -- triggers full scan + cast on every row
WHERE status = 'active' -- uses index

Optimize AUTO_INCREMENT lock mode for bulk inserts:
```
SHOW VARIABLES LIKE 'innodb_autoinc_lock_mode';
```
Mode 0 (traditional) takes a table-level AUTO-INC lock for each INSERT statement — catastrophic under concurrent bulk loads. Mode 2 (interleaved, the default in MySQL 8.0) uses lightweight mutexes and is safe with row-based replication:
```
SET PERSIST innodb_autoinc_lock_mode = 2;
```

Additional Information

MySQL is single-threaded per query — one query uses at most one CPU core, unless it uses parallel query (MySQL 8.0 Enterprise) or parallel replication. Scaling to many cores requires many concurrent queries.
The performance_schema.events_stages_summary_by_digest table breaks down time spent in each stage (e.g., Creating sort index, Sending data) and can pinpoint whether the CPU spike is in sorting, scanning, or network serialization.
On AWS RDS and Aurora, CPU credit depletion on burstable instance types (db.t3.*) can cause sudden CPU throttling. Check CPUCreditBalance in CloudWatch.
innodb_buffer_pool_size affects CPU indirectly: a undersized buffer pool causes InnoDB to read pages from disk repeatedly, which causes more I/O waits — but a well-sized pool keeps hot pages in RAM and reduces logical read overhead.

Frequently Asked Questions

Q: CPU is high but SHOW PROCESSLIST shows only Sleep threads. What's happening? A: You may be checking between bursts. Use performance_schema.events_statements_summary_by_digest which accumulates over time. Also check Threads_running vs. Threads_connected — a sustained gap indicates steady active load.

Q: After adding an index, CPU dropped from 90% to 30% but is still high. What next? A: Check the next query in the top-CPU list (step 3) and repeat the indexing cycle. CPU optimization is iterative — fix the top consumer, then find the next one.

Q: Is high CPU always a query problem? A: Mostly, but not exclusively. InnoDB background threads (purge, checkpoint, page cleaners) consume CPU during high-write bursts. Check SHOW ENGINE INNODB STATUS → BUFFER POOL AND MEMORY and BACKGROUND THREAD sections. Binlog encryption, InnoDB compression, and row format conversion also add CPU overhead.

Q: The spike happens at a specific time every day. What should I look for? A: Time-correlated spikes are almost always scheduled jobs — cron, batch ETL, scheduled backups (mysqldump, xtrabackup), or report generation. Identify the job by correlating the spike time with cron schedules and application scheduler logs.

Q: Should I raise innodb_buffer_pool_instances to reduce CPU? A: Only indirectly. More pool instances reduce mutex contention on the buffer pool itself under very high concurrency (> 8 cores, > max_connections hammering the pool). The default of 8 instances is adequate for most workloads; the CPU impact of changing this is small compared to fixing a bad query plan.