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

Huh?

[fatcook.livejournal.com]

Squid died? Is that anything like " the rabbit died"?

Re: Huh?

[unixronin.livejournal.com]

Not in the slightest.....

Re: Huh?

[cymrullewes.livejournal.com]

Squid is the web proxy. It caches stuff that one or the other of us looks at so when the other goes to look at it, it is there waiting instead of needing to be downloaded again. He set it up when we on 28.8K dial-up.

Re: Huh?

[fatcook.livejournal.com]

Thank you for the answer. I told you I wasn't very computer lit, but I'm learing.

[smandal.livejournal.com]

How much do you have to back up that you need to go to all this trouble?

[cymrullewes.livejournal.com]

It all needs to be backed up. :-)

I don't think he thinks of it as trouble. It's The Way Things Are Supposed to Go.

[smandal.livejournal.com]

ok. I just use an external USB hard disk a few times bigger than the one in my laptop, and rsnapshot (http://www.rsnapshot.org).

Given how cheap massive hard disks are these days, it's an increasingly good solution.

[cymrullewes.livejournal.com]

He's backing up 9 machines and has two more that need to be backed up. I'm not sure why the Macintoshes aren't being backed up. I think he said there was a Bacula client for them. If not then I suggested that he code one. He's got access to Apple developers who could help him out and he's done some Apple hacking before. I still want to have the little Aztec cursor.

[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.

[unixronin.livejournal.com]

The Macs aren't being backed up because they're running OS9, and there is no Bacula client for OS9. It wouldn't be a simple port, the environment is different enough that it would have to be written from scratch. Even valid file and path specifications are different enough from both the Unix and Windows worlds to be a problem.

[ithildae.livejournal.com]

I used to do tape. Then tape got way too small and far too expensive. At this point, I have two servers that contain copies of my home directories from the various machines on the network. I figure that if I lose both servers, a restore will be the least of my problems.

Curious, have you tried PostgreSQL? The DBA in me really wants an ACID database. InnoDB still only has a pH of about 6.5.

[unixronin.livejournal.com]

There is PostgreSQL support in Bacula, but I personally don't use it. The performance characteristics of pgsql turn out to be much less compatible with general use of Bacula than those of MySQL. (As a generalization, we've found that Bacula+MySQL is faster on writes, Bacula+PostgreSQL is faster on reads. 98% of the time, in Bacula, you're writing, not reading.)

[mrmeval.livejournal.com]

Consider a BTree database, it had to be compacted offline. It's a proprietary format. It was the back end to a proprietary database IDE called TAS+ which generated pseudocode. It used a novel program/driver to run the database file.

*never again*