How to Tame 900% CPU Spikes in MySQL and Java Processes

MySQL CPU Spike to 900% – Diagnosis and Fix

When MySQL’s CPU usage jumps above 200% and can even reach 900%, the root cause is usually a combination of heavy concurrent queries, missing indexes, and unnecessary slow‑log collection. The following steps help locate and resolve the issue.

Run top to confirm that mysqld is the process consuming CPU.

Execute SHOW PROCESSLIST to list active sessions and identify long‑running or resource‑heavy SQL statements.

For each high‑cost query, examine the execution plan, check for missing indexes, and assess data volume.

Remediation:

Kill the offending threads and observe whether CPU usage drops.

Add missing indexes (e.g., on user_code) and rewrite inefficient queries.

Introduce a caching layer (Redis) to offload repetitive reads from MySQL.

Avoid enabling the slow‑log in a high‑CPU scenario, as it can further degrade performance.

Iteratively apply adjustments, monitor the effect, and repeat until CPU stabilises around 70‑80%.

Java Process CPU Spike to 900% – Diagnosis and Fix

Java applications normally stay within 100‑200% CPU. A spike to 900% often stems from infinite loops, excessive garbage collection, or busy selectors. The diagnostic workflow mirrors the MySQL case but focuses on thread‑level analysis.

Use top to find the Java PID with high CPU.

Run top -Hp <PID> to list threads and locate the one consuming the most CPU.

Convert the thread ID to hexadecimal with printf "%x\n" <tid> for use with jstack.

Execute jstack -l <PID> > jstack_result.txt and grep the hexadecimal thread ID to find the stack trace.

Identify the problematic code (e.g., an empty spin loop, object‑creation burst, or selector polling) and apply the appropriate fix.

Typical fixes include:

Insert Thread.sleep() or proper locking to break empty loops.

Reduce object allocation or employ an object pool to curb GC pressure.

Rebuild or limit Netty selectors to prevent busy‑polling.

Replace non‑blocking poll() on a BlockingQueue with take() so the thread blocks when the queue is empty, eliminating a tight CPU‑consuming loop.

After applying these changes, the Java process CPU usage dropped from 700%+ to under 10% in production.