MySQL Import Performance – Troubleshooting Slow and Failing Large File Imports

etlMySQLpostgresql

I have a 47 GB MySQL dump of a single table:

http://dumps.wikimedia.org/commonswiki/latest/commonswiki-latest-image.sql.gz

I ultimately want it into PostgreSQL, but since I didn't figure out an easy way to transform the MySQL SQL to PostgreSQL SQL I figured, I will get it into MySQL and then write a small ETL script to do this.

I initially tried to just load it using MySQL WorkBench Data Import/Restore, but it was failing.

Now I've run split -l 2000 commonswiki-latest-image.sql and have 20 files of roughly 2 GB each, but it still fails with:

23:12:28 Restoring C:\temp\commonswiki\xaa.sql

Running: mysql.exe --defaults-extra-file="c:\users\jmurdoch\appdata\local\temp\tmpi_ltz8.cnf"  --host=127.0.0.1 --user=root --port=3306 --default-character-set=utf8 --comments --database=mediawikiimages < "C:\\temp\\commonswiki\\xaa.sql"

ERROR 2013 (HY000) at line 331: Lost connection to MySQL server during query


Operation failed with exitcode 1

00:54:17 Import of C:\temp\commonswiki\xaa.sql has finished with 1 errors

It's also terribly slow as it only imported 209236 rows during the nearly 2 hours, but I think there are about 20M items to import, so at that rate it would take 200 hours to import.

I'm using Windows 7, MySQL 5.6.14 and MySQL Workbench 6.

My main questions:

Is there a script which would feed MySQL with the splitted files and perform automatic error recovery in case it times out?
Is there a conversion tool which would convert MySQL SQL to PostgreSQL SQL without first loading it into a database?

Best Answer

Using

max_allowed_packet = 256M

in MySQL config allowed to import the files without errors.

Assuming you can't further increase the memory size then there are a couple of options.

If you aren't familiar with the command line and you really want to stick with a GUI style import then you can use BigDump (http://www.ozerov.de/bigdump/). I used it once but it's been a while. From what I remember you will download a file named bigdump.php (with some instructions) and put it on your webserver in the directory with the MySQL DB dump file that is too large to import via PHPMYADMIN. Then navigate to it using your browser - something like http://your-website.com/bigdump.php.
If you are familiar with the command line and using a Linux based system then you can use code like like this:

mysql -u USERNAME -p DATABASENAME < FILENAME.sql

The database and user (with privileges to the database) will need to exist prior to running this command though. Note I copied the above command from another source. I always do my dumps and restores like this: DUMP: mysqldump -u DB_USER -h DB_HOST -pDB_PASSWORD DB_NAME | gzip -9 > DB_NAME.sql.gz RESTORE: gunzip < PATH_TO_DUMP/DB_NAME.sql.gz | mysql -u DB_USER -pDB_PASSWORD DB_NAME

Finally, and most painfully, you could choose to dump individual tables or groups of tables into small enough dumps. Then restore these individual dumps one at a time via PHPMYADMIN.

FTP & RESTORE instructions:

Download FileZilla here: http://filezilla-project.org/download.php?type=client

Typically you can FTP into your server (especially a shared host) using the same login and password you would for SSH (you command line login). Just make sure to set up the connection to your server properly in FileZilla.

Once connected your default path will likely be your $HOME folder (on nix machines). If you aren't planning on doing a lot of FTPing just dump what you want in there. You can always move it from the command line.

If you FTP'd a MySQL Dump into your $HOME folder then the RESTORE command for a DB named mydb and a db dump file named mydb.sql.gz (in the $HOME folder) would be:

gunzip < $HOME/mydb.sql.gz | mysql -u DB_USER -pDB_PASSWORD mydb

where you would obviously need change the DB_USER and DB_PASSWORD values to what user and password you setup for the DB.

Remember:

The DB must already exist (but be empty) before the RESTORE command is executed
The DB_USER and DB_PASSWORD must be setup for the DB before the RESTORE command (you can do this from PHPMYADMIN - Privileges tab)
When you set up DB_USER from PHPMYADMIN it will default to only allowing DB_USER to access the DB from localhost (the server the DB is hosted on). I'm assuming this is fine for your situation grant. When you get into more advanced DUMPs and RESTOREs you may want to do it from another machine entirely which isn't hard but requires a couple more pieces of information be provided and DB_USER with the proper privileges.

FTP the DB and RESTORE it... Step by Step

Locate zipped MySQL dump file on your desktop (Example: C:\Web\DB\Dumps\mydb.sql.zip)
Download and install FileZilla
In FileZilla go to File->Site Manager and click on the New Site button (lower left quadrant of GUI). Set the host to a domain (Example: dest.com) on that resides on your DESTINATION server. Set Protocol to FTP or SFTP (this depends on your hosting, ask them). Set the User and Password (Example: User = grant, Password = grant's password) - if the FTP protocol is SFTP then any set of valid SSH login credentials should work.
(In FileZilla) Click Connect (bottom left most button)
(In FileZilla) Once connected to the remote server (should be your DESTINATION server) use the left side of FileZilla to navigate to the directory where the dump file you located in Step 1 resides. (The path for the local machine is on the far left, about half way down and it is labeled Local site:. In our example the textbox next to Local site: would read C:\Web\DB\Dumps.)
(In FileZilla) Locate the Remote site: label directly to the right of Local Site:.
(In FileZilla) There is no technical reason you can't put the DB DUMP in the folder tha FileZilla opens up to. It will likely default to your $HOME directory. If you don't have any reason to put the DB Dump in a specific directory - such as for your own organizational purposes - then leave the path on the Remote site alone. Write down that path (Linux Example: /usr/grant).
(In FileZilla) Locate the file on your local machine on the left half of FileZilla just under the Tree shown beneath the Local site: label you found in Step 5. Double click on that file and FileZilla will FTP it to your Remote Site.
Within PHPMYADMIN on your DESTINATION server, create a new database (type mydb into the Create new database textbox and Click the Create button).
(In PHPMYADMIN) Choose mydb from the menu on the left.
(In PHPMYADMIN) Click on the Privileges tab (upper right). Once on that page click on Add New User.
(In PHPMYADMIN->Privileges->Add New User) Provide a username (Example: mydb_grant) and password (Example: mydb_password). In the Host dropdown choose Local. Make sure that the radio button next to "Grant all privileges on database" is selected. Then click Go (bottom right of screen).
SSH into your DESTINATION server.
(In your SSH terminal) Navigate to the directory your wrote down in Step 7. Using a command like - cd $HOME or cd /usr/grant. (On Linux machines $HOME is an environment variable that will equal something like /usr/grant.)
(In your SSH terminal) Execute the following command: gunzip < /usr/grant/mydb.sql.gz | mysql -u mydb_grant -pmydb_password mydb (there is no space between -p and the password because that is the way it needs to be, that is not a typo).

Once that last command completes your DB has been RESTORED. I don't recommend using the DB user and password I provided simply replace them with more secure choices of your own.

DUMP and RESTORE on different servers without FTP

If you wish to skip the FTP stuff and don't mind redoing the DUMP then you can do it this way:

Login into the DESTINATION server (where you want the DB to reside - we'll call it destination.com)
Create the DB, User, and proper privileges as mentioned above on the DESTINATION server
Make sure the privileges on the SOURCE server allows for SOURCE_DB_USER to access the DB remotely - either allow access from dest.com or from % (% - everyone and everywhere if they have the right username and password)
On the DESTINATION server run: mysqldump -u SOURCE_DB_USER -h source.com -pSOURCE_DB_PASSWORD mydb| gzip -9 > $HOME/mydb.sql.gz where source.com is the SOURCE server (where the DB currently resides)
Then (on the DESTINATION server) run: gunzip < $HOME/mydb.sql.gz | mysql -u DEST_DB_USER -pDEST_DB_PASSWORD mydb

That should be all you need to do to move the DB from one server to another. Caveats are that I've only ever done this on my own dedicated Ubuntu (with standard LAMP) and Dreamhost's VPS servers (which I believe run CentOS but I could be wrong).

MySQL – How to Import a Huge SQL Dump Faster?

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.

Best Answer

Related Solutions

Mysql – importing large SQL file phptheadmin

Assuming you can't further increase the memory size then there are a couple of options.

FTP & RESTORE instructions:

FTP the DB and RESTORE it... Step by Step

DUMP and RESTORE on different servers without FTP

MySQL – How to Import a Huge SQL Dump Faster?

Related Question