37

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?

Marco Ramos
  • 3,120
  • 23
  • 25
Dave Forgac
  • 3,546
  • 7
  • 37
  • 48

7 Answers7

36

This link shows what one can do to speed up restoring process.

http://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb-bulk-data-loading.html

One can put put the commands at the top of the dump file

SET @OLD_AUTOCOMMIT=@@AUTOCOMMIT, AUTOCOMMIT = 0;
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS = 0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS = 0;

And put these statements at the end of the dump file

SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
SET AUTOCOMMIT = @OLD_AUTOCOMMIT;
COMMIT;

This worked for me. Happy restoring :-)

Leahkim
  • 175
  • 6
Dharmesh Tailor
  • 501
  • 4
  • 6
  • 12
    This helped quite a bit. Rather than edit the file, I created pre.sql and post.sql from the snippets above and used that to restore the db: `cat pre.sql dump.sql post.sql | mysql ...` – Jason R. Coombs Jul 01 '17 at 14:55
  • @JasonR.Coombs What if my dump.sql is a gzip file? – user2335065 Aug 24 '20 at 11:39
  • @user2335065 then you can do for example `cat pre.sql <(gzip -cd dump.sql.gz) post.sql | mysql ...` – sshow Sep 15 '20 at 21:29
35

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=0 and SET FOREIGN_KEY_CHECKS=1).

Keith
  • 4,637
  • 15
  • 25
Marco Ramos
  • 3,120
  • 23
  • 25
  • 2
    I found two things: the key_buffer_size was set to 8MB and there was one InnoDB table in the mix with foreign keys. Increased the key_buffer_size to 1GB and temporarily turned off foreign key checks. The restore finished in 5 minutes. Thanks! – Dave Forgac May 29 '10 at 19:05
  • wow! Glad it helped :) – Marco Ramos May 29 '10 at 19:11
  • 2
    I just noticed that I typed '5' minutes. I'm pretty sure it was more like 50 minutes but still way more reasonable ;-) – Dave Forgac Mar 19 '13 at 04:18
  • 6
    key_buffer_size is for MYISAM. – Fernando Fabreti Apr 03 '13 at 13:20
  • @FernandoFabreti - that's an important point for many readers, but the OP did specify they had mostly MyISAM – mc0e Jun 13 '17 at 16:57
  • Is there some configuration we can change when using an innodb storage engine to improve the performance of restoring indexes? – Kay Feb 10 '23 at 10:09
3

This will do:

mysql --init-command="SET SESSION FOREIGN_KEY_CHECKS=0;SET UNIQUE_CHECKS=0;" -u root -p < Backup_Database.mysql

Matiss
  • 31
  • 1
2

If you have the physical copy of the dump file (the DB directory), you can just copy it to the new server if the new server have the same MySQL version and it will work fine. This work fine with MyISAM and for me I think it is better than restoring the data based on the logical SQL dump file.

usef_ksa
  • 815
  • 4
  • 11
  • 16
1

if you have multiple tables chances are you might benefit from mk-parallel-restore.

pQd
  • 29,981
  • 6
  • 66
  • 109
1

The only reason I can image why the restore would gradually slow down is indexing. Investigate turning off indexing until the end and then let it do the whole lot at once.

John Gardeniers
  • 27,458
  • 12
  • 55
  • 109
0

I suggested you ,

  1. Check your tables: Do it has triggers? Clear all triggers
  2. SET: AUTOCOMMIT=0, UNIQUE_CHECKS=0, FOREIGN_KEY_CHECKS=0 (AND DON'T FORGET TO ROLLBACK THIS CHANGES)
  3. USE COMMAND LINE LIKE mysql -u root -pPasss requests < mydb.sql
  4. Check your database file size

Good Luck

masegaloeh
  • 18,236
  • 10
  • 57
  • 106
Ferhat KOÇER
  • 11