How to Diagnose and Fix 900% CPU Spikes in MySQL and Java Processes

Why CPU spikes to 900% happen

In production, a CPU usage above 200% is common when a process performs heavy work or when many low‑performance SQL statements run concurrently without proper indexes, or when Java threads enter tight loops or excessive garbage collection.

Scenario 1: MySQL process CPU 900%

Typical symptoms: the MySQL daemon (mysqld) consumes a huge amount of CPU, often caused by missing indexes, large data scans, or an enabled slow‑query log that adds extra overhead.

Use top to confirm mysqld is the culprit.

Run show processlist; to list active sessions and identify resource‑hungry SQL.

Inspect the offending SQL, check its execution plan, and verify whether required indexes are missing.

Remediation steps:

Kill the offending threads while monitoring CPU drop.

Add missing indexes (e.g., on user_code).

Consider limiting connection counts if a sudden surge of sessions occurs.

Avoid enabling the slow‑query log during high‑concurrency periods, as it can further degrade performance.

Introduce caching (e.g., Redis) to offload frequent reads from MySQL.

Real case: MySQL optimization

A production incident showed MySQL CPU at >900%. The investigation revealed many identical queries without an index on user_code. Adding the index reduced the query cost dramatically.

show processlist;

select id from user where user_code = 'xxxxx';

show index from user;

After creating the index, CPU dropped to 70‑80%.

Additional improvements:

Disable the slow‑query log, which had been logging many long‑running statements.

Cache frequent read‑only data in Redis, further lowering MySQL CPU to around 70%.

Scenario 2: Java process CPU 900%

Java processes normally stay within 100‑200% CPU. Extreme spikes occur due to infinite loops, excessive object creation causing GC pressure, or selector spin‑loops.

Identify the high‑CPU Java PID with top.

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

Convert a thread ID to hex: printf "%x\n" 30309 (result 0x7665).

Export the thread dump: jstack -l <PID> > ./jstack_result.txt.

Search the dump for the hex nid: cat jstack_result.txt | grep -A 100 7665 to locate the offending method (e.g., ImageConverter.run()).

Real case: Java CPU 700% optimization

The problematic thread was looping on an empty BlockingQueue, causing a busy‑wait. Switching from poll() to take() (which blocks until data arrives) eliminated the spin.

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

After redeploying, the Java process CPU fell below 10% and the server remained stable.