improve speed of mysql import

Doing a dump and restore in the manner described will mean MySQL has to completely rebuild indexes as the data is imported. It also has to parse the data each time.

It would be much more efficient if you could copy data files in a format MySQL already understands. A good way of doing this is to use innobackupex from Percona

(Open Source and distributed as part of XtraBackup available to download from here).

This will take a snapshot of MyISAM tables, and for InnoDB tables it will copy the underlying files, then replay the transaction log against them to ensure a consistent state. It can do this from a live server with no downtime (I have no idea if that is a requirement of yours?)

I suggest you read the documentation, but to take a backup in it’s simplest form use:

$ innobackupex --user=DBUSER --password=DBUSERPASS /path/to/BACKUP-DIR/
$ innobackupex --apply-log /path/to/BACKUP-DIR/

If the data is on the same machine, then innobackupex even has a simple restore command:

$ innobackupex --copy-back /path/to/BACKUP-DIR

There are many more options and different ways of actually doing the backup so I would really encourage you have a good read of the documentation before you begin.

For reference to speed, our slow test server, which does about 600 IOPS can restore a 500 GB backup in about 4 hours using this method.

Lastly: You mentioned what could be done to speed up importing. It’s mostly going to depend on what the bottle neck is. Typically, import operations are I/O bound (you can test this by checking for io waits) and the way to speed that up is with faster disk throughput – either faster disks themselves, or more of them in unison.

Leave a Comment