digoal
2021-07-20
PostgreSQL , ora2pg , 性能 , oracle_fdw
One of such features is a faster data migration by copying multiple tables in parallel followed by parallel jobs to migrate each table. To optimize it to the best speed possible, the version 22.0 of Ora2Pg now supports the Foreign Data Wrapper, oracle_fdw, to increase the data migration speed. 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.
使用oracle_fdw插件, 同时开启并行迁移, 速度提升非常明显.
Here are the new results using Oracle data extraction on 4 connections (-J 4) :
$ ora2pg -t COPY -c config/ora2pg.conf -a TABLE_TEST -J 4
[========================>] 1/1 tables (100.0%) end of scanning.
NOTICE: user mapping for "gilles" already exists for server "orcl", skipping
[========================>] 1/1 tables (100.0%) end of table export.
NOTICE: drop cascades to foreign table ora2pg_fdw_import."TABLE_TEST"
[========================>] 100000000/100000000 total rows (100.0%) - (1250 sec., avg: 80000 recs/sec), TABLE_TEST
[========================>] 100000000/100000000 total rows (100.0%) - (1250 sec., avg: 80000 recs/sec), TABLE_TEST
[========================>] 100000000/100000000 total rows (100.0%) - (1250 sec., avg: 80000 recs/sec), TABLE_TEST
[========================>] 100000000/100000000 total rows (100.0%) - (1250 sec., avg: 80000 recs/sec), TABLE_TEST
[========================>] 100000000/100000000 rows (100.0%) on total estimated data (1250 sec., avg: 80000 tuples/sec)
So 80,000 tuples per seconds is far better. Of course with better hardware and 16 or 32 cores we could have a higher throughput.
Running the export the same way but without the use of oracle_fdw gives the following results.
ora2pg -t COPY -c config/ora2pg.conf -a TABLE_TEST -J 4 -j 2
[========================>] 1/1 tables (100.0%) end of scanning.
[======> ] 25000000/100000000 rows (25.0%) Table TABLE_TEST-part-0 (3032 sec., 8245 recs/sec)
[======> ] 25000000/100000000 rows (25.0%) Table TABLE_TEST-part-2 (3031 sec., 8248 recs/sec)
[======> ] 25000000/100000000 rows (25.0%) Table TABLE_TEST-part-3 (3031 sec., 8248 recs/sec)
[======> ] 25000000/100000000 rows (25.0%) Table TABLE_TEST-part-1 (3031 sec., 8248 recs/sec)
[========================>] 100000000/100000000 rows (100.0%) Table TABLE_TEST (3032 sec., 32981 recs/sec)
[========================>] 100000000/100000000 rows (100.0%) on total estimated data (3033 sec., avg: 32970 tuples/sec)
Ora2Pg parallel migration using 4 jobs but without oracle_fdw, migrates on an average of 32,970 tuples per second compared to 80,000 tuples/sec with oracle_fdw. So the performance gain is more than double.
https://ora2pg.darold.net/documentation.html
-j | --jobs num : Number of parallel process to send data to PostgreSQL.
-J | --copies num : Number of parallel connections to extract data from Oracle.
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
