MySQL Using 100% CPU

MySQL Using 100% CPU

MySQL consuming 100% CPU almost always means one of three things: a query doing a full table scan on a large table (missing index), a query returning too many rows that the application is filtering in code instead of SQL, or a recently changed query that was fast with small data but became slow as data grew. The fix is nearly always adding a missing index or rewriting the query — not tuning MySQL configuration parameters.

Immediate diagnosis

# Step 1: confirm MySQL is using high CPU:
top -p $(pgrep mysqld)

# Step 2: see what queries are running RIGHT NOW:
sudo mysql -u root -p -e "SHOW FULL PROCESSLIST\G"

SHOW FULL PROCESSLIST output

*************************** 3. row ***************************
     Id: 1234
   User: appuser
   Host: 10.0.0.10:43210
     db: production
Command: Query
   Time: 145
  State: Sending data
   Info: SELECT * FROM orders WHERE customer_id = 5 AND status = 'pending'
# Time: 145 seconds! This query has been running for 2+ minutes.
# "Sending data" + long time = large result set, probably missing index

# Step 3: kill the runaway query:
sudo mysql -u root -p -e "KILL QUERY 1234"

# Step 4: identify slow queries by running time:
sudo mysql -u root -p -e "SELECT id, user, host, db, time, info FROM information_schema.processlist WHERE time > 10 ORDER BY time DESC"

Slow query log analysis

# Enable slow query log (critical for ongoing diagnosis):
sudo mysql -u root -p -e "SET GLOBAL slow_query_log = 1; SET GLOBAL long_query_time = 1; SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';"

# Persist in /etc/mysql/mysql.conf.d/mysqld.cnf:
# slow_query_log = 1
# slow_query_log_file = /var/log/mysql/slow.log
# long_query_time = 1    # Log queries taking more than 1 second

# Analyze slow query log:
sudo mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

mysqldumpslow output

Count: 1456  Time=12.45s (18127s)  Lock=0.00s (0s)  Rows=45678.0 (66527688)
  SELECT * FROM orders WHERE customer_id = N AND status = 'N'
# This query ran 1456 times, averaging 12 seconds each = serious problem

Query optimization

# EXPLAIN shows the query execution plan:
sudo mysql -u root -p production -e "EXPLAIN SELECT * FROM orders WHERE customer_id = 5 AND status = 'pending'\G"

EXPLAIN output — showing full table scan

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: orders
   partitions: NULL
         type: ALL     ← ALL = full table scan (BAD)
possible_keys: NULL    ← No usable indexes found
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2345678  ← Scanning 2.3 million rows!
     filtered: 0.10
        Extra: Using where

# Add the missing index:
sudo mysql -u root -p production -e "ALTER TABLE orders ADD INDEX idx_customer_status (customer_id, status);"

# Verify the index is used now:
sudo mysql -u root -p production -e "EXPLAIN SELECT * FROM orders WHERE customer_id = 5 AND status = 'pending'\G"
# Now shows: type: ref, rows: 3 (down from 2.3 million)

MySQL configuration tuning

# Key MySQL performance settings (/etc/mysql/mysql.conf.d/mysqld.cnf):
# innodb_buffer_pool_size = 70-80% of available RAM for dedicated MySQL servers
# Set based on available RAM:
# 4GB server  → 3GB
# 16GB server → 12GB
# 32GB server → 24GB

sudo mysql -u root -p -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"

# Apply (requires restart):
# innodb_buffer_pool_size = 12G
sudo systemctl restart mysql

# Check if buffer pool is sufficient (hit rate should be >99%):
sudo mysql -u root -p -e "SHOW STATUS LIKE 'Innodb_buffer_pool_read_requests'; SHOW STATUS LIKE 'Innodb_buffer_pool_reads';"
# Hit rate = 1 - (Reads/Read_requests) * 100
# If hit rate < 99%, increase innodb_buffer_pool_size

Conclusion

Enable the slow query log immediately on any MySQL server — it has negligible performance impact and is invaluable for catching performance problems before they become outages. The slow query log tells you exactly which queries need attention, and EXPLAIN shows you why they are slow. In 95% of cases, the solution is adding a composite index on the columns in the WHERE clause. Always add indexes on foreign key columns and any column used in frequent WHERE or JOIN conditions.