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 6DFF1D1D17E for ; Tue, 3 Feb 2004 22:36:11 +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 80661-02 for ; Tue, 3 Feb 2004 18:36:10 -0400 (AST) Received: from server.lan.shivers.org.uk (shivers1.gotadsl.co.uk [62.3.255.42]) by svr1.postgresql.org (Postfix) with ESMTP id 9BE37D1C511 for ; Tue, 3 Feb 2004 18:36:07 -0400 (AST) Received: from shanewright.co.uk (shivers [10.0.1.1]) by server.lan.shivers.org.uk (Postfix) with ESMTP id 5E4534B8029 for ; Tue, 3 Feb 2004 22:35:57 +0000 (GMT) Message-ID: <40202216.4010608@shanewright.co.uk> Date: Tue, 03 Feb 2004 22:35:02 +0000 From: Shane Wright User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.6) Gecko/20040113 X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-admin@postgresql.org Subject: hanging for 30sec when checkpointing Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/37 X-Sequence-Number: 12211 Hi, I'm running a reasonable sized (~30Gb) 7.3.4 database on Linux and I'm getting some weird performance at times. When the db is under medium-heavy load, it periodically spawns a 'checkpoint subprocess' which runs for between 15 seconds and a minute. Ok, fair enough, the only problem is the whole box becomes pretty much unresponsive during this time - from what I can gather it's because it writes out roughly 1Mb (vmstat says ~1034 blocks) per second until its done. Other processes can continue to run (e.g. vmstat) but other things do not (other queries, mostly running 'ps fax', etc). So everything gets stacked up till the checkpoint finishes and all is well again, untill the next time... This only really happens under medium/high load, but doesn't seem related to the length/complexity of transactions done. The box isn't doing a lot else at the same time - most queries some in from separate web server boxes. The disks, although IDE, can definately handle more than 1Mb/sec - even with multiple concurrent writes. The box is powerful (2.6Ghz Xeon, 2Gb RAM). Its a clean compile from source of 7.3.4, although I can't really upgrade to 7.4.x at this time as I can't afford the 18 hours downtime to dump/restore the database. Fsync is on. Most other settings at their defaults. 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. If it makes any odds, this seems to happen much more often when doing bulk UPDATEs and INSERTs - athough these are in transactions grouping them together - and they don't affect the same tables as other queries that still get stalled (no lock contention causing the problem). What am I missing? I'm sure I'm missing something blatantly obvious, but as it's only really happening on production systems (only place with the load and the volume of data) I'm loathe to experiment. Any help appreciated, Cheers, Shane