Fun with backups, for some values of fun

[unixronin]

"I've had fun, and this is not it!"

"Sometimes you have fun, sometimes the fun has you."

Backups are one of those things that are boring and unexciting, except when they're not.

I ran full backups today.  This is basically a quite painless process, between Bacula and an LTO1 tape drive, though it does mean spending quite a while sitting and waiting around with high system load while eight machines get backed up.  (One of these days I should probably move the bacula server from babylon5 to minbar.)  It was painless and uneventful right up until the point that the InnoDB ibdata1 file filled up the /opt partition, which -- naturally -- came minutes before completing the last backup.

Of course, when the partition filled, squid died, which caused me some parallel puzzlement until I figured out what had happened.  I had to purge the incomplete full backups I'd just run, dump all the databases to an ASCII file, stop MySQL, move the old database files out of the way, then restart MySQL and recreate the databases from the dump, meanwhile hunting around trying to figure out why LogJam had stopped working (the answer of course being that curl could no longer talk to squid).

Aside from the large waste of time, here's the bad part of this:  This process recovered something like 600MB of disk space.  /opt/mysql/var/ibdata1 had grown to around 1.1Gb, and over half of it was wasted space.

This shouldn't happen, but it's a design flaw of InnoBase.  InnoDB has a lot of advantages over MyISAM, but it has one rather nasty vice, which is that it is very bad about reclaiming unused or freed space.  The ibdata files grow monotonically over time, and there's no way to compact them except by dumping, deleting, re-creating and reloading.  There is no tool or function within the database engine that will compact a database and shrink the file, and that's Bad.

Oracle recently acquired Innobase, in response to which MySQL has announced their intention to develop their own database engine.  Hopefully, it will offer the technical advantages of InnoDB but with a more robust compaction/vacuuming capability.

Comments

[smandal.livejournal.com]

ic, the Bacula solution is more "admin-centric."

[unixronin.livejournal.com]

It's more that the Bacula solution (which can back up to disks, by the way, though there's still some headaches involved in using removable IDE or USB disks) will automatically back up all the machines (except at present the Macs), unattended, on a regular schedule with a differential once a week and incrementals the other six nights, and generally all I have to do is change a tape every couple of weeks. If I drop a tape, it's not a worry; in fact, should I need to, I can just slip one into a bubble-pack envelope and drop it in the mail with few concerns about it being damaged in transit. I get about 130GB on an LTO1 tape, and I can buy them for about $40 each.

All in all, it works well.

The other issue is that I'm one of the lesser developers of Bacula, though at the moment I'm not actively doing any work on it. Several of the features I've added have been because I wanted to use them.

[smandal.livejournal.com]

Yeah, I can do the same with anacron+rsnapshot, but it has to be to a random access device like an attached or network-mounted volume.

How many write cycles can a tape handle? Bandwidth?

[unixronin.livejournal.com]

Modern tape media is DLC coated -- epitaxial diamond film, essentially -- and lasts a LONG time, especially in light use. Bandwidth, right now with two clients running I'm pushing 9 megabytes a second onto my LTO1.