X-Original-To: pgsql-hackers-pitr-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id AB541D1B470; Tue, 2 Mar 2004 22:16:32 +0000 (GMT) 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 60509-08; Tue, 2 Mar 2004 18:16:23 -0400 (AST) Received: from cmailm5.svr.pol.co.uk (cmailm5.svr.pol.co.uk [195.92.193.21]) by svr1.postgresql.org (Postfix) with ESMTP id 31848D1C96F; Tue, 2 Mar 2004 18:16:16 -0400 (AST) Received: from modem-2578.lemur.dialup.pol.co.uk ([217.135.138.18] helo=LaptopDellXP) by cmailm5.svr.pol.co.uk with esmtp (Exim 4.14) id 1AyIBT-0003od-4X; Tue, 02 Mar 2004 22:16:15 +0000 Reply-To: From: "Simon Riggs" To: "'Tom Lane'" Cc: "'Rob Fielding'" , , Subject: Re: [PERFORM] WAL Optimisation - configuration and usage Date: Tue, 2 Mar 2004 22:16:12 -0000 Organization: 2nd Quadrant Message-ID: <004801c400a3$f9696640$5baa87d9@LaptopDellXP> MIME-Version: 1.0 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.2627 Importance: Normal In-Reply-To: <24768.1078189289@sss.pgh.pa.us> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2727.1300 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/4 X-Sequence-Number: 28 >Tom Lane > "Simon Riggs" writes: > > - why checkpoint interval of 300 secs causes them to happen every 10 > > mins in quieter periods; is that an occaisional update occurring? > > There is code in there to suppress a checkpoint if no WAL-loggable > activity has happened since the last checkpoint. Not sure if that's > relevant to the issue or not though. Thanks Tom, at least that clears up why the checkpoints are off. I must admit, I'm taken aback though: I'd prefer it if it DIDN'T do that. If the system is quiet, the odd checkpoint doesn't matter that much - however, taking a long time to return the xlog files to the *desired* state of having many pre-allocated log files is not a great thing. What do you think about continuing to checkpoint normally until the number of xlog files has returned to 2*checkpoint_segments+1, then allowing a slow down of checkpoints when quiet? It would be easy enough to set a variable true while rearranging files to the limit, then set it false when the limit has been hit and then using that to activate the slow-down code (not that I know where that is mind...). However, that would require some backend to postmaster ipc, which may be a problem. Or perhaps the real problem is only recycling one file at a time - if we're running this as the checkpoint process it wouldn't be a problem to recycle more than one at the same time would it? The reason for my interest is: when archiving logs for PITR, there may be occasional long pauses while waiting for tape mounts (typically 30 minutes from notification to change). These pauses could (and therefore will eventually for some people) cause severe log file build up, and I'm interested in making sure this build up doesn't take too long to clear. Forgetting the archival API stuff for a second, this is roughly the same situation as Rob is experiencing (or at least causing him to pause and think). Best Regards, Simon Riggs