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.

Make sure you increase your "max_allowed_packet" variable to a large enough size. This will really help if you have a lot of text data. Using high performance hardware will surely improve the speed of importing data.

mysql --max_allowed_packet=256M -u root -p < "database-file.sql"

One thing you can do is

SET AUTOCOMMIT = 0; SET FOREIGN_KEY_CHECKS=0

And you can also play with the values

innodb_buffer_pool_size
innodb_additional_mem_pool_size
innodb_flush_method

in my.cnf to get you going but in general you should have a look at the rest of innodb parameters as well to see what best suits you.

This is a problem I have had in the past I don't feel I have tackled completely but I hope I had pointed myself in this direction from the get go. Would have saved myself quite some time.

There are a lot of parameters that are missing, to fully understand the reason for the problem. such as:

1. MySQL version
2. Disk type and speed
3. Free memory on the server before you start MySQL server
4. iostat output before and at the time of the mysqldump.
5. What are the parameters that you use to create the dump file in the first place.

and many more.

So I'll try to guess that your problem is in the disks because I have 150 instances of MySQL that I manage with 3TB of data on one of them, and usually the disk is the problem

Now to the solution:

First of all - your MySQL is not configured for best performance.

You can read about the most important settings to configure at Percona blog post: http://www.percona.com/blog/2014/01/28/10-mysql-settings-to-tune-after-installation/

Especially check the parameters:

innodb_buffer_pool_size 
innodb_flush_log_at_trx_commit
innodb_flush_method

If your problem is the disk - reading the file from the same drive - is making the problem worse.

And if your MySQL server starting to swap because it does not have enough RAM available - your problem becomes even bigger.

You need to run diagnostics on your machine before and at the time of the restore procedure to figure that out.

Furthermore, I can suggest you to use another technic to perform the rebuild task, which works faster than mysqldump.

It is Percona Xtrabackup - http://www.percona.com/doc/percona-xtrabackup/2.2/

You will need to create the backup with it, and restore from it, or rebuild from running server directly with streaming option.

Also, MySQL version starting from 5.5 - InnoDB performs faster than MyISAM. Consider changing all your tables to it.