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

What is interesting is that even on the traditional system with two disks (ie no striping) one for data and one for indexes then there isn't any evidence that separating the INDEXES and DATA by disk would help without knowing the application. What I like to see is good use of the available disks throughput. If the indexes aren't used that much, for example for reads they are in the buffer cache and writes are low volume either from few inserts, key updates, or just small indexes changes like on one sequential id field where one dirty index block could represent many dirty data blocks and/or the data is heavily read, ie many data block reads (say few rows per data block) verses few index block reads compounded maybe by such things ast the data accessed doesn't fit in buffer cache and updates or inserts that create alot of io for data verses small io for indexes. We could have alot of activity on the data tablespace and low activity on the index tablespace, and if we are talking about the two tablespaces on different disks then we have one disk heavily used and the other under used. One brazen response would be, on a system that small, who cares, but I've been at some very small (little PC) benchmarks like modified TPC-A and TPC-C where such things help a fair bit. Empirical evidence is always great, but there are so many if ands and buts that rules on such things as this are rought to come by.

I like what I think some mentioned of separating INDEXES from DATA at the tablespace level for recovery reasons. IE we can loose the index tablespace and rebuild it. I didn't want to mention a "sloppy" backup strategy because if one good backup policy, one shouldn't ever need to do this, but I have been very thankful at a number of sites where they didn't have a backup and they did get a corrupted index tablesoace. - phewww ! I'm sure no on in this day and age would ever not have a backup! (truth is stranger than fiction)

The idea of putting INDEXES and DATA into two tablespaces on the same disk is a no brainer I'd say - sure, why not. THere isn't any i/o disadvantage and there is and administrative adavantage and as you mentioned apparently an write lock advantage.

On the orginal posters question, it seems to depend so much on what they are doing. For one I didn't get whether they had 2 striped disks and a 3rd disk or if all they had was two striped disks or if they had partitions on each of the two disks plus a third partition that was striped over the two disks. Either way it all depends on what they are doing, where does the application typically wait, how much of the data can be cached in the buffer cache. Typically the waits are on log sync and reads. The reads can be eliminated/reduced by the buffer cache, but the log syncs are a part of life. If there is alot of data changing, I would have a tendency to save the extra disk for redo, but how much data is going through a 2 disk pc setup and .. if .. but ...

Best
Kyle

  With a two disk system, it seems like such a huge list of compromises that it doesn't seem worth res

Given a two disk system, my reaction to would be a bit of a brazen, "who cares".

On Mon, 19 Feb 2001 09:57:18 -0600, "Yong Huang" <yhuang_at_indigopool.com> wrote:

>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
Received on Tue Feb 20 2001 - 03:59:11 CST