Higher speed options for executing very large (20 GB) .sql file in MySQL

Question

My firm was delivered a 20+ GB .sql file in reponse to a request for data from the gov't. I don't have many options for getting the data in a different format, so I need options for how to import it in a reasonable amount of time. I'm running it on a high end server (Win 2008 64bit, MySQL 5.1) using Navicat's batch execution tool. It's been running for 14 hours and shows no signs of being near completion.

Does anyone know of any higher speed options for such a transaction? Or is this what I should expect given the large file size?

Thanks

Answer 1

Use BULK Import in MySQL for this.

Answer 2

You could follow some of the advice provided as answer to a similar question.

Answer 3

I guess you mean it's a file produced by mysqldump as a backup of a database, so it contains mostly CREATE TABLE and INSERT statements.

(But strictly speaking, an SQL script can contain anything, such as definition and execution of long-running stored procedures, queries that result in deadlocks, etc. I'll assume this is not the case.)

Here are some things you can do to speed up restore, given that you have the backup file and can't change the type of file it is:

1. Disable foreign key checks: SET FOREIGN_KEY_CHECKS=0 (remember to re-enable afterwards). Disable unique checks too: SET UNIQUE_CHECKS=0

2. Make sure your key_buffer_size is set as large as possible if you use MyISAM tables. The default is 8MB, and the max is 4GB. I'd try 1GB.

   These first tips come from a post by Baron Schwartz: http://lists.mysql.com/mysql/206866

3. Make sure your innodb_buffer_pool_size is set as large as possible if you use InnoDB tables. The default is 8MB, and the max is 4GB. I'd try 1GB.

4. Set innodb_flush_log_at_trx_commit = 2 during the restore if you use InnoDB tables.

5. @Mark B adds a good suggestion below to disable keys during a restore. This is how you do it:

   ALTER TABLE <table-name> DISABLE KEYS;
   ...run your restore...
   ALTER TABLE <table-name> ENABLE KEYS;
   

   But that command affects only one table at a time. You'll have to issue a separate command for each table. That said, it's often the case that one table is much larger than the other tables, so you may need to disable keys only for that one large table.

   Also, if the SQL script containing your restore drops and recreates tables, this would circumvent disabling keys. You'll have to find some way to insert the commands to disable keys after the table is created and before rows are inserted. You may need to get creative with sed to preprocess the SQL script before feeding it to the mysql client.

Answer 4

Request just the table definitions, and the data in a .csv. Then do a bulk-import.