Thread: autovacuum

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

Noah Freire wrote:
> <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?

What version of PostgreSQL?  Is the table being excluded? (see the
pg_autovacuum system table settings)  Are you sure that it's not getting
processed? Perhaps one worker is / has been churning on this table for a
  *LONG* time (that is a fairly big table).  What does it say for the
most recent autovacuum?

SELECT relid, schemaname, relname, last_vacuum, last_autovacuum  from
pg_stat_all_tables;

Noah Freire wrote:
> On Wed, Oct 29, 2008 at 4:46 PM, Matthew T. O'Connor <matthew@zeut.net
> <mailto:matthew@zeut.net>> wrote:
>
>
>     Is the table being excluded? (see the pg_autovacuum system table
>     settings)
>
>
> there's an entry for this table on pg_autovacuum, and it's enabled.
>
>
>       Are you sure that it's not getting processed? Perhaps one worker
>     is / has been churning on this table for a  *LONG* time (that is a
>     fairly big table).
>
>
> Right. I was wrong :-) the table is being processed by autovacuum (I
> checked via pg_stat_activity). However, as you pinpointed, it's
> already running for hours (the test workload ended hours ago,
> basically it is just this autovacuum worker running on the system).
>
> Is there a way to make a more aggressive autovacuum setting for this
> table? it does not matter if it will affect performance, my concern is
> that it finishes as soon as possible. I wonder if a manual vacuum
> wouldn't be faster.


Yes, in the pg_autovacuum table, you can set per-relation vacuum cost
delay settings etc...