Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


RAID and Its impact on your SQL performance


RAID and Its impact on your SQL performance

Author
Message
Perry Whittle
Perry Whittle
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8825 Visits: 16561
derek.colley (5/1/2012)
Would have been nice to explore the topic of 1) virtualisation and 2) SANs and how the IOPS rates differ when using these different technologies. Any more articles planned along these lines?

Not exact, but it's along the same lines and a also a good starting point. My recent article is at this link.



Brent Ozar (5/1/2012)
Granted, capacity drops, so you can keep less backups online and they have to go to tape quicker.

Quick fix here is to negate the effect with a good 3rd party backup product or even the built in SQL Server 2008 compression ;-)

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
Rich Mechaber
Rich Mechaber
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1087 Visits: 3661
Nice article, thank you.

I have a question about the write penalty for RAID 1 (mirror). You write: "RAID 1 does have a performance penalty for writes. Each write IOP has to be performed twice (you have to write every piece of data to two disks). " Below, you indicate that the write penalty for RAID 1 is 2.

But I don't understand why a hardware-based RAID controller doesn't simply pipe the same data off to 2 disks simultaneously, reducing the write-time penalty to something much less than 100%? I'd expect there to be some time penalty for the controller to do that, but wouldn't that be significantly less than double the single-disk write time?

Thanks,
Rich
Brent Ozar
Brent Ozar
SSC-Enthusiastic
SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)

Group: General Forum Members
Points: 104 Visits: 489
rmechaber (5/1/2012)

But I don't understand why a hardware-based RAID controller doesn't simply pipe the same data off to 2 disks simultaneously, reducing the write-time penalty to something much less than 100%? I'd expect there to be some time penalty for the controller to do that, but wouldn't that be significantly less than double the single-disk write time?


RAID controllers do indeed write to multiple drives simultaneously. The issue isn't whether they're serial or parallel - the issue is that we're just asking the drives to do more writes. Let's say we've got ten hard drives, and each of them can sustain 100 writes per second - a total of 1,000 writes per second. If we configure RAID in a manner that requires two writes for every thing SQL Server needs to get to disk, that means we can push 500 transactions per second. (In reality, it's way less than that since we've gotta write both the data and log file, but let's talk theoretically for a second.) If we configure RAID in a way that requires a 6 write penalty instead of 2, that means we can only do 1000/6 = 167 transactions per second.



Rich Mechaber
Rich Mechaber
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1087 Visits: 3661
Brent Ozar (5/1/2012)
rmechaber (5/1/2012)

But I don't understand why a hardware-based RAID controller doesn't simply pipe the same data off to 2 disks simultaneously, reducing the write-time penalty to something much less than 100%? I'd expect there to be some time penalty for the controller to do that, but wouldn't that be significantly less than double the single-disk write time?


RAID controllers do indeed write to multiple drives simultaneously. The issue isn't whether they're serial or parallel - the issue is that we're just asking the drives to do more writes. Let's say we've got ten hard drives, and each of them can sustain 100 writes per second - a total of 1,000 writes per second. If we configure RAID in a manner that requires two writes for every thing SQL Server needs to get to disk, that means we can push 500 transactions per second. (In reality, it's way less than that since we've gotta write both the data and log file, but let's talk theoretically for a second.) If we configure RAID in a way that requires a 6 write penalty instead of 2, that means we can only do 1000/6 = 167 transactions per second.

OK, Brent, that totally makes sense, thank you. I thought the article implied there was an additional write penalty for simultaneous writing to multiple disks, rather than the overall throughput penalty incurred because the array is a mirror.

So, while I've got your attention: is there any? miniscule? significant? write-performance hit in writing to a 2-drive mirror compared to writing the same data to a single disk (assuming a hardware RAID controller)? That's what I was really inquiring about.

Thanks for the quick reply!
Rich
BJ Hermsen
BJ Hermsen
SSC-Addicted
SSC-Addicted (487 reputation)SSC-Addicted (487 reputation)SSC-Addicted (487 reputation)SSC-Addicted (487 reputation)SSC-Addicted (487 reputation)SSC-Addicted (487 reputation)SSC-Addicted (487 reputation)SSC-Addicted (487 reputation)

Group: General Forum Members
Points: 487 Visits: 880
jonalberghini (5/1/2012)
I have a question. Everyone talks about Raid levels and performance but what about performance in terms of the number of logical luns? Is it better from a pure performance stand point to show say 8 LUNS to Windows Server as oppsed to one large LUN? I know it is bound by the number of actual spindles but I was under the impression that more LUNS is better. I know it is harder from a maintainane stand point. But since Windows Server see it as a physical disk. can it more efficiently utilize the large SAS pipe. My raid setup has 5 drive in a RAID1 setup. They are 15,000 RPM
600 gig drive with a SAS 6g pipe. I show one Logical LUN would I get better performance if I showed 4 LUNS. Can Windows have more outstanding requests?

Thanks
Pleas help.


This will completely depend on how you are accessing the LUNs. If you are accessing all of the LUNs over the same path, then you are gaining on your restore capabilities as you can choose which disk to restore first as you have for instance 5 - 300GB drives presented to the OS vs 1 1.5TB drive. This means for much more granular recovery ability. If you are able to do parrallel access of multiple LUNs then it would make sense to do multiples as, like another poster mentioned, we often move our performance bottleneck from Disk to HBA Port, Fibre Switch, Cache ability etc. It really comes down to being in Lock Step with your hardware vendors and with your system design folks to ensure that the system is corretly layed out from end to end to meet its purpose.

All to often we get the parties involved saying 'What do you need' when in fact a roundtable discussion of experts in each area is required to ensure that we don't add a simple bottleneck to an extremely robust high performing solution thus negating 10's of thousands of dollars in investments.
Brent Ozar
Brent Ozar
SSC-Enthusiastic
SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)

Group: General Forum Members
Points: 104 Visits: 489
rmechaber (5/1/2012)

So, while I've got your attention: is there any? miniscule? significant? write-performance hit in writing to a 2-drive mirror compared to writing the same data to a single disk (assuming a hardware RAID controller)? That's what I was really inquiring about.


Sure, it depends on the RAID controller or storage processor. If it was just one write in absolute isolation, it's going to be minimal, but as disk activity starts to pile up, you can run into problems with the bandwidth of the storage gear altogether. If the storage gear doesn't have enough connections to the drives, or if it uses a small shared connection (like 2Gb FC) across a whole lot of drives, then you can run into a bottleneck there.



Rich Mechaber
Rich Mechaber
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1087 Visits: 3661
Brent Ozar (5/1/2012)
rmechaber (5/1/2012)

So, while I've got your attention: is there any? miniscule? significant? write-performance hit in writing to a 2-drive mirror compared to writing the same data to a single disk (assuming a hardware RAID controller)? That's what I was really inquiring about.


Sure, it depends on the RAID controller or storage processor. If it was just one write in absolute isolation, it's going to be minimal, but as disk activity starts to pile up, you can run into problems with the bandwidth of the storage gear altogether. If the storage gear doesn't have enough connections to the drives, or if it uses a small shared connection (like 2Gb FC) across a whole lot of drives, then you can run into a bottleneck there.

Thanks Brent. I don't design SANs or NAS at all, but I like to have an understanding of the performance bottlenecks that can arise.

Rich
VinicioAizpurua
VinicioAizpurua
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 115
Good article altought was corrected by BrentO. I recently attended to at South Florida group meeting where Brent spoke about SSD. It would be interesting to see what should be the impact on RAID configuration on SSD.
Thanks Greg for posting this article and Brent for complementing it.
jonalberghini
jonalberghini
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 49
In my situation I have a table that is partioned into 12 partiones. So I have different file for each partioned group on the same lun going to the 5 disk stripped set. Would it make more sense to break it up into 12 logical luns. Would SQl server and Windows Server access faster because my SASS channel is 6g and I am only utilizing up to 1g (in monitoring it). This is a read only Data set.
BJ Hermsen
BJ Hermsen
SSC-Addicted
SSC-Addicted (487 reputation)SSC-Addicted (487 reputation)SSC-Addicted (487 reputation)SSC-Addicted (487 reputation)SSC-Addicted (487 reputation)SSC-Addicted (487 reputation)SSC-Addicted (487 reputation)SSC-Addicted (487 reputation)

Group: General Forum Members
Points: 487 Visits: 880
jonalberghini (5/1/2012)
In my situation I have a table that is partitioned into 12 partitions. So I have different file for each portioned group on the same lun going to the 5 disk stripped set. Would it make more sense to break it up into 12 logical luns. Would SQl server and Windows Server access faster because my SASS channel is 6g and I am only utilizing up to 1g (in monitoring it). This is a read only Data set.


If you have 1 5-disk stripe with no redundancy at all I doubt your partitioning is gaining you anything. You are likely hitting your max reads on the disks as is. The stripe set can only operate 1 read at a time. You will have no parallel reads occurring. I would suggest you have separate stripe sets at that point to allow for parallel access, but that would depend on if the controller and pathing you use would also allow for the parallel access.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search