Understanding MySQL CPU Usage: Common Cases and Optimization Strategies

High CPU usage is a frequent symptom in MySQL environments; effective performance troubleshooting starts with monitoring resource consumption and understanding how the server utilizes CPU.

On Linux, CPU time is divided into five primary states: us (user space), sy (kernel space), ni (nice), id (idle), and wa (I/O wait), plus additional indicators such as hi (hardware interrupt), si (software interrupt), and st (steal time). While sy, ni, and si are largely controlled by the OS, the us, id, and wa fields offer optimization opportunities.

Common MySQL scenarios that drive CPU spikes include a surge in connections, inefficient SQL statements, hash joins or multi‑table joins, slow read/write I/O, and poorly tuned parameters.

Problematic SQL states observed via SHOW PROCESSLIST include:

"Sending data" – the thread reads rows for a SELECT and sends them to the client, often involving heavy disk reads.

"Copying to tmp table" and "Copying to tmp table on disk" – the server creates temporary tables in memory or on disk when result sets become too large.

"Sorting result" – large sorts without appropriate indexes on ORDER BY columns.

Relevant excerpts from MySQL documentation:

The thread is reading and processing rows for a SELECT statement, and sending data to the client. Because operations occurring during this state tend to perform large amounts of disk access (reads), it is often the longest‑running state over the lifetime of a given query.

Copying to tmp table
The server is copying to a temporary table in memory.
Copying to tmp table on disk
The server is copying to a temporary table on disk. The temporary result set has become too large

Optimization directions include adding appropriate (single‑column or composite) indexes, limiting joins to two tables when possible, avoiding unnecessary function calls, and tuning buffer sizes such as join_buffer_size, sort_buffer_size, read_buffer_size, read_rnd_buffer_size, and tmp_table_size. In urgent cases, controlling concurrency with parameters like innodb_thread_concurrency and max_execution_time can provide temporary relief.

MySQL I/O handling involves both synchronous and asynchronous operations. Reads triggered by user threads are typically synchronous, while background threads may perform asynchronous reads. InnoDB uses a Write‑Ahead Logging (WAL) approach: logs are written first, and data pages are flushed later. Situations that force asynchronous writes include insufficient log space, aggressive innodb_flush_log_at_trx_commit settings, master thread redo flushes, checkpoints, and page‑cleaner activity.

To pinpoint the CPU core executing a specific MySQL thread, the sys.processlist view can be queried:

mysql> SELECT thd_id, conn_id, user, pid, program_name, command, current_statement FROM sys.processlist WHERE conn_id>0 AND pid>0;

Then use standard Linux tools: # ps -o pid,psr,comm -p 3247 or # top -p 3247 The PSR column shows the CPU core (e.g., core 3) on which the MySQL process is currently running; it may change if the scheduler migrates the thread.

In summary, recommended actions are:

Deploy servers with at least 8 CPU cores and SSD storage.

Design proper indexes and table schemas, and rewrite inefficient queries.

Implement read/write splitting to offload read‑only traffic.

Use caching layers such as Memcached or Redis.

Adjust system parameters (disk scheduler, NUMA, filesystem), MySQL log settings ( innodb_flush_log_at_trx_commit, sync_binlog), and concurrency controls ( innodb_buffer_pool_instances, innodb_thread_concurrency).