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

Re: VACUUMs take twice as long across all nodes

From: Andrew Sullivan <ajs(at)crankycanuck(dot)ca>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: VACUUMs take twice as long across all nodes
Date: 2006-10-29 14:58:25
Message-ID: 20061029145825.GB14585@phlogiston.dyndns.org (view raw or flat)
Thread:
Lists: pgsql-performance
Ok, I see Tom has diagnosed your problem.  Here are more hints
anyway:

On Fri, Oct 27, 2006 at 10:20:25AM +0100, Gavin Hamill wrote:
> > table bloat in various slony-related tables.
> 
> I know it takes longer, I know it blocks. It's never been a problem

The problem from a VACUUM FULL is that its taking longer causes the
vacuums on (especially) pg_listen and sl_log_[n] to be unable to
recover as many rows (because there's an older transaction around). 
This is a significant area of vulnerability in Slony.  You really
have to readjust your vacuum assumptions when using Slony.

> > 3.	Your backups "from the slave" aren't done with pg_dump,
> > right?
> 
> Em, they are indeed. I assumed that MVCC would ensure I got a
> consistent snapshot from the instant when pg_dump began. Am I wrong?

That's not the problem.  The problem is that when you restore the
dump of the slave, you'll have garbage.  Slony fools with the
catalogs on the replicas.  This is documented in the Slony docs, but
probably not in sufficiently large-type bold italics in red with the
<blink> tag set as would be appropriate for such a huge gotcha. 
Anyway, don't use pg_dump on a replica.  There's a tool that comes
with slony that will allow you to take consistent, restorable dumps
from replicas if you like.  (And you might as well throw away the
dumpfiles from the replicas that you have.  They won't work when you
restore them.)

A

-- 
Andrew Sullivan  | ajs(at)crankycanuck(dot)ca
"The year's penultimate month" is not in truth a good way of saying
November.
		--H.W. Fowler

In response to

Responses

pgsql-performance by date

Next:From: Gavin HamillDate: 2006-10-29 15:08:26
Subject: Re: VACUUMs take twice as long across all nodes
Previous:From: Andreas KostyrkaDate: 2006-10-28 22:28:05
Subject: partitioned table performance

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