Question 45: How to diagnose the sudden increase of MySQL memory

Ask

In question 44, we use the tool provided by tcmalloc to view the memory allocation of MySQL

This method has little impact on performance and can be run in a production environment, but the MySQL allocator needs to be configured as tcmalloc

In this experiment, we introduce another method to diagnose the sudden increase in MySQL memory.

experiment

We still open up a database:

In this experiment, we need to simulate the sudden increase of MySQL memory. We found a related bug that is easy to reproduce from the MySQL bug database: https://bugs.mysql.com/bug.php?id=99382

The description of this bug is very clear, and a SQL script is provided, and the surge of memory can be reproduced by directly executing the script:

Let's try it:

The current memory usage of MySQL is 181M

Execute the script:

During the execution of the script, we will find that the memory used by MySQL continues to rise.

Now let's diagnose MySQL's use of memory. During the execution of the script, execute the following commands at the same time to observe:

Tips

What is the mmap system call?

Simply put, MySQL does not apply for memory directly from Linux, but applies for memory from glibc. glibc will maintain a memory pool. When glibc finds that the memory pool is tight, it will apply for memory from Linux through the system call mmap (or brk).

So we monitor the system call mmap, and also monitor under what circumstances MySQL needs a lot of memory (that is, when glibc's memory pool is tight)

As for the mechanism of glibc memory allocation, please refer to: https://sploitfun.wordpress.com/2015/02/10/understanding-glibc-malloc/

You can see that perf.data is generated in the current directory:

We convert perf.data into a readable way:

Taking a simple look at perf.out, we will see that each mmap call has recorded enough information, including:

1. The part marked in red in the figure is the thread number that requires memory allocation. With the thread number, we can find which SQL is occupying the memory.
2. The part marked in blue in the picture is the size of the allocated memory
3. The part marked in green in the figure is the stack information when memory is allocated

With the thread number and stack information, we can determine which SQL (or which internal thread of MySQL), and under what circumstances require memory allocation

In this example, we judge process 29735. In the create view operation, memory needs to be allocated when the table is opened.

Of course, the perf.out file is very long, you need to aggregate the information together, and then determine who is the most memory consuming

Limitations

The method introduced in this experiment has limitations: this method is suitable for the case of MySQL memory surge, in other cases (such as slow and continuous growth of memory), this method may not be able to observe accurate information.

As mentioned in the tips, MySQL applies for memory from glibc (the first step).When the memory pool of glibc is tight, glibc then applies for memory from Linux (the second step).

In the case of a rapid increase in memory, the occurrence of the first step will cause the second step to occur with a high probability. We observe the system calling mmap, in fact, observe the occurrence of the second step, and deduce the reason for the first step.

In the case of slow memory growth, the small probability of the first step will cause the second step to occur, making it impossible to derive the first step from the second step.

For example: due to some reason, the memory grows slowly. When the glibc memory pool is about to be tight, other business SQL occurs, which causes these business SQL to trigger the glibc memory pool tightness, then we diagnose that the reason for the memory growth is because of business SQL. And the real reason is hidden.

When using this method, I hope everyone will notice this limitation.

---

What else do you want to know about the technical content of MySQL? Hurry up and leave a message to tell the editor!