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 C3BCBD1B538 for ; Thu, 25 Sep 2003 15:23:51 +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 17149-03 for ; Thu, 25 Sep 2003 12:23:19 -0300 (ADT) Received: from localhost.localdomain (unknown [65.217.53.66]) by svr1.postgresql.org (Postfix) with ESMTP id C42CDD1B4ED for ; Thu, 25 Sep 2003 12:23:17 -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 h8PEGUqd018474; Thu, 25 Sep 2003 10:16:32 -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 h8PFNCZ29796; Thu, 25 Sep 2003 11:23:12 -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 TD8AT88M; Thu, 25 Sep 2003 11:23:11 -0400 Subject: Re: upping checkpoints on production server From: Robert Treat To: Tom Lane Cc: pgsql-performance@postgresql.org In-Reply-To: <25883.1064440665@sss.pgh.pa.us> References: <1064438654.25702.2076.camel@camel> <25883.1064440665@sss.pgh.pa.us> Content-Type: text/plain Content-Transfer-Encoding: 7bit X-Mailer: Ximian Evolution 1.0.8 Date: 25 Sep 2003 11:23:12 -0400 Message-Id: <1064503392.30308.2160.camel@camel> Mime-Version: 1.0 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200309/320 X-Sequence-Number: 3686 On Wed, 2003-09-24 at 17:57, Tom Lane wrote: > Robert Treat writes: > > 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? > > Hm. What is the typical delta in the mod times of the log files? It > sounds like you are in a regime where checkpoints are always triggered > by checkpoint_segments and never by checkpoint_timeout, in which case > increasing the former might be a good idea. Or decrease the latter, > but that could put a drag on performance. > # ls -lht /var/lib/pgsql/data/pg_xlog/ total 129M -rw------- 1 postgres postgres 16M Sep 25 11:12 0000006E00000059 -rw------- 1 postgres postgres 16M Sep 25 11:12 0000006E0000005A -rw------- 1 postgres postgres 16M Sep 25 11:08 0000006E00000058 -rw------- 1 postgres postgres 16M Sep 25 11:05 0000006E0000005F -rw------- 1 postgres postgres 16M Sep 25 11:02 0000006E0000005E -rw------- 1 postgres postgres 16M Sep 25 10:59 0000006E0000005D -rw------- 1 postgres postgres 16M Sep 25 10:55 0000006E0000005B -rw------- 1 postgres postgres 16M Sep 25 10:51 0000006E0000005C #ls -lht /var/lib/pgsql/data/pg_xlog/ total 129M -rw------- 1 postgres postgres 16M Sep 25 10:52 0000006E00000054 -rw------- 1 postgres postgres 16M Sep 25 10:51 0000006E00000053 -rw------- 1 postgres postgres 16M Sep 25 10:49 0000006E00000052 -rw------- 1 postgres postgres 16M Sep 25 10:45 0000006E00000059 -rw------- 1 postgres postgres 16M Sep 25 10:40 0000006E00000057 -rw------- 1 postgres postgres 16M Sep 25 10:37 0000006E00000058 -rw------- 1 postgres postgres 16M Sep 25 10:33 0000006E00000056 -rw------- 1 postgres postgres 16M Sep 25 10:29 0000006E00000055 from the 7.4 docs: "Checkpoints are fairly expensive because they force all dirty kernel buffers to disk using the operating system sync() call. Busy servers may fill checkpoint segment files too quickly, causing excessive checkpointing." it goes on to mention checkpoint_warning, which I don't have in 7.3, but I think this is a case where I'd likely see those warnings. The server in question has a fairly high write/read ratio and is fairly busy (over 100 tps iirc). since more often than not I don't make it to 5 minutes, seems like upping checkpoint segments is the way to go, right? Robert Treat -- Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL