Lenz Grimmer's blog

Today I gave my first MySQL University session as a speaker, talking about Backing up MySQL using file system snapshots. The talk went quite well (at least that was my impression) and we had ~10 people attending. The slides (PDF) and a recording of the session are now available from the Wiki page. Unfortunately the recording lacks the audio track, which is a bit of a bummer. We've submitted a support request with the DimDim folks, so hopefully they can provide us with a complete recording.

There was one question during the session that I was not able to answer myself, so I'm asking for your insights here:

Consider we're using InnoDB and MyISAM tables on a file system that can be snapshotted (e.g. Linux LVM or ZFS) and we're performing the following operations:

1. FLUSH TABLES WITH READ LOCK (yes, this won't help for the InnoDB tables)
2. Create the snapshot
3. Store the output of SHOW MASTER/SLAVE status in a file to be part of the backup
4. UNLOCK TABLES
5. Mount the snapshot
6. Start a second MySQL instance that accesses the tables on the snapshot, let InnoDB perform its table recovery
7. Shut down the second instance and perform the backup of the snapshot

The question that came up was if this actually still is a consistent backup, considering that InnoDB rolled back uncommited transactions. Does the state of the tables still match the binary log positions we noted before? I assume yes, as long as the transaction does not involve modifications non-transactional tables.

Another suggestion that came up was to change InnoDB's configuration variable innodb_max_dirty_pages_pct to "0" prior to performing the snapshot, to minimize the amount of dirty pages that have not been written to disk (and thus reducing the time required for recovering later). I wonder if this would make a difference...

What other InnoDB variables might have a noteworthy effect in the context of snapshot backups? I am looking forward to your comments.

Shortly after I blogged about my upcoming talks and events, I got informed that one of my session proposals for the next09 conference was accepted. This event will take place in Hamburg, Germany on Tuesday and Wednesday (5th and 6th of May), so at least there isn't much travel involved.

My talk about "Working for a Virtual Company: How we do it at MySQL" has been scheduled for 9:00 am on Wednesday morning, parallel to a keynote by Brian Solis from FutureWorks. I'm not sure how many people will actually show up, but I look forward to giving a talk about a less technical subject for a change! I'm going to share my experiences how it's like working in a virtual company of our scale and talk about some of the key aspects of what makes this model work. If you happen to be in Hamburg at that time, consider attending the conference!

The conference program looks quite interesting and there are quite a few well-known names on the speakers roster. Considering that I'll also be giving two talks about MySQL at AMOOCON on Monday that very same week, it'll be some busy times...