goldengate ogg 常用调优方法 – 并行处理与进程拆分

ALLSTARS_ORACLE

goldengate ogg 常用调优方法 –  并行处理与进程拆分

拆分粒度
可以通过schema进行区分，每个复制链路负责一个或多个schema；
也可以根据表进行分割，每个进程负责不同表的集合；
对于同一个表也可以通过Range拆分为几个进程同时处理。例如：
Replicat 1：
MAP sales.acct, TARGET sales.acct, FILTER (@RANGE (1, 2));
Replicat 2：
MAP sales.acct, TARGET sales.acct, FILTER (@RANGE (2, 2));
进程拆分的注意事项
各进程间没有同步机制，应尽量确保同一交易涉及表在一个进程
单个extract进程可处理日志一般为30-50G/小时，单个replicat进程一般只能处理1G队列/小时，可采用一个extract对多个replicat的模式
由于extract在catch up（追赶）模式需要读取归档日志，速度慢且耗费资源高，建议extract一旦出现较大延迟则立即进行拆分


保证抽取一致性
由于OGG的Extract性能较高，可以使用尽量少的Extract完成抽取，多个之间以业务或Schema进行区分
单个Extract抽取出来的队列中可以保证交易的一致性和先后顺序
尽量保证投递一致性
如单个Replicat无法满足一个队列投递数据要求
可以根据表进行分割，每个进程负责不同表的集合，尽量保证同一业务涉及表放在一个Replicat中，可以保证一致性；（需临时禁止表间的外键链接保证Replicat可进行拆分）
对于同一个表也可以通过Range拆分为几个进程同时处理
Replicat拆分可能临时造成各进程间不同步，但是
多个Replicat性能会得到很大提高，可以保证数据复制始终是实时的
当源端出现灾难后，由于Extract可以保证源端抽取时数据的一致性，而目标端多Replicat读取的是同一个队列，当它们应用队列数据完毕后是可以达到数据一致的

ALLSTARS_ORACLE

为了提高性能，我们分别配置了五个replicat进程来对数据进行插入操作
Oracle数据库的replicat
ADD REPLICAT rep1, EXTTRAIL /home/oracle/ogg/dirdat/ma, BEGIN now
ADD REPLICAT rep2, EXTTRAIL /home/oracle/ogg/dirdat/ma, BEGIN now
ADD REPLICAT rep3, EXTTRAIL /home/oracle/ogg/dirdat/ma, BEGIN now
ADD REPLICAT rep4, EXTTRAIL /home/oracle/ogg/dirdat/ma, BEGIN now
ADD REPLICAT rep5, EXTTRAIL /home/oracle/ogg/dirdat/ma, BEGIN now
REPLICAT rep1
USERID goldengate,PASSWORD goldengate
ASSUMETARGETDEFS
REPERROR (default,abend)
DBOPTIONS LIMITROWS DEFERREFCONST
BATCHSQL BATCHESPERQUEUE 1000, BATCHTRANSOPS 2000 ,OPSPERBATCH 6000 , OPSPERQUEUE 6000
GROUPTRANSOPS 20000
ALLOWNOOPUPDATES
DDL include all
map test.T_BILL_BALANCE_DETAIL_201112_0, target settle.T_BILL_BALANCE_DETAIL_201112_0 , FILTER (@RANGE (1, 5));
map test.T_BILL_BALANCE_DETAIL_201112_1, target settle.T_BILL_BALANCE_DETAIL_201112_1 , FILTER (@RANGE (1, 5));
map test.T_BILL_BALANCE_DETAIL_201112_2, target settle.T_BILL_BALANCE_DETAIL_201112_2 , FILTER (@RANGE (1, 5));
map test.T_BILL_BALANCE_DETAIL_201112_3, target settle.T_BILL_BALANCE_DETAIL_201112_3 , FILTER (@RANGE (1, 5));
map test.T_BILL_BALANCE_DETAIL_201112_4, target settle.T_BILL_BALANCE_DETAIL_201112_4 , FILTER (@RANGE (1, 5));
map test.T_ACCOUNT_1, target settle.T_ACCOUNT_1;
map test.T_SERVICE_1, target settle.T_SERVICE_1;
map test.T_CUSTOMER_1, target settle.T_CUSTOMER_1;
MAP TEST.TF01 ,TARGET SETTLE.TF01 , COLMAP (F2=F2,F4=F4,F6=F6,F1=@COLSTAT (NULL),F3=@COLSTAT (NULL),F5=@COLSTAT (NULL),dd=@datenow (
));
MAP TEST.*, TARGET SETTLE.*;
REPLICAT rep2
USERID goldengate,PASSWORD goldengate
ASSUMETARGETDEFS
REPERROR (default,abend)
DBOPTIONS LIMITROWS DEFERREFCONST
BATCHSQL BATCHESPERQUEUE 1000, BATCHTRANSOPS 2000 ,OPSPERBATCH 6000 , OPSPERQUEUE 6000
GROUPTRANSOPS 20000
DDL exclude all
map test.T_BILL_BALANCE_DETAIL_201112_0, target settle.T_BILL_BALANCE_DETAIL_201112_0 , FILTER (@RANGE (2, 5));
map test.T_BILL_BALANCE_DETAIL_201112_1, target settle.T_BILL_BALANCE_DETAIL_201112_1 , FILTER (@RANGE (2, 5));
map test.T_BILL_BALANCE_DETAIL_201112_2, target settle.T_BILL_BALANCE_DETAIL_201112_2 , FILTER (@RANGE (2, 5));
map test.T_BILL_BALANCE_DETAIL_201112_3, target settle.T_BILL_BALANCE_DETAIL_201112_3 , FILTER (@RANGE (2, 5));
map test.T_BILL_BALANCE_DETAIL_201112_4, target settle.T_BILL_BALANCE_DETAIL_201112_4 , FILTER (@RANGE (2, 5));
REPLICAT rep3
USERID goldengate,PASSWORD goldengate
ASSUMETARGETDEFS
REPERROR (default,abend)
DBOPTIONS LIMITROWS DEFERREFCONST
BATCHSQL BATCHESPERQUEUE 1000, BATCHTRANSOPS 2000 ,OPSPERBATCH 6000 , OPSPERQUEUE 6000
GROUPTRANSOPS 20000
DDL exclude all
map test.T_BILL_BALANCE_DETAIL_201112_0, target settle.T_BILL_BALANCE_DETAIL_201112_0 , FILTER (@RANGE (3, 5));
map test.T_BILL_BALANCE_DETAIL_201112_1, target settle.T_BILL_BALANCE_DETAIL_201112_1 , FILTER (@RANGE (3, 5));
map test.T_BILL_BALANCE_DETAIL_201112_2, target settle.T_BILL_BALANCE_DETAIL_201112_2 , FILTER (@RANGE (3, 5));
map test.T_BILL_BALANCE_DETAIL_201112_3, target settle.T_BILL_BALANCE_DETAIL_201112_3 , FILTER (@RANGE (3, 5));
map test.T_BILL_BALANCE_DETAIL_201112_4, target settle.T_BILL_BALANCE_DETAIL_201112_4 , FILTER (@RANGE (3, 5));
REPLICAT rep4
USERID goldengate,PASSWORD goldengate
ASSUMETARGETDEFS
REPERROR (default,abend)
DBOPTIONS LIMITROWS DEFERREFCONST
BATCHSQL BATCHESPERQUEUE 1000, BATCHTRANSOPS 2000 ,OPSPERBATCH 6000 , OPSPERQUEUE 6000
GROUPTRANSOPS 20000
DDL exclude all
map test.T_BILL_BALANCE_DETAIL_201112_0, target settle.T_BILL_BALANCE_DETAIL_201112_0 , FILTER (@RANGE (4, 5));
map test.T_BILL_BALANCE_DETAIL_201112_1, target settle.T_BILL_BALANCE_DETAIL_201112_1 , FILTER (@RANGE (4, 5));
map test.T_BILL_BALANCE_DETAIL_201112_2, target settle.T_BILL_BALANCE_DETAIL_201112_2 , FILTER (@RANGE (4, 5));
map test.T_BILL_BALANCE_DETAIL_201112_3, target settle.T_BILL_BALANCE_DETAIL_201112_3 , FILTER (@RANGE (4, 5));
map test.T_BILL_BALANCE_DETAIL_201112_4, target settle.T_BILL_BALANCE_DETAIL_201112_4 , FILTER (@RANGE (4, 5));
REPLICAT rep5
USERID goldengate,PASSWORD goldengate
ASSUMETARGETDEFS
REPERROR (default,abend)
DBOPTIONS LIMITROWS DEFERREFCONST
BATCHSQL BATCHESPERQUEUE 1000, BATCHTRANSOPS 2000 ,OPSPERBATCH 6000 , OPSPERQUEUE 6000
GROUPTRANSOPS 20000
DDL exclude all
map test.T_BILL_BALANCE_DETAIL_201112_0, target settle.T_BILL_BALANCE_DETAIL_201112_0 , FILTER (@RANGE (5, 5));
map test.T_BILL_BALANCE_DETAIL_201112_1, target settle.T_BILL_BALANCE_DETAIL_201112_1 , FILTER (@RANGE (5, 5));
map test.T_BILL_BALANCE_DETAIL_201112_2, target settle.T_BILL_BALANCE_DETAIL_201112_2 , FILTER (@RANGE (5, 5));
map test.T_BILL_BALANCE_DETAIL_201112_3, target settle.T_BILL_BALANCE_DETAIL_201112_3 , FILTER (@RANGE (5, 5));
map test.T_BILL_BALANCE_DETAIL_201112_4, target settle.T_BILL_BALANCE_DETAIL_201112_4 , FILTER (@RANGE (5, 5));
mysql 数据库的replicat
replicat rep1
DBOPTIONS HOST 127.0.0.1, CONNECTIONPORT 3306
TARGETDB ogg,userid goldengate,password goldengate
SOURCEDEFS /home/oracle/ogg/mysql/dirdef/record.def
reperror (default,abend)
MAP TEST.TESTSQL, TARGET OGG.TESTSQL;
MAP TEST.T_BILL_BALANCE_DETAIL_201112_9, TARGET OGG.T_BILL_BALANCE_DETAIL_201112_9 , FILTER (@RANGE (1, 5));
replicat rep2
DBOPTIONS HOST 127.0.0.1, CONNECTIONPORT 3306
TARGETDB ogg,userid goldengate,password goldengate
SOURCEDEFS /home/oracle/ogg/mysql/dirdef/record.def
reperror (default,abend)
MAP TEST.T_BILL_BALANCE_DETAIL_201112_9, TARGET OGG.T_BILL_BALANCE_DETAIL_201112_9 , FILTER (@RANGE (2, 5));
replicat rep3
DBOPTIONS HOST 127.0.0.1, CONNECTIONPORT 3306
TARGETDB ogg,userid goldengate,password goldengate
SOURCEDEFS /home/oracle/ogg/mysql/dirdef/record.def
reperror (default,abend)
MAP TEST.T_BILL_BALANCE_DETAIL_201112_9, TARGET OGG.T_BILL_BALANCE_DETAIL_201112_9 , FILTER (@RANGE (3, 5));
replicat rep4
DBOPTIONS HOST 127.0.0.1, CONNECTIONPORT 3306
TARGETDB ogg,userid goldengate,password goldengate
SOURCEDEFS /home/oracle/ogg/mysql/dirdef/record.def
reperror (default,abend)
MAP TEST.T_BILL_BALANCE_DETAIL_201112_9, TARGET OGG.T_BILL_BALANCE_DETAIL_201112_9 , FILTER (@RANGE (4, 5));

ALLSTARS_ORACLE

Scale Extract

Split heavy table into its own extract
Split one table into many extracts using @RANGE()
Use this as your last resort


Scale data pump


Use PASSTHRU

Only send tables needed


Scale replicat

Split a table into its own replicat

Stop
extseqno, extrba
new parameter file, MAP the isolated table only
Remove from the original parameter file
Add new replicat … extseqno … extrba …
Start

Fung920

如果在repa进程中已经在同步的表，我需要加入到repb进程中，应该怎么操作呢？

hhitd2008

thanks a lot!