Received: from localhost (maia-2.hub.org [200.46.204.187])
	by postgresql.org (Postfix) with ESMTP id 020689FAE4E
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Fri,  9 Feb 2007 12:33:22 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024)
	with ESMTP id 93130-08 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Fri,  9 Feb 2007 12:33:11 -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 670819FA4ED
	for <pgsql-performance@postgresql.org>;
	Fri,  9 Feb 2007 12:33:11 -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 l19GX7h4001117;
	Fri, 9 Feb 2007 11:33:07 -0500 (EST)
To: Benjamin Minshall <minshall@intellicon.biz>
cc: Alvaro Herrera <alvherre@commandprompt.com>,
	pgsql-performance@postgresql.org
Subject: Re: stats collector process high CPU utilization 
In-reply-to: <45CCA0DB.2010903@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>
Comments: In-reply-to Benjamin Minshall <minshall@intellicon.biz>
	message dated "Fri, 09 Feb 2007 11:27:07 -0500"
Date: Fri, 09 Feb 2007 11:33:06 -0500
Message-ID: <1116.1171038786@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200702/103
X-Sequence-Number: 23157

Benjamin Minshall <minshall@intellicon.biz> writes:
> When I checked on the server this morning, the huge stats file has 
> returned to a normal size.  I set up a script to track CPU usage and 
> stats file size, and it appears to have decreased from 90MB down to 
> about 2MB over roughly 6 hours last night.  The CPU usage of the stats 
> collector also decreased accordingly.

> The application logs indicate that there was no variation in the 
> workload over this time period, however the file size started to 
> decrease soon after the nightly pg_dump backups completed.  Coincidence 
> perhaps?

Well, that's pretty interesting.  What are your vacuuming arrangements
for this installation?  Could the drop in file size have coincided with
VACUUM operations?  Because the ultimate backstop against bloated stats
files is pgstat_vacuum_tabstat(), which is run by VACUUM and arranges to
clean out any entries that shouldn't be there anymore.

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.

			regards, tom lane