Yes, complicated with as simple a combination as tables and their indexes.
Consider even more variables introduced by using LOBs and interMedia Text.
In a past job, I used to make myself crazy analyzing and placing all the
segments involved with interMedia-indexed CLOBs. Here's what I came up
with:
- the main table on its own spindle
- the out-of-line CLOB segments on a 2nd spindle
- the "regular" indexes for the main table on a 3rd spindle
- the "I" table part of the interMedia index on a 4th spindle
- the "K", "R" and "N" tables and "I" index segments on a 5th spindle
This was all on 22 JBODs (36GB each) under 8.1.7 / Win2k. The 13 million
CLOB documents that were online and indexed made the database about 200GB.
It was beautiful (to this nerd) to watch the disk I/O on the Win2k
Performance Monitor as different queries would hit different parts of those
segments at different times. Though it probably wasn't, the PerfMon graphs
looked like there was a lot of parallel work going on across all those
spindles.
That database was used by only one or a very vew users at a time, doing
similar queries, so I think performance benefitted from that segment
separation. I would definitely agree, though, that with dozens or hundreds
of concurrent users, I would have had to closely monitor tablespace /
datafile I/O and shuffle datafiles around to better distribute load across
available spindles. That would especially hold true if user activity
coincided with the nightly loading and reindexing of up to 250,000 new
CLOBs.
No particular point here, but I/O distribution was a consideration for
segment-to-tablespace mapping, even though an equal (or greater)
consideration was differing extent sizes - 1MB for tables and "regular"
indexes 100MB for the CLOB segments. ...all on LMTs, of course.
Here at AISD, almost all our 8.1.7 databases live on the 14 RAID-5 LUNs
presented by our HP XP-512 array (that's just something we've gotta live
with, though I'm definitely a BAARFist). Most of the DBs are on 3 HP-UX
boxes, but one is on Win2k. I've not yet had a chance to map all the
pieces of all the DBs to see which pieces share which physical drives, but
I/O hasn't seemed to hurt DB performance. Bad performance continues to be
more than adequately addressed by the horribly-inefficient SQL produced by
our two 3rd Party apps (Student Info. System and Financials System). I
guess that goes to show that segment distribution - even on RAID-5 - is an
insignificant factor when compared to bad SQL (producing tens of millions
of unnecessary logical I/Os, in this case).
Jack C. Applewhite
Database Administrator
Austin Independent School District
Austin, Texas
512.414.9715 (wk)
512.935.5929 (pager)
[EMAIL PROTECTED]
"Cary Millsap"
<[EMAIL PROTECTED] To: Multiple recipients of list
ORACLE-L <[EMAIL PROTECTED]>
sos.com> cc:
Sent by: Subject: RE: should you seperate
indexes from tables in seperate datafiles?
[EMAIL PROTECTED]
.com
07/15/2003 12:19
PM
Please respond to
ORACLE-L
The thing that occurred to me a few years ago (as a result of a test
designed by Craig Shallahamer) is that "what disks do" gets very, very
complicated when you add users. On any system busy enough to have a
performance problem, the odds are usually slim that a disk is just "sitting
there" waiting for your next I/O call. On a busy system, someone else's I/O
call is almost always going to intercede between two of *your* I/O calls.
As has been said many times, many ways...
- DO separate tables and indexes into different tablespaces. There are lots
of reasons you should do this.
- DON'T necessarily feel that you have to put the index and data
tablespaces
on different devices. One decision criterion is performance: don't ever put
two files on the same device if the sum of their I/O-per-second rates
exceeds the I/O-per-second capacity of the device. Another decision
criterion is availability: don't ever put more data on a device than you
can
recover in your acceptable downtime window. The list goes on...
Cary Millsap
...
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
