Re: Ineffective autovacuum

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.
>
> 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
becausethey're sleeping all the time?  If so, then I think perhaps not. 

>
>> I have a table named datasession that is frequently inserted, updated and deleted from.  Typically the table will
havea few thousand rows in it.  Each row typically survives a few days and is updated every 5 - 10 mins.  The
applicationreceives unreliable, potentially duplicate data from its source, so this table is heavily used for
synchronisingapplication threads as well.  A typical access pattern is: 
>
>> - tx begin
>> - SELECT FOR UPDATE on a single row
>> - Do some application processing (1 - 100 ms)
>> - Possibly UPDATE the row
>> - tx commit
>
> Transactions of that form would not interfere with autovacuum.  You'd
> need something that wants exclusive lock, like a schema change.
>
>> I've read some recent threads and found a discussion (below) on auto vacuum that mentions auto vacuum will be
cancelledwhen a client requests a lock that auto vacuum is using∑ My questions: 
>> 1) Does it look like I'm affected by the same problem as in the below discussion?
>
> 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'dover and over again… 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… 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…. I've no idea why we're doing that (and our usual sysadmin / DBA has resigned,
soI 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
hopefullyI'll have some more useful information. 

--Royce