On 27/09/2011, at 2:21 PM, Tom Lane wrote:
> Royce Ausburn <royce...@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 because they'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 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:
>
>> - 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 cancelled when 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'd over 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, 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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
