Ora2PG最新参数fdw_server能不能提高迁移速度？

前言

大家好，今天我们要使用最新版的ora2pg来测试一下迁移的效率。因为从v22版本开始，开始支持oracle_fdw来进行迁移，这会提高迁移速度，根据[Gilles Darold]
的测试结果，使用最新的oracle_fdw快一倍。性能提升还是很大的，至于有没有这种效果，我们还是拿真实的表在测试环境跑一下。

准备工作

ora2pg如何升级? ，官方文档暂时没看到upgade，我们就直接把目录铲掉，重新编译安装软件就行了。其他的那些perl的驱动包如DBD :: Oracle
，不需要再次安装。

[root@pgbackup01 ora2pg-22.0]# perl Makefile.PL[root@pgbackup01 ora2pg-22.0]# make && make install[root@pgbackup01 ora2pg]# ora2pg -versionOra2Pg v22.1

在Oracle侧，我们拿生产的1张大表(带两个clob字段)，在测试用户下创建一张一模一样表。

--带2个clob字段create table dpg.SALE_INT_EXTSYS_LOG_1003_test as select * from xsce.SALE_INT_EXTSYS_LOG_1003;--SALE_INT_EXTSYS_LOG_1003_test表大小，带2个LOB总共大小有23GB.SQL> select TABLE_NAME,SEGMENT_NAME from dba_lobs where TABLE_NAME='SALE_INT_EXTSYS_LOG_1003_TEST';TABLE_NAME                     SEGMENT_NAME------------------------------ ------------------------------SALE_INT_EXTSYS_LOG_1003_TEST  SYS_LOB0000455305C00004$$SALE_INT_EXTSYS_LOG_1003_TEST  SYS_LOB0000455305C00005$$SQL> select sum(bytes/1024/1024/1024) from dba_segments where segment_name in ('SALE_INT_EXTSYS_LOG_1003_TEST','SYS_LOB0000455305C00004$$','SYS_LOB0000455305C00005$$');SUM(BYTES/1024/1024/1024)-------------------------               23.3148193

表上创建唯一索引，因为我们要使用分片并行技术进行测试。

create unique index dpg.idx_LOG_ID_test on dpg.SALE_INT_EXTSYS_LOG_1003_test(LOG_ID);SQL> select count(1) from hbdx_zhaoyou.SALE_INT_EXTSYS_LOG_1003_test;  COUNT(1)----------   6739963

前期准备工作完成，接下来就在PostgreSQL中也创建一张同名的表，PostgreSQL这边为了快速插入，不创建索引。

LOB表测试

我们使用ora2pg测试参数如下:

ORACLE_HOME /data/instantclientORACLE_DSN dbi:Oracle:host=172.0.186.156;port=1521;service_name=xsceORACLE_USER  xsceORACLE_PWD  **********LOGFILE     /tmp/b1.logUSER_GRANTS    0SCHEMA    xsce DEFINED_PK sale_int_extsys_log_1003_test:log_idJOBS 4ORACLE_COPIES 8EXPORT_SCHEMA   0SKIP     fkeys pkeys ukeys indexes checksNLS_LANG      AMERICAN_AMERICA.UTF8PG_DSN    dbi:Pg:dbname=postgres;host=172.0.120.50;port=5432 PG_USER   rootPG_PWD    **********PG_SCHEMA publicLOG_ON_ERROR      0TRUNCATE_TABLE  1DISABLE_SEQUENCE  1PG_VERSION  12debug 1ALLOW sale_int_extsys_log_1003_test

这里我们使用ORACLE_COPIES
为8，在Oracle数据库上面开8个并发进程执行。同时还需要指定并发分片的字段，这里设置DEFINED_PK sale_int_extsys_log_1003_test:log_id
为刚刚我们建立的唯一索引字段就可以了。指定JOBS=4，在PostgreSQL中开4个进程入库。

[root@pgbackup01 ora2pg]# ora2pg -t COPY -c b1.conf -T /tmp/a1

调用起来之后，我们在Oracle查一下session会话，可以看到8个perl进程在执行抽取数据。

SQL> select machine,program,sql_id from v$session where machine like '%pgbackup01%';MACHINE                                                          PROGRAM                                          SQL_ID---------------------------------------------------------------- ------------------------------------------------ -------------pgbackup01                                                       perl@pgbackup01 (TNS V1-V3)                      0d23s3hqp9bvdpgbackup01                                                       perl@pgbackup01 (TNS V1-V3)                      0d23s3hqp9bvdpgbackup01                                                       perl@pgbackup01 (TNS V1-V3)                      0d23s3hqp9bvdpgbackup01                                                       perl@pgbackup01 (TNS V1-V3)                      0d23s3hqp9bvdpgbackup01                                                       perl@pgbackup01 (TNS V1-V3)                      0d23s3hqp9bvdpgbackup01                                                       perl@pgbackup01 (TNS V1-V3)                      0d23s3hqp9bvdpgbackup01                                                       perl@pgbackup01 (TNS V1-V3)                      0d23s3hqp9bvdpgbackup01                                                       perl@pgbackup01 (TNS V1-V3)                      0d23s3hqp9bvdpgbackup01                                                       perl@pgbackup01 (TNS V1-V3)

在PostgreSQL我们通过pg_stat_activity可以观察到4个进程在进行copy入库。

postgres=# select pid,application_name,substr(query,1,30) from pg_stat_activity;  pid  | application_name |             substr             -------+------------------+-------------------------------- 43295 | psql             | select pid,application_name,su 56115 | ora2pg           | TRUNCATE TABLE sale_int_extsys  7814 | ora2pg           |                               +       |                  | COPY sale_int_extsys_log_1003  7815 | ora2pg           |                               +       |                  | COPY sale_int_extsys_log_1003  7816 | ora2pg           |                               +       |                  | COPY sale_int_extsys_log_1003  7817 | ora2pg           |                               +       |                  | COPY sale_int_extsys_log_1003  7819 | ora2pg           | SET search_path = public,publi  7820 | ora2pg           | SET search_path = public,publi  7822 | ora2pg           | SET search_path = public,publi  7823 | ora2pg           | SET search_path = public,publi

导入完成后查看日志，速度大概是8542row/秒。

[2021-07-06 22:52:49] Total time to export data from 1 tables (0 partitions, 0 sub-partitions) and 6739963 total rows: 789 wallclock secs ( 0.27 usr  0.20 sys + 6462.72 cusr 209.64 csys = 6672.83 CPU)[2021-07-06 22:52:49] Speed average: 8542.41 rows/sec

现在我们使用最新的fdw_server
参数来进行测试，我们只需要在上面的参数文件中加入FDW_SERVER orcl
就行了，它会自动根据你设的oracle账号和密码创建oracle_fdw外部服务器，然后再创建ora2pg_fdw_import
这个schema，并且在这个schema下面创建外部表。这些都是全自动的。创建完成之后它就通过这个外部表进行insert入库。我们实际来观察一下。

[root@pgbackup01 ora2pg]# ora2pg -t COPY -c b1.conf -T /tmp/a1

再次执行观察Oracle数据库。

SQL> select machine,program,sql_id from v$session where machine='test-telepg-01';MACHINE                                                          PROGRAM                                          SQL_ID---------------------------------------------------------------- ------------------------------------------------ -------------test-pg-01                                                   postgres: root postgres xxx.0.117.5(10903) BIND@ ajz9gnm2bn6qxtest-pg-01                                                   postgres: root postgres xxx.0.117.5(10909) BIND@ djcw92n308f1btest-pg-01                                                   postgres: root postgres xxx.0.117.5(10907) BIND@ 05xrpn4s2yjbytest-pg-01                                                   postgres: root postgres xxx.0.117.5(10901) BIND@ dbrjxjdy8zfcctest-pg-01                                                   postgres: root postgres xxx.0.117.5(10911) BIND@ fzz930xxwkg1btest-pg-01                                                   postgres: root postgres xxx.0.117.5(10905) BIND@ bdqfpb8fxnu17test-pg-01                                                   postgres: root postgres xxx.0.117.5(10899) BIND@ 5y4w86pjfz6aatest-pg-01                                                   postgres: root postgres xxx.0.117.5(10897) BIND@ 9htv4n99ah496

Oracle里面，只有8个postgres进程在拉数据，但是奇怪的是这个ip地址是中间ora2pg的地址，并不是postgreSQL服务器的地址。

再次观察PostgreSQL数据库，这次变成了insert语句在并发插入。

postgres=# select pid,application_name,substr(query,1,50) from pg_stat_activity;   pid  | application_name |                       substr                       -------+------------------+---------------------------------------------------- 17954 | ora2pg           | TRUNCATE TABLE sale_int_extsys_log_1003_test; 17956 | ora2pg           | INSERT INTO sale_int_extsys_log_1003_test (log_id, 17957 | ora2pg           | INSERT INTO sale_int_extsys_log_1003_test (log_id, 17958 | ora2pg           | INSERT INTO sale_int_extsys_log_1003_test (log_id, 17959 | ora2pg           | INSERT INTO sale_int_extsys_log_1003_test (log_id, 17960 | ora2pg           | INSERT INTO sale_int_extsys_log_1003_test (log_id, 17961 | ora2pg           | INSERT INTO sale_int_extsys_log_1003_test (log_id, 17962 | ora2pg           | INSERT INTO sale_int_extsys_log_1003_test (log_id, 17963 | ora2pg           | INSERT INTO sale_int_extsys_log_1003_test (log_id,

这里的并发也是8个，并没有受到我设置的jobs=4的限制。因为你Oracle开8个select查询，PG肯定也要开8个对应进程insert。不然那就乱套了。

导入完成查看日志，速度没提升，反而下降了，相比之前，从8542rows/sec下降到了7694rows/sec。

[2021-07-06 23:14:38] Total time to export data from 1 tables (0 partitions, 0 sub-partitions) and 6739963 total rows: 876 wallclock secs ( 0.20 usr  0.16 sys +  0.14 cusr  0.10 csys =  0.60 CPU)[2021-07-06 23:14:38] Speed average: 7694.02 rows/sec

其实测试结果基本相当，没有太大的区别。那为什么[Gilles Darold]
博客的测试结果差距会大一倍呢？仔细的阅读了一下他写的文章，原文提到如下：

This is particularly useful for tables with BLOB because data needs a transformation to bytea that was known to be slow with Ora2Pg and faster with the C implementation in oracle_fdw.

这对于带有BLOB的表特别有用，因为数据需要转换为bytea，这在Ora2Pg中是缓慢的，而在oracle_fdw中的C的实现则更快。

所以最终原因是我的表是CLOB，转到PG里面是text，所以我测试下来效率是相当的，而它的表可能是BLOB要转成bytea。那么对于我们迁移来说，fdw_server
参数的作用就非常有局限性了。BLOB转成bytea的这种场景很少见。

后记

今天花时间测试了一下ora2pg最新的22.1版本的fdw_server
参数，详细测试结果在上面，有点失望。fdw_server
的作用有一些局限性。

参考文档:

https://www.migops.com/blog/2021/07/01/ora2pg-now-supports-oracle_fdw-to-increase-the-data-migration-speed/