Re: stats collector suddenly causing lots of IO

Josh Kupershmidt <schmiddy(at)gmail(dot)com> writes:
> On Fri, Apr 16, 2010 at 11:41 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> 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