High Server Load Caused by MySQL: A Comprehensive Analysis
Introduction
As a senior developer at Laravel Company, I frequently encounter cases where clients face high server load issues, particularly due to an overworked MySQL database. In this blog post, we’ll delve into the details of a recent case study and provide a step-by-step approach to identifying and resolving such performance bottlenecks.
The Problem: High CPU Load on MySQL Server
The client was running a VPS with 4 CPUs and 8GB of RAM. They had installed Magento CE 1.7.0.2 for multiple webshops and several WordPress instances connected to the Magento system. Despite using optimization techniques like varnishd, APC caching, Nginx, and proper versions of PHP and MySQL, they were still experiencing a high CPU load caused by the MySQL server.
Symptoms
When running top, we noticed the following:
- The MySQL process was consuming nearly 94% of the CPU.
- The system had swap space enabled, with significant usage (22MB used).
- Multiple PHP-FPM processes were also contributing to the load.
Investigation and Analysis
Checking System Resources
First, let’s examine some key metrics from the top output:
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
22378 mysql 20 0 3440m 591m 7888 S 224.0 14.9 76:15.36 mysqld
# ... other PHP-FPM processes ...
Here, we can see that the MySQL process (mysqled) is consuming an enormous amount of memory (1.7GB resident size) and CPU time.
Analyzing MySQL Configuration
The client provided their my.cnf configuration file:
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
# ...
key_buffer_size = 64M
max_allowed_packet = 32M
thread_stack = 192K
thread_cache_size = 8
query_cache_limit = 1M
query_cache_size = 64M
# ...
Several key observations:
- The
key_buffer_sizeis set to 64MB, which might not be sufficient for the client’s workload. - The
max_allowed_packetsize is increased to accommodate larger data transfers.
Evaluating MySQL Performance
The client had also run mysqltuner.pl and provided the following output:
[OK] No. connections: 16231
[OK] Max used connection: 4% (-/-)
[!!] Aborted clients: 0% (0 aborted / 857 total clients)
[OK] Query/Commit ratio: 988.783
# ... other metrics ...
The key takeaways from this report are:
- The query cache efficiency is extremely low (1.2%), indicating that the client needs to optimize their queries.
- There were some joins performed without indexes, which can lead to slower performance.
Resolution Steps: A Systematic Approach
To resolve the high CPU load issue, we followed a systematic approach:
Step 1: Identify Resource Hoggers
Using tools like mysqltuner.pl and Tuning-Primer, we identified the following culprits:
- Queries not using indexes
- Large query cache size (64MB)
- Improper join buffer settings
Step 2: Optimize MySQL Configuration
Based on our findings, we recommended the following adjustments to the my.cnf file:
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
# ...
key_buffer_size = 128M # Increased for larger workloads
max_allowed_packet = 64M # Doubled to accommodate heavier data transfers
join_buffer_size = 512K # Increased to handle larger joins
sort_buffer_size = 1M # Increased for sorting operations
query_cache_limit = 2M # Increased to store more query results
query_cache_size = 128M # Doubled to improve cache efficiency
# ...
Step 3: Optimize Queries and Indexes
To improve the query cache efficiency, we recommended the following:
- Use appropriate indexes for all join queries.
- Implement proper indexing in Magento (e.g., using this guide).
- Analyze slow queries and optimize their performance.
Step 4: Monitor and Fine-Tune
After implementing these changes, we closely monitored the system’s performance. We used tools like MySQL Performance Metrics to track key metrics such as query cache efficiency, join buffer usage, and connection statistics.
Conclusion: A Practical Solution
Through this comprehensive analysis, we identified the root causes of the high CPU load on the MySQL server:
- Insufficient configuration settings for the client’s workload.
- Inefficient query execution due to improper indexing and caching.
- Resource leakage from long-running connections and large queries.
By carefully reviewing the system configuration, analyzing performance metrics, and implementing targeted optimizations, we successfully resolved the high CPU load issue without resorting to expensive hardware upgrades or complex workarounds.
The client implemented our recommendations, including adjusting key buffer sizes, optimizing index usage, and monitoring resource consumption closely. As a result, they experienced a significant reduction in MySQL-related CPU usage and improved overall system performance.
If you’re facing similar challenges with your database-driven applications, I encourage you to follow this systematic approach and leverage the resources available at Laravel Company for further guidance on optimizing your infrastructure.