Hiding from the Stasi

I find it seriously depressing to think that the free and wild internet of my youth has become so spied upon that projects to protect your privacy are now so common.

It's very difficult to surf anonymously any more, with endless cookies and logs being kept by ISPs to allow the state to spy on your surfing habits. If this bothers you, have a look at TOR. It's rough and ready, and it's not a one-stop anonymiser, but because of the way things work, I doubt they'd ever be able to achieve a one-stop anonymiser.

In a nutshell, all you have to do is go to http://tor.eff.org and leech a binary, run it, and config everything for socks A proxy on port 9050, but that wouldn't be much of a article would it?

More here.

Length matters

I don't want to bore non-techies with this one, but I do want you to think about your passwords for just a few minutes.

The first thing is this: even if your password isn't something obvious like "password" or "obo", then hackers don't have to guess them. There is free, readily available software out there to do brute-force password hacking. So they can fire it up, go out for a cup of coffee, do the groceries, have a good night out and come back to find your password ready and waiting for them.

The second thing is this: the longer your password is, the longer it takes to crack.

The third thing is this: the more types of characters you use, the longer it takes to crack.

Let me give you a for instance: if I choose the password "obo", a brute-force cracker will take an average of 0.02 seconds to crack. So, "immediately". If I choose a slightly longer password, like "obnoxio", that will take two and a quarter hours. Much better, but still not really secure. However, if I simply change the password to include numbers and special characters, e.g. "Obnox1o$", it will take 210 years to crack.

And "Obnox1o$ Cl0wn" could take 154,640,721,434,000 years to crack using brute force.

So, put a bit of effort in, mix it up a little and make it just a little bit longer. Because it's worth it.

More info here.

Microsoft: cunts!

Beware the cuntish BSoD:

Microsoft has confirmed reports that some users are experiencing Blue Screen of Death errors after they apply Security Bulletin MS10-015 (rated Important) to patch vulnerabilities in Windows Kernel (977165). At the same time, the Redmond company revealed that customers affected by BSOD after installing this month’s security bulletins also rendered their computers useless, as the machines became incapable of booting, not even in Safe Mode. The issue seems to affect mainly Windows XP computers, however, users of Windows Vista and Windows 7 also reported similar behavior. As a direct consequence, Microsoft has pulled MS10-015, and is no longer distributing the bulleting through Windows Update, while it is working to produce a resolve.

17 years to write a patch and they cunt it up royally. Marvellous.

Beware!

Tech Tip du Jour: I'll see your MIT and Stanford and raise you an Oxbridge

Looks like iTunes University is really kicking off: Cambridge and Oxford are both offering free material.

Tech Tip du Jour: Easy PDFs

Seems to work OK for doing simple PDF images, it won't preserve links, for instance. Also, Windows-only, I'm afraid!

But the price is right!

Tech Tip du Jour: MIT Open Courseware

Seems like MIT have a shedload more free courseware than Stanford.

Hat tip to Gumby.

Tech Tip du Jour: Stanford Engineering Everywhere

Stanford University is offering free online courses in basic computing, AI and linear systems.

Even if you're not a geek, it might be worth a look.

Tech Tip du Jour: IDS is cheaper than supported MySQL or PostgreSQL

Non-geeks may want to look away now.

There was a discussion on comp.databases.informix between two arrogant, opinionated arseholes:

Ian Michael Gumby wrote:
the support license costs [for MySQL or PostgreSQL] are much less than those of IBM or Oracle.

Obnoxio The Clown replied
Are you absolutely sure of that?

Ian Michael Gumby wrote:
OTC, yeah I'm sure. Unless you're also including certain very large customers who qualify for J level discount and then negotiate a 90% discount on top of that.

But that's not the market that we're talking about. We're talking about the SMB customer who at best could get a 20-25% discount from list.

You can check the prices and maintenance renewal.

Since I'm babysitting tonight, I cracked open my trusty business partner's guide to pricing:

Assuming a single-CPU Intel box (which isn't entirely unreasonable for an SMB):

• Picking a PostgreSQL support vendor at random, I got a price of $2995 per annum for support. (Plus the bastard is in the US, so no bloody use to me.)
• MySQL is also $2995 for support.
• IDS Express Edition is $1035 for support. (Plus I get to speak to the wonderful, patient and mild-mannered Big Potatoe!)

So, for a five year period:

PostgreSQL: $14975
MySQL: $14975
IDS: $9315

And that's list. And pretty SMB.

The resident Oracle shill then piped up:

And if IDS were free one might use your numbers as an argument in its favor: Alas it is not. Why don't you put up all of the relevant numbers?

Let's see:
Oracle SE1 $7,076 with support.
SQL Server SE $5,999 with support.

Really whipping them into a frenzy there OTC.

But, as I replied: for $9135, you get to use Informix. You may as well compare a BMW with a Kia Picanto. I know what I'd rather work with. (And the license costs are included.)

Gumby then said he was talking about the full-fat, Enterprise Edition. But really, if someone wants to buy that, then it's all a lot more complicated, Oracle EE pricing goes way up, there's all sorts of option buying and stuff that PostgreSQL and MySQL can't do or can only do with lots of buggering around and extra costs.

I reckon if they're looking at supported PostgreSQL or supported MySQL, then really, Express Edition IDS is more than adequate.

There, Timmy, are you happy now?

That has got to be the dullest blog post I've ever made here.

Tech Tip du Jour: IDS trainer available!

From here.

This is apparently doing the rounds at IBM.

Tech Tip du Jour: Update them stats!

People are often overly paranoid about running UPDATE STATISTICS, running it too often, or running them at too high a level, or, worst of all, running the wrong stats.

So, to keep it simple, let's start with what is a bare minimum:

Do an UPDATE STATISTICS LOW on your entire database every three months.

Are you still experiencing any unduly slow queries? Are the underlying tables very active? Perhaps try UPDATE STATISTICS LOW on those tables monthly, or even weekly. If you find that UPDATE STATISTICS fixes things for a while, but then performance tails off, you probably just need to UPDATE STATISTICS more often. However, more than once a week is probably an indication of a different problem.

So, assuming you still have query problems, it's time to start taking time to look into other levels of statistics. However, do not assume that UPDATE STATISTICS MEDIUM is necessarily any better than UPDATE STATISTICS LOW. I've seen more pain from this assumption than I care to remember.

Identify the queries which are still running badly (onstat -g sql), identify the tables that they query (you will almost certainly find one or two tables are common to most of them) and fix the statistics on these.

And now it's time for me to slip into lazy Delia mode: in here is a program called dostats.ec, written by a very clever man called Art Kagel, who has saved us all a lot of trouble -- compile it and run it against the previously identified tables. The temptation may be there to run it against the entire database, but don't succumb, because in many cases it will just be wasted disk space and wasted cycles.

Think of your carbon footprint!

Just kidding! But seriously, don't overdo your stats, OK?

Tech Tip du Jour: CPU load

Today I'm going to take a brief step outside the Informix box and talk about sar and vmstat and one very particular issue I've seen: if sar or vmstat (or whatever) reports that you have less than an order of magnitude more %user than %sys, you have got a problem.

Let me illustrate:


informix@freebnort:/home/informix
pts/4:ksh$ sar 5 12
Linux 2.6.16.42-0.10-smp (freebnort) 07/08/08

22:25:12 CPU %user %nice %sys %iowait %idle
22:25:17 all 10.66 0.00 12.52 0.00 76.83
22:25:22 all 9.13 0.00 12.30 0.00 78.57
22:25:27 all 12.04 0.00 12.76 0.00 75.20
22:25:32 all 11.46 0.00 12.69 0.00 75.85
22:25:37 all 10.14 0.00 12.69 0.00 77.17
22:25:42 all 12.81 0.00 13.17 0.00 74.02
22:25:47 all 12.15 0.00 12.71 0.00 75.14
22:25:52 all 13.76 0.00 13.10 0.00 73.14
22:25:57 all 21.79 0.00 12.89 0.00 65.32
22:26:02 all 14.84 0.00 12.64 0.00 72.53
22:26:07 all 12.11 0.00 12.47 0.00 75.42
22:26:12 all 13.43 0.00 13.18 0.00 73.38
Average: all 12.86 0.00 12.76 0.00 74.38

informix@freebnort:/home/informix
pts/4:ksh$


(Pardon the formatting, blogger is doing my head in on this.)

As you can see, the %sys is the same order of magnitude as the %user. Given that this server is only running IDS, something is wrong. The %user should be at least an order of magnitude greater than than %sys. In general, if a database server is only a database server, then if %user is 10%, then %sys should be about 1%. If the server has a lot of connections, this might get a bit higher, but %sys shouldn't generally be more than about 3% if %user is about 10%.

Things to watch out for are:

1. Swapping, especially if %sys is significantly greater than %user and the server doesn't seem to be doing anything.
2. Very high rates of network activity, such as lots of connect / disconnect activity; or poorly configured connectivity, leading to excessive transmission of small packets, rather than fewer transmissions of larger packets.

Obviously, there could be many other causes, but these are the most common.

Tech Tip du Jour: How much data are you moving around?

This one is a really obvious one, but it's so often overlooked in this day and age of experienced programmers who are inexperienced in the ways of databases: think about the amount of data you're moving around in those SQL statements you're bandying about.

Scenario 1:

Table tablea contains a BLOB/CLOB/BYTE/whatever. Programmer uses SELECT * on the table when he doesn't need to access the large object.

Result? Poor performance and massive overhead on the server (and network) for no reason whatsoever.

Fix? Only select the columns you actually need, whether the table contains a large object or not.

Scenario 2:

Table tableb contains 16 different versions of a LVARCHAR(1000) field. This table is the core of the system and is hammered all day long by thousands of users. Only one version of the LVARCHAR is actually ever valid at any given moment.

Result? Massive IO load on a server that should really be doing bugger-all. Pointless excess load on the network.

Fix? Normalise the LVARCHAR into a separate table with a version number.

Scenario 3:

Table tablec contains a large object. The application has been coded in such a way that rows in tablec are read multiple times when the data has not changed between reads.

Result? Poor performance and massive overhead on the server (and network) for no reason whatsoever.

Fix? Only select rows when you actually need them, whether the table contains a large object or not.

Scenario 4:

Programmer needs to sum the values in a column for a given set of rows. He uses "SELECT * FROM tabled WHERE foo = bar" to pull the required rows into Java objects, then manually sums the values by inspecting the objects. (Really, I'm not making this up.)

Result? Unnecessary network load and poor performance.

Fix? Learn a bit of SQL, and try SELECT sum(columnname).

I make no apologies whatsoever for the apparent fatuity of this post. Programmers out there are making these exact mistakes right now.

Tech Tip du Jour: The Sacred Mysteries of the Threshold of the B-Tree Cleaner

Non-geeks may want to look away now. And I know I've done this before, but ...

The B-Tree Cleaner is one of the least understood aspects of the Informix engine. I know this, because I don't understand it at all. However, what little I do know is this:

1. Its job is to clean indexes of entries marked as deleted and rebalance them
   
2. It can cause apparently bizarre performance issues

The reason it causes so much grief is often down to an application design issue: if you have one or more tables where index entries are frequently deleted, then the B-Tree cleaner process has an abnormal amount of work to do. And it shows up as "inexplicable slowness, given the expected workload." It often appears in incomprehensibly long checkpoints, because the IO system gets tied up with the rebalancing and doesn't have the bandwidth to do checkpoints.

Here's something I've seen often: do you have a table that you use as a queue? Where something comes in, gets fiddled with and then gets deleted? That's your problem right there, especially if the table is very active but stays roughly the same size as inserts are roughly matched by deletes. What's happening is this:

1. Row gets deleted
2. Index entry gets marked for deletion
3. B-Tree cleaner notices the new marker, deletes the entry and tries to rebalance the index
4. New row gets inserted, buggering up the newly-balanced index
5. GOTO 1 (don't try this at home kids, it rots your teeth and makes your feet itch.)

The essence of this is that the B-Tree cleaner is permanently rebalancing indexes for no real reason and achieving bugger-all at great resource expense. So, the way around this is to tell the B-Tree cleaner not to start cleaning at the drop of a hat, but to wait for a certain amount of activity before it does anything. You do this by increasing the threshold, let's say we increase it to 1 million. This means that rather than rebalancing the index every time a row is deleted, the cleaner waits until 1 million hit against that index have occurred and then it tries to rebalance it.

The chances are that the rebalance will still achieve bugger-all, but because it's not happening so often, the server has more CPU time and IO capacity to do useful things and performance gets much better. It's not helped by the fact that such tables tend to have a zillion indexes on them, because they're generally quite central to a system. So not only do they have lots of rebalances called for, but the rebalances affect a large number of indexes, making the problem even worse. And if the table size is stable but large ... oy, vey!

The way that you can assess the need to set the threshold is onstat -C hot:


IBM Informix Dynamic Server Version 11.50.FC1 -- On-Line -- Up 3 days 10:08:55 -- 20575966 Kbytes

Btree Cleaner Info

Index Hot List
==============
Current Item 2 List Created 01:24:22
List Size 1 List expires in 299 sec
Hit Threshold 5000 Range Scan Threshold 100


Partnum Key Hits
0x079002A3 1 9876 *
0x079002A2 1 8877 *
0x079002A7 1 8008 *
0x079002AB 1 7777 *
0x079002AD 1 6878 *
0x079002AA 1 6444 *
0x079002A9 1 6321 *
0x079002A1 1 6001 *
0x079002A0 1 5448 *
0x079002A4 1 5333 *
0x079002A6 1 5078 *



If this list is always long, and if the "list expires in" is always close to 300 seconds, then the chances are your threshold is too low. Be bold in increasing the threshold. Looking at the list above, I'd start by multiplying the threshold by 10. (I bet you'll see an improvement in performance right away!) If you still see the "list expires in" pretty consistently above 295 seconds, multiply it by 10 again. Keep going until you get to a point where "list expires in" drops below 290 most of the time. Don't worry if it gets to zero, that's a good thing.

(By the way, the asterisk at the end of each line above means that the particular index has been processed. If you run onstat -C hot -r 1, you will see the asterisks "run down" the line as each one is processed.)

When I've sobered up (next week, probably) I'll have a go at the range option, and then wait till I'm drunk again to talk about ALICE.

Tech Tip du Jour: The Idiot's Guide to Disaster Recovery

Non-geeks may want to look away now.

Well, now that I've stopped laughing, I guess I'd better get on with more serious issues, like what a DR plan should really look like.

A lot depends on what your availability requirements are, how critical your data is, and mostly, sadly, what your budget looks like. For the sake of the argument, I'm going to assume that your data is critical. If nobody cares about the loss of data, cool! I've never met anyone in that fortunate position, though.

So, anyway, here's "The idiot's guide to disaster recovery."

Hardware

Hardware is definitely getting more reliable ... on average! However, it's a brave man who bets the farm on his hardware never failing.

There are things that you can do with hardware to ensure greater availability, such as RAID disks, server clusters, redundant hardware, etc.

It's still a very brave man who bets the farm on his hardware never failing!

Backups

If your budget is low and your data doesn't need to be available all the time, then a backup is a good, simple solution.

You do need to be aware that restoring a backup often is slower than taking the backup, and you need to be aware that restoring multiple levels of backups and restoring hours worth of logical logs are also going to be depressingly slow. For that reason, I always advocate taking only Level-0 (and not Level-1, Level-2) backups if you can get away with it. Secondly, unless you're running a data warehouse that you can rebuild from scratch, ALWAYS back up your logical logs. To a tape. Backing up logical logs to /dev/null is quick, but the restore takes forever!

Another option to consider if you are using a SAN and have a massive amount of data is to take "business continuity volume" backups: you have your disk mirrored within the SAN, pause Informix, break the mirror and let Informix continue. You then take a backup of the off-line, broken mirror copy. When you restore the mirroring, the SAN "catches up" the mirror to the online disks behind the scenes and all is well.

Update: Generally you wouldn’t resync until immediately before you wanted to split again. One reason is that for some advanced SANs (and BCV implies EMC Symmetrix, to which this does apply), the syncing software maintains an list of the pages that have changed between the two copies since the split. This potentially allows for exceptionally fast restore: suppose you had a 1Tb database but a delta of page changes of only 10g a day, you could expect to do an external restore in a few seconds.

Hat tip to Neil Truby of Ardenta for the technology-specific advice.

HDR

If you can spare the extra cash for a High-availability Data Replication secondary, you are onto a winner: you can still take backups to keep things safe and sound in the worst case (and I will assume that you will always take backups!) but you can also keep your data immediately available. It's very easy to set it up, if a little pernickety about how exactly the primary and the secondary have to match up. HDR works by taking an exact copy of what is happening on the primary and applying it continuously to the secondary via the logical logs. The secondary is also available for read-only work like reporting, but you have to remember that it's also doing all the work that the primary is doing, alongside whatever else you're putting on it.

With 11.5 and the connection manager, you can hide failover pretty much completely from your users as well, so they would probably never notice the database going down. (Developers still have to code for a potential failure, though! The connection manager will not re-submit any failed SQL to the "new" server.)

RSS

One of the most common observations I've heard about HDR is "we'd really like to have more than one secondary!" Well, with IDS 11 and Remote Standalone Secondaries, you can. Kind of. It's conceptually the same as an HDR secondary but there are differences: it isn't guaranteed to be synchronised at checkpoint time, it uses a different communication protocol and an RSS cannot be promoted directly to a primary. It can, however, be promoted to HDR secondary. And you can have as many RSS nodes as you like!

RSS is effectively another level of backup, it doesn't do much for availability, but does offer the ability to do a very far off site backup or replicate the database instance out over a WAN, where HDR might suffer because of network latency.

SDS

Shared Disk Secondary technology seemed a bit odd in IDS 11.1, after all, what really is the point of being able to point multiple servers at a shared disk? You'd have to be running very slow servers and/or insanely fast disk to justify it.

However, even with IDS 11.1, there is a fantastic upside to SDS: failing over a primary to an SDS node is very quick and painless.

In IDS 11.5, Redirected Writes make SDS a much more compelling option. You can make a clustered server more or less instantly. Setting up a basic 4-note SDS cluster can easily be done in a day, starting from the IDS install point!

SDS gives you a fantastic availability (and performance) option, but does nothing for recovery if you lose the disk.

ER

Enterprise Replication is the ultimate in flexible replication for increased availability and performance. It is entirely flexible in how you choose to deploy it, you can replicate everything in a multi-node update-anywhere model, replicate some columns of some rows of some tables, disseminate out, consolidate in, forests and trees: "the possibilities are endless."

However, the more flexible the topology you choose, the more work you have to put into designing it. Even a simple two-node update-anywhere topology is, relatively speaking, a lot more effort to configure than the comparable "HDR with Redirected Writes" setup. However, ER has one crucial advantage over HDR, RSS and SDS: it allows rolling upgrades, because the versions of Informix on different nodes do not have to be absolutely identical.

Your DR plan

So, what should your DR plan look like?

• Well, you should ALWAYS take backups.
• RAID1+0 is the next step.
• If you can afford it, go for HDR.
• If you can afford even more, go for RSS.
• If availability is critical, go for SDS.
• If you need rolling upgrades and 24x7 uptime, you're going to need an ER update-anywhere stack in there somewhere.

In a truly resilient environment, you'd probably have a number of ER update-anywhere nodes, possibly quite widely geographically dispersed. Each one of those nodes would be an SDS cluster pointing at a high-speed SAN and also an HDR cluster, with the HDR secondary pointed at a different SAN or RAID. Each of the ER nodes would also have at least one RSS server backing it up in yet another distant "bunker". And a lot of money.

Tech Tip du Jour: Disaster Recovery

Blogging has been light this weekend, as I spent some time with an old (very old, in fact) crony of mine. We swapped a number of war stories, and I had to pass on a couple of amusing ones:

Can you patch us back up? We're in a bit of a hurry!

I just had to laugh. It was that, or cry.

Customer: "Hi, we've had a crash, we're restoring the backup but it's taking too long because we have level 0, level 1, level 2 and eight days of logical logs to restore. We can't wait for the logs any more, can you just patch the system 'up' for us?"
Long-suffering engineer: "Yes, we can, but you are aware that if we do this, your system could be inconsistent?"
C: "Yes, it's fine, where do I sign and can you do it immediately, please?"
LSE: "OK."

Everyone's happy, right? Well, kind of. Six weeks later:

C: "Hi, sorry to bother you, but our data seems to be inconsistent, and we can't figure out why. Is it possible that you could fix this for us?"
LSE: " ... "

We don't have enough disk space, so let's just expire the storage pools quickly.

Customer, proudly walking me through their (not so) massive storage manager setup: "We had a real problem with getting enough space on the Legato servers, but we managed to work around it by expiring the logical logs storage pools after 20 minutes."
Me, edging for the door in a suitably restrained fashion: "And how long did you say it took to restore your level 0? Two hours?"

The moral of the above stories is: don't assume that because you're taking a backup, everything in DR-land is cool.

1. Test your recovery process before you need it in anger.
2. Get your DR plan vetted by a disinterested expert.
3. Assume the worst and don't just depend on backups for getting out of a bad situation -- look at HDR and / or RSS as well.

Tech Tip du Jour: Fragmenting Strategies

Thanks to some wittering from the peanut gallery, I've decided to get onto this a bit earlier than anticipated. Bastards.

There are a number of reasons why you might want a fragmentation strategy:

1. You may want to eliminate dbspaces from being queried in order to increase throughput or concurrency
2. You may want to invoke parallelism
3. You may want easier data maintenance

There may be other reasons, but that's all I can think of for now.

Fragment Elimination
The reasoning here is that you would say "we'll put all department X's data in dbspaceX and all department Y's data in dbspaceY" (or some other strategy) and then when a user from department X logs in and does work, the engine will automatically exclude dbspaceY from being queried.

The problem is that in real life, data rarely gets separated out so clearly. And while that approach might work for application A, application B might have a different fragmentation requirement, rendering the given approach utterly useless.

But it's great if you can get there.

Parallelism
It doesn't really matter which fragmentation strategy you use, parallelism is always a gain. If you can't work out a fragment elimination strategy, use a mod function for even data distribution, or use round robin fragmentation. You will find that bulk operations like index builds become massively quicker, especially if you also fragment the index that you are building. You can also get dbspaces scanned in parallel, which is great for big aggregation operations.

However, don't use round robin with attached indexes -- this effectively renders the indexes useless.

Data Maintenance
One of the more common uses for fragmentation is the ability to detach fragments into separate tables, archive them and then drop the detached tables. This is a way doing very quick data purges. This is most common in data warehouses where data is dropped on a month by month or year by year basis. Just remember that your indexes either have to be attached, or fragmented on a similar strategy (the dbspaces can be different, but otherwise the strategy for the index must be the same as that for the data) or the index will be dropped and rebuilt by the engine when you detach a fragment.

Placement
Even if you don't want to make use of any of the above, there is something to be said for placing your data and indexes explicitly so that indexes for a table are in different dbspaces to the data (although this is probably more true of JBoD setups, where you know exactly where things are in disks -- it's probably not so relevant on RAID or SAN.) This will at least make it possible to use onstat -g ppf properly, though!

I somehow feel that this subject might get re-visited.

Tech Tip du Jour: The RIGHT Index

I frequently post about queries not only using an index, but using the right index.

So, what is the right index? Well, it's an index that supports your query as fully as possible. For my examples, I'm going to consider the stores demo database that ships with IDS. It's tiny and it's pretty much optimised, but I'm going to use it to show examples of what I mean.

So, here's the schema:

{ TABLE items row size = 18 number of columns = 6 index size = 30 }
create table items
(
item_num smallint,
order_num integer,
stock_num smallint not null ,
manu_code char(3) not null ,
quantity smallint,
total_price money(8,2),

check (quantity >= 1 ),
primary key (item_num,order_num)
);
revoke all on items from "public";

alter table items add constraint (foreign key (order_num)
references orders );
alter table items add constraint (foreign key (stock_num,
manu_code) references stock );

Here's my test query:

SELECT order_num FROM items
WHERE stock_num = 8
AND quantity > 3
ORDER BY order_num

Here's my first query plan:

select order_num from items
where stock_num = 8
and quantity > 3
order by order_num

Estimated Cost: 1
Estimated # of Rows Returned: 1
Temporary Files Required For: Order By

1) items: INDEX PATH

Filters: items.quantity > 3

(1) Index Keys: stock_num manu_code (Serial, fragments: ALL)
Lower Index Filter: items.stock_num = 8

Filters are generally a badness. They indicate that the data row is going to have to be inspected. This isn't a problem with a 67-row table, but it can lead to a lot of pain with a 67-million-row table.

Now I: CREATE INDEX foo1 ON items (stock_num, quantity);

And the plan changes to:

select order_num from items
where stock_num = 8
and quantity > 3
order by order_num

Estimated Cost: 1
Estimated # of Rows Returned: 1
Temporary Files Required For: Order By

1) items: INDEX PATH

(1) Index Keys: stock_num quantity (Serial, fragments: ALL)
Lower Index Filter: (items.stock_num = 8 AND items.quantity > 3 )

Notice that there is no longer a filter needed to satisfy the query. But I can improve this query even further: although all the restriction is done via an index, I still have to go to the data rows to get the projection.

If I now: DROP INDEX foo1; CREATE INDEX foo1 ON items (stock_num, quantity, order_num);

The query plan is now:

select order_num from items
where stock_num = 8
and quantity > 3
order by order_num

Estimated Cost: 1
Estimated # of Rows Returned: 1
Temporary Files Required For: Order By

1) items: INDEX PATH

(1) Index Keys: stock_num quantity order_num (Key-Only) (Serial, fragments: ALL)
Lower Index Filter: (items.stock_num = 8 AND items.quantity > 3 )

Notice the new "Key-Only" expression. This means that in order to get everything that I needed, I only accessed the index pages, I did not have to access the data pages at all. This is generally optimal.

Now, that was a fairly straightforward case. A more subtle case arises when you have to accept that you can't create an index and you have to choose between indexes:

For the sake of the argument, let's say I can't create any more indexes. We revert to the original index structure and my new query is this:

SELECT order_num FROM items
WHERE stock_num = 8
AND item_num = 1
ORDER BY order_num

My query plan is:

select order_num from items
where stock_num = 8
and item_num = 1
order by order_num

Estimated Cost: 1
Estimated # of Rows Returned: 1
Temporary Files Required For: Order By

1) items: INDEX PATH

Filters: items.item_num = 1

(1) Index Keys: stock_num manu_code (Serial, fragments: ALL)
Lower Index Filter: items.stock_num = 8

Is this my best option, given that I have a unique, primary key index on item_num and order_num and a non-unique, composite index on stock_num and manu_code? Well, in this case, yes, because the composite index will be much more selective. Every order will have an item_num of 1, so using the primary key index would be useless. However, if 99% of orders only had one line and we were looking for things on line two, then the primary key might be more useful.

Good optimiser statistics would be your best hope for the engine getting this decision right, but I have on one or two occasions found that the optimiser gets the decision wrong, possibly because of the comparative size of the index or some other, subtle consideration.

Know your data, know what the query is doing and make sure the optimiser is doing the right thing.

Never assume that just because a query is using an index, it's using the correct index.

Update: a minor correction to one of my CREATE INDEX statements, thanks to Ian Goddard for spotting it.

Tech Tip du Jour: Fragmenting Tables (At All)

Non-geeks may want to look away now.

It never fails to amaze me that otherwise bright people seem to think that just using RAID or a SAN is sufficient to spread data around.

Informix was completely rebuilt to make best use of SMP technology, which means that it likes to parallelize tasks where it possibly can.

But Informix cannot be made aware of operating system-level data distribution. If you want to use things like fragment elimination, PDQ, etc., then you have to use Informix's fragmentation. (You can, of course, use this on top of RAID / SAN / whatever.) But if you just use the storage subsystem to partition or fragment the data, then as far as Informix is concerned, it's all in one big pot.

This can not only make all the desirable parallelisation options unavailable, but can also lead to disk contention, long checkpoints, teenage pregnancies and athlete's foot.

I'll post some ruminations on actual fragmentation strategies in the future.

Tech Tip du Jour: Benchmarking

Non-geeks may want to look away now.

Yes, kids, today we're going to talk about benchmarking. And I know you're all salivating at the prospect. If there is one thing that is guaranteed to get every Informix DBA moist and emotional, it's benchmarking.

Why doesn't IBM release a benchmark? Why doesn't IBM release this benchmark, or that benchmark?

But I'm not talking about that! I'm talking about when last you benchmarked your own environment. Have you ever? In 99 out of every 100 sites I've been to, the answer is "no".

So when users come up to and say: "the system is slower than it was last week", can you refute them? Do you have a representative suite of tests that you can run that reflect the consequences of increased user load and of increased data size? Can you plot a trend to show when you're likely to break your SLA so that you can get kit upgraded in time?

No? You answered "no" to all of the above?

Well, then, perhaps you need to think about writing a set of benchmarks -- they don't need to be complex, some timed dbaccess scripts will be fine! -- that will slow down as you database grows. You need to set up a separate, static database that you can use to show the consequences of increased user load.

So: take a number of your most common queries, preferably including some that aggregate all your data (what is our average order value, what is our most valuable customer?) as well as some that pull out OLTP data (which items belong to order 4285, what product is associated with product code 1037?) Write dbaccess scripts to time these, execute the scripts and record the times.

Then, create a reasonable subset database, which is static for all time. Execute the same queries against the subset database and records the times.

These performance tests should be run every day, via cron, when the system is idle, when it's busy and when load is general average. Make sure the data is kept in a way that it's easy to graph. Review the graphs regularly, you might be surprised at what you find.

(If you've got 11.1 or 11.5, you'll find nice ways of integrating this with OAT and dbcron. That's also good. But if you're on 10.0, 9.40, 7.31, 5.20 or SE, do it the old-fashioned way.)

Do your users use ODBC or JDBC? Make sure you have a full suite of network-based tests as well. I can't tell you how many times having a ready suite of network-based tests (simple things, like SQL statements in cells in Excel connected via ODBC) saved my bacon when people came round and said "The database is slow." By showing them a quick test of the Excel suite and comparing it to historical data, it not only showed that the database was fine, but that the network was fine, too. So that meant that the problem was in the application code. Quelle horreur!

I can't be more specific than that, because each database is different, each application requirement is different. But when you have done it, you will have a tool that is worth a thousand TPC benchmarks to your organisation.

Update: I managed to raise the ire of Fernando Nunes over at the Informix Technology blog, he wrote on comp.databases.informix:

I would not disagree with the points you make, but one particular point almost makes me angry... Let me quote you:

"the system is slower than it was last week", can you refute them?

Why the hell should we refute users claims? This reminds me of my "daily wars" with people from all areas (DB, developers, OS, network, SANs etc). Everybody's concern is to make clear that it's not their fault, when everyone should provide information that would make it easier to find out what is causing the issues...I strongly believe that the users don't complaint without a reason... So what we should "benchmark" are activity metrics... disk reads, full scans etc.

On IDS 11.x it's easier to collect them, but it's relatively easy to collect them in any informix version. Some simple onstats or sysmaster queries can be very valuable...

My responses are:

Why the hell should we refute users claims?

Mostly, because they're talking out of their arseholes. You have to understand this is only my experience, but after twenty-five years of doing this for a living, I can count the number of genuine instances of the system actually being demonstrably slower on the fingers of one hand.

By contrast, I get told that "the system is slower" about once a week. So, on average, one out of every 260 times I get told the system is slower, it actually IS slower. The other 259 times, the user is talking shit.

Everybody's concern is to make clear that it's not their fault, when everyone should provide information that would make it easier to find out what is causing the issues...

Well, I think I highlighted that in my example: I exonerated the database and the network by demonstrating something that even a CIO can understand: how long it takes to do a repeatable operation. This was important for finding the root cause.

what we should "benchmark" are activity metrics... disk reads, full scans etc.

onstat information might mean something to you and me, but it don't mean shit to a CIO. Time is something everyone can understand. Both things have their place.

In an ideal world Fernando would be completely right and I'd be completely wrong. But in my experience, we're not in an ideal world, and you're probably better off listening to both of us, rather than one of us.

Tech Tip du Jour: Variable Length Records

Nothing pisses me off more than people who use variable length records.*

People coming from an Oracle background seem to be very keen on the idea, I suppose when your database is so shit that you have half the world's hardware to keep your CRM system up, it doesn't really matter. But actually, unless you really do have very variable data, variable length fields are a bad idea:

1. Variable length fields all have a storage size overhead. I've seen far too many fucking idiots with VARCHAR(1) and VARCHAR(2) in their tables.
2. People ALWAYS say: "well, since it's a VARCHAR, we'll make it the maximum possible size, because there's no downside to that." Well, guess what? There fucking is: when the engine inserts a variable length row, it checks to see if the maximum possible length of the row would fit in the remainder of the page.
   This means that if your maximum row length is more than 50% of your page size, each row will go on its own page, even if the actual row is only 5 bytes long. This is fixed in 11.1, but only if you a) configure MAX_FILL_DATA_PAGES and b) unload and reload all your data. Plus, the engine has extra work to do: without MAX_FILL_DATA_PAGES, the engine has to calculate the maximum row size; with it, the engine may have to move the row to another page if an UPDATE increases the row length sufficiently.
3. I can count on the fingers of one hand the number of times I've seen VARCHARS and LVARCHARS used for a justifiable reason. Inevitably, people use VARCHARS because they're too fucking lazy to do any kind of analysis on the contents of their data. This is the bit that really pisses me off, because it's an indication that there's going to be lots of shit you need to pick up in years to come. Don't be a cunt, do the spade work -- it always pays off in the end.

*This is a lie. But not much.