This is Part 4 in an ongoing series on disk performance. You can read the entire series by starting at the Introduction.
RAID 1 is typically recommended for transaction log files, though I have known several people (including myself, I’ll admit) who have also used RAID 1 to hold data files. Unlike RAID 5 & 10 there is no stripe size to factor into performance since RAID 1 only utilizes mirroring. Still, it’s important to use the right offset and allocation unit size – or is it? For these tests I used Test Harness #1 (Dell PowerEdge 2950) and Test Harness #3 (Dell PowerVault MD1000). You can view the test harness specifics here.
Partition Offset & Allocation Unit Size – Dell PowerEdge 2950
Based on the results from my RAID 10 and RAID 5 tests I chose to focus on a 64 KB allocation unit. In addition to 32 KB and 64 KB offset values I also tested a 1024 KB offset to see if a larger offset made a difference. As with previous tests I’ll present the results for 8 KB random reads and writes, the two operations SQL performs on data files most of the time.
Here’s what 8 KB random reads look like:
And here’s what 8 KB random writes look like:
For both reads and writes the numbers are so close together that there’s no practical difference between them. I’ll point out that Test Harness #1 was using (somewhat) older 72 GB 15K RPM SCSI drives. Let’s take a look at a slightly newer system that uses 146 GB 15K RPM SAS drives.
Partition Offset & Allocation Unit Size – Dell PowerVault MD1000:
Let’s look at the same tests against a Dell PowerVault MD1000 (Test Harness #3). I’m also going to add one more thing to the mix – in my tests against the PowerEdge 2950 I configured SQLIO to use 4 threads. To see if the results were the same across increased workloads I also ran my tests using 8 and 16 threads.
Here’s what 8 KB random reads look like:
And here’s what 8 KB random writes look like:
Now we see at least a minimal difference in the numbers and it appears that using a 64 KB offset or higher results in a small increase in performance. Just how much? For 64 KB vs. 32 KB offset it’s a ~10% improvement in IOs/sec, a ~5% improvement in MBs/sec, and a ~10% improvement in average latency for 8 KB random reads. For 8 KB random writes improvements are ~10.5% in IOs/sec, ~11% in MBs/sec, and 10-11% in average latency. Going from a 64 KB offset to a 1024 KB offset resulted in a very small improvement (about 1%), but an improvement nonetheless.
Transaction Log Write Performance – Dell PowerEdge 2950
Back to Test Harness #1 for a second. Remember that RAID 1 drives are typically used for transaction logs. Unlike data reads\writes, transaction log writes are single threaded, sequential operations that range between 512 bytes and 64 KB. To see how performance varied for this kind of activity I ran a set of tests on a 2 GB file using 8 KB and 64 KB writes on 4 KB, 8 KB, and 64 KB allocation units on both 32 KB and 64 KB offsets.
Here’s what sequential writes look like for a 32 KB offset:
And here’s what sequential writes look like for a 64 KB offset:
I’m not going to bother to show any comparisons of 64 KB vs. 32 KB because the numbers are pretty much dead-on the same. Would there be any difference on newer hardware? Perhaps, but my guess is that it wouldn’t be anything dramatic.
Conclusion
For OLTP data files on RAID 1 my tests showed the optimal configuration to be a 64 KB allocation unit and at least a 64 KB offset. For OLTP transaction log files on RAID 1 it doesn’t matter what offset or allocation unit size you use, there doesn’t appear to be any combination of settings that result in a significant performance improvement.
In Part 5 I’ll take a look at how RAID 10 performance stacks up against RAID 5.
About Kendal
Kendal is a database strategist, community advocate, public speaker, and blogger. A practiced IT professional with over 15 years of SQL Server experience, Kendal excels at disaster recovery, high availability planning/implementation, & debugging/troubleshooting mission critical SQL Server environments. Kendal is a Senior Consultant on the Microsoft Premier Developer Support team and President of MagicPASS, the Orlando, FL based chapter of PASS. Before joining Microsoft, Kendal was a SQL Server/Data Platform MVP from 2011-2016.
3 comments
How many times can I thank you! I have mirrored servers, each with two RAID 10 arrays, one for binaries and transaction logs, the other for data files. Default partition size of 31.5K of course, because I didn't know any better! The thought of having to repartition the system RAID has been too much for me to seriously consider (this is an environment where 24/7 up time is important). Since your tests show that the offset has no impact on the transaction log, all I need do is repartition the data array, which is far more palatable.
There are some programs that would change the partition offset but all vendors claim that doing so will cause issues with the boot information. Not a problem with an array dedicated to the data files.
So thank you - again!
First off, thanks for the comment - I'm glad that you found this useful. Just to be clear, my tests showed that offset had no impact on transaction logs on RAID 1. That may not hold true for RAID 10, though admittedly I haven't tested that specific scenario.
In our *small* company we have 1 SQL Server that hosts 3 databases, and we put 4 internal SATA drives in it: 1 for OS, 1 for data files, 1 for log files, and 1 for backups.
We're trying to improve the performance, so the 'easiest' thing we'd like to do is buying a couple of RAID enclosures: 1 for data files, and another 1 for log files.
Do you think this is a good bet? Or it won't help much? If so, what other practical and cost effective improvement we can try?
Thanks.
Post a Comment