Thread: Problem with autovacuum and pg_autovacuum

Problem with autovacuum and pg_autovacuum

From
Andreas 'ads' Scherbaum
Date:
Hello,

we got a small problem with auto_vacuum: since we have some big tables
which have heavy read/write access, we tried to exclude this tables
from autovacuum:

database1=# select vacrelid,enabled,(select relname from pg_class where oid=vacrelid) as relname from pg_autovacuum;
 vacrelid | enabled |       relname
----------+---------+----------------------
    42041 | f       | guestbook
    42344 | f       | forum_threads
    42406 | f       | forum_thread_entries
    41937 | f       | user_online
    42255 | f       | forum_fora
    41570 | f       | users
    41694 | f       | user_data
(7 rows)


Now it seems, that autovacuum is processing exactly this tables:

2007-07-04 22:37:05 CEST DEBUG: autovacuum: processing database "database1"
2007-07-04 22:37:06 CEST DEBUG: autovac: will VACUUM ANALYZE users
2007-07-04 22:37:06 CEST DEBUG: autovac: will VACUUM ANALYZE user_stats
2007-07-04 22:37:06 CEST DEBUG: autovac: will VACUUM ANALYZE guestbook
2007-07-04 22:37:06 CEST DEBUG: autovac: will VACUUM ANALYZE forum_fora
2007-07-04 22:37:06 CEST DEBUG: autovac: will VACUUM ANALYZE forum_threads
2007-07-04 22:37:06 CEST DEBUG: autovac: will VACUUM ANALYZE forum_thread_entries
2007-07-04 22:37:06 CEST DEBUG: autovac: will VACUUM ANALYZE user_data
2007-07-04 22:37:06 CEST DEBUG: autovac: will VACUUM ANALYZE user_online
2007-07-04 22:37:06 CEST DEBUG: vacuuming "schema1.users"
2007-07-04 22:38:39 CEST DEBUG: vacuuming "pg_toast.pg_toast_41570"
2007-07-04 22:38:47 CEST DEBUG: vacuuming "schema1.user_stats"
2007-07-04 22:49:06 CEST DEBUG: vacuuming "pg_toast.pg_toast_43602"
2007-07-04 22:51:51 CEST DEBUG: vacuuming "schema1.guestbook"
2007-07-04 23:00:38 CEST DEBUG: vacuuming "pg_toast.pg_toast_42041"
2007-07-04 23:00:43 CEST DEBUG: vacuuming "schema1.forum_fora"
2007-07-04 23:00:50 CEST DEBUG: vacuuming "pg_toast.pg_toast_42255"
2007-07-04 23:00:50 CEST DEBUG: vacuuming "schema1.forum_threads"
2007-07-04 23:01:06 CEST DEBUG: vacuuming "schema1.forum_thread_entries"
2007-07-04 23:01:50 CEST DEBUG: vacuuming "pg_toast.pg_toast_42406"
2007-07-04 23:01:54 CEST DEBUG: vacuuming "schema1.user_data"
2007-07-04 23:05:36 CEST DEBUG: vacuuming "pg_toast.pg_toast_41694"
2007-07-04 23:05:45 CEST DEBUG: vacuuming "schema1.user_online"

database1=# select version();
                                                     version
-----------------------------------------------------------------------------------------------------------------
 PostgreSQL 8.2.4 on x86_64-pc-linux-gnu, compiled by GCC cc (GCC) 4.1.2 20061115 (prerelease) (Debian 4.1.1-21)
(1 row)

Any idea, what's wrong here?


Kind regards

--
                Andreas 'ads' Scherbaum
Failure is not an option. It comes bundled with your Microsoft product.
 (Ferenc Mantfeld)

Re: Problem with autovacuum and pg_autovacuum

From
Alvaro Herrera
Date:
Andreas 'ads' Scherbaum wrote:
>
> Hello,
>
> we got a small problem with auto_vacuum: since we have some big tables
> which have heavy read/write access, we tried to exclude this tables
> from autovacuum:
>
> database1=# select vacrelid,enabled,(select relname from pg_class where oid=vacrelid) as relname from pg_autovacuum;
>  vacrelid | enabled |       relname
> ----------+---------+----------------------
>     42041 | f       | guestbook
>     42344 | f       | forum_threads
>     42406 | f       | forum_thread_entries
>     41937 | f       | user_online
>     42255 | f       | forum_fora
>     41570 | f       | users
>     41694 | f       | user_data
> (7 rows)

Most likely it is worried about XID wraparound, and those are precisely
the tables that need urgent vacuumed because they haven't been vacuumed
in a long time.

What do you do to keep them clear of dead tuples?

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Re: Problem with autovacuum and pg_autovacuum

From
Andreas 'ads' Scherbaum
Date:
Hello,

On Wed, 4 Jul 2007 18:04:35 -0400 Alvaro Herrera wrote:

> Most likely it is worried about XID wraparound, and those are precisely
> the tables that need urgent vacuumed because they haven't been vacuumed
> in a long time.

No, autovacuum is doing this with every run. Beside this, the database has
only some 10k changes per day. The wraparound was my first idea, but i
don't see a reason, why this should be happen with every autovacuum run.


> What do you do to keep them clear of dead tuples?

Most of this tables are just big (guestbook or forum entries as example).
But there will be no dead tuples, since the entries are inserted and never
changed. The main reason for putting this tables into the pg_autovacuum
table was to avoid the locks at all with normal autovacuum processing
and analyze the tables in a nightly maintenance window.


Kind regards

--
                Andreas 'ads' Scherbaum
Failure is not an option. It comes bundled with your Microsoft product.
 (Ferenc Mantfeld)

Re: Problem with autovacuum and pg_autovacuum

From
Alvaro Herrera
Date:
Andreas 'ads' Scherbaum wrote:
>
> Hello,
>
> On Wed, 4 Jul 2007 18:04:35 -0400 Alvaro Herrera wrote:
>
> > Most likely it is worried about XID wraparound, and those are precisely
> > the tables that need urgent vacuumed because they haven't been vacuumed
> > in a long time.
>
> No, autovacuum is doing this with every run. Beside this, the database has
> only some 10k changes per day. The wraparound was my first idea, but i
> don't see a reason, why this should be happen with every autovacuum run.

Ok a new weird scenario.  Could you please let us look at

select relname, relfrozenxid, age(relfrozenxid) from pg_class where
relkind in ('r', 't') order by 3 desc;

and

select datfrozenxid, age(datfrozenxid) from pg_database where
datname = 'your database';

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Re: Problem with autovacuum and pg_autovacuum

From
Andreas 'ads' Scherbaum
Date:
Hello,

On Wed, 4 Jul 2007 18:40:15 -0400 Alvaro Herrera wrote:

> Andreas 'ads' Scherbaum wrote:
> >
> > On Wed, 4 Jul 2007 18:04:35 -0400 Alvaro Herrera wrote:
> >
> > > Most likely it is worried about XID wraparound, and those are precisely
> > > the tables that need urgent vacuumed because they haven't been vacuumed
> > > in a long time.
> >
> > No, autovacuum is doing this with every run. Beside this, the database has
> > only some 10k changes per day. The wraparound was my first idea, but i
> > don't see a reason, why this should be happen with every autovacuum run.
>
> Ok a new weird scenario.  Could you please let us look at
>
> select relname, relfrozenxid, age(relfrozenxid) from pg_class where
> relkind in ('r', 't') order by 3 desc;

Thats a bit more information ...

http://rafb.net/p/xJ4W6W43.html


> select datfrozenxid, age(datfrozenxid) from pg_database where
> datname = 'your database';

database1=# select datfrozenxid, age(datfrozenxid) from pg_database where datname = 'database1';
 datfrozenxid |   age
--------------+----------
          524 | 35952722
(1 row)


Kind regards

--
                Andreas 'ads' Scherbaum
Failure is not an option. It comes bundled with your Microsoft product.
 (Ferenc Mantfeld)

Re: Problem with autovacuum and pg_autovacuum

From
Alvaro Herrera
Date:
Andreas 'ads' Scherbaum wrote:
>
> Hello,
>
> On Wed, 4 Jul 2007 18:40:15 -0400 Alvaro Herrera wrote:
>
> > Andreas 'ads' Scherbaum wrote:
> > >
> > > On Wed, 4 Jul 2007 18:04:35 -0400 Alvaro Herrera wrote:
> > >
> > > > Most likely it is worried about XID wraparound, and those are precisely
> > > > the tables that need urgent vacuumed because they haven't been vacuumed
> > > > in a long time.
> > >
> > > No, autovacuum is doing this with every run. Beside this, the database has
> > > only some 10k changes per day. The wraparound was my first idea, but i
> > > don't see a reason, why this should be happen with every autovacuum run.
> >
> > Ok a new weird scenario.  Could you please let us look at
> >
> > select relname, relfrozenxid, age(relfrozenxid) from pg_class where
> > relkind in ('r', 't') order by 3 desc;
>
> Thats a bit more information ...
>
> http://rafb.net/p/xJ4W6W43.html

Oh.  It's not the age.  Please let us look at the pg_stat_user_tables
entries for the involved tables?  If it's picking the same tables maybe
pgstats has stale info, but why is it not updating it?

--
Alvaro Herrera                  http://www.amazon.com/gp/registry/5ZYLFMCVHXC
"The problem with the facetime model is not just that it's demoralizing, but
that the people pretending to work interrupt the ones actually working."
                                                           (Paul Graham)

Re: Problem with autovacuum and pg_autovacuum

From
"Pavan Deolasee"
Date:


On 7/5/07, Andreas 'ads' Scherbaum <adsmail@wars-nicht.de> wrote:

Hello,

On Wed, 4 Jul 2007 18:04:35 -0400 Alvaro Herrera wrote:

> Most likely it is worried about XID wraparound, and those are precisely
> the tables that need urgent vacuumed because they haven't been vacuumed
> in a long time.

No, autovacuum is doing this with every run. Beside this, the database has
only some 10k changes per day. The wraparound was my first idea, but i
don't see a reason, why this should be happen with every autovacuum run.


Did you check freeze_max_age values in the pg_autovacuum table ? A very
small value can trigger XID wraparound related VACUUMs in every run.

Thanks,
Pavan

--
Pavan Deolasee
EnterpriseDB     http://www.enterprisedb.com

Re: Problem with autovacuum and pg_autovacuum

From
Andreas 'ads' Scherbaum
Date:
On Wed, 4 Jul 2007 19:47:12 -0400 Alvaro Herrera wrote:

> Andreas 'ads' Scherbaum wrote:
>
> Oh.  It's not the age.  Please let us look at the pg_stat_user_tables
> entries for the involved tables?  If it's picking the same tables maybe
> pgstats has stale info, but why is it not updating it?

Hmm, maybe because all the columns have '0' values ...

stats_row_level is on and stats_start_collector is on default (on).


Greetings from italy

--
                Andreas 'ads' Scherbaum
Failure is not an option. It comes bundled with your Microsoft product.
 (Ferenc Mantfeld)

Re: Problem with autovacuum and pg_autovacuum

From
Andreas 'ads' Scherbaum
Date:
On Fri, 6 Jul 2007 11:30:19 +0530 Pavan Deolasee wrote:

Hello,

> On 7/5/07, Andreas 'ads' Scherbaum <adsmail@wars-nicht.de> wrote:
> >
> > On Wed, 4 Jul 2007 18:04:35 -0400 Alvaro Herrera wrote:
> >
> > > Most likely it is worried about XID wraparound, and those are precisely
> > > the tables that need urgent vacuumed because they haven't been vacuumed
> > > in a long time.
> >
> > No, autovacuum is doing this with every run. Beside this, the database has
> > only some 10k changes per day. The wraparound was my first idea, but i
> > don't see a reason, why this should be happen with every autovacuum run.
> >
> Did you check freeze_max_age values in the pg_autovacuum table ? A very
> small value can trigger XID wraparound related VACUUMs in every run.

The value is '0' for all columns in all entries, except 'vacrelid' and 'enabled'.
Can a VACUUM run happen, even if enabled is set to false?


Greetings from Italy

--
                Andreas 'ads' Scherbaum
Failure is not an option. It comes bundled with your Microsoft product.
 (Ferenc Mantfeld)

Re: Problem with autovacuum and pg_autovacuum

From
Alvaro Herrera
Date:
Andreas 'ads' Scherbaum wrote:
> On Fri, 6 Jul 2007 11:30:19 +0530 Pavan Deolasee wrote:
>
> Hello,
>
> > On 7/5/07, Andreas 'ads' Scherbaum <adsmail@wars-nicht.de> wrote:
> > >
> > > On Wed, 4 Jul 2007 18:04:35 -0400 Alvaro Herrera wrote:
> > >
> > > > Most likely it is worried about XID wraparound, and those are precisely
> > > > the tables that need urgent vacuumed because they haven't been vacuumed
> > > > in a long time.
> > >
> > > No, autovacuum is doing this with every run. Beside this, the database has
> > > only some 10k changes per day. The wraparound was my first idea, but i
> > > don't see a reason, why this should be happen with every autovacuum run.
> > >
> > Did you check freeze_max_age values in the pg_autovacuum table ? A very
> > small value can trigger XID wraparound related VACUUMs in every run.
>
> The value is '0' for all columns in all entries, except 'vacrelid' and 'enabled'.
> Can a VACUUM run happen, even if enabled is set to false?

Huh, try putting -1 in all columns instead.  0 is a nasty value to have
in there.  I haven't tested the effects but if freeze_max_age is 0 it
may be doing what Pavan says.

A fix is pending for out-of-range values in pg_autovacuum, per Galy Lee.
I should have worked harder at moving this stuff into
pg_class.reloptions, which would have meant less problems since there
would be no need to put values to those variables in the first place :-(
Sorry.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Re: Problem with autovacuum and pg_autovacuum

From
Andreas 'ads' Scherbaum
Date:
On Fri, 6 Jul 2007 12:06:41 -0400 Alvaro Herrera wrote:

> Andreas 'ads' Scherbaum wrote:
> > On Fri, 6 Jul 2007 11:30:19 +0530 Pavan Deolasee wrote:
> >
> > > On 7/5/07, Andreas 'ads' Scherbaum <adsmail@wars-nicht.de> wrote:
> > > >
> > > > On Wed, 4 Jul 2007 18:04:35 -0400 Alvaro Herrera wrote:
> > > >
> > > > > Most likely it is worried about XID wraparound, and those are precisely
> > > > > the tables that need urgent vacuumed because they haven't been vacuumed
> > > > > in a long time.
> > > >
> > > > No, autovacuum is doing this with every run. Beside this, the database has
> > > > only some 10k changes per day. The wraparound was my first idea, but i
> > > > don't see a reason, why this should be happen with every autovacuum run.
> > > >
> > > Did you check freeze_max_age values in the pg_autovacuum table ? A very
> > > small value can trigger XID wraparound related VACUUMs in every run.
> >
> > The value is '0' for all columns in all entries, except 'vacrelid' and 'enabled'.
> > Can a VACUUM run happen, even if enabled is set to false?
>
> Huh, try putting -1 in all columns instead.  0 is a nasty value to have
> in there.  I haven't tested the effects but if freeze_max_age is 0 it
> may be doing what Pavan says.

Ok, did this. Will take a look, what autovacuum is doing now.


Thanks for the help

--
                Andreas 'ads' Scherbaum
Failure is not an option. It comes bundled with your Microsoft product.
 (Ferenc Mantfeld)

Re: Problem with autovacuum and pg_autovacuum

From
Andreas 'ads' Scherbaum
Date:
On Sat, 7 Jul 2007 00:12:32 +0200 Andreas 'ads' Scherbaum wrote:

> On Fri, 6 Jul 2007 12:06:41 -0400 Alvaro Herrera wrote:
>
> > Andreas 'ads' Scherbaum wrote:
> > > The value is '0' for all columns in all entries, except 'vacrelid' and 'enabled'.
> > > Can a VACUUM run happen, even if enabled is set to false?
> >
> > Huh, try putting -1 in all columns instead.  0 is a nasty value to have
> > in there.  I haven't tested the effects but if freeze_max_age is 0 it
> > may be doing what Pavan says.
>
> Ok, did this. Will take a look, what autovacuum is doing now.

Seems like that was the problem. I changed all '0' to '-1' and VACUUM is
skipping this tables now.


Thanks for finding this out

--
                Andreas 'ads' Scherbaum
Failure is not an option. It comes bundled with your Microsoft product.
 (Ferenc Mantfeld)

Re: Problem with autovacuum and pg_autovacuum

From
Alvaro Herrera
Date:
Andreas 'ads' Scherbaum wrote:
> On Sat, 7 Jul 2007 00:12:32 +0200 Andreas 'ads' Scherbaum wrote:
>
> > On Fri, 6 Jul 2007 12:06:41 -0400 Alvaro Herrera wrote:
> >
> > > Andreas 'ads' Scherbaum wrote:
> > > > The value is '0' for all columns in all entries, except 'vacrelid' and 'enabled'.
> > > > Can a VACUUM run happen, even if enabled is set to false?
> > >
> > > Huh, try putting -1 in all columns instead.  0 is a nasty value to have
> > > in there.  I haven't tested the effects but if freeze_max_age is 0 it
> > > may be doing what Pavan says.
> >
> > Ok, did this. Will take a look, what autovacuum is doing now.
>
> Seems like that was the problem. I changed all '0' to '-1' and VACUUM is
> skipping this tables now.
>
>
> Thanks for finding this out

Oops :-(  We should certainly make an effort to check the validity of
the values in pg_autovacuum, but 0 is a perfectly valid value, so the
check would not help you any in this case :-(

--
Alvaro Herrera                               http://www.PlanetPostgreSQL.org/
"Right now the sectors on the hard disk run clockwise, but I heard a rumor that
you can squeeze 0.2% more throughput by running them counterclockwise.
It's worth the effort. Recommended."  (Gerry Pourwelle)

Re: Problem with autovacuum and pg_autovacuum

From
Andreas 'ads' Scherbaum
Date:
On Sun, 8 Jul 2007 19:12:46 -0400 Alvaro Herrera wrote:

> Oops :-(  We should certainly make an effort to check the validity of
> the values in pg_autovacuum, but 0 is a perfectly valid value, so the
> check would not help you any in this case :-(

Apparently not, taken into account, that the 'enabled' column was set
to false. So the question remains, why autovacuum is not checking
this column first and then skip other calculations in the case, the table
is not activated at all.

Thinking more forward: would it make sense to have some kind of helper
in the database which gives (if enabled) notices back, if you are doing
something crazy? As example insert a value into a serial column (would
not be wrong and can be perfectly valid but for a beginner this could just
be a problem), maybe some helps from the planner about index usage or
missing index or in case of pg_autovacuum give some hints about
values which seem to create problems.


Kind regards

--
                Andreas 'ads' Scherbaum
Failure is not an option. It comes bundled with your Microsoft product.
 (Ferenc Mantfeld)