Re: How to avoid Force Autovacuum

[Vishalakshi Navaneethakrishnan <nvishalakshi@xxxxxxxxxx>]

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.janes@xxxxxxxxx> wrote:

On Fri, Aug 9, 2013 at 9:06 AM, Kevin Grittner <kgrittn@xxxxxxxxx> wrote:
> Vishalakshi Navaneethakrishnan <nvishalakshi@xxxxxxxxxx> 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