Message-Id: <4704F29B.EE98.0025.0@wicourts.gov>
Date: Thu, 04 Oct 2007 14:03:07 -0500
From: "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
To: <pgsql-performance@postgresql.org>,
	"Josh Trutwin" <josh@trutwins.homeip.net>
Subject: Re: Tuning Help - What did I do wrong?
References: <20071004102804.3418912e@joplin.trutwins.homeip.net>
In-Reply-To: <20071004102804.3418912e@joplin.trutwins.homeip.net>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

>>> On Thu, Oct 4, 2007 at 10:28 AM, in message
<20071004102804.3418912e@joplin.trutwins.homeip.net>, Josh Trutwin
<josh@trutwins.homeip.net> wrote:=20
> running postgres 8.1.4
=20
> # cat /proc/meminfo
>         total:    used:    free:  shared: buffers:  cached:
> Mem:  3704217600 3592069120 112148480        0 39460864 2316271616
=20
> shared_buffers =3D 16384 (was 1000)
> effective_cache_size =3D 10000 (was 1000)
=20
It's kind of silly to tell PostgreSQL that its total cache space is 10000
pages when you've got more than that in shared buffers plus all that OS
cache space.  Try something around 285000 pages for effective_cache_size.
=20
> stats_command_string =3D on (was off)
> stats_block_level =3D on (was off)
> stats_row_level =3D on (was off)
=20
> 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.
=20
Did you try turning off the collection of those additional statistics?
That isn't free.
=20
You didn't get specific about what you saw in performance problems.  If
you are seeing occasional "freezes" of all queries, you are likely looking
at a known issue with "spikiness" of disk output.  For some this can be
corrected by using very aggressive background writer settings.  Some have
solved it by disabling OS write delays.  Some haven't found a solution and
are waiting for 8.3; there have been some serious changes made to attempt
to resolve this issue.
=20
-Kevin
=20