cancel
Showing results for 
Search instead for 
Did you mean: 

Performance improvements in EDB Migration Toolkit 52.0.3

EDB Team Member

Migration Toolkit Overview Version 52.0.3

 

EDB’s 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 Advanced Server and PostgreSQL.

MTK supports migration of the following databases:

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

 

For more information, see EDB Postgres™ Migration Toolkit Guide.

 

Performance improved for the following:

  • Migrating TEXT data types from MySQL to Advanced Server
  • Migrating data from Oracle to Advanced Server using fetchSize option 

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

 

Environment Details (Number of rows: 1000072)

Destination

Database and Operating System

Source

MySQL 5.5.36 on Windows 2012 Server (64 bit) with 4 GB RAM and 2 CPU

Target

MTK and  Advanced Server 11.3.10 on CentOS 7 (64bit) with 4 GB RAM and 2 CPU



MySQL table structure before migration

 

Screen Shot 2019-06-28 at 3.21.41 PM.png

Migration Process

 

Migrate schema only

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

  

Table structure after migration using MTK 51.0.0

                     

Screen Shot 2019-06-28 at 3.22.43 PM.png

 

Table structure after migration using MTK 52.0.3

 

Screen Shot 2019-06-28 at 3.24.17 PM.png

 

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



Graphical analysis

 

Screen Shot 2019-06-28 at 3.25.09 PM.png

                                                

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 of value for the fetchSize option, it may cause Out of Memory error.

  

Example:

Environment (Number of rows: 100045)

Destination

Database & Operating System

Source

Oracle 11g R1 on CentOS 7 64 bit server with 4 GB RAM and 2 CPU

Target

MTK and Advanced Server on CentOS 7 64 bit server with 4 GB RAM and 2 CPU

 

 

Oracle table structure

 

Screen Shot 2019-06-28 at 3.26.05 PM.png 

Migration Process

 

Migrating table structure only

 

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

 

 

Advanced Server table structure after migration

 

Screen Shot 2019-06-28 at 3.26.56 PM.png 

Migrating data using  -fetchSize option (with different values)

 

 

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



Graphical analysis

 

Screen Shot 2019-06-28 at 3.27.58 PM.png

 

Conclusion

Using JDBC’s default fetchSize (Value for the 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 the fetchSize option:

After using the appropriate fetchSize option, the performance has improved by 98% on average.

Note: You may not see further significant improvement post a certain fetchSize value.