From: Kevin Grittner <Kevin.Grittner@wicourts.gov>
To: pgsql-performance@postgresql.org
To: Shawn <postgres@xmtservices.net>
Subject: Re: Slow Query
Date: Mon, 03 Sep 2007 13:32:13 -0500
Message-ID: <46DC0CDD.EE98.0025.0@wicourts.gov> (raw)
In-Reply-To: <20070903095749.5045211b@boffin.xmtservices.net>
References: <20070901102947.0c0a50a3@boffin.xmtservices.net>
<22800.1188670194@sss.pgh.pa.us>
<20070901131816.3f7ef91a@boffin.xmtservices.net>
<20070901173519.0ed11401@boffin.xmtservices.net>
<25184.1188702010@sss.pgh.pa.us>
<20070903095749.5045211b@boffin.xmtservices.net>
>>> 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
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, postgres@xmtservices.net
Subject: Re: Slow Query
In-Reply-To: <46DC0CDD.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