Today Sun announced release of X4540 Open Storage server (a.k.a Thor). We were fortunate to get early access to Thor to certify Amanda Enterprise and Zmanda Recovery Manager (ZRM) for MySQL. Both solutions are optimized for backing up to disk, are already certified with Solaris 10, and leverage capabilities of ZFS. So it made perfect sense to certify them on Thor, effectively creating a high-performance and yet relatively inexpensive backup appliance.

The unit we were using is powered by 8 CPUs operating at 2300 MHz and provides 48 SATA drives with total capacity close to 50TB in 4U enclosure. That is good packing; a typical EMC unit with so much capacity will take the whole rack.

The two boot drives configured as a mirror were running UFS. To get capacity for backups, we easily created Zpool with 12 drives and 6 spares in RAIDZ2 configuration. That gave us 7 TB of capacity for backups with very high level of protection against drive failure:

bash-3.00# zfs list
NAME USED AVAIL REFER MOUNTPOINT
zmanda 203K 7.13T 49.0K /zmanda
zmanda/vtapes 49.0K 7.13T 49.0K /var/lib/amanda/vtapes

While installing ZRM and Amanda Enterprise on Thor, we found that a couple of Solaris packages that we require were missing, and our coverage of dependencies in documentation was not perfect. Luckily, that was the only hiccup. After fixing the dependencies, the rest of installation and configuration went smoothly.

This screenshot shows part of a summary report about backup of five Solaris clients to Thor over 1 Gb network. Each client had about 100 MB of data. In that test we forced full backup on each run, with each client pushing data at 60 MB/s. That is not bad at all considering that you can get only 20-25 MB/s for a typical LTO-2 tape drive.

The X4540 Open Storage server is an excellent choice for creating your own backup to disk appliance. It provides high performance and very large capacity. One key feature is how easy it is to manage all that capacity with ZFS.

Special thanks to Sun?s Menlo Park and Colorado interoperability teams who helped us to get started and advised on considerations in configuring ZFS for backup to disk.
———
Dmitri Joukovski

The other day I was explaining options to someone who wanted to know about archiving data in MySQL. “So,” he said, “I might have to code my app to look for the data in two places?” The disadvantage of this is that his app might be more complex. Another disadvantage is that it might take two queries — if you look for a user in the usual location and it’s not there, you have to look for it elsewhere.

One way to deal with this, as long as the archived data is on the same server, is a UNION.

select user_id from user where user_id = 123
union all
select user_id from user_archive where user_id = 123;

The benefit is that you don’t have to issue two queries. That saves network round trips, and makes your code shorter. But it has a disadvantage, too: you’re still querying the archive table when you don’t need to. Does this matter? Yes, it does. Your archive table may be very large and slow — perhaps stored on a big slow hard drive, perhaps on a SAN — and just peeking at it is kind of expensive in some cases.

Something occurred to me a couple of weeks ago: why not write a UNION that stops executing as soon as one part of it finds a row? Then you can UNION to your heart’s content and not incur the overhead of that second lookup unless you need it. For lack of a better term, I’m calling this a lazy UNION.

My idea here is to use a user variable. If the first part of the UNION finds a row, it sets the variable. The second part has the variable in its WHERE clause, and won’t execute if the variable has been set by the first part. To make the whole thing self-contained, I’m adding a third part of the UNION, which will always execute but never return any rows; it will set the variable back to its initial state of NULL.

Here’s a complete example:

drop table if exists user, user_archive;
create table user(user_id int not null primary key);
create table user_archive like user;
insert into user(user_id) values(1);
insert into user_archive(user_id) values(2);

select greatest(@found := -1, user_id) as user_id, 'user' as which_tbl
   from user where user_id = 1
union all
select user_id as user_id, 'user_archive' as which_tbl
   from user_archive where user_id = 1 and @found is null
union all
select 1, '' from dual where ( @found := null ) is not null;

select greatest(@found := -1, user_id) as user_id, 'user' as which_tbl
   from user where user_id = 2
union all
select user_id as user_id, 'user_archive' as which_tbl
   from user_archive where user_id = 2 and @found is null
union all
select 1, '' from dual where ( @found := null ) is not null;

You can play around with it and verify that indeed, the second part of the UNION never executes if the first part finds a row. There are several ways to do this: with EXPLAIN, by adding some more variables to show which part of the query executes, etc. The results of the above query are as follows:

+---------+-----------+
| user_id | which_tbl |
+---------+-----------+
|       1 | user      | 
+---------+-----------+
1 row in set (0.00 sec)

+---------+--------------+
| user_id | which_tbl    |
+---------+--------------+
|       2 | user_archive | 
+---------+--------------+
1 row in set (0.00 sec)

I have not benchmarked this. My gut feeling is that whether it’s beneficial is going to depend on your workload. But it’s a fun little hack I thought I’d share with you. By the way, there’s no reason you have to stop at two; you could add any number of queries to the UNION.