Why Is MySQL Consuming CPU? Identify Culprits and Optimize Performance

Who Is Consuming CPU?

User + System + IO wait + soft/hard interrupts + idle

Who Is the Culprit?

User

User‑space CPU consumption comes from heavy logical operations such as high TPS, function calls, sorting, type conversion, and logical IO access.

IO Wait

IO requests that are pending keep the CPU idle; this appears as high "wa" in vmstat. The CPU is not doing work while waiting for IO completion.

Impact of user and IO wait consuming most CPU:

Throughput drops (TPS)

Query response time increases

Slow query count rises

Concurrent MySQL connections surge, further degrading performance

How to Reduce CPU Consumption?

Reduce Waiting

Decrease IO volume: use proper indexes to reduce scanned rows (balance index benefit vs maintenance cost).

Improve IO processing capability: add cache, faster disks, SSDs.

Reduce Computation

Avoid functions; move calculations to application servers (e.g., substr, dateadd, abs).

Minimize sorting; use indexes to retrieve ordered data or avoid unnecessary ORDER BY.

Eliminate type conversions; ensure parameter types match column types exactly.

Prefer simple, small data types to reduce memory and CPU cache usage.

Reduce Logical IO

Optimize indexes to avoid full table scans; add or reorder composite indexes.

Split tables and apply appropriate redundancy (e.g., move rarely used large columns to separate tables).

Rewrite SQL to leverage existing indexes, avoid unnecessary scans, sorts, joins, subqueries, and prefer UNION ALL.

Use appropriate data types; avoid oversized fields (e.g., use tinyint instead of int when sufficient).

Reduce Query Request Volume (Non‑Database)

Cache frequently accessed static data such as user or product information.

Optimize implementation to eliminate duplicate requests (e.g., prevent the same page from requesting identical data multiple times).

Assess demand vs. benefit; discard low‑value features.

Upgrade CPU

If after reducing computation and waiting the CPU utilization remains high, consider upgrading the CPU.

Decide whether to choose a faster CPU or add more CPU cores based on workload characteristics.

References:

"High Performance MySQL"

"Illustrated Performance Optimization"

Materials compiled from "MySQL Tuning For CPU Bottleneck"