CODEWITHSUNDEEP
mysql
Share Knowledge
Share Knowledge

Reputation: 297

Importing 10 GB SQL file

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 ?

Upvotes: 2

Views: 1358

Answers (1)

vhu
vhu

Reputation: 12818

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.

Upvotes: 3

Related Questions