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 «««678910»»

RAID and Its impact on your SQL performance Expand / Collapse
Author
Message
Posted Saturday, November 24, 2012 8:43 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 5:59 PM
Points: 31,082, Visits: 15,529
Perry Whittle (11/24/2012)
The differences between RAID 1+0 and 0+1 are not academic. Let's just review that again, not academic!!!

If you do decide to deploy a 0+1 array for your mission critical data and you're unlucky enough to encounter a drive failure in each array you'll realise just how important the differences are.

As you're sitting there typing your resume you'll have time to reflect on the storage decision you made


+1







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1388310
Posted Saturday, November 24, 2012 8:51 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, September 22, 2014 12:21 PM
Points: 835, Visits: 1,372
Raid 1+0 and 0+1 isn't even identical in performance, 1+0 performs better when the array is degraded.



Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

Concatenating Row Values in Transact-SQL
Post #1388311
Posted Saturday, November 24, 2012 5:22 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 15, 2014 2:04 PM
Points: 110, Visits: 496
this article is just being republished.....sounds like I need to update it and publish again....

amazing what you learn from others eh?



GAJ


Gregory A Jackson MBA, CSM
Post #1388348
Posted Monday, December 24, 2012 11:54 AM
SSC-Addicted

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

Group: General Forum Members
Last Login: Monday, April 15, 2013 11:42 PM
Points: 424, Visits: 55
Nice article.
Post #1399947
Posted Friday, May 16, 2014 6:37 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 3:30 PM
Points: 140, Visits: 900
I see serious problems with taking a RAID chart from IT guys and accepting it as being applicable in any way to SQL Server. SQL Server requires almost the exact opposite of what IT likes to implement for their file servers. The RAID chart says RAID 1 (mirror) is only "good" at both reading and writing when RAID 1 is the fastest for general SQL Server apps. What I've found is sql server needs a setup optimized for 8k random reads and writes and you can and should ignore virtually all other disk ratings. An SSD will perform between 10x and 100x better than mechanical disks. Hands down, RAID 1 will perform the best for SQL Server but most of my customers have chosen the worst setup of using RAID 5 and then blame me when their RAID is 4x slower than my laptop performance of no RAID.

Actually, having many RAID 1 arrays is the best solution. Having many separate I/O channels will dramatically speed up SQL Server.
Post #1571690
Posted Friday, May 16, 2014 6:59 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 28, 2014 1:28 AM
Points: 14, Visits: 70
Only getting a couple of hundred IOps per 15K drive isn't really accurate because in reality we don't do a fully random workload across the entire disk.

I wrote the blog post here a few weeks ago because I got fed up with SAN / Storage engineers I couldn't possibly get the number of IOps I am getting out of the kit at one of my clients: http://dataidol.com/tonyrogerson/2014/04/07/maximum-iops-for-a-10k-or-15k-sas-hard-disk-drive-is-not-170/

For a 100% read work load of 64KiB on a 20GiB file on a pair of 300GB 15K disks in RAID 1 with a Queue Depth of 32 I easily get 2,281 IOps. For 8KiB I get 14K!

It really does depend on what you are doing, if you are doing sequential scans and you've performed the correct defrag maintenance on your tables, set them up correctly the disk head doesn't need to move too far so IOps goes up - dramatically.

Hope that helps.
Post #1571702
Posted Friday, May 16, 2014 8:15 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 11:52 AM
Points: 876, Visits: 2,421
I might recommend looking at my post earlier in this thread, as I list a table of SQLIO result from actual systems in a variety of RAID setups, both spindle and SSD disks, over different IO sizes and IO queue depths.

If you want to know the difference between RAIDx and RAIDy on your particular setup, I would very much recommend running an exhaustive SQLIO set over the metrics appropriate to your workload; it's not unknown to see a particular setup have one or another odd quirk for a specific data transfer type at a given RAID level (and/or stripe size); keep your firmware, drivers, SAN controllers, etc. updated.

In some older hardware, I sometimes saw a cap on writes that was very unexpected to me.
Post #1571746
Posted Friday, May 16, 2014 10:14 AM
SSC-Addicted

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

Group: General Forum Members
Last Login: 2 days ago @ 8:08 AM
Points: 477, Visits: 1,897
Hello,

regarding Raid configuration you also have to count in the time/cost of a rebuild.

If your smallest unit is 1 mirrored drive the controller only needs to read and write the content of 1 drive to rebuild the mirror.

If you have let's say a 4 drive raid 5 configuration the controller has to read the content of 3 disks to rebuild the raid array.

Now consider a SAN box having a raid 5 or a raid 0 with say 20 drives...
Reading the content of 19 drives to rebuild the array - how long will the array be degraded? When will the next drive fail?


Best regards
karl
Post #1571811
Posted Thursday, May 29, 2014 12:09 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, August 14, 2014 8:37 AM
Points: 82, Visits: 66
Why is the formula to determine how long it takes to perform a single IOP = Seek Time + Rotational Latency?

Wouldn't you be seeking at the same time that you are rotating? Thereby making it the max of one or the other?
Post #1575752
Posted Thursday, May 29, 2014 12:20 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 15, 2014 2:04 PM
Points: 110, Visits: 496
No.
generally speaking the disk spins to the correct sector before the actuator moves the head to its location.


GAJ


Gregory A Jackson MBA, CSM
Post #1575764
« Prev Topic | Next Topic »

Add to briefcase «««678910»»

Permissions Expand / Collapse