SQL Server Performance

RAID 5 vs. RAID 1+0

Discussion in 'Performance Tuning for Hardware Configurations' started by MikeJS78, May 19, 2006.

  1. MikeJS78 New Member

    I'm looking to reconfig our disk IO subsystem due to the fact that the IO on the data drives is constantly pegged. Currently, have 3 databases (with a fourth on the way). Two are highly write-intensive and transactional (i.e. inserting/deleting/updating thousands of records/sec). The fourth will also be heavily transactional. The other database is a "summary" database of all the other five. It contains only about 200k records, but each record is updated when the 60k or 70k records in the "Child" databases are changed. Current config is as follows:

    Dell Server w/ RAID 1 (2 73 GB 10k UltraSCSI drives)
    * Holds O/S and SQL Server Program files, as well as Master, modal, msdb

    SCSI controller w/ 2 channels going to DAS with capacity for 14 drives. Setup is as follows:

    RAID 5 (3 136 GB 10k UltraSCSI drives)
    * Holds all MDF files

    RAID 5 (3 73 GB 10k UltraSCSI Drives)
    * Holds all LDF files

    Single 36GB UltraSCSI 15k
    * Holds TempDB (I know this should be RAID, this wasn't my work...)

    Single 136 GB 10k UltraSCSI Drive (hot Standby)

    Budgeet is available to fill out the array, maybe get some faster drives, and get a second DAS and SCSI card to attach to the server. This would give us a total of 28 drive capacity in the DAS arrays, and the single RAID-1 on the server. Thoughts on Configuration

    * Is it better to do a 4-disk RAID 1+0 for each data file or a 5-disk RAID-5
    * Should the database files be on their own array?

    Also, if someone could help come up with some sample configs, that would be much appreciated.

    Thanks.



  2. joechang New Member

    each of the active tx db should have its own 4 disks RAID 10 for logs, min 2 disks RAID 1 if if can tolerate performance slow downs during t-log backups

    a min of 2 RAID controllers, 4 SCSI channels, 4 disks per channel for all data & tempdb
  3. cmdr_skywalker New Member

    for intensive sequence write (like logs), use the RAID 1+0, tempdb RAID 0 (some uses RAID 1 but since tempdb is not saved and always reset, i recommend RAID 0). For the other database, distribute the files into different logical disks (different channels) to maximize I/O throughput.

    May the Almighty God bless us all!
    www.empoweredinformation.com
  4. derrickleggett New Member

    Don't ever use RAID 0 for tempdb. If you lose one of those drive, you just crashed your server. Go with what Joe configured for you. It's a great config for the money you'll be spending. By seperating the log files, you are going to capitalize on a sequential write environment. You can afford to have the data and tempdb log files configured as he has pointed out, since they are much more random in nature. This will also get rid of your non-redundant situation. On the server, I would just keep the OS. <br /><br />Do you know what your current disk read and write queue lengths look like? You might benefit from 15k drives, but it's hard to say. Just getting the seperate channels and spindles is going to be a massive improvement from where you are at. If they don't cost that much, go for it though.<br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br />* Is it better to do a 4-disk RAID 1+0 for each data file or a 5-disk RAID-5<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />Just to clarify, focus on seperating the log files first. IF you have disks left, seperate the data files as well. RAID 10 is almost always a better option IF you can afford it. It's critical IMO for the log files though. The data files come after that on a truly intensive system.<br /><br /><br />BTW Joe, I have a client that might want to speak with you. Do you ever do consulting engagements? [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />MeanOldDBA<br />derrickleggett@hotmail.com<br /><br />When life gives you a lemon, fire the DBA.
  5. cmdr_skywalker New Member

    There are places for tempdb on RAID 0. However, after reconsidering the situation above, yes, go for the RAID1 on tempdb <img src='/community/emoticons/emotion-1.gif' alt=':)' />.<br /><br />May the Almighty God bless us all!<br />www.empoweredinformation.com
  6. derrickleggett New Member

    <img src='/community/emoticons/emotion-1.gif' alt=':)' /> The only places are on a development environment, or one where downtime isn't important.<br /><br />MeanOldDBA<br />derrickleggett@hotmail.com<br /><br />When life gives you a lemon, fire the DBA.
  7. satya Moderator

    True, never ever use RAID0 for TEMPDB in production.
    If you want take advantage of performance from TEMPDB then consider SQL 2005.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  8. csuttman New Member

    One more RAID 5 vs. RAID 1+0 question...

    My server has 8 drive bays, and I need to configure these to be optimized for a high IO database. Is it better to have a 6 disk raid 10 for the database and a the remaining 2 disks as raid 1, or would it be better to create two 4 disk raid 10's?

    If I do the 6 disk option... I'm afraid the log file array will be a bottleneck.

    If I do the 4 disk otion... I'm afraid striping between only 2 disks won't be fast enough for the high IO.

    Any thoughts (that don't involve dumping tons of money into this)?



    Louder+Harder+Faster >= Better
  9. satya Moderator

    That depends on the database usage and growth, you can control the log file issues by maintaining suitable backups and schedule to avoid any spurious growth within Transaction log and I would go with 1st option in this case.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided �AS IS� with no rights for the sake of knowledge sharing.
  10. csuttman New Member

    Satya,

    Thanks, but I'm not sure you understood my concern. I'm concerned more with the speed than with the size. The first option has the log writing to a 2 disk raid 1 (mirrored) which is writing to a single disk. So I'm wondering if the log being written to a single disk will be fast enough to keep up with the 3 disk striped array the database file would be on.


    In perfmon, it looks like the current configuration of the server (db is on 5 disk raid 5 and log is on 2 disk raid 1)... each array seems to be a bottleneck at different times. If I switch the 5 disk raid 5 to a 6 disk raid 1+0 and that is faster, then I'm concerned the log array would still keep the db array from performing to it's full potential.

    This probably isn't as difficult as I'm making it, but I guess I'm just not grasping what the optimal array type & size is for a database and how fast the log file array needs to be. Does the log have to be written one for one, or does it get written to memmory first and then catch up when it can?




    Louder+Harder+Faster = Better

Share This Page