Ineffective autovacuum

[Royce Ausburn <royce.ml@xxxxxxxxxxx>]

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 <tgl@xxxxxxxxxxxxx>

Subject: Re: [GENERAL] Vacuum as "easily obtained" locks 

Date: 4 August 2011 1:52:02 AM AEST

To: Michael Graham <mgraham@xxxxxxxxx>

Cc: Pavan Deolasee <pavan.deolasee@xxxxxxxxx>, pgsql-general@xxxxxxxxxxxxxx

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 <mgraham@xxxxxxxxx> 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 (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general