2

I have 10 GB SQL file with millions of rows in 72 tables. My storage engine is innodb.

I have created the database with all required tables and issued the following command in MySQL prompt:

mysql> source path/to/my/sqlfile.sql

It would run great until it got to about 10% of the way. It would start fast and after a few minutes or later it slow down to three seconds per insert statement and it keeps slowing to 10 seconds per insert.

It has been running for three days now and 30% of data has been restored.

I can't figure why is it so slow ?

vhu
  • 12,244
  • 11
  • 38
  • 48
Share Knowledge
  • 297
  • 3
  • 18
  • Do you have foreign keys or constraints in your tables? – vhu Jul 09 '15 at 07:47
  • yes, the database is designed by very professionals database designers. and it has constraints and foreign keys on each table. – Share Knowledge Jul 09 '15 at 07:51
  • it looks like the best solution would be something like this `mysql -h yourhostname -u username -p databasename < yoursqlfile.sql ` but you also need to check what is wrong with the permissions and file limits – Jorge Y. C. Rodriguez Jul 09 '15 at 08:11
  • i have granted my self all permissions to do any kind of operation on the database. i am just working on my local machine. but what kinda of file limits which should i look at. where should i look? @jyr753 – Share Knowledge Jul 09 '15 at 08:18

1 Answers1

3

It's a good idea to start the load by disabling the foreign key checks to speed up the load:

mysql> SET foreign_key_checks = 0;
mysql> SOURCE dump_file_name;
mysql> SET foreign_key_checks = 1;

This is suggested by the manual to make loading easier (tables don't need to be loaded in order) and faster (foreign keys are not validated when row is inserted).

Additionally you may want to disable UNIQUE_CHECKS as well (SET UNIQUE_CHECKS=0).

Further optimization is rather difficult without seeing the DDL and data. There are several options you could try.

Community
  • 1
  • 1
vhu
  • 12,244
  • 11
  • 38
  • 48
  • Thanks @vhu i will try to do that. i will keep you informed. – Share Knowledge Jul 09 '15 at 08:21
  • Hi @vhu. i have changed my stored engine to Myisam. it's really faster than Innodb. i have manged to import about 3 gb of the data into my database. after following your suggestions and while i am watching the command prompt this is the result per execution at the very beginning (Query ok, 7788 rows affected <0.2> seconds) but now it executes very slow this the current result per execution (Query ok 7736 rows affected <40.05> seconds) per execution. any other suggestions i would be very thankful. – Share Knowledge Jul 09 '15 at 18:51