Raid Setup

  • This has probably been discussed in the past and I'm sure will be discussed again somewhere down the road. But here we go again.

    My current production database in running on a raid 0+1 configuration. It is OLTP. It is 40 GB approximately in size. So the other day I'm reading that I should be running raid 5 or 5E, (we are IBM hardware based). I am running Windows 2000 Advance Server and Sql Server Enterprise Edition, with 4 GB of ram and 4 processors. Any comments or suggestions are appreciated.

    Snowing in Aspen right now, X Games rule!!!

     

     

  • Raid 0+1 is good for write and Raid 5 is good for Read access. Therefore for best performance, we put the transaction log and the tempDB on a disk array with Raid0+1 and the database files on a Raid 5 logical drive.

     

     

  • If you can afford it, keep the 1+0 for both log & data.  I've tested this, in an OLTP environment, results showed using RAID 5 for data performed noticably worse than 1+0.  As you would expect.  I only use RAID 5 for data where I cannot afford 1+0.

    The RAID 5 advice you saw may stem from an earlier time when 1+0 was rare/expensive.

  • Raid 5 is only suitable for a read only database.

    In all the hiogh performaing oltp environments I've worked I've always used raid 10, right back from sql server 6.0 and disks were expensive then!!

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • I've used RAID5 for data files and it worked OK (you have to analyze your application needs first).

    RAID 1+0 (10 whatever you want to call it), will always be faster then RAID 5, though, because RAID 1+0 does not require the RAID adapter to compute parity information. This step is what makes RAID5 slower.

  • The main issue with Raid 5 is that it requires 4 i/o's per disk write, that effectively puts quite a brake on the throughput of your data array. ( i/o's x spindles ). Some of the better raid 1+0/0+1 controllers can read and write on both sides of the mirror thus doubling your read throughput and negating the write overhead.

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • The first question you should consider asking is, " Where are my SQL Server bottle necks?". Our Database (Running Similar IBM speced server) has a mixture of Raid 5, Raid 1. we considered raid 0+1 but decided better performance was attained by having multiple Raid 5 arrays for our biggest table (100GB Note: were working to a fixed Budget). Using multiple Raid arrays and filegroups can give increased perfomance for large tables as SQL server will read a table in parallel if it is spread over several disk arrays. Also decided to put indexes on seperate Raid 5 arrays (This sped up index creation and Query performance). In Summary look at where your performance issues are and then decide on Raid arrays to give you best performance for your money

  • Transaction logs are written sequentially. There is no added benefit to put them in a 0+1 container. I do use RAID 1 for them. 0+1 is the best solutionf for data and indexes. If you can afford 2 containers 0+1 (data and indexes) it will be much better. Your goal is to have more spindles available for random I/O operations, and no contention for sequetial operations.

Viewing 8 posts - 1 through 8 (of 8 total)

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