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:
- FLUSH TABLES WITH READ LOCK (yes, this won't help for the InnoDB tables)
- Create the snapshot
- Store the output of SHOW MASTER/SLAVE status in a file to be part of the backup
- UNLOCK TABLES
- Mount the snapshot
- Start a second MySQL instance that accesses the tables on the snapshot, let InnoDB perform its table recovery
- 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...
My calendar for the upcoming months is already filling up with conferences, trade fairs and other events at which I'll speak about MySQL. Here's a quick overview:
- This coming Thursday at 15:00 CET, I'll be speaking about "Backing up MySQL using file system snapshots" at the MySQL University. The session will be hosted live using DimDim, which is a great online conferencing and presentation system (Flash required). Attendance is free, so come and join me if you want to learn more about this backup technique!
- On Friday, 6th of March at 15:15 I'll give a presentation about "MySQL Backup and Security" in the CeBit Open Source Forum in Hannover, Germany. By the way, Sun will have a large presence themed "Open Source for Open Minds" in Hall 6, Booth E36. There'll be a MySQL info pod as well, which I will help manning from Friday until Sunday, 8th. So make sure to stop by and say hello!
- On March 23-24, there'll be a PHP/MySQL Conference in Warsaw, Poland, organized by IDG. I submitted three talks about various MySQL topics, currently I am still waiting for the confirmation which of these they selected.
- On April 20-23, there is of course the MySQL Conference & Expo in Santa Clara, to which I am very much looking forward to. I'm helping to organize the DotOrg Pavilion in the exhibition hall and will likely give a BoF about a project I'm currently involved in.
- In May 4-5 there is the AMOOCON in Rostock, Germany, which is a conference about Open Source VoIP and communication. I'll talk about "MySQL High Availability Solutions" and "Backup & Security Best Practices" (in German). My colleague Geert will be there as well, giving an in-depth talk about MySQL Cluster.
There is a number of additional events in the pipeline, I'll blog about these once my session proposals have been accepted.
Sorry for the downtime of this site - until around a week ago I hosted my home page on a trusty Genesi Pegasos II system (powered by a PowerPC G4 Processor clocked at 1GHz, using Debian 4.0 PPC with 512 MB of RAM), serving these pages from my home DSL connection. Unfortunately this system provided no means of redundancy - the hard disk drive died.
Luckily I perform frequent backups, so I moved most parts of the site to a shared hosting space now - the picture gallery is unfortunately too big to fit into the space that I have there. I'll try to move the pictures into my Flickr account instead, but this will take some time.
Note that the primary domain name of this site is now lenzg.net - lenzg.org, (the domain that I tried to promote as the official domain for my site) used to redirect to the home machine at lenz.homelinux.org. Both now redirect to the new address instead. I've initiated the move of the lenzg.org domain to the other provider as well, so soon this site will be available from both the .org and .net domain. Please don't link to lenz.homelinux.org anymore, as that site will eventually go out of service. Until then, a small openSUSE Linux box (Intel PIII, 500 MHz, 192 MB of RAM) running lighttpd will perform the URL redirection.
