autovacuum: I need some explanation

From: "Noah Freire" <noah(dot)freire(at)gmail(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: autovacuum: I need some explanation
Date: 2008-10-29 18:26:15
Message-ID: d8dd025a0810291126j430b4cbbl940bcb6053964809@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hello,

I have a table (accounts) with 600,000,000 rows. A heavy high-concurrent
workload that makes mostly updates on this table generates a lot of dead
tuples in its run, which is expected due to MVCC.
The problem is that even though autovacuum is enabled, the autovacuum worker
does not vacuum this table (I entered custom autovacuum settings for this
table in pg_autovacuum to try to force a situation). Autovacuum is working
for other smaller tables but not for accounts.

<2008-10-29 11:09:03.453 PDT>DEBUG: 00000: accounts: vac: 16697969
(threshold 6000050), anl: 16697969 (threshold 120000048)
<2008-10-29 11:09:05.610 PDT>DEBUG: 00000: accounts: vac: 16699578
(threshold 6000050), anl: 16699578 (threshold 120000048)
<2008-10-29 11:10:03.563 PDT>DEBUG: 00000: accounts: vac: 16735906
(threshold 6000050), anl: 16735906 (threshold 120000048)

please check the first log message: the vacuum threshold is 6,000,050 rows
and the number of dead tuples is 16,697,969. Even though the number of
dead_tuples is greater than the threshold the autovacuum is not being
triggered for this table. So, besides this condition (dead_tuples >
threshold) what else is taken into account by autovacuum?

Thank you,

-Noah

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Dann Corbit 2008-10-29 18:32:42 Re: Multi CPU Queries - Feedback and/or suggestions wanted!
Previous Message David Blewett 2008-10-29 18:17:44 Re: pre-MED