Postgres Pro
Downloads
Home   >   mailing lists

Ineffective autovacuum - Mailing list pgsql-performance

From Royce Ausburn
Subject Ineffective autovacuum
Date
Msg-id 059E65EF-8C66-4DA6-835C-936D6592C94B@inomial.com
Whole thread Raw
Responses Re: Ineffective autovacuum
List pgsql-performance
Tree view
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@sss.pgh.pa.us>
Subject: Re: [GENERAL] Vacuum as "easily obtained" locks 
Date: 4 August 2011 1:52:02 AM AEST
To: Michael Graham <mgraham@bloxx.com>

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


pgsql-performance by date:

Previous
From: Timothy Garnett
Date:
Subject: Re: Performance Anomaly with "col in (A,B)" vs. "col = A OR col = B" ver. 9.0.3
Next
From: Tom Lane
Date:
Subject: Re: Ineffective autovacuum