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 don't normally work with MySQL but with MS-SQL and am having issues restoring a dump backup of a 9 GB database. I converted it to MS-SQL and it takes a grand total of 4 minutes to restore but the MySQL DB takes over an hour on the same server. The MySQL database is using InnoDB but is there an alternative to speeding up the restores? Both databases are on the same machine, Windows 2008R2 in a VM with a dymanic SANs.

Correction - it takes MS-SQL 1 minute to restore, 1 hour to restore the same database in MySQL

EDIT: mysql.ini (with commented lines removed):

[client]
no-beep
port=3306
[mysql]
default-character-set=utf8
[mysqld]
port=3306
basedir="C:\Program Files\MySQL\MySQL Server 5.5\"
datadir="C:\ProgramData\MySQL\MySQL Server 5.5\data\"
character-set-server=utf8
default-storage-engine=INNODB
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
log-output=NONE
general-log=0
general_log_file="CM999-SV510.log"
slow-query-log=0
slow_query_log_file="CM999-SV510-slow.log"
long_query_time=10
log-error="CM999-SV510.err"
max_connections=100
query_cache_size=0
table_cache=256
tmp_table_size=22M
thread_cache_size=8
myisam_max_sort_file_size=100G
myisam_sort_buffer_size=43M
key_buffer_size=8M
read_buffer_size=64K
read_rnd_buffer_size=256K
sort_buffer_size=256K
innodb_additional_mem_pool_size=4M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=2M
innodb_buffer_pool_size=124M
innodb_log_file_size=63M
innodb_thread_concurrency=9
share|improve this question
    
Please post your my.ini file for the MySQL Instance –  RolandoMySQLDBA Oct 5 '12 at 1:52
    
How did you convert to SQL Server? –  dezso Oct 5 '12 at 10:17
    
I created a linked server and copied the data in. Then I scripted out the indexes in MS-SQL –  user1207758 Oct 5 '12 at 14:41
1  
@user1207758 That way you try to compare two completely different systems. This will not yield too much useful information, even less about why the restore takes so long on MySQL. And a question: what options did you pass to mysqldump? –  dezso Oct 5 '12 at 19:28
    
How do you backup mysql ? Do you use mysqldump? Are you running mysqldump of all database to a single file ? –  RolandoMySQLDBA Jun 3 '13 at 18:59
add comment

1 Answer 1

Try adding this setting to your configuration:

innodb_flush_log_at_trx_commit=0
share|improve this answer
1  
Why do you think this is a good recommendation? What does it do that it improves restores? –  Marian Mar 5 '13 at 14:42
add comment

Your Answer

 
discard

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.