3

I'm configuring a new server: it has 1 raid controller with 8x 146 Gb disks. The system is currently configured in Raid 1+0 but has only 1 logical drive, which is not that optimal as data and logfiles should be on seperate disks?

Does it make sense to reconfigure the system to use:

  • 2 disks as a raid-1 logical disk for OS+logs
  • 6 disks as a raid-10 logical disk for data

All the disks are still on the same raid-controller, so' I don't know if this gives me a performance advantage?

Thanks for the advice!

sfonck
  • 131
  • 2
  • 6

6 Answers6

4

It depends on whether your database will be mostly reads, mostly writes, or an unknown mix.

If the database will be mostly reads (SELECTs), then go with all eight drives in the same RAID 10.

If the database will be mostly writes (INSERTs/UPDATEs/DELETEs), then you might even want to consider two 4-drive RAID 10 arrays - especially if your databases will be doing frequent transaction log backups, database mirroring, or log shipping.

If you just don't know, I would go with 2+6 if the server has space to add additional hard drives in the future. That way, if you decide you need more performance on the data or log arrays later, you can usually hot-add pairs of drives and restripe them without an outage. If you post the RAID card model, we can check that for sure.

If you don't know, and you don't have space to add additional drives, then I'd go with a single 8-drive array for easier management. You won't be as worried about running out of space on the log drives.

And no matter which option you choose, put the log files on a separate logical drive from the OS. If you suddenly have a ton of transaction volume and the log files fill up the OS's logical drive (C), your server can go down hard.

Brent Ozar
  • 4,425
  • 18
  • 21
  • 1
    Mostly reads on RAID 10? Dude, going to smack you. Mostly reads on RAID 5. Give you more spindles to use for the reads, and the RAID 5 overhead isn't much because you aren't doing much writing. RAID 10 only gives you 10% of the disks for reading, but RAID 5 gives you n-1 (or n depending on RAID 5 design used) for reading. – mrdenny Jan 27 '10 at 22:53
  • Depends on your definition of "mostly" and how much performance matters on the writing part. Though I agree RAID 5 seems to get pushed aside too often. But can you elaborate on the 10% math? – SqlACID Jan 28 '10 at 00:41
  • I 'll put a limit on the log-file growth, to avoid the OS logical drive from filling up. I think that's as good as creating another logical drive? – sfonck Jan 28 '10 at 10:38
  • @mrdenny means RAID 10 gives you 50% of the disks for reading. We can split hairs about that one - RAID 5 is better for one person reading, RAID 10 can be better for multiple simultaneous reads. Either one is a good choice for mostly SELECTs though. @sfonck - no, that's not as good as creating another logical drive. In theory it is, but here's the reality: sooner or later, someone (maybe you) is going to add another database and they're going to forget about setting a limit on the log file growth. Or, all of the log files will grow simultaneously and they'll add up to fill up the drive. – Brent Ozar Jan 28 '10 at 12:46
2

Here is a question I asked a while ago on this site:

Recommended disk/partition setup for a SQL Server

It sounds like it may help you with your current scenario.

Basically try to get your OS on RAID 1, data on RAID 10 (I go off of RAID 5 at the moment), and logs on RAID 1. The posts in the topic do a very good job of describing why you'd want this.

I would advise against putting your log files on the same drives as your OS. Log files have an annoying tendency to grow a lot and this could potentially fill up your OS drive which would not be good. If they're on their own drives, it should only affect SQL if the drives fill up.

Community
  • 1
Shane
  • 1,869
  • 4
  • 20
  • 34
0

In both 1 and 0+1 RAID you read a single sector from one disk and write it on two.

Diferent story if you have RAID 5, on which, given n physical disks, you read from n-1 and write on n.

In your setup, the only difference between two scenarios is that a raidset fails badly, you still have the other raidset, for what is worth.

lrosa
  • 1,687
  • 14
  • 15
  • The raid-1 with 2 disks should be optimal for writing sequentially, which is needed for log-files. The raid 10 with 6 disks will read from 3 disks and write on 6 disks. My question is if this is a better setup than the raid-10 with 8 disks, which will read from 4 disks and write to 8, but probably no good access for the log-files? – sfonck Jan 27 '10 at 13:36
  • Assuming you have a decent controller and good queue management - with RAID 5 (distributed parity) you read from n disks, not n-1, all disks contain data (and parity) - this is one of the benefits of distributed parity. With RAID 10 you also read from n disks. For writes RAID 5 requires 2 reads and 2 writes (generally) while RAID 10 requires 2 writes - the write penalty for RAID 5 is therefore 4 (1/4 the read rate) and for RAID 10 is 2 (half the read rate). The isolation prevents interference in IO patterns and is good but there is no real difference in aggregate IO rate between the two. – Helvick Jan 27 '10 at 22:14
  • @lrosa I'm assuming you meant 1+0 and not 0+1. 1+0 is almost always preferable to 0+1. – MDMarra Jan 27 '10 at 22:30
0

I'd go exactly with what you've suggested, the 2+6 idea, I think it's well thought out.

Chopper3
  • 101,299
  • 9
  • 108
  • 239
0

I would reconfigure to have 2 logical drives. For starters, having the OS on a separate spindle from your data is good practice because it makes restorals easier. Also, although I don't know the details of the RAID controller, in general splitting your OS onto a different set of drives will increase performance because it will reduce contention on the drives and decrease so called IO Thrashing when the disks spend too much time seeking and not enough reading/writing.

Catherine MacInnes
  • 1,958
  • 11
  • 15
0

Here is a question I asked a while ago on this site:

Recommended disk/partition setup for a SQL Server

It sounds like it may help you with your current scenario.

Basically try to get your OS on RAID 1, data on RAID 10 (I go off of RAID 5 at the moment), and logs on RAID 1. The posts in the topic do a very good job of describing why you'd want this.

I would advise against putting your log files on the same drives as your OS. Log files have an annoying tendency to grow a lot and this could potentially fill up your OS drive which would not be good. If they're on their own drives, it should only affect SQL if the drives fill up. This can be tough with only 8 drives but if you can avoid it, it's probably for the best.

Community
  • 1
Shane
  • 1,869
  • 4
  • 20
  • 34