2

I am working on Data Transfer Utility which transfers data from Oracle to MySQL.

While moving data from Oracle to MySQL, the engine selected for MySQL table is MyISAM which speedily inserts data. If I change Engine type to InnoDB it takes much time to insert as Innodb follows ACID rules.

How can I achieve the best performance by InnoDB engine? I also want to add Foreign Key to table before inserting data into table. With MyISAM engine this is not possible.

I have tested the below scenario,

With Engine type MyISAM 50 lac data moved in 2 hrs but unable to add foreign key With Engine type InnoDB 50 lac data moved in 20 hrs approx.

Please help me to get performance with Innodb Engine type. I am using MySQL 5.1.6 version working with all the default settings.

Ispirer SQLWays Migrations
  • 1,028
  • 9
  • 16
user1760986
  • 69
  • 1
  • 2
  • 8
  • 1
    Maybe creating tables without referential integrity, then inserting data and ten altering for referential integrity helps. This way during insert no reference control will be required. Of course I'm assuming your data on Oracle is consistent in terms of referential integrity. – Ertunç Oct 20 '12 at 08:53
  • hi,yes my data in Oracle is consistent but problem occurs when data is not consistent i want to handle such situation. – user1760986 Oct 20 '12 at 10:29
  • So does this mean you are required to run data transfer operation on tables with existing referential integrity constraints while importing into mysql? – Ertunç Oct 20 '12 at 10:40
  • yes i want to run datat trasnfer operation on tables with existing referential integrity constraints. – user1760986 Oct 20 '12 at 12:17
  • Then it would have an effect on performance on data pump. Are you also creating tables with indexes first then inserting data? – Ertunç Oct 20 '12 at 12:27
  • yes i am creating indexes before inserting data,also i have tried without creating index but it wouldn't help.still data insertion is slow. – user1760986 Oct 20 '12 at 14:22
  • Importing without the indexes should not be slow. Maybe your server resources are not enough for this kind of operation. – Ertunç Oct 20 '12 at 14:39
  • hello i am using system which has 4 gb ram,2.2ghz processor is there any need to add more RAM or to make some cofiguration settings in mysql as i am using defualt setting.Thank you for your help. – user1760986 Oct 20 '12 at 14:43
  • Is this a one time operation? Or are you trying to develop an application that performs this kind of task? Since you index data on insertion your bottlenecks will be your cpu freq (family is important), and your hard disk. You may utilize on some configuration, but it is not the kind of machine I would dare to engage this data operation. – Ertunç Oct 20 '12 at 15:10
  • yes i am trying to develop this kind of application that moves data from oracle,MS SQL SERVER. – user1760986 Oct 21 '12 at 03:28
  • Maybe you should paste some code, mention programming language that you are using. I hope you're comitting records in batches in transactions not inserting row by row . – Ertunç Oct 21 '12 at 06:35
  • i am using vb.net and i am using "insert all" query to insert records in batches – user1760986 Oct 22 '12 at 02:57

0 Answers0