How to Diagnose and Resolve CPU Spikes up to 900% in MySQL and Java Processes

CPU usage exceeding 200% is a frequent production issue, especially when MySQL or Java processes jump to 900% or more, often caused by high concurrency, missing indexes, heavy GC, or busy loops.

MySQL scenario: Use top to confirm mysqld is the culprit, then run show processlist; to find resource‑intensive queries. Check execution plans for missing indexes (e.g., user_code without an index) and consider disabling slow‑query logging in high‑load periods. Mitigate by killing offending threads, adding appropriate indexes, enabling a cache layer (Redis), limiting connections, and tuning memory parameters.

Real MySQL case: A production database showed 900% CPU due to unindexed queries and an active slow‑log. After adding the missing index, disabling the slow‑log, and routing frequent reads through Redis, CPU dropped to 70‑80% and later stabilized around 30‑40%.

Java scenario: Typical triggers include empty loops, excessive object creation leading to frequent GC, and selector spin. Locate the hot thread with top, then top -Hp <PID> to list threads, convert the thread ID to hex using printf "%x\n" <tid>, and inspect the stack with jstack -l <PID>. Resolve by inserting Thread.sleep or locks for empty loops, using object pools, or rebuilding selectors.

Real Java case: A Java service consumed 700% CPU. Using top and top -Hp 23602 identified thread 30309, which after hex conversion (0x7665) was traced via jstack to ImageConverter.run(). The method used a non‑blocking poll() on a LinkedBlockingQueue, causing a busy loop when the queue was empty. Replacing poll() with take() (blocking) eliminated the spin, reducing CPU usage to under 10%.

These examples demonstrate that systematic use of Linux monitoring tools, SQL diagnostics, and proper concurrency control can effectively tame extreme CPU spikes in both database and application layers.