X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 1C1B8D1B2A4 for ; Tue, 11 May 2004 00:24:17 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 79032-03 for ; Tue, 11 May 2004 00:23:56 -0300 (ADT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 3F697D1B1A3 for ; Tue, 11 May 2004 00:23:56 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i4B3NnOo024639; Mon, 10 May 2004 23:23:49 -0400 (EDT) To: Jack Orenstein Cc: Jan Wieck , pgsql-performance@postgresql.org Subject: Re: Configuring PostgreSQL to minimize impact of checkpoints In-reply-to: <40A03435.7020300@geophile.com> References: <200405101909.i4AJ9Wv09415@candle.pha.pa.us> <40A03435.7020300@geophile.com> Comments: In-reply-to Jack Orenstein message dated "Mon, 10 May 2004 22:02:29 -0400" Date: Mon, 10 May 2004 23:23:49 -0400 Message-ID: <24638.1084245829@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200405/68 X-Sequence-Number: 6899 Jack Orenstein 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