X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 4EE51329D03 for ; Tue, 14 Sep 2004 03:01:09 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 45372-01 for ; Tue, 14 Sep 2004 02:01:01 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id E5164329D2A for ; Tue, 14 Sep 2004 03:01:04 +0100 (BST) 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 i8E210XG028519; Mon, 13 Sep 2004 22:01:00 -0400 (EDT) To: Bruce Momjian Cc: Gaetano Mendola , pgsql-performance@postgresql.org Subject: Re: Determine optimal fdatasync/fsync, O_SYNC/O_DSYNC options In-reply-to: <200409140128.i8E1S9i04905@candle.pha.pa.us> References: <200409140128.i8E1S9i04905@candle.pha.pa.us> Comments: In-reply-to Bruce Momjian message dated "Mon, 13 Sep 2004 21:28:09 -0400" Date: Mon, 13 Sep 2004 22:00:59 -0400 Message-ID: <28518.1095127259@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200409/102 X-Sequence-Number: 8230 Bruce Momjian writes: > If we had a majority of queries filling more than one block we would > be checkpointing like crazy and we don't normally get reports about > that. [ raised eyebrow... ] And of course the 30-second-checkpoint-warning stuff is a useless feature that no one ever exercises. But your logic doesn't hold up anyway. People may be doing large transactions without necessarily doing them back-to-back-to-back; there could be idle time in between. For instance, I'd think an average transaction size of 100 blocks would be more than enough to make fsync a winner. There are 2K blocks per WAL segment, so 20 of these would fit in a segment. With the default WAL parameters you could do sixty such transactions per five minutes, or one every five seconds, without even causing more-frequent-than-default checkpoints; and you could do two a second without setting off the checkpoint-warning alarm. The lack of checkpoint complaints doesn't prove that this isn't a common real-world load. regards, tom lane