Thread: Problem with autovacuum and pg_autovacuum
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)
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
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)
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.
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)
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)
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
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)
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)
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.
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)
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)
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)
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)