How to Identify and Reduce CPU Bottlenecks in MySQL

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…

What system calls are generated and which functions use CPU cycles?

IO Wait

Waiting for IO requests to complete.

CPU is actually idle during this period.

In vmstat the wa column may be high, but IO wait does not always increase proportionally.

Impact

User and IO wait consume most CPU.

Throughput drops (TPS)

Query response time increases

Slow query count rises

Concurrent MySQL load spikes, causing the above effects

How to reduce CPU consumption?

Reduce waiting

Reduce IO volume

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

Improve IO processing capability

Add cache, upgrade disks/SSD.

Reduce computation

Reduce logical operation amount

Avoid using functions; move calculations 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 unnecessary type conversion; ensure parameter types match column types (use tinyint/int/bigint appropriately).

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

…

Reduce logical IO volume

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

Table : Reasonable sharding, moderate redundancy (split rarely used large fields, duplicate small frequently accessed fields).

SQL : Refactor queries to leverage existing indexes, avoid unnecessary scans, sorts, complex joins, subqueries.

Data type : Use the smallest adequate type (e.g., tinyint instead of int, avoid unnecessary bigint or timestamp).

…

Reduce query request volume (outside the database itself)

Appropriate caching : Cache static or frequently requested data (user info, product info).

Optimize implementation : Eliminate duplicate requests, prevent multiple pages from requesting the same data.

Reasonable demand : Evaluate cost‑benefit of features and drop low‑value ones.

…

Upgrade CPU If reductions are insufficient, consider upgrading CPU—choose faster cores for low latency or more cores for high throughput.

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

High throughput : Multiple cores benefit when many queries run concurrently.

References

High Performance MySQL

Illustrated Performance Optimization

Mostly compiled from “MySQL Tuning For CPU Bottleneck”