For Day 10 of this series, I am going to talk a little bit about disk performance and one easy benchmark tool that you can use to quickly compare the performance of different types of disks and disk arrays. CrystalDiskMark, which is available from Crystal Dew World is a fairly well-known disk subsystem benchmark. You can select the number of test runs, desired file size, desired test file type, and which logical drive you want to test. It allows you to measure:
- Sequential read and write performance in megabytes/second
- Random read and write performance for a 512K block size
- Random read and write performance for a 4K block size
- Random read and write performance for a 4K block size with a queue depth of 32
There are other disk benchmarks such as SQLIO that will do a much more thorough job of benchmarking your disk subsystem, but they are a little more difficult to work with. Using CrystalDiskMark should be a supplement to other disk benchmarking that you do. I like to do my first round of testing on each logical drive using CrystalDiskMark before I do more detailed, time-consuming testing with SQLIO. You should test all of your logical drives with these tools before you install SQL Server.
I have captured the test results for a high-performance consumer grade 6Gbps MLC SSD (the 256GBGB Samsung 840 Pro) in Figure 1, showing pretty impressive sequential and random I/O performance. This particular drive is basically limited by the sequential bandwidth limits of the 6Gbps SATA III interface (which is about 550MB/sec). We will probably start to see 12Gbps SATA become available over the next year or so.
Figure 2 shows the results for two 15K SAS drives in a RAID 1 array. Both sequential and random read write performance are much better with the single SSD drive, but where you see the biggest difference is with random reads and writes with a queue depth of 32, which is more like most server workloads. That is where flash-based storage really shines.
Figure 1: CrystalDiskMark Results for a 256GB Samsung 840 Pro
Figure 2: CrystalDiskMark Results for two 300GB 15K SAS drives in RAID 1
Figure 3 shows the test results for an older 640GB Fusion-io Duo MLC device, which has better sequential read performance than the Samsung 840 Pro (since it is plugged into a PCI-E slot), but actually has lower random I/O performance at high queue depths with this test. Keep in mind that consumer level SSDs do not perform as well when they are under a server-level workload, and their performance becomes more inconsistent as they have to do more garbage collection as part of their wear-leveling process.
Figure 3: CrystalDiskMark Results for a 640GB Fusion-io Duo MLC device
Figure 4 shows the test results for an LSI Nytro WarpDrive BLP4-1600 card using random data (which simulates the effect of data compression or backup compression). The LSI Nytro WarpDrive BLP4-1600 card uses hardware-based compression to improve write performance and increase its write durability. Hardware compression does not work as well when the data has already been compressed by any sort of software compression.
Figure 4: CrystalDiskMark Results for an LSI Nytro WarpDrive BLP4-1600 with random data
Figure 5 shows the test results for an LSI Nytro WarpDrive BLP4-1600 card using 0Fill data (which is highly compressible). The LSI Nytro WarpDrive BLP4-1600 card has much better write performance when the data is not already compressed. This is really a pretty impressive card.
Figure 5: CrystalDiskMark Results for an LSI Nytro WarpDrive BLP4-1600 with compressible data
One thing to keep in mind is that most consumer SSD drives perform better in their larger capacities (so a 256GB drive will have better performance than a 128GB drive of the same model line). You also want to make sure that you use a 6Gbps SATA III port for a 6Gbps SATA III SSD, or else you won’t get get all of the sequential performance the SSD drive is capable of. It you plug a 6Gbps SSD into an older 3Gbps SATA port, you will be limited to about 275MB/sec of sequential throughput.
Sequential throughput is very important when it comes to doing many common database-related tasks, such as backups and restores, creating indexes, and rebuilding indexes.