| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Need suggestions for filesystem config, spindle separation, striping for HP-UX/PeopleSoft
The version of the Performance manual is the latest one, for 8.1.7, called "Designing and Tuning for Performance". Although, I first saw this in the 8.0.5 version where it simply said table/index separation was not necessary. The 8.1.7 version goes into a little more detail. It now talks a little about separating them into different tablespaces, but apparently not necessarily separate spindles. I don't understand why separate tablespaces would make any difference, if they are not on separate spindles, at least for the reasons they state - maybe they really mean spindle and are just choosing words badly. Here's what it now says:
Separating Tables and Indexes
It is not necessary to separate a frequently used table from its index.
During the
course of a transaction, the index is read first, and then the table is
read. Because
these I/Os occur sequentially, the table and index can be stored on the
same disk
without contention. However, for very high OLTP systems, separating
indexes from
tables may be required.
Split indexes and tables into separate tablespaces to minimize disk head
movement
and parallelize I/O. Both reads happen faster because one disk head is
on the index
data and the other is on the table data.
The idea of separating objects accessed simultaneously applies to
indexes as well.
For example, if a SQL statement uses two indexes at the same time, then
performance is improved by having each index on a separate disk.
Also, avoid having several heavily accessed tables on the same disk.
This requires
strong knowledge of the application access patterns.
The use of partitioned tables and indexes can improve performance of
operations in
a data warehouse. Divide a large table or index into multiple physical
segments
residing in different tablespaces. All tables that contain large object
datatypes
should be placed into a separate tablespace as well.
All this may be somewhat of a moot point for me, since I have only two spindles (this is a development box). Be that as it may, what I have to work with is what it is.
At any rate, the crux of the issue above seems to be whether it is more beneficial to always have tables on separate spindles from indexes, or to have both tables and indexes striped across multiple disks since both are accessed frequently and both would benefit from distribution given that multiple objects of each type are usually accessed at the same time. I guess ideally, it would be best to stripe both tables and indexes, but on separate arrays, if enough spindles are available. However, this is a tough choice when there are only 2 spindles to work with. It seems that completely separating index from table would lean toward OLTP, while striping would lean toward DSS. This doesn't help too much since our application is a hybrid. However, for development purposes we will probably prefer better performance for massive data transfers, etc. than for the limited amount of OLTP we'll be doing (primarily for testing and demos). So, I think I'd lean toward DSS.
Even though we have only two spindles, I would still like to do what I can, splitting at least between the two. I'm thinking that the best option will be to use HFS, stripe the data tables and indexes together in one volume across the two spindles (non-mirrored), and put the rest on non-striped volumes, maybe as follows:
Non-Striped
----------- ---------------------------- --------------------------------- o/s temp t/s data t/s's oracle s/w redo logs (non-mirrored) index t/s's system t/s control files (non-mirrored) rollback t/s dumps
Note: I'm not overly concerned about redundancy, since we'll be doing daily backups anyway, and it is only a development database. I'd rather have the performance.
What's your critique?
Regards,
John
"Howard J. Rogers" wrote:
> "John K. Hayes" <aikosys_at_earthlink.net> wrote in message
> news:3A89AC42.44ABBA4D_at_earthlink.net...
> > We are installing a new server. Anyone have any opinions on the best
> > database server filesystem configuration to provide best performance for
> > the PeopleSoft HRMS application?
> >
> > Platform: HP 9000 Series L-1000
> > 1 x 440MHz PA8500 CPU
> > 1024MB RAM
> > 2 x 36GB disk (72GB total)
> > HP-UX 11.0 64-bit O/S
> > Oracle 8.1.7 DBMS
> > Apache Web Server and PeopleSoft8 A/S to run on this same box
> >
> > Question: What is the best disk spindle separation scheme for the
> > following oracle object types: data tables, indexes, rollbacks, redo
> > logs, system tables, temp tables, oracle software files
> >
> > Question: I always used to see that Oracle recommended placing data
> > tables and their indexes on separate spindles. However, in the 8.0
> > performance tuning manuals they say that this is not necessary since the
> > index is read first, then the table afterward and therefore there is no
> > disk contention issue. If so, wouldn't this have always been the case,
> > or is this something new from 8.0 onward?
>
>> > the PeopleSoft HRMS application?
> >
> > Question: Do you recommend disk striping? What stripe size best suits
>
>> > spindles (providing limited capability to separate objects by spindle),
> > Question: As an example, on a system with only two large capacity
>
>
>> >
> >
> > Question: Should anything not be striped (e.g. system tables, rollbacks,
> > oracle software files, etc.)?
> >
> > Thanks for any tips!
> > John K. Hayes
> > AikoSys, Inc.
> > aikosys_at_earthlink.net
> >
> >
 |
 |