Oracle has just released the MySQL 5.6.10 GA version, so it’s time to update the previous performance test data. This round of testing includes the following versions:
MySQL-5.5.29
MySQL-5.6.10
MariaDB-5.5.28a
MariaDB-10.0.1
The 5.5 version was retained in this test for regression testing. We have often found in the past that new versions actually lag behind in performance.
This test was conducted in a different environment, the main difference being the use of high-performance RAID-5 storage with 512 MB battery-backed cache instead of an SSD. Furthermore, the test machine had 16 cores, with 12 cores running mysqld and the other 4 cores running sysbench.
The test used sysbench-0.5 OLTP, encompassing 8 tables and 10G of data. The InnoDB buffer pool size was 16G, with 4G for logs. Different disk systems require different InnoDB configurations:
innodb_io_capacity = 1000 (was 20000 for SSD)
innodb_flush_neighbors = 1 (was 0 for SSD)
Below are the test results, starting with OLTP read-only:
Very strangely, MySQL 5.6 exhibited abnormal performance in this round of testing. The difference was negligible at 8 threads, and it performed best at 16 threads. However, at higher concurrency, performance dropped rapidly, even worse than MySQL 5.5. MariaDB 10.0 performed slightly worse than MariaDB 5.5, but the difference was not as pronounced.
The response time chart, on the other hand, appears better and smoother:
MySQL-5.6 and MariaDB-10.0 appear to be slightly better, meaning they can distribute CPU cycles more efficiently.
Disclaimer: Thread pooling was not used in this test. Oracle’s thread pool implementation has been closed-sourced, so it could not be tested or used. Using the thread pool in MariaDB would have been somewhat unfair.
If you want to understand the impact of thread pooling on performance, you can check out these two previous articles:
Second Test: OLTP Read-Write Test
This graph is similar to the previous test. MySQL 5.6 and MariaDB 10.0 both show a considerable drop in performance compared to the 5.5 versions, roughly a 10% decline under high load.
It is a well-known fact that MySQL 5.5 suffers performance degradation under high load due to synchronous flush operations.
The response time graph looks relatively better:
This is good news. The 5.5 version had much worse response times with 64 threads or more. MySQL 5.6 and MariaDB 10.0’s adaptive flush algorithm seems to be working well.
There is another issue here: if you use multiple buffer pool instances, you will see higher write latency. In the results above, the read-only test used 16 buffer pools, while the read-write test used only 1.
Conclusion:
MySQL-5.6 performs worse than its predecessor, especially under high concurrency. This does not match the test results published by Oracle. I can only speculate as to why the results differ so much; my guess is Oracle’s closed-source thread pool and testing on larger machines contributed to the discrepancy.
When using a single buffer pool, there is no need to worry about write latency issues. Additionally, MySQL 5.6 allows redo logs up to 512G, which can reduce synchronous flush operations.
The scripts used for this test can be accessed at the following address:
http://bazaar.launchpad.net/~ahel/maria/mariadb-benchmarks/revision/20
Feel free to rerun these benchmarks and share your results with us.
Article reposted from: http://www.oschina.net/question/12_90065
http://blog.mariadb.org/sysbench-oltp-mysql-5-6-vs-mariadb-10-0/
