2

I've seen the advice everywhere (including here and here): keep your OS partition, DB data files and DB transaction logs on separate discs/arrays. The general recommendation is to use RAID1 for OS, RAID10 for data (or RAID5 if load is very read-biased) and RAID1 for transaction logs.

However, considering that you will need at least 6 or 8 drives to build this setup, wouldn't a RAID10 over 6-8 drives with BBWC perform better? What if the drives are SSDs?

I'm talking here about internal server drives, not SAN.

Improve this question

4 Answers 4

Reset to default
3

The reason this advice comes up is to avoid overloading one disk group. Consider the following set of simultaneous events:

  1. A DB connection runs a SELECT on a big table
  2. A second DB connection runs a large INSERT or UPDATE on a different table
  3. Several people all log in to the machine at once (SSH)

If your system was running on a single disk the head now needs to bounce around between where the table we're SELECTing from is located, wherever /var lives (to record the logins), the location of the Write-Ahead Log (to record the INSERT or UPDATE), the various locations in /home and /bin needed to start up the new user sessions, and eventually the tables that were INSERTed into or UPDATEd.

All that disk-head travel takes time - as the number of conflicting requests pile up the time required becomes measurable, and possible noticeable as a performance hit.

If each of those three items were happening to a separate disk group (SELECTs to the data group, INSERTs and UPDATEs to the WAL group, and OS stuff to the OS group) each of the three items above could theoretically happen in parallel (and the eventual flushing of the Write-Ahead-Log could be done when it's convenient for the data group).

The key is that this is based on a theoretically small number of spindles - a single disk, or a small RAID group of 2-4 drives.
As both you and ewwhite point out, at a certain point having more spindles mitigates the conflicting head travel requests, and eventually the gain eclipses the potential performance penalty for most workloads.

The key here is benchmarking your workload (as much as you practically can).
Also as has been pointed out SSDs invalidate the major underlying assumption (SSDs are constant-time access for any "sector" - there's no rotational or head-positioning delay), so you should bear that in mind if you're using SSDs in your design: You'll probably see more benefit from striping SSDs than you would from separating them to isolate activity based on the assumption that conflicting disk requests require more time to reposition mechanical heads and platters.

Improve this answer
3

I don't really care to separate the OS from the data drives in internal disk setups since the benefit from having more spindles (at that scale) trumps the gains from having discrete disk sets.

The best way to find out is to test with your setup and your data...

Remember, you're on an HP controller. Smart Array controllers can take a set (array) of 8 disks and carve it into multiple logical drives of differing RAID levels. So you can carve this up into 200GB of RAID 5, 36GB of RAID 1+0, 100GB of RAID 1+0, and 50GB of RAID 0, if you wanted... Better to leverage more disks if you can.

SSD's change things a little. With a P410 controller, you really shouldn't go more than 6 disks. Diminishing returns above that level.

Improve this answer
2
  • Great link, however I don't understand what Never run SSD's in SA ZMR mode (no cache module) means. Care to clarify? Thanks!
    – Vlad
    Oct 2, 2012 at 8:32
  • 1
    @vlad "ZMR" controllers are Smart Array controllers that have no read/write cache or a battery. They're spec'd on low-end servers. You don't have to worry about that.
    – ewwhite
    Oct 2, 2012 at 8:37
1

The answers above are all good but there is another reason to separate the OS from the data and/or logs. It makes it easier to set different policies for anti-virus. It's also a security best practice/requirement for both database and web servers to keep the data separate from the OS. One last thing is that you can wipe and rebuild the OS without having to worry about the data.

Improve this answer
4
  • 1
    Everything you're describing is true (and in line with best practices), but it can all be done with logical partitions that still reside on the same drive/RAID group - the question here is about the common advice to put components of a DB system on separate physical spindles/groups for performance reasons.
    – voretaq7
    Oct 1, 2012 at 18:03
  • @voretaq7 It probably makes some sense to have them in different arrays in case one of the arrays crashes completely. If they're logical partitions and the array crashes then all partitions will be broken.
    – Vlad
    Oct 2, 2012 at 13:54
  • @Vlad True, but if you lose an array (in any of the configurations we're talking about) you're probably going to have to pull your backups anyway - The restore process isn't substantially different (the only chance to save time would be if you lost the OS array but xlog and data were undamaged).
    – voretaq7
    Oct 2, 2012 at 15:06
  • @voretaq7 There does come a point where queue depth becomes a problem having everything on the same array. More spindles helps but separating the OS loads from the data loads and the log loads makes everything that much easier to monitor and troubleshoot when usage gets large. Best to architect with growth in mind.
    – murisonc
    Oct 2, 2012 at 23:11
0

It probably depends on your IO rates. A 4 disk RAID 10 array can probably get close to 500 IO ops per second. Your best data will come from a performance test where you monitor IO rate, disk queue length and disk % busy. If everything looks good, there's no need to move things around.

For SSDs, it's a different story. One disk is going to be able to handle huge IO loads. A RAID 1 array may perform near 2x better; depends on how good your RAID card is and if it does reads off of both disks (most likely it does). My worry with SSDs and DB logs would be the huge number of writes and if it would reduce the life of the drive. I haven't seen too much data on this.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .