July 28, 2014

Improving InnoDB recovery time

Speed of InnoDB recovery is known and quite annoying problem. It was discussed many times, see:

http://bugs.mysql.com/bug.php?id=29847

http://dammit.lt/2008/10/26/innodb-crash-recovery/

This is problem when your InnoDB crashes, it may takes long time to start. Also it affects restoring from backup (both LVM and xtrabackup / innobackup)

In this is simple test, I do crash mysql during in-memory tpcc-mysql benchmark with 16 running threads.
MySQL params are:

So let’s take standard MySQL 5.4.1, it starts:

So you see it takes 1h 12 mins to start.

In XtraDB we made some improvements, and it shows:

1h 06mins. Better, but still not good.

So Yasufumi sat, scratched his head and made new patch for InnoDB. With this patch
mysqld starts:

6 mins 41 secs. 10x improvement. Something to think about.

The patch is not in official XtraDB tree yet, it lives in
http://bazaar.launchpad.net/~percona-dev/percona-xtradb/recovery-speed-hack/
tree.

The patch by itself is quite small, I would ask InnoDB experts to review it:

http://bazaar.launchpad.net/~percona-dev/percona-xtradb/recovery-speed-hack/revision/86

So your comments about patch is welcome! If it is stable it will go to XtraDB and XtraBackup.

About Vadim Tkachenko

Vadim leads Percona's development group, which produces Percona Clould Tools, the Percona Server, Percona XraDB Cluster and Percona XtraBackup. He is an expert in solid-state storage, and has helped many hardware and software providers succeed in the MySQL market.

Comments

  1. yejr says:

    well done,thanks much

  2. Mark Callaghan says:

    Yasufumi rocks! But we don’t hear enough about his amazing work. I think Percona needs another person who spends all of their time explaining his changes to the rest of us.

  3. Domas says:

    Nice! :-) Last time I looked at the problem, I considered putting the whole structure into RB-tree, which would mean way way more code.
    Indeed, explaining some of the changes could be very nice thing to do %)

  4. peter says:

    This is pretty cool !

    We should see how recovery math will work now – it may change the log files usable in production dramatically.

  5. Shirish says:

    Amazing. The patch is really tiny, some explanation on how & why this works so beautifully will really help.

  6. Gavin Towey says:

    Awesome work!

    One question though: does this patch affect rollback time as well? Is it related to the crash recovery process?

  7. Jay Pipes says:

    Agree with Shirish…a comment explaining the change in the code would be very helpful :)

    -jay

  8. Vadim says:

    Ok, ok,

    we are preparing explanation. Unfortunately for Yafusumi good explanation in English much harder than writing C code :)

  9. Mark Callaghan says:

    …or the community can contribute. I volunteer Domas to document this one.

  10. Domas says:

    I see Mark is getting much better understanding of communities after leaving ‘do no evil’ company ;-)

  11. Apachez says:

    Shirish: Im more interrested in why this wasnt fixed by innodb or mysql inc. themselfs all these years? :-)

  12. Vadim says:

Speak Your Mind

*