Fun with mysqlslap benchmarking

[Chris]

Mysqlslap is a good benchmarking tool but can be much more versatile in my opinion. So, recently, I was benchmarking a problem that was, not hard to solve, but somewhat tricky to benchmark. Using mysqlslap was the fastest way to get the answers I wanted with the least amount of BS, or so I thought!

Lets take a look:

-- The problem
1. A MySQL instance with 5 databases
2. Only one of the databases looked to be the problem from system analysis
3. Light concurrency issues suspected, given that the tables were MyISAM and the information gathered from the customer

-- How to run a close to real world benchmark
Turning on the general query log is a must in this situation so I gathered 1 hour worth of transactions. Please keep in mind that you have to take a snapshot of the data before you enable the general query log for obvious reasons.

Now I have a snapshot of the data then 1 hour of general query logging thereafter. Originally I thought that I would be able to feed the general query log into mysqlslap and be done with it. This, however, was not the way of it.

-- Creating the files
I knew from the system analysis that there was one database of the 5 that was the root of the issue. From there I started to grep through the general log to find all of the queries that had table names from the one database. Unfortunately, many of the table names were duplicated throughout all of the databases on the server.

I wrote a quick in-line script to get the connection ids for all the connections on the database I was interested in, easy enough!

# NOTE: depending on your version of MySQL
## This example is for mysql 5.4
Shell> for x in var=`cat mysql.log |grep Connect |awk '{print $2}' |cut -d: -f3`; do echo ${var:2:50} >> out.txt; done

I now have all of the connection ids for the database I want to benchmark. From here I need to parse through the mysql.log (the general log) to get the queries that I would like to run with mysqlslap. Here goes:

Shell> for x in `cat ./out.txt`; do cat mysql.log | awk -v x="$x" '{ if ( $1 == x ) { printf("%s ;\\\\n",$0,x) } }' $1; done |awk -FQuery '{print $2 “ ;”}' |grep –v “INSERT” >> myqueries.txt

Are there other ways to do it, sure, this is just one and not bad for a few minutes of work.

-- The benchmark
With the snapshot in place and mysql running we can now make a “close to real world” benchmark.

Shell> mysqlslap --concurrency=5 \\\\
--iterations=5 --query=myqueries.txt \\\\
--delimiter=";"

-- Repeat the benchmark with a different configuration or my.cnf tweak.
1. rebuild mysql with the snapshot taken
2. tweak mysql
3. run mysqlslap