Skip to content
Troubleshooting 900% CPU Spikes in MySQL and Java: A Step-by-Step Production Guide

Troubleshooting 900% CPU Spikes in MySQL and Java: A Step-by-Step Production Guide

This article details systematic approaches to diagnose and resolve extreme CPU usage in MySQL and Java processes, covering root causes like missing indexes and busy-wait loops, and demonstrating practical troubleshooting with top, jstack, and thread analysis.

Java Architect Handbook
Java Architect Handbook
Java Architect Handbook

Problem Overview: CPU Spikes Above 200% in Production

CPU usage exceeding 200% is a common production scenario. The article focuses on two primary cases: MySQL process hitting 900% and Java process hitting 900%, with brief mention of Redis and Nginx.

Scenario 1: MySQL Process CPU at 900%

Root Causes

High concurrency combined with poor SQL performance (e.g., missing indexes)

Enabling slow query logs under heavy load worsens performance due to disk I/O

Diagnosis Steps

Run top to confirm mysqld is the culprit

Execute show processlist; to inspect active sessions and identify resource-heavy queries

Analyze the execution plan of problematic SQL; check for missing indexes or excessive data volume

Resolution Process

Kill offending threads while monitoring CPU drop

Apply fixes: add indexes, rewrite SQL, adjust memory parameters

If connection count spikes, coordinate with application team to limit connections

Optimize iteratively: apply one change, observe, then proceed

Real-World Case Study

A developer's SQL caused MySQL CPU to reach 900%+. The troubleshooting sequence:

Analyzed data volume (~few million rows), index status, and cache usage

Found many queries bypassed cache and hit MySQL directly

Used show processlist; revealing repeated select id from user where user_code = 'xxxxx'; stuck in query state

Checked indexes with show index from user; — user_code lacked an index

Added index; query executed normally

Timeouts recurred; discovered slow query log was enabled. Disabling it dropped CPU to ~300%

Moved real-time queries to Redis cache; CPU stabilized at 70–80%

Key takeaways:

Avoid enabling slow logs during high-CPU incidents

Use show processlist for direct SQL diagnosis

Combine indexing with caching to reduce MySQL load

Memory tuning is another viable lever

Scenario 2: Java Process CPU at 900%

Typical Causes

Infinite loops or busy-waiting (spin loops)

Excessive object allocation triggering frequent GC (e.g., loading 1M+ rows from MySQL)

Selector busy-polling in NIO (Netty-style)

Diagnosis Steps

top

— find high-CPU Java process PID top -Hp <PID> — list threads; sort by CPU (Shift+P) or memory (Shift+M)

Convert offending thread ID to hex: printf "%x\n" <TID> Capture thread dump: jstack -l <PID> > jstack_result.txt Search dump for hex nid (prefixed with 0x): grep -A 100 <nid> jstack_result.txt or jstack <PID> | grep -A 200 <nid> Locate the exact method and line in source code

Resolution Patterns

Empty loop / spin: Insert Thread.sleep() or use locking to block the thread

Mass object creation in loop: Reduce allocations or introduce object pooling

Selector busy-polling: Rebuild selector after threshold of empty polls (per Netty approach)

Real-World Case Study: Java Process at 700%

Post-deployment, a Java process consumed 700% CPU, crashing the physical server.

Step 1: Identify Process top PID 29706 showed sustained 700%+ CPU.

Step 2: Identify Threads top -Hp 29706 Multiple threads at 90%+ CPU. Selected thread 30309.

Step 3: Convert Thread ID printf "%x\n" 30309 Result: 7665 → nid 0x7665 .

Step 4: Capture and Search Thread Dump

jstack -l 29706 > ./jstack_result.txt
cat jstack_result.txt | grep -A 100 7665

Stack trace pointed to ImageConverter.run() .

Step 5: Analyze Problematic Code

private BlockingQueue<byte[]> dataQueue = new LinkedBlockingQueue<>(100000);

@Override
public void run() {
    while (isRunning) {
        if (dataQueue.isEmpty()) {
            continue; // busy-wait
        }
        byte[] buffer = dataQueue.poll();
        int len = buffer.length;
        // process...
    }
}

When dataQueue stays empty, the continue creates a tight spin loop, maxing out CPU.

Step 6: Apply Fix Using Blocking API LinkedBlockingQueue offers:

E take() throws InterruptedException; // blocks until element available
E poll(); // returns null immediately if empty

Replaced busy-wait with take() :

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

Result After restart, process CPU dropped below 10%.

Original Source

Signed-in readers can open the original source through BestHub's protected redirect.

Sign in to view source
Republication Notice

This article has been distilled and summarized from source material, then republished for learning and reference. If you believe it infringes your rights, please contactadmin@besthub.devand we will review it promptly.

Javaperformance tuningMySQLproduction debuggingthread analysisjstackCPU troubleshootingblocking queue
Reader feedback

How this landed with the community

Sign in to like

Rate this article

Was this worth your time?

Sign in to rate
Discussion

0 Comments

Thoughtful readers leave field notes, pushback, and hard-won operational detail here.