To: Josh Berkus <josh@agliodbs.com>
cc: PostgreSQL-development <pgsql-hackers@postgresql.org>
Subject: Re: Return of the Solaris vacuum polling problem -- anyone remember
	this?
In-reply-to: <4C69827A.409@agliodbs.com> 
References: <4C69827A.409@agliodbs.com>
Comments: In-reply-to Josh Berkus <josh@agliodbs.com>
	message dated "Mon, 16 Aug 2010 11:24:58 -0700"
Date: Mon, 16 Aug 2010 15:22:17 -0400
Message-ID: <19969.1281986537@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Josh Berkus <josh@agliodbs.com> writes:
> This is something I'd swear we fixed around 8.3.2. However, I'm seeing
> it again in production, and was wondering if anyone could remember what
> the root cause was and how we fixed it.

Hmm, I can't find anything in the 8.3-series CVS logs suggesting that
there was a post-8.3.0 fix related to vacuum delays.

> The problem is that sometimes (but not the majority of times) autovaccum
> with cost_delay is going into a pathological cycle where it polls the
> system clock after reading every single disk page of a table.

What I find interesting about that trace is the large proportion of
writes.  That appears to me to indicate that it's *not* a matter of
vacuum delays, or at least not just a matter of that.  The process seems
to be getting involved in having to dump dirty buffers to disk.  Perhaps
the background writer is malfunctioning?

Another idea that comes to mind is that you have vacuum_cost_page_dirty
set to an unreasonably large value, so that autovac is blocking whenever
it has to write even one page.

			regards, tom lane