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

Search Discussions

  • Jaime Casanova at Feb 13, 2009 at 8:51 pm

    On Fri, Feb 13, 2009 at 3:21 PM, Joshua D. Drake 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
  • Joshua D. Drake at Feb 13, 2009 at 9:16 pm

    On Fri, 2009-02-13 at 15:51 -0500, Jaime Casanova wrote:
    On Fri, Feb 13, 2009 at 3:21 PM, Joshua D. Drake 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
  • Tom Lane at Feb 13, 2009 at 9:18 pm

    "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
  • Alvaro Herrera at Feb 16, 2009 at 4:20 pm

    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.
  • Robert Treat at Feb 19, 2009 at 3:36 pm

    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
  • Euler Taveira de Oliveira at Feb 19, 2009 at 3:55 pm

    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/
  • Tom Lane at Feb 19, 2009 at 4:26 pm

    Euler Taveira de Oliveira 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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedFeb 13, '09 at 8:21p
activeFeb 19, '09 at 4:26p
posts8
users6
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase