I blogged about how the April labs release 2012 made 5.6 improve performance of the Sysbench OLTP RO benchmark by more than 50%. Now we made an even more significant improvement of the MySQL 5.6 performance in the june 2012 labs release available at labs.mysql.com.
We've been able to improve Sysbench OLTP RO performance by a massive 270% going from MySQL 5.6.5 to the MySQL 5.6 june 2012 labs release. The benchmarks were made on an internal pre-release of the june labs release.
Also for Sysbench OLTP RW we made a very significant breakthrough in performance, the performance increase is 182% here.
The tests were made on a "monster"-box we have available at Oracle with 8 CPU sockets, each socket has an Intel Xeon 7540 processor that have 6 cores per socket and 2 threads per core. Thus a total of 96 CPU threads are available in the machine. The CPU frequency was 2.00 GHz and the available memory was 512 GB. The disks used in the benchmark was SSDs. The benchmarks was executed using the dbt2-0.37.50 scripts available on the dev.mysql.com site. We will upload the latest changes to those scripts early next week. The MySQL Server was locked to use up to 64 CPU threads in the benchmarks executed.
So what is the cause of such massive performance improvement. Well actually it is the result of all 5.6 changes made over a long period of time. There has been a great number of significant changes in the InnoDB engine in the 5.6 development. Most notable of those are changes by Sunny Bains to split the InnoDB kernel mutex into 10 different mutexes. Also Inaam Rana made a significant changes of the buffer pool mutexes to improve these. There has also been numerous other changes in InnoDB, one notable such one that Dimitri Kravtchuk has been instrumental in benchmarking and suggesting changes to is the new adaptive flushing algorithm. Check Dimitri's blog for more meat on the bones of that change. Most of the InnnoDB changes were in the MySQL 5.6.5 release as well, so why the large jump in performance now.
Part of the reason is that there was a cache line that became very hot in the MySQL Server as mentioned in an earlier blog. Removing this bottleneck alone made performance jump by more than 50%. However there was also other bottlenecks that caused performance to still not jump. Most notable of those were the LOCK_open mutex in the MySQL Server part. We have finally now removed this as the main bottleneck in the server. I will describe these changes more in a coming blog. So the impact of the important changes in the MySQL 5.6.5 were hidden by the cache line problems and LOCK_open problems and once those were removed we could see this leapfrog in performance making the MySQL 5.6 the best MySQL release ever.
With LOCK_open removed as a bottleneck it also made sense to see how much performance was affected in Sysbench by only having one table in the benchmark. So we did the benchmarks mentioned above using 2 tables instead of one. Using 3 tables had no impact on performance, but going to 2 tables means that we split one of the mutexes in the data dictionary into two and also the InnoDB index mutex is split into two and finally the root page of the InnoDB indexes are two instead of one. We think it's fair to say that most real-life applications would use more than one table. The second table improved performance by about 25-30%.
We've been able to improve Sysbench OLTP RO performance by a massive 270% going from MySQL 5.6.5 to the MySQL 5.6 june 2012 labs release. The benchmarks were made on an internal pre-release of the june labs release.
Also for Sysbench OLTP RW we made a very significant breakthrough in performance, the performance increase is 182% here.
The tests were made on a "monster"-box we have available at Oracle with 8 CPU sockets, each socket has an Intel Xeon 7540 processor that have 6 cores per socket and 2 threads per core. Thus a total of 96 CPU threads are available in the machine. The CPU frequency was 2.00 GHz and the available memory was 512 GB. The disks used in the benchmark was SSDs. The benchmarks was executed using the dbt2-0.37.50 scripts available on the dev.mysql.com site. We will upload the latest changes to those scripts early next week. The MySQL Server was locked to use up to 64 CPU threads in the benchmarks executed.
So what is the cause of such massive performance improvement. Well actually it is the result of all 5.6 changes made over a long period of time. There has been a great number of significant changes in the InnoDB engine in the 5.6 development. Most notable of those are changes by Sunny Bains to split the InnoDB kernel mutex into 10 different mutexes. Also Inaam Rana made a significant changes of the buffer pool mutexes to improve these. There has also been numerous other changes in InnoDB, one notable such one that Dimitri Kravtchuk has been instrumental in benchmarking and suggesting changes to is the new adaptive flushing algorithm. Check Dimitri's blog for more meat on the bones of that change. Most of the InnnoDB changes were in the MySQL 5.6.5 release as well, so why the large jump in performance now.
Part of the reason is that there was a cache line that became very hot in the MySQL Server as mentioned in an earlier blog. Removing this bottleneck alone made performance jump by more than 50%. However there was also other bottlenecks that caused performance to still not jump. Most notable of those were the LOCK_open mutex in the MySQL Server part. We have finally now removed this as the main bottleneck in the server. I will describe these changes more in a coming blog. So the impact of the important changes in the MySQL 5.6.5 were hidden by the cache line problems and LOCK_open problems and once those were removed we could see this leapfrog in performance making the MySQL 5.6 the best MySQL release ever.
With LOCK_open removed as a bottleneck it also made sense to see how much performance was affected in Sysbench by only having one table in the benchmark. So we did the benchmarks mentioned above using 2 tables instead of one. Using 3 tables had no impact on performance, but going to 2 tables means that we split one of the mutexes in the data dictionary into two and also the InnoDB index mutex is split into two and finally the root page of the InnoDB indexes are two instead of one. We think it's fair to say that most real-life applications would use more than one table. The second table improved performance by about 25-30%.
No comments:
Post a Comment