What Could Cause High CPU Usage of the MySQL Service on an Ubuntu LEMP Stack and What to Do First?

1. Insufficient MySQL Configuration

  • Inadequate buffer sizes: For example, small query_cache_size, innodb_buffer_pool_size, or key_buffer_size.

  • Table locks: Contention for table locks due to high write operations or unoptimized operations.

  • Thread management: A misconfigured thread_cache_size can lead to thread bottlenecks.

2. Missing Query Caching

  • If query caching is disabled or too small, recurring queries are sent to the database repeatedly, increasing CPU load.

3. High Read/Write Workload

  • A high number of read operations (even with a read-intensive setup like yours) or write operations that bypass caching layers can increase the load.

4. Disk I/O Bottlenecks

  • Slow disks can lead to high CPU load as MySQL waits for disk operations, especially with large temporary tables or SELECT operations without appropriate indexes.

5. Connection Pool Issues

  • Excessive or inefficient connection handling, such as repeatedly opening and closing connections instead of using a connection pool.

6. Background Operations

  • Tasks such as replication, binary log writing, or backup operations can heavily strain the CPU.

7. Corrupted Tables or Indexes

  • Corrupted tables or indexes can lead to repeated failed attempts at query execution, causing high resource consumption.

8. Insufficient Server Resources

  • Overloaded CPU due to competing processes on the same server or under-provisioned hardware.

9. External Attacks or Abuse

  • Brute-force or SQL injection attacks: External abuse directly targeting your database.

  • Bots or crawlers: Excessive requests to the website leading to high MySQL query loads.

Diagnostic Steps

  • Check slow queries: Use the slow_query_log to identify problematic queries.

  • Monitor MySQL performance: Use tools like mysqladmin, MySQL Workbench, or Performance Schema.

  • Examine server metrics: Use htop, top, or iotop to identify bottlenecks.

  • Enable query profiling: Run EXPLAIN on your queries to analyze execution plans.

Solutions

  • Optimize queries and indexes.

  • Tune the MySQL configuration with a script like mysqltuner.pl.

  • Offload read-heavy workloads to a caching layer (e.g., Redis, Memcached).

  • Increase hardware resources (CPU, RAM) if needed.

  • Use connection pooling for PHP (e.g., persistent connections in PDO or a pool like ProxySQL).

innodb_buffer_pool_size - Usage

innodb_buffer_pool_size can be the most important variable for any MySQL database using InnoDB tables. Configuring innodb_buffer_pool_size is strongly recommended to reduce excessive I/O usage. I/O usage is the primary bottleneck for MySQL servers, as it is slow and high usage indicates that the disk subsystems are overloaded. This can significantly impact performance during peak times and maintenance operations.

The InnoDB buffer pool works by caching the most recently accessed data. By caching data in memory, it can be retrieved from the cache instead of from disk. The buffer pool is designed to keep the most frequently used data available in cache by managing two sublists. When new data is accessed, it is stored at the top of the "oldest" list. The oldest item in this list is removed and must be fetched from disk again when re-queried. When data is accessed again, it is moved to the top of the "newest" list.

When the buffer pool is properly configured, frequently accessed data can be quickly retrieved from memory as needed.

As a general principle, innodb_buffer_pool_size should be set to the size of the database.

Is your MySQL database the bottleneck? Contact me for a performance analysis and optimization consultation.