Debating storage layout for databases
We recently purchased Oracle and plan on migrating our Microsoft SQL Server database over to it. One question I am getting mixed responses to is regarding disk/file allocation. In our Microsoft world, I found it to be very beneficial to place various files on different drives. For example each of the following are on separate sets of mirrored drives; executables, data files, log files, temporary table space and a few others. After looking around OTN I found several papers by Oracle stating to place everything on the same large set of RAID 0 + 1 drive sets (SAME). I brought this up in an Oracle class I had last week here in Michigan and the instructor went off on me about this. He said under no circumstances to follow the SAME guidelines. He said to break things out on separate sets of RAID 0+1 sets like I have now.
The questions is: Who is right?
As you've experienced, the debate surrounding storage layout for databases of any vendor is vigorus -- almost religous. I have heard arguments from both sides and, depending on what experiment is conducted, one can make a good case for each scenario. Up until recently, Oracle had recommended that the more mount points or drives you had available, the better off you were. It is only in the last few years that we've heard Oracle start to advance the principles of SAME.
Here are a few of the factors I consider when determining the physical database layout:
1. If you can avoid managing many separate drives without compromising performance, you've made a good trade off. Plus, you'll have more time (since you're not constantly tuning physical storage) to focus on other important database administration functions.
2. The RAID arrays from most vendors include some significant amount of RAM used for caching writes. This will mitigate most of the write penalties associated with individual or striped logical devices.
3. Online redo logs are very important to the recoverability of the database in the event of a failure. The placement of online redo logs needs to be on storage that is protected or there should be multiple redo log members in each log group (if placed on unprotected storage).
4. Archived redo logs should be placed on a protected drive that is not used for any other part of the database. If a compromise must be made, I usually place the archived redo logs with data files instead of with the online redo logs. Proper backups of the archived redo logs is critical for recoverability.
This was first published in April 2004