Benchmarking MySQL can be a tricky task. There are many variables that interact with each other and it can be difficult to run good isolated experiments and get data you can use. I ran into trouble benchmarking with sysbench recently that stumped me at first.

I was upgrading a database server to MySQL 5.1 from MySQL 5.0 in the hopes of getting better concurrency performance from InnoDB. The first step in that upgrade process was to run some benchmarks on 5.0 and 5.1 to make sure we were getting the benefits we were aiming for and not taking any performance hits. I performed an end-to-end application benchmark, a concurrency benchmark, and two standard benchmarks. The standard benchmarks were sysbench (read only and read/write) and the Database Test Suite’s OLTP test.

The Mystery

All of my benchmarks looked great except for the sysbench test. I was getting an order of magnitude better performance from 5.0 than from 5.1. This kind of performance regression was pretty disconcerting and needed to be investigated before we could continue with the upgrade.

The first step was isolating the variables. I got two cloud servers with a base Debian Lenny install and installed MySQL on both. (One server had 5.0, and the other 5.1.) I wanted to be sure that none of the other config our servers normally have could be causing the performance regression. I tweaked the config identically on both servers to give some reasonable values for things like the InnoDB buffer pool size. I then had sysbench create a test table with 1000000 rows. Finally I dumped the table and imported it into the other MySQL server.

The next step was to confirm that the problem still existed in my new isolated setups. The benchmark I used was the following:

sysbench --test=oltp --oltp-table-size=1000000 --mysql-db=sysbench --mysql-user=root --max-time=60 --max-requests=0 --num-threads=4 --oltp-read-only=on run

Running that on the new setups gave these results:

Transactions Per Second

• MySQL 5.0: 1309.28
• MySQL 5.1: 425.23

This confirms that after removing all of the variables associated with our setup and our applications the performance regression was still present. MySQL 5.0 was performing more than three times better than 5.1 on this read only benchmark.

The Investigation

The first thing to investigate were the MySQL status variables. I noticed that MySQL 5.1 had a really high query cache lowmem prune rate, especially when compared to the insert rate. I checked the same query cache variables in 5.0 and found that the query cache wasn’t being used at all. This was a surprise since the query cache was turned on in both installs and configured in the exact same way.

MySQL 5.1

mysql> show status like '%Qcache%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Qcache_free_blocks      | 2549    |
| Qcache_free_memory      | 9036656 |
| Qcache_hits             | 74182   |
| Qcache_inserts          | 298916  |
| Qcache_lowmem_prunes    | 291970  |
| Qcache_not_cached       | 92      |
| Qcache_queries_in_cache | 6946    |
| Qcache_total_blocks     | 16442   |
+-------------------------+---------+
8 rows in set (0.00 sec)

MySQL 5.0

mysql> show status like '%Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        | 
| Qcache_free_memory      | 16759744 | 
| Qcache_hits             | 0        | 
| Qcache_inserts          | 0        | 
| Qcache_lowmem_prunes    | 0        | 
| Qcache_not_cached       | 1105842  | 
| Qcache_queries_in_cache | 0        | 
| Qcache_total_blocks     | 1        | 
+-------------------------+----------+
8 rows in set (0.00 sec)

You can see that MySQL was pruning almost as many queries from the query cache as it was inserting in 5.1.

As an experiment I turned off the query cache in both installs and ran the benchmarks again. This time I got these results:

Transactions Per Second

• MySQL 5.0: 1305.35
• MySQL 5.1: 1313.37

It was now pretty clear that I had found the performance problem. It had something to do with a change in the way the query cache operated between 5.0 and 5.1. A quick googling for changes in the query cache between versions didn’t turn anything up, so it was time to dig a bit deeper.

I realized that I didn’t know much about the queries sysbench was running. This can be found out pretty easily by turning on the general log in MySQL. I ran the benchmarks again and checked the logs. I found that sysbench uses 10 different types of queries and that it executes them using prepared statements. Now this time google was able to give me some answers. As of 5.1.17, the query cache can cache prepared statements.

From skimming the general log after running sysbench it was clear that the read only benchmark was a worst case scenario for the query cache. It was a lot of simple, generally non-repeating, selects. This means that there is going to be a high query cache insert rate, but low hit rate (because they’re unlikely to repeat), and because so many are being inserted the cache is going to be constantly pruned.

Determining the value of the query cache is a whole separate problem. Some would argue that you’re better off without it. But, I wasn’t trying to evaluate the query cache, and since our apps don’t rely on prepared statements the results I was getting didn’t give a fair representation of how our app would perform.

The Takeaway

It was important to figure out what caused the performance regression. It couldn’t just be ignored, even though the other three benchmarks all agreed with each other. Once I figured out what caused the strange results (prepared statements are cached using the query cache) I could make a decision as to whether or not that was important. If you use a lot of prepared statements in your apps, then this information is very valuable and should be considered in your decision to upgrade.

By setting db-ps-mode to disabled you can tell sysbench not to use prepared statements. Once I determined that this better reflected the actual kinds of selects we used in our apps I could run the benchmarks again without the prepared statements:

sysbench --test=oltp --oltp-table-size=1000000 --mysql-db=sysbench --mysql-user=root --max-time=60 --max-requests=0 --num-threads=4 --oltp-read-only=on --db-ps-mode=disable run