To continue fun with FusionIO cards, I wanted to check how MySQL / InnoDB performs here. For benchmark I took MySQL 5.1.42 with built-in InnoDB, InnoDB-plugin 1.0.6, and XtraDB 1.0.6-9 ( InnoDB with Percona patches).
As benchmark engine I used tpcc-mysql with 1000 warehouses ( which gives around 90GB of data + indexes) on my workhourse Dell PowerEdge R900 ( details about box ).
On storage configuration: FusionIO 160GB SLC and 320GB MLC cards are configured in software RAID0 to store InnoDB datafiles. For InnoDB logs and system tablespace I used partition on regular RAID10 with regular hard drives, here I followed Yoshinori Matsunobu’s recommendations http://yoshinorimatsunobu.blogspot.com/2009/05/tables-on-ssd-redobinlogsystem.html and taking fact that FusionIO is not perfect for sequential writes https://www.percona.com/blog/2010/01/11/fusionio-320gb-mlc-benchmarks/
Full results I put on page https://www.percona.com/docs/wiki/benchmark:fusionio:mysql:start, here are my thoughts and interesting facts.
First, chart with results for InnoDB vs InnoDB-plugin during runs (values are in new order transactions per minute, more is better) :
As you see InnoDB-plugin is doing much better here, it allows to utilize multiple IO threads,
which as we saw ( https://www.percona.com/blog/2010/01/11/fusionio-320gb-mlc-benchmarks/ ) is necessary to get most throughput from FusionIO.
Also you may see from graph some waves for InnoDB-plugin. Here we observe innodb_adaptive_flushing in action (which is ON by default), and I think innodb_adaptive_flushing in InnoDB-plugin is not quite balanced, it may do overaggressive flushing, when it is not necessary.
But looking on CPU stats (see graph later), I guess InnoDB-plugin spends most time in buffer_pool mutex, contention here is not fully resolved yet in InnoDB-plugin.
Now, let’s take XtraDB. In additional to multiple IO threads, we have patch to decrease contention on buffer_pool mutex, plus separate purge thread. Also we use different adaptive_checkpoint algorithm.
The results are:
So I guess buffer_pool improvements play here for XtraDB, and looking on summary result:
InnoDB-plugin is 1.6x times better InnoDB, and XtraDB is 1.7x times better InnoDB-plugin.
Now on CPU usage and disk utilization.
Disk throughput:
CPU (user) usage:
Even with improvements, XtraDB performs less 150MB/s in disk writes (from benchmarks we
saw FusionIO can do much more) and with 45-50% of idle CPU.
I assume we still see significant contentions inside XtraDB, and there big room for improvements. As for InnoDB-plugin, I’d wish InnoDB team makes some actions on buffer_pool mutex problem.
Finally I wanted to check what if we put innodb transactional logs and system tablespace on FusionIO also, there is graph for that:
It is not so bad, with final result 23038.283 NOTPM, it is only about 12% worse than with logs on separate partition.
And to make reference point, I run the same but with all files on RAID10 with regular disks,
the graph is there:
with final result: 2873.783 NOTPM ( about 88% worse than all files on FusionIO)
To summarize
