Performance Improvements in EDB Migration Toolkit 52.0.3

Contributed by Muhammad Ikram 

Migration Toolkit Overview:

The EDB Postgres Migration Toolkit (MTK) is a powerful command-line tool that offers granular control of the process of migrating of database objects and data from other database management systems to EDB Postgres Advanced Server and PostgreSQL.

MTK supports migration of the following databases:

• Oracle® Database
• SQL Server
• MySQL
• Sybase
• PostgreSQL

Learn more about the performance improvements in the EDB Migration Toolkit 52.0.3 below. For more information, see EDB Postgres™ Migration Toolkit Guide.

Migrating TEXT data types from MySQL to Advanced Server

Enhancement

Earlier this release, MySQL’s TEXT, TINYTEXT, MEDIUMTEXT, and LONGTEXT data types were mapped to Advanced Server’s CLOB data type. Migrating CLOB data is a time taking process as MTK migrates one row at a time, by default. As the TEXT family data types already exist in Postgres and  Advanced Server, with this release, MySQL’s TEXT type columns are migrated to Advanced Server’s TEXT type columns. This helped in improving the overall data migration performance.

Example: Data migration from MySQL to Advanced Server

Migration Process

Migrate schema only

./runMTK.sh -sourcedbtype mysql -targetdbtype enterprisedb -tables data -schemaOnly testdb

Migrate Data

Command used for migrating data using MTK 51.0.0 and MTK 52.0.3

./runMTK.sh -sourcedbtype mysql -targetdbtype enterprisedb -tables data -dataOnly  testdb

Conclusion:

After mapping the MySQL’s TEXT family data types to the respective Advanced Server TEXT family data types instead of CLOB, the performance has improved by 98% on an average.

Migrating data from Oracle to Advanced Server using fetchSize option

Starting this release, the overall data migration time from Oracle to Advanced Server improved substantially while fixing the fetchSize option related issue.

Note:

If you specify too large value for the fetchSize option, it may cause Out of Memory error.

Example:

Migration Process

Migrating table structure only

runMTK.sh -sourcedbtype oracle -targetdbtype enterprisedb  -schemaOnly -tables EMPLOYEES HRM

Migrating data using  -fetchSize option (with different values)

runMTK.sh -sourcedbtype oracle -targetdbtype enterprisedb  -dataOnly -fetchSize 10 -fastCopy -tables EMPLOYEES HRM

Conclusion

Using JDBC’s default fetchSize (Value for fetchSize option is not specified): 

While running MTK, if no value for fetchSize is specified, then MTK uses default JDBC fetchSize (10, in case of Oracle JDBC) and migrating hundred thousand rows takes around 40 minutes.

Using fetchSize option:

After using the appropriate fetchSize option, the performance has improved by 98% on an average. Note: You may not see further significant improvement post a certain fetchSize value.