Hi Andrew,

My first reason for this query is because vacuum full is sorting out the
problem, where a regular vacuum wouldn't. I'm still not sure why, but it
is definitely the case that performance does not improve following a
vacuum, or vacuum analyze, but increases dramatically after a vacuum
full. My best reasoning is that the table changes so frequently that the
old entries end up spread all around the disk so it takes a long time to
read from the table because it is reading from lots of different disk
areas. The vacuum full cleans all these old spread out entries, so
speeding up reads. To be honest though, I don't need to know why it
makes a difference, just that vacuum full is what works.

We have found we need to perform this vacuum full on pg_listener once
every 10 minutes, instead of once an hour, but doing that our
performance problem is fixed. I am a bit worried about locks, but my
test system has now been running with the vacuum full (only on
pg_listener) every 2 minutes for 3 days, and nothing has reported an
error. A block causing a slight delay is much better than a constant
performance problem.

Vicki

 

-----Original Message-----
From: slony1-general-bounces at lists.slony.info
[mailto:slony1-general-bounces at lists.slony.info] On Behalf Of Andrew
Sullivan
Sent: 26 March 2007 12:38
To: slony1-general at lists.slony.info
Subject: Re: [Slony1-general] Vacuum full required?

On Fri, Mar 23, 2007 at 10:29:59AM -0000, Victoria Parsons wrote:

> Because we are not ready to upgrade slony at the moment we will solve
> this with a vacuum full of pg_listener for each database once an hour.
> That seems to be keeping our replication under control.

You ought to be able to cause the same effect with a plain vacuum,
maybe more often than once an hour.  

Remember that VACUUM FULL takes a write lock on the table, so you're
going to block things while you do this.

Just as important, VACUUM FULL collapses the table, meaning that the
very next write on the table will require an I/O operation to extend
the table _before_ it can write in the table.  A regular VACUUM will
merely mark the relevant areas as eligible spots for writing.  That
means that the next write operation that comes alone can re-use that
free space, which actually means less I/O and therefore better
performance.

Note that we have actually had tables in some of our databases where
we were, historically, vacuuming every 5 minutes.  The key is to watch
your I/O and table growth.  There's a sweet spot where you vacuum that
often, the table remains managable in size, and the I/O is as low as
it can get.

A


-- 
Andrew Sullivan                         204-4141 Yonge Street
Afilias Canada                        Toronto, Ontario Canada
<andrew at ca.afilias.info>                              M2P 2A8
jabber: ajsaf at jabber.org                 +1 416 646 3304 x4110
_______________________________________________
Slony1-general mailing list
Slony1-general at lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general


This message should be regarded as confidential. If you have received this 
email in error please notify the sender and destroy it immediately.
Statements of intent shall only become binding when confirmed in hard copy 
by an authorized signatory.