Hi all,
I have a problem with autovacuum apparently not doing the job I need it to do.
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
In a few instances of our application we're seeing this table grow obscenely to
the point where our monitoring servers get us out of bed to manually vacuum. I
like sleep, so I want to fix this =D
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?
2) Are there better solutions to this problem than a periodic task that
vacuums/truncates-and-rebuilds the table?
Perhaps relevant info:
# select version();
version
--------------------------------------------------------------------------------------------------
PostgreSQL 8.3.12 on x86_64-pc-linux-gnu, compiled by GCC cc (GCC) 4.2.4
(Ubuntu 4.2.4-1ubuntu3)
(1 row)
Auto vacuum and vacuum parameters are set to the factory defaults.
Cheers,
--Royce
> From: Tom Lane <[email protected]>
> Subject: Re: [GENERAL] Vacuum as "easily obtained" locks
> Date: 4 August 2011 1:52:02 AM AEST
> To: Michael Graham <[email protected]>
> Cc: Pavan Deolasee <[email protected]>, [email protected]
>
>>> On Wed, 2011-08-03 at 11:40 -0400, Tom Lane wrote:
>>> The other problem is that once autovacuum has gotten the lock, it has
>>> to keep it for long enough to re-scan the truncatable pages (to make
>>> sure they're still empty). And it is set up so that any access to the
>>> table will kick autovacuum off the lock. An access pattern like that
>>> would very likely prevent it from ever truncating, if there are a lot
>>> of pages that need to be truncated. (There's been some discussion of
>>> modifying this behavior, but nothing's been done about it yet.)
> Michael Graham <[email protected]> writes:
>> Ah! This looks like it is very much the issue. Since I've got around
>> 150GB of data that should be truncatable and a select every ~2s.
>
>> Just to confirm would postgres write:
>
>> 2011-08-03 16:09:55 BST ERROR: canceling autovacuum task
>> 2011-08-03 16:09:55 BST CONTEXT: automatic vacuum of table
>> "traffic.public.logdata5queue"
>
>> Under those circumstances?
>
> Yup ...
>
> If you do a manual VACUUM, it won't allow itself to get kicked off the
> lock ... but as noted upthread, that will mean your other queries get
> blocked till it's done. Not sure there's any simple fix for this that
> doesn't involve some downtime.
>
> regards, tom lane
>
> --
> Sent via pgsql-general mailing list ([email protected])
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
