Oracle CLOB to MySQL LONGTEXT poor performance

[sam-nla]

Migrating from Oracle to MySQL. Table contains CLOB data. Performance is very poor.

For 12Gb data, mostly CLOB, migration takes well over an hour. Performance is not predictable, sometimes it is much slower.

The tool displays extremely asymmetric read and write times:

     Rows read:     742680 (602336 rows/sec)
     Rows written:  734428 (1399 rows/sec, 1.7 GB, 3.4 MB/sec)
     Transfer time: 8 min 47 sec (1.2 sec read, 8 min 45 sec write)

Partial trace:

2020-02-13 08:57:48.699 OCI Fetch() Entered
2020-02-13 08:57:48.699 MySQL/C LOAD DATA INFILE Read callback() Entered, buffer size is 8192 bytes
2020-02-13 08:57:48.700 MySQL/C LOAD DATA INFILE Read callback() Waiting for data
2020-02-13 08:57:48.763 OCI Fetch() Left, retcode 0
2020-02-13 08:57:48.875 MySQL/C TransferRows() Entered
2020-02-13 08:57:48.876 MySQL/C LOAD DATA INFILE Read callback() Data arrived
2020-02-13 08:57:48.882 MySQL/C LOAD DATA INFILE Read callback() Left - middle of batch, 8192 bytes chunk, 1885681617 bytes all
2020-02-13 08:57:48.882 MySQL/C LOAD DATA INFILE Read callback() Entered, buffer size is 8192 bytes
2020-02-13 08:57:48.886 MySQL/C LOAD DATA INFILE Read callback() Left - middle of batch, 8192 bytes chunk, 1885689809 bytes all
2020-02-13 08:57:48.887 MySQL/C LOAD DATA INFILE Read callback() Entered, buffer size is 8192 bytes
2020-02-13 08:57:48.889 MySQL/C LOAD DATA INFILE Read callback() Left - middle of batch, 8192 bytes chunk, 1885698001 bytes all
2020-02-13 08:57:48.891 MySQL/C LOAD DATA INFILE Read callback() Entered, buffer size is 8192 bytes
2020-02-13 08:57:48.894 MySQL/C LOAD DATA INFILE Read callback() Left - middle of batch, 8192 bytes chunk, 1885706193 bytes all
2020-02-13 08:57:48.895 MySQL/C LOAD DATA INFILE Read callback() Entered, buffer size is 8192 bytes
2020-02-13 08:57:48.896 MySQL/C LOAD DATA INFILE Read callback() Left - middle of batch, 8192 bytes chunk, 1885714385 bytes all
2020-02-13 08:57:48.897 MySQL/C LOAD DATA INFILE Read callback() Entered, buffer size is 8192 bytes

[pmacdon]

Sam-Nla,

I’m sorry I can’t help with your issue as I am just learning how to use SQLines. I was wondering if you could help me though. I have a large Oracle dB I am trying to copy into MS SQL. I can get the table that do not have CLOB,BLOB, LONG or LONG RAW columns. But I cannot get any of the 100+ tables with those data types to transfer. Can you tell me if you used the command line option and if so, what did your file look like? I have tried through the GUI with all kinds of parameter settings. I have gotten it to crest the table in SQL, but never get any rows to cross. The error tells me it have an integer overflow.

Can you help?

Thanks in advance...

ptmacdon

[nbusseneau]

I'm a bit late to the party, but we successfully transferred more than 20GB of data from Oracle to MySQL, that were mostly stored in CLOB columns, in around 20 minutes (time stable over multiple retries).

We had split the migration in two sqldata phases in order to speed it up:

• One using -fetch_lob_as_varchar=yes (which "can increase performance by 3x - 10x") for tables with LOB smaller than 32K.
• The other one using -fetch_lob_as_varchar=no for tables with LOB larger than 32K.