Wednesday, April 3, 2013

Is MySQL 5.6 slower than 5.5?

Since MySQL 5.6 released there are so many benchmarks reports posted using the sysbench OLTP workload. Most of the benchmarks are surprising and totally disagree with the claim MySQL posted about performance gain of more than 200% with MySQL 5.6. So I did sysbench OLTP benchmark on old system which show MySQL 5.5 has performance edge over MySQL 5.6. Its very early now to make any assumptions on performance because the new version just starting in production use or in QA/Staging. So after 5-6 month we might have real time performance benchmark.
One important point we should remember that MySQL 5.6 has performance schema enabled by default and the latest version has lots of improvement in performance schema which reduced overhead so i have added benchmark with and without performance schema feature.
My Hardware configuration is:
Red Hat Enterprise Linux Server release 5.2 2.6.18-92.el5
Dual-Core AMD Opteron(tm) Processor 2216 4 CPU
Without any mysql tuning parameter.

sysbench --test=oltp --mysql-user=root --mysql-password=root --oltp-table-size=1000000 --mysql-db=test --max-time=60 --max-requests=0 --num-threads=? run

                                                          Read Only Benchmark

The graphs are clearly showing better MySQL 5.5 performance over 5.6, also we could see 5.6 without performance schema is better than using performance schema, i think performance schema has little overhead.

The Read Write benchmark also showing MySQL 5.5 performance is better than 5.6, however 5.6 with out performance schema is performing better than MySQL 5.5

                                       Read Write Benchmark

Lets tweak some parameters on MySQL 5.6 and see the graph. I think Buffer Pool might make a difference in performance so i did another benchmark with Buffer Pool 4 GB, again results are not encouraging.
There are two more important parameters which affect performances suddenly if configured properly according to hardware and OS configuration. These are : innodb_adaptive_hash_index and innodb_spin_wait_delay. I see some performance gain if  innodb_spin_wait_delay is disabled but still performance is below MySQL 5.5. No performance impact can be seen if innodb_adaptive_hash_index is OFF.

See the cumulative graph for Read Only Benchmark.

1 comment:

  1. I am using 5.5 and 5.6, when i run the same query on 5.5 it takes 30 minutes to execute while on 5.6 it takes just 2-3 seconds pls help me which parameter should i tune in 5.5 to improve the performance.
    I am using CentOS on Vmware with 8G of RAM..