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.
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 7665Stack 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 emptyReplaced 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%.
Signed-in readers can open the original source through BestHub's protected redirect.
This article has been distilled and summarized from source material, then republished for learning and reference. If you believe it infringes your rights, please contactand we will review it promptly.
How this landed with the community
Was this worth your time?
0 Comments
Thoughtful readers leave field notes, pushback, and hard-won operational detail here.
