Problem with slow data migration speed.

[faqsarg]

Hello @darold,

I am using Ora2Pg v24.0 to perform a migration of the data of all my tables in the Oracle database. I already have my postgres with the tables and the database structure ready.

I was running the tool in docker at first, but migration must be completed within 30 hours so I am trying to speed it up as much as possible.

Now I have Ora2Pg installed directly on the operative system, and I am using the same ora2pg.conf that I was using with docker. The problem I see is that migration is too slow on certain tables, and I don't see any improvement over the dockerized version.

I want to provide you as much information as I can, so maybe you can see the problem.

First of all, I am running ora2pg in Red Hat Enterprise Linux Server release 7.9 (Maipo)

This is the output of lscpu: Architecture: x86_64 CPU op-mode(s): 32-bit, 64-bit Byte Order: Little Endian CPU(s): 4 On-line CPU(s) list: 0-3 Thread(s) per core: 2 Core(s) per socket: 2 Socket(s): 1 NUMA node(s): 1 Vendor ID: AuthenticAMD CPU family: 25 Model: 1 Model name: AMD EPYC 7763 64-Core Processor Stepping: 1 CPU MHz: 2445.426 ...

This server has 64GB memory.

Some of directives that I am using in the ora2pg.conf file are the following:

ORACLE_DSN dbi:Oracle:host=xxx;sid=xxx;port=xxx ORACLE_USER xxx ORACLE_PWD xxx USER_GRANTS 1 DEBUG 1 EXPORT_SCHEMA 0 TYPE COPY EXCLUDE xxx xxx xxx xxx xxx (I am exluding 5 tables that I don't need now) EXTERNAL_TO_FDW 1 TRUNCATE_TABLE 0 USE_TABLESPACE 0 REORDERING_COLUMNS 0 CONTEXT_AS_TRGM 0 FTS_INDEX_ONLY 1 USE_UNACCENT 0 USE_LOWER_UNACCENT 0 DATADIFF 0 PG_DSN dbi:Pg:dbname=xxx;host=xxx;port=xxx PG_USER xxx PG_PWD xxx PG_VERSION 14 OUTPUT output.sql DATA_LIMIT 10000 BLOB_LIMIT 100 TRANSACTION readonly NO_LOB_LOCATOR 1 LOG_ON_ERROR 1 JOBS 4 ORACLE_COPIES 1 PARALLEL_TABLES 2 PLSQL_PGSQL 1

Unfortunately, I think I can not use ORACLE_COPIES since all our primary keys are defined as VARCHAR2. I read that this could improve the speed but well, I think there is no solution for this. Maybe I could just delete the ORACLE_COPIES from the config file.

I have also changed the TRANSACTION directive to readonly, because I faced ORA-01555 Snapshot Too Old error. This change fixed the error, but I don't know if it could lead to a slower migration.

I tried to export data from some tables increasing the DATA_LIMIT to 30000, but didn't see any improvement with that.

I don't know if JOBS and PARALLEL_TABLES are well configured according to the specs of the machine.

Something I don't understand is that I ran yesterday a full db migration, using the dockerized ora2pg with all the configuration you see above. I want to show you the speed of 2 different tables according to the stdout:

ora2pg | [2023-09-19 16:55:01] Extracted records from table AD_ALERT: total_records = 210000 (avg: 7241 recs/sec)

ora2pg | [2023-09-19 17:06:23] Extracted records from table C_ORDER: total_records = 170000 (avg: 871 recs/sec)

And this is now running ora2pg in the operative system with the exact same configuration file:

[2023-09-20 15:06:14] Extracted records from table AD_ALERT: total_records = 210000 (avg: 14000 recs/sec)

[2023-09-20 15:44:49] Extracted records from table C_ORDER: total_records = 170000 (avg: 108 recs/sec)

Both process were started using: docker compose up > migration.log ora2pg -c ora2pg.conf > migration.log Is it possible that the > operator is making the process a bit slower? I know that maybe I don't need this since I can get logs using some of the config file directives.

But as you can see, C_ORDER table is making the migration really slow now. This is the description of C_ORDER table:

271 columns 1 CLOB type

It also happens to me in C_ORDERLINE table, which has 2 CLOB columns (around 100 rec/sec)

When I run top command, I always see something like this:

%Cpu(s): 0,2 us, 0,1 sy, 0,0 ni, 99,5 id, 0,1 wa, 0,0 hi, 0,1 si, 0,0 st KiB Mem : 65807780 total, 1909340 free, 3342500 used, 60555940 buff/cache KiB Swap: 0 total, 0 free, 0 used. 42116052 avail Mem

and while I keep monitoring, sometimes I see 2 processes ora2pg - sending and ora2pg - querying, that apparently consumes 90% and 65% of CPU respectively, but suddenly dissapears. The %Cpu(s) value at the top remains almost always at the same: 0,2 us. Sometimes it can go to 30% or more but it just last a second.

Is it normal to have this migration speed? Can you see anything wrong in my configuration? May the logs directive in the configuration file affect the migration speed?

Sorry for this really long text but I just want you to have everything you need to know in order to detect the problem. I look forward to hearing from you.

Best regards.

UPDATE: The last migration (using ora2pg installed directly in the server instead of dockerized) took almost x2 of the time it took using the dockerized version. I don't know why is this happening.

[darold]

Hi,

Directive NO_LOB_LOCATOR is obsolete and is replaced by USE_LOB_LOCATOR. You should see the configuration file at NO_LOB_LOCATOR and LOB_CHUNK_SIZE. But the problem you are facing might be the time used by ora2Pg to process the value of the 271 columns. If you can't parallelize due to varchar PK you should consider using the oracle_fdw to import your data to PostgreSQL. You just have to install oracle_fdw and set FDW_SERVER in ora2Pg.conf, Ora2Pg will do the other job for you.

Best regards,

[faqsarg]

I understand. However, I think it is strange that C_ORDER goes almost 7 times slower comparing to running it with docker. I thought that maybe the multiprocess directives were not set correctly according to the server resources.

If you see that JOBS and PARALLEL_TABLES are correct, I will pray for oracle_fdw to speed up this process.

I will try to install it as soon as possible and come back to you with the news.

Thank you very much for the response.

[faqsarg]

Hello @darold,

I have installed oracle_fdw in the postgres server, and ran again the migration. stdout changed, and now I am not able to see the recs/sec of each table. Is there a way to find this information?

I want to compare the time alll data is inserted into the table, but with this new stdout that I get when using FDW_SERVER directive I just see something like:

Exporting data of table XXX using foreign table... Exporting foreign table data for XXX using query: ...

As far as I could see, once this "Exporting foreign table data for XXX ..." finishes and a new log of the next table appears, sometimes it happens that at that instant the records are NOT loaded in the PostgreSQL database, but when I do a select statement some time later, finally this table is loaded with the data.

Is there any way I can keep track of the insertion times for the tables?

It is the first time I use oracle_fdw and I don't know how I can achieve this.

EDIT: I also don't see much resources usage on the PostgreSQL machine. Just 5% CPU usage and just 1GB of 64GB of RAM. Is this normal?

[faqsarg]

Hello @darold,

After some testing, it seems that in our case oracle_fdw doesn't improve much. I'm trying to figure out a way to make this migration possible and the following idea came to my mind: is it possible to set up a new server with our Oracle database, and migrate from both Oracle servers to the Postgres server? i.e. migrate with the first server half of the data, and with the second server migrate the other half simultaneously? is this possible or is it a stupid idea?

[darold]

Sorry for the late response. You can always run multiple instances of Ora2Pg with different configuration, so yes, this is possible.