I am restoring a 30GB database from a mysqldump file to an empty database on a new server. When running the SQL from the dump file, the restore starts very quickly and then starts to get slower and slower. Individual inserts are now taking 15+ seconds. The tables are mostly MyISAM with one small InnoDB. The server has no other active connections. SHOW PROCESSLIST; only shows the insert from the restore (and the show processlist itself).
Does anyone have any ideas what could be causing the dramatic slowdown?
Are there any MySQL variables that I can change to speed the restore while it is progressing?
Best Answer
One thing that may be slowing the process is the key_buffer_size, which is the size of the buffer used for index blocks. Tune this to at least 30% of your RAM or the re-indexing process will probably be too slow.
For reference, if you were using InnoDB and foreign keys, you could also disable foreign key checks and re-enable it at the end (using
SET FOREIGN_KEY_CHECKS=0andSET FOREIGN_KEY_CHECKS=1).