pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Sean Chittenden <sean@chittenden.org>
To: scott.marlowe <scott.marlowe@ihs.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Moving postgresql.conf tunables into 2003...
Date: Thu, 3 Jul 2003 16:23:51 -0700
Message-ID: <20030703232351.GS72567@perrin.int.nxad.com> (raw)
In-Reply-To: <Pine.LNX.4.33.0307031405180.22660-100000@css120.ihs.com>
References: <20030703190502.GR72567@perrin.int.nxad.com>
	<Pine.LNX.4.33.0307031405180.22660-100000@css120.ihs.com>

> > What are the odds of going through and revamping some of the
> > tunables in postgresql.conf for the 7.4 release?  I was just
> > working with someone on IRC and on their 7800 RPM IDE drives,
> > their random_page_cost was ideally suited to be 0.32: a far cry
> > from 4.  Doing so has been a win across the board and the problem
> > query went from about 40sec (seq scan) down to 0.25ms (using idx,
> > higher than 0.32 resulted in a query time jump to 2sec, and at 0.4
> > it went back up to a full seq scan at 40sec).
> 
> I'm the guy who advocates settings of 1 to 2, and that still sounds
> low to me. :-) I'm wondering if the effective_cache_size was set
> properly, as well as there be enough buffers allocated.
> 
> I generally set effective cache size to 100,000 pages (800 megs or
> so) on my box, which is where it sits most days.  with this setting
> I've found that settings of under 1 are not usually necessary to
> force the planner to take the path of righteousness (i.e. the
> fastest one :-) 1.2 to 1.4 are optimal to me.

This is a nightly report that's run, cache sizes won't impact
performance of the query at all.  The planner was consistently
choosing a sequential scan over using the index until the
random_page_cost was set to 0.32.  After adjustment, the query just
flies (0.25ms@0.32 vs 0.350s@0.33 vs. 40s@>0.4).  Since it's a nightly
report that only gets performed once a day and data is COPY'ed in once
every few minutes, there's a huge amount of data that's not cached nor
should it be.

> Since theoretically a random page of of 1 means no penalty to move
> the heads around, and there's ALWAYS a penalty for moving the heads
> around, we have to assume:
> 
> 1: That either the planner is making poor decisions on some other
> variable, and we can whack the planner in the head with a really low
> random page count.

By all accounts of having played with this query+data, this is the
correct assumption from what I can tell.

> OR 
> 
> 2: The other settings are suboptimal (buffers, sort_mem,
> effective_cache_size, etc...) and lowering random page costs helps
> there.

None of those other than possibly sort_mem had any impact on the
query, but even then, lower sort_mem doesn't help until the data's
been picked out of the table.  Sorting ~16k of rows is quicker with
more sort_mem.  Higher sort_mem has zero impact on fetching ~16K rows
out of a table with 40M rows of data.  Getting the planner to pick
using the index to filter out data inserted in the last 3 days over
doing a seq scan...  well, I don't know how you could do that without
changing the random_page_cost.  A good thump to the side of the head
would be welcome too if I'm wrong, just make sure it's a good thump
with the appropriate clue-bat.

> I've always wondered if most performance issues aren't a bit of both.  

Eh, in my experience, it's generally that random_page_cost needs to be
adjusted to match the hardware and this value every year with new
hardware, seems to be getting lower.

> The answer, of course, is fixing the planner so that a
> random_page_cost of anything less than 1 would never be needed,
> since by design, anything under 1 represents a computer that likely
> doesn't exist (in theory of course.)  A 1 would be a machine that
> was using solid state hard drives and had the same cost in terms of
> OS paths to do random accesses as sequential.

Well, this could be a bug then, but I'm skeptical.  What's odd to me
is that hanging the value between 0.32, 0.33, and 0.4 all radically
change the performance of the query.

> What constants in the planner, and / or formulas would be the likely
> culprits I wonder?  I've wandered through that page and wasn't sure
> what to play with.

random_page_cost should be proportional to the seek time necessary for
the disk to find a page of data on its platters.  It makes sense that
this value, as time progresses, gets smaller as hardware gets faster.

-sc

-- 
Sean Chittenden



view thread (45+ messages)  latest in thread

Message-ID: <20030703232351.GS72567@perrin.int.nxad.com>
Permalink:  ../20030703232351.GS72567@perrin.int.nxad.com/
Also on:    postgresql.org/message-id/20030703232351.GS72567@perrin.int.nxad.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: sean@chittenden.org, scott.marlowe@ihs.com
  Subject: Re: Moving postgresql.conf tunables into 2003...
  In-Reply-To: <20030703232351.GS72567@perrin.int.nxad.com>
  References: <20030703190502.GR72567@perrin.int.nxad.com> <Pine.LNX.4.33.0307031405180.22660-100000@css120.ihs.com> <20030703232351.GS72567@perrin.int.nxad.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox