High server load caused by mysql

High server load caused by mysql

July 4, 2026 · Illustrated author avatarBy Stefan Bogdanescu
High server load caused by mysql

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_size is set to 64MB, which might not be sufficient for the client’s workload.
  • The max_allowed_packet size 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:

  1. Insufficient configuration settings for the client’s workload.
  2. Inefficient query execution due to improper indexing and caching.
  3. 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.

Magento 1 Cache Database Performance

Our network