Ineffective autovacuum
От | Royce Ausburn |
---|---|
Тема | Ineffective autovacuum |
Дата | |
Msg-id | 059E65EF-8C66-4DA6-835C-936D6592C94B@inomial.com обсуждение исходный текст |
Ответы |
Re: Ineffective autovacuum
|
Список | pgsql-performance |
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" locksDate: 4 August 2011 1:52:02 AM AESTTo: Michael Graham <mgraham@bloxx.com>Cc: Pavan Deolasee <pavan.deolasee@gmail.com>, pgsql-general@postgresql.org
On Wed, 2011-08-03 at 11:40 -0400, Tom Lane wrote:The other problem is that once autovacuum has gotten the lock, it hasto keep it for long enough to re-scan the truncatable pages (to makesure they're still empty). And it is set up so that any access to thetable will kick autovacuum off the lock. An access pattern like thatwould very likely prevent it from ever truncating, if there are a lotof pages that need to be truncated. (There's been some discussion ofmodifying 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 around150GB 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 task2011-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 по дате отправления:
Предыдущее
От: Timothy GarnettДата:
Сообщение: Re: Performance Anomaly with "col in (A,B)" vs. "col = A OR col = B" ver. 9.0.3