Following my series of posts on testing different SSD, in my last post I mentioned that SATA SSD performance is getting closer to PCIe cards. It really makes sense to test it under MySQL workload, but before getting to that, let me review the same workload on Fusion-io ioDrive PCIe card. This is yet previous generation of Fusion-io cards, but this is the one that has biggest installation base.
Continue reading
Following my previous benchmark of Samsung 830, today I want to show results for STEC MACH16 SATA card, 200GB size, this card is based on SLC, and regarding STEC website, it is an enterprise grade storage.
Continue reading
As ext4 is a standard de facto filesystem for many modern Linux system, I am getting a lot of question if this is good for SSD, or something else (i.e. xfs) should be used.
Traditionally our recommendation is xfs, and it comes to known problem in ext3, where IO gets serialized per i_node in O_DIRECT mode (check for example Domas’s post)
Continue reading
I have a chance to test a system with Intel 320 SSD drives (NewRelic provided me with an access to the server), and compare performance with SAS hard drives.
Continue reading
I have been following Virident for a long time (e.g. http://www.mysqlperformanceblog.com/2010/06/15/virident-tachion-new-player-on-flash-pci-e-cards-market/). They have great PCIe Flash cards based on SLC NAND.
I always thought that Virident needed to come up with an MLC card, and I am happy to see they have finally done so.
At Virident’s request, I performed an evaluation of their MLC card to assess how it handles MySQL workload. As I am very satisfied with the results, I wish to share my findings in this post.
Continue reading
I wrote about Intel 320 SSD write performance before, but I was not satisfied with these results.
Somewhat each time on Intel 320 SSD I was getting different write performance, so it made me looking into this with details.
So let’s run experiment as in previous post, this is sysbench fileio random write on different file size, from 10GiB to 140GiB with 10GiB step. I use ext4 filesystem, and I perform filesystem format before increasing filesize.
The results are pretty much as in previous post, the throughput drops as we increase filesize:
However, there is when interesting stuff begin. Now when we run the same iterations again, the result will look like:
As you see, second time the throughput is much worse, even on medium size files. Just after 50GiB size, throughput gets below 40MiB/sec And this is with the fact, that I perform filesystem format before each run.
This leads me to conclusion that write performance on Intel 320 SSD is decreasing in time, and actually it is quite unpredictable in each given point of time. Filesystem format does not help, and only secure erase procedure allows to return to initial state. There are commands for this procedure for reference.
hdparm --user-master u --security-set-pass Eins /dev/sd$i
hdparm --user-master u --security-erase Eins /dev/sd$i
Discussing this problem with engineers working with Intel 320 SSD drives I was advised to use artificial space provisioning, about 20%. Basically we create partition which takes only 80% of space.
So let’s try this. The experiment the same as previously, with difference that I use 120G partition, and max filesize is 110GiB.
You can see that throughput in first iteration is basically the same as with full drive, but second iteration performs much better. Throughput never drops below 40MiB/sec, and stays on about 50MiB/sec level.
So, I think, this advise to use space provisioning is worth to consider if you want to have some kind of protection and maintain throughput on some level.
Raw results and used scripts as always you can find on our Benchmarks Launchpad
While PCI-e Flash cards show great performance, I am often asked about alternatives, as price for PCI-e cards is still significant and not acceptable for small companies and startups.
Intel 320 SSD appears to be a popular drive with a quite acceptable price.
I wrote about write performance of these cards, and now let’s take look on a random read workload.
I used a Cisco UCS C250 as base hardware, comparing in it:
For simulating the workload I used sysbench’s fileio random reads. Scripts and raw results available on Launchpad.
Let’s see throughput results:
| threads | Intel 320 | Intel 320 2 strip | RAID10 | ratio Intel 320 / RAID10 | ratio Intel 320 2 strip / RAID10 |
|---|---|---|---|---|---|
| 1 | 30.27 | 31.18 | 3.75 | 8.07 | 8.31 |
| 2 | 55.18 | 60.49 | 6.98 | 7.91 | 8.67 |
| 4 | 95.13 | 112.85 | 12.10 | 7.86 | 9.33 |
| 8 | 143.58 | 191.64 | 19.05 | 7.54 | 10.06 |
| 16 | 174.75 | 277.70 | 26.70 | 6.54 | 10.40 |
| 32 | 174.60 | 351.84 | 32.90 | 5.31 | 10.69 |
And response times:
| threads | Intel 320 SSD | Intel 320 SSD strip | RAID | ratio RAID/Intel 320 | ratio RAID/Intel 320 strip |
|---|---|---|---|---|---|
| 1 | 0.53 | 0.56 | 6.13 | 11.57 | 10.95 |
| 2 | 0.72 | 0.59 | 7.27 | 10.10 | 12.32 |
| 4 | 0.89 | 0.74 | 10.07 | 11.31 | 13.61 |
| 8 | 1.24 | 0.95 | 15.63 | 12.60 | 16.45 |
| 16 | 1.76 | 1.38 | 25.52 | 14.50 | 18.49 |
| 32 | 3.33 | 2.15 | 47.35 | 14.22 | 22.02 |
As conclusion, this card provides great read performance. A single card provides 5-8x better throughput and 10-14x better response time. Striping helps to increase throughput in 8-10x and response time in 10-22x.
While there are questions about write performance (see my previous post), I think this card is very suitable for read-intensive tasks, where you can expect significant improvements.
I was advised that new drivers and new firmware for FusionIO cards improve performance and stability and it is recommended to review results I’ve got about year ago.
Using the same methodology and the same box as for Intel 320 SSD, I run random writes benchmarks for FusionIO 320GB MLC card (I do not have the card I had year ago on hands).
Information about system, FusionIO drives are raw results are on Benchmarks Launchad
First graph is to show timeline for different filesizes. Benchmark starts just after formatting card and filesystem and runs around 1 hour with measuring throughput each 10 sec.
Interesting to see the same pattern as for Intel 320 SSD: the throughput starts at max, then drops down and after some peak stabilizes.
1500 sec seems enough to get stable line for all filesizes. If we take slice of data after 1500 sec and build summary space->throughput graph, it looks like:
So we still have decent declining line. The throughput drops from 500MiB/sec at peak to 110-120MiB/sec at full capacity.
And to have some fun with R/ggplot2 graphs, let’s build graph to compare FusionIO and Intel 320 SSD (with results from previous post)
While I like performance provided by PCI-E cards like FusionIO or Virident tachIOn, I am often asked about SATA drives alternatives, as price of PCI-E cards often is barrier, especially for startups. There is wide range of SATA drives on market, and it is hard to pick one, but Intel SSD are probably one of most popular, and I’ve got pair of Intel 320 SSD 160GB to play with it.
Probably most interesting characteristic for SSD for me is Random Write throughput in correlation with file size , as it is known that the write throughput declines when you use more space.
In this post I will test (using sysbench fileio) single Intel 320 SSD card with different filesize ( from 10 to 140 GiB, with step 10GiB). Filesystem is XFS and IO blocksize is 16KiB.
I posted all scripts and results on our Launchpad project where you can find
I used next methodology for testing: format xfs, run 1 hour random write test with measuring throughput each 10 sec.
The results are bit tricky to analyze, as throughput performs in this way (for 100GiB filesize)
You can see that just after format throughput starts with 80MiB/sec, then drops to 10MiB/sec and after about half of hour stabilizes on 30MiB/sec level.
We can build the same graph (time -> throughput) for all filesizes we have:
where you can see that throughput drops from 100 MiB/sec for 10GiB file to 15MiB/sec for 140GiB file.
For reference I added result from similar benchmark for RAID10 over 8 regular spinning SAS 15K disks, which is around 23MiB/sec.
From graph we see that all results are stabilized after 2500 sec, and if we get slice of data after 2500 sec, the summary graph ( size -> throughput) looks like:
This graph allows to get idea what is throughput for given filesize much easier.
E.g. for 70GiB files, we have 40MiB/sec and for 120GiB file, it is 20MiB/sec.
Some conclusions from these results:
In final I want to give credit to R projects and ggplot2 which are very helpful for graphical analyzing of data.
Crossposted from MySQLPerformanceBlog.
This is to follow up on my previous post and show the results for MySQL 5.5.8 and Percona Server on the fastest hardware I have in our lab: a Cisco UCS C250 server with 384GB of RAM, powered by a Virident tachIOn 400GB SLC card.
To see different I/O patterns, I used different innodb_buffer_pool_size settings: 13G, 52G, an 144G on a tpcc-mysql workload with 1000W (around 100GB of data). This combination of buffer pool sizes gives us different data/memory ratios (for 13G – an I/O intensive workload, for 52G – half of the data fits into the buffer pool, for 144G – the data all fits into memory). For the cases when the data fits into memory, it is especially important to have big transactional log files, as in these cases the main I/O pressure comes from checkpoint activity, and the smaller the log size, the more I/O per second InnoDB needs to perform.
So let me point out the optimizations I used for Percona Server:
For MySQL 5.5.8, I used:
For both servers I used:
The raw results, config, and script are in our Benchmarks Wiki.
Here are the graphs:
13G innodb_buffer_pool_size:
In this case, both servers show a straight line, and it seems having 8 innodb_buffer_pool_instances was helpful.
52G innodb_buffer_pool_size:
144G innodb_buffer_pool_size:
The final graph shows the difference between different settings of innodb_io_capacity for MySQL 5.5.8.
Small innodb_io_capacity values are really bad, while 20000 allows us to get a more stable line.
In summary, if we take the average NOTPM for the final 30 minutes of the runs (to avoid the warmup stage), we get the following results:
This is actually the first case where I’ve seen NOTPM greater than 100,000 for a tpcc-mysql workload with 1000W.
The main factors that allow us to get a 1.4x improvement in Percona Server are:
We recognize that hardware like the Cisco UCS C250 and the Virident tachIOn card may not be for the mass market yet, but
it is a good choice for if you are looking for high MySQL performance, and we tune Percona Server to get the most from such hardware. Actually, from my benchmarks, I see that the Virident card is not fully loaded, and we may benefit from running two separate instances of MySQL on a single card. This is a topic for another round.
(Edited by: Fred Linhoss)