Understanding CPU Utilization in MySQL

High CPU usage often indicates underlying performance issues. Analyzing resource consumption helps identify bottlenecks and optimize server operations. MySQL can cause CPU spikes due to inefficient queries, IO bottlenecks, or configuration issues.

CPU States Explained

$ top
top - 10:24:03 up 36 days, 28 min,  1 user,  load average: 0.98, 2.18, 4.09
%Cpu(s):  0.2 us,  0.3 sy,  3.8 ni, 95.6 id,  0.0 wa,  0.0 hi,  0.0 si,  0.0 st

• us: User processes CPU time
• sy: System kernel CPU time
• ni: Nice-adjusted process priority
• id: Idle CPU percentage
• wa: I/O wait time
• hi/si: Hardware/software interrupts
• st: Virtualization steal time

Common MySQL CPU Scenarios

Inefficient SQL Queries

Problematic states in SHOW PROCESSLIST:

• Sending data: Involves disk reads and data processing
• Copying to tmp table: In-memory temporary table operations
• Copying to tmp table on disk: Disk-based temporary tables
• Sorting result: Large result set ordering

Optimization strategies:

• Implement proper indexign
• Avoid implicit conversions and function calls
• Tune buffer sizes: join_buffer_size, sort_buffer_size
• Limit concurrency: innodb_thread_concurrency

IO-Related CPU Spikes

IO operations impact CPU:

• Synchronous reads block user threads
• Asynchronous writes occur during log rotations and checkpoints
• High wa state indicates IO bottlenecks

Configuration improvements:

• Adjust innodb_flush_log_at_trx_commit and sync_binlog
• Increase innodb_io_capacity
• Use high IOPS storage

Diagnosing CPU Issues

Identify Process CPU Affinity

mysql> SELECT thread_id, connection_id, user, process_id 
       FROM sys.processlist 
       WHERE connection_id > 0 AND process_id > 0;

$ ps -o pid,psr,comm -p 3247
   PID PSR COMMAND
  3247   3 mysqld

Analyze High CPU Threads

$ top -H -p 1821
   PID USER      %CPU  COMMAND                                                                                               
  1942 mysql     90.0  mysqld

Correlate Threads with SQL

SELECT a.thread_os_id, b.id, b.command, b.state, b.query
FROM performance_schema.threads a
JOIN information_schema.processlist b 
  ON b.id = a.processlist_id
WHERE a.thread_os_id = 1942;

Optimization Approaches

• Use 8+ core CPUs and SSDs
• Implement proper indexing strategies
• Enable query caching with Redis/Memcached
• Configure disk scheduler and file systems
• Adjust innodb_buffer_pool_instances
• Disable deadlock detection (innodb_deadlock_detect) when appropriate