Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id E31D79FB56B for ; Thu, 1 Mar 2007 16:13:06 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024) with ESMTP id 08333-06 for ; Thu, 1 Mar 2007 16:12:58 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 072249FB555 for ; Thu, 1 Mar 2007 16:12:59 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id l21KCu5M016283; Thu, 1 Mar 2007 15:12:57 -0500 (EST) To: Benjamin Minshall cc: pgsql-performance@postgresql.org Subject: Re: stats collector process high CPU utilization In-reply-to: <45CCBF13.2000707@intellicon.biz> References: <45CB5D06.7090004@intellicon.biz> <7994.1170960391@sss.pgh.pa.us> <45CB9330.3000603@intellicon.biz> <19935.1170969862@sss.pgh.pa.us> <45CB9C4D.30902@intellicon.biz> <20601.1170972476@sss.pgh.pa.us> <45CBA443.9090101@intellicon.biz> <7959.1170986672@sss.pgh.pa.us> <62089.75.14.32.120.1170994418.squirrel@intellicon.biz> <14668.1170995218@sss.pgh.pa.us> <20070209142459.GB4253@alvh.no-ip.org> <24717.1171033699@sss.pgh.pa.us> <29832.1171036557@sss.pgh.pa.us> <45CCA0DB.2010903@intellicon.biz> <1116.1171038786@sss.pgh.pa.us> <45CCBF13.2000707@intellicon.biz> Comments: In-reply-to Benjamin Minshall message dated "Fri, 09 Feb 2007 13:36:03 -0500" Date: Thu, 01 Mar 2007 15:12:56 -0500 Message-ID: <16282.1172779976@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.065 tagged_above=0 required=5 tests=AWL, BAYES_50, BIZ_TLD, SPF_HELO_PASS, SPF_PASS X-Spam-Level: * X-Archive-Number: 200703/12 X-Sequence-Number: 23417 Benjamin Minshall writes: > Tom Lane wrote: >> It's sounding like what you had was just transient bloat, in which case >> it might be useful to inquire whether anything out-of-the-ordinary had >> been done to the database right before the excessive-CPU-usage problem >> started. > I don't believe that there was any unusual activity on the server, but I > have set up some more detailed logging to hopefully identify a pattern > if the problem resurfaces. A further report led us to realize that 8.2.x in fact has a nasty bug here: the stats collector is supposed to dump its stats to a file at most every 500 milliseconds, but the code was actually waiting only 500 microseconds :-(. The larger the stats file, the more obvious this problem gets. If you want to patch this before 8.2.4, try this... Index: pgstat.c =================================================================== RCS file: /cvsroot/pgsql/src/backend/postmaster/pgstat.c,v retrieving revision 1.140.2.2 diff -c -r1.140.2.2 pgstat.c *** pgstat.c 26 Jan 2007 20:07:01 -0000 1.140.2.2 --- pgstat.c 1 Mar 2007 20:04:50 -0000 *************** *** 1689,1695 **** /* Preset the delay between status file writes */ MemSet(&write_timeout, 0, sizeof(struct itimerval)); write_timeout.it_value.tv_sec = PGSTAT_STAT_INTERVAL / 1000; ! write_timeout.it_value.tv_usec = PGSTAT_STAT_INTERVAL % 1000; /* * Read in an existing statistics stats file or initialize the stats to --- 1689,1695 ---- /* Preset the delay between status file writes */ MemSet(&write_timeout, 0, sizeof(struct itimerval)); write_timeout.it_value.tv_sec = PGSTAT_STAT_INTERVAL / 1000; ! write_timeout.it_value.tv_usec = (PGSTAT_STAT_INTERVAL % 1000) * 1000; /* * Read in an existing statistics stats file or initialize the stats to regards, tom lane