MySQL is the most popular database used in web applications. It is supported by all hosting providers, is easy to administer, and free. However, MySQL servers often face high server load due to high memory usage.

What causes MySQL high memory usage?

Before we discuss how to fix MySQL high memory usage errors, let’s take a look at what causes such situations.

1. Application software such as PHP, Python, Java threads taking up more memory due to improper configuration, unoptimized queries, complex coding, etc.
2. Improper MySQL server configuration causing inefficient allocation or wastage of memory.
3. Low memory availability in the server than required by the processes

Fixing MySQL high memory usage

MySQL High Memory usage

Depending on what’s causing the memory bottleneck, the solution can vary. Here are the top resolutions for MySQL high memory usage.

1. MySQL settings optimization

MySQL uses memory in two ways:

1. Memory permanently reserved for its use – This category of memory known as “Global Buffers” is obtained from the operating system during server boot-up and is not released to any other process.
2. Memory which is requested dynamically based on requests – MySQL uses “Thread Buffers“, which is memory requested from the operating system as and when a new query is processed. Once the query is executed, this memory is released back to the operating system.

So, you can say the memory usage of MySQL is “Global Buffers + (Thread Buffers x maximum number of allowed connections)”.

This value should always be kept below 90% of server memory for a dedicated database server. If it is a shared server, it should be kept below 50%. Here are a few of the common MySQL settings that determines memory usage, and how you can decide it’s size.

• innodb_buffer_pool_size – For InnoDB storage engine, caching is done in the buffer pool. The size of the buffer pool is important for system performance and is assigned a value that is between 50-70% of available RAM. Too small pool size can cause excessive flushing of pages and too large size can cause swapping due to competition for memory.
• key_buffer_size – For MyISAM storage engine, this parameter determines the caching and key_buffer_size has to be set according to the RAM, which is around 20% of RAM size.
• max_connections – To limit the number of connections possible for MySQL at any instant of time, to avoid a single user from overloading the server, max_connections is used. Each thread uses a portion of the RAM for allotting its buffers and hence limiting the maximum no of connections based on the RAM size is important.
  • Approx formula, max_connections = (Available RAM – Global Buffers) / Thread Buffers
• query_cache_size – The query cache can be useful in an environment where you have tables that do not change very often and for which the web server receives many identical queries such as a blog or record lookup. The query cache stores the text of a SELECT statement together with the corresponding result that was sent to the client. So this parameter is used only for such application servers or otherwise disabled and set to 0 for other servers. To avoid resource contention, even if it is enabled, the value is set a minimal one of around 10MB.

2. Block resource abusers

When a website is under attack (like DoS, comment spamming, etc.), an abnormally high number of connections could be established in a short time. Use the “PROCESSLIST” in MySQL to identify the top users, and block access to the abusive connections.

[ Use your time to build your business. We’ll take care of your servers. Grab our Emergency server services at affordable pricing. ]

3. Fix slow queries

Identify queries that take a long time to execute – these queries require further optimization for better server performance and are identified from the slow-query log. Slow queries would cause more disk reads which require more memory and CPU usage, which in turn affects the server performance.

[ You don’t have to lose your sleep over high memory usage. Our expert server specialists are here to keep your MySQL server stable. ]

4. Upgrade RAM

If the server constantly resorts to using swap memory, even after optimizing the database settings, you might need to increase the server RAM.

Bobcares helps business websites of all sizes achieve world-class performance and uptime, using tried and tested website architectures. If you’d like to know how to make your website more reliable, we’d be happy to talk to you.