When users experience memory usage issues with any software, including MySQL®, their first response is to think that it’s a symptom of a memory leak. As this story will show, this is not always the case.
This story is about a bug.
All Percona Support customers are eligible for bug fixes, but their options vary. For example, Advanced+ customers are offered a HotFix build prior to the public release of software with the patch. Premium customers do not even have to use Percona software: we may port our patches to upstream for them. But for Percona products all Support levels have the right to have a fix.
Even so, this does not mean we will fix every unexpected behavior, even if we accept that behavior to be a valid bug. One of the reasons for such a decision might be that while the behavior is clearly wrong for Percona products, this is still a feature request.
A bug as a case study
A good recent example of such a case is PS-5312 – the bug is repeatable with upstream and reported at bugs.mysql.com/95065
This reports a situation whereby access to InnoDB fulltext indexes leads to growth in memory usage. It starts when someone queries a fulltext index, grows until a maximum, and is not freed for quite a long time.
Yura Sorokin from the Percona Engineering Team investigated if this is a memory leak and found that it is not.
When InnoDB resolves a fulltext query, it creates a memory heap in the function fts_query_phrase_search This heap may grow up to 80MB. Additionally, it has a big number of blocks ( mem_block_t ) which are not always used continuously and this, in turn, leads to memory fragmentation.
In the function exit , the memory heap is freed. InnoDB does this for each of the allocated blocks. At the end of the function, it calls free() which belongs to one of the memory allocator libraries, such as malloc or jemalloc. From the mysqld point of view, everything is done correctly: there is no memory leak.
However while free() should release memory when called, it is not required to return it back to the operating system. If the memory allocator decides that the same memory blocks will be required soon, it may still keep them for the mysqld process. This explains why you might see that mysqld still uses a lot of memory after the job is finished and all de-allocations are done.
This in practice is not a big issue and should not cause any harm. But if you need the memory to be returned to the operating system quicker, you could try alternative memory allocators, such as jemalloc. The latter was proven to solve the issue with PS-5312.
Another factor which improves memory management is the number of CPU cores: the more we used for the test, the faster the memory was returned to the operating system. This, probably, can be explained by the fact that if you have multiple CPUs, then the memory allocator can dedicate one of them just for releasing memory to the operating system.
The very first implementation of InnoDB full text indexes introduced this flaw. As our engineer Yura Sorokin found:
- The very first 5.6 commit which introduces Full Text Search Functionality for InnoDB WL#5538: InnoDB Full-Text Search Support – https://dev.mysql.com/worklog/task/?id=5538
- Implement WL #5538 InnoDB Full-Text Search Support, merge – https://github.com/mysql/mysql-server/commit/b6169e2d944 – also has this problem.
Options to fix
We have a few options to fix this:
- Change implementation of InnoDB fulltext index
- Use custom memory library like jemalloc
Both have their advantages and disadvantages.
Option 1 means we are introducing an incompatibility with upstream, which may lead to strange bugs in future versions. This also means a full rewrite of the InnoDB fulltext code which is always risky in GA versions, used by our customers.
Option 2 means we may hit flaws in the jemalloc library which is designed for performance and not for the safest memory allocation.
So we have to choose between these two not ideal solutions.
Since option 1 may lead to a situation when Percona Server will be incompatible with upstream, we prefer option 2 and look forward for the upstream fix of this bug.
Conclusion
If you are seeing a high memory usage by the mysqld process, it is not always a symptom of a memory leak. You can use memory instrumentation in Performance Schema to find out how allocated memory is used. Try alternative memory libraries for better processing of allocations and freeing of memory. Search the user manual for LD_PRELOAD to find out how to set it up at these pages here and here.
Hi Sveta,
I have bumped into a similar issue over the last few months that is very similar if not the same.
The related bug: https://bugs.mysql.com/94647 also reported as not a bug (https://jira.mariadb.org/browse/MDEV-14050).
In my case a server upgrading from MariaDB 10.1 to 10.3 suddenly started exhibiting higher memory usage, triggering OOM conditions. Changing the memory allocator resolved the issue. (I used tcmalloc.)
What somewhat surprised me was this happened on the same underlying OS so clearly the access patterns to glibc’s malloc() must have changed to trigger this. (as queries did not change and 10.1 had been working fine). The symptoms were recognised as being true, but the “problem was the memory allocator” so “not an issue”.
Oracle MySQL, Percona Server or MariaDB users want to use as much memory _as possible_ on their systems. They want that memory usage to be stable, and possibly capped at a value to prevent possible out of memory issues, as the consequences of not doing that lead to OOM situations or to the user having to under-utilise memory to protect his system from dying unexpectedly.
In the specific bug I mention there’s a reference to try malloc_trim() which would free memory as the brief look I made of the malloc code indicated that free() is lazy (by design). That’s fine for many apps but not for memory hungry applications like a database server, and why this wasn’t tickled by older versions of MySQL or MariaDB I’m not sure.
If we accept that we have to use a memory allocator to prevent such issues then basically we are saying that glibc is broken for database usage. Having to manually configure the server additionally to use such “custom” memory allocators is more work and effort and entirely unhelpful, and it seems that maybe MySQL or MariaDB should be shipped with a pre-built alternative allocator “which works”, while still allowing people to override that if they so choose.
I haven’t had time to try to see if “appropriate usage of malloc_trim()” would resolve the issue, as perhaps that is something that might be worth considering. However, it has not happened yet “as there’s no bug…”, and I find that disappointing.
Coming back to InnoDB, people think that memory usage is managed by the size of the buffer pool. Clearly you show that’s not the case as InnoDB has other memory regions which are not part of the buffer pool which can change in size and that change could be significant. So I would really like to see all InnoDB memory usage managed together, with a size limit, so that the issues you describe and other similar ones I have seen would also be controlled.
Longer term I guess it makes sense to have a single allocator, usable by MySQL/MariaDB, with a configurable memory cap, and even external plugins and storage engines should use that. Then memory sizing will be under control.
That clearly then brings up all the problems of what to do under memory pressure but that’s something I think that when you have lots of cache that the system should be able to handle, and free “less important” areas to make space, or simply to report an error “insufficient memory to complete request” or equivalent. Reporting an error is betting than the server being killed by the kernel or systemd.
Until we do that we’ll bump into these issues and they’ll be frustrating and take up a lot of our time: users, people in support, the original upstream developers checking their code and their managers figuring out if there’s a “bug” and what to do about it.
I do hope that progress will be made in managing total memory usage, and think that doing that in InnoDB, where most memory is already used, seems like a good starting point. Extending that to the server as a whole would make all of our lives easier and enable us most efficiently use the memory we want to use in MySQL for caching data and providing the best performance possible.
Filing these things as “not a bug” may be true technically, but doing so doesn’t really answer the underlying problem the user is experiencing and wants to resolve.
jemalloc did not fix my issue.
please suggest option 1 to Oracle
thanks
@james We reported a bug at https://bugs.mysql.com/bug.php?id=95065 This is now up to Oracle how to fix it.