speeding up MySQL import

Top Page
Attachments:
Message as email
+ (text/plain)
Delete this message
Reply to this message
Author: der.hans
Date:  
To: quatsch
Subject: speeding up MySQL import
moin moin,

I've got a MySQL ( 5.1 ) instance that's scheduled to be converted to
Percona.

One DB has 100GB on disk. It's all InnoDB on the slave and the master will
finish moving to InnoDB when we can get some downtime ( or we are able to
reverse slave/master ordering ).

I'm trying to create a second slave for some testing. Part of the testing
is testing the backups.

Using --single-transaction --master-data=1 options for mysqldump. -opt is
on by default.

I think dumps are taking about half an hour.

For import, I'm turning off autocommit and foreign key checks, then using
"source $backupfile.sql" before turning foreign key checks and autocommit
back on.

Two days thus far and I'm up to 84GB on disk. Much better than before I
turned off autocommit and foreign key checks when 3 days of restore only
got me 6GB on disk

Any suggestions on how to improve the speed of the import?

CPUs are mostly idle. One has 15% wait, but the others have none. Guess
that's the one doing the import.

I currently can't shut down the slave to just copy the entire mysql
directory to the new box. That might change should the directory copy be
my only feasable option.

For added fun, there are some data inconsistencies on the slave that I
also need to overcome :).

ciao,

der.hans
-- 
#  http://www.LuftHans.com/        http://www.LuftHans.com/Classes/
#  "Batman Sues Batsignal: Demands Trademark Royalties." -- Cory Doctorow
---------------------------------------------------
PLUG-discuss mailing list - 
To subscribe, unsubscribe, or to change your mail settings:
http://lists.PLUG.phoenix.az.us/mailman/listinfo/plug-discuss