Re: How to avoid Force Autovacuum - Mailing list pgsql-general
| From | Vishalakshi Navaneethakrishnan |
|---|---|
| Subject | Re: How to avoid Force Autovacuum |
| Date | |
| Msg-id | CAP-PUP3sAkvUdAe3hhvFoxJwwyWxzipa9S=9DkCmLgPdHaeuGQ@mail.gmail.com Whole thread Raw |
| In response to | Re: How to avoid Force Autovacuum (Jeff Janes <jeff.janes@gmail.com>) |
| List | pgsql-general |
Hi Team,
> log_autovacuum_min_duration = 0
That is good for debugging. But what are you seeing in the log as the
result of this?
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@gmail.com> wrote:
Non-zero values are normal. There is no mechanism to preventOn Fri, Aug 9, 2013 at 9:06 AM, Kevin Grittner <kgrittn@ymail.com> wrote:
> Vishalakshi Navaneethakrishnan <nvishalakshi@sirahu.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.
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
pgsql-general by date: