RAID and Its impact on your SQL performance

Question

RAID and Its impact on your SQL performance

GregoryAJackson

SSCrazy

Points: 2794
More actions
April 30, 2012 at 10:56 pm

#75653

Comments posted to this topic are about the item RAID and Its impact on your SQL performance
Gregory A Jackson MBA, CSM

Viewing 15 posts - 1 through 15 (of 95 total)

You must be logged in to reply to this topic. Login to reply

Perry Whittle SSC Guru Points: 234013 More actions · Answer 1

Nice article, incidentally RAID 1+0 and 0+1 are not identical. As 0+1 is essentially 2 stripes the loss of 1 disk in each portion of the array is catastrophic. 1+0 can lose up to half the disks in the array as long as no mirrored pair fail.

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

"Ya can't make an omelette without breaking just a few eggs" 😉

craig 81366 SSC Eights! Points: 808 More actions · Answer 2

A questoin; out of curiosity:

You quoted some PerfMon stats on your production environment.

Given your analysis, would I be correct in assuming your produciton environment already runs an array of > 100 disks?

In fact given your preference for RAID 1+0, are you running > 200 disks?

yazalpizar_ Default port Points: 1425 More actions · Answer 3

With the proliferation of SSD disk and its costs going down, some RAID levels with low write performances are no longer so low. Put an SSD in your life. I've done it, both home, laptop and work. Is the best money can buy right now in order to increase performance several degrees. We are planning to do it also on our local servers, first on the test server and then on the main one if evertyhing is ok.

btd SSC Journeyman Points: 91 More actions · Answer 4

I do agree a well put together document BUT I wish we could get away from the interpretation of RAID. 'inexpensive discs'... The 'I' certainly did not mean inexpensive discs when RAID first came out way back when...'Independent' ... why does the computer world keep changing things when it does not need to, we have plenty of change without it...... and as for 'tables' instead of files.........

tonyrogerson-498793 SSC Enthusiast Points: 189 More actions · Answer 5

The IOPs calculation for the disk needs further explanation, there are a lot more factors involved in how many IOps you can get out of a physical disk, for example in a comparison between a commodity 7.2K disk (density 1TB per platter) compared with a enterprise 15K disk (density 150GB per platter) for 100% sequential read from a 40giB file located on the outer edge of the disk you can easily get 20K-30K IOps at an average latency of less than a millisecond.

More disks in a RAID means the file is split up over more disks thus giving implicity shortstroking.

There is also a massive difference in where the file is located on the disk - sequential read from the outer edge compared to the inner can differ significantly in throughput with a slight increase in latency as well.

RAID doesn't quite go away with SSD, you'd not use RAID 5 so you'd just use RAID 1 or RAID 10, you'd only RAID 10 or RAID 0 to get round say the single channel bandwidth (approx 550MiB/sec) you get with SAS 600 and SATA 3 but also if you need more storage than a single SSD can give.

Tony.

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 6

Very good summary article with some condensed information that's useful to know - thanks! Although I'm familiar with RAID and the effect on SQL Server I didn't know that read penalties were far lower than write penalties - not that it matters, I suppose, unless we're talking about read-only databases. I didn't know there was a direct correlation between disk transfers/sec in perfmon and IOPS rates but thinking about it, it does make sense!

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?

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

Jonathan AC Roberts SSCoach Points: 18361 More actions · Answer 7

We use SAN with massive amounts of memory installed on them. This seems to be a whole different kettle of fish. The bottlenecks move from the disk to other areas like the throughput of ports on the SAN being able to cope with the volume of data and the SAN's CPUs.

jonalberghini SSC Enthusiast Points: 187 More actions · Answer 8

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.

Brent Ozar SSCrazy Points: 2451 More actions · Answer 9

Greg - good post, but the explanation of IOPs is not correct. You write:

In basic terms IOPS is a measurement for the amount of time, on average, required to service an input or ouput request (a read or a write).

That's the definition of total storage latency, not IOPs. (I mention total storage latency because your post also mentions rotational latency.)

I like to explain it using FedEx. Let's pretend you're sitting at work, and you want to find out how fast FedEx delivers packages. You seal up one envelope, write New York City on the label, and call for a FedEx pickup. The driver arrives, and the next morning, you get confirmation that your envelope arrived in New York City.

That's one operation per day, one package delivered - however, that doesn't mean FedEx can only deliver one package per day. To really test FedEx, you have to put together LOTS of packages and envelopes, then call for the truck. One package still takes the same amount of time to be delivered, but they can handle many at once.

IOPs = the number of packages you can move.

Latency = how long it takes to move each package.

The size/speed/quantity of the truck also come into play to make the total storage picture, plus the size/speed/quantity of the truck on the other end, and the office's size on the other end - I talk through that in my day-long storage classes.

I'd also disagree strongly about your recommendation for RAID 5 for backups. I see a lot of shops that use a shared RAID 5 array for backups from multiple servers, and the backup times are unacceptable to the business. Simply by switching that target to RAID 10, I've seen backups drop from >4 hours to <30 minutes, for example, without raising the spindle count. Granted, capacity drops, so you can keep less backups online and they have to go to tape quicker, but if your goal is backup performance, RAID 5 won't cut it.

Perry Whittle SSC Guru Points: 234013 More actions · Answer 10

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[/url] 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 SSChampion Points: 11130 More actions · Answer 11

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 SSCrazy Points: 2451 More actions · Answer 12

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 SSChampion Points: 11130 More actions · Answer 13

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 Hall of Fame Points: 3123 More actions · Answer 14

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.