Diagnosing and Resolving MySQL and Java CPU Spike Issues (CPU up to 900%)

Scenario 1: MySQL process CPU spikes to 900%, how to handle?

Location process:

Use the top command to confirm whether mysqld is the cause.

If it is, run show processlist to view sessions and identify resource‑heavy SQL statements.

Find the high‑consumption SQL, check its execution plan, verify missing indexes, or consider large data volume.

Handling process:

Kill the offending threads while observing CPU usage, then adjust (add indexes, rewrite SQL, modify memory parameters) and rerun the queries.

If the issue is a sudden surge of sessions, work with the application team to limit connections or throttle traffic.

Optimization is iterative: apply one change, observe the effect, then continue refining.

Real case: MySQL optimization example

A colleague once caused MySQL CPU usage to exceed 900% due to unindexed queries and an enabled slow‑log, which further degraded performance. After adding the missing index and disabling the slow‑log, CPU dropped to 70‑80%.

Key steps included:

Avoid blindly enabling slow‑log under high concurrency; it can push MySQL toward collapse.

Inspect data volume, index status, and cache usage; the dataset was only a few million rows.

Identify that many queries bypassed cache, leading to massive direct MySQL load.

Run show processlist; to spot repetitive queries.

Discover missing index on user_code and create it, which resolved the heavy query.

After disabling the slow‑log, CPU stabilized around 300% but was still high.

Introduce Redis caching for real‑time data, bringing CPU back to 70‑80%.

Summary: Adding appropriate indexes and leveraging caching are simple yet effective solutions for high‑CPU MySQL incidents.

Do not enable slow‑log when CPU is already high; disk I/O will further degrade performance.

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

Employ a cache layer to reduce query frequency.

Memory tuning can also help.

Scenario 2: Java process CPU spikes to 900%, how to handle?

Location process:

Typical steps to locate the cause:

Run top to find the high‑CPU Java process PID.

Use top -Hp PID to view threads within the Java process.

Convert the thread PID to hexadecimal with printf "%x\n" <tid> and search the stack trace.

Run jstack to obtain a thread dump and locate the offending code.

Analyze the code and apply fixes.

Handling process:

Common remedies:

For empty loops or spin‑locks, insert Thread.sleep or proper locking to block the thread.

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

For selector empty‑polling causing CPU spikes, rebuild the selector after a threshold, as shown in Netty source code.

Real case: Java CPU spike to 700% optimization

A production Java service reached 700% CPU, causing server overload. The investigation proceeded as follows:

Using top to locate the process

Identified PID 29706 with CPU >700%.

Using top -Hp to locate the thread

Thread 30309 showed >90% CPU usage.

Using jstack to locate the code

Convert thread ID to hex (e.g., printf "%x\n" 30309 → 0x7665) and grep the thread dump:

cat jstack_result.txt | grep -A 100 7665

The problematic method was ImageConverter.run() , which contained a busy loop reading from a BlockingQueue .

Code analysis and fix

Original loop used poll() with an empty‑check, leading to a tight loop when the queue was empty:

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

Replacing poll() with the blocking take() method prevents the empty‑loop and reduces CPU usage dramatically.

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

After redeploying, CPU consumption fell below 10% and the service stabilized.

Backend Technical Community Invitation

Join a high‑quality technical exchange group for developers and technical recruiters. Share job referrals, discuss industry topics, and help each other improve.

Maintain civil discourse and focus on technical exchange , job referrals , and industry discussion .

Advertisements and private solicitations are prohibited.