Skip site navigation (1) Skip section navigation (2)

Re: autovacuum: I need some explanation

From: "Noah Freire" <noah(dot)freire(at)gmail(dot)com>
To: "Alvaro Herrera" <alvherre(at)commandprompt(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: autovacuum: I need some explanation
Date: 2008-10-29 19:26:18
Message-ID: d8dd025a0810291226v36babdccv79a3bbb4b5dd5d5c@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
Hi Alvaro,
On Wed, Oct 29, 2008 at 3:46 PM, Alvaro Herrera
<alvherre(at)commandprompt(dot)com>wrote:

> Noah Freire escribió:
>
> > 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?
>
> That there's no other process doing the same.  Did you check
> pg_stat_activity to ensure that there's really no autovacuum worker
> processing this table?


 datid | datname | procpid | usesysid | usename | current_query | waiting |
xact_start | query_start | backend_start | client_addr | client_port
-------+---------+---------+----------+----------+-------------------------------------------------------------------------+---------+-------------------------------+-------------------------------+-------------------------------+-------------+-------------
 45974 | pgbench | 14660 | 10 | postgres | autovacuum: VACUUM
public.accounts | f | 2008-10-29 11:09:05.610857-07 | 2008-10-29
11:09:05.610857-07 | 2008-10-29 11:09:03.45083-07 | |

indeed; autovacuum is currently running for accounts. It is running for 1
hour but the number of dead rows keeps increasing. Apparently autovacuum
cannot pace the rate of updates on this table (please check the two
snapshots of pg_stat_user_tables below taken with a 2 seconds interval
between them). It would be better to run vacuum manually than using
autovacuum in this case?
pgbench=# select relname, n_dead_tup from pg_stat_user_tables where
relname='accounts';
 relname | n_dead_tup
----------+------------
 accounts | 19917490
(1 row)

pgbench=# select relname, n_dead_tup from pg_stat_user_tables where
relname='accounts';
 relname | n_dead_tup
----------+------------
 accounts | 19923767
(1 row)
Thanks,
-Noah

In response to

Responses

pgsql-hackers by date

Next:From: Andrew DunstanDate: 2008-10-29 19:31:09
Subject: Re: minimal update
Previous:From: Alvaro HerreraDate: 2008-10-29 18:53:04
Subject: Re: Block-level CRC checks

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group