I used a simple workload with sysbench to determine the rate at which InnoDB can read blocks from disk. The workload is read-only and each query fetches 1 row by PK. The workload was IO-bound with a 2G InnoDB buffer pool and 32G database. Storage was fast courtesy of buffered IO and enough RAM to cache the database in the OS filesystem cache.
I ran single-threaded performance tests to compare MySQL using releases 4.0.30, 5.0.85, 5.1.63 and 5.6.11. On my simple tests 4.0.30 is almost 1.5X faster than 5.6.11. I think it is important to reduce these regressions. Maybe this is an area in which the forks (MariaDB, Percona) will lead the way? I previously opened bug 68825 for this and will update it with the results I report here.
For some workloads the InnoDB performance model is very simple. It must flush dirty pages as fast as storage will allow. With a few more bug fixes InnoDB in MySQL 5.6 should be able to do that. The performance model becomes a bit more complicated when you consider that there are two reasons for sustaining a high rate of flushing dirty pages.
I compared the performance of MySQL 5.6.11 versus 4.0.30 using a read-only workload with sysbench. Performance was much better for 5.6.11 in most cases. At low-concurrency MySQL 4.0 was a bit faster. MySQL 5.6.11 was faster at high-concurrency and when doing many page reads per second. The product has improved a lot since I started using MySQL.
MySQL 5.6.11 is here with many useful bug fixes. Not so good news - you won't be able to read about those bugs beyond the brief text in the release notes as many of the bug reports are behind the support paywall. If you have lots of time to spare maybe you can read diffs in the source tree on launchpad.
I spent more time looking at the performance regressions in MySQL 5.6 for single-threaded read-only workloads and I can get 5.6 closer to 5.1 by disabling a few options when building MySQL 5.6 -- optimizer tracing & profiling. This is my summary of things to do if you want to avoid some of the performance regressions I encountered while doing update-only and read-only performance tests for single-threaded and high-concurrency workloads:
This post describes MySQL 5.6 performance for a workload that is update-only and single-threaded. My previous post was for a read-only workload. I think there is a performance regression in MySQL 5.6 for a workload dominated by fast queries even when the performance schema is disabled. I filed bug 68825 for this. Hopefully my next post will identify the sources of that regression.
LinkBench is a benchmark that models the social graph OLTP workload at a busy social networking site. It is implemented for MySQL but there is abstraction layer so it should be possible to implement drivers for other products like HBase, Cassandra, MongoDB and Postgres. This has been a long running project that was finished by an awesome intern. I am happy to share the announcement and source code.