how to reduce checkpoint time?

From: czl(at)iname(dot)com (charles)
To: pgsql-general(at)postgresql(dot)org
Subject: how to reduce checkpoint time?
Date: 2001-11-25 07:25:14
Message-ID: 7e1335c.0111242325.20bf8d1c@posting.google.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Browse pgsql-general by date

  From Date Subject
Next Message Lee Crampton 2001-11-25 11:54:20 Re: Setting up MAKE file for Postgres and C++/Newbie question
Previous Message Brent Verner 2001-11-25 05:34:17 Re: freebsd & postgresql 7.1.3