Re: Slow Query - Mailing list pgsql-performance

From Kevin Grittner
Subject Re: Slow Query
Date
Msg-id 46DC0CDD.EE98.0025.0@wicourts.gov
Whole thread Raw
In response to Re: Slow Query  (Shawn <postgres@xmtservices.net>)
List pgsql-performance
>>> On Mon, Sep 3, 2007 at 11:57 AM, in message
<20070903095749.5045211b@boffin.xmtservices.net>, Shawn
<postgres@xmtservices.net> wrote:
> Also it runs a lot faster by itself

Given the context of the run, there is a possibility that a checkpoint tends
to fall at this point in the script because you're filling your WAL files.
There is a known issue (which people have been working on fixing in 8.3)
which causes the checkpoint to push a lot of pages to the disk and then wait
on physical writes.  If the VACUUM ANALYZE doesn't turn up anything useful,
an interesting experiment would be to run your script with these
modifications to your postgresql.conf file:

bgwriter_lru_percent = 20.0             # 0-100% of LRU buffers scanned/round
bgwriter_lru_maxpages = 200             # 0-1000 buffers max written/round
bgwriter_all_percent = 10.0             # 0-100% of all buffers scanned/round
bgwriter_all_maxpages = 600             # 0-1000 buffers max written/round

Don't leave these in effect permanently without close attention to the
overall impact.  These settings have worked well for us, but are likely
not to work well for everyone.

-Kevin




pgsql-performance by date:

Previous
From: "Kevin Grittner"
Date:
Subject: Re: Slow Query
Next
From: Shawn
Date:
Subject: Re: Slow Query