DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=MkziwCsCsRzslOD+e+ysa45SISS5JBagNXpT/lKk8FUdDb+/c3JxRVoDXPFhXezKuez7eiU6RuyjaRSlfbXs+TXobeUuWb3u5Sj+Hz07WL6tZdO3pxP6C3CufSIbSxBbvTqED/i9HnZoEnBO6gleNRHu3yj+ixSM/062LVkix14=
Message-ID: <8a547c840801250936w59aad548w268f24a5fcc89ccf@mail.gmail.com>
Date: Fri, 25 Jan 2008 09:36:18 -0800
From: "Tory M Blue" <tmblue@gmail.com>
To: "Greg Smith" <gsmith@gregsmith.com>
Subject: Re: Postgres 8.2 memory weirdness
Cc: pgsql-performance@postgresql.org
In-Reply-To: <Pine.GSO.4.64.0801241335180.7217@westnet.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <8a547c840801231023q55796128nb2e88b8c8cfe86e@mail.gmail.com>
	<Pine.GSO.4.64.0801231923420.12679@westnet.com>
	<8a547c840801240946n37c7fad6ycf62187f82b2027c@mail.gmail.com>
	<Pine.GSO.4.64.0801241335180.7217@westnet.com>

On Jan 24, 2008 10:49 AM, Greg Smith <gsmith@gregsmith.com> wrote:
> 8.2.1 has a nasty bug related to statistics collection that causes
> performance issues exactly in the kind of heavy update situation you're
> in.  That's actually why i asked for the exact 8.2 version.  You should
> plan an upgrade as soon as feasible to the current release just to
> eliminate this as a possible influence on your problems.  No need to dump
> the database or do anything fancy, just get the new version going and
> point it at the existing database.

Not seeing any excessive cpu from the stats collector process.. So
maybe not being hit with this bug.

 PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  P SWAP
TIME COMMAND
28445 postgres  15   0  7432  828  408 S    0  0.0   4:15.47 3 6604
4:15 postgres: stats collector process

With the above said, we had started sometime ago to move 8.2.5 into
our environments, so that should be on these servers next week (the
push is a slow process, but we are really liking what we are seeing
for 8.3, so I'm hoping once blessed, i'll push it  thru quickly)..


> > checkpoint_segments = 50
> > checkpoint_timeout = 300
> > checkpoint_warning = 3600s              <--- set this last night and
>
> If you're getting checkpoints every 89 seconds it's no wonder your system
> is dying.  You may need to consider a large increase to
> checkpoint_segments to get the interval between checkpoints to increase.
> It should at least be a few minutes between them if you want any
> reasonable performance level.

I doubled the checkpoint segments yesterday and have not seen any
warnings. Will run with segments of 100 for a while and see how things
look.. Anyway to make sure that there is not a number between 50 and
100 that makes more sense?


> > effective_cache_size = 330000  <-- This appears totally wrong and
> > something I noticed last night.  left over from previous versions of
> > postgres on different hardware. (thinking to set this to 6-7G)
>
> Right, that's where it should be.

We have started some performance analysis and this numvber is sure
affecting performance in good ways by having it set semi correctly.
This has not been pushed (too many changes), but we will continue
performance testing and it will probably make it to prod next week.

Thanks for some sanity checks here Greg, it's truly appreciated.

Tory