X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 86080D1D86C for ; Tue, 3 Feb 2004 23:44:18 +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 04758-05 for ; Tue, 3 Feb 2004 19:44:19 -0400 (AST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id 07DE5D1D52A for ; Tue, 3 Feb 2004 19:44:16 -0400 (AST) 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 i13NiIsO003021; Tue, 3 Feb 2004 18:44:18 -0500 (EST) To: Shane Wright Cc: pgsql-admin@postgresql.org Subject: Re: hanging for 30sec when checkpointing In-reply-to: <40202216.4010608@shanewright.co.uk> References: <40202216.4010608@shanewright.co.uk> Comments: In-reply-to Shane Wright message dated "Tue, 03 Feb 2004 22:35:02 +0000" Date: Tue, 03 Feb 2004 18:44:18 -0500 Message-ID: <3020.1075851858@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/38 X-Sequence-Number: 12212 Shane Wright writes: > When the db is under medium-heavy load, it periodically spawns a > 'checkpoint subprocess' which runs for between 15 seconds and a minute. It sounds like checkpoint is saturating your disk bandwidth. > I've looked at the documentation and various bits about adjusting > checkpoint segments and timings - but it seems reducing segments/timeout > is implied to be bad, but it seems to me that increasing either will > just make the same thing happen less often but more severely. I was actually just about to suggest reducing the inter-checkpoint interval. That should reduce the volume of data that needs to be written per checkpoint. Might spread the pain a little thinner at least. If you think you should have more disk bandwidth available than the system seems to be using, maybe there is something wrong with the disk or kernel configuration, but that's way out of my league to diagnose. You'd probably need to find some kernel hackers to help you with that. BTW, Jan Wieck has been doing some work recently to try to reduce the "I/O storm at checkpoint" effect, but it won't appear till 7.5. regards, tom lane