pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Scott Marlowe <smarlowe@g2switchworks.com>
To: ogjunk-pgjedan@yahoo.com
Cc: pgsql-admin@postgresql.org
Subject: Re: postgres optimization (effective_cache_size)
Date: Mon, 22 Aug 2005 14:43:21 -0500
Message-ID: <1124739800.28179.4.camel@state.g2switchworks.com> (raw)
In-Reply-To: <20050812224819.30546.qmail@web31112.mail.mud.yahoo.com>
References: <20050812224819.30546.qmail@web31112.mail.mud.yahoo.com>

On Fri, 2005-08-12 at 17:48, ogjunk-pgjedan@yahoo.com wrote:
> Hello,
> 
> I followed this advice for picking a good effective_cache_size value
> (below) from Scott Marlowe, and run into a bit of trouble:
> 
> I looked at the `top' output and saw "721380k cached".
> So I calculated the effective cache size using Scott's formula:
> 
>   721380/8 = 90172
> 
> Then I changed my effective_cache size from the previous 10000 to
> 90172:
> 
>   effective_cache_size = 90172   # typically 8KB each
> 
> 
> I restarted PG, and thought I'd see some performance improvements, but
> I actually saw degradation in performance.  All of a sudden a query
> that took a second started taking a few seconds, and consumed more CPU
> than before.

There are a lot of reasons why this may be so, but the most likely is
that when restarting postgresql, the kernel cache got old and was dumped
out, so that it wasn't holding all the data.

Basically, a larger effective cache size favors indexes over seq scan,
since the data are more likely to be in memory when you ask for them.

> Can anyone explain this drop in performance and increase in CPU usage
> and tell me what I did wrong?
> 
> I also noticed that the "721380k cached" number in top dropped to about
> 300000k (about a half).  Maybe that was simply due to PG restart?  If
> so, does that indicate the kernel had about 400,000K worth of PG data
> cached?

That's pretty normal, over time, with postgresql being idle.  Don't
worry about how the query runs the first time you run it so much as how
it runs consistently over time.  If the numbers come back to where they
were, then you've got the right setting.  Like I said, the
effective_cache_size is kind of a lead hammer, used to nudge the planner
one way or another but doesn't need to be exact to be useful.

Take a look at the plan being chosen, it is likely to be a random access
(i.e. use an index) right now rather than a seq scan.  The seq scan
would win on a machine with nothing in the kernel cache, but the index
scan will usually win if everything is already cached.  Again, these are
broad strokes of the brush.  It all depends on the actual % of a table
returned and a few other things...



view thread (2+ messages)

Message-ID: <1124739800.28179.4.camel@state.g2switchworks.com>
Permalink:  ../1124739800.28179.4.camel@state.g2switchworks.com/
Also on:    postgresql.org/message-id/1124739800.28179.4.camel@state.g2switchworks.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-admin@postgresql.org
  Cc: smarlowe@g2switchworks.com, ogjunk-pgjedan@yahoo.com
  Subject: Re: postgres optimization (effective_cache_size)
  In-Reply-To: <1124739800.28179.4.camel@state.g2switchworks.com>
  References: <20050812224819.30546.qmail@web31112.mail.mud.yahoo.com> <1124739800.28179.4.camel@state.g2switchworks.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