2 replies to this topic

[Fighter]

I have a 25+ megabyte MySQL backup file and I need to restore it on multiple servers when required, this is fine although most servers take maybe an hour to restore each time of which they do not respond during so. I am using the InnoDB engine and most of the rows are small, but I have tens of thousands of them.

How would I speed up the process of restoring without modifying too many of the configurations of the MySQL setup itself?

Edited by Fighter, 01 January 2011 - 09:59 PM.
Added that it is InnoDB

[Alexander]

Hi, it could be possible you are low on memory and need to free some or it will lock.

I see you are using InnoDB, it by default commits each query on a per-key basis and can lead to unwanted resource usage, try turning autocommit off beforehand in console:

SET AUTOCOMMIT = 0;

If you are aware of foreign keys and are checking is not needed in your specific application, this could reduce a few thousand checks

SET FOREIGN_KEY_CHECKS = 0;

Please remember to commit the change and reset configuration once completed!

References of interest:
MySQL :: MySQL 5.0 Reference Manual :: 13.2.8 The InnoDB Transaction Model and Locking
MySQL :: MySQL 5.5 Reference Manual :: 13.6.5.4 FOREIGN KEY Constraints

Hope this helps!

[Fighter]

I couldn't reply for some reason, sorry.

Thank you for the help, I can't remember my computer speed if it took shorter time but it prevents it from locking, that is quite a big step :) Thanks.