There have been a number reports/benchmarks showing MySQL 5.6 to be slower than MySQL 5.5 on variety of workloads. There are many possible reasons and I believe we will learn about many of them in the next few weeks and months as MySQL 5.6 is starting to get production battle tested and there is inflow of real world production performance data hitting MySQL Support Team at Oracle. For number of Simple workloads I would expect to see some slowdown – MySQL 5.6 Optimizer got a lot smarter and the more plans have to be considered for the query the more time the query optimization is likely to take. I also would expect newer code to benefit from “polishing” and clean up to achieve the best performance possible. At Percona we surely will be doing fine tuning for our Percona Server 5.6 release.

Another thing to remember about MySQL 5.6 is – it comes with Performance Schema enabled by default. Even though Performance Schema overhead was reduced in MySQL 5.6 it is still not free and it will cause an overhead, yet hopefully having more information about system operation at your fingertips will help you to achieve gains higher than the slowdown it causes.

I decided to do my own quick check into the problem using SysBench. It is by no means reflection of any production workload but we found it is a good measure of the overhead, especially for simple queries. I ran small (1M rows) benchmark so data was well fitting in memory and Read Only one to simplify things and get more repeatable results without spending too much time on it. I used Ubuntu 12.04 on some old Dell PowerEdge 2850 server. The questions I tried to answer are how much slower 5.6 is for such simple workload ? How much of this slowness comes from Performance Schema ? Whenever we get difference higher or lower with high concurrency compared to single thread ?

Note Sysbench is simple enough it does not take advantage of any of new MySQL 5.6 optimizations so it is geared towards worse case scenario for MySQL 5.6 performance.

Here is Sysbench command I used:

root@dpe01:/mnt/data/sandboxes/msb_5_6_10# sysbench --test=oltp --mysql-socket=/tmp/mysql_sandbox5610.sock  --mysql-user=msandbox_rw --mysql-password=msandbox --oltp-table-size=1000000 --num-threads=1  --oltp-read-only=on --init-rng=on --max-requests=0 --max-time=300 run

For single thread MySQL 5.5 is 11% faster than MySQL 5.6. If you disable Performance Schema in MySQL 5.6 comes to just about 3% showing Performance Schema itself is responsible for 7.5% overhead in this case. This is quite reasonable result and I do not think 3% difference will be noticed in most production cases.

For benchmark with 64 threads MySQL 5.5 is 26% faster compared to MySQL 5.6 and if you disable Performance Schema the difference is still 11% with 13% difference between MySQL 5.6 with Performance Schema enabled and Disabled. The difference of over 25% is rather substantial and if fair amount of workloads will see similar kind of overhead I expect a lot of grief.

It is also unfortunate to see the difference for this specific benchmark actually growing with increased contention even though MySQL 5.6 is positioned as improving scalability over MySQL 5.5. Also number of expected slowdowns such as more work done at optimizer phase should not increase with contention.

Granted I have not tuned MySQL 5.6 for best performance going with defaults outside of few variables MySQL Sandbox sets explicitly but yet this is likely course of action for many MySQL users out there, also was not change of defaults in MySQL 5.6 had more reasonable defaults as its goal ?

Summary: Well, to be frank I expected more from MySQL 5.6 release – more of cleaned up code and better out of the box performance. Yet I’m still very excited about all architecture changed implemented in MySQL 5.6 and new feature and I believe when we look at real workloads we’ll see more cases when MySQL 5.6 excels. I also expect some analyses and cleanup done over next few months which should help to improve MySQL 5.6 performance.