Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id EAF5363358A for ; Fri, 16 Apr 2010 13:49:09 -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 25459-07 for ; Fri, 16 Apr 2010 16:48:57 +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 3A3CB633256 for ; Fri, 16 Apr 2010 13:48:59 -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 o3GGmmjX011153; Fri, 16 Apr 2010 12:48:48 -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> <10082.1271432484@sss.pgh.pa.us> Comments: In-reply-to Josh Kupershmidt message dated "Fri, 16 Apr 2010 12:31:51 -0400" Date: Fri, 16 Apr 2010 12:48:48 -0400 Message-ID: <11152.1271436528@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/216 X-Sequence-Number: 38466 Josh Kupershmidt writes: > On Fri, Apr 16, 2010 at 11:41 AM, Tom Lane wrote: >> 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. > Hrm, well autovacuum is at least trying to do work: it's currently > stuck on those bloated pg_catalog tables, of course. Another developer > killed an autovacuum of pg_attribute (or maybe it was pg_attrdef) > after it had been running for two weeks. See current pg_stat_activity > output attached, which shows the three autovacuum workers running plus > two manual VACUUM ANALYZEs I started yesterday. Two weeks? What have you got the autovacuum cost delays set to? Once you're up to three AV workers, no new ones can get launched until one of those finishes or is killed. So that would explain failure to prune the stats collector's tables (the tabpurge code is only run during AV worker launch). So what we need to figure out is why it's taking so obscenely long to vacuum these tables ... regards, tom lane