From: Kevin Grittner <Kevin.Grittner@wicourts.gov>
To: pgsql-performance@postgresql.org
To: Josh Trutwin <josh@trutwins.homeip.net>
Subject: Re: Tuning Help - What did I do wrong?
Date: Thu, 04 Oct 2007 14:03:07 -0500
Message-ID: <4704F29B.EE98.0025.0@wicourts.gov> (raw)
In-Reply-To: <20071004102804.3418912e@joplin.trutwins.homeip.net>
References: <20071004102804.3418912e@joplin.trutwins.homeip.net>
>>> On Thu, Oct 4, 2007 at 10:28 AM, in message
<20071004102804.3418912e@joplin.trutwins.homeip.net>, Josh Trutwin
<josh@trutwins.homeip.net> wrote:
> running postgres 8.1.4
> # cat /proc/meminfo
> total: used: free: shared: buffers: cached:
> Mem: 3704217600 3592069120 112148480 0 39460864 2316271616
> shared_buffers = 16384 (was 1000)
> effective_cache_size = 10000 (was 1000)
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.
> stats_command_string = on (was off)
> stats_block_level = on (was off)
> stats_row_level = on (was off)
> 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.
Did you try turning off the collection of those additional statistics?
That isn't free.
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.
-Kevin
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: Kevin.Grittner@wicourts.gov, josh@trutwins.homeip.net
Subject: Re: Tuning Help - What did I do wrong?
In-Reply-To: <4704F29B.EE98.0025.0@wicourts.gov>
* 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