Thread: autovacuum not honoring pg_autovacuum in 8.3.5?

autovacuum not honoring pg_autovacuum in 8.3.5?

From
"Joshua D. Drake"
Date:
Hello,

I found this today. Note, auto vacuum has been disabled for this
relation for a very, very long time. At the very end you will see that
this relation has been autovacuumed previously as well. We have a manual
cron to vacuum this table every hour so I am unsure why autovacuum is
doing what it is doing.

app=# select * from pg_autovacuum where vacrelid = '21474846';
-[ RECORD 1 ]----+---------
vacrelid         | 21474846
enabled          | f
vac_base_thresh  | 0
vac_scale_factor | 0
anl_base_thresh  | 0
anl_scale_factor | 0
vac_cost_delay   | 0
vac_cost_limit   | 0
freeze_min_age   | 0
freeze_max_age   | 0


-[ RECORD 1 ]-+--------------------------------------------
datid         | 41824
datname       | bar
procpid       | 23019
usesysid      | 10
usename       | postgres
current_query | autovacuum: VACUUM ANALYZE public.foo
waiting       | t
xact_start    | 2009-02-13 15:02:04.938608-05
query_start   | 2009-02-13 15:02:04.938608-05
backend_start | 2009-02-13 15:01:29.001314-05
client_addr   | 
client_port   | 

-[ RECORD
1 ]--+---------------------------------------------------------------------
relname        | foo
relnamespace   | 2200
reltype        | 21474848
relowner       | 16388
relam          | 0
relfilenode    | 83717977
reltablespace  | 53723308
relpages       | 200675
reltuples      | 1.47796e+06
reltoastrelid  | 83717991
reltoastidxid  | 0
relhasindex    | t
relisshared    | f
relkind        | r
relnatts       | 13
relchecks      | 3
reltriggers    | 7
relukeys       | 0
relfkeys       | 0
relrefs        | 0
relhasoids     | f
relhaspkey     | t
relhasrules    | f
relhassubclass | t
relfrozenxid   | 1921217148
relacl         |
{kangaroo=arwdxt/kangaroo,postgres=arwdxt/kangaroo,gecko=r/kangaroo}
reloptions     | 


app=# select * from pg_stat_user_tables where relname = 'freq_mesg';
-[ RECORD 1 ]----+------------------------------
relid            | 21474846
schemaname       | public
relname          | foo
seq_scan         | 1782558
seq_tup_read     | 466560347
idx_scan         | 645524149
idx_tup_fetch    | 954031368
n_tup_ins        | 772875
n_tup_upd        | 3594780
n_tup_del        | 486793
n_tup_hot_upd    | 957822
n_live_tup       | 1477979
n_dead_tup       | 152
last_vacuum      | 2009-02-13 15:00:54.190851-05
last_autovacuum  | 2009-02-13 14:29:05.220037-05
last_analyze     | 2009-02-13 15:00:54.190851-05
last_autoanalyze | 2009-02-13 14:29:05.220037-05


-- 
PostgreSQL - XMPP: jdrake@jabber.postgresql.org  Consulting, Development, Support, Training  503-667-4564 -
http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997
 



Re: autovacuum not honoring pg_autovacuum in 8.3.5?

From
Jaime Casanova
Date:
On Fri, Feb 13, 2009 at 3:21 PM, Joshua D. Drake <jd@commandprompt.com> wrote:
> Hello,
>
> I found this today. Note, auto vacuum has been disabled for this
> relation for a very, very long time. At the very end you will see that
> this relation has been autovacuumed previously as well. We have a manual
> cron to vacuum this table every hour so I am unsure why autovacuum is
> doing what it is doing.
>
> app=# select * from pg_autovacuum where vacrelid = '21474846';
> -[ RECORD 1 ]----+---------
> vacrelid         | 21474846
> enabled          | f
> vac_base_thresh  | 0
> vac_scale_factor | 0
> anl_base_thresh  | 0
> anl_scale_factor | 0
> vac_cost_delay   | 0
> vac_cost_limit   | 0
> freeze_min_age   | 0
> freeze_max_age   | 0
>

i was bitten for this already... the problem is that you have all
parameters in 0... they should be -1 (specifically max_freeze_age)

--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157


Re: autovacuum not honoring pg_autovacuum in 8.3.5?

From
"Joshua D. Drake"
Date:
On Fri, 2009-02-13 at 15:51 -0500, Jaime Casanova wrote:
> On Fri, Feb 13, 2009 at 3:21 PM, Joshua D. Drake <jd@commandprompt.com> wrote:
> > Hello,
> >
> > I found this today. Note, auto vacuum has been disabled for this
> > relation for a very, very long time. At the very end you will see that
> > this relation has been autovacuumed previously as well. We have a manual
> > cron to vacuum this table every hour so I am unsure why autovacuum is
> > doing what it is doing.
> >
> > app=# select * from pg_autovacuum where vacrelid = '21474846';
> > -[ RECORD 1 ]----+---------
> > vacrelid         | 21474846
> > enabled          | f
> > vac_base_thresh  | 0
> > vac_scale_factor | 0
> > anl_base_thresh  | 0
> > anl_scale_factor | 0
> > vac_cost_delay   | 0
> > vac_cost_limit   | 0
> > freeze_min_age   | 0
> > freeze_max_age   | 0
> >
> 
> i was bitten for this already... the problem is that you have all
> parameters in 0... they should be -1 (specifically max_freeze_age)
> 

Thanks for the work around, but that is ridiculous. I still say this is
a bug. If I say enabled = f, the *only* thing that should be firing
autovacuum on that relation is xid wrap.

Sigh...

Joshua D. Drake


-- 
PostgreSQL - XMPP: jdrake@jabber.postgresql.org  Consulting, Development, Support, Training  503-667-4564 -
http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997
 



Re: autovacuum not honoring pg_autovacuum in 8.3.5?

From
Tom Lane
Date:
"Joshua D. Drake" <jd@commandprompt.com> writes:
> Thanks for the work around, but that is ridiculous. I still say this is
> a bug. If I say enabled = f, the *only* thing that should be firing
> autovacuum on that relation is xid wrap.

Right, and you have the xid wrap timeout set to zero.
        regards, tom lane


Re: autovacuum not honoring pg_autovacuum in 8.3.5?

From
Alvaro Herrera
Date:
Joshua D. Drake wrote:

> Thanks for the work around, but that is ridiculous. I still say this is
> a bug.

Yes, which is why we fixed it on 8.4 by dropping pg_autovacuum.  (As
Jaime and Tom say, it's actually pilot error, but the UI is crap so we
fixed it.)

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


Re: autovacuum not honoring pg_autovacuum in 8.3.5?

From
Robert Treat
Date:
On Monday 16 February 2009 11:20:11 Alvaro Herrera wrote:
> Joshua D. Drake wrote:
> > Thanks for the work around, but that is ridiculous. I still say this is
> > a bug.
>
> Yes, which is why we fixed it on 8.4 by dropping pg_autovacuum.  (As
> Jaime and Tom say, it's actually pilot error, but the UI is crap so we
> fixed it.)
>

A little confused by this... 

robert=# select version();                                              version
     
 
-----------------------------------------------------------------------------------------------------PostgreSQL
8.4develon i386-pc-solaris2.10, compiled by cc: Sun C 5.9 
 
SunOS_i386 2007/05/03, 32-bit
(1 row)

robert=# \d pg_catalog.pg_autovacuum
Did not find any relation named "pg_catalog.pg_autovacuum".
robert=# \dtS+ pg_catalog.pg_autovacuum                         List of relations  Schema   |     Name      | Type  |
Owner |  Size   | Description 
 
------------+---------------+-------+--------+---------+-------------pg_catalog | pg_autovacuum | table | robert | 0
bytes| 
 
(1 row)

I think this build is a couple weeks old, but is the pg_autovacuum table 
really gone in 8.4, or just deprecated? 

-- 
Robert Treat
Conjecture: http://www.xzilla.net
Consulting: http://www.omniti.com


Re: autovacuum not honoring pg_autovacuum in 8.3.5?

From
Euler Taveira de Oliveira
Date:
Robert Treat escreveu:
> I think this build is a couple weeks old, but is the pg_autovacuum table 
> really gone in 8.4, or just deprecated? 
> 
It is gone. As stated in the docs [1], pg_autovacuum catalog was just a
workaround.


[1] http://www.postgresql.org/docs/8.3/static/routine-vacuuming.html#AUTOVACUUM


--  Euler Taveira de Oliveira http://www.timbira.com/


Re: autovacuum not honoring pg_autovacuum in 8.3.5?

From
Tom Lane
Date:
Euler Taveira de Oliveira <euler@timbira.com> writes:
> Robert Treat escreveu:
>> I think this build is a couple weeks old, but is the pg_autovacuum table 
>> really gone in 8.4, or just deprecated? 
>> 
> It is gone.

http://archives.postgresql.org/pgsql-committers/2009-02/msg00077.php
        regards, tom lane