pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jack Orenstein <jao@geophile.com>
Cc: Jan Wieck <JanWieck@Yahoo.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Configuring PostgreSQL to minimize impact of checkpoints
Date: Mon, 10 May 2004 23:23:49 -0400
Message-ID: <24638.1084245829@sss.pgh.pa.us> (raw)
In-Reply-To: <40A03435.7020300@geophile.com>
References: <200405101909.i4AJ9Wv09415@candle.pha.pa.us>
	<40A03435.7020300@geophile.com>

Jack Orenstein <jao@geophile.com> writes:
> I'm looking at one case in which two successive transactions, each
> updating a handful of records, take 26 and 18 *seconds* (not msec) to
> complete. These transactions normally complete in under 30 msec.

I've seen installations in which it seemed that the "normal" query load
was close to saturating the available disk bandwidth, and the extra load
imposed by a background VACUUM just pushed the entire system's response
time over a cliff.  In an installation that has I/O capacity to spare,
a VACUUM doesn't really hurt foreground query response at all.

I suspect that the same observations hold true for checkpoints, though
I haven't specifically seen an installation suffering from that effect.

Already-committed changes for 7.5 include a background writer, which
basically will "trickle" out dirty pages between checkpoints, thereby
hopefully reducing the volume of I/O forced at a checkpoint.  We have
also got code in place that throttles the rate of I/O requests during
VACUUM.  It seems like it might be useful to similarly throttle the I/O
request rate during a CHECKPOINT, though I'm not sure if there'd be any
bad side effects from lengthening the elapsed time for a checkpoint.
(Jan, any thoughts here?)

None of this is necessarily going to fix matters for an installation
that has no spare I/O capacity, though.  And from the numbers you're
quoting I fear you may be in that category.  "Buy faster disks" may
be the only answer ...

			regards, tom lane



view thread (21+ messages)  latest in thread

Message-ID: <24638.1084245829@sss.pgh.pa.us>
Permalink:  ../24638.1084245829@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/24638.1084245829@sss.pgh.pa.us

 · 

reply

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, jao@geophile.com, JanWieck@Yahoo.com
  Subject: Re: Configuring PostgreSQL to minimize impact of checkpoints
  In-Reply-To: <24638.1084245829@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