How to Tackle a 900% CPU Spike in MySQL and Java Processes

MySQL CPU spike to 900%

High concurrency combined with missing indexes or an enabled slow‑log can push mysqld CPU well beyond 200% and even reach 900%.

Diagnosis

Run top to confirm that mysqld is the high‑CPU process.

Execute show processlist to locate resource‑heavy SQL statements.

Inspect the execution plan of those statements for missing indexes or unusually large data scans.

Remediation

Kill the offending threads while observing CPU, then apply adjustments such as adding indexes, rewriting SQL, or tuning memory parameters.

If a flood of connections causes the surge, analyze why connections spike and consider limiting the connection count.

Iterate: apply one change, monitor CPU, then proceed to the next.

Real‑world case

A production incident showed CPU >900% caused by a query on user_code without an index and an active slow‑log. Adding the missing index reduced CPU to 70‑80%. Disabling the slow‑log further improved stability. Introducing Redis caching for frequent reads kept CPU at 70‑80% consistently.

Do not enable slow‑log when CPU is already high; it can exacerbate the problem.

Use show processlist to pinpoint problematic SQL.

Employ a cache layer (e.g., Redis) to cut down MySQL query frequency.

Memory tuning can also contribute to the solution.

Java CPU spike to 900%

Typical causes include infinite loops, excessive garbage collection, or selector spin in high‑concurrency code.

Diagnosis

Identify the high‑CPU Java PID with top.

List threads and their CPU usage: top -Hp <PID>.

Convert the thread TID to hexadecimal (e.g., printf "%x\n" 30309).

Generate a thread dump: jstack -l <PID> > ./jstack_result.txt and grep the hex nid to locate the offending method.

Analyze the identified code.

Remediation

If the thread is in an empty loop or spin, insert Thread.sleep or appropriate locking to block it.

If massive object creation triggers frequent GC (e.g., processing >1 M rows), reduce allocations or use an object pool.

For selector spin (e.g., Netty), rebuild the selector after a threshold of empty polls, following Netty source recommendations.

Code illustration

private BlockingQueue<byte[]> dataQueue = new LinkedBlockingQueue<byte[]>(100000);
while (isRunning) {
    if (dataQueue.isEmpty()) {
        continue; // busy loop → high CPU
    }
    byte[] buffer = device.getMinicap().dataQueue.poll();
    int len = buffer.length;
}

The empty‑queue check creates a tight loop that burns CPU.

Fix by using the blocking take() method, which waits until data arrives:

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

After applying this change the Java process CPU dropped below 10%.