Monitor MySQL Memory Usage – Part 2 (Valgrind)

Posted on by

So, today, continuing with memory analysis I will present the Valgrind tool. Valgrind programming tool provides information for memory debugging, memory leak detection, and profiling. It is useful for cases where you suspect that MySQL memory is increasing or simply it is using too much memory given the parameters that the database is configured.

Pre-requisites

With the introduction done, let’s start with a practical example. First, we need Valgrind. You can download it here. At the date of this post, the latest version is 3.15.0. The snippet below is based on this version:

Note that these libraries are needed to compile and run Valgrind:

Next, the steps needed to compile and run:

Finally, to validate if everything was ok:

MySQL Debug Symbols

Before running Valgrind on MySQL, make sure debug symbols are present. Debug symbols are present when the binary is not stripped of them (downloaded ‘GA’ [generally available] packages may contain optimized or stripped binaries, which are optimized for speed rather than debugging). The command below helps to identify if the current version is stripped or not:

In this case, let’s install the debug symbols:

For MySQL community versions, they are usually not stripped:

Running Valgrind

Finally, with all pre-requisites done, it’s time for fun. MySQL needs to start with Valgrind, so, first, make sure that MySQL is NOT running. Next, start MySQL with Valgrind:

Here is an example:

Valgrind creates an output file as defined on the massif-out-file. Subsequently, it is time to visualize the results. Run the command below:

And a quick interpretation of the results:

In this example, MySQL is using 19GB of memory in a optimization of a JOIN which is a huge value for a single query. So, the next reasonable step would be collect more information about this query.

Conclusion

To resume: using Valgrind Massif will help you to analyze the ins and outs of MySQL (or any other program) memory usage. It’s a great tool to debug memory on the application side as well.

Leave a Reply