MySQL Performance Tuning memory or SSD?

When a traditional outward scaling method becomes popular for MySQL, let's see what we have to expand (cheap memory? Quick storage? Better power supply efficiency ?) It will become very interesting. There are indeed many options here-I may encounter a customer using the fushion-io card every week. However, I saw an interesting option for them-they chose to buy an SSD and they could still read many pages per second (at this time, I would rather choose to purchase memory instead ), the storage drive is used for "write operations.

 

Here, I propose a few reference criteria for you to confirm whether it is the case I mentioned above:

• Percona-XtraDB-9.1 release
• The OLTP of sysbench (Open-Source Performance Testing Tool) has 10 million lines of "workload" (roughly equivalent to 18 GB of Data + indexes)
• Install the XFS file system with the nobarrier Option
• Test run:

1. there are more than 8 bbu raid 10 hard disks (the so-called bbu, the Community explains that in the case of power failure, data can be cached for 72 h. When the power supply of the machine is normal, write Data to the disk from the cache)

2. Inter SSD X25-E 32 GB

3. fushionio 320 gb mlc [1]

• For each test, the buffer pool is set to 2 GB to 22 GB during running (to compare performance with suitable memory)
• Hardware configuration:

Dell poweredger900

4 quadcoreintel (r) Xeon (r) CPU e7320 @ 2.13 GHz (16 cores in total)

32 GB of RAM

Raid10 on 8 disks 2.5 "15 K RPMs

Fusionio 160 gbslc

Fusionio 320 gbmlc

OS:

Centos 1, 5.5

XFS filesystem

At the beginning, we tested the storage of RAID 10 to create a baseline. The Y axis is the data transmitted per second (the higher the transmission rate, the better ). The X axis is the size of innodb_buffer_pool_size.

I have selected three interesting points in this test.

• A: When the data fully matches the buffer pool size (optimal performance ). Once you reach this point, it is like a further increase in memory. It is important to understand this information.
• The B coordinate occurs when the data size exceeds the buffer pool size at the beginning. This is the biggest headache for many users. Because when the memory drops by only 10%, the performance is reduced by 2.6 times. In the product, this usually deals with such a statement-"Everything looks okay last week, but it is getting slower and slower !". I would like to suggest that you increase the memory is the best practice so far, in this case.
• Point C displays about three times the size of the buffer pool. It is an interesting point. Since you cannot calculate the memory cost (You may not know how much it will take to buy memory for future bottlenecks), it is more appropriate to buy an SSD.

At point C, in this figure, a fusion-iocard can improve performance by up to 5 (if you use interl SSD, it will be twice ). To achieve the same performance improvement by increasing the memory, you will need to increase the memory by more than 60%-or if you want to increase the performance by 5 times, you need to increase the memory size by more than 260%. Imagine a production environment where your C-point (assuming that the system is getting slower and slower) is generated when you use 32 gb ram and GB of data. Then it will become interesting:

• You can simply add another 32 gb ram (your memory slot and are fully occupied ?)
• Does your budget allow you to install an SSD card? (You may still need more than one, because they are relatively small. There are already some household appliances on the market, they use eight intel SSD devices ).
• Is the performance improvement of 2 or 5 times sufficient to meet your needs? If you can afford all the memory you need, I think you may get better performance.

The test here is designed to ensure as much "hot" data as possible, but I guess the most important lesson here is not to underestimate the size of your "Activity Set" data. For example, some users only append data to some sort log tables, which may only require a small percentage, but in other cases, it may be considered to take up a large percentage (for example, log records are relatively frequent ).

Important Note: This figure and these conclusions are only verified in sysbenchuniform. In your special work scenarios, the possible distribution locations of point B and point C are different.

Source result data in the figure:

Reference:

[1]: SSD hard drive uses two types of nand flash memory: single-level unit (SLC) and multi-level unit (MLC ). The difference between the two is the data volume stored per unit. SLC stores 1 bit and MLC stores 2 bit. The key is that SLC and MLC occupy the same chip area. Therefore, MLC can have twice the capacity at the same price.

The erasure performance of SLC and MLC is the same. The read performance of MLC Flash Memory takes two times longer, and the write performance takes four times longer.

The biggest advantage of SLC lies not in its good performance but in its long service life.

PS: SLC is generally used on enterprise-level SSDS because of its fast speed and long service life. While MLC is mostly used in consumer-level markets, such as workstation. Fusion-io developed a new smlc (Single Mode Level cell) technology for managing MLC flash memory, combining enterprise reliability of SLC technology with consumer-level MLC flash memory. The bandwidth of smlc is similar to that of SLC, and its durability and write performance are comparable to that of SLC, and the cost is much lower than that of traditional SLC solutions.