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.

flush list

The flush list maintains pages in the order in which they were made dirty (see below for the fine print). This list only has dirty pages. Good reasons to flush dirty pages from the innodb_fast_free_list but my patch is no longer needed. The big change in 5.6 is that most of this flushing should now be done by a background thread – buf_flush_page_cleaner_thread.

Configuration

In MySQL 5.6 the rate of flushing for the LRU and flush lists should be determined by configuration variables – innodb_io_capacity for the flush list and innodb_lru_scan_depth for the LRU. This is a big change from earlier MySQL releases because innodb_lru_scan_depth is new and prior to 5.6 furious flushing would frequently be done.

In MySQL 5.6 these variables are the peak page write rates per buffer pool instance and with the exception of shutdown these rates should not be exceeded. Prior to 5.6 InnoDB would flush as fast as possible when needed and that rate could exceed innodb_io_capacity. I use furious flushing to describe this.

This is good news for getting predictable performance assuming InnoDB is able to sustain these configured rates. There are a few more bugs to get fixed in 5.6 before that is true. Fortunately the bugs are getting fixed quickly and many are fixed in 5.6.12. But I wish there were but one variable to set the page flush rate rather than two.

Benchmarks

There are a few basic performance tests to determine whether InnoDB can sustain the desired page flush rates. I use sysbench with a workload that updates 1 row by primary key per transaction and then run it for two configurations — cached and IO-bound. The doublewrite buffer is enabled but fsync-on-commit and the binlog are disabled. The workload has no data contention. The workload & configuration make it as easy as possible for InnoDB to flush pages. If it can’t keep up here then it will have more trouble elsewhere.

Cached

The cached configuration uses a buffer pool much larger than the test tables. For this workload all of the flushing should be done from the flush list. Whether InnoDB needs to sustain the maximum page flush rate (innodb_buffer_pool_instances X innodb_io_capacity) is determined by the size & number of the redo logs and the rate at which redo logs are written. For the tests reported here I did not vary the size & number of redo logs to confirm the peak flush rate could be sustained.

These list the rate of updates/second. One interesting result is that for both 4.0.30 and 5.6.11 the rate at high-concurrency with all data in one table is better than the rate for a workload that uses 8 tables. I hope that problem will be fixed in 5.6.12 (see bugs my advice for getting peak performance from 5.6.

updates/second using 1 table
   1       2      4      8     16     32  clients
5411   10342  20268  36473  19616   9718  4.0.30
4372    8347  16757  34656  60680  70662  5.6.11

updates/second using 8 tables
    8     16     32     64    128    256  clients
33580  18501   9875   7367   6659   6012  4.0.30
34328  25716  24633  23828  23790  23114  5.6.11
IO-bound

The IO-bound configuration uses a buffer pool much smaller than the test tables and most of the flushing should be done from the LRU list. The buffer pool is 2G and the tables are ~32G total. The peak flush (innodb_buffer_pool_instances X innodb_lru_scan_depth) is one limit on the peak update rate.

I ran IO-bound tests to compare MySQL 4.0.30 and 5.6.11 and was disappointed by the results for 5.6.11 as the rate was much less than the peak LRU flush rate. I filed bug 69170 and learned it was already fixed in 5.6.12. The results below are the rate of updates/second for 4.0.30, 5.6.11 and 5.6.11 with my hack to fix bug 69170. The 5.6.11 configuration used innodb_buffer_pool_size=2G, innodb_buffer_pool_instances=4 and innodb_lru_scan_depth=4000.

For both the 1 table and 8 table configurations the peak LRU flush rate should be 16,000 and I want the peak rate of updates/second to be close to 16,000 when concurrency grows. Because of bug 69170 it was far from the peak for the 1 table case and my hack fixes that. So I expect that InnoDB in 5.6.12 will be able to sustain much higher QPS for workloads that are bound by LRU flushing. The results with 5.6.11 for 8 tables are much worse than for 1 table and I think this suffers from the bugs listed in the cached section above. I will wait for 5.6.12 to confirm.

updates/second using 1 table
   1     2      4      8     16     32  clients
2535  3865   5554   6729   6759   5825  4.0.30
2818  3740   5026   5359   5557   5525  5.6.11
3070  5893  10940  15420  15514  13628  5.6.11+hack

updates/second using 8 tables
   8    16     32     64    128    256  clients
4626  4522   4253   4076   3972   3941  4.0.30
7565  6234   6109   6144   6125   6271  5.6.11