To: "Luke Lonergan" <llonergan@greenplum.com>
Cc: "Carlo Stonebanks" <stonec.register@sympatico.ca>,
	<pgsql-performance@postgresql.org>
Subject: Re: random_page_costs - are defaults of 4.0 realistic for SCSI RAID 1
In-Reply-To: <C30AF4C7.4230B%llonergan@greenplum.com> (Luke Lonergan's message
	of "Mon\, 10 Sep 2007 13\:19\:03 -0700")
User-Agent: Gnus/5.11 (Gnus v5.11) Emacs/22.1 (gnu/linux)
References: <C30AF4C7.4230B%llonergan@greenplum.com>
From: Gregory Stark <stark@enterprisedb.com>
Organization: EnterpriseDB
Date: Mon, 10 Sep 2007 22:44:05 +0100
Message-ID: <87y7fekxga.fsf@oxford.xeocode.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii


"Luke Lonergan" <llonergan@greenplum.com> writes:

> Should be a lot higher, something like 10-15 is approximating accurate.

Most people's experience is that due to Postgres underestimating the benefits
of caching lowering the random_page_cost is helpful.

> Increasing the number of disks in a RAID actually makes the number higher,
> not lower.  Until Postgres gets AIO + the ability to post multiple
> concurrent IOs on index probes, random IO does not scale with increasing
> disk count, but sequential does, thus the increasing "random page cost" as
> the RAID gets faster.

That does sound right, though I don't think it's very common. If you have very
wide stripes you can get some amazing sequential scan speeds and it won't
really help random access at all. This is especially helpful if you're in an
environment where you don't care about the costs you're imposing on other
processes, such as a data warehouse where you have a single large batch query
running at a time.

What I don't understand is the bit about "until Postgres gets AIO + the
ability to post multiple concurrent IOs on index probes". Even with AIO your
seek times are not going to be improved by wide raid stripes. And you can't
possibly find the page at level n+1 before you've looked at the page at level
n. Do you mean to be able to probe multiple index keys simultaneously? How
does that work out?

-- 
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com