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.

1 2 Next Page