Oracle CLOB to MySQL LONGTEXT poor performance #55
Description
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