Stored Procedures memory consumption in Percona Server for MySQL

1. What it is about

This investigation began as a performance comparison for different memory allocators. However, during benchmarking, I discovered unexpected effects deserving a more detailed explanation. I hope you find these findings both interesting and useful.

Imagine you need to set up a MySQL database server. Every detail is planned: the operating system, the CPU architecture, the number of cores, the amount of RAM, the storage capacity and speed. On paper the hardware looks like it can handle the workload. But in reality, things rarely go exactly as planned. So, conducting a thorough stress test is the next thing to do.

2. Realities of stress testing

You configure your MySQL server setting the innodb_buffer_pool_size to 70-80% of your available RAM. This creates a large fast buffer for your data and indexes, reducing the need for slower disk input/output.

After a warmup period and a few hours of testing, everything looks great. The server is working at a steady pace, performance is stable. You tick the box – the server has passed the basic stress test. Thinking everything is fine, you consider leaving the test running over the weekend, expecting only minor fluctuations in performance.

However, when you check the status the next morning, you find that the CPU is idle and the mysqld process has vanished. Did it crash? You check the server error logs, but there is no record of a crash or a shutdown—not even a core dump. Then, you look at the system logs and find something unexpected:

It appears that mysqld ran out of memory and was terminated by the OOM (Out of Memory) killer after running for about 16 hours.

We will focus on Resident Set Size (RSS), which is the subset of Virtual Memory Size (VSZ). RSS is the most significant part of VSZ and other parts like swap (only 8Gb) do not make notable contributions.

The RSS reached 183GiB, significantly higher than the initial 145GiB (with the innodb_buffer_pool_size set to 135G). The mysqld process had grabbed nearly 40GiB of extra memory, which at first looked like a memory leak. I ran my stress tests on different versions of MySQL and Percona Server and found a recurring pattern: memory usage climbed steadily until the system killed the process.

I won’t dive into the leak diagnosis here, but the result was clear: mysqld wasn’t leaking memory in the traditional sense. However, we still had to explain that 40GiB growth.

3. Configuration and methodology

The configuration was as follows:

The testing was done as follows:

What we wanted to achieve:

• Create conditions when memory allocations and deallocations inside the database server are frequent.
• Utilize as much of physical memory as possible (at least 80%) by giving it to InnoDB Buffer Pool.
• Use all available CPU resources in the most efficient way to prevent threads contesting for execution time (the number of connections should match the number of logical CPU cores).
• Eliminate any layers that add overhead and get in the way of direct measuring of allocators frequency and efficiency. The connections will be established using a socket file.

Servers configuration file:

4. Where did the memory go?

Memory management is complex, so let’s simplify. Applications rarely talk directly to the Linux kernel because the kernel typically works in 4KB pages, which is inefficient for developers. Instead, applications use allocators like glibc malloc, jemalloc, or tcmalloc. These tools handle memory operations by minimizing overhead, managing bookkeeping, and preventing fragmentation. Most importantly, they use caching.

When a program frees memory, the allocator rarely returns it to the OS immediately. Instead, it moves that memory into an internal “free-list” cache. Reusing memory from this cache is much faster than requesting new memory from the kernel.

Also, the Percona Server for MySQL and upstream MySQL Server use their own implementation of the memory arena allocator called MEM_ROOT. Historically MEM_ROT was architected decades ago when the standard Linux implementation of glibc memory allocator was slow and prone to lock contention in multithreaded programs.

Enabling memory profiling revealed that MEM_ROOT allocations for cursor metadata in stored routines was responsible for most of the additional memory acquired by the server process:

NOTE: 87G is a significant growth of memory allocation considering that in that run the server initially allocated ~85G with Innodb_buffer_pool_size=80G.

The problem happens regardless of the data size because the actual issue is in stored routines cursor metadata. When the stored procedure is called the memory allocated for cursor metadata is not freed. Over the course of many repeated calls to the same stored procedure the cumulative amount of memory for the cursor can reach any value.

The following graph demonstrates the memory growth in Percona Server 8.4.8-8 from ~80G to over ~180G in RSS and over 200G VSZ over the period of 24 hours.

Thus, a bug was reported for Percona Server: https://perconadev.atlassian.net/browse/PS-11472

With Percona Server for MySQL 9.7.0-1 the RSS/VSZ growth was at a slower rate, but still noticeable and it was not flattening towards a stable horizontal line (the server was configured with a small amount of memory for innodb_buffer_pool_size=4G and run for 5 hours instead of 20).

Memory profiling showed the new allocations in version 9.7.0-1 were in the same place where cursor metadata is handled:

5. Possible workarounds

My tests showed that OOM crashes happened consistently under two specific conditions:,

1. Connections are never closed and stay open permanently
2. Connections ran queries at maximum speed without any pauses

Also, when the connection lifetime was limited and users were made to close connection and reconnect after 1M transactions, the memory exhaustion stopped, and memory was freed correctly – all with only a minor impact on performance. To minimize the delays associated with creating a new connection thread on the server I used the connection pool functionality in HammerDB. When the connection lifetime is ended, the actual connection is not closed, but “reset” and reused. This frees the context accumulated during the connection activity and stimulates returning memory to the OS. This connection pool mechanism is more efficient than the open/close cycle for maintaining the connection lifetime. 

I had two runs with reconnecting users: with and without connection pool. The graph demonstrates that using the pool improves the performance in this test.

Also, during another experiment with unlimited connection lifetime, adding a 0.5ms pause after a few transactions prevented the crashes, though performance dropped slightly more.

The memory graphs have consistent periodic oscillations that never reach into the dangerous zone.

6. Summary

To sum it up: the observed MySQL’s memory bloating is caused by a problem in the server cursor implementation not freeing metadata memory. 

Under heavy, constant load, that memory accumulates to the amount which eventually causes an OOM crash. Capping how long connections stay active or adding a short pause between transactions, gives the server time to clean itself up. Normally the client side processing adds such pauses without need to do it on purpose.

Finally, it is important to remember that the best benchmark results do not always guarantee the best real-life performance.