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
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-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

Browse pgsql-hackers by date

  From Date Subject
Next Message Andrew Sullivan 2006-11-02 21:52:16 Re: Design Considerations for New Authentication Methods
Previous Message Magnus Hagander 2006-11-02 21:48:29 Re: Design Considerations for New Authentication Methods

Browse pgsql-patches by date

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