MySQL的性能:我该买 SSD 存储,还是更多的内存呢?[英文]

鉴客 发布于 2010/04/08 23:43
阅读 1K+
收藏 3

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.


  • 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.


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






虽然规模来解决传统上一直为MySQL的流行,这很有趣,看看有什么空间,我们现在必须扩大规模 - 低价内存,快速存储,更好的电源效率。当然有很多选择了 - 我一直在会议上有关客户/周使用的Fusion - IO卡。一个有趣的选择,我已经看到人们作出不过,购买的是SSD的时候,他们仍然有很多页的读/秒 - 我宁愿买内存代替,并使用写入存储设备。


* Percona - XtraDB - 9.1发布
* Sysbench OLTP工作负载为80万行(约18GB的数据+索引值)
* XFS文件系统安装与nobarrier选项。
Ø RAID10与BBU的超过800盘
Ø英特尔的SSD X25还- E的32GB的
Ø FusionIO 320GB的刚果解放运动

首先,我们对RAID10 存储测试,以确定基线。 Y轴是交易/秒(更多更好),X轴是 innodb_buffer_pool_size大小:



*这架B 箭头其中数据刚刚开始超过缓冲池的大小。这是许多客户最痛苦的一点 - 因为虽然内存仅下降10%〜2.6倍的性能下降!这通常 在生产相匹配的“描述的最后一周一切都很好..但它只是越来越缓 慢!“。我 建议增加内存是迄今为止最好的办法是在这里。
*政府箭头可显示的数据大约是 3倍的缓冲池。这是一个有趣的可放大 - 因为你可能无法辩解的内存成本,而是一个SSD的可能是一个很适合:


凡在C箭头,在这个图的融合,提高投入产出卡约5倍(或与英特尔的SSD性能的2 倍)。要获得同等的改善记忆,你将需要 添加一个改善60%以上5倍内存或- 260%更多的内存。想象的情况下你的C点是当你有100GB的RAM和 32GB的数据。比它得到有趣:

*你能很容易地添加另一 个32G条内存(是你的内存插槽已经满?)
*您的预算是否允许安装SSD的卡? (您可能还需要超过1,因为它们都是相对比较小。已有的市场,英特尔的SSD设备使用8用具)。

这里的工作量是旨在使尽可能多的数据尽可能热,但我猜主要的教训是,不要低估了你的“积极组数据”的大小。对于有些人谁只是将数据追加到表中的一些记录,可能只需要一个很小的百分比排序 - 但在其他情况下,它可以大大提高。如果你不知道你的工作集是 - 问我们!

重要说明:本图和这些结果只适用于sysbench制服。在您的特定工作点B和C可 能位于不同。