While a scale-out solution has traditionally been popular for MySQL, it’s interesting to see what room we now have to scale up – cheap memory, fast storage, better power efficiency.Â There certainly are a lot of options now – I’ve been meeting about a customer/week using Fusion-IO cards.Â One interesting choice I’ve seen people make however, is buying an SSD when they still have a lot of pages read/second – I would have preferred to buy memory instead, and use the storage device for writes.
Here’s the benchmark I came up with to confirm if this is the case:
To start with, we have a test on the RAID10 storage to establish a baseline.Â The Y axis is transactions/second (more is better), the X axis is the size of innodb_buffer_pool_size:
Where the C arrow was, in this graph a Fusion-IO card improves performance by about five times (or 2x with an Intel SSD).Â To get the same improvement with memory, you would have needed to add 60% more memory -or- 260% more memory for a 5x improvement.Â Imagine a situation where your C point is when you have 32GB of RAM and 100GB of data.Â Than it gets interesting:
The workload here is designed to keep as much of the data hot as possible, but I guess the main lesson here is not to underestimate the size of your “active set” of data.Â For some people who just append data to some sort of logging table it may only need to be a small percentage – but in other cases it can be considerably higher.Â If you don’t know what your working set is – ask us!
Important note: This graph and these results are valid only for sysbench uniform. In your particular workload the points B and C may be located in differently.
|Buffer pool, GB||FusionIO||Intel SSD||RAID 10|