Managing High CPU Usage in MySQL/MariaDB: A Systematic Approach

In today’s data-driven world, database performance is often the difference between a smooth user experience and a frustrating one. MySQL and its fork MariaDB remain industry powerhouses, running millions of applications worldwide. However, as your applications scale, you may encounter the all-too-common issue of excessive CPU consumption, which can throttle performance and impact availability.

This article presents a systematic, evidence-based approach to diagnose and resolve CPU bottlenecks in MySQL/MariaDB environments. We’ll explore not just what parameters to tweak, but why they matter and how they interact with your workload.

The Warning Signs: Identifying CPU Problems in Your Database

Before diving into optimizations, it’s important to recognize when your MySQL/MariaDB instance is suffering from CPU-related issues:

• Query response times suddenly increasing from milliseconds to seconds
• The database server becoming unresponsive during traffic spikes
• MySQL processes consistently showing high CPU utilization in monitoring tools
• Unexpected latency increases despite adequate memory and disk I/O
• System load averages climbing disproportionately to user traffic

While these symptoms might seem straightforward, the underlying causes are often complex and interconnected.

Diagnostic Toolkit: Gathering Evidence for Optimization

Effective database optimization is a data-driven discipline. Here are the essential tools to gather meaningful performance metrics:

1. MySQLTuner

MySQLTuner provides a comprehensive snapshot of your database’s health and offers targeted recommendations:

mysqltuner --user admin --pass [password]

The output includes critical metrics like buffer utilization, cache efficiency, and specific performance bottlenecks:

[!!] Temporary tables created on disk: 39% (11K on disk / 28K total)
[!!] Query cache prunes per day: 1,995,304
[!!] Maximum possible memory usage: 17.1G (88.00% of installed RAM)

2. Performance Schema

MySQL’s Performance Schema provides deeper insights into server operations:

-- Find your most CPU-intensive queries
SELECT DIGEST_TEXT, COUNT_STAR, SUM_TIMER_WAIT/1000000000000 as seconds,
       SUM_TIMER_WAIT/COUNT_STAR/1000000000 as avg_ms
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

-- Check for table scans
SELECT * FROM sys.statements_with_full_table_scans
ORDER BY total_latency DESC
LIMIT 10;

3. Slow Query Log Analysis

Enable and analyze the slow query log to identify problematic queries:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1; -- Log queries taking over 1 second
SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';

Process this data with tools like pt-query-digest from Percona Toolkit:

pt-query-digest /var/log/mysql/mysql-slow.log

Root Causes: Why MySQL Consumes Excessive CPU

Based on diagnostic data, several common patterns emerge as primary CPU consumers:

1. Disk-Based Temporary Tables

When MySQL can’t fit temporary tables in memory, it writes them to disk, causing significant CPU overhead:

[!!] Temporary tables created on disk: 39% (11K on disk / 28K total)

The issue often stems from complex queries with:

• GROUP BY or ORDER BY on non-indexed columns
• Queries with DISTINCT operations
• JOINs requiring sorting or grouping
• Insufficient tmp_table_size and max_heap_table_size settings

2. Query Cache Thrashing

The query cache can be either a performance booster or a CPU bottleneck:

[!!] Query cache prunes per day: 1,995,304

High prune rates indicate the cache is constantly evicting entries to make room for new ones—an expensive operation that locks the query cache mutex and increases CPU utilization.

3. Memory Overcommitment

Excessive memory allocation can lead to swapping and CPU thrashing:

[!!] Maximum possible memory usage: 17.1G (88.00% of installed RAM)

This typically results from:

• Oversized global buffers (like innodb_buffer_pool)
• Large per-connection buffers with high max_connections
• Insufficient headroom for OS and other applications

4. Suboptimal Query Patterns

Even with proper server configuration, inefficient queries can dominate CPU resources:

[!!] Reduce your SELECT DISTINCT queries which have no LIMIT clause

Common culprits include:

• Missing indexes or using the wrong indexes
• SELECT * instead of specific columns
• Unbound DISTINCT operations
• Complex JOINs without proper indexing

Strategic Optimizations: Reducing CPU Load

With a clear understanding of the root causes, we can implement targeted optimizations:

1. Temporary Table Optimization

To reduce disk-based temporary tables and their associated CPU cost:

# Increase memory allocated for temporary tables
tmp_table_size = 128M                    # Previous: 64M
max_heap_table_size = 128M               # Previous: 64M

For workloads with complex queries, consider further increases, but monitor memory usage carefully.

Additionally, analyze and optimize queries that generate temporary tables:

-- Find queries generating temporary tables
SELECT DIGEST_TEXT, SUM_CREATED_TMP_DISK_TABLES
FROM performance_schema.events_statements_summary_by_digest
WHERE SUM_CREATED_TMP_DISK_TABLES > 0
ORDER BY SUM_CREATED_TMP_DISK_TABLES DESC
LIMIT 10;

2. Query Cache Strategy

For MySQL 5.7 and earlier or MariaDB environments, you have two options:

Option A: Optimize Existing Cache

query_cache_type = 1
query_cache_size = 256M                  # Increased from 128M
query_cache_limit = 2M                   # Reduced to avoid caching large results
query_cache_min_res_unit = 2K            # Optimized for smaller result sets

Option B: Disable Query Cache (Recommended for newer versions)

query_cache_type = 0
query_cache_size = 0

Note: MySQL 8.0 has removed the query cache entirely, as its mutex-based design was identified as a frequent bottleneck in high-concurrency environments.

3. Per-Connection Buffer Tuning

Rightsizing per-connection buffers has a multiplier effect on memory allocation:

# Reduce per-connection buffers
join_buffer_size = 256K                  # Previous: 512K
sort_buffer_size = 1M                    # Previous: 2M
read_buffer_size = 1M                    # Previous: 2M
read_rnd_buffer_size = 2M                # Previous: 4M

# Adjust maximum connections based on actual needs
max_connections = 500                    # Previous: 1000

Use performance monitoring to determine the actual number of concurrent connections:

SHOW STATUS LIKE 'Max_used_connections';

4. InnoDB-Specific Optimizations

InnoDB offers several CPU-focused optimizations:

# Core InnoDB settings
innodb_buffer_pool_size = 8G             # Sized to contain working dataset
innodb_buffer_pool_instances = 8         # 1 per GB of buffer pool

# CPU optimization options
innodb_adaptive_hash_index = OFF         # Disable if CPU usage remains high
innodb_purge_threads = 4                 # Dedicated threads for purge operations
innodb_lru_scan_depth = 100              # Reduced from default 1024
innodb_io_capacity = 1000                # Set appropriate for SSD
innodb_io_capacity_max = 2000            # Maximum I/O capacity

The adaptive hash index can be particularly CPU-intensive on write-heavy workloads. Monitor before and after disabling to confirm improvement.

5. Query Optimization

The most effective long-term strategy is optimizing problematic queries:

1. Identify CPU-intensive queries using Performance Schema or slow query log
2. Analyze execution plans with EXPLAIN and EXPLAIN ANALYZE
3. Add appropriate indexes targeting filter and join conditions
4. Rewrite inefficient patterns like unbounded SELECT DISTINCT operations

-- Before vs. After optimization example:

-- Before: Unbounded DISTINCT with multiple columns
SELECT DISTINCT user_id, product_id, order_date 
FROM orders 
WHERE status = 'completed';

-- After: More efficient GROUP BY with limit
SELECT user_id, product_id, order_date 
FROM orders 
WHERE status = 'completed'
GROUP BY user_id, product_id, order_date
LIMIT 10000;

-- Add targeted indexes for the query
CREATE INDEX idx_orders_status_user_product_date ON orders 
(status, user_id, product_id, order_date);

Case Study: E-commerce Platform Database

Let’s examine a real-world scenario: a MariaDB database supporting multiple e-commerce websites experiencing CPU saturation during peak hours. MySQLTuner analysis revealed:

• 39% of temporary tables created on disk
• ~2 million query cache prunes daily
• 88% of system RAM potentially allocated to MySQL
• Multiple unbounded SELECT DISTINCT operations

We implemented the following changes:

1. Increased tmp_table_size and max_heap_table_size to 128M
2. Reconfigured query cache settings
3. Reduced per-connection buffers and max_connections
4. Adjusted InnoDB-specific parameters
5. Optimized the top 10 CPU-intensive queries

Results after optimization:

• CPU utilization decreased from ~90% to ~40% during peak hours
• Disk-based temporary tables reduced from 39% to 12%
• Average query response time improved by 60%
• Server capacity increased by approximately 2.5x

Beyond Configuration: Architectural Considerations

While configuration tuning can yield significant improvements, consider these broader strategies:

Read/Write Splitting

For read-heavy applications, implement replication with dedicated read replicas:

Primary (Writes) → Replicas (Reads)

This distributes CPU load across multiple servers and allows for specialized optimization of read vs. write workloads.

Database Sharding

For very large datasets, consider horizontal sharding:

Users A-M → Database Server 1
Users N-Z → Database Server 2

This reduces the working dataset size per server and distributes CPU load across the cluster.

Caching Layers

Implement application-level caching with Redis or Memcached to reduce database load:

Application → Cache → Database

This approach is particularly effective for read-heavy workloads with predictable access patterns.

Security and Performance

Secure configurations often have performance benefits:

-- Restrict hosts instead of using wildcard '%'
RENAME USER 'app_user'@'%' TO 'app_user'@'10.0.1.%';

This reduces authentication overhead and helps prevent unauthorized access attempts.

Continuous Monitoring and Optimization

Database optimization is an ongoing process:

1. Implement comprehensive monitoring with tools like:
   • Prometheus + Grafana
   • Percona Monitoring and Management (PMM)
   • DataDog or New Relic
2. Create dashboards focusing on key metrics:
   • CPU utilization (per thread)
   • Temporary tables created on disk
   • Query response time distribution
   • Buffer pool efficiency
   • Lock wait events
3. Establish performance baselines and alerts for deviations
4. Regularly review slow query logs and access patterns as your application evolves

Conclusion

MySQL/MariaDB CPU optimization requires a methodical approach:

1. Gather performance metrics using tools like MySQLTuner and Performance Schema
2. Identify specific bottlenecks such as disk-based temporary tables or query cache thrashing
3. Implement targeted configuration changes
4. Optimize problematic queries with appropriate indexing and rewrites
5. Consider architectural changes for scalability
6. Monitor continuously and adjust as workload patterns evolve

The most effective optimizations come from understanding the relationship between your database’s workload characteristics and MySQL’s internal mechanisms. By applying these principles, you can transform an overloaded database server into an efficient, scalable system capable of handling growing workloads without proportional resource increases.

Remember that there’s no “one-size-fits-all” configuration—the optimal setup depends on your specific workload, hardware, and application requirements. The key is methodical analysis, targeted adjustments, and continuous measurement of results.

Additional Resources

• MySQL Performance Schema Documentation
• MariaDB Performance Tuning Guide
• High Performance MySQL, 4th Edition (O’Reilly)
• Percona Database Performance Blog
• Facebook’s MyRocks Storage Engine (for write-intensive workloads)
• ProxySQL for Query Routing and Caching