Oracle Storage - Cooked To RAW - What are the implications to our Backup Strategy?

[Alzhy]

Due to some DBA's pointing to reference configurations of record breaking UNIX TPMC peformance - which are all RAW, there are plans to move our multi-TB instances to RAW. From a storage management/conversion perspective I have no problem converting their current datafiles to raw. My only question centers on how our backups will change.

Our current backup strategy is to use BusinessCopy on our EVAs (plans are ahead to use VxVM so we can do inter-EVA or EVA-XP mirrors..) and we use NetBackup to push the data to tape. I am sure NetBackup can do RAW volume backups and my questions are:

1) Will the HotBackup-DB suspend-split approach still work unmodified if Oracle is now dealing with raw storage as opposed to filesystems?

2.) Is RAW storage safe from a server panic/unscheduled reboot standpoint? With Filesystem/cooked storage -- we have a log to replay with so damage is minimal but in the case of raw storage - Will oracle be intelligent enough to recover?

I've had RAW storage experience before but with Informix and Sybase and both handles crashes gracefully...

Any inputs will be greatly appreciated.

[Sanjay_6 · Accepted solution]

Hi Nelson,

We were backing up a multi-TB raw database using netbackup and from the backup /restore standpoint we did not had any problem. As far as the database recovery is concerned in the case of a panic / reboot, oracle has its own strategies for recovering from a system / db crash.

Since the db is so big, your dba's are probably using the db in archive mode and it rolls back any transaction that was completely recorded / updated in the database at the time of database recovery following a system / db crash.

Hope this helps.

Regds

[Sanjay_6]

A mistake in my earlier post,

read

it rolls back any transaction that was completely recorded / updated

as

it rolls back any transaction that was not completely recorded / updated

Sorry abt that.

Thanks
Sanjay

[Alzhy]

Sanjay,

Thanks for the info.. Yes all our DB's are in archive log mode (BTW, are both your archive log and redo log storage on RAW as well?). Do you actually do split-mirror type of backups ?

Also, do you use pointers to your RAW volumes/disks? I am planning to simply create pointers to the actual raw device files so there is minimal changes that the DBAs will do. We'll be using a mix of RAW Disks/LUNs (/dev/rdsk/cXtYdZ) and LVM raw lvols that will be striped.

[Sanjay_6]

Hi Nelson,

We have multiple situation. In some situations, we have everything for the db on raw lvs. In another situation we have a mix of raw / filesystem lv structure for the database.

The backup scenario we use primarily is symmetrix SRDF. the EMC connects, sync up and the database shutdown happens, the emc split happens and the database is started on the backup node to check that the sync was okay and then it is shutdown and the backup kicked off from the backup node. successful db startup confirmation sent to primary node and the db is started on primary node and made is available for the users while the backup is going on from the backup server.

Our DB storage is primarily raw lvs striped across 8 luns on the EMC. To keeps things organized, we create standard 2GB lvs and let the dba use as many as they want. We add more lvs to the db as and when the demand arises to add more tablespace.

Hope this helps.

Regds
Sanjay

[A. Clay Stephenson]

I always use "pointers" (better known as symbolic links) to translate between oracle files and raw devices. This also serves as at least a minimal form of documentation. One of the biggest dangers to raw i/o is you. It's very easy to assume that a given LVOL is unused (and Sam will help you to decide that it's unused) when in fact it's vital to Oracle.

Unless you are running Oracle RAC, I think that you will find that the gains are not very great and, in fact, my findings under 11.11 with plenty of memory is that cooked files actually do better than raw i/o. This was not the case under 10.20 or 11.0. Buffer cache should probably be in the range of 800-1600 MB and I prefer static by setting a non-zero bufpages value. My best performance under 11.11 typically occurs with cooked files, about 1500GB of buffer caches, and whomping big (~3GB) SGA's.




If you have OnlineJFS, let me suggest a much less painful method of testing. Mount your tables and indices using convosync=direct,mincache=direct,nodatainlog. This will bypass the UNIX buffer cache and is persformance is essentially indistinguishable from that of raw i/o; moreover; all your conventional back tools still work.

[Zygmunt Krawczyk]

Hi Nelson,
read the doc:
"Best practices for Oracle on HP-UX"
http://h21007.www2.hp.com/dspp/files/unprotected/database/HP3KOracle.ppt

Regards,
Zygmunt

[Steven E. Protter]

A Clay answered a thread of mine on this topic by essentially letting us all know that there were not much of a gain to be had on 11i.

You lose a lot as a systems administrator. A cold backup on a cooked filesystem is a database shutdown and file copy. Its a little more complex, probably forcing you to use the rman utility to backup the database.

1-Correctly configured the hot standby database will be just as reliable either way.

2-No real difference, just harder to backup and restore the database, harder to manage space, because bdf gets you nothing.

SEP

[Hein van den Heuvel]

The TPC test execute relatively light transactions, never parsing any, generating relatively high IO rates. The IO processing is significant for them, and any speedup there will be useful. IF (big if) disk IO takes 10% of the CPU time and RAW saves 10% of that, then the resulting 1% system savinf are worth it for TPC results. But for regular work? Direct-io will get you most of the available benefit.

This generally does NOT apply to your typical business work. There, much more CPU time is spend parsing, creating processes and so on. Just look at the usertime vs system time. RAW will only reduce system time, not usertime. Is the target system doing 10,000 IO/sec or 1,000 IO/sec. For the former it may be worth it, for the latter... no.

No while I find the potential benefit of RAW overhyped, I also find the 'management nightmare' of RAW exagerated.

With the softlinks as pointed out before, some clear LVM LV naming, and a little adminitration driven by Oracle's DBA_DATA_FILES tables there is not much too it... except for the lack of auto-extent.

Back to specific questions.

- EVA BC work on the PV level below the VG.
They work as well for LVs carved from those used by filesystems or raw. No difference.

- REDOs (and TEMP and UNDOs) are your prime and first RAW usage areas. (IMHO). Why? Heavy IO, and no need to backup! For example, you can just temporarely flip to (small) redo files just before a (file based) backup starts, and recreate raw Redo after the backup. An easy script!
- ARCHIVES are NOT candidates for RAW.

Finally... it is not an all or nothing situation. Oracle is parfectly happy having some raw devices (high IO), and some files (automatic extents).

Good luck!

Hein.

[Alzhy]

Another round of replies and I will close this thread.

Why do most of the UNIX DB benchmarks from SUN to HP have storage on RAW then? Is this becuase it's the standard for these benchmarks (ie. TPC?) If I recall, some years ago in a shopp that was migrating from mainframe to UNIX and evaluating platforms - we actually bought the TPC suite and in it it really does not force you to use RAW. Has anyone tried TPC yet on Filesystems?

Our Databases have on average anyware from 20 to 30 Gigabytes of SGA. On our biggest server (a 64GB system) .. I/O's average a sustained 7,000 to 10,000 I/O's per sec. and about 120-150 MB per sec. Currently we've the filesystems on "DirectIO" and VxFS 3.5 and performance is still deemed unacceptable. Planning to go QuickIO but cost is deemed prohibitive - software maintenance alone will be significant.

Some of you claim DirectIO'd cooked filesystems approaches or even exceeds raw implementations with minimal buffer cache allocations -- will this be a general rule these days for 11i + Oracle 9/10 environments?

I did ask the author of HP's Clustering technologies once who claimed he actually still consider himslef a "performance" sepcialist.. with a wink .. he said, "RAW is still faster no matter what." He even mentioned a figure ~ at least 20 per cent and significant memory savings.

[A. Clay Stephenson]

This is really one of those "it depends" answers. How your actual database performs depends much more upon the database design than it does upon the hardware. In your case, since you are already bypassing the buffer cache using the vxfs mount options, I'm going to bet that your gains going to raw i/o are going to be difficult to measure. One mistake often made is that if 1GB of buffer cache is great; 4GB will be even better. It's unusual to find gains above about 1600MB.

It's really time to ask yourself "Would a 2X performance gain be enough?" If the answer to that is no then immediately stop looking at raw vs. cooked i/o, kernel tuning, etc. A 2X gain is almost unheard of except on incredibly badly configured systems and a 0.2X gain is about as much as you can realistically expect -- and that's a stretch as well. If your system is underperforming by large margins then there is really only one place to look -- at the SQL itself. The SQL can yield 10X (or better) improvements.

[Steve Lewis]

Raw has less initial configuration.
Raw forces your DBAs to consider actual tablesizes in advance.
Autoextend on next causes filesystem fragmentation which affects read-ahead on sequential access.
Indexed access on raw requires searching the database index then lseeking to the point in the raw volume.
Indexed access on cooked files requires searching the index to find the logical point in the file, then going thru the filesystem potentially walking thru several inodes (if they aren't cached) to find the point on the raw volume. It may require tuning of your inode cache and will definitely require lots of memory.
Since both methods of storage use database indexes but cooked files also go through what is effectively a filesystem index/cache why don't you just add more resources to your DB or make the large indexes memory-resident?

For a database of your size with that i/o rate, I reckon you are getting close to the comfortable limits of your memory i/o for a single server. You also have the cpus and the network to consider, all competing for memory access. You have gone beyond multiple controller cards and multiple busses and are now into the range of clustered / distributed databases if you want large increases in performance. I am well out of my depth on that sort of stuff, but ACS's comments above are useful too, as always.

[Steven E. Protter]

Depending on your actual use and setup, it is possible to get cooked filesystems to exceed the performance of raw. The data that A. Clay collected suggested the costs don't exceed the less than 10% benefit of cooked filesystems.

You can improve performance a lot more by loading your system with more memory, or even additional processors, though processers mean adding huge licensing costs.

There are so many factors that effect oracle performance. You can even use filesystem tuning techniques to provide block sizes and such that match the way oracle wants.

These benchmark tests should not get you terribly excited. They are set up to provide spectacular results to feed into the marketing machinery of the vendor.

In real life application, the peformance of raw versus cooked filesystem is roughly equal. I run both on my systems and there is very little difference when I collect performance data.

The biggest issue with disk is how fast is it. IF you go raid 10 instead of raid 5 for data, you'll get a massive performance improvement on i/o. If your disks are 15,000 rpm versus 10,000 rpm, you'll get better performance. If your disk admin knows the disk system and lays things out intelligently to avoid contention, you'll get better performance.

Remember also that raw filesystems make backup and recovery more burdensome. Is it worth it? I don't think so in most cases.

Side note: Hope you're enjoying the Harley.

SEP

[Jean-Luc Oudart]

I will go with A.Clay, the Physical layout and hardware should be last resource (unless it has been built on a non-sense model !)

Database design : probably to late at this stage
SQL tuning : a few things can be done here.
- track "bad" SQL and transaction with lon g response time (statspack report, Oracle trace + tkprof). Usually, developers use a much "smaller" database and won't see the performance aspect of new transactions.
- missing index
- statistics not updated
Oracle parameters
OS parameters
Datafile layout , stripping
and finally HW (server & storage)

Also, I suppose if you to validate changes (from a performance prospective), benchmarking would be the ultimate answer.
Unfortunately, you won't be given the opportunity (time, resource, cost) to do it all the time

Regards
Jean-Luc
PS : attached the Unix performance cookbook.