What is the recommended RAID configuration for an Oracle database?

Question

RAID (Redundant Arrays of Inexpensive Disks) comes with different configurations (RAID-0, RAID-1...). What is the recommended RAID configuration that I should set up and use when installing an Oracle database. The database will mainly be used as a data warehouse.

Answer 1

It depends. When looking at a data warehouse, if you don't have a specific design in mind, automatic storage management may be an excellent route.

Consider the discussion at AskTom, OTN Forums, OTN Forums 2, and OTN Forums 3.

There is no one right way to deal with things, and the answers change based on a host of hardware and network factors. In order to discover for yourself, preload a sample data warehouse (only a gig or two, enough to play with) on an ASM based machine, on a SAN with the Raid being virtualized by linux and on a hardware based raid machine.

By timing the results of queries on all three of the environments, you'll be able to discover which methodology works the best for you performance-wise. I've deployed databases using ASN and linux-based virtual raids, and a virtual raid behaved slightly better (a few years ago.) However, I suspect that was in part the way the drives were set up.

There is no singular right answer. If you can provide us more details about the size and performance requirements, it may be possible to explore various test cases.

--Edit--

Every "disk group" may be made up of one or more disks, directories, or files on the appropriate subsystem. Oracle recommends "For best performance and reliability, choose a RAID device or a logical volume on more than one physical device and implement the stripe-and-mirror-everything (SAME) methodology." when placing files on a filesystem. That reads as if oracle is recommending RAID 1+0.

ASM managed disk groups, however, "A normal redundancy disk group requires a minimum of two failure groups (or two disk devices) if you are using two-way mirroring. The effective disk space in a normal redundancy disk group is half the sum of the disk space in all of its devices" apparently automatically provide mirroring.

These devices themselves can be comprised of RAID devices, and so on. In practical tests when I was setting up RAIDed data warehouses, a simple virtual RAID 5 on the filesystem provided acceptable performance, and additional ASM added no performance benefits. In this kind of optimization task, first identify your resources, and then test the every possible configuration, as sometimes the results can be extremely counterintuitive.

Answer 2

If you have two physical drives:

RAID0: Fast but no redundancy. Any drive error will kill the whole array. Some people put temporary storage on RAID0 (i.e. tempdb under MSSQL) but I would still consider this dangerous as while you won't lose any meaningful data if the array fall over you will have a server outage until the situation it is repaired.

RAID1: Go for this if you have two drives. There is no write performance benefit though you might see a read performance increase with a good controller. The key feature of RAID1 is surviving one of the drives dying.

If you have three physical drives:

Your options are RAID5, the non-standard 3-drive RAID10 (or RAID1E as IBM controllers refer to it) if supported. You could of course use RAID1 and keep the extra drive as a spare for when one of the others fails, but you should keep spares around in a mission critical environment anyway so this goes without saying.

RAID5 offers more space than RAID10 (two drives worth instead of one-and-a-half) but has a potential write performance issue as for every block written the controller needs to read the parity block, update it, and write it back. This write performance issue can be doubled up for database writes as there are at least two writes for every update: one to the transaction log and one to the actual data areas. As space is cheap these days I would recommend 3-drive RAID10 if supported for the better write performance. Linux's software RAID offers this, as do many IBM controllers (they call it RAID1E). You might find it under other names too as it is not considered a standard arrangement so doesn't have a standard name.

Both R5 and R10-over-three give the same redundancy (any one drive can fail at a time and the array will survive) and similar read performance metrics (similar to a two-drive RAID0 array).

If you have four physical drives:

If creating just one array then there are two options (ignoring "with hot spare" variations): RAID6 and "traditional" RAID10 (a RAID0 of RAID1s).

Both give the same space (two drives of your four). RAID6 gives better redundancy as any two drives can fail at a time where-as RAID10 can only survive four of the six possible two-drives-gone situations. Both give simialr read performance but RAID6 has a write performance issue similar to RAID5's (the same on a good controller, though it can be slower than RAID5 on a bad controller or with software RAID depending on OS an I/O control capabilities. RAID10 is usually preferred for databases for performance reasons - if you need the extra redundancy you can use six drives and have a RAID0 or 2 3-drive RAID1s.

Once you have four or more drives though things get more interesting as you could have a seperate pair of RAID1 arrays. This can offer significant performance benefits with spinning-disks by keeping your data stores on one array and transaction logs on another - this can reduce head movements considerably in some cases and seek times due to "random" access are a real performance killer. For a data warehouse, assuming this means it will see very few writes relatively speaking, splitting transaction logs from data files may be of more limited benefit but you might still want to consider multiple arrays and instead partition your data over them for potentially better read performance.

If you have more than four drives:

You options become wide open here and it really depends on what your data is and what your expected update/read loads/patterns are. For instance once of our services runs on 12 ~70Gb drives:

• 4x as RAID10 for the system areas (OS, SQL Server (MSSQL in our case), swap, tempdb).
• 4x as RAID10 for the data files
• 4x as RAID10 for the transaction logs

Tempdb is kept on the system array. We could move it to the other two arrays and just run the system array as 2 drives in RAID1 as the extra speed isn't much needed for the sytem chunks (as that is only really significant during boot or when swapping and we ensure there is enough RAM for it to never need to swap), but with the way we pay the hosting provider for that set of machines it wouldn't cost us any less to drop the two drives. Backups also go to the system array, before being copied to the off-server, off-site and off-line backup locations.

Of course this is seriously overkill for some databases (there would be no point running a small blog server this way!) but our main app performs very well with this arrangement.

If you have six drives you might consider three RAID1 arrays, or two three-drive RAID10 arrays.

Generally

There is unfortunately no real simple "best practice" as it very much depends on your system's size and use patterns. The only general rules I can think or are:

• avoid RAID5 and 6 unless you know the write performance issue is not going to affect you significantly
• with four or more spinning-disk based drives consider splitting things over multiple arrays to reduce head movements (the full benefit of multiple arrays will not apply to good SSDs as there are no physical head movements to consider, though you might see some difference depending on the SSDs' controller's write combining strategy and so forth)
• test, test and test again: it is always good to try find time to verify that your chosen arrangement is indeed optimal

Hardware or Software RAID?

It used to be that performance of software RAID was below that of hardware RAID for RAID 5 due to the parity calculations and for all arrangements due to slow interfaces between drives and the CPU. With modern CPUs the parity calc problem is not really an issue, but if you have very fast drives hardware RAID can still win out if the total speed of the drives can come anywhere near (within an order of magnitude, at a guess) to how fast the machine can talk to the disk controller. If you have a four drive RAID1 array (i.e. four copies of the same data for lots of redundancy) with software RAID each write operation will result in the OS sending four lots of data to the I/O controller, possibly sequentially - with a hardware controller the OS just sends one write request and the controller sends that to the four drives, probably in parallel.

Good hardware RAID can offer other advantages too: some high-spec controllers have write cache with battery backup so that pending writes are not lost in a power-cut even if your UPS fails, for instance.

Software RAID is obviously cheaper, and more portable so you are not tied to a particular controller if you have to move the arrays due to controller/machine failure.

Cheap hardware RAID usually combines the negatives of software and hardware RAID with few (or none) of the benefits of either so is best avoided.

I tend to use software RAID on our dev, test and UAT servers and good hardware RAID for servers running live customer-/public-facing services.

Answer 3

In some cases, JBOD is the correct answer (ie, not RAID).

The issue is, if you have too large of RAID groups, you don't have the flexibility of specifying how the physical storage is laid out within the database, such as making sure that indexes and records for a table are stored on separate spindles, and making sure that you're balancing out writes across all of your disks.

You can use striping (RAID0) to balance out the writes, but if it's all one big group, you can't separate out the indexes vs. the records.

Mirroring (RAID1) is fault tolerant, and it's faster for reads (as you can read from whichever spindle isn't busy), but it can be slower for writes as you have to wait for both copies to have been written.

I would never go RAID5 or RAID6 on a database. If the data's important, buy more disks and go with RAID1; RAID5/6 is slow (especially in software), and with today's hard drive sizes might take days to rebuild after replacing failed disks for a large disk group ... not to mention that the way most RAID5/6 systems deal with parity errors is to just recalculate parity ... but odds are, the fault's in the data, not the parity but you have no idea where the fault was. (unfortunately, I don't think there's something like LOCKSS for databases)

...

The most interesting layout I've seen on database actually involved having two partitions per spindle -- the innermost part of the disk was used for the production database, the over sections of the disk were used for backups. (and they made sure a partition wasn't backed up to the same spindle; I think there were multiple databases, so each one backed up to the disks from a different one). This gave them the advantage of spreading things across more spindles during the work day, and then at night, they'd run backups.

I'm guessing there'd be a slower recovery if something went wrong and you needed to restore, as you'd have some reads from the outer disk happening while the databases are in use through the day, but there's always tradeoffs in everything.

...

So, anyway, the point I'm trying to make -- there's no one answer that fits every situation. If there was, DBAs would be out of jobs, and companies would buy pre-built database appliances.

The databases I deal with are what my boss refers to as 'WORN' : Write Once, Read Never; he's joking, but "data warehouse" can mean any level of activity ... I've seen some that were loaded from tape nightly/weekly (and were just copies of the OLTP instance, and helped us verify the tapes were good) and massive analysis jobs were run on them, and others where there's a constant stream of input and occassional reads, but no real competition for resources.

Answer 4

The "Oracle Database Performance Tuning Guide" has a chapter dedicated to I/O Configuration. In short:

• Use striping (hardware RAID, software RAID, ASM)
• Do not use RAID5 for Archiving and redo logs
• Align Filesystem block size and DB block size

Answer 5

How much data are you planning on using and how often are you going to read vs write from the system? There's a lot of planning that goes into this, enough so that some people devote an entire academic career to the subject.

Normally I would tell you to goto Wikipedia and read the article before continuing, as there are quite a few types of RAID and each is best used in a different place.

The basics go like this:

RAID0

Good for video gamers. Bad for just about anyone else. It wouldn't be bad to use this for a caching server that doesn't need to keep data for any length of time. Once a disk fails the system is down. Game over.

RAID1

Great for reliability. Not much expandability. Pretty good on speed.

RAID5

The preferred mix between RAID0 and RAID1 (sort of).

Now, after this, it really becomes almost something that should be asked on ServerFault because of the fact that it's server configuration moreso than database design. Always discuss server performance with your Server Admin. That's what they're there for. If this weren't a private beta I would vote to close to migrate you there.

Answer 6

My recommendation for servers is always RAID 5. The time and effort spent recovering your first failed hard drive will always be memorable. If you do set up RAID arrays, I strongly recommend that you standardize on a single drive size and stash 2 spare hard drives in the server room. One drive goes bad? Put one of the replacements in (and let the array rebuild). I've seen RAID arrays go down hard because a second drive went bad while they were waiting for the first to arrive (next day delivery was still too late).