On 9/27/2005 3:08 PM, Christopher Browne wrote:

> Joe Markwardt wrote:
> 
>>Hi all,
>>	I am having some problems trying to get slony to catch up after a
>>weekend of heavy updates. I've tried playing with the -s and -g settings
>>for slony, but that hasn't seemed to have any effect on the situation.
>>I'm not sure where to proceed from here, so heres where I say "uncle"
>>and ask for help from those wiser than I :-)  I'm not sure what info you
>>need so I'll just have at it ( If I leave anything out let me know and
>>i'll get it for you):
>>
>>setup: replicate 3 databases from node 1 to node 2
>>	Each database is replicated using a separate slony config file (using
>>the slon-tools)
>>
>>	node 1: provider
>>		PG 7.4.7
>>		Slony 1.1.0
>>
>>	node 2: subsciber
>>		PG 8.0.3
>>		Slony 1.1.0
>>
>>Problem:
>>	node 2 is ~ 3 days behind node 1 after a series of heavy updates on the
>>weekend and falling further behind.
>>
>>Symtops:
>>	the FETCH statement on the provider is eating 100% of one of the CPU's
>>on the server, not touching the disks at all.  The subscriber spends 99%
>>of its time Idle in Transaction (waiting for the fetch to finish?)
>>
>>Details:
>>
>>from the node2 slony log:
>>
>>2005-09-27 10:55:28 CDT DEBUG2 remoteListenThread_1: queue event
>>1,1691509 SYNC
>>2005-09-27 10:55:28 CDT DEBUG2 remoteHelperThread_1_1: 120.122 seconds
>>delay for first row
>>2005-09-27 10:55:28 CDT DEBUG2 remoteHelperThread_1_1: 120.122 seconds
>>until close cursor
>>2005-09-27 10:55:28 CDT DEBUG2 remoteWorkerThread_1: new sl_rowid_seq
>>value: 1000000000000000
>>2005-09-27 10:55:28 CDT DEBUG2 remoteWorkerThread_1: SYNC 1610537 done
>>in 120.170 seconds
>>2005-09-27 10:55:28 CDT DEBUG2 remoteWorkerThread_1: Received event
>>1,1610538 SYNC
>>2005-09-27 10:55:28 CDT DEBUG2 remoteWorkerThread_1: SYNC 1610538
>>processing
>>2005-09-27 10:55:28 CDT DEBUG2 remoteWorkerThread_1: syncing set 1 with
>>114 table(s) from provider 1
>>2005-09-27 10:55:30 CDT DEBUG2 syncThread: new sl_action_seq 1 - SYNC
>>635243
>>
>>from sl_status:
>>select * from _pl_replication.sl_status ;
>> st_origin | st_received | st_last_event |      st_last_event_ts      |
>>st_last_received |    st_last_received_ts     |
>>st_last_received_event_ts  | st_lag_num_events |      st_lag_time
>>-----------+-------------+---------------+----------------------------+------------------+----------------------------+----------------------------+-------------------+-----------------------
>>         1 |           2 |       1691576 | 2005-09-27 10:58:05.907383 |
>>1610538 | 2005-09-27 10:55:44.046123 | 2005-09-24 14:02:55.407712 |
>>81038 | 2 days 20:55:12.07423
>>
>>from sl_log_1:
>> select count(*) from _pl_replication.sl_log_1;
>>  count
>>----------
>> 14427361
>>
>>Any ideas or suggestions on how to reverse this process so it will start
>>catching up again?
>>
>>  
>>
> 
> We have just been able to successfully point at a Probably Really Good
> answer to this; it just got checked into CVS HEAD and 1_1_STABLE...
> 
> Let me suggest that you create an extra index:
> 
> create index sl_log_1_idx2 on _pl_replication.sl_log_1(log_xid
> _pl_replication.xxid_ops);

Is that an optimization problem introduced by using BETWEEN instead of 
the excessive OR chains?


Jan

> 
> This needs to be created on any provider node; it won't be particularly
> valuable on nodes that only subscribe.
> 
> It should MASSIVELY increase the speed of the queries; you will more
> than likely see the "120.122 seconds delay for first row" message
> diminish to just a few seconds.
> 
> In the short term, you might also want to consider two other things:
> 
>  a) Set the "-s" option somewhat higher until things catch up; default
> is 10000 ms; "-s60000" would do one SYNC per minute, which might be
> useful...
> 
>  b) It looks like you're processing just one SYNC at a time; make sure
> the "-g" option is set to some value; we often run with "-g40" to group
> 40 SYNCs together at a time...
> _______________________________________________
> Slony1-general mailing list
> Slony1-general at gborg.postgresql.org
> http://gborg.postgresql.org/mailman/listinfo/slony1-general


-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck at Yahoo.com #