Import of 8GB mysql dump takes a long time

Question

I've got an 8GB MYSQL database dump of InnoDB tables created with mysqldump. I import the data with:

mysql -uroot -p my_db < dump.sql

An 5 GB dump of the DB was imported within an hour. The 8 GB dump takes 50 hour and counting. When I inspected the processlist with

SHOW PROCESSLIST;

Most of the time there was a single INSERT query visible with state "'freeing items"

Is there a way, besides copying the raw files, to speed up the import process?

Answer 1

The trick really, is to ensure that the biggest single table fits in the innodb buffer pool. If it does not, then inserts (and import of course) will be extremely slow.

It doesn't matter the size of the whole database, but the biggest single table.

For significantly larger databases, you might want to consider alternative methods of transfering the database, such as filesystem snapshots. This of course works best if your machines are running the same version of the database, OS and architecture.

Answer 2

How much memory does the machine have? My first guess would be the machine has 6gb or 8gb of memory, and mysql was able to keep the first dump completely in-memory but is somehow swapping hard-core on the second import. Can you run a vmstat 5 for a few iterations while doing the import and see how heavily the system is swapping?