Re: Scaling with memory & disk planning (was Re: Non-linear

On Thu, 30 May 2002, Steve Wolfe wrote:

> > Keeping indexes on a separate drive is also traditional database advice,
> > but I don't have any feeling for how much it matters in Postgres.
>
> My gut feeling is that you'd be better off taking two drives and
> striping them RAID 0 instead of putting data on one and indexes on
> another. Either way, you have the same potential total throughput, but if
> they're in the RAID array, then you can use the total throughput more
> easily, and in more situations - you don't have to depend on reading two
> seperate pieces of data simultaneously to utilize the entire throughput.

I'd go with that too. Looking at the stuff I'm playing with right
now, which is heavily random-read oriented, When I get 5000 rows
back out of a table, the system does about 5020 reads. Since it's
highly unlikely that more than a very few of those rows would be
in the same disk block, I expect that the system is only doing
about 20-30 index block reads here, and the rest is on the table.
Splitting this by index vs. table would put 99% of the burden of
this query on the table's drive.

The log-file situation tends to be somewhat different, since you're
also looking at different types of disk access. Log files just
don't get randomly accessed, and so having only one disk arm
available for them is not really a disadvantage. So it's usually
fine on a single disk (or mirrored pair). Giving more disk arms to
a random-access application (such as pulling blocks out of an
(unsorted) table based on what you retrieved from an index) can
provide a lot of help, though, so spreading it across many disks
makes sense.

cjs
--
Curt Sampson <cjs(at)cynic(dot)net> +81 90 7737 2974 http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light. --XTC