DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=rTeLbaSv/7meFcenWW3VDVK2foGXyeloYC5aJfux1D3WXpc49EOR0VWl6mfUbW19La7HQBUpUpiHx7nkE5BBd3i36t543jygYFPotD8Cr5/kzvK+ZTBgHJCBv+xPVw/+8ETz/clC3wJxvvUH40zQbEh1sltrAgyAVNp2gd9ebQQ=
Message-ID: <dcc563d10710040919k716a51d4ha8522444ecf791fd@mail.gmail.com>
Date: Thu, 4 Oct 2007 11:19:22 -0500
From: "Scott Marlowe" <scott.marlowe@gmail.com>
To: "Josh Trutwin" <josh@trutwins.homeip.net>
Subject: Re: Tuning Help - What did I do wrong?
Cc: pgsql-performance@postgresql.org
In-Reply-To: <20071004102804.3418912e@joplin.trutwins.homeip.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <20071004102804.3418912e@joplin.trutwins.homeip.net>

On 10/4/07, Josh Trutwin <josh@trutwins.homeip.net> wrote:
> We have a pretty busy linux server running postgres 8.1.4, waiting to
> upgrade until 8.3 to avoid dump/restoring twice.

You should immediate update your version to 8.1.whateverislatest.
That requires no dump / restore and it is a bug fix update.  I doubt
this problem is because you're out of date on patches, but who
knows...

> # cat /proc/meminfo
>         total:    used:    free:  shared: buffers:  cached:
> Mem:  3704217600 3592069120 112148480        0 39460864 2316271616
> Swap: 2516918272   270336 2516647936

Well, you've got plenty of memory, and a large chunk is being used as cache.

> The postgresql.conf was basically the default so I decided to
> increase the cache size and a couple paramaters to make more use of
> that memory - here's what I did:
>
> shared_buffers = 16384 (was 1000)
> work_mem = 16384 (was 1024)
> wal_buffers = 24 (was 8)
> checkpoint_segments = 5 (was 3)
> effective_cache_size = 10000 (was 1000)
> stats_command_string = on (was off)
> stats_block_level = on (was off)
> stats_row_level = on (was off)

Your changes seem reasonable.

> Also, the entire cluster gets vacuumed analyzed nightly.

You should look into running the autovacuum daemon.  for heavily used
databases nightly vacuuming may not be enough.

> After making these changes, the performance on the server actually
> worsened.   I slowly backed off on some of the paramaters but didn't
> seem to help.

Most likely turning on stats collection slowed you down a bit.

We need to see examples of what's slow, including explain analyze
output for slow queries.  Also a brief explanation of the type of load
your database server is seeing.  I.e. is it a lot of little
transactions, mostly read, batch processing, lots of users, one user,
etc...   Right now we don't have enough info to really help you.