Re: How to avoid Force Autovacuum

Hi Team,

Today also we faced issue in autovacuum.. Is there any workaround for this
instead of upgrading,, If yes means can you please give me tuning
parameters..

> log_autovacuum_min_duration = 0

That is good for debugging. But what are you seeing in the log as the
result of this?

There is nothing logged during autovacuum

This is the Pid in Pg_stat_activity

postgres=# select * from pg_stat_activity where pid=25769;
datid | datname | pid | usesysid | usename | application_name |
client_addr | client_hostname | client_port | backend_start
| xact_start | query_start | state_change | waiting |
state | query
-------+---------+-------+----------+----------+------------------+-------------+-----------------+-------------+-------------------------------+-------------------------------+-------------+--------------+---------+-------+-------
16408 | db1 | 25769 | 10 | postgres | |
| | | 2013-08-13 04:00:14.767093-07 |
2013-08-13 04:00:14.765484-07 | | | f |
|
(1 row)

This is the top command:

postgres 25769 30705 93 03:54 ? 00:01:45 postgres: autovacuum worker
process db1
postgres 24680 30705 84 03:55 ? 00:00:33 postgres: autovacuum worker
process db2
postgres 24692 30705 79 03:55 ? 00:00:26 postgres: autovacuum worker
process db3

On Sat, Aug 10, 2013 at 12:23 AM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:

> On Fri, Aug 9, 2013 at 9:06 AM, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:
> > Vishalakshi Navaneethakrishnan <nvishalakshi(at)sirahu(dot)com> wrote:
> >
> >> select * from pg_database where datname = 'template0';
> >> -[ RECORD 1 ]-+------------------------------------
> >> datname | template0
> >> datdba | 10
> >> encoding | 6
> >> datcollate | en_US.UTF-8
> >> datctype | en_US.UTF-8
> >> datistemplate | t
> >> datallowconn | f
> >> datconnlimit | -1
> >> datlastsysoid | 12865
> >> datfrozenxid | 2025732249
> >> dattablespace | 1663
> >> datacl | {=c/postgres,postgres=CTc/postgres}
> >>
> >>
> >> select * from pg_stat_database where datname = 'template0';
> >> -[ RECORD 1 ]--+------------------------------
> >> datid | 12865
> >> datname | template0
> >> numbackends | 0
> >> xact_commit | 320390
> >> xact_rollback | 7
> >> blks_read | 3797
> >> blks_hit | 9458783
> >> tup_returned | 105872028
> >> tup_fetched | 1771782
> >> tup_inserted | 10
> >> tup_updated | 457
> >> tup_deleted | 10
> >> conflicts | 0
> >> temp_files | 0
> >> temp_bytes | 0
> >> deadlocks | 0
> >> blk_read_time | 0
> >> blk_write_time | 0
> >> stats_reset | 2013-04-19 19:22:39.013056-07
> >
> > Well, that's why template0 is getting vacuumed. At some point
> > someone must have set it to allow connections; otherwise you would
> > have zero for commits, rollbacks, and all those block and tuple
> > counts.
>
> Non-zero values are normal. There is no mechanism to prevent
> template0 from getting vacuumed. template0 will get vacuumed once
> every autovacuum_freeze_max_age even if no one has ever connected to
> it, and that vacuum will cause block reads and writes to happen. (But
> I'm not sure why it would contribute xact_rollback or tup_updated, and
> the tup_returned seems awfully high to be due to only anti-wrap-around
> vacs.)
>
> Cheers,
>
> Jeff
>

--
Best Regards,
Vishalakshi.N