May
02
2019
--

MySQL Memory Management, Memory Allocators and Operating System

memory management mysql bug

memory management mysql bugWhen 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:

Options to fix

We have a few options to fix this:

  1. Change implementation of InnoDB fulltext index
  2. 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.

Jan
03
2017
--

Enabling and Disabling Jemalloc on Percona Server

Jemalloc on Percona Server

Jemalloc on Percona ServerThis post discusses enabling and disabling jemalloc on Percona Server for MySQL.

The benefits of jemalloc versus glibc for use with MySQL have been widely discussed. With jemalloc (along with Transparent Huge Pages disabled) you have less memory fragmentation, and thus more efficient resource management of the available server memory.

For standard installations of Percona Server 5.6+ (releases starting with 5.6.19-67.0), the only thing needed to use jemalloc as the memory library for mysqld is for it to be installed on the server.

Enabling Jemalloc on Percona Server

First thing first: install Jemalloc.

The library is available on the Percona repository, which is available for both apt and yum package management:

Once you have the repo, just run the install command (according to your OS) to install it:

yum install jemalloc / apt-get install jemalloc

Now that you have the jemalloc package installed, all it takes to start using it is…..

  • Restart the server.

That’s it! No modifications needed on the my.cnf file or anywhere else. Plain and simple!

Disabling Jemalloc on Percona Server

If for any reason you need to disable jemalloc and go back to the default library, you have two options: remove the jemalloc package (not too practical), or add the following line to the [mysqld_safe] section of the my.cnf file:

malloc-lib =

In other words, an empty path. That will do the trick. Note that commenting or removing the “malloc-lib” parameter on the cnf file won’t work.

How to Know if Jemalloc is Being Used?

There are couple of ways you can verify this, but the less invasive way is by using the pt-mysql-summary (version 2.2.20 and higher) tool from the Percona Toolkit:

root@reports:~# pt-mysql-summary | grep -A5 -i "memory management"
# Memory management library ##################################
jemalloc enabled in MySQL config for process with ID 5122
Using jemalloc from /usr/lib/x86_64-linux-gnu/libjemalloc.so.1
# The End ####################################################

Apr
15
2013
--

Memory allocators: MySQL performance improvements in Percona Server 5.5.30-30.2

In addition to the problem with trx_list scan we discussed in Friday’s post, there is another issue in InnoDB transaction processing that notably affects MySQL performance – for every transaction InnoDB creates a read view and allocates memory for this structure from heap. The problem is that the heap for that allocation is destroyed on each commit and thus the read view memory is reallocated on the next transaction.

There are two aspects of this problem:

1) memory allocation is an costly operation and if memory allocator has scalability problems (like allocator from glibc) this will notably slowdown MySQL-transaction creation and many threads will get stuck on glibc/kernel syscalls, which will in turn result in contention on kernel_mutex (trx_sys->mutex in 5.6), as memory allocation occurs under that mutex. See an example of such an issue. Related bugs: BUG#54982, BUG#49169.

2) memory allocation for read-view structure is not a direct malloc() call, but rather goes through the InnoDB heap layer – so InnoDB allocates heap area and then creates requested block(s) there. That optimization helps to avoid fragmentation in case of many small allocations and allows to free all blocks from specific heap at once. But in the case when we need memory only for a single block this 2 layers approach is quite inefficient and in some cases can be the reason for notable MySQL performance drop.

Now in Percona Server, for each connection we use a preallocated read view structure, reuse that memory during the entire connection lifetime and free it at disconnect. If some transactions require a larger amount of memory – we just reallocate memory to fulfill it needs.

To demonstrate the difference we have run sysbench POINT_SELECT test for glibc and jemalloc allocators.

point_select_qps_1024_alloc

Observations:

= MySQL 5.5.30
– throughput of MySQL 5.5.30 with glibc is limited first of all by inefficiency of transaction list handling (see our previous post) and also by bad scalability of glibc malloc itself
– jemalloc helps MySQL 5.5.30 to fix issues with malloc scalability but still scanning of the transaction list
causes performance drop

= MySQL 5.6.10
– in autocommit mode 5.6.10 has no problem with transaction list scanning (due to the read-only transactions optimization), but it still allocates/frees memory for read view structure and that causes drops at high threads with glibc. Jemalloc helps to solve that.

= Percona Server 5.5.30-30.2
– both issues are solved in our recent release and such we have almost no difference in results between runs either with glibc or jemalloc

The post Memory allocators: MySQL performance improvements in Percona Server 5.5.30-30.2 appeared first on MySQL Performance Blog.

Powered by WordPress | Theme: Aeros 2.0 by TheBuckmaker.com