Wandering Thoughts archives

There is such a thing as too much SQL (a reminder to myself)

This is a very small war story.

I've spent part of today working on generating some reports from our IP traffic accounting system. We have multiple internal networks that get NAT'd out different gateways, and what we want is a report (or a set of reports) of the top individual traffic sources for each gateway that did more than a certain amount of aggregate traffic in the day.

Our traffic accounting data is held in a PostgreSQL database. Since a typical day has around six to eight million records (and this is after a record aggregation process), making efficient SQL queries for this reporting is relatively important; even if we're only going to run it once a day, it would be nice if it didn't take ages and bog the database down.

Because of how we collect the raw traffic data our traffic monitoring system only has the traffic's internal IP, without explicit information on which NAT gateway the traffic used. The internal IP statically determines the outgoing gateway, but in a complex way; there are subnets of some networks that go out different gateways than their containing network (eg most of a /16 goes out one way, but a /24 in the middle uses another gateway IP), and there are some machines that are assigned individual public IPs. This complexity means that attributing traffic to the right gateway is one of the hard parts of generating this report.

It's possible to do this in SQL and I spent today working out how to do it (with reasonable efficiency); I needed some mapping tables, one with CIDR netblocks and one with the exceptional machines, and some joining and NOT IN work. Then I ran into the subnet challenge. As I was working out how to split the remaining IP address ranges of the overall network with the subnet into multiple CIDR netblocks, I came to my senses.

A quick ad-hoc query showed me that we have less than a thousand different internal IP addresses generating traffic on any given day. Yes, I could do the gateway attribution in SQL, but doing it in SQL makes no sense; with so few internal IP addresses to process it's overkill and almost insanity. The right approach to my problem is to only use SQL for the heavy work of data reduction by generating a 'volume by internal IP' report and then to do the complex work of attributing each internal IP to a particular gateway in another program, in an environment where I have the full power of a procedural programming language (and where I am not potentially running the attribution process seven million times but instead only a few hundred times).

(As a bonus I'm going to do one less volume-aggregation query for reasons that don't fit in this entry's margin. And I no longer need those auxiliary mapping tables, which means that we don't have to maintain and update them.)

As I've noted to myself before, SQL is not the answer to everything. Today was a sharp reminder of this, although fortunately it was not all that painful a one. (Still, I'm going to mourn my nice SQL query a bit.)

PS: if there is a fast, efficient way to get PostgreSQL to tell you the most specific CIDR match for an IP address out of a table of CIDRs (in the context of an overall SELECT involving lots of different IP addresses), please don't tell me what it is. I don't need the temptation.

(Okay, I'd be interested to know what it is, but I'm always curious about things like that.)

Don't make your 'I am processing' animation too complex

This is a lesson from long ago, one that I learned from the very first Amiga word processor. This word processor's mouse busy-cursor animation was of a bird leisurely flapping its wings; because the original Amiga was kind of slow, people wound up seeing this animation fairly often. Several years after the Amiga was released, I wound up talking to one of the people who had designed this (she was my HCI teacher) and she asked me whether us actual Amiga users had liked the busy cursor; the team who built the word processor had apparently been proud of that particular feature.

I had to tell her that we'd pretty much universally hated it. The problem was that it sure looked like the word processor was sitting around wasting its time slowly animating a (complex) cursor instead of spending that time actually doing whatever the slow operation was. That things were slow was bad enough; to have the bird slowly flapping away was salt on the wound, and intensely irritating.

This held true even for us Amiga programmers, who knew perfectly well that this cursor animation was actually a very cheap operation that took basically no time. We may have known better intellectually, but it didn't help the emotional impression; we still wanted the program to stop being fancy and get on with it.

The whole thing left a powerful impression on me. I'm sure that you can draw a lot of lessons from it, but the obvious one for me is that you don't want to make your busy-cursor or other processing animations too complex (or rather, you don't want them to look too complex). Regardless of the actual work it takes, a complex animation will leave the emotional impression that your program is spending a bunch of effort on it instead of other, more important things.

The Mac's classic spinning-clock busy cursor may have been very simple and basic, but it had the virtue of looking simple and cheap. I suspect that a similar thing holds true for Firefox's throbber animation (which is subtle in any case).

Sidebar: how the animated cursor was cheap on the Amiga

The Amiga display hardware had hardware sprites, and one of them was used for the mouse cursor. Changing what any sprite looked like was a simple matter of updating the address where the display hardware should fetch its bitmap data. The net result is that you could cycle through a fixed, precomputed mouse cursor animation by just writing a control register every so often.

(This also made moving the mouse cursor a very fast and cheap operation; you just wrote a couple of control registers to change the X and Y position of the cursor sprite.)

Focusing on what you actually need in a program

I recently wrote a local program to do more useful job of reporting the status of our ZFS pools. To simplify slightly, ZFS pools have a number of flavours of redundancy; none, mirroring, RAID-5, and RAID-6 (currently). However, we only use mirrored pools, so for our own uses the program I was writing didn't need to deal with any other sort of pool.

So of course I spent a not insignificant chunk of time making my program handle RAID-5 and RAID-6 pools. And not just handle them, handle them just right, which involved deciding the severity of various faults in them. Maybe someday we will have a use for this and it's certainly correct, but in the mean time it's functionally useless.

This is a perfect illustration of one of my abiding sins in programming (strictly speaking, in development). Left to my own devices, I can easily wind up spending an inordinate amount of time on picky issues that ultimately don't matter for what we need today's program to do. It's hard for me to do otherwise, because I can always see one thing that the program doesn't handle right, or one bit of output that could be improved, or what have you.

(For me this usually manifests as a nearly endless obsession about fine tuning the program's output format.)

Thus, once again, I need to learn to sort out what we need now from the perfect answer to the problem I've set, and to accept that I don't need to handle some cases because they're ones that we don't care very much about or have no use for; it's the core functionality that matters. And when that core works, I need to let go of my feelings that the peripheral things aren't right and need to be polished more. Especially for local tools, the urge for generalism and perfection is a time-wasting mistake (or at least a danger).

It's not an easy thing. Polished, complete code gives programmers a warm feeling of satisfaction (and for good reason in many cases), while there's little joy in code that you know is incomplete and limited. The temptation to spend ten minutes or half an hour or whatever to fix some of those little nagging issues is always there. More than that, I think that such polishing is addictive, partly because it gives you a series of rapid feedback and rewards. It's sort of like unit tests; you can handle a new case or make the output better and ding, you've gotten that feedback. The program is improving before your eyes, never mind that the improvement is unimportant.

(Note that the rules are different for tools that are intended to be general from the start; there it's not a mistake to handle all of the cases, it's part of the requirements. Whether sysadmins should write general tools even when they only need local ones is one of those interesting debates.)