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:
1 2 3 | innodb_buffer_pool_size=16G innodb_log_files_in_group=3 innodb_log_file_size=512M |
So let’s take standard MySQL 5.4.1, it starts:
1 2 3 | 090706 15:39:47 InnoDB: Database was not shut down normally! ... 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:
1 2 3 | 090706 17:04:11 InnoDB: Database was not shut down normally! ... 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:
1 2 3 | 090707 19:00:01 InnoDB: Database was not shut down normally! ... 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.
well done,thanks much
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.
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 %)
This is pretty cool !
We should see how recovery math will work now – it may change the log files usable in production dramatically.
Amazing. The patch is really tiny, some explanation on how & why this works so beautifully will really help.
Awesome work!
One question though: does this patch affect rollback time as well? Is it related to the crash recovery process?
Agree with Shirish…a comment explaining the change in the code would be very helpful 🙂
-jay
Ok, ok,
we are preparing explanation. Unfortunately for Yafusumi good explanation in English much harder than writing C code 🙂
…or the community can contribute. I volunteer Domas to document this one.
I see Mark is getting much better understanding of communities after leaving ‘do no evil’ company 😉
Shirish: Im more interrested in why this wasnt fixed by innodb or mysql inc. themselfs all these years? 🙂
Yasufumi made explanation how it works on this page
https://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.