February 1, 2010 at 1:09 am
Comments posted to this topic are about the item Get In a Line
February 1, 2010 at 2:30 am
I've read the articles on partition alignment and seeing the potential gains of 20-30% was quite keen to try them out.
We use a SQL2005 environment over WS2003, however, the original filesystems were installed over W2K and SQL2K so it looked a fair bet our partitions would be out of alignment.
My first step was to test each drive array to see which ones were in and which ones were out of alignment, this was pretty straight forward and as expected most were in need of attention 🙂
Before doing anything we wanted to get a base line so we could measure the improvement and to be honest wave a little flag to the business ... after reading the articles several times along with several blogs it seemed that using a batch file to run various SQLIO tests was a decent way to go.
The information was collated, our test server was then aligned and the tests were run again - the difference was not showing as 20-30% gain :ermm: I did the same type of test on other arrays - RAID1 pairs, RAID1+0 sets and the results were inconsistent.
Is there a better way to get reliable performance results? e.g. results that relate to real world gains or losses as our tests don't back up the official Microsoft data so we're left in a quandary over whether it's worth implementing the changes to production systems.
Thoughts?
February 1, 2010 at 4:53 am
I have heard mixed results on this myself in the past and the bottom line for 24/7 shops out there is we just can't afford thet kind of down time for production boxes. So, it is not a very realistic solution for most of us...
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
February 1, 2010 at 5:33 am
Rob L-566658 (2/1/2010)
The information was collated, our test server was then aligned and the tests were run again - the difference was not showing as 20-30% gain :ermm: I did the same type of test on other arrays - RAID1 pairs, RAID1+0 sets and the results were inconsistent.Is there a better way to get reliable performance results? e.g. results that relate to real world gains or losses as our tests don't back up the official Microsoft data so we're left in a quandary over whether it's worth implementing the changes to production systems.
Thoughts?
Rob, what sort of results did you get? Was it better at all, worse?
I would say using SQLio and SQLioSim is a good way to go, plus just comparing perfmon results before and after. Perfmon would be showing throughput with your actual day to day workload.
---------------------------------------------------------------------
February 1, 2010 at 5:47 am
That's the problem we had - some results were better some were worse but from server to server the better or worse ones were not the same.
At this stage I tested only on non production systems so that I could test the results outside of working time to ensure my load testing was the only thing running on both the before and the after tests so that the comparison was valid.
For instance this test;
Test 5 - Sequential writing for 360 seconds using 8k blocksize and 8 outstanding I/O requests per thread
This one varied from +10% to -84% in the 4 separate arrays we tried.
We ran 16 separate runs - Random Writing, Sequential Writing, Random Reading & Sequential Reading all using each of 8k, 64k, 128k & 256k blocksizes.
I'm no expert on SQLIO but I do believe the tests were done in a representative manner so was a bit disappointed when the magic wand didn't work 😉
February 1, 2010 at 6:05 am
If you ever find a magic wand let me know 😉
Main gotcha with SQLio is making sure the file sizes are large enough so it is not all done in cache.
---------------------------------------------------------------------
February 1, 2010 at 7:23 am
Let me start by saying I cannot claim responsibility for authoring either of these scripts... nor can I tell you who did as I didn't note where I found them initially and now when i search for them again i get so many results of identical entries with no reference to their source...
The document referenced in the editorial has reference to dmdiag -v but in the event you don't have the support tools installed...
**Powershell-found at http://sqlblog.com/blogs/linchi_shea/archive/2008/11/24/finding-disk-partition-offsets-made-easy.aspx**
$a = Get-WmiObject -class Win32_DiskPartition
foreach ($obj in $a)
{if (($obj.StartingOffste / 1024) -ne 64)
{Write-Host $obj.DiskIndex
Write-Host $obj.Index
Write-Host ($obj.StartingOffset / 1024)}}
**vbscript-found at http://technet.microsoft.com/en-us/library/cc966412.aspx**
strComputer = "."
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\CIMV2")
Set colItems = objWMIService.ExecQuery("SELECT * FROM Win32_DiskPartition",,48)
For Each objItem in colItems
Wscript.Echo "Disk: " & objItem.DiskIndex & " Partition: " & objItem.Index & " StartingOffset: " & objItem.StartingOffset/1024 & "KB"
Next
February 1, 2010 at 7:27 am
😀 yeah we tried to recruit Harry Potter as a DBA but we couldn't afford him:-P
Agree about the file size - from one of the blogs I read that, so we used files between 50gb and 70gb to make sure we were truly using io on disk rather than memory cache.
Good point to make though.
Interesting to hear from anybody else that has tested the alignment pre and post figures and how their implementations fared :unsure:
February 1, 2010 at 10:26 am
I'm also very interested in seeing results like this.
Was there other activity happening when the SQLIO tests were run? Multiple LUNs per RAIDGROUP, or any other spindle-sharing, can hide this.
Were the SQLIO data files contiguous on disk (external [filesystem level] fragmentation)?
Were the SQLIO data files in the same relative position on the disk (i.e. if one test had the files on the inner tracks [probably near the end of the partition], and another had the files on the outer tracks, the tests were not comparable)?
Benchmarking at this level requires a lot of careful setup; I'd recommend using the same spindles (which you did), short-stroking each configuration as identically as possible, and sized to the size of the test files (i.e. if you have a 146GB spindle, and you're going to test 70GB files, then create a 70GB partition and leave the other 70GB unallocated).
February 1, 2010 at 10:49 am
Yes, all good advice.
Our testing was the only activity on the server. As the process of changing alignment is destructive the only files left on the disk were the test files we created in SQLIO and each time we ran tests the same size file was used so the comparison should be representative.
From my testing so far, I don't see it is worth the effort in making the same changes on the production systems, however, I am open to retesting using other quantifiable metrics that may prove more consistently that it is worth it.
February 1, 2010 at 10:56 am
Did you change the RAID stripe size as well and test "aligned" vs. "unaligned" at a couple different RAID stripe sizes?
Were test results consistent from one run to the next on exactly the same configuration?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply