For a couple of months there have been no updates to our msl patch, however recently I managed some time to change this. The functionality was extended a little bit and what’s even more important the patch is available for all the recent MySQL releases.

To remind anyone who has not yet come across this piece of code. msl (microslow) patch was developed a few years ago to allow more precise logging of query execution times into the slow log. Originally MySQL database offered a second time resolution and also a 2 second minimum for the query to get written, that is when you set long_query_time=1. After applying the patch you could see whether the time was 0.005s or 0.9s, which can make a substantial difference for the database and application performance. Over time msl patch grew with new features to let people learn more details of query execution, normally hidden from everyone’s eyes. Currently it’s used by many DBAs and developers to help in optimizations or troubleshooting the applications. We ourselves often rely on it when doing commercial MySQL consulting and MySQL AB has even adopted it into the official database distribution starting 5.1.21 release.

Below you will find the files to download, but first let me walk you through the installation and configuration because things changed along the way.

Installation

In order to use the patch first you will need to build the database binaries from source. This is unavoidable. Following this guide I hope should make it fairly easy even for those of you, who do not have much experience in that kind of tasks.

Of course the problem can be approached in many different ways, however I will be describing the method that is simple, non-intrusive to the system and additionally allows marginal database downtime. It should also work on virtually any Linux distribution and possibly under most other systems.

The patch is currently available for MySQL 5.0.45, 5.0.51a, 5.0.54a and 5.0.56. I assume you have some MySQL version installed already, but if it’s not any of these releases, you will need to upgrade (it will be worth it anyway :-)). You will also need to download the sources matching the installed database version. Once you have it all in place, we can begin.

Open command line (shell) on the server and go to the directory where the sources archive and the patch is. First you should determine how your current MySQL installation was built, so you can have the new binary to be as close to the original one as possible (e.g. to have the same set of storage engines or default paths):

(garfield:~/work) % env VISUAL=/bin/cat mysqlbug | grep ‘Configure command’
Configure command: ./configure ‘–disable-shared’ ‘–with-server-suffix=-community’ ‘–without-embedded-server’ ‘–with-ndbcluster’ ‘–with-innodb’ ‘–with-csv-storage-engine’ (…)


From that output you can learn how to run configure command, which needs to be done prior to compiling the sources. It should be one long line full of various options and in the above case it was:

./configure ‘–disable-shared’ ‘–with-server-suffix=-community’ ‘–without-embedded-server’ ‘–with-ndbcluster’ ‘–with-innodb’ ‘–with-csv-storage-engine’ ‘–with-archive-storage-engine’ ‘–with-blackhole-storage-engine’ ‘–with-example-storage-engine’ ‘–with-federated-storage-engine’ ‘–without-bench’ ‘–with-zlib-dir=bundled’ ‘–with-big-tables’ ‘–enable-assembler’ ‘–enable-local-infile’ ‘–with-mysqld-user=mysql’ ‘–with-unix-socket-path=/var/lib/mysql/mysql.sock’ ‘–with-pic’ ‘–prefix=/’ ‘–with-extra-charsets=all’ ‘–with-yassl’ ‘–exec-prefix=/usr’ ‘–libexecdir=/usr/sbin’ ‘–libdir=/usr/lib64′ ‘–sysconfdir=/etc’ ‘–datadir=/usr/share’ ‘–localstatedir=/var/lib/mysql’ ‘–infodir=/usr/share/info’ ‘–includedir=/usr/include’ ‘–mandir=/usr/share/man’ ‘–enable-thread-safe-client’ ‘–with-comment=MySQL Community Edition (GPL)’ ‘–with-readline’ ‘CC=gcc’ ‘CFLAGS=-O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector –param=ssp-buffer-size=4 -m64 -mtune=generic’ ‘CXXFLAGS=-O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector –param=ssp-buffer-size=4 -m64 -mtune=generic’ ‘CXX=gcc’ ‘LDFLAGS=’


Take your output and copy&paste it to some notepad for later. Next step is to extract the archive:

(garfield:~/work) % gzip -c -d mysql-5.0.51a.tar.gz | tar -x -v -f -
mysql-5.0.51a/
mysql-5.0.51a/bdb/
mysql-5.0.51a/bdb/Makefile.in
mysql-5.0.51a/bdb/btree/
mysql-5.0.51a/bdb/btree/bt_curadj.c
…


Once that has completed, enter the sources directory:

(garfield:~/work) % cd mysql-5.0.51a


Finally you can apply the patch:

(garfield:~/work/mysql-5.0.51a) % patch -p1 < ../patch-mysql-5.0.51a-msl.diff
patching file include/my_time.h
patching file scripts/mysqldumpslow.sh
patching file sql-common/my_time.c
patching file sql/filesort.cc
patching file sql/log.cc
patching file sql/log_event.cc
patching file sql/my_time.c
patching file sql/mysql_priv.h
patching file sql/mysqld.cc
patching file sql/set_var.cc
patching file sql/set_var.h
patching file sql/slave.cc
patching file sql/sql_cache.cc
patching file sql/sql_class.cc
patching file sql/sql_class.h
patching file sql/sql_parse.cc
patching file sql/sql_select.cc
patching file sql/sql_show.cc
patching file sql/structs.h


Now it is the time to run configure with all the options you discovered earlier. Please do not use the below example as it may not be suitable for your environment.

(garfield:~/work/mysql-5.0.51a) % ./configure ‘–disable-shared’ ‘–with-server-suffix=-community’ ‘–without-embedded-server’ ‘–with-ndbcluster’ ‘–with-innodb’ ‘–with-csv-storage-engine’ ‘–with-archive-storage-engine’ ‘–with-blackhole-storage-engine’ ‘–with-example-storage-engine’ ‘–with-federated-storage-engine’ ‘–without-bench’ ‘–with-zlib-dir=bundled’ ‘–with-big-tables’ ‘–enable-assembler’ ‘–enable-local-infile’ ‘–with-mysqld-user=mysql’ ‘–with-unix-socket-path=/var/lib/mysql/mysql.sock’ ‘–with-pic’ ‘–prefix=/’ ‘–with-extra-charsets=all’ ‘–with-yassl’ ‘–exec-prefix=/usr’ ‘–libexecdir=/usr/sbin’ ‘–libdir=/usr/lib64′ ‘–sysconfdir=/etc’ ‘–datadir=/usr/share’ ‘–localstatedir=/var/lib/mysql’ ‘–infodir=/usr/share/info’ ‘–includedir=/usr/include’ ‘–mandir=/usr/share/man’ ‘–enable-thread-safe-client’ ‘–with-comment=MySQL Community Edition (GPL)’ ‘–with-readline’ ‘CC=gcc’ ‘CFLAGS=-O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector –param=ssp-buffer-size=4 -m64 -mtune=generic’ ‘CXXFLAGS=-O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector –param=ssp-buffer-size=4 -m64 -mtune=generic’ ‘CXX=gcc’ ‘LDFLAGS=’


This command will take about a minute to complete and it prints the following message at the end when everything goes fine:

Remember to check the platform specific part of the reference manual for
hints about installing MySQL on your platform. Also have a look at the
files in the Docs directory.

Thank you for choosing MySQL!

Otherwise you will see some error. The sources have been prepared for compilation, so you are ready to run it:

(garfield:~/work/mysql-5.0.51a) % make


Now it’s a good moment to go and get yourself a cup of tea, because for the next 5-10 minutes, depending on the hardware, you will be sitting idle and watching tons of text scrolling through the screen. The process should end with no errors and message like this one:

make[1]: Leaving directory `/home/macko/work/mysql-5.0.51a’


The new database binary should be now available in sql subdirectory:

(garfield:~/work/mysql-5.0.51a) % ls -l sql/mysqld
-rwxr-xr-x 1 macko macko 28467073 2008-04-18 20:31 sql/mysqld


If it’s not there, you had errors in compilation. You may strip the symbols off the binary to make it smaller:

(garfield:~/work/mysql-5.0.51a) % strip sql/mysqld

(garfield:~/work/mysql-5.0.51a) % ls -l sql/mysqld
-rwxr-xr-x 1 macko macko 7453516 2008-04-18 20:38 sql/mysqld

You are ready to have the patched MySQL installed in the system. Locate where the original mysqld binary is with:

(garfield:~/work/mysql-5.0.51a) % whereis mysqld
mysqld: /usr/sbin/mysqld /usr/share/man/man8/mysqld.8.bz2


As you can see in this case it resides in /usr/sbin, because the other file is just a manual. For the following operations you will need to become root user, unless you are already

(garfield:~/work/mysql-5.0.51a) % su root
Password:


First you should apply the changes to the database configuration, specifically you need to enable slow logging (see Configuration section for details). Then rename the binary, which can be safely done even with MySQL still running. This will also automatically create a backup of the un-patched version in case you need to restore it.

(garfield:~/work/mysql-5.0.51a) # cp /usr/sbin/mysqld /usr/sbin/mysqld.BAK-unpatched


Finally you can place the patched mysqld to the right location.

(garfield:~/work/mysql-5.0.51a) # cp sql/mysqld /usr/sbin/mysqld


Even though everything is ready, the database is still running the original code. In order to activate the patch you will need to restart MySQL. You can obviously choose the most convenient time, there is no rush. The restart can take as little as few seconds, however it will depend on many factors such as storage engines used (InnoDB will take significantly longer to close than MyISAM), database configuration, load, etc.

Configuration

There are several parameters related to slow log you can set with patch applied. All filter-type options work in conjunction meaning that in order for query to be logged it must match long_query_time AND min_examined_row_limit AND log_slow_filter.


log-slow-queries[=name]


Log slow queries to this log file. Defaults logging to hostname-slow.log file. Must be enabled to activate other slow log options.

This is the most important one as it enables the logging. If you don’t specify it in my.cnf file, the remaining part of the configuration will not matter, because the log file won’t be created. This is also the only option which you cannot change at runtime from MySQL console wit SET or SET GLOBAL command.

log_slow_filter=name


Log only the queries that followed certain execution plan. Multiple flags allowed in a comma-separated string. [qc_miss, full_scan, full_join, tmp_table, tmp_table_on_disk, filesort, filesort_on_disk]

It allows you to filter queries logged by execution plan. For example to log only queries doing full table scans you would need to set this to “full_scan”, while in order to get only those which use on-disk temporary storage for intermediate results “tmp_table_on_disk,filesort_on_disk” would be a proper flags set. To clear the filter just assign an empty string “” to this option.

Note: you should put double quotes around the entire string of comma-separated flags.

Can be changed at run time with both SET SESSION and SET GLOBAL.

log_slow_rate_limit=#


Rate limit statement writes to slow log to only those from every (1/log_slow_rate_limit) session.

With high traffic coming to your database, the slow logging may consume a lot of IO bandwidth and the file may grow huge very quickly when logging all the queries. This parameter allows you to get the full sessions logged while doing it only for every n-th of them thus limiting the number of writes to the log.

Note: this feature will fail to work well if your application uses some kind of connection pooling. Rate limiting is disabled for the replication thread.

Can be changed at run time with both SET SESSION and SET GLOBAL.

log_slow_verbosity=name


Choose how verbose the messages to your slow log will be. Multiple flags allowed in a comma-separated string. [microtime, query_plan, innodb]

msl patch currently can log three types of information: query timings, execution plan details and InnoDB engine per-query statistics. With this option you may choose which of those you want to have in your slow log. For example to have microsecond query timing and InnoDB statistics you would need to set this option to “microtime,innodb”.

Note: You should put double quotes around the entire string of comma-separated flags. Currently “microtime” is mandatory meaning you cannot disable it. “innodb” is only available with the patch supporting that feature.

Can be changed at run time with both SET SESSION and SET GLOBAL.

long_query_time=#


Log all queries that have taken more than long_query_time microseconds to execute to file.

This option is standard MySQL, however after you apply the patch, it will no longer take time in seconds. Instead it will want you to specify the number of microseconds.

Can be changed at run time with both SET SESSION and SET GLOBAL.

min_examined_row_limit=#


Don’t log queries which examine less than min_examined_row_limit rows to file.

If you are not interested in queries which scan no more than N rows, you can set this to the desired value.

Can be changed at run time with both SET SESSION and SET GLOBAL.

Downloads

msl patch for MySQL 5.0.45
msl patch for MySQL 5.0.51a
msl patch for MySQL 5.0.54a
msl patch for MySQL 5.0.56

msl patch for MySQL 5.0.45 with InnoDB extensions
msl patch for MySQL 5.0.51a with InnoDB extensions
msl patch for MySQL 5.0.54a with InnoDB extensions
msl patch for MySQL 5.0.56 with InnoDB extensions

Entry posted by Maciej Dobrzanski | 2 comments

Add to: | | | |