July 7, 2009

Improving InnoDB recovery time

Posted by Vadim |

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:

PLAIN TEXT
CODE:
  1. innodb_buffer_pool_size=16G
  2. innodb_log_files_in_group=3
  3. innodb_log_file_size=512M

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

PLAIN TEXT
CODE:
  1. 090706 15:39:47  InnoDB: Database was not shut down normally!
  2. ...
  3. 090706 16:51:56 [Note] libexec/mysqld: ready for connections.

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

In XtraDB we made some improvements, and it shows:

PLAIN TEXT
CODE:
  1. 090706 17:04:11  InnoDB: Database was not shut down normally!
  2. ...
  3. 090706 18:10:53 [Note] libexec/mysqld: ready for connections.

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

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

PLAIN TEXT
CODE:
  1. 090707 19:00:01  InnoDB: Database was not shut down normally!
  2. ...
  3. 090707 19:06:42 [Note] libexec/mysqld: ready for connections.

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.

Posted by Vadim @ 7:42 pm :: Backups, xtradb  
Print This Post Print This Post book mark Improving InnoDB recovery time in del.icio.usdel.icio.us :: submit Improving InnoDB recovery time to digg.comdigg  
Comment RSS  
Related posts: :Innodb Undelete and Sphinx Support::Magic Innodb Recovery self healing::How quickly you should expect to see bugs fixed:
 

12 Comments »

  1. 1. yejr

    well done,thanks much

    Comment :: July 7, 2009 @ 8:57 pm

  2. 2. Mark Callaghan

    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.

    Comment :: July 7, 2009 @ 10:52 pm

  3. 3. Domas

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

    Comment :: July 7, 2009 @ 11:26 pm

  4. 4. peter

    This is pretty cool !

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

    Comment :: July 8, 2009 @ 8:42 am

  5. 5. Shirish

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

    Comment :: July 8, 2009 @ 11:08 am

  6. 6. Gavin Towey

    Awesome work!

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

    Comment :: July 8, 2009 @ 1:59 pm

  7. 7. Jay Pipes

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

    -jay

    Comment :: July 9, 2009 @ 8:47 am

  8. 8. Vadim

    Ok, ok,

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

    Comment :: July 9, 2009 @ 9:15 am

  9. 9. Mark Callaghan

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

    Comment :: July 9, 2009 @ 9:33 am

  10. 10. Domas

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

    Comment :: July 9, 2009 @ 11:23 am

  11. 11. Apachez

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

    Comment :: July 11, 2009 @ 4:09 pm

  12. 12. Vadim

    Yasufumi made explanation how it works on this page
    http://www.percona.com/docs/wiki/percona-xtradb:patch:innodb_recovery_patches#is_the_buf_flush_insert_sorted_into_flush_list_hack_correct

    Your comments are welcome.

    Comment :: July 16, 2009 @ 9:52 pm

 

Subscribe without commenting

Trackbacks/Pingbacks