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

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 Sat Feb 17 2001 - 07:14:28 CST