Disk Performance Hands On, Part 2: RAID 10 Performance

<div><p><em>This is Part 2 in an ongoing series on disk performance. You can read the entire series by starting at the <a href="http://kendalvandyke.blogspot.com/2009/02/disk-performance-hands-on-series.html">Introduction</a>.</em></p> <p>RAID 10 is the most commonly recommended type of configuration to house OLTP data files for SQL Server because of the balance of redundancy and speed that it offers. However, poorly chosen configuration options can result in lackluster performance. Today I’m going to look at how stripe size, partition offset, and allocation unit size can affect the performance of a hardware based RAID 10 drive. For reference I’m using the results from my Test Harness #1, a Dell PowerEdge 2950 (<a href="http://kendalvandyke.blogspot.com/2009/02/disk-performance-hands-on-part-1-test.html">view the test harness specifics here</a>). I’m assuming you’re familiar with the various RAID levels; if not, take a second to read <a href="http://en.wikipedia.org/wiki/Redundant_array_of_independent_disks" target="_blank">the Wikipedia entry on it</a>.</p> <p><strong>Stripe Size & Allocation Unit Size</strong> <br />When configuring a RAID 10 drive the first choice you’ll have to make is what stripe size to use. On most servers I’ve worked with this is 64 KB. For fun, in addition to the default 64 KB stripe I tested an 8 KB stripe to match the size of a page and a 128 KB stripe to see if a larger stripe made any difference.</p> <p>To use your newly created array you’ll have to create a partition and format it. On Windows Server most people just use the Disk Management snap-in in the Computer Management console to do this. When you go this route in Windows 2000\2003 your partition is created with a default offset of 32 KB. I’ll cover this more in the next section, so for now I’ll stick with the default.</p> <p>When you format a partition you have to choose an allocation unit size. In Windows Server 2000, 2003, & 2008 the default allocation unit size is 4 KB. I tested the default size and just like I did for the RAID stripe I also tested 8 KB and 64 KB allocation unit sizes.</p> <p>Finally, since we’re talking about OLTP performance here, I’ll focus on the kinds of operations that SQL Server does on data files most of the time. According to <a href="http://blogs.msdn.com/sqlcat/archive/2005/11/17/493944.aspx" target="_blank">this post by SQL Customer Advisory Team</a> and backed up by <a href="http://sqlblog.com/blogs/linchi_shea/archive/2008/03/29/performance-impact-of-controller-cache-sql-server-oltp-workloads.aspx" target="_blank">Linchi Seah’s post on OLTP workloads</a> that’s 8 KB for both reads and writes, and both tend to be random in nature.</p> <p>Here’s what 8 KB random reads look like:</p> <p><img alt="IOs/sec, 8 KB random reads, 32 KB offset" border="0" height="291" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj35VTaZOW9HLtw2ZLKOcAQ-d6bKXbhkZdN2ZmQQ3orherQx-jqQ_ogpkXJthOWPnUeUuTOGrA291BrCGKdYz_LrAdlreCbThOtGRxbqd_mNrgKzVz8lM9L-beXsMnmyTtilyhJLjQEWEKx/?imgmax=800" style="border-top-width: 0px; display: inline; border-left-width: 0px; border-bottom-width: 0px; border-right-width: 0px" title="IOs/sec, 8 KB random reads, 32 KB offset" width="483" /> <img alt="MBs/sec, 8 KB random reads, 32 KB offset" border="0" height="291" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi1921kI25wif1i4k6FkXr_0nPaX3C7wf67BV2hESsRM45SxZZPPrIwz_lkCuAr_5Qkn48uGSO3qbIkmhyphenhyphennJu0KX50_xvy5Q19lngmi1_yUlLgDGAmOnrXXlUdMCzRm9025VePyEKYYASwI/?imgmax=800" style="border-top-width: 0px; display: inline; border-left-width: 0px; border-bottom-width: 0px; border-right-width: 0px" title="MBs/sec, 8 KB random reads, 32 KB offset" width="483" /> <img alt="Avg Latency, 8 KB random reads, 32 KB offset" border="0" height="291" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEieXxybGxz8P02IIe0r3fx_rtLNwVIRlaGs-hafWlB3Aoe3MkrQpsAKBPwVOdxcXTQiigoS_VbfoEUUgfr2Wze7cV2IUhZre6cqB3Tx8rXeMOAhLvVJ__b5t63vv9Bu6dO5Rv2NEoQHAkxt/?imgmax=800" style="border-top-width: 0px; display: inline; border-left-width: 0px; border-bottom-width: 0px; border-right-width: 0px" title="Avg Latency, 8 KB random reads, 32 KB offset" width="483" />  </p> <p>And here’s what 8 KB random writes look like:</p> <p><img alt="IOs/sec, 8 KB random writes, 32 KB offset" border="0" height="291" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgcQniRLiA8ZnLTbTw41fzUcj4Vq2Sv6pXDmNoWFCJlh4zcrryvPvbzWkgdQQUjWm6EYkbB6_oaUYQaWEzZTuA595rklwxOI_no_sg1_gRjTwk4KsVTxlXoVnOhBv1ctvRxTyDYhQBwtNWK/?imgmax=800" style="border-top-width: 0px; display: inline; border-left-width: 0px; border-bottom-width: 0px; border-right-width: 0px" title="IOs/sec, 8 KB random writes, 32 KB offset" width="483" /> <img alt="MBs/sec, 8 KB random writes, 32 KB offset" border="0" height="291" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhbdaf_svCuSyI8LL151BniQKDC4tBDs-FsTA3mI7rRYBdrQCGV01yj94L8tQRlDLgTwbb_zStsxZx_zcg-2gmy-ogKUxWsxgLNlfos57-CPrth3VoUUP8dTtNj6r2yFwDssPudo06r9_wM/?imgmax=800" style="border-top-width: 0px; display: inline; border-left-width: 0px; border-bottom-width: 0px; border-right-width: 0px" title="MBs/sec, 8 KB random writes, 32 KB offset" width="483" /> <img alt="Avg latency, 8 KB random writes, 32 KB offset" border="0" height="291" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjjuwbk9JQzIAbZ6RpQYrV8emttsklADIzobHsWpPx90YW4662woRyuQZolttwKa4MeLROYBnefTO4kxJ1J4VwOyu2cN-3OBvG3FJTTaqwFzMqgfqHZhk877mExYBYML2GuNRv7aAyPurU5/?imgmax=800" style="border-top-width: 0px; display: inline; border-left-width: 0px; border-bottom-width: 0px; border-right-width: 0px" title="Avg latency, 8 KB random writes, 32 KB offset" width="483" /> </p> <p>It’s pretty clear that a 128 KB stripe size offers the best performance. It also appears that allocation unit size makes relatively little difference when using a 32 KB offset. Now let’s look at what effect a 64 KB partition offset has on performance.</p> <p><strong>Partition Offset <br /></strong>Remember that by default Windows 2000\2003 uses a 32 KB partition offset. There’s been a lot of talk lately about how using a 64 KB offset can result in major performance gains, so let’s take a look at the same test setup but this time with a 64 KB offset.</p> <p>Here’s what 8 KB random reads look like:</p> <p><img alt="IOs/sec, 8 KB random reads, 64 KB offset" border="0" height="291" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiNYBeU0Uw8mz79Z-MtTZ0CzYE0r7vDIN6LwPJEdZkuxa9EbYaVT6FQz72aQqNfCxfMHeOV_ewT5EorVa2m8ByFcPtC9DJIch-TocGrp6PZ643xc4xCew4m-CZjz4E3idfRdCQRVFU9xaqZ/?imgmax=800" style="border-top-width: 0px; display: inline; border-left-width: 0px; border-bottom-width: 0px; border-right-width: 0px" title="IOs/sec, 8 KB random reads, 64 KB offset" width="483" /> <img alt="MBs/sec, 8 KB random reads, 64 KB offset" border="0" height="291" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhSDvqzr0wo4a8uLXRCDFtS3vni8f_baPAiDV_rvx6aBp9xsC_4TJFezCwSpnNsWfKOYaMgETXgYAqHgiDmBG39tArQotZKt78zaJpZ0KiiadRP-_7SK1MR61YbAJ5_6Mq4iMDoXyQ0vAl0/?imgmax=800" style="border-top-width: 0px; display: inline; border-left-width: 0px; border-bottom-width: 0px; border-right-width: 0px" title="MBs/sec, 8 KB random reads, 64 KB offset" width="483" /> <img alt="Avg Latency, 8 KB random reads, 64 KB offset" border="0" height="291" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgZpM3p7u3AVZH5ZJuMC92MfuglAThy6XGT0MRb9DvqcBw5cz2PevFrW5p1GxWiV6oOmEpjBRg2KgLKhIhQtOLq-ixtTuRoYPiJRQ88FmP3P48LzPvZbW6Tcq7aEPneti5zqaAVwONNzjt_/?imgmax=800" style="border-top-width: 0px; display: inline; border-left-width: 0px; border-bottom-width: 0px; border-right-width: 0px" title="Avg Latency, 8 KB random reads, 64 KB offset" width="483" /> </p> <p>And here’s what 8 KB random writes look like:</p> <p><img alt="IOs/sec, 8 KB random writes, 64 KB offset" border="0" height="291" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgHBKhzYJr32lIM9cs29raY6i4qZ30zh3ltt6g1FST4XwqTUHwytI6cAyDRFLQsuAf7o8GMKN9wc11P8FE5ezDYgoPLifcR_0tW2JtEpXujcqiag0Uue99Bnq6AkUBJyFg_IxQPkoUevNgK/?imgmax=800" style="border-top-width: 0px; display: inline; border-left-width: 0px; border-bottom-width: 0px; border-right-width: 0px" title="IOs/sec, 8 KB random writes, 64 KB offset" width="483" /> <img alt="MBs/sec, 8 KB random writes, 64 KB offset" border="0" height="291" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi_35OwJNvG8FyN07hkwIIlepk-ROJ8PTyOC4rFvsVuVRqNGTme57dvV4MqhFdRAoMhwsnIze8kJrGJaKeYJgxPhEgN4dllRcRBq90G1EEuDvpovZNYl1FkIa_dwtjFTX2SYdgUBe0gnU5G/?imgmax=800" style="border-top-width: 0px; display: inline; border-left-width: 0px; border-bottom-width: 0px; border-right-width: 0px" title="MBs/sec, 8 KB random writes, 64 KB offset" width="483" /> <img alt="Avg latency, 8 KB random writes, 64 KB offset" border="0" height="291" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhSi3XNqbGINKYvBVWQJrjAGiFyLKCqnUHqPb652txBoJXMGQzzvkKxlM-TzQqXcaaoJCoSACmaZKPY9TylSqren6DRyHNivYGh3eLZPZLy7ipEjjaPLOQJDoVrtjeg-p9-oR-xCe83QOB1/?imgmax=800" style="border-top-width: 0px; display: inline; border-left-width: 0px; border-bottom-width: 0px; border-right-width: 0px" title="Avg latency, 8 KB random writes, 64 KB offset" width="483" /> </p> <p>Notice how a 128 KB stripe size no longer results in the best performance; that honor now belongs to a 64 KB stripe size. It also looks like an 8 KB allocation unit size offers the best write performance. However, keeping in mind that most OLTP databases fall somewhere around 80\20 in favor of reads, it’s clear that a 64 KB allocation unit is still the best choice on a 64 KB partition offset.</p> <p>To offer one more perspective on how partition offset affects performance, here’s a comparison of 32 KB vs. 64 KB offset on a 64 KB stripe with a 64 KB allocation unit:</p> <p>8 KB random reads:</p> <p><img alt="IOs/sec, 8 KB random reads, 64 KB vs 32 KB offset" border="0" height="291" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgoB_Yg5pGYFID-pUEwRuBXCCzILKr1ynl8cjW1OkLC1c6tP8L3v6YQMNIBglKFgAV42FhaswF0Mrfeg5ESn2oUSb2Bu_CtFeRxpxJngwKYW9QySHaWgZJSh01-tz7EXP0yVJ5WxOV7S9Sc/?imgmax=800" style="border-top-width: 0px; display: inline; border-left-width: 0px; border-bottom-width: 0px; border-right-width: 0px" title="IOs/sec, 8 KB random reads, 64 KB vs 32 KB offset" width="483" /> <img alt="MBs/sec, 8 KB random reads, 64 KB vs 32 KB offset" border="0" height="291" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgzJGK0pcYOrRbQyK2tn4NVLbN3xsoFo_h4wNXvF9celM6XEemw0fPt5UzbI3tZlhmGBYTD8FivOu1kpGSUGRDM5gJUtt0xdHKdYPcShFkMVxcF2hm1xQ-YUMjUdDAluEcvL1ITM_K1YSRp/?imgmax=800" style="border-top-width: 0px; display: inline; border-left-width: 0px; border-bottom-width: 0px; border-right-width: 0px" title="MBs/sec, 8 KB random reads, 64 KB vs 32 KB offset" width="482" /> <img alt="Avg Latency, 8 KB random reads, 64 vs 32 KB KB offset" border="0" height="291" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg2k3-frf7IBauVF81YXXZepxiHargvrmGVj1qYQW8O08bMHG4-Xv06-FlUuDXRLG9Zy3693FwljjTpNmeJ3LKrMxWk2AkhogBCY63BUri9QuNvv1N-pPaLBO9MDUdDZrVHBM9smls5JuNQ/?imgmax=800" style="border-top-width: 0px; display: inline; border-left-width: 0px; border-bottom-width: 0px; border-right-width: 0px" title="Avg Latency, 8 KB random reads, 64 vs 32 KB KB offset" width="483" /> </p> <p>8 KB random writes:</p> <p><img alt="IOs/sec, 8 KB random writes, 64 KB vs 32 KB offset" border="0" height="291" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiC0QBOoe_S_0TiVQHi2tNaV4ZxSRuLiNY3UujzAV717feiKSz8sbygbkGV8HUzMNIacgoPBWv_sSkorj7HAxUGZJ8IibQt-FNX4h-fWgHx2osYASqZDZOEmqF95CEnbz8KUoupUL6z9IB6/?imgmax=800" style="border-top-width: 0px; display: inline; border-left-width: 0px; border-bottom-width: 0px; border-right-width: 0px" title="IOs/sec, 8 KB random writes, 64 KB vs 32 KB offset" width="483" /> <img alt="IOs/sec, 8 KB random writes, 64 KB vs 32 KB offset" border="0" height="291" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiYcn93cK6zDlKhhfJOQ65jKM5CrxMsK6zSTwJ3znYRZewyyWVx5Erssob-ub083KqIcPVdeENOn1eGhOyrA4GEixrnaZhhOWiaWFkoeGpIALGYJuXssNUPnMNPHEU4EAWwU7uTsLv0Nmu3/?imgmax=800" style="border-top-width: 0px; display: inline; border-left-width: 0px; border-bottom-width: 0px; border-right-width: 0px" title="IOs/sec, 8 KB random writes, 64 KB vs 32 KB offset" width="482" /> <img alt="Avg latency, 8 KB random writes, 64 KB vs 32 KB offset" border="0" height="291" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEga79d1M6pDN312NCBtoGj91mDQTbecD2GE3Tud0husXb-fqS5VOtdpNstE27DINNF50fBcnfQmwqhtgG0-LFQChU6cVdipuDXfRt6eEKyJAK8a6aoU5I0wgUCfURAiI2pSH1O50QOVjXt7/?imgmax=800" style="border-top-width: 0px; display: inline; border-left-width: 0px; border-bottom-width: 0px; border-right-width: 0px" title="Avg latency, 8 KB random writes, 64 KB vs 32 KB offset" width="483" /> </p> <p></p> <p></p> <p></p> <p></p> <p></p> <p></p> <p></p> <p></p> <p></p> <p></p> <p></p> <p>If the graphs weren’t obvious enough, try this: on a 64 KB raid stripe, using a 64 KB offset vs.32 KB offset represented an 11% improvement in IOs/sec and MBs/sec, and a 10% improvement in average latency for 8 KB random reads. For 8 KB random writes using a 64 KB offset resulted in a 10.6% improvement in IOs/sec, MBs/sec, and average latency.</p> <p><strong>Conclusion</strong> <br />The results show that for OLTP databases on a RAID 10 array the optimal configuration is a 64 KB RAID stripe, 64 KB partition offset, and 64 KB allocation unit size. As always, mileage may vary according to the hardware you’re on, however the gist of these results should hold true for most systems using local disks or DASD enclosures.</p> <p>In <a href="http://kendalvandyke.blogspot.com/2009/02/disk-performance-hands-on-part-3-raid-5.html">Part 3</a> I will take a look at how the same values affect performance on RAID 5.</p> <p><em><strong>Update – 2/17/09</strong>: After posting my initial <a href="http://kendalvandyke.blogspot.com/2009/02/disk-performance-hands-on-correction.html">correction</a> I reviewed my RAID 10 results again and found another problem – all the numbers still reflected 64 KB reads. Although I said that adjusting for 8 KB reads didn’t change the results I was wrong. I have updated the graphs once again, triple checked everything, and promise that the numbers are right this time!</em></p> </div>

Share This

• Twitter
• Google+
• Linked In
• Facebook

Related Posts

Best Practices Performance SQL Server 2005 SQL Server 2008