Hi Alvaro,
On Wed, Oct 29, 2008 at 3:46 PM, Alvaro Herrera
<alvherre@commandprompt.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