"Niblett, David A" <niblettda at gru.com> writes:
> Just curious here, I emailed the list when I think it was
> down about a problem I was having with Slony and replicating
> a large database with large rows.
>
> We are using DBMail for our Email store and I was replicating
> it to another server with Slony.  The database is ~17G and
> easily has records in the multi-meg size.
>
> I started having problems where the CPU load was running around
> 5 consistently, and the database was almost 45G in size.  Since
> everything was becoming unusable I stopped slony, and uninstalled
> the nodes.  Once I did that, my process load went to <1 at almost
> all times and my database shrunk back to a more normal 17G size.
>
> Likewise on the slave database it dropped some in size, but a
> VACUUM FULL was required to get it back to normal.  Also the
> load on it dropped to almost nothing and more importantly the
> RAM and Swap went way down on both servers.
>
> So I'm wondering if this particular problem is what was
> causing me issues with Slony, or if I have something else
> wrong.  It seem to me that there must be some huge log space
> that was taking up a lot of space, but when I uninstalled
> the nodes that went away, so I can't look now.

Clearly _something_ bloated; we can't know now unless you did a VACUUM
FULL VERBOSE, and the results are still sitting in PG logs on your
disk...

If VACUUM FULL "resolved the issue," then something clearly wasn't
being vacuumed properly.  If you had some VACUUM VERBOSE output to
peek at, we might be able to discover that it was one table that
wasn't being vacuumed often enough, but presumably we can't, now.

Regrettably, this is something where "details count."  Vacuuming the
wrong table will do no good.

We had a case recently where doing a VACUUM FULL on pg_listener ran
for just a few seconds, and resolved a persistent situation of
replication running slowly and falling behind.  I'd go there, first,
as it's easy for that problem to arise, and it takes only a few
seconds to see if it helps.

Personally, I'd be inclined to keep (for weeks/months) the statistics
emitted by VACUUM FULL as that information can be fabulously valuable
for diagnosis purposes, and not just for Slony-I...
-- 
output = ("cbbrowne" "@" "ca.afilias.info")
<http://dev6.int.libertyrms.com/>
Christopher Browne
(416) 673-4124 (land)