This post is the following part #2 of the previous one - in fact Vadim's comments bring me in some doubts about the possible radical difference in implementation of AIO vs normal I/O in Linux and filesystems. As well I've never used Sysbench for I/O testing until now, and was curious to see it in action. From the previous tests the main suspect point was about random writes (Wrnd) performance on a single data file, so I'm focusing only on this case within the following tests. On XFS performance issues started since 16 concurrent IO write processes, so I'm limiting the test cases only to 1, 2, 4, 8 and 16 concurrent write threads (Sysbench is multi-threaded), and for AIO writes seems 2 or 4 write

This article is following the previously published investigation about I/O limitations on Linux and also sharing my data from the steps in investigation of MySQL/InnoDB I/O limitations within RW workloads..

So far, I've got in my hands a server with a Fusion-io card and I'm expecting now to analyze more in details the limits we're hitting within MySQL and InnoDB on heavy Read+Write workloads. As the I/O limit from the HW level should be way far due outstanding Fusion-io card performance, contentions within MySQL/InnoDB code should be much more better visible now (at least I'm expecting ;-))

But before to deploy on it any of MySQL test workloads, I want to understand the I/O limits I'm hitting on the

It was a long time now that I wanted to run some benchmark tests to understand better the surprises I've met in the past with Linux I/O performance during MySQL benchmarks, and finally it happened last year, but I was able to organize and present my results only now..

My main questions were:

• what is so different with various I/O schedulers in Linux (cfq, noop, deadline) ?..
• what is wrong or right with O_DIRECT on Linux ?..
• what is making XFS more attractive comparing to EXT3/EXT4 ?..

Forget to say, I've also tested PostgreSQL 8.3.7 during the last benchmark serie with dbSTRESS!

A big surprise - if two years ago on the same workload PostgreSQL was two times faster (see: http://dimitrik.free.fr/db_STRESS_BMK_Part2_ZFS.html ), now it's MySQL 5.4 outperforming PostgreSQL!

• Read-Only workload: MySQL is near two times faster now! (13.500 TPS vs ~7.000 TPS for PostgreSQL)
  
  
• Read+Write workload: MySQL performs as well or better (7.000-8.000 TPS vs 6.000-7.000 TPS for PostgreSQL)

For more details: http://dimitrik.free.fr/db_STRESS_MySQL_540_and_others_Apr2009.html#note_5443