"Mark Steben" <msteben at autorevenue.com> writes:
> We are running a Linux Redhat system and running a postgres 8.3.7 
> Database.  We are also running slony 1.2.21 with a simple 1 master -1 slave
> Configuration.  We wish to delete older inactive data from our largest
> table, the end result will be dropping from 380 million rows and 2.9 million
> Pages to 290 million rows.  When I perform a vacuum full in test, the table
> size Reduces to 2.2 million pages.  I would like this to be the end result,
> The problem is the vacuum full took nearly 36 hours to run.  I've also read
> The cautions about long running transactions being a potential problem to
> Slony.  
>
> In test, I have successfully run the delete to remove the rows and
> Currently have the table with the removed rows but the bloated page size.
> My questions are:
>   1. What better method other than VACUUM FULL is there to rid myself of
>       The bloat and,
>   2. How can I make this method play nice with slony?
>
> Thank you for your time,

CLUSTER is usually the relevant alternative to VACUUM FULL.  It has the
demerit that it doubles the size of the data, but it essentially
simultaneously does a reindex on all tables, so you're not left (as is
the case after VACUUM FULL) with a "clean" table, but indexes that are
in pretty bad shape.

It'll still run similarly long to VACUUM FULL.

By the way, I'm going to assume that the table undergoing this
maintenance is one which the application isn't presently adding to.  

If it is, then there's not much of a Slony-I issue - the CLUSTER/VACUUM
FULL will lock the table, blocking your application from doing work, so
that Slony-I won't have any work to do during that period.  :-) But I
suspect this isn't the case; a 36 hour outage seems more than
allowable...

If you've got a 36 hour transaction running, and the application *is*
active, then you'll surely want Slony-I to have a *way* fewer SYNCs, as
the larger the set of those, the more data just can't get cleared out,
and the worse performance is likely to get.

It would seem helpful to increase the sync timeout intervals:

{~}    slon -h
2010-08-18 18:05:57 EDTCONFIG main: slon version 2.1.0 starting up
usage: slon [options] clustername conninfo

Options:
    -h                    print usage message and exit
    -v                    print version and exit
    -d <debuglevel>       verbosity of logging (1..4)
    -s <milliseconds>     SYNC check interval (default 10000)
    -t <milliseconds>     SYNC interval timeout (default 60000)
    -o <milliseconds>     desired subscriber SYNC processing time
    -g <num>              maximum SYNC group size (default 6)
    -c <num>              how often to vacuum in cleanup cycles
    -p <filename>         slon pid file
    -f <filename>         slon configuration file
    -a <directory>        directory to store SYNC archive files
    -x <command>          program to run after writing archive file
    -q <num>              Terminate when this node reaches # of SYNCs
    -r <num>              # of syncs for -q option
    -l <interval>         this node should lag providers by this interval

In particular, the two values you'd fiddle with are the "-s" and "-t"
ones.

The default is to consider generating a SYNC every 10 seconds (-s
10000); I'd suggest increasing that to once a minute, or more, so that
there's not a glut of tens of thousands of SYNCs being looked at
continually.  The "-t" value needs to be higher than the "-s" value.

If you set "-s 180000 -t 180000", then there'll be a SYNC every 3
minutes, and, after 36 hours, that adds to just 720 SYNCs to be
reconsidering, which isn't over-gigantic.

You may want to simply open a psql session and type "BEGIN;", and leave
it open for a while, and watch to see if there are any of the tables
that bloat up notably heavily.  

pg_listener is the "usual suspect", and the fact that the above config
change reduces the number of SYNCs, and the number of times event
requests are raised, should diminish the bloat of dead tuples there.
It'll bloat a bit, but by cutting down on SYNCs, it won't be zillions of
dead tuples waiting for that VACUUM FULL/CLUSTER to end.
-- 
select 'cbbrowne' || '@' || 'ca.afilias.info';
Christopher Browne
"Bother,"  said Pooh,  "Eeyore, ready  two photon  torpedoes  and lock
phasers on the Heffalump, Piglet, meet me in transporter room three"