Why Is MySQL Eating Your CPU? Identify and Reduce CPU Bottlenecks

Who Is Consuming CPU?

User + System + IO Wait + Soft/Hard Interrupts + Idle

Who Is the Culprit?

User

User‑space CPU consumption caused by various logical operations.

Heavy TPS: functions, sorting, type conversion, logical IO access…

IO Wait

Waiting for IO requests to complete.

CPU is actually idle during this time; vmstat may show high wa, but IO wait does not always raise the wa metric because the process is off‑core.

Impact

User and IO wait consume the majority of CPU.

Throughput drops (TPS)

Query response time increases

Number of slow queries rises

Sudden MySQL concurrency spikes cause the above effects

How to Reduce CPU Consumption?

Reduce Wait

Reduce IO Volume

Use appropriate indexes to lower scanned rows (balance index benefit vs maintenance cost; trade space for time).

Improve IO Processing Capability

Add cache, add faster disks/SSD.

Reduce Computation

Decrease logical operation amount.

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

Reduce sorting by leveraging indexes or avoiding unnecessary sorts (e.g., use UNION ALL instead of UNION, order by indexed columns).

Prohibit implicit type conversion; use matching data types and convert before inserting into the database.

Prefer simple data types; smaller types use less disk, memory, cache, and CPU cycles.

…

Reduce logical IO volume.

Optimize indexes to avoid unnecessary table scans (add indexes, reorder composite index fields, drop low‑selectivity columns).

Split tables and apply moderate redundancy (move rarely used large columns to separate tables, keep frequently accessed small columns in a reference table).

Rewrite SQL to fully utilize existing indexes, avoid costly joins, excessive ORDER BY, and sub‑queries.

Choose appropriate data types; avoid oversized fields (e.g., use TINYINT instead of INT when sufficient).

…

Reduce query request volume (outside the database).

Apply appropriate caching for static, frequently requested data (e.g., user info, product info).

Optimize implementation to eliminate duplicate requests (e.g., prevent multiple identical requests on the same page, use cross‑page parameters).

Assess demand‑to‑output ratio and discard low‑value requirements.

…

Upgrade CPU

If reductions are insufficient and CPU utilization remains high, consider a hardware upgrade.

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

References

"High Performance MySQL"

"Illustrated Performance Tuning"

Mostly compiled from "MySQL Tuning For CPU Bottleneck"