Subject: Re: Ineffective autovacuum 
Mime-Version: 1.0 (Apple Message framework v1244.3)
Content-Type: text/plain; charset=utf-8
From: Royce Ausburn <royce.ml@inomial.com>
In-Reply-To: <14103.1317097282@sss.pgh.pa.us>
Date: Tue, 27 Sep 2011 15:08:26 +1000
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <2499AF8B-FB90-40F9-9CF7-5BDC0F33BE6E@inomial.com>
References: <059E65EF-8C66-4DA6-835C-936D6592C94B@inomial.com>
	<14103.1317097282@sss.pgh.pa.us>
To: Tom Lane <tgl@sss.pgh.pa.us>


On 27/09/2011, at 2:21 PM, Tom Lane wrote:

> Royce Ausburn <royce.ml@inomial.com> writes:
>> I have a problem with autovacuum apparently not doing the job I need =
it to do.
>=20
> Hm, I wonder whether you're getting bit by bug #5759, which was fixed
> after 8.3.12.

If this were the case, would I see lots of auto vacuum worker processes =
in ps that are essentially doing nothing because they're sleeping all =
the time?  If so, then I think perhaps not.

>=20
>> I have a table named datasession that is frequently inserted, updated =
and deleted from.  Typically the table will have a few thousand rows in =
it.  Each row typically survives a few days and is updated every 5 - 10 =
mins.  The application receives unreliable, potentially duplicate data =
from its source, so this table is heavily used for synchronising =
application threads as well.  A typical access pattern is:
>=20
>> - tx begin
>> - SELECT FOR UPDATE on a single row
>> - Do some application processing (1 - 100 ms)
>> - Possibly UPDATE the row
>> - tx commit
>=20
> Transactions of that form would not interfere with autovacuum.  You'd
> need something that wants exclusive lock, like a schema change.
>=20
>> I've read some recent threads and found a discussion (below) on auto =
vacuum that mentions auto vacuum will be cancelled when a client =
requests a lock that auto vacuum is using=E2=88=91 My questions:
>> 1) Does it look like I'm affected by the same problem as in the below =
discussion?
>=20
> Not unless you're seeing a lot of "canceling autovacuum task" messages
> in the postmaster log.

Okay - This is not the case.

Since sending this first email I've up'd the autovacuum log level and =
I've noticed that the same tables seem to be auto vacuum'd over and over =
again=E2=80=A6 Some of the tables are a bit surprising in that they're =
updated semi-regularly, but not enough (I'd think) to warrant an =
autovacuum every few minutes=E2=80=A6 Is this unusual?


Perhaps unrelated: I've done some digging around and happened across a =
nightly task doing:

select pg_stat_reset()

on each of the databases in the cluster=E2=80=A6. I've no idea why we're =
doing that (and our usual sysadmin / DBA has resigned, so I doubt I'll =
ever know).  There must have been a reason at the time, but I wonder if =
this might be interfering with things?

At any rate, I think the logs might glean some more interesting =
information, I'll let it alone for a few hours and hopefully I'll have =
some more useful information.

--Royce