Improving InnoDB recovery time

Improving InnoDB recovery time

PREVIOUS POST
NEXT POST

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.

PREVIOUS POST
NEXT POST

Share this post

Comments (12)

  • yejr Reply

    well done,thanks much

    July 7, 2009 at 8:57 pm
  • Mark Callaghan Reply

    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.

    July 7, 2009 at 10:52 pm
  • Domas Reply

    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 %)

    July 7, 2009 at 11:26 pm
  • peter Reply

    This is pretty cool !

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

    July 8, 2009 at 8:42 am
  • Shirish Reply

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

    July 8, 2009 at 11:08 am
  • Gavin Towey Reply

    Awesome work!

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

    July 8, 2009 at 1:59 pm
  • Jay Pipes Reply

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

    -jay

    July 9, 2009 at 8:47 am
  • Vadim Reply

    Ok, ok,

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

    July 9, 2009 at 9:15 am
  • Mark Callaghan Reply

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

    July 9, 2009 at 9:33 am
  • Domas Reply

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

    July 9, 2009 at 11:23 am
  • Apachez Reply

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

    July 11, 2009 at 4:09 pm
  • Vadim Reply July 16, 2009 at 9:52 pm

Leave a Reply