Your (everyones) efforts are certainly appreciated and I fully intend  
to try out the SET MOVE TABLE strategy in the next week or so on a  
200GB migration.

Catchup isn't so bad since each large block occurs in its own  
transaction. I'm not really concerned with the actual time to  
subscribe but rather than fact my master node is not very happy when  
we add new slaves.

Cheers and I'll buy you a beer at the next GTABug.

- Rod

On 11-Jul-07, at 3:44 PM, Christopher Browne wrote:

> Rod Taylor <rbt at sitesell.com> writes:
>>> This is do-able, but it feels mighty clumsy, and even more fragile.
>>>
>>> It seems really tempting to think about having a "virtual
>>> replication set" for this, and putting that into Slony-I proper.
>>>
>>> The notion of the "virtual replication set" still seems pretty  
>>> clumsy.
>>
>> Not too sure about hiding it. It is very fragile if it breaks mid-way
>> through. If it is hidden it will be difficult to fix, unless you
>> think you can make it continue when it finds a virtual set partially
>> subscribed. Good transaction boundaries leave the number of possible
>> broken states at a small enough count.
>>
>>> Here's an outline of a very different approach that Jan and I have
>>> been talking about that we call "COPY pipelining." It ought to help
>>> by parallelizing the data load. It's in the TODO...
>>
>> Fine for small to mid sized databases. If you're using table
>> partitioning because of the size of the structures, and have lots of
>> partitions, this isn't going to do much to eliminate the large
>> transaction problem.
>>
>> Granted, it would be very nice if subscribes went a little quicker
>> :)
>
> If you set up 4 pipelines, then that should improve the speed by a
> factor of around 4, which should be a not unappreciable improvement
> :-).
>
> FYI, Jan has been trying out an approach that the Skype guys pointed
> out that seems to *massively* improve the ability to catch up after
> large transactions, so I think that may get helped soon enough (e.g. -
> v2.0).
> <http://lists.slony.info/pipermail/slony1-commit/2007-July/ 
> 001854.html>
> That may be the "better change."  Note that it's a very small change;
> it might well work in 1.2, too.
>
> In any case, for a database with a whole lot of partitions, I don't
> think it's too outrageous to directly head to the "subscribe in steps"
> approach.
>
> foreach $table (@tables_in_set_10) {
>    create set (id=999+$table, provider=1, comment='Temporary set';
>    set move table (origin=1, id=$table, new set=999+$table);
>    subscribe set (id=999+$table, provider=1, receiver=2);
> }
> subscribe set (id=10, provider=1, receiver=2);
>
> and then clean up with a bunch of MERGE SET requests afterwards.
> -- 
> "cbbrowne","@","cbbrowne.com"
> http://cbbrowne.com/info/rdbms.html
> Rules of the Evil Overlord #78.  "I will not tell my Legions of Terror
> "And he must  be taken alive!" The command will be:  ``And try to take
> him alive if it is reasonably practical.''"
> <http://www.eviloverlord.com/>
> _______________________________________________
> Slony1-general mailing list
> Slony1-general at lists.slony.info
> http://lists.slony.info/mailman/listinfo/slony1-general