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

Re: [PATCHES] WAL logging freezing

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

> 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. 

Well, by default a Windows install is about 80MB, plus 7x 16MB WAL gives
nearly 200MB, so we're talking about the doubling the basic on-disk
footprint for every user if we let that happen.

> 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?

Given many users are individual PCs, or at least stand-alone servers not
in constant use, then I think more aggressive vacuuming makes sense as a
way to keep clog smaller. In many situations the time lost through
continually virus scanning databases will be more than if we do a more
regular autovacuum, so we shouldn't really worry about that.

Sounds like we need a GUC for those who don't care about 256MB though,
but may care about autovacuum switching in at bad moments.

Also, that solution doesn't square with the current autovacuum defaults:
We should set autovacuum on by default, with
autovacuum_vacuum_cost_delay = 10 by default.

-- 
  Simon Riggs             
  EnterpriseDB   http://www.enterprisedb.com



In response to

pgsql-hackers by date

Next:From: Josh BerkusDate: 2006-11-03 01:02:24
Subject: Re: Design Considerations for New Authentication Methods
Previous:From: Andrew SullivanDate: 2006-11-02 23:07:31
Subject: Re: Force 8.2 initdb to rename pg_database/pg_class minxid columns?

pgsql-patches by date

Next:From: Joachim WielandDate: 2006-11-03 03:57:21
Subject: Micro-doc patch for CREATE FUNCTION
Previous:From: Tom LaneDate: 2006-11-02 21:50:31
Subject: Re: [PATCHES] WAL logging freezing

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