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:
|
1 |
sudo yum install -y wget make gcc gcc-c++ libtool libaio-devel bzip2 glibc* |
Next, the steps needed to compile and run:
|
1 2 3 4 5 6 |
wget https://sourceware.org/pub/valgrind/valgrind-3.15.0.tar.bz2 tar -xf valgrind-3.15.0.tar.bz2 cd valgrind-3.15.0/ ./configure make sudo make install |
Finally, to validate if everything was ok:
|
1 |
valgrind --version |
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:
|
1 2 |
# file /usr/sbin/mysqld /usr/sbin/mysqld: ELF 64-bit LSB executable, x86-64, version 1 (GNU/Linux), dynamically linked (uses shared libs), for GNU/Linux 2.6.32, BuildID[sha1]=ee2104a90a03b9077ffdebbe50c7898f301e4f9c, stripped |
In this case, let’s install the debug symbols:
|
1 |
# yum install -y Percona-Server-57-debuginfo |
For MySQL community versions, they are usually not stripped:
|
1 2 |
#file /opt/mysql/5.7.27/bin/mysqld /opt/mysql/5.7.27/bin/mysqld: ELF 64-bit LSB shared object, x86-64, version 1 (GNU/Linux), dynamically linked (uses shared libs), for GNU/Linux 2.6.32, BuildID[sha1]=2a2a22de75a09a0a54411a90c1c8fbb4d057c759, 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:
|
1 |
valgrind --tool=massif --massif-out-file=./massif.out /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid |
Here is an example:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
[root@node1 massif]# valgrind --tool=massif --massif-out-file=./massif.out /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid ==16045== Massif, a heap profiler ==16045== Copyright (C) 2003-2017, and GNU GPL'd, by Nicholas Nethercote ==16045== Using Valgrind-3.15.0 and LibVEX; rerun with -h for copyright info ==16045== Command: /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid ==16045== ==16046== ==16045== [root@node1 massif]# ps -ef | grep mysql root 3016 1 0 10:48 ? 00:00:00 /bin/sh -c /usr/local/percona/qan-agent/bin/percona-qan-agent >> /var/log/pmm-mysql-queries-0.log 2>&1 mysql 16047 1 51 11:36 ? 00:00:02 valgrind --tool=massif --massif-out-file=./massif.out /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid root 16077 3372 0 11:36 pts/0 00:00:00 grep --color=auto mysql |
Valgrind creates an output file as defined on the massif-out-file. Subsequently, it is time to visualize the results. Run the command below:
|
1 |
# ms_print massif.out > report.out |
And a quick interpretation of the results:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
->85.65% (21,006,007,264B) 0x804662: SQL_SELECT::test_quick_select(THD*, Bitmap<64u>, unsigned long long, unsigned long long, bool, st_order::enum_order) (in /usr/sbin/mysqld) | | | | ->85.65% (21,006,007,264B) 0x840EED: ??? (in /usr/sbin/mysqld) | | | | ->85.65% (21,006,007,264B) 0x84354B: JOIN::optimize() (in /usr/sbin/mysqld) | | | | ->85.65% (21,006,007,264B) 0x707FFE: mysql_select(THD*, TABLE_LIST*, unsigned int, List<Item>&, Item*, SQL_I_List<st_order>*, SQL_I_List<st_order>*, Item*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*) (in /usr/sbin/mysqld) | | | | ->85.65% (21,006,007,264B) 0x7088D4: handle_select(THD*, select_result*, unsigned long) (in /usr/sbin/mysqld) | | | | ->85.65% (21,006,007,264B) 0x55C981: ??? (in /usr/sbin/mysqld) | | | | ->85.65% (21,006,007,264B) 0x6E09B2: mysql_execute_command(THD*) (in /usr/sbin/mysqld) | | | | ->85.65% (21,006,007,264B) 0x6E6407: mysql_parse(THD*, char*, unsigned int, Parser_state*) (in /usr/sbin/mysqld) | | | | ->85.65% (21,006,007,264B) 0x6E7BDA: dispatch_command(enum_server_command, THD*, char*, unsigned int) (in /usr/sbin/mysqld) | | | | ->85.65% (21,006,007,264B) 0x6B3EA1: do_handle_one_connection(THD*) (in /usr/sbin/mysqld) | | | | ->85.65% (21,006,007,264B) 0x6B3F3F: handle_one_connection (in /usr/sbin/mysqld) | | | | ->85.65% (21,006,007,264B) 0x919705: pfs_spawn_thread (in /usr/sbin/mysqld) | | | | ->85.65% (21,006,007,264B) 0x4E3754A: start_thread (in /usr/lib64/libpthread-2.26.so) |
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.
