MySQL High CPU Usage Diagnosis and Optimization on cPanel Server


2 views

When your MySQL server consistently shows 100% CPU utilization despite moderate traffic, we're dealing with either inefficient queries or suboptimal configuration. Your case shows mysqld consuming 303.8% CPU (indicating heavy multi-core usage) with these key indicators:

  • 92.549 queries per second (high for 4-core system)
  • 58% immediate table locks (suggesting contention)
  • 129 fragmented tables

Run these while the CPU is pegged:

SHOW FULL PROCESSLIST;
SHOW ENGINE INNODB STATUS\G
SHOW GLOBAL STATUS LIKE 'Threads_running';
SHOW GLOBAL STATUS LIKE 'Handler_%';
SHOW VARIABLES LIKE 'innodb_io_capacity';

Several parameters need adjustment:

[mysqld]
# Reduce from 500 to prevent connection storms
max_connections=200  

# Increase for InnoDB dominance (33M data vs 164M MyISAM)
innodb_buffer_pool_size=1G  

# Optimize for modern SSDs
innodb_io_capacity=2000
innodb_io_capacity_max=4000
innodb_flush_neighbors=0

# Disable for write-heavy workloads
query_cache_type=0

Create a slow query log (add to my.cnf):

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

Example of optimizing a problematic query:

-- Before
SELECT * FROM orders WHERE status='pending' ORDER BY created_at;

-- After
ALTER TABLE orders ADD INDEX idx_status_created (status, created_at);
SELECT id, customer_id FROM orders 
WHERE status='pending' ORDER BY created_at LIMIT 1000;

For the 129 fragmented tables:

# Get list of fragmented tables
SELECT table_schema, table_name, data_free/1024/1024 AS mb_free
FROM information_schema.tables
WHERE engine='InnoDB' AND data_free > 1000000;

# Optimize critical tables (run during low traffic)
OPTIMIZE TABLE important_customers, order_items;

Before considering hardware upgrade:

  • Implement MySQL 8.0 (better thread pool handling)
  • Setup ProxySQL for query routing
  • Migrate remaining MyISAM tables to InnoDB

When a MySQL server consistently hits 100% CPU utilization despite moderate traffic, we're typically dealing with one of these scenarios:

  • Inefficient query execution plans
  • Suboptimal buffer pool configuration
  • Table fragmentation issues
  • Concurrency bottlenecks
# Critical metrics from your configuration:
mysql> SHOW GLOBAL STATUS LIKE 'Threads_running';
mysql> SHOW PROCESSLIST;
mysql> SHOW ENGINE INNODB STATUS\G

Your current my.cnf reveals several optimization opportunities:

# Problematic settings in current config:
innodb_buffer_pool_size = 128M  # Too small for 8GB RAM
query_cache_size = 256M         # Risk of contention
table_cache = 4000              # Excessive for current usage

First apply these emergency measures to stabilize the server:

# Emergency my.cnf adjustments:
[mysqld]
innodb_buffer_pool_size = 4G
innodb_log_file_size = 256M
innodb_flush_method = O_DIRECT
query_cache_type = 0
table_open_cache = 2000
innodb_thread_concurrency = 8

Implement these procedures systematically:

# Table optimization routine:
mysqlcheck -u root -p --optimize --all-databases

# Fragmentation analysis:
SELECT TABLE_SCHEMA, TABLE_NAME, DATA_FREE/1024/1024 AS MB_FREE
FROM information_schema.TABLES 
WHERE DATA_FREE > 1000000;

Enable slow query logging and analyze with pt-query-digest:

# Slow query configuration:
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1
log_queries_not_using_indexes = 1

# Analysis command:
pt-query-digest /var/log/mysql/mysql-slow.log > slow_query_analysis.txt

Before upgrading to 16GB RAM, validate these metrics:

# Memory pressure check:
vmstat 1 5
# Key InnoDB metrics:
SHOW STATUS LIKE 'innodb_buffer_pool%';

For cPanel environments, add these specialized settings:

# cPanel performance tweaks:
innodb_io_capacity = 2000
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_purge_threads = 4

Set up proactive monitoring with these queries:

# Real-time diagnostics:
SELECT * FROM sys.session 
WHERE time_ms > 1000 
ORDER BY time_ms DESC LIMIT 10;

# Historical analysis:
SELECT * FROM performance_schema.events_statements_summary_by_digest 
ORDER BY sum_timer_wait DESC LIMIT 10;

When standard optimizations aren't enough:

  • Implement MySQL 8.0's resource groups
  • Consider ProxySQL for query routing
  • Evaluate Percona Server's thread pool plugin
# Resource group example:
CREATE RESOURCE GROUP rg_web 
TYPE = USER 
VCPU = 0-3
THREAD_PRIORITY = 5;