Thanks for your help. Knowing where the problem lies and that there is
nothing we can do about it before a slony upgrade has at least stopped
our need for investigation into other causes.

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.

Thanks for you help, I would not have known what was causing the table
bloat without you. I am surprised this has not got anyone else running
with a large number of nodes, hopefully this thread will help anyone
suffering in the future.

Cheers,
Vicki



-----Original Message-----
From: Christopher Browne [mailto:cbbrowne at ca.afilias.info] 
Sent: 22 March 2007 19:31
To: Victoria Parsons
Cc: Joseph Shraibman; slony
Subject: Re: [Slony1-general] Vacuum full required?

"Victoria Parsons" <victoria.parsons at streamshield.com> writes:
> pg_listener does not have any indexes, so no re-indexing to be done.

Right; that's good news.

> I cannot find the function you use pg_relation_size, but I can see the
> rel_pages in pg_class stays at 1 for 'pg_listener' even though every
> vacuum claims to be reducing the pages from some large number to 1. Is
> the vacuum output lying to me? The utput below says it is reducing
pages
> from 97 to 1 but the data in pg_class shows it was only ever 1 page of
> data.
> sss=# select * from pg_listener order by listenerpid;
>          relname         | listenerpid | notification
> -------------------------+-------------+--------------
>  _sss_repcluster_Node_3  |        5637 |            0
>  _sss_repcluster_Confirm |        5637 |            0
>  _sss_repcluster_Event   |        5637 |            0
>  _sss_repcluster_Node_3  |        5644 |            0
>  _sss_repcluster_Node_2  |        7730 |            0
>  _sss_repcluster_Confirm |        7730 |            0
>  _sss_repcluster_Event   |        7730 |            0
>  _sss_repcluster_Node_2  |        7732 |            0
>  _sss_repcluster_Restart |        7866 |            0
>  _sss_repcluster_Event   |        7866 |            0
>  _sss_repcluster_Node_1  |        7873 |            0
>  _sss_repcluster_Node_1  |        7874 |            0
> (12 rows)

Unfortunately, it's common that whenever an event goes out to indicate
(for instance) a new SYNC, all of the tuples associated with the node
get updated.  The more nodes there are, the more "listening" that
happens.

In Slony-I 1.2, there is an attempt to generate less dead tuples in
pg_listener; things should improve there.
-- 
select 'cbbrowne' || '@' || 'ca.afilias.info';
<http://dba2.int.libertyrms.com/>
Christopher Browne
(416) 673-4124 (land)


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.