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

Re: [PATCHES] WAL logging freezing

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Alvaro Herrera <alvherre(at)commandprompt(dot)com>
Cc: Heikki Linnakangas <heikki(at)enterprisedb(dot)com>, Simon Riggs <simon(at)2ndquadrant(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: [PATCHES] WAL logging freezing
Date: 2006-11-02 21:50:31
Message-ID: 21576.1162504231@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-patches
I wrote:
> * pg_clog is truncated according to the oldest pg_database.datvacuumxid.

While testing this patch I realized that there's a bit of an issue here.
It's usually going to be the case that the oldest datvacuumxid is
template0's, meaning that it will never be possible to truncate clog
until autovacuum decides that template0 is at risk of wraparound and
goes and vacuums it.  Shortening the freeze horizon will reduce the size
that pg_clog occupies just *after* that happens, but we're still going
to see pg_clog bloating up to something close to 256MB before autovacuum
kicks in.  And there is nothing a user can do about it, unless he's
willing to override the datallowconn = false safety cover on template0
so he can connect to it and vacuum it manually.

This wasn't a problem in the pre-8.2 logic because we ignored
non-connectable databases while determining the global minimum
datvacuumxid, but it's a real problem now.

Seems like either we go back to ignoring non-connectable databases
(with the risks that entails), or adopt some more-aggressive policy
for launching autovacuums on them, or give up the idea of keeping
pg_clog small.  A more-aggressive policy seems like the best option,
but I'm not entirely sure what it should look like.  Maybe force
autovacuum when age(datvacuumxid) exceeds twice the freeze horizon,
or some such?  Comments?

			regards, tom lane

In response to

Responses

pgsql-hackers by date

Next:From: Andrew SullivanDate: 2006-11-02 21:52:16
Subject: Re: Design Considerations for New Authentication Methods
Previous:From: Magnus HaganderDate: 2006-11-02 21:48:29
Subject: Re: Design Considerations for New Authentication Methods

pgsql-patches by date

Next:From: Simon RiggsDate: 2006-11-02 23:57:08
Subject: Re: [PATCHES] WAL logging freezing
Previous:From: Tom LaneDate: 2006-11-02 14:59:54
Subject: Re: [PATCHES] Writing WAL for relcache invalidation:pg_internal.init

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