From: Tom Lane <tgl@sss.pgh.pa.us>
To: Royce Ausburn <royce.ml@inomial.com>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: Ineffective autovacuum
Date: Tue, 27 Sep 2011 00:21:22 -0400
Message-ID: <14103.1317097282@sss.pgh.pa.us> (raw)
In-Reply-To: <059E65EF-8C66-4DA6-835C-936D6592C94B@inomial.com>
References: <059E65EF-8C66-4DA6-835C-936D6592C94B@inomial.com>
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.
> 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.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, royce.ml@inomial.com
Subject: Re: Ineffective autovacuum
In-Reply-To: <14103.1317097282@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox