Oracle Data Placement on XtremIO
January 29, 2015 2 Comments
Many customers these days are implementing Oracle on XtremIO so they benefit from excellent, predictable performance and other benefits such as inline compression and deduplication, snapshots, ease of use etc.
Those benefits come at a price and if you just consider XtremIO on a usable gigabyte basis, it does not come cheap. Things change if you calculate the savings due to those special features. Still, customers are trying to get the best bang for the buck, and so I got a question from one customer if it would make sense to place only Oracle datafiles on XtremIO and leave everything else on classic EMC storage. This would mean redo logs, archive logs, control files, temp tables, binaries and everything else, *except* the datafiles, would be stored on an EMC VNX or VMAX. The purpose of course is to only have things that require fast random reads (the tables) on XtremIO.
I can clearly see the way of thinking but my response was to change the layout slightly. I highly recommend to place everything that is needed to make up a database in a consistent way, on the same storage box.
In the case of Oracle this means you store on XtremIO at least:
- Datafiles (containing tables and indexes)
- Redo logs
- Control files
- Rollback segments
I also argue that TEMP should be on XtremIO.
Archive logs, binaries, dumps & exports and maybe even Flashback logs (if used) could be stored outside of the – considered expensive – XtremIO platform.
A bit of explanation is needed here. In the past, EMC has stated that placing REDO logs on Flash does not hurt but does not help either. Here I’m suggesting to put it on Flash. Why? We all know flash wears out due to writes, and redo logs are writing data all the time, right?
Well, first of all, EMC tested XtremIO to handle enormous amounts of write data before the flash disks would wear out. If you run a very heavy write intensive workload 24*7 for 5 years you still would not see the SSD’s inside breaking. The economic lifetime of the box is reached well before the technical lifetime, even under dire circumstances.
Redo logs are also (usually) relatively small compared to the data files, so adding them on XtremIO will not change the total cost much (maybe a little bit).
Still, why not keep the redo logs on other storage as they will not benefit from Flash random read performance?
Two fundamental reasons:
- By keeping everything required for consistency on one platform, you lower the risk of the database going down because one system fails. Splitting data and redo across multiple boxes is a recipe for trouble unless configured very carefully and with lots of extra redundancy in mind. If one box goes down and due to some bug or conf issue, the other box will continue pushing writes to the disk for a while, the end result may lead to an unrecoverable database. It’s just not worth the effort, just stick the REDOs where the rest of the data is.
- Snapshots. You cannot create a consistent snapshot for database cloning if you miss out on the redo logs. So you invest in a feature rich box and then cripple it’s features by storing a tiny part of the database elsewhere? Even if you don’t need snapshots today, a smart guy (m/f) keeps his options open
For control files the same rule holds. Plus they are usually so small that the cost per megabyte is negligible. Don’t even think twice. Just do it.
What about TEMP? It’s not absolutely needed for recovery?
I tend to still keep those on XtremIO as well. Why?
- If the database DOES use TEMP, the I/O pattern tends to be highly random. Speedup by having it on XtremIO is priceless.
- If the database does NOT use TEMP (much) consider keeping the TEMP tables small so they don’t eat up too much precious Flash capacity.
What about Archive logs?
This issue came up several times in the last weeks. My take? It does not hurt to put them on XtremIO but you will not benefit either. Archive logs don’t dedupe well and are mostly written once (sequentially) and then discarded. I prefer to have them on some kind of SATA disk (Isilon, VNX or a SATA tier on VMAX).
Only read heavily when starting up the machine or database. Files tend to do sequential reads, works fine if your storage is classic VNX or VMAX or even JBOD. No need for storing on XtremIO. Even the audit, trace and dump files are not needed for consistency and only waste precious flash space. Leave it out if possible.
- Some databases store a copy of the control file in the “FRA” diskgroup. Oracle might complain if that diskgroup (or file system) is not available during startup (requiring manual intervention to start the database). Make sure all control files are stored on the same (in this case, XtremIO) box.
- The Oracle binaries file system (ORACLE_HOME for experts) often stores some files needed to start up the database (init.ora, passwd file, audit, …) I recommend you make frequent copies of those files so you can restart a database without problems on another host where you don’t directly have those config files available
Choose your data placement wisely. I hope my recommendations help making the decisions.