Load testing on MySql with Percona Query Playback — a quick tutorial
Sometimes it is necessary to test new configuration on MySql, to see quickly if performances are better than the previous configuration or at least if they are not disastrous :)
There are not so many tools for this task, and I know many IT admins that sometimes rely on load tests on the entire LAMP stack. This could be fine, but sometimes it is too much for a simple configuration change on a Mysql server.
I’ve discovered Percona Query Playback and it seems to fit my case: it makes a load test on a Mysql database, without testing the whole stack and primarily timing the results. As input it requires the slow query log or the general log, and as output it tells us how many queries were slow queries and how much time it needed to execute the entire set of queries.
For this tutorial, I’ve used a test environment with Percona Mysql 5.5 and Centos 7
Installation
At the moment there are no repositories with Playback ready for the installation, so we need to compile it. The official guide is a bit too much scarce and I suggest to compile the docs too.
The first thing we need to do is install the packages to compile everything.
$ sudo yum -y install tbb tbb-devel cmake boost boost-devel Percona-Server-devel-55 gcc gcc-c++ openssl-devel
If you have a standard mysql installation you can use mysql-devel instead of Percona-Server-devel.
Then we can proceed with the download of the source or the clone of the github repository. For this tutorial I will proceed with the archive.
$ wget https://github.com/Percona-Lab/query-playback/archive/master.zip
$ unzip master.zip
We can compile it using the guide on github.
$ cd query-playback-master
$ mkdir build_dir
$ cd build_dir
$ cmake -DCMAKE_BUILD_TYPE=RelWithDebInfo ..
$ make
In my case I had this problem compiling:
In file included from /usr/local/src/query-playback-master/percona_playback/mysql_client/mysql_client.cc:18:0:
/usr/local/src/query-playback-master/percona_playback/mysql_client/mysql_client.h:18:19: fatal error: mysql.h: No such file or directory
Because I needed a dirty-’n-quick workaround, I’ve modified query-playback-master/percona_playback/mysql_client/mysql_client.h first lines like this:
#include <percona_playback/db_thread.h>
//#include <mysql.h>
#include </usr/include/mysql/mysql.h>
Be sure that you have mysql.h in that path. If there is no mysql.h, you will need to check if you can find it with locate or if you have mysql-devel, Percona-Server-devel or mariadb-devel installed.
After we have compiled everything, we can check if it works and then install the new compiled Query Playback (this is optional).
$ make check
$ sudo make install
Execution
Ok, now we have everything compiled and working. To see the help menu with the various options we can just type:
$ ./percona-playback --help
I personally suggest to use Playback with the slow query log, because Playback makes a comparison between the execution time stated on the slow query log and the execution time of our test.
To configure a Slow Query Log just follow this guide and set the slow query time at 0, to capture every query that it is being executed. In my case, I’ve enabled the slow query log, done a load test with JMeter and then used the brand new slow query log as input for Playback.
To make a test:
./percona-playback --mysql-host localhost --mysql-socket /var/lib/mysql/mysql.sock --mysql-username testuser --mysql-password ******* --mysql-schema db_name --query-log-file /var/log/mysqlslow.log
If you are using the general log you will have to use the option — input-plugin:
./percona-playback --mysql-host localhost --mysql-socket /var/lib/mysql/mysql.sock --mysql-username testuser --mysql-password ******* --general-log-file /var/log/mysqlgeneral.log --input-plugin general-log --mysql-schema db_name
When you use the slow query log the output will be like that:
Detailed Report
----------------
SELECTs : 325637 queries (290917 faster, 34720 slower)
INSERTs : 0 queries (0 faster, 0 slower)
UPDATEs : 7284 queries (4940 faster, 2344 slower)
DELETEs : 180 queries (34 faster, 146 slower)
REPLACEs : 0 queries (0 faster, 0 slower)
DROPs : 0 queries (0 faster, 0 slower)
Report
------
Executed 347652 queries
Spent 00:01:54.743872 executing queries versus an expected 00:05:24.782214 time.
302206 queries were quicker than expected, 45446 were slower
A total of 0 queries had errors.
Expected 5795467 rows, got 5796333 (a difference of 866)
Number of queries where number of rows differed: 430.
Average of 102.86 queries per connection (3380 connections).
As you can see, you have the number of queries, the execution time and how many queries were slower. You can now try to make some tuning and see how your database will behave under different conditions.
