Skip to content
statance
MenuClose
← All troubleshooting guides

MySQL is using 100% CPU

Linux

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

  1. See running queries

    Long Time values and repeated similar queries are the place to start.

    sudo mysql -e 'SHOW FULL PROCESSLIST'
  2. 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'"
  3. Summarise the slow log

    Groups queries by pattern and total time.

    sudo mysqldumpslow -s t /var/lib/mysql/HOSTNAME-slow.log | head -40
  4. Explain 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.