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.ja...@gmail.com> wrote:
> On Fri, Aug 9, 2013 at 9:06 AM, Kevin Grittner <kgri...@ymail.com> wrote:
> > Vishalakshi Navaneethakrishnan <nvishalak...@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.
>
> 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
