I returned to a long unfinished change to improve the efficiency of the code in InnoDB that finds free pages when the buffer pool is full. It took me a day to get the change ready for benchmarks and then I tested it with sysbench, a 1G InnoDB buffer pool and a 16G database. The storage for this test was very fast, it was the OS filesystem cache. The test server has 12 real cores and 24 with HT enabled. Clients run on a separate server in the same rack. The test was run with 8, 16, 32, 64 and 128 concurrent clients.

The results from my change were great. Peak QPS from the change increased by more than 2X from 46000 to 110000. As most queries missed in the InnoDB buffer cache this means that MySQL 5.1 was able to do more than 100k IOPS assuming very fast storage. This is relevant because flash devices are another form of very fast storage that are a bit more persistent than the OS filesystem cache. I didn't think this was possible from MySQL 5.1.

In the graphs that follow:

• fb.5152.new - MySQL 5.1.52 + the Facebook patch + faster free pages
• fb.5152.old - MySQL 5.1.52 + the Facebook patch
• orig.5152 - unmodified MySQL 5.1.52
• orig.565m8 - unmodified MySQL 5.6.5m8
• orig.586m8 - unmodified MySQL 5.6.6m8 with 4kb InnoDB pages

Then I tested MySQL 5.6.5 m8 and wasn't so impressed with my change. The peak for it was 147000 QPS. The best results from orig.565m8 and fb.5152.new were limited by user CPU. So a bigger and faster server will have a higher peak. We have come a very long way from MySQL 4.0.

I then repeated the test using innodb_page_size=4096 for MySQL 5.6.6m8 and the peak QPS increased to 162k. That is amazing and I think 200k is within reach.