Skip site navigation (1) Skip section navigation (2)

Re: stats collector suddenly causing lots of IO

From: Josh Kupershmidt <schmiddy(at)gmail(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Chris <lists(at)deksai(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: stats collector suddenly causing lots of IO
Date: 2010-04-16 16:31:51
Message-ID: h2p4ec1cf761004160931r47cf4949o9a2fc14529c17ab1@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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.

> Is there anything in the postmaster log that would suggest autovac
> difficulties?

Yup, there are logs from April 1st which I just grepped through. I
attached the redacted output, and I see a few warnings about "[table]
contains more than "max_fsm_pages" pages with useful free space", as
well as "ERROR:  canceling autovacuum task".

Perhaps bumping up max_fsm_pages and making autovacuum settings more
aggressive will help me? I was also planning to run a CLUSTER of those
four bloated pg_catalog tables -- is this safe, particularly for
tables like pg_attrdef which rely on OIDs?

Josh

Attachment: pg_logs.txt
Description: text/plain (15.5 KB)
Attachment: pg_stat_activity.txt
Description: text/plain (2.1 KB)

In response to

Responses

pgsql-performance by date

Next:From: Josh BerkusDate: 2010-04-16 16:39:34
Subject: Re: Autovaccum with cost_delay does not complete on one solaris 5.10 machine
Previous:From: Tom LaneDate: 2010-04-16 16:25:46
Subject: Re: stats collector suddenly causing lots of IO

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group