Diagnosing and Resolving 900% CPU Spikes in MySQL and Java Processes

The article begins by noting that CPU spikes above 200% are common in production and introduces two typical cases: a MySQL process reaching 900% CPU and a Java process doing the same.

Scenario 1: MySQL CPU Spike

Investigation steps:

Use top to confirm the culprit is mysqld.

Run show processlist to identify resource‑heavy SQL statements.

Examine execution plans, missing indexes, or large data volumes.

Remediation steps:

Kill the offending threads and observe CPU reduction.

Add missing indexes, rewrite inefficient SQL, or adjust memory parameters.

Limit connection counts and enable caching (e.g., Redis) to reduce query frequency.

Iterate optimizations and monitor the effect.

Real MySQL case: A production MySQL instance hit 900% CPU due to unindexed user_code queries and excessive slow‑log logging. By adding the missing index, disabling the slow log, and moving frequent reads to Redis cache, CPU dropped to 70‑80% and later to 30‑40% after further tuning.

Scenario 2: Java CPU Spike

Investigation steps:

Identify the high‑CPU Java PID with top.

List threads using top -Hp PID.

Convert thread IDs to hex with printf "%x\n" TID and locate them in a jstack dump.

Analyze the stack trace to find the problematic code.

Remediation steps:

If a busy loop or empty spin, add Thread.sleep or proper locking.

If massive object creation triggers GC, reduce allocations or use object pools.

For selector spin loops, rebuild the selector as shown in Netty source.

Real Java case: A Java service consumed 700% CPU. Using top, top -Hp, and jstack, the offending thread was traced to ImageConverter.run(), which performed an empty‑queue poll in a tight loop. Replacing poll() with the blocking take() method eliminated the spin, reducing CPU usage to under 10%.

Both cases emphasize that proper indexing, caching, and careful thread handling are essential to prevent extreme CPU usage in backend services.