How long should a 20GB restore take in MySQL? (A.k.a. Is something broken?)

I'm trying to build a dev copy of a production MySQL database by loading one of the backups. How long should it take to do this if the uncompressed dump is ~20G?

This comma开发者_开发知识库nd has been running for something like 24h with 10% CPU load and I'm wondering if it's just slow or if it/I am doing something wrong.

mysql -u root -p < it_mysql_dump.sql 

BTW it's on a beefy desktop dev machine with plenty of ram, but it might be reading and writing the same HDD. I think I'm using InnoDB.

Restoring MySQL dumps can take a long time. This is because it does really rebuild the entire tables.

Exactly what you need to do to fix it depends on the engine, but in general

I would say, do the following:

Zeroth rule: Only use a 64-bit OS.

1. Make sure that you have enough physical ram to fit the biggest single table into memory; include any overhead for the OS in this calculation (NB: On operating systems that use 4k pages i.e. all of them, the page tables take up a lot of memory themselves on large-memory systems - don't forget this)
2. Tune the innodb_buffer_pool such that it is bigger than the largest single table; or if using MyISAM, tune the key_buffer so that it is big enough to hold the indexes of the largest table.
3. Be patient.

Now, if you are still finding that it is slow having done the above, it may be that your particular database has a very tricky structure to restore.

Personally I've managed to rebuild a server with ~ 2Tb in < 48 hours, but that was a particular case.

Be sure that your development system has production-grade hardware if you intend to load production data into it.

In particular, if you think that you can bulk-load data into tables which don't fit into memory (or at least, mostly into memory), forget it.

If this all seems like too much, remember that you can just use a filesystem or LVM snapshot online with InnoDB, and then just copy the files. With MyISAM it's a bit trickier but can still be done.

Open another terminal, run mysql, and count the rows in some of the tables in your dump (SELECT COUNT(*) FROM table). Compare to the source database. That'll tell you the progress.

I INSERTed about 80GB of data into MySQL over a network in about 14 hours. They were one-insert-per-row dumps (slow) with a good bit of overhead, inserting on a server with fast disks.

24 hours is possible if the hardware is old enough, or your import is competing with something else for disk IO and memory.

I just went through the experience of restoring a 51.8 Gb database from a 36.8 Gb mysqldump file to create an imdb database. For me the restore which was not done over the network but was done from a file on the native machine took a little under 4 hours.

The machine is a Quad Core Server running Windows Server 2008. People have wondered if there is a way to monitor progress. There actually is. You can watch the restore create the database files by going to the Program Data directory and finding the MYSQL subdirectory and then finding the subdirectory with your database name.

The files are gradually built in the directory and you can watch them build up. No small comfort when you have a production issue and you are wondering if the restore job is hung up or just taking a long time.