Solved

what raid config for Database server

Posted on 2008-10-13
17
371 Views
Last Modified: 2012-05-05
We are about to install a new Database server (SQL Server 2000 (yeah, I know that's not the latest), and need to choose the best raid version for the array.

I have a server with 6 hotswap SCSI drive bays. I have enough discs to fill them all should that be the best way. The server has an onboard Raid Controller (PERC 4DI) , which supports all kinds of raid flavours.

Data safety is paramount, speed only slight less so... any config will give me enough storage. So what should I do?

I could setup raid 5 on 5 discs with a hot spare
or raid 10 on 4 disc with 2 hot spares
or raid 10 on 6 disc no hotpare (is this possible)

And, what kind of block size should I choose for the array / partitions?
0
Comment
Question by:pdvd
  • 5
  • 4
  • 3
  • +2
17 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22704109
>or raid 10 on 6 disc no hotpare (is this possible)
yes

what about
+ 2 disks for a RAID 1 ( mirror ) -> OS + log files
+ 4 disks RAID 5 -> data files
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 22706110
That sounds reasonabl, esp. if you don't have a very large volume of modifications (inserts / updates / deletes).


>> And, what kind of block size should I choose for the array / partitions? <<
64K


Also, it's very important to make sure the disks are properly aligned so that you don't do extra I/O.  Do a Google search for "sql server disk partition alignment" for more info.




0
 
LVL 6

Expert Comment

by:IncisiveOne
ID: 22707666
Assuming you want a dedicated database server (file system files are placed elsewhere), which means just the o/s itself; MS SQL itself and all required structures; and the SQL Devices (which contain the databases, and the contents of the databases):

1  Definitely not RAID5 for databases
2  (the notion of having hot spares AND mirroring needs to be examined)
Raid 10 across 6 disks, with no hot spare
3  You can also look at Raid 01 with a spare

Blocksize.  For a db srever, keep it small but not too small.  16k.

Cheers

0
 

Author Comment

by:pdvd
ID: 22709312
Hi guys thanks for the responses.... though as I feard 3 people 3 different opinions...

1) Can you eleborate your anwsers, and explain why you advise the setups?
2) Is there some kind of 'mother of all configs guide' online?
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 125 total points
ID: 22709587
the block size should be large, because of:
a) data files are large, so 0 loss (it's not a fileserver with thousands of little .docs, each a few kb for example.

b) data will be read from disk in block sizes, so the larger the blocks, the less I/O requests the OS has to do. the full picture is a bit more complex, obviously, but roughly that's the idea

0
 
LVL 6

Assisted Solution

by:IncisiveOne
IncisiveOne earned 125 total points
ID: 22710403
Elaboration

0 Declaration
I am a database guru.  Not here in EE, I just joined two weeks ago, but in the real world.  That's all I do.  Sybase & MS, and a working knowledge of Oracle.

0 Assumption
You want a dedicated database server (file system files are placed elsewhere), which means just the o/s itself; MS SQL and all required structures; and the SQL Devices (which contain the databases, and the contents of the databases).  You do not want a a file server.

1  Definitely not RAID5 for databases
Completely unnecessary for db servers; good for file servers
Very slow.

2  (the notion of having hot spares AND mirroring needs to be examined)
For db servers, for disks secured against failure, you need mirroring.  If you are mirroring, you do not need a hot spare as well, either one xor the other.  Raid provides striping, which does give you additional speed (as long as you are doing it at the controller level or below, NOT at the o/s level) beyond unstriped disk, and helps flatten the load.  Therefore 1+0 or 0+1.

Therefore Raid 1+0 across 6 disks, with no hot spare

4  Blocksize
For a file server, I agree with Angellll, you need large blocksize, they are sequential, and you want to grab as much in each I/O request, and reduce the I/O requests.

For a db server, assuming an online transaction processing workload, the pattern is millions of small I/O requests, all over the shop, almost random (if you stripe it is random anyway); with occasional large sequence read [*].  Therefore you want a small blocksize.  Given that mosts I/O subsytems will optimise and do some caching; and given that sometimes the server will read more than one 2k page sequentially, we can safely go up a notch from the minimum of 8k to 16k.  This is the standard to this question in the Sybase world.

* SQL Server notices large sequential accesses, and has internal optimisations such as Large I/O and Asynch Pre Fetch, which increases the speed of those access types.  Therefore the Raid array should be tuned for the smaller random, not the larger sequential, stripesize/blocksize.

** SQL Server open the Devices (filesystem files) once, on boot, and keeps them open until shutdown.  The files are large (or should be).  The I/O requests to/from files are block I/O.

Cheers
0
 
LVL 55

Expert Comment

by:andyalder
ID: 22713598
http://www.microsoft.com/technet/prodtechnol/sql/2005/physdbstor.mspx - it's for 2005 but I think applies to 2000 as well.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 22714324
>> and given that sometimes the server will read more than one 2k page sequentially, we can safely go up a notch from the minimum of 8k to 16k <<

SQL Server reads/writes to disk in 64K blocks. Thus there is no reason to use a smaller blocksize; an 8/16k physical block just causes unnecessary i/o "chaining".

0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 55

Expert Comment

by:andyalder
ID: 22716459
Yup, and even if you use a bigger block size (128K) and accidentally read something you don't need the disk head was there anyway.

Dell have a great graph showing a 1% performance gain by tuning MS Exchange down to its "correct" block size from having the stripe size too big whereas it shows about 20% performance loss by having it too small. SQL and any other DB is no different, suck as much data off the disk when the head is in the right place because it take a long time to seek to another track.

Tuning it through diskpar(t) ain't much use either since the logical disk mapping doesn't relate to the physical. I can give you 1% improvement for a day's work tuning it but for my daily charge you could have bought another disk.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 22716519
But the initial sector aligment using diskpar(t) is *vital*.  You'll lose up to 40% in performance otherwise.
0
 
LVL 55

Expert Comment

by:andyalder
ID: 22716748
Nope. It's vital when trying to explain how to tune disk subsystems but in the real world any physical disk might have to do a long seek to get to a spared-out sector. Diskpar is wonderful if you have perfect disks without any errors on them  but in the real world oxide on platters isn't perfect so there are unexpected random seeks when the disk goes slow to get past a dodgy bit of oxide.

The comment box is less than a character high again so I might hace made lots of typos, EE is going down hill as far as the UI is concerned.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 22720673
RAID-10 for everything is definitely better if you can afford it.  Most people can't, just because of the economics of doubling the drive space for everything :-) .

Making sure the sectors are properly assigned is a well-known issue for SQL Server.  It has nothing to do with the mix of work load on the box after it is up and running.  That's immaterial, since you're having to read extra sectors regardless.
0
 

Author Comment

by:pdvd
ID: 22728804
Wow... I seem to have started quite the debate here :)

RAID 10 it will probably be then. If all (or most) of you agree... I'd go RAID 1+0 on 6 disks.

The block size I'll let you argue about for a while longer and see what surfaces.

Points will be awarded all arround :)
0
 
LVL 55

Assisted Solution

by:andyalder
andyalder earned 125 total points
ID: 22729109
>2) Is there some kind of 'mother of all configs guide' online?

Yes, it's from Oracle and it's called SAME. www.oracle.com/technology/deploy/availability/pdf/oow2000_same.pdf . 1MB stripe width (stripe size * number of disks) is a bit excessive for just 6 disks but the main point is you will not suffer much from having the stripe size large but you will suffer if it is small. It's just as relevent for other DB vendors since they all work fairly similarly at the disk I/O level.
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 125 total points
ID: 22731339
MS recommends -- indeed states -- that for SQL Server you should use 64K.


>> you will not suffer much from having the stripe size large but you will suffer if it is small <<

If it's *too* small, yes.  But overall it still depends on the workload.  If you do truly random reads of (relatively) small amounts of data, too large a size wastes buffer space and increases i/o.
0
 

Author Comment

by:pdvd
ID: 22738468
So would you guys agree that if I run a RAID 1+0 on  6 disks with a 64k strip size / block size, that I get close to optimal performance?
0
 
LVL 6

Expert Comment

by:IncisiveOne
ID: 22795735
Look, trying to get a few guys who have had lots of experience each is not the question.  Each of us, due to our hard-earned experience, has reached our conclusions/positions, and we are probably quite attached to them.  Best advice for you is, think about the different responses, and make a (now informed) decision.  When you have issues 3 years from now, you will know from experience, which direction was the most true.  But that cannot be reached right now; not unless we obtain a mass of info from you; and certainly not by debate.  Just choose one, the responsibility is yours.  "take what you like and leave the rest."

Notice that I do not participate further than providing the requested information.  I am not "agreeing" to anything other than my post.

There is no such thing as the mother of all configurations; even if there were, it would (by definition) be generic.  The whole idea and goal of configuration is the specific, therefore non-generic, act of setting hardware parms for your specific collection of bits and pieces, and importantly, for the specific use.  Therefore by definition, the mother of all configs, isn't.

BTW, I am not disagreeing that head movement is the only moving part in the whole chain relating to performance and therefore the slowest.  However, there is no need to suck up the whole 64k (wait for it) unless you have somewhere to put it.  Which means a rather large cache, for stuff beyond what you need, just because you picked it up, just because you were nearby.  Therefore, if you are not caching the 64k, then do not bother reading it, read 16k, and get on with the next head movement.  Read speed is not the issue, cache space is.

For the life of me, I do not understand why anyone would believe MS, given their consistent and abysmal record recredibility.  Forced to buy a new box, to run the new o/s ... and the box needs a new o/s ... which needs a new box.  People actually accept this insanity from the gods of commerce.  Anyway, do not listen to them (unless you are willing to buy a new box ...)
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Usually shares are where we want them for our users and we tend to take them for granted. There are times, however, when those shares may disappear causing difficulty for your users. One of the first things to try is searching for files that shou…
More or less everybody in the IT market understands the basics of Networking, however when we start talking about Storage Networks, things get a bit dizzier, and this is where I would like to help.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now