MySQL Performance Blog

As I’ve wrote few times using multiple key caches is a great way to get CPU scalability if you’re using MyISAM. It is however very annoying – this feature really looks half baked to me.

The problem with multiple key caches and mapping of tables to the different files is – there is no way to see the existing key caches, their mapping and stats. The only thing you can access is key cache parameters – via structured variables
[read more...]

In benchmarks passion (see my two previous posts) I managed to setup all three devices (RAID was on board; Intel X25-E SSD connected to HighPoint controller, FusionIO card) on our working horse Dell PowerEdge R900 (btw, to do that I had to switch from CentOS 5.2 to Ubuntu 8.10, as CentOS was not able to start with attached SSD card to HighPoint controller) and along with other tests I ran tpcc-like IO-bound workload on all devices.

For tests I used MySQL 5.4/InnoDB, and all other parameters are the same from previous posts (100W, buffer_pool 3GB). Filesystem – XFS mounted with nobarrier option.

Graphical results are here

and average results:

RAID10 – 7439.850 TPM
SSD – 10681.050 TPM
FusionIO – 17372.250 TPM

However what should be noted – both SSD and FusionIO are run in “non-durable” mode, that is you may lose some transactions in case of power outage (see my post http://www.mysqlperformanceblog.com/2009/03/02/ssd-xfs-lvm-fsync-write-cache-barrier-and-lost-transactions/).

While results for SSD (note it is single device, in comparison to RAID 10 on 8 disks) and FusionIO are impressive, it is worth to consider price/performance parameter.

Here is my very rough calculation:
For RAID 10 we use 8 73GB SAS 2.5″ 15K RPM disks, with price 190$ per disks it gives us 1520$ for 292GB useful space, or ~ 5.2$ per GB.
For SSD I can get 32GB card for 390$, which is ~12.1$ per GB
For FusionIO I really not sure what is price (it was given as only for tests), but quick googling gave me 30$ per GB, so for 160GB card gives 4800$.

Now simple dividing TPM on price of IO system, we have
RAID 10 – 4.8 TPM / $
SSD – 27 TPM / $
FusionIO – 3.6 TPM / $

Please note that price of transaction is not the main criteria to consider, as total TCO for systems with SSD may be much cheaper (considering you need less servers, less space, less power). Also worth to consider that SSD is only 32GB space and to have the same space as FusionIO we need 4 cards (but it still will be cheaper than FusionIO), but it also may improve performance as such setup will be able to handle IO requests in parallel.

As continue to my benchmarks http://www.mysqlperformanceblog.com/2009/04/30/looking-on-54-io-bound-benchmarks/ on 5.4 I tried in-memory load (basically changed buffer pool from 3GB to 15GB, and database size is 10GB). The results are on the same spreadsheet http://spreadsheets.google.com/ccc?key=rYZB2dd2j1pQsvWs2kFvTsg&hl=en#, page CPUBound.

I especially made short warmup (120 sec) and long run (2700sec) to see how different versions go through warmup stage.

The graph is

In default mode I would say XtraDB performs almost the same as 5.4, but dips are a bit worse than in 5.4.

Now about dips – all of them are caused by InnoDB checkpoint activity, InnoDB is doing intensive flushing of buffer_pool pages and that basically causes stall for some period of time in all user processes.
In XtraDB we have special mode adaptive_checkpoint, you see result in this mode. While max performance is worse, there is no dips, and average performance is better.

If Sun Perfomance engineers read this – I call attention to this problem and do not ignore it if Sun started to make changes to InnoDB anyway.

If InnoDB engineers read this and are interested – then, yes, we are ready to provide adaptive_checkpoint patch under BSD license.

Also I was asked what if we set small innodb_max_dirty_pages_pct , will not that help with such dips – you can see results for 5.4 with innodb_max_dirty_pages_pct=15. There really no dips, but average performance is not acceptable. I also tried innodb_max_dirty_pages_pct=30, but results in this case similar to usual 5.4, so I do not show them.