speeding up MySQL import

[Eric Cope]

It doesn't answer your questions, but what made you decide to move to
Percona? Are you moving to XtraDB?
Thanks,
Eric

On Wed, Oct 13, 2010 at 11:58 PM, der.hans <PLUGd at lufthans.com> wrote:

> 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 - PLUG-discuss at lists.plug.phoenix.az.us
> To subscribe, unsubscribe, or to change your mail settings:
> http://lists.PLUG.phoenix.az.us/mailman/listinfo/plug-discuss
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.PLUG.phoenix.az.us/pipermail/plug-discuss/attachments/20101014/0956e662/attachment-0001.html>