Profiling MySQL memory usage with Valgrind / Massif

High memory usage scenarios may sometimes be trivial to troubleshoot e.g. when memory parameters are explicitly set too high. Investigations into such issues may also prove to be very difficult when memory pressure is a result of specific workload patterns or better yet, engine bugs.

Advanced memory troubleshooting in MySQL was never easy but thanks to performance_schema memory instrumentation in MySQL 5.7, we finally have some tools to work with. There are still situations when performance schema will not be sufficient:

1. On servers running MyQL <5.7 (well... vast majority),
2. When the component you're interested in is not instrumented,
3. When you don't fancy reading cryptic names of performance schema instruments. While I do love performance schema, this is no joke: OS-level heap profiles are much easier to read than data in performance schema (and they point you directly to the relevant locations in the code).

In this short article, I'll show you how easy it is to use the well known Valgrind / Massif tools to profile MySQL memory usage, no matter which MySQL version you're running.

Running Valgrind

Using Valgrind (and its heap profiling tool, Massif) is relatively straightforward:

1. Valgrind is basically a virtual machine, under which MySQL is started. Valgrind comes with multiple tools, for heap profiling we're going to use a tool named Massif.
2. Because MySQL runs within a controlled environment, all memory allocation/deallocation calls go through the profiler and can therefore be tracked.
3. The tool records memory usage snapshots periodically.
4. When the program (MySQL) finishes, Valgrind (Massif) produces an output file that can be used to generate memory usage reports.

Let's cut to the chase! This is how I normally launch MySQL from its base directory:

bin/mysqld --defaults-file=my.cnf

And this is how I launch it within Valgrind:

valgrind --tool=massif --massif-out-file=/home/mysql/massif/mysql-heap-profile.out bin/mysqld --defaults-file=my.cnf

The parameters are:

• --tool: the profiling tool to be used (Massif)
• --massif-out-file: desired location of the resulting file.

Note that the output file is only produced after the process finishes (or is terminated). Once MySQL starts, you can work with it as you normally would, though you should expect it to be noticeably slower.

Using Massif output files

Outputs from Massif can be displayed in textual form or you can visualize them using massif-visualizer tool. Let's run an actual test and see what we can learn from Massif. For this article, I decided to simply re-run a test from one of my previous blog posts.

This is what massif-visualizer produced:

Observations:

• Valgrind recorded peak memory usage of 12.4GB, we see a very nice visual representation of memory usage over time.
• As shown by the amount of memory allocated by "pfs_malloc_array", performance schema chewed up 2.5GB. Funny enough, this is where Valgrind already proves its value by pointing out that... I forgot to disable P_S before the test.
• Performance schema is there by mistake but let's use it. As proved by the green-yellow-ish area at the bottom of memory usage graph, the P_S structure was pre-allocated right after server startup. This is indeed true because I used MySQL 5.6.27 here (5.7 would not have allocated the entire memory up front).
• The individual lines under "massif data" contain actual names of MySQL functions, there are also pointers to source files where the functions can be found. Isn't that nice?

Second go, this time with performance schema disabled

Observations:

• We're no longer spending memory on performance schema: functions related to P_S structure allocation are gone, also the graph does not show significant amounts of memory being allocated up front. Memory usage is just increasing gradually during the test.
• Memory usage peaked at 9.8GB, most of which was used for allocating InnoDB table handlers. In fact, you may notice that as we go deeper into the call stack, the source of memory pressure seems to be caused by heavy use of partitions. If you noticed that, you're right! These profiles were captured during an investigation into memory usage in heavily partitioned databases.

Closing words

Examples used in this article, albeit very simple, show the value of Valgrind/Massif as MySQL troubleshooting tools. I encourage you to play with your own MySQL installations and gather heap profiles under various workloads. Tools such as these should have a place in every DBA's toolbox and now that you see how easy it is to use them, there's simply no excuse not to try them out!