More on using Google Perftools with MySQL

I have updated my MySQL 5.0 branch in launchpad  to use more from Google Perftools including CPU profiling and integration with exported tcmalloc functions. It took me and others to figure out how to get this done. You might want to stop reading now if you don't build your own MySQL binaries.

On some binaries, tcmalloc makes MySQL much faster for workloads with a lot of concurrency running on big SMP servers. I claim that this is true for some binaries, not all binaries. And whether this is true seems to be determined by whether the other obvious bottlenecks have been fixed in the binary. For example, tcmalloc improves performance much more for the high-performance XtraDB binaries than for official versions of MySQL.

The first step is to use the tcmalloc library. I use tcmalloc_minimal rather than tcmalloc and suggest you do the same. To use tcmalloc without rebuilding you may be able to set LD_PRELOAD_LIBRARY. I prefer to link against it. To do that, run configure and then build mysqld:

./configure --with-mysqld-ldflags=-L/path/to/google-perftools \
--with-mysqld-libs=-ltcmalloc_minimal

I confirm that this really linked tcmalloc by running 'nm mysqld | grep -i tcmalloc'. In some cases for me, it wasn't linking it because libtool used a command line with -lc prior to -ltcmalloc_minimal as a result of odd dependencies listed in the *.la files local to my environment used by libtool. So if something goes wrong, that may be the problem.

Google Perftools provides hierarchical profiling. If you aren't using DTrace or have not compiled your Linux kernel and all system libraries with sufficient flags to get hierarchical profiles from oProfile, then you need perftools. Details on it are here and here. Unlike tcmalloc, support for CPU profiling is not transparent. You need to call an exported function from libprofiler at thread start time. I have modified MySQL to do that for all threads started in mysqld.cc which includes some background threads and all user threads. I chose not to do this for the background threads started by InnoDB. Code and more details (see README.patch) are in my 5.0 branch revision 2702.

Finally, I provided access to a few features from perftools (see revision 2703 for the code):

• RELEASE MEMORY is a SQL command that returns cached memory from tcmalloc back to the OS
• SHOW MEMORY STATUS is a SQL command that prints the data returned from MallocExtension::GetStats. This is a lot of data on the state of memory allocated by tcmalloc.
• tcmalloc_max_thread_cache_size is a my.cnf parameter that sets the size of the memory cache shared by all threads.