Re: Configuring PostgreSQL to minimize impact of checkpoints

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Jack Orenstein <jao(at)geophile(dot)com>
Cc: Jan Wieck <JanWieck(at)Yahoo(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Configuring PostgreSQL to minimize impact of checkpoints
Date: 2004-05-11 03:23:49
Message-ID: 24638.1084245829@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Jack Orenstein <jao(at)geophile(dot)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

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Matthew Nuzum 2004-05-11 13:35:18 Re: Configuring PostgreSQL to minimize impact of checkpoints
Previous Message Bruce Momjian 2004-05-11 02:33:27 Re: Configuring PostgreSQL to minimize impact of checkpoints