Should I buy a Fast SSD or more memory?

 

以下摘抄国外人的性能测试，大致结论如下：

使用mysql的场景下，

1. A点： 当所需要的数据都在pool中的时候，fusion io, ssd, hdd的性能基本一样，数据都在内存中，fusion io在自己的cache中。 

2. B点： 当10%的数据在pool之外的情况下，数据读取牵涉到硬盘的io，这个时候tps迅速减少，最高值是当前tps的2.6倍。此时使用ssd，fusion io会使tps有比hdd的提升。 相比而言此时的fusion和ssd对于tps的提升量比较，fusion超ssd 1.3倍。

3. C点：此时tps较少，使用fusion和ssd对于当前tps有大幅度提升。fusion 5倍，ssd 2.2倍。

可以这样理解，若此时pool的大小已经很大，再添加已经很困难了。这个时候用fusion和ssd都是值得的选择，但是成本上需要考虑。 但如果这个时候，pool很小，那么增加pool的大小可能比使用fusion和ssd要来的实惠。

 

 

 

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:

• Percona-XtraDB-9.1 release
• Sysbench OLTP workload with 80 million rows (about 18GB worth of data+indexes)
• XFS Filesystem mounted with nobarrier option.
• Tests run with:
  • RAID10 with BBU over 8 disks
  • Intel SSD X25-E 32GB
  • FusionIO 320GB MLC
• For each test, run with a buffer pool of between 2G and 22G (to test performance compared to memory fit).
• Hardware was our Dell 900 (specs here).

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:

Let me point out three interesting characteristics about this benchmark:

• The A arrow is when data fits completely in the buffer pool (best performance). It’s important to point out that once you hit this point, a further increase in memory at all.
• The B arrow is where the data just started to exceed the size of the buffer pool.  This is the most painful point for many customers – because while memory decreased by only ~10% the performance dropped by 2.6 times!  In production this usually matches the description of “Last week everything was fine.. but it’s just getting slower and slower!”.  I would suggest that adding memory is by far the best thing to do here.
• The C arrow shows where data is approximately three times the buffer pool.  This is an interesting point to zoom in on – since you may not be able to justify the cost of the memory, but an SSD might be a good fit:

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:

• Can you easily add another 32G RAM (are your memory slots already filled?)
• Does your budget allow to install SSD cards? (You may still need more than one, since they are all relatively small.  There are already appliances on the market which use 8 Intel SSD devices).
• Is a 2x or 5x improvement enough?  There are more wins to be had if you can afford to buy all the memory that is required.

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.

Raw results:

Buffer pool, GB	FusionIO	Intel SSD	RAID 10
2	450.3	186.33	80.67
4	538.19	230.35	99.73
6	608.15	268.18	121.71
8	679.44	324.03	201.74
10	769.44	407.56	252.84
12	855.89	511.49	324.38
14	976.74	664.38	429.15
16	1127.23	836.17	579.29
18	1471.98	1236.9	934.78
20	2536.16	2485.63	2486.88
22	2433.13	2492.06	2448.88