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|
Percona’s widely read Percona Data Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.
Besides specific database help, the blog also provides notices on upcoming events and webinars.
Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below.