Re: How to avoid Force Autovacuum

On Wed, Aug 7, 2013 at 2:46 AM, Vishalakshi Navaneethakrishnan
<nvishalakshi(at)sirahu(dot)com> wrote:
> Hi All,
>
> We have one production database server , having 6 DBs, Postgres 9.2.1
> version.
>

You should probably upgrade to 9.2.4.

...

> log_autovacuum_min_duration = 0

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

>
> Even sometimes autovacuum running on the databases and increase the load
> (Above 200) very much and the server was unresponsive
>
> I have seen the autovacum worker process in top command,

How certain are you that the autovacuum is causing the high load? The
simple fact that you see it show up in top is not very compelling
evidence. It seems at least as likely to me that autovacuum is just
another victim, and not the cause

> While i executing pg_stat_activity as postgres user, i have seen the pid of
> autovacuum process in the result but the query filed is "Empty"

I've never seen that. I always see a query of something like
"autovacuum: VACUUM public.pgbench_accounts (to prevent wraparound)".
I don't know what part of the vacuuming process you might be stuck at
which would not have such a query--I would think it would have to be
either during the start-up phase or the shut-down phase. Could you
attach to the worker with gdb when this is going on, and get a
backtrace?

>
> since template0 has no activities why the age(datfrozenxid) increasing
> heavily and reach the thresold value?

Time marches on. If datfrozenxid is not changed, but the current xid
advances, then the age of the datfrozenzid must increase.

Cheers,

Jeff