Shaun Thomas <sthomas at leapfrogonline.com> writes:
> I'm still going to assume it's just Savvis and their finicky firewall.  
> I've got our sysadmin operating under that assumption, and hope if 
> multiple sets don't work, we can get them to, at least temporarily, 
> turn off whatever is killing my slons.

As details have emerged about your situation, I am concluding that
that's *exactly* the problem.

Splitting the data into multiple sets will help get *most* of the
tables subscribed, but unless you can do something to help that
biggest table, I think you'll find that the problem is the amount of
time it takes to do the longest reindex.  

Note: It doesn't index them all at once; the methodology is to loop
thus:

  for each table:
     truncate table on subscriber
     shut off indices (catalog hack)
     COPY to pipe on provider / COPY from pipe on subscriber
     turn indices back on (unhack catalog)
     reindex table

[Aside: We've got a theory that in 2.x we might get a win out of doing
this in parallel.  Thus, we might open, say, 4 threads/8 threads, and
try to keep them all busy.  As soon as one table finishes copying and
starts reindexing, we could start another COPY against that
thread/connection...  It wouldn't take many threads/connections to
maximize the benefits.  That likely won't be in 2.0, though...]

There may *still* be a way around the problem; supposing the
troublesome table has 4 indexes on it, you could, on the subscriber
node, drop the 3 that aren't the candidate primary key, which
hopefully helps cut REINDEX time enough, and then, afterwards,
recreate the 3 indexes.  

If you're on PG 8.2, you may be able to re-add them in the background,
which would be slick :-).

If not, you'll have to do them in the foreground, and will find that
replication will fall behind because inserts into that table on the
subscriber are being blocked by the lock for the transaction.  Life...
Eventually the node should catch up...

FYI, you should probably let the subscriber catch up before redoing
such indexes; there is, at present, an inefficiency in handling the
*later* data when the system has fallen behind due to having a really
enormous transaction running (e.g. - like the initial subscription
that runs many hours).  The early catchup isn't bad; it gets bad
later.

It looks like the Skype people noticed this issue and did something
about for their replication system; I was talking with Jan about it
last week, and he seems to think he can have an improvement for it for
Slony-I 2.0.  Won't help yet, but someday...
-- 
"cbbrowne","@","ca.afilias.info"
<http://dba2.int.libertyrms.com/>
Christopher Browne
(416) 673-4124 (land)