Why Is One MySQL CPU Core at 99%? A Step‑by‑Step Troubleshooting Guide

While running a query performance test, monitoring with nmon revealed that one CPU core on a MySQL server consistently hovered around 99% usage, whereas the other three cores remained idle on a 4‑core, 8 GB instance.

01. Verify I/O Thread Configuration

First, the innodb_read_io_threads and innodb_write_io_threads variables were inspected:

show variables like 'innodb_read_io_threads';
show variables like 'innodb_write_io_threads';

Both variables returned a value of 8, indicating that I/O thread count was not the root cause of the high CPU usage.

02. Confirm Multi‑Core Utilization

The pidstat -p <pid> command was used to verify that MySQL can indeed leverage multiple CPU cores. The output confirmed that MySQL was capable of multi‑core execution.

03. Examine Transactions and Locks via information_schema

To determine whether the problem stemmed from locking or long‑running transactions, the information_schema.INNODB_TRX table was queried: SELECT * FROM information_schema.INNODB_TRX; This table lists all currently active InnoDB transactions, showing their state, start time, and the SQL statement being executed. It requires the PROCESS privilege.

Running transactions in the RUNNING state were identified, and the MySQL slow‑query log was consulted to pinpoint the offending statements.

04. Enable and Configure the Slow Query Log

The slow‑query log is disabled by default. It was turned on and configured as follows:

mysql> show variables like '%slow_query_log%';
+---------------------+-----------------------------------------------+
| Variable_name       | Value                                         |
+---------------------+-----------------------------------------------+
| slow_query_log      | OFF                                           |
| slow_query_log_file | /home/WDPM/MysqlData/mysql/DB-Server-slow.log |
+---------------------+-----------------------------------------------+

mysql> set global slow_query_log=1;  -- enable for current session
mysql> set global long_query_time=2;  -- log queries >2 s
mysql> set global slow_query_log_file='/usr/local/mysql/data/slow.log';  -- set log file location

After enabling the log, the mysqldumpslow utility was used to analyze it:

mysqldumpslow -s r -t 5 /database/mysql/mysql06_slow.log   # top 5 queries by rows examined
mysqldumpslow -s c -t 5 /database/mysql/mysql06_slow.log   # top 5 queries by count

The analysis uncovered six queries with extremely long execution times, some exceeding 140 000 seconds, and revealed missing indexes on several tables. In particular, queries lacked indexes on columns used in WHERE clauses.

05. Optimize Queries and Indexes

Developers added the necessary indexes and rewrote hot‑table queries to limit the time range, then re‑ran the performance test. Monitoring showed CPU usage evenly distributed across all four cores, averaging 35%, and query execution times dropped dramatically.

This experience demonstrates a systematic approach to diagnosing high CPU usage in MySQL: check I/O thread settings, verify multi‑core usage, inspect active transactions, enable and analyze the slow‑query log, and finally optimize indexes and query logic.