Home / server / Questions / 122612
Accepted
telstone
Asked: 2010-03-16 05:53:23 +0800 CST

Optimal disk partitions for database setup (15 Drives)

  • 772

We are setting up a new database system and have 15 drives to play with (+2 on-board for the OS).

With a total of 15 drives would it be better to setup all 14 as one RAID-10 block (+1 hot spare) OR split into two RAID-10 sets one for Data (8 disks) and one for logs/backups (6 disks).

My question boils down to the following: is there a specific point where having more drives in a RAID-10 setup will out preform having the drives broken into smaller RAID-10 sets.

performance raid database

2 Answers

  1. You wont like it... it depends ;)

    Wha I would do:

    • Take 4 drives, RAID 10. Put down 64gb bottom for operating systme, the rest for logs ;) There yoyu go. As the OS is mostly dong nothning, and you have a caching raid controller (hopefully), the impact of two partitions is minimal.

    • The other 11... take 1 out as hot spare.

    • 10... depends what you need. For high performance i would AGAIN go RAID 10.

    The trick here is that no IO activity on the database storage discs will interfere with the log IO capability. This basically gives both ends defined IO characteristics which are guaranteed and can be measured independently.

    Depending on RAID controllre and OS you may want to be carefull with your formatting - on SQL Server:

    • Align partition to 1mb - automatic with 2008 upward, screwed before. Use 256k+ stripe groups.
    • Format disc subsystems (except OS) with 64kb nodes NTFS
    • Use multiple database files, in particular at least as many database / log files for tmepdb and heavy duty as your processor can run threads in parallel (=cores, cores*2 for hyperthreading) as there is an allocation "bottleneck" for files.
    • Dont use autogrow in normal operations - preallocate space. You dont really want the performance imppact of autogrow during heavy times.
    • 3
  2. Best Answer

    I'd probably setup 12 in RAID-10 with one hot-spare and 4 in RAID-5, so you can get some parity for your backups, though RAID-6 would be more ideal.

    The performance you get will heavily depend on your RAID controller, though with a good controller it scales pretty well.

    • 0