MySQL – How to Import a Huge SQL Dump Faster?

backupinnodbMySQLmysqldump

I have this huge 32 GB SQL dump that I need to import into MySQL. I haven't had to import such a huge SQL dump before. I did the usual:

mysql -uroot dbname < dbname.sql

It is taking too long. There is a table with around 300 million rows, it's gotten to 1.5 million in around 3 hours. So, it seems that the whole thing would take 600 hours (that's 24 days) and is impractical. So my question is, is there a faster way to do this?

Further Info/Findings

The tables are all InnoDB and there are no foreign keys defined. There are, however, many indexes.
I do not have access to the original server and DB so I cannot make a new back up or do a "hot" copy etc.
Setting innodb_flush_log_at_trx_commit = 2 as suggested here seems to make no (clearly visible/exponential) improvement.
Server stats during the import (from MySQL Workbench): https://imgflip.com/gif/ed0c8.
MySQL version is 5.6.20 community.
innodb_buffer_pool_size = 16M and innodb_log_buffer_size = 8M. Do I need to increase these?

Best Answer

Percona's Vadim Tkachenko made this fine Pictorial Representation of InnoDB

InnoDB Architecture

You definitely need to change the following

innodb_buffer_pool_size = 4G
innodb_log_buffer_size = 256M
innodb_log_file_size = 1G
innodb_write_io_threads = 16
innodb_flush_log_at_trx_commit = 0

Why these settings ?

innodb_buffer_pool_size will cache frequently read data
innodb_log_buffer_size : Larger buffer reduces write I/O to Transaction Logs
innodb_log_file_size : Larger log file reduces checkpointing and write I/O
innodb_write_io_threads : Service Write Operations to .ibd files. According to MySQL Documentation on Configuring the Number of Background InnoDB I/O Threads, each thread can handle up to 256 pending I/O requests. Default for MySQL is 4, 8 for Percona Server. Max is 64.
innodb_flush_log_at_trx_commit
- In the event of a crash, both 0 and 2 can lose once second of data.
- The tradeoff is that both 0 and 2 increase write performance.
- I choose 0 over 2 because 0 flushes the InnoDB Log Buffer to the Transaction Logs (ib_logfile0, ib_logfile1) once per second, with or without a commit. Setting 2 flushes the InnoDB Log Buffer only on commit. There are other advantages to setting 0 mentioned by @jynus, a former Percona instructor.

Restart mysql like this

service mysql restart --innodb-doublewrite=0

This disables the InnoDB Double Write Buffer

Import your data. When done, restart mysql normally

service mysql restart

This reenables the InnoDB Double Write Buffer

Give it a Try !!!

SIDE NOTE : You should upgrade to 5.6.21 for latest security patches.

Related Solutions

MySQL Replication: Insert generating VERY huge temporary tables

Algorithmically, it is doing what your colleague says. But, do you see what it is doing ???

It is generating 10,000 temp tables each containing 1 row after traversing 317 million rows through in the InnoDB internal index. Each temp table is a complete regeneration of the rowids in sernumbers_results_2009 table along with executing handler_read_prev commands internally to sort the data by an index scan from the back of the internal rowid index. Also, please remember you are dealing with InnoDB. Who knows what Multiversioning (via MVCC) is going on so that the INSERT is completed without interference and with rollback capabilities.

Is there any reason why this query wouldn't work for you ???

INSERT INTO sernumbers_results_2009
SELECT * FROM sernumbers_results
ORDER BY rowid DESC LIMIT 10000;

This will definitely generate one temp table.

Give it a Try !!!

MySQL corruption even after full dump and re-import

Paolo, can you please provide the mysql error log? If one isn't available, you can add one by adding this line to your my.cnf file

[mysqld]

log-error=/var/log/mysql/mysql.err

One possible idea to narrow down your research.

You already know how to remove the ibdata and iblog files to create new ones. If you just want to test your mysql installation.

Remove the Innodb_force_recovery or comment it out
Backup your databases : mysqldump --all-databases -u root -p | gzip -9 > all_databases.sql.gz
Move your MySQL data directory somewhere else: mv /var/lib/mysql{,.bak}
Create a new MySQL data directory: mkdir /var/lib/mysql
Give rights to the MySQL user: chown -R mysql:mysql /var/lib/mysql
Start MySQL: /etc/init.d/mysql start
Run a tail -f on the mysql log you added to my.cnf: tail -f /var/log/mysql/mysql.err

If MySQL isn't running then something else on the system level isn't right. As Gopinath's link mentionned. Is there enough disk space: df -h and inodes: df -i? Is you file system healthy? A system reboot or using fsck on the disk partition where MySQL is installed, would detect and possibly fix file system problems.

If MySQL comes back, then you can restore your databases:

zcat all_databases.sql.gz | mysql -u root -p

Send us the logs after completing the steps I sent, and we'll take it from there.

Further Info/Findings

Best Answer

Related Solutions

MySQL Replication: Insert generating VERY huge temporary tables

MySQL corruption even after full dump and re-import

Related Question