Why Does MySQL CPU Spike to 900%? Diagnosis and Fixes

CPU usage above 200% is a common production issue; MySQL and Java processes can even reach 900% when high concurrency meets poorly optimized SQL or inefficient code.

MySQL CPU Spike Diagnosis

Use top to confirm mysqld is the culprit.

Run show processlist to locate resource‑heavy sessions.

Inspect the offending SQL’s execution plan for missing indexes or excessive data volume.

Kill the problematic threads, then apply adjustments such as adding the missing index, rewriting the query, or tuning memory parameters before re‑executing the SQL.

Real‑world MySQL case

A production query without an index on user_code caused CPU usage to exceed 900%. The steps were: show processlist; Repeated queries were found in the query state. Checking the index: show index from user; After adding the index, the query ran normally. Disabling the slow‑log (which had further degraded performance) reduced CPU to 300%, and moving frequent reads to Redis cache finally lowered it to 70‑80%.

Do not enable slow‑log during high load.

Use show processlist to pinpoint heavy SQL (often caused by missing indexes, locks, large tables, or full‑table scans).

Introduce caching (e.g., Redis) to cut MySQL request frequency.

Consider memory tuning as an additional remedy.

Java CPU Spike Diagnosis

Identify the high‑CPU process with top and note its PID.

List threads of that PID: top -Hp <PID>.

Convert the thread ID to hexadecimal using printf "%x\n" <tid>.

Dump the thread stack with jstack -l <PID> > jstack_result.txt and grep the hex ID.

Locate the offending method (e.g., ImageConverter.run()).

The problematic code repeatedly called dataQueue.poll() inside a while loop, causing a busy‑wait when the queue was empty, which drove CPU to 900%.

while (isRunning) {
    if (dataQueue.isEmpty()) {
        continue;
    }
    byte[] buffer = device.getMinicap().dataQueue.poll();
    // processing ...
}

Replacing poll() with the blocking take() method eliminated the empty‑loop spin:

while (isRunning) {
    byte[] buffer = new byte[0];
    try {
        buffer = device.getMinicap().dataQueue.take();
    } catch (InterruptedException e) {
        e.printStackTrace();
    }
    // processing ...
}

After redeploying, CPU usage dropped below 10% and the process stabilized.