Backup strategies: which do you choose?

Edwin DeSouza had been nagging me about various web pages on the subject of online backup; one, on the Microsoft Developer Network, claims that it's bad to optimize your backup strategy for backup speed.

That's an interesting issue, and one that we thought about early on. We decided that, in general, it is good to optimize your backup strategy for speed. One size doesn't fit all, of course, but:

• The basic issue is that a fast backup can bring a slow recovery in its wake. That's often the case, but not always.
• The impact of a slow recovery depends on how often you do it. Our attitude is that a recovery is a very rare occurrence. The fact that we've got by without an online backup at all for so long reinforces this viewpoint.
• Backups can't proceed without some impact on performance. A “fast” backup frequently has less impact on performance than a slow one.
• There are different reasons to do backups in the first place; some of them don't involve disaster recovery.

This is only peripheral to the tenor of the article, though. The author is primarily trying to get people to think through their strategy, and the title basically refers to one example. One way to speed up backups is to do incremental or differential backups, or log backups. The example of recovering a database from a month-old backup and a month's worth of log backups makes my hair stand on end, and that's probably the intention. At least it gets people to think about the issues, and that can only be good.

To reinforce this viewpoint, the author wrote a second article about the different reasons for making backups; though he doesn't compare his statements there with the other article, he does state that archival backups don't need to be so frequent. And that frequently means faster backups.

The question is, which kind of backup is more common? You'd think the disaster recovery scenario is more important, but even there there are two kinds:

• Catastrophic hardware or software failure. In practice, RAID will recover from most hardware failures; after a system crash, there are software recovery techniques, usually involving logging, to recover table data.
• User error. The second of the articles refers to the case where a user accidentally issues a DROP TABLE command. This scenario usually requires a recovery from backups, and it may be the only one. Disregarding foreign key constraints, recovery in this particular case could be performed on an otherwise active database.

I don't have the overview myself, but people whom I respect tell me that the main use of backups nowadays is for archival purposes. We're using that as the basis for design decisions for the backup software, though it will be on a per-storage-engine basis: most transactional engines will do a “dirty backup” of the table (i.e. copy the table data without regard for consistency), followed by the redo log for the duration of the backup. Restore will then restore the dirty table and perform a recovery operation on it. This is slower than just restoring clean data, but it makes the backup much faster. I'd be interested in feedback on this approach.

I originally wrote this in early June, but as the result of problems with the RSS feed, it didn't make the forum until the end of the month, by which time it had been lost in history.

Working for MySQL

A few months ago, I was looking for people to work on the backup team. I no longer need anybody, but from time to time people who know me, notably from the FreeBSD project, asking what it's like working for MySQL. Many of the questions are of more general interest, so I'll put down some of the thoughts here.

What's working for MySQL like? The answer to that question depends on what you're used to. In my case, I've been working with the FreeBSD project for over ten years, and many of the people who ask me are also involved with BSD, so that's a good point to compare with.

On the other hand, others reading this may have no background in Open Source, so it's worth mentioning that aspect too.

There's a lot working with MySQL, of course, too much for a single installment. This is the first of a number of articles I plan on aspects of work at MySQL.

The office

For most of us, there is no office. My “commute” to work takes about 10 seconds (it's a big house, and I have an office at one end). Others don't have a dedicated office, but use a corner of the family room, for example.

Working from home is a great advantage, but it has its potential down sides too. On the up side, you can plan your work around other things you have to do. I know a number of people who work only a few hours during the day time, then go back to work at 10 pm and work another few hours.

That can be a disadvantage as well, of course. What does your family think of that? Being able to go back to work can be a real down side for workaholics. I've made the personal decision that I won't work after dinner unless there's a good reason. But that's the beauty of it: you can make personal decisions.

Team interaction

Of course, in a team you're not alone. Like me, you might be 750 km away from the nearest colleague; some, like Shane Bester in Capetwon, are many thousand kilometres from the next employee. Can that get lonely? Yes, of course it can. It can also result in poor communication. So MySQL goes to a lot of trouble to ensure good communications. One of the things that gets used better than I've seen before is IRC (Internet Relay Chat). Here's an excerpt from a discussion we had while I was writing this section:

<groggy> sbester|zz: How far are you physically from the next MySQL employee?
                                                              [10:55]
<EventumBOT> [#YYYYY] #XXXX (S3; mlord) FOLLOWUP: Waiting 4 Hours -
         https://support.mysql.com/4248
<sbester|zz> not sure.  many thousands of km.
<groggy> sbester|zz: Heh, just what I wrote.                  [10:56]
<JamesDay> hmm..  and on which continent are they
<JamesDay> might even be mexico:0
<sbester|zz> i thought south america
<groggy> Australia?
<Shawn> Has anyone done a "Google Earth" setup with pins for everyone's home
    addresses?
<JamesDay> I haven't seen one.  WOuld be interesting          [10:57]
<groggy> Shawn: I've been trying to convince Chad Miller to do soemthing like
     that.
<sbester|zz> there should be a physical map at one of the offices
<groggy> sbester|zz: There's one in Uppsala.
<groggy> sbester|zz: But what good are physical maps in a virtual company?
<Shawn> what good is a physical map in a virtual company?
<Shawn> ROFL
<sbester|zz> no good :-0
<kolbe|away> there's some intranet app like that              [10:58]
<bryan> groggy: I was going to do that with google maps, but I couldn't put it
    on the intranet
<kolbe|away> i saw it once but i never found it
<kolbe|away> again
<bryan> it violates the terms of service
<groggy> bryan: How come?
<Shawn> do we have anyone in Brazil                           [10:59]
<kolbe|away> Shawn: miguel is in brazil
<kolbe|away> !pd brazil
<ChanLogger> No matches could be found for 'brazil'.
<bryan> You can't use their API on  a website that is password protected
                                                              [11:00]
<groggy> !pd brasil
<ChanLogger> No matches could be found for 'brasil'.
<Shawn> !pd miguel
<ChanLogger> Miguel Solorzano, Support Manager Bugs Analysis
         (miguel@mysql.com) ...
<Shawn> I think he's closest to Shane                         [11:01]
<groggy> Where is Florianopolis?
<kolbe|away> http://en.wikipedia.org/wiki/Florianopolis       [11:02]
<kolbe|away> oh hey there it is on my huge map
<kolbe|away> Shawn: you're right, he is almost surely closest to shane
<groggy> Does Google maps do distances between two points?    [11:03]

To some extent, IRC is the virtual coffee room of MySQL. This particular channel, though, is also one of the main communication channels of the support department. The message above from EventumBOT is part of our internal support tracking system. The YYYYY and XXXX are tracking information which we don't release outside the support department, except to those people who are involved.