Rod Taylor wrote:

>I'm working on upgrading 7.4 to 8.0 at the moment via Slony 1.0.5
>(modified to handle a group size larger than 100).
>
>Since I ran into large issues due to the initial data copy (an 8 day
>transaction), I decided to try creating one set per table, and merging
>them as it went along. This mostly worked.
>
>The initial data copy was done in small chunks.
>
>The problem is that when copying multiple sets from a single source to a
>single destination, the query plan does an index scan based on the
>Origin key id, and nothing else (no transaction range is included).
>
>This is made far worse by the forced index scan.
>
>See below for the query and the plan.
>
>        http://rbt.urbandb.com/slonyplan
>
>This is what I'm left with until it gets through the various MERGE_SET
>events.
>
>  
>
This seems to fit into the category of "queries I was planning to start 
tuning once 1.1 gets released."

There are definitely some cases where the queries that are generated are 
"a bit pessimal." 

I ran into one where, after waiting quite a while to start as 
subscription (alongside some co-conditions; it may not be trivially 
repeatable...), I wound up with a "NOT IN (HUGE LIST OF ITEMS) query 
which was sufficiently large as to blow up the query parser.  (It was 
just short of 640K long, which suggested some joke to the effect that 
"640K ought to be enough for anyone...")

There seem to be possibilities for multiple sorts of optimizations of 
queries, whether by:
 a) Folding similar clauses together

  For your sample, a likely fruitful change would involve...

           log_tableid between 919 and 926
             and (log_xid < '284891817' and "_ssrep".xxid_lt_snapshot(log_xid, '284889837:284891817:''284891666'',''284889837'''))
             and (log_xid >= '284889652' and "_ssrep".xxid_ge_snapshot(log_xid, '284889652:284889654:''284889652'''))

               or

               log_table_id between 500 and 918

            and (log_xid < '284891817' and "_ssrep".xxid_lt_snapshot(log_xid, '284889837:284891817:''284891666'',''284889837'''))
            and (log_xid >= '284889652' and "_ssrep".xxid_ge_snapshot(log_xid, '284889652:284889654:''284889652'''))


    And it's possible (haven't checked) that it's better to have

              log_table_id between 500 and 926 and

            and (log_xid < '284891817' and "_ssrep".xxid_lt_snapshot(log_xid, '284889837:284891817:''284891666'',''284889837'''))
            and (log_xid >= '284889652' and "_ssrep".xxid_ge_snapshot(log_xid, '284889652:284889654:''284889652'''))


 b) Putting common values into temp tables

    In the above, this might lead to...


exists (select * from temp_table_ids where tab_id = log_tableid)
and (this) and (that)


Which would mean needing to vacuum pg_class/pg_attribute a bit more often...

Alas, that won't be addressing your issue tomorrow :-(.

I do like your approach of splitting things up into smaller sets; we 
haven't seen specific cases of things actually breaking down due to it 
taking too long to get subscriptions going, but we have perceived that 
it could be an issue for particularly large sets.

You may now have more insight into how the system deals with running for 
significant periods when "staggeringly behind"; something I have been 
curious about is what kind of balance of benefit there is in increasing 
the sizes of the "sync groups."

I notice your comment thus:

(modified to handle a group size larger than 100).

Is this about changing the FETCHes to pull > 100 records at a time?  Or 
about allowing the grouping of more syncs together?

An open question (to me) is whether it is particularly beneficial to 
raise these sizes when behind or not.

I'd expect increasing each of these to have three effects:

1.  Doubling the 'grouping' should cut down the number of queries by ~ 
1/2, which should diminishing parsing overhead.  I would expect this to 
be relatively immaterial, but could be wrong.

2.  Doubling the 'grouping' will lead to query result sets being larger 
on the provider, which will have some added cost in memory and in the 
cost of sorting

3.  Doubling the 'grouping' should allow pages that provide data for two 
SYNCs to be read once instead of twice.

I'd expect #2 to be a (significant?) cost, and for #3 to be a 
(significant?) benefit.  I'm not sure how they would balance.

Another open question is whether or not there may be a possibility for 
it to be beneficial to add additional indices on some of the Slony-I 
tables to help in such cases.

That's more questions than answers, but that's where I am...