MySQL Performance Blog

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.

It seems pretty common to find customers install DRBD for the wrong reasons. There are many pros/cons to compare DRBD to replication, but I've managed to cut down my spiel I give to customers to these two points:

• DRBD's aim (assuming replication mode C) is to provide 100% consistency, and then as much uptime as possible.
• MySQL Replication (with a manager such as MMM) aims to have 100% availability, at the potential loss of some data surrounding a failure.

So if you are installing DRBD with the aim of purely "availability", and are not worried about losing that last write on the crash to your master database that (hopefully) happens only once every few years, you may be using the wrong technology.

While the prized "1 minute failover" is possible in DRBD, it doesn't really explain the full picture. The typical crash recovery process in DRBD is a lot longer:

• After resource transfer, a filesystem check runs (0-5 seconds).
• mysqld is started (1-5 seconds)
• InnoDB runs through crash recovery (1 minute - several hours). Peter wrote about this here.
• The server is then ready to accept connections.

Now, having said that: If you have an application that requires 100% consistency, then DRBD is one of your best choices on the mysql-market today.

This is just a public service announcement (reminder?) that LenZ and Giuseppe are planning OpenSQL Camp 2009, this time in Europe, which is great. It'll be part of FrOSCon.

I wish I could say that I'll attend, but due to various unpredictabilities in my family, I can't plan that far ahead. I don't yet know whether anyone from Percona can attend, but I know a couple of our European consultants are looking at it and tossing around various proposals. (BTW, any lack of proposals / sessions from Percona is strictly our fault, in case there is concern about that. One person noticed and said something to me, so I thought it's worth mentioning.)

I'm thrilled that people thought the first OpenSQL Camp was valuable enough that they're carrying it forward!