Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

RAID and Its impact on your SQL performance Expand / Collapse
Author
Message
Posted Tuesday, May 01, 2012 6:31 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:26 PM
Points: 5,961, Visits: 12,847
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"
Post #1293092
Posted Tuesday, May 01, 2012 6:38 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: 2 days ago @ 1:43 PM
Points: 687, Visits: 2,995
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
Post #1293099
Posted Tuesday, May 01, 2012 6:47 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 3:59 PM
Points: 62, Visits: 390
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.



Post #1293107
Posted Tuesday, May 01, 2012 6:58 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: 2 days ago @ 1:43 PM
Points: 687, Visits: 2,995
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
Post #1293118
Posted Tuesday, May 01, 2012 6:59 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 9:23 AM
Points: 471, Visits: 823
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.
Post #1293119
Posted Tuesday, May 01, 2012 7:01 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 3:59 PM
Points: 62, Visits: 390
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.



Post #1293123
Posted Tuesday, May 01, 2012 7:04 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: 2 days ago @ 1:43 PM
Points: 687, Visits: 2,995
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
Post #1293127
Posted Tuesday, May 01, 2012 7:16 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, November 20, 2012 1:38 PM
Points: 40, 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.
Post #1293138
Posted Tuesday, May 01, 2012 7:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 26, 2014 6:26 PM
Points: 3, Visits: 46
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.
Post #1293147
Posted Tuesday, May 01, 2012 8:09 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 9:23 AM
Points: 471, Visits: 823
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.
Post #1293174
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse