Troubleshoot and Fix MySQL High CPU Usage

High CPU usage in MySQL can severely degrade database performance and cause application latency. This guide provides a straightforward approach to identifying the root causes of MySQL CPU spikes—such as inefficient queries, missing indexes, and misconfigured settings—and outlines actionable steps to resolve them and restore system stability.

Identify the Cause of High CPU

To resolve high CPU usage, you must first pinpoint what MySQL is doing when the spike occurs. Use the following diagnostic steps:

1. Check Active Queries

Run the following command in the MySQL command line to see which queries are currently running and how long they have been executing:

SHOW FULL PROCESSLIST;

Look for queries in the Query state that have a high Time value. If you see numerous queries performing “Sending data” or “Creating sort index,” these are likely candidates causing the CPU spike.

2. Enable and Analyze the Slow Query Log

The slow query log captures queries that take longer than a specified threshold to execute. Enable it by adding these lines to your MySQL configuration file (my.cnf or my.ini):

slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1

Once populated, use the mysqldumpslow tool to parse the log and identify the most frequent or resource-intensive queries.

3. Monitor Engine Status

Analyze internal InnoDB metrics to check for locking issues or thread bottlenecks:

SHOW ENGINE INNODB STATUS;

Search the output for “SEMAPHORES” to see if threads are waiting for OS locks, which frequently drives up CPU usage.

Resolve High CPU Usage

Once you have identified the culprit queries or system bottlenecks, apply these solutions to resolve the issue.

1. Optimize Inefficient Queries

Most MySQL CPU issues stem from poorly written queries that scan too many rows. * Use EXPLAIN: Run EXPLAIN before your problematic query to analyze its execution plan.

EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

• Add Indexes: If the type column in the EXPLAIN output shows ALL, MySQL is performing a full table scan. Create targeted indexes on columns used in WHERE, JOIN, ORDER BY, and GROUP BY clauses to reduce CPU overhead.

2. Optimize MySQL Configuration

Inadequate memory allocation forces MySQL to read data from the disk frequently, increasing CPU wait times. Adjust these key parameters in your configuration file:

• innodb_buffer_pool_size: Set this to 50% to 80% of your system’s total RAM on a dedicated database server to ensure data and indexes are cached in memory.
• innodb_log_file_size: Ensure write logs are large enough (typically 25% of the buffer pool size) to avoid frequent flushing checkpoints that saturate the CPU.
• max_connections: Limit the maximum number of concurrent connections to prevent thread contention from overwhelming the processor.

3. Manage Connection Spikes

A sudden influx of database connections can exhaust CPU resources due to connection establishment overhead. * Implement connection pooling in your application layer (using tools like ProxySQL or application-level pools) to reuse active connections. * Verify that your application properly closes database connections after executing queries.