High MySQL CPU almost always comes from a few queries scanning too many rows. Run SHOW FULL PROCESSLIST to see what is running now, enable the slow query log to catch repeat offenders, and use EXPLAIN to check whether they use an index. Adding the right index usually fixes it; buying more CPU usually does not.
Symptoms
- mysqld at the top of top with high %CPU
- Pages that query the database slow down
- Many queries in “Sending data” or “Creating sort index” state
How to diagnose it
See running queries
Long Time values and repeated similar queries are the place to start.
sudo mysql -e 'SHOW FULL PROCESSLIST'Enable the slow query log
Logs queries over one second. Set the same in my.cnf to persist.
sudo mysql -e "SET GLOBAL slow_query_log=1; SET GLOBAL long_query_time=1;" sudo mysql -e "SHOW VARIABLES LIKE 'slow_query_log_file'"Summarise the slow log
Groups queries by pattern and total time.
sudo mysqldumpslow -s t /var/lib/mysql/HOSTNAME-slow.log | head -40Explain a slow query
type: ALL and a large rows value mean a full table scan.
EXPLAIN SELECT ...;
Common causes and fixes
Missing index
Add an index on the columns used in WHERE, JOIN and ORDER BY.
ALTER TABLE orders ADD INDEX idx_customer_created (customer_id, created_at);Buffer pool too small
If the working set does not fit in innodb_buffer_pool_size, MySQL reads from disk and burns CPU. Raise it within available RAM.
Too many connections hammering the same query
Cache results in the application or Redis, and use connection pooling.
WordPress plugins or large autoloaded options
Check wp_options for large autoloaded rows and plugins running heavy queries on every page.
SELECT option_name, LENGTH(option_value) len FROM wp_options WHERE autoload='yes' ORDER BY len DESC LIMIT 10;Prevent it happening again
- Alert when CPU_overall is above 90% for 10 minutes; Statance’s top-process history shows whether mysqld was responsible.
- Keep the slow query log enabled with a sensible threshold.
- Add a keyword or JSON check on a page that queries the database.
Set it up: Linux monitoring · Keyword monitoring monitoring.
FAQ
How do I see what MySQL is doing right now?
SHOW FULL PROCESSLIST lists every connection, its state and the query it is running.
Should I kill long-running queries?
KILL QUERY ID stops a runaway query, but it will come back. Fix the query or index.
Related guides
Get alerted before this becomes an outage.
Install the agent in one line. Statance watches CPU, memory, disk and services, and its AI assistant helps find the cause.