X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id C09B4D1B906 for ; Wed, 24 Sep 2003 21:24:27 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 08187-10 for ; Wed, 24 Sep 2003 18:24:19 -0300 (ADT) Received: from localhost.localdomain (unknown [65.217.53.66]) by svr1.postgresql.org (Postfix) with ESMTP id B71B8D1BB36 for ; Wed, 24 Sep 2003 18:24:15 -0300 (ADT) Received: from thorn.mmrd.com (thorn.mmrd.com [172.25.10.100]) by localhost.localdomain (8.12.8/8.12.8) with ESMTP id h8OKHMqd011216 for ; Wed, 24 Sep 2003 16:17:23 -0400 Received: from gnvex001.mmrd.com (gnvex001.mmrd.com [192.168.3.55]) by thorn.mmrd.com (8.11.6/8.11.6) with ESMTP id h8OLOEZ12772 for ; Wed, 24 Sep 2003 17:24:15 -0400 Received: from camel.mmrd.com ([172.25.5.213]) by gnvex001.mmrd.com with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2653.13) id TD8AT5BG; Wed, 24 Sep 2003 17:24:12 -0400 Subject: upping checkpoints on production server From: Robert Treat To: pgsql-performance@postgresql.org Content-Type: text/plain Content-Transfer-Encoding: 7bit X-Mailer: Ximian Evolution 1.0.8 Date: 24 Sep 2003 17:24:14 -0400 Message-Id: <1064438654.25702.2076.camel@camel> Mime-Version: 1.0 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200309/313 X-Sequence-Number: 3679 All this talk of checkpoints got me wondering if I have them set at an optimum level on my production servers. I noticed the following in the docs: "There will be at least one 16 MB segment file, and will normally not be more than 2 * checkpoint_segments + 1 files. You can use this to estimate space requirements for WAL. Ordinarily, when old log segment files are no longer needed, they are recycled (renamed to become the next segments in the numbered sequence). If, due to a short-term peak of log output rate, there are more than 2 * checkpoint_segments + 1 segment files, the unneeded segment files will be deleted instead of recycled until the system gets back under this limit." In .conf file I have default checkpoints set to 3, but I noticed that in my pg_xlog directory I always seem to have at least 8 log files. Since this is more than the suggested 7, I'm wondering if this means I ought to bump my checkpoint segments up to 4? I don't really want to bump it up unnecessarily as quick recover time is important on this box, however if i would get an overall performance boost it seems like it would be worth it, and given that I seem to be using more than the default number anyways... I've always treated wal logs as self maintaining, am I over analyzing this? Another thought popped into my head, is it just coincidence that I always seem to have 8 files and that wal_buffers defaults to 8? Seems like it's not but I love a good conspiracy theory. Robert Treat -- Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL