Skip site navigation (1) Skip section navigation (2)

Re: can't stop autovacuum by HUP'ing the server

From: "Dave Cramer" <pg(at)fastcrypt(dot)com>
To: "Alvaro Herrera" <alvherre(at)commandprompt(dot)com>
Cc: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: can't stop autovacuum by HUP'ing the server
Date: 2008-08-26 16:58:59
Message-ID: 491f66a50808260958i70c3bd23hc9bbcacc6b297f84@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
On Tue, Aug 26, 2008 at 12:50 PM, Alvaro Herrera <alvherre(at)commandprompt(dot)com
> wrote:

> Dave Cramer wrote:
>
> > Yes
> >
> >  select * from pg_database where datname='template0';
> >   datname  | datdba | encoding | datistemplate | datallowconn |
> datconnlimit
> > | datlastsysoid | datfrozenxid | dattablespace | datconfig |
> > datacl
> >
> -----------+--------+----------+---------------+--------------+--------------+---------------+--------------+---------------+-----------+-------------------------------------
> >  template0 |     10 |        6 | t             | f            |
> -1
> > |         11510 |    201850617 |          1663 |           |
> > {=c/postgres,postgres=CTc/postgres}
> >
> > So how to fix ?
>
> I think I see the problem -- vac_truncate_clog is not ignoring these
> databases when passing the new frozen value to SetTransactionIdLimit.
>
>    /*
>     * Scan pg_database to compute the minimum datfrozenxid
>     *
>     * Note: we need not worry about a race condition with new entries being
>     * inserted by CREATE DATABASE.  Any such entry will have a copy of some
>     * existing DB's datfrozenxid, and that source DB cannot be ours because
>     * of the interlock against copying a DB containing an active backend.
>     * Hence the new entry will not reduce the minimum.  Also, if two
> VACUUMs
>     * concurrently modify the datfrozenxid's of different databases, the
>     * worst possible outcome is that pg_clog is not truncated as
> aggressively
>     * as it could be.
>     */
>    relation = heap_open(DatabaseRelationId, AccessShareLock);
>
>    scan = heap_beginscan(relation, SnapshotNow, 0, NULL);
>
>    while ((tuple = heap_getnext(scan, ForwardScanDirection)) != NULL)
>    {
>        Form_pg_database dbform = (Form_pg_database) GETSTRUCT(tuple);
>
>        Assert(TransactionIdIsNormal(dbform->datfrozenxid));
>
>        if (TransactionIdPrecedes(myXID, dbform->datfrozenxid))
>            frozenAlreadyWrapped = true;
>        else if (TransactionIdPrecedes(dbform->datfrozenxid, frozenXID))
>        {
>            frozenXID = dbform->datfrozenxid;
>            namecpy(&oldest_datname, &dbform->datname);
>        }
>    }
>
>    ...
>
>    /*
>     * Update the wrap limit for GetNewTransactionId.  Note: this function
>     * will also signal the postmaster for an(other) autovac cycle if
> needed.
>     */
>    SetTransactionIdLimit(frozenXID, &oldest_datname);
>
>
> If it doesn't ignore them, then it should be properly vacuuming
> template0 as any other database.  We've changed autovac's behavior on
> this area back and forth so I may be misremembering what's our rationale
> du jour.
>

Well, I'm willing to help debug this, however this is a busy production
database and I need to be able to turn it off for a few hours a day. Would
changing autovacuum_freeze_max_age be a solution ?

Dave

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

In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2008-08-26 17:00:48
Subject: Re: can't stop autovacuum by HUP'ing the server
Previous:From: Alvaro HerreraDate: 2008-08-26 16:50:00
Subject: Re: can't stop autovacuum by HUP'ing the server

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group