Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 71EAA632369 for ; Fri, 16 Apr 2010 12:41:37 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 14008-05 for ; Fri, 16 Apr 2010 15:41:26 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by mail.postgresql.org (Postfix) with ESMTP id A7D656322B6 for ; Fri, 16 Apr 2010 12:41:26 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id o3GFfOxY010083; Fri, 16 Apr 2010 11:41:24 -0400 (EDT) To: Josh Kupershmidt cc: Chris , pgsql-performance@postgresql.org Subject: Re: stats collector suddenly causing lots of IO In-reply-to: References: <20100413170105.GA29571@chris-laptop.a2hosting.com> <25952.1271370711@sss.pgh.pa.us> <9738.1271431432@sss.pgh.pa.us> Comments: In-reply-to Josh Kupershmidt message dated "Fri, 16 Apr 2010 11:30:27 -0400" Date: Fri, 16 Apr 2010 11:41:24 -0400 Message-ID: <10082.1271432484@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.116 tagged_above=-10 required=5 tests=AWL=-0.206, BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201004/210 X-Sequence-Number: 38460 Josh Kupershmidt writes: > On Fri, Apr 16, 2010 at 11:23 AM, Tom Lane wrote: >> Hmm.  That makes me wonder if autovacuum is functioning properly at all. >> What does pg_stat_all_tables show for the last vacuum and analyze times >> of those tables?  Try something like >> >> select relname,n_live_tup,n_dead_tup,last_vacuum,last_autovacuum,last_analyze,last_autoanalyze from pg_stat_all_tables where schemaname = 'pg_catalog' order by 1; > Output attached. Note that I ran pg_stat_reset() a few days ago. Wow. Well, we have a smoking gun here: for some reason, autovacuum isn't running, or isn't doing its job if it is. If it's not running at all, that would explain failure to prune the stats collector's file too. Is there anything in the postmaster log that would suggest autovac difficulties? regards, tom lane