Forums

[SOLVED: mysqldump + mysql < is much faster than LOAD DATA INFILE. 56 mins vs 30hrs]

What steps would you suggest in order to bulk load a fairly large table? (115M+ rows; 8G+ footprint)

• ex1: start with a myIsam engine, disable keys, load infile, enable keys, alter engine to innoDB
• ex2: mysqldump locally, zip and upload to PA, mysql <
• ex3. create the innodb on PA, make a copy of the local table.idb, zip and upload, ask PA staff to copy to the proper directory. issue ALTER TABLE tbl_name IMPORT TABLESPACE

A naive LOAD DATA INFILE on PA takes a day to run. Locally, it completes in an hour

Hmm, I'd say that #2 is probably the safest option, but #1 should work too; the slowdown vs local is probably because when you run it on your own machine, the MySQL server is on the same machine as the server where you're running the import -- so there's no network latency to take into account.

I don't think we could do #3 -- the database servers we run are AWS RDS instances, so we have no access to the raw filesystem.

thanks for your time.

I'll eventually try 3 approaches and report times. On my machine and on PA.

1. straight to innoDB, with raw data sorted on the primary key.
2. myIsam, disable keys, load, enable keys, alter engine
3. mysqldump, mysql <

take care

[SOLVED -- the database is not dropped, unless the mysqldump script calls for it]

One more thing -- your blog entry on backup/restore warns that restoring will wipe out the pre-existing database.

I assume that if the mysqldump command was issued for a single table, the other pre-existing tables will not be affected. (after all, mysqldump generates a script, in which specific tables are dropped/recreated/repopulated)

Right?

That's correct. If the mysqldump script does not have the drop instructions for a table, then the table will not be dropped.