Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a 350MB msyql dump file that takes over 3 minutes to load. I'd like to speed it up. I've tried using --opt with mysqldump (shaved off 30 seconds). The best I can achieve is 42 seconds faster. Any other techniques to speed up a mysql restore from a dump file?

share|improve this question
    
Fundamentally, restore time depends on how fast your server can handle a single thread executing (usually bulk) inserts, but other questions occur to me: How many tables and how many databases are contained in the file? Are the tables MyISAM or InnoDB or something else? Is the restore also overwriting existing tables? Have you considered how much time dropping those tables contributes to the restore? What version of MySQL Server are you using? Are you sure --opt actually changed anything and the timing wasn't coincidental... since --opt is typically already on by default? –  Michael - sqlbot Jul 24 '13 at 1:37
    
Not sure if this is already in your mysqldump file: DISABLE KEYS beforehand, then ENABLE KEYS afterwards is much faster than index creation at insertation time. –  0xCAFEBABE Jul 24 '13 at 7:25
    
MySQL version is 5.5.31 (dist from Oracle). We're using all InnoDB tables on this database. Yes, it does overwrite tables if they exist. I've tried --disable-keys on the mysqlump side of the script but really did not get much back in return. –  dperry1973 Jul 24 '13 at 19:55

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.