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
8 GB RAM
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.
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.
ReplyDeleteI am using CentOS on Vmware with 8G of RAM..