Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Need suggestions for filesystem config, spindle separation, striping for HP-UX/PeopleSoft

Re: Need suggestions for filesystem config, spindle separation, striping for HP-UX/PeopleSoft

From: Yong Huang <yhuang_at_indigopool.com>
Date: Mon, 19 Feb 2001 09:57:18 -0600
Message-ID: <96rfv6$lhe$1@news.sinet.slb.com>

I don't see the original post. But Kyle, what you say makes very good sense from the hardware standpoint. The advice of putting data and indexes in separate tablespaces and, in fact really, in separate datafiles that reside on different disks, is for traditional machines without RAID. That's also what Howard has in mind. We use NetApp files (RAID 4). All data and index datafiles are in one directory but data and indexes are in different tablespaces for easy management. And also for easy recovery (e.g. in case of only index tablespace corruption, I can recreate it).

However, from the software standpoint, is there any advantage at all, other than easier management? I don't know much but consider how the file system works. If Oracle opens one file to write data, a different file to write indexes, the file lock operation (let's guess it's flock(3B)), among other things, won't contend for one file. If both data and indexes are in the same TBS, it's possible for this contention.

Some time ago, somebody asked if there's any advantage to put DATA and INDEX tablespaces on two logical drives on Windows NT, where the two drives are simply two partitions out of one physical disk. Hardware wise, no; but if you consider file systems, maybe. Here's it may still be flock(3B) or something like partition_lock. Just a guess. No benchmark.

Yong Huang
yhuang_at_indigopool.com

kyle hailey <oraperf_at_yahoo.com> wrote in message news:7rms8t849hpfab33p2ng6kb04hei7a17q8_at_4ax.com...
> RE putting indexes and data in the same tablespace:
>
> > Howard J. Rogers wrote:
> >
> > I don't know what version of the Performance Tuning Manuals you are
 using,
> > but that piece of advice is utter nonsense. It might be fine for reads,
 but
> > what about DML? If you update a table, you also have to update the
> > index(es) on that table -and if indexes and tables are on the same
 spindle,
> > your i/os will inevitably have to queue up one behind the other.
>
>
> "utter nonsense" - that's a bit strong sounding to me. Lets think
> about this ... it is unclear as to whether Indexes and Data need to be
> separate for perfomance reasons.
>
> When we are talking about performance, we are talking about users
> performance. Users wait for data file reads not data file writes
> (with some special case exceptions). It is the DBWR that actually does
> the write, and it does the write at some later time than the actual
> transaction and the order in which the write is done depends of
> various factors. The fact that a user dirties a block in the SGA, ie
> in memory not on disk, from a table and an index which are stored in
> same tablespace (maybe on the same disk or not) doesn't necessarily
> make a difference to the users performance.
>
> There are the cases of interference, when writes are being done on
> files being read. There are the cases where DBWR gets behind in taking
> care of the buffer cache with resulting free buffer waits, write
> complete waits, checkpoint problems etc, but none of this is obviously
> helped or hurt by putting INDEX and DATA in different tablespaces
> because of options like striping and the fact that we don't know when
> and in what order the writes will be done anyway.
>
> The idea of ridgidly dividing data for performance breaks down in the
> world of striping and write back cache. Benchmarks done by Oracle tend
> to stripe data and indexes over pretty much everything available up to
> cetain limits. I know the EMC guys are really touchy about striping
> beyond 16 axes or something, but EMCs are a whole nother ball game
> with typically large write caches. Most serious client applications I
> worked on, and certainly the newer ones that had access to recent disk
> technogy for their original implementations, depended on striping (
> Air France, Bougues, Coface, Aerospatial, Cogema, Swiss PTT, Italy
> PTT, Carrefour, Dresdner, European Community, Alcatel, Baan
> benchmarks, Sap benchmarks, SNECMA, APHP, St Gobain, Total Gaz,
> SNCF, France Telecom, Societe General, MAIF, Gravelow, etc). I have
> seen some important databases that didn't stripe (Handspring for
> example) but those databases weren't even in archive log mode so lots
> to complain about there. I've seen SAP benchmarks done with with
> strategic data layout and then again I've seen them on no-brainer
> striping layouts and the striping cases were definitly better. I take
> it for granted that anyone really interested in performance of an
> important databse is going to stripe. I can imagine some very, very
> specific application that depends on non-striped disk and datafile
> layout but I have yet to run accross one of these that worked better
> than its striped version not to mention with write cache. Separating
> data in the striping, especially with write back cache, becomes more
> of and adminstrative choice.
>
> There are performance cases for separting things that have unusual
> read or write access, such as the special cases of RBS and TEMP of
> course but also batch tables, but this is more DSS.
> OLTP tends to be a good place for striping because of the random
> and frequent transaction natures but its also worth noting that
> Oracle TPC-D (DSS) benchmarks have been massively striped.
>
> Best
> Kyle
>
> www.geocities.com/oraperf
>
Received on Mon Feb 19 2001 - 09:57:18 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US