Re: autovacuum: I need some explanation

Noah Freire escribió:

> 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).

Maybe you just need to decrease the vacuum_cost_delay, so that it goes a
bit faster.

Keep in mind that the number of dead tuples only decreases when vacuum
finishes, not while it is working.

> It would be better to run vacuum manually than using
> autovacuum in this case?

It would be exactly the same (unless it used different cost_limit/delay
settings, but then you can configure them via pg_autovacuum)

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.