Question 44: What is the memory consumption of MySQL which is not in the statistical range of performance_schema?

Ask

When MySQL memory rises abnormally, we can observe memory usage through performance_schema, which we introduced in experiment 5 .

But we will also find that the MySQL memory usage counted by the operating system is more than the MySQL memory usage counted by performance_schema.

So, what is the memory consumption of MySQL that is not in the performance_schema statistics?

this issue, we design an experiment to observe this problem

experiment

Let's install google-perftools first:

After installation, you can find related libraries:

Kuanyou starts a database:

Write down the startup parameters of the database, and turn off the database:

Before we start the database command, we add two environment variables LD_PRELOAD and HEAPPROFILE.

Where LD_PRELOAD points to the tcmalloc library we just installed, and HEAPPROFILE is the path of the output file.

After starting the database, we see two heap files output in the log.

We add some pressure to the database, or use the familiar doubling method:

Do more data:

Observe that more heap files are output:

Next we install pprof to parse these heap files.

Download golang first, this step needs to hang the proxy:

Install golang:

Install pprof:

Use pprof to parse the heap file we just generated:

We let pprof generate an svg file, and we put the svg file in Chrome to open it for viewing:

The figure is relatively large, each line marks the memory allocation flow, and we use red arrows to mark three collection points (all connections will flow to one of these three collection points).

Among them, pfs_malloc is a memory allocation that can be tracked by performance_schema, while the other two collection points cannot.

Below we will enlarge a part of the picture, and give an example to give a general introduction (in this picture, we use the red arrow to enhance the connection of the original picture):

We can see that 16384.53kB is allocated by the log_allocate_buffer function calling ut_allocator, which cannot be tracked by performance_schema.

Looking at the code, I know that log_allocate_buffer applies for memory according to the log_buffer_size parameter:

Check the parameters and confirm that the configuration of log_buffer_size is consistent with the memory allocation we observed in the figure:

Summarize

In this experiment, we used tcmalloc as MySQL's memory allocator, and used the heap dump function provided by tcmalloc to track MySQL's memory allocation.

Through the memory allocation diagram, we can intuitively understand MySQL's memory allocation:

We can observe from which code path each part of memory is allocated, and which memory can be tracked by performance_schema.

This function has little impact on performance and can be used relatively smoothly in production to explore some memory allocation abnormalities in the production environment.

---

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