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 threads
may be more than enough as each thread by default is managing 128
AIO write requests..
Few words about Sysbench …
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 lower levels (if
any) - first on the card itself, and then on the filesystem
levels..
NOTE …
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 ?..
There were already several posts in the past about impact on
MySQL performance when one or another Linux I/O layer feature was
used (for ex. Domas about I/O schedulers, Vadim regarding TPCC-like …
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