RE: Diskgroups for redo, controlfiles, and temp?
Date: Thu, 8 May 2014 16:34:43 -0500
Message-ID: <AD8FE6616C097545A4C9A8B0792909AC2E6EB25D45_at_DNBEXCH01.dnbint.net>
Update: I confirmed with our SAN team what I assumed - all of the SAN is viewed as x number of huge pools of disks, separated mainly by the business class of disk. This means even if additional diskgroups are created they more than likely will get LUNs built off logical disks with stripes on the same physical disks. It also means that separate databases on different servers/clusters also may have slices on the same disks. About as SAME as you can get.
Dave Herring
-----Original Message-----
From: Mark W. Farnham [mailto:mwf_at_rsiz.com]
Sent: Wednesday, May 07, 2014 10:53 AM
To: Herring, David; oracle-l_at_freelists.org
Subject: RE: Diskgroups for redo, controlfiles, and temp?
First, yes.
Second, given the high i/o capacity of many systems available a lot of folks don't NEED to do this.
I think a pretty good overview of the overall situation can be found by searching the web for "Go Faster Stripes Oracle Scratchpad" (smirk).
You'll also find a *lot* of material floating around that you're unlikely to make redo log writes and syncs faster by splitting them off onto flash. Kevin Closson probably describes this best. He is not wrong. His arguments hold up even if you are using non-flash persistent memory SSD, because it is not the drain speed that is the issue.
But what you *may* do by separating redo is remove a lot of heat from the rest of the disk farm and possibly save money compared to putting itsy bitsy (in modern terms) redo logs on the minimum size independently accessible path to media you are likely to allocate from your SAN. And of course then it is also separated as a source of reads feeding arch if that is an issue. (Archiving is entirely omitted from most studies on separating redo onto separate storage, as is competitive read load on the same media.)
Redo being written often is a very high rate of tiny writes. Removing those competitive seeks from spinning rust can be valuable in interrupting the other uses of your disk farm less. Throughput is very unlikely to be an issue except possibly for arch. (You probably should not have arch on the same physical underlying storage as your database files and what not for a lot of reasons.)
There is definitely dynamic tension between implementing as few disk groups as possible as simply as possible all in one piece and carving things up for independence of access. I believe at the very least you should rationalize disk groups by the underlying speed of the components.
For TPC benchmarks I believe vendors still typically split everything out, there would probably best the place to look for documented performance advantages of splitting everything out. Unfortunately because they tend to split everything out, isolating the advantage of just splitting out one type of file may be difficult.
Controlfiles are also pretty small things that tend to be written a lot per unit size.
IF you can allocate truly independently accessible TEMP LUNs, there is still an interesting dynamic interplay of load: Consider that whenever you're using TEMP there is a bit of the job that is loading up TEMP from the non-TEMP sources. Then there might be a bit of the job that is reading and writing TEMP and not the non-TEMP media. And then there is a bit of the job that is reading TEMP and possibly writing back to non-TEMP media (or sending a projection to the client).
So the part that first loads up TEMP from non-TEMP sources would always be marginally faster if it were not being molested by all the bits of the other jobs operating on TEMP. So some loads look great with a separated out TEMP. But you can also have an imbalance of i/o load on TEMP that is probably constructed of fewer total devices than your non-TEMP media from the other bits of the load. Depending on the texture of this load, it is sometimes useful to have independent TEMPs and/or have them on faster media. Your mileage will vary. Fortunately changing the location of TEMP later is not a big deal. It remains a shame that TEMP is an attribute of user rather than being something you can override by session and instance, but that is a different story.
In summary, separation is not a dead duck of history, BUT the question arises whether your estimated iops load and throughput loads compared to your provisioned storage are even close to needing that level of engineering. If you keep the underlying assemblage of LUNs BORING, retrofitting a change should not be difficult.
Read James Morle's SANE SAN stuff, too. Especially the updates for a modern disk farm. Then you'll be well armed to have this discussion (though I'm betting you already are.)
mwf
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Herring, David
Sent: Wednesday, May 07, 2014 10:32 AM
To: oracle-l_at_freelists.org
Subject: Diskgroups for redo, controlfiles, and temp?
Folks,
Has anyone ever run into blogs that have demostrated large performance gains, when on a SAN, by splitting out redo, controlfiles, and temp onto their own diskgroups? I don't recall ever seeing this as a best practice and in this periodic discussion brought up to our group I believe has agreed. I'm getting pressure from an outside resource to split out these file types for performance reasons yet haven't received anything specific to explain why and the arguments presented have been high level and similar to arguments from long ago on older hardware.
Dave Herring
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Thu May 08 2014 - 23:34:43 CEST