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:
- Its job is to clean indexes of entries marked as deleted and rebalance them
- 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:
- Row gets deleted
- Index entry gets marked for deletion
- B-Tree cleaner notices the new marker, deletes the entry and tries to rebalance the index
- New row gets inserted, buggering up the newly-balanced index
- 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.
No comments:
Post a Comment