Why Is MySQL Consuming CPU? Identify and Reduce CPU Bottlenecks

Who Is Consuming CPU?

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

The Culprit?

User

User‑space CPU consumption due to logical operations.

Heavy TPS workload Functions / sorting / type conversion / logical IO access…

What system calls are generated? Which functions use CPU cycles?

IO Wait

Waiting for IO request completion; CPU is actually idle.

At this point CPU is idle.

High wa in vmstat indicates IO wait, but an increase in IO wait does not always raise wa because processes may be moved off the core after issuing IO.

Impact

User and IO wait consume most CPU.

Throughput drops (TPS)

Query response time increases

Slow queries increase

Concurrent MySQL spikes cause the above effects

How to Reduce CPU Consumption?

Reduce Wait

Reduce IO volume

Use appropriate indexes to reduce scanned rows (balance index benefits vs maintenance cost, space for time).

Improve IO handling

Add cache / faster disks / SSD.

Reduce Computation

Reduce logical operation count

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

Reduce sorting by using indexes or avoiding unnecessary sorts (e.g., UNION ALL instead of UNION, ORDER BY indexed columns).

Prohibit type casting; use matching data types (e.g., tiny/int/bigint for numbers, ensure parameters match column types).

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

…

Reduce Logical IO

Index : Optimize indexes, reduce unnecessary table scans (add indexes, adjust composite index order, drop low‑selectivity columns).

Table : Reasonable splitting, moderate redundancy (split rarely used large fields, duplicate small frequent fields in reference tables).

SQL : Refine queries to leverage existing indexes, avoid unnecessary scans, sorts, complex joins, subqueries; use UNION ALL, limit ORDER BY.

Data Types : Use appropriate size (e.g., tinyint instead of int, avoid unnecessary bigint, use DATE instead of TIMESTAMP when possible).

…

Reduce Query Requests (outside DB)

Cache appropriately : Cache static frequently requested data (user info, product info).

Optimize implementation : Eliminate duplicate requests, pass parameters across pages to reduce calls.

Rationalize demand : Evaluate cost‑benefit, drop low‑value features.

…

Upgrade CPU

If reductions are insufficient, consider upgrading CPU: choose faster cores for low‑latency queries or more cores for high‑throughput workloads.

Low latency : Faster CPU needed when each query uses a single core.

High throughput : Multiple queries benefit from multiple CPUs.

References

High Performance MySQL

Illustrated Performance Optimization

Mostly compiled from “MySQL Tuning For CPU Bottleneck”