how to reduce checkpoint time? - Mailing list pgsql-general

From czl@iname.com (charles)
Subject how to reduce checkpoint time?
Date
Msg-id 7e1335c.0111242325.20bf8d1c@posting.google.com
Whole thread Raw
List pgsql-general
summary: I'm after a quick fix. How could I reduce the elapsed
checkpoint time or lower priority of the checkpoint task? I couldn't
find anything in dbadm manual.
   alternatively, are there any timeout settings for cygipc and/or
jdbc driver?

Details:
--------
1. this problem has been posted before to *.hackers, but now I have
better understanding of it - and it is different than I thought :-)

2. I'm running a multiuser test through JDBC. After pg runs the
checkpoint the test goes funny. First cpu and io go down to zero.
After about 1-2 minutes io stays zero, but cpu goes to 100% with 85%
of it in privileged time. All postgress processes are sharing cpu
equally at that time  Even if I kill the test (user processes), pg
processes stay up and eat cpu.

3. This may be caused by a timeout somewhere on the transaction path -
either in cygwin, pg or jdbc driver.  During checkpointing cpu and io
are totally maxed out - the system responds sluggishly even to the
mouse movements. So cygipc or jdbc driver or pg may think that
something is dead then tune out.

4. I'm just after a quick fix. How could I reduce the checkpoint time
or lower priority of the checkpoint task? I couldn't find anything in
sysman manual. Checkpointing more frequently doesn't seem to shorten
it in elapsed time much. I suspect that the elapsed time here is a
problem, as checkpoint takes several minutes.

alternatively, are there any timeout settings for cygipc and/or jdbc
driver? - especially cygipc could be a good suspect here.

charles

P.S. The problem is (most probably) _not_ related to the test program
directly - the problem occurs _always_ after the checkpoint.
checkpoint_timeout time (which I tried from 300 to 1800 seconds).
my other settings were
wal_buffers = 512 #  (tried many values from 64 to 512)
wal_files = 6 # (tried 4, 6, 8)
wal_sync_method = open_sync # (tried fsync, too)
checkpoint_segments = 4 # in logfile segments (16MB each), min 1
checkpoint_timeout = 600 # (tried from 300 to 1800)
deadlock_timeout = 60000 # (tried from 1 to 180 secs)

P.P.S running pg 7.1.2 with jdbc driver 7.1-1.3, jdk 1.3.1_01a (and
others). OS is W2K, WNT

pgsql-general by date:

Previous
From: "Chris McCormick"
Date:
Subject: [HOSTING QUESTION] - Advice on a decent PostgreSQL host?
Next
From: "Lee Crampton"
Date:
Subject: Re: Setting up MAKE file for Postgres and C++/Newbie question