From: Tom Lane <tgl@sss.pgh.pa.us>
To: David Parker <dparker@tazznetworks.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: checkpoint segments
Date: Mon, 16 May 2005 00:34:21 -0400
Message-ID: <8842.1116218061@sss.pgh.pa.us> (raw)
In-Reply-To: <07FDEE0ED7455A48AC42AC2070EDFF7C74609A@corpsrv2.tazznetworks.com>
References: <07FDEE0ED7455A48AC42AC2070EDFF7C74609A@corpsrv2.tazznetworks.com>
"David Parker" <dparker@tazznetworks.com> writes:
> I was recently running a test with multiple client shell processes
> running psql commands (inserts) when all the client processes appeared
> to hang simultaneously. I assumed that I had an application deadlock
> somewhere, but after a few seconds - less than a minute, but certainly
> noticeable - all the clients picked up again and went on their way.
>
> In the database log at that time there was a "recycling transaction log"
> message which seems to correspond to the time when the clients were
> paused, though I don't have it concretely correlated.
I think what you saw was the disk being hogged by checkpoint writes.
"Recycling transaction log" is a routine operation, and by itself is a
reasonably cheap operation, but it's only done as the last step in a
checkpoint (in fact, from a technical point of view, it's done after the
checkpoint finishes). My guess is that the actual performance hit
occurred while the checkpoint was pushing out dirty buffers.
What you want is to reduce the amount of deferred I/O that has to happen
when a checkpoint occurs. There is not any way to do that before PG
8.0 (the obvious idea of reducing the interval between checkpoints is
counterproductive, IMHO). In 8.0 you can fool around with the bgwriter
parameters with an eye to "dribbling out" writes of dirty pages between
checkpoints.
regards, tom lane
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: tgl@sss.pgh.pa.us, dparker@tazznetworks.com
Subject: Re: checkpoint segments
In-Reply-To: <8842.1116218061@sss.pgh.pa.us>
* 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