Lists: | pgsql-hackers |
---|
From: | "Joshua D(dot) Drake" <jd(at)commandprompt(dot)com> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | autovacuum not honoring pg_autovacuum in 8.3.5? |
Date: | 2009-02-13 20:21:17 |
Message-ID: | 1234556477.7908.17.camel@jd-laptop.pragmaticzealot.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
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(at)jabber(dot)postgresql(dot)org
Consulting, Development, Support, Training
503-667-4564 - http://www.commandprompt.com/
The PostgreSQL Company, serving since 1997
From: | Jaime Casanova <jcasanov(at)systemguards(dot)com(dot)ec> |
---|---|
To: | jd(at)commandprompt(dot)com |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: autovacuum not honoring pg_autovacuum in 8.3.5? |
Date: | 2009-02-13 20:51:38 |
Message-ID: | 3073cc9b0902131251l7933347bqc1eaabc70a611a59@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
On Fri, Feb 13, 2009 at 3:21 PM, Joshua D. Drake <jd(at)commandprompt(dot)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
From: | "Joshua D(dot) Drake" <jd(at)commandprompt(dot)com> |
---|---|
To: | Jaime Casanova <jcasanov(at)systemguards(dot)com(dot)ec> |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: autovacuum not honoring pg_autovacuum in 8.3.5? |
Date: | 2009-02-13 21:16:02 |
Message-ID: | 1234559763.6483.1.camel@jd-laptop.pragmaticzealot.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
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(at)commandprompt(dot)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(at)jabber(dot)postgresql(dot)org
Consulting, Development, Support, Training
503-667-4564 - http://www.commandprompt.com/
The PostgreSQL Company, serving since 1997
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | jd(at)commandprompt(dot)com |
Cc: | Jaime Casanova <jcasanov(at)systemguards(dot)com(dot)ec>, pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: autovacuum not honoring pg_autovacuum in 8.3.5? |
Date: | 2009-02-13 21:18:32 |
Message-ID: | 18991.1234559912@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
"Joshua D. Drake" <jd(at)commandprompt(dot)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
From: | Alvaro Herrera <alvherre(at)commandprompt(dot)com> |
---|---|
To: | "Joshua D(dot) Drake" <jd(at)commandprompt(dot)com> |
Cc: | Jaime Casanova <jcasanov(at)systemguards(dot)com(dot)ec>, pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: autovacuum not honoring pg_autovacuum in 8.3.5? |
Date: | 2009-02-16 16:20:11 |
Message-ID: | 20090216162011.GA11185@alvh.no-ip.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
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.
From: | Robert Treat <xzilla(at)users(dot)sourceforge(dot)net> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Cc: | Alvaro Herrera <alvherre(at)commandprompt(dot)com>, "Joshua D(dot) Drake" <jd(at)commandprompt(dot)com>, Jaime Casanova <jcasanov(at)systemguards(dot)com(dot)ec> |
Subject: | Re: autovacuum not honoring pg_autovacuum in 8.3.5? |
Date: | 2009-02-19 15:36:08 |
Message-ID: | 200902191036.09368.xzilla@users.sourceforge.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
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.4devel on 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
From: | Euler Taveira de Oliveira <euler(at)timbira(dot)com> |
---|---|
To: | Robert Treat <xzilla(at)users(dot)sourceforge(dot)net> |
Cc: | pgsql-hackers(at)postgresql(dot)org, Alvaro Herrera <alvherre(at)commandprompt(dot)com>, "Joshua D(dot) Drake" <jd(at)commandprompt(dot)com>, Jaime Casanova <jcasanov(at)systemguards(dot)com(dot)ec> |
Subject: | Re: autovacuum not honoring pg_autovacuum in 8.3.5? |
Date: | 2009-02-19 15:56:07 |
Message-ID: | 499D8117.3050702@timbira.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
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/
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Euler Taveira de Oliveira <euler(at)timbira(dot)com> |
Cc: | Robert Treat <xzilla(at)users(dot)sourceforge(dot)net>, pgsql-hackers(at)postgresql(dot)org, Alvaro Herrera <alvherre(at)commandprompt(dot)com>, "Joshua D(dot) Drake" <jd(at)commandprompt(dot)com>, Jaime Casanova <jcasanov(at)systemguards(dot)com(dot)ec> |
Subject: | Re: autovacuum not honoring pg_autovacuum in 8.3.5? |
Date: | 2009-02-19 16:25:50 |
Message-ID: | 8999.1235060750@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
Euler Taveira de Oliveira <euler(at)timbira(dot)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