Why MySQL Eats CPU and How to Slash Its Usage

Who Is Consuming CPU?

User space, system calls, I/O wait, soft/hard interrupts, and idle time together make up CPU usage. The dominant contributors are:

User processes : heavy TPS, functions, sorting, type conversion, logical I/O.

I/O wait : CPU appears idle while waiting for I/O completion; high wa in vmstat indicates this.

Impact : User and I/O wait consume most CPU, leading to reduced throughput, longer query response times, more slow queries, and stress on MySQL concurrency.

How to Reduce CPU Consumption?

1. Reduce Waiting

Decrease I/O volume: use better SQL/indexes, choose appropriate indexes to cut scanned rows (balance index benefit vs maintenance cost).

Boost I/O handling: add cache, faster disks, SSDs.

2. Reduce Computation

Cut logical operations.

3. Reduce Logical I/O

Index : add or reorder composite indexes, drop low‑selectivity indexes.

Table design : split rarely used large columns into separate tables; duplicate frequently accessed small columns in a reference table.

SQL : rewrite queries to use existing indexes, avoid unnecessary scans, sorts, complex joins, sub‑queries, and excessive ORDER BY.

Data types : choose the smallest sufficient type (e.g., tinyint instead of int, date instead of timestamp).

4. Reduce Query Request Volume (Application Layer)

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

Eliminate duplicate requests across pages by sharing parameters.

Assess demand‑to‑value ratio and drop low‑impact features.

5. Upgrade CPU When Necessary

If after minimizing computation and waiting the CPU utilization remains high, consider hardware upgrades:

Low‑latency scenarios benefit from faster single‑core CPUs.

High‑throughput workloads gain from adding more CPU cores to parallelize queries.