Re: Tuning Help - What did I do wrong? - Mailing list pgsql-performance

From Josh Trutwin
Subject Re: Tuning Help - What did I do wrong?
Date
Msg-id 20071004142852.3cee0f42@joplin.trutwins.homeip.net
Whole thread Raw
In response to Re: Tuning Help - What did I do wrong?  ("Kevin Grittner" <Kevin.Grittner@wicourts.gov>)
List pgsql-performance
On Thu, 04 Oct 2007 14:03:07 -0500
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> wrote:

> 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.

Good point.

> > 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.

I turned off all but row level since I decided to try turning
autovacuum on.

> 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.

Thanks - I put some additional information in replies to Scott, but
mainly the performance of the web sites that are talking to postgres
is the problem - people calling in, etc.  I turned on slow query
logging to see if I can find if it's particular queries or something
else?

Josh

pgsql-performance by date:

Previous
From: "Kevin Grittner"
Date:
Subject: Re: Tuning Help - What did I do wrong?
Next
From: Ben
Date:
Subject: Re: quickly getting the top N rows