OAT and MON$

Just as a "for your information", should anybody be interested.
The following query on Firebird V2.x gets the attachment that holds the oldest active transaction via the monitoring tables.

SELECT *
FROM MON$ATTACHMENTS
where MON$ATTACHMENT_ID =
(select MON$ATTACHMENT_ID FROM MON$TRANSACTIONS
where MON$TRANSACTION_ID =
(SELECT min(MON$TRANSACTION_ID) FROM MON$TRANSACTIONS))

HPUX Oracle and Itanium

The announcement by Oracle last week that it will stop developing software for the Itanium processor will affect users of Oracle on HPUX Integrity servers. No surprise then that we see the following blog post - Oracle is abandoning HP-UX customers…Fight back! from Ed Boyajian the CEO of EnterpriseDB suggesting that users should consider migrating to their version of PostgreSQL, even though as he admits PostgreSQL has not supported the HP-UX platform very well, but that is now going to change.

For those who are curious, Firebird V2.5 builds just fine on Itanium HPUX (IA-64). See prefix.hpux_aCC and prefix.hpux_ia64 in the builds/posix directory. The only issue we have in providing such builds, is access to the relevant hardware/operating system to do a build, and bringing upto date the HPUX installer.

Forced Writes?

Forced Writes are turned ON by default on Windows since the release of Firebird V1.0.

See the following item on the tracker: CORE-591

But Forced Writes were OFF on Linux even if they were turned ON, until an issue with Linux (Posix) was resolved in Firebird V2.1 Beta 2 and this fix has now also ben backported to Firebird V2.0.4.

See the following item on the tracker: CORE-1476

There was some discussion amongst the Firebird developers about the change (either to OFF on Win or to ON on Linux so we would have consistent defaults), but so far there has not been a final resolution since Windows and Linux handle I/O differently, and its perfectly valid to have different default settings. Before the fix in Firebird V2.1 Linux never did forced writes even when asked to, and pretty much everybody got used to the performance with Forced Writes off, when we turned them back on correctly the performance decrement was definitely going to be a problem.

Basically, Linux writes pages out of the page cache as often as possible, so the file on disk stays pretty much current, whilst Windows writes to disk only when it must, so the difference between the disk and cache pre Firebird V1.5 could be enormous - hours or days of changes if the database file was not closed correctly. So, although it may be preferable that all systems should run with forced writes on, for Firebird on Windows it was pretty critical.

Within Firebird V1.5 we attempted to make sure that the issue of Windows cache, the Firebird cache and what was on disk could not be vastly different even if Forced Writes were turn off by introducing the following two parameters in the Firebird configuration file. However the new parameters do not guarentee complete consistency between the cache and disk, for that you do need Forced Writes turned on.

The parameters:

# ----------------------------
#
# How often the pages are flushed on disk
# (for databases with ForcedWrites=Off only)
#
# Number of unflushed writes which will accumulate before they are
# flushed, at the next transaction commit. For non-Win32 ports,
# the default value is -1 (Disabled)
#
# Type: integer
#
#MaxUnflushedWrites = 100

#
# Number of seconds during which unflushed writes will accumulate
# before they are flushed, at the next transaction commit. For non-Win32
# ports, the default value is -1 (Disabled)
#
# Type: integer
#
#MaxUnflushedWriteTime = 5

Colissimo - What a joke.

If you want to send a package to anywhere in France, La Poste insist that you send it by Colissimo. Colissimo is more "expensive" than normal post, but it has insurance just in case its delivered late, goes missing or whatever you are sending gets broken.

Recently I sent a couple of Firebird mugs via Colissimo to a customer/Firebird user in France. After receipt and opening of the carefully packed box, the customer found that one of the mugs was cracked. After a brief email exchange he agreed to send me the damaged mug and I would send him a new one.

Once I had the broken mug, I asked the local post office for the basic repayment for the damaged mug from Colissimo. Their response was to hand me a form to fill in, and suggest I post the completed form direct to Colissimo. OK - job done - now I sit back and wait for them to send me the money for something they had managed to break.

After a couple of weeks a letter arrives telling me that they are looking into it, two weeks later another letter arrives, this one apologises, and says we cannot pay you the insurance because the reciever of the damaged goods should have filled in the complaint form and not the sender.

WTF? When you receive damaged goods from someone, what is the first thing you do?
Well, I would have thought, you contact the sender, ask if they want it back and ask for a replacement.... Its then the senders problem to deal with the people he used to send it.

Not in France it seems. By now we have paid for three lots of Colissimo (send original, send it back, send another) and wasted a whole lot of time. The result - huge profits for Colissimo, zero service for the customer. I now give up and throw the whole lot in the bin. Life is just too short to start aguing with a "fonctionnaire".

DEC founder Ken Olsen is dead - Silence at the Mill

The Register today contained an obituary to Ken Olsen, the man who created DEC. Reading the article brings back many memories for me. The first computer I worked with in the mid 1980s was a VAX 11/750, along with Dec Professionals. We implemented a distributed accounting system (MAS-M from Hoskyns) for BRS Northern. I then moved to BRS Group where we started writing a more advanced system using the latest DEC software at the time. ACMS, TDMS, RDB etc, I was the DBA responsible for designing and implementing the corporate database. Great times, great memories. I will raise a glass in your memory tonight.

A Quick Start Guide to Index Optimisation

Below a Quick Start Guide to index optimisation, courtesy of Pavel Cisar.

1. Always define ALL integrity constraints: primary, foreign and unique constraints. As this will automatically cover join optimisations.

2. Define a separate index on EACH column you will use for additional constraints in your queries (eg. WHERE clause filter conditions) that are not covered by point 1.

3. Update your index statistics regularly as you add/change/delete data in your database. A good general rule is to update statistics as part of your regular database maintenance routine (when you do a backup, sweep etc.), and/or whenever you add/change a quarter of all the rows, once the table is bigger than 10,000 rows.

4. Once you have a representative sample of real world data in your database, you can then evaluate the usability of the indices to eliminate those that will not help your queries (a worthless index will only slow down your inserts and updates!) and add new composite or expression indices that speed up specific queries.

5. Useless indices are typically those with low selectivity (few distinct values). Run some queries with filter condition on bad cardinality columns and check whether the optimizer uses the index or not. If the low selectivity index is always/often used with other conditions, you can improve it's selectivity and thus usability by
creating a composite index on columns that are used together for filter conditions instead of independent indices on these columns.

6. If you always/frequently use a group of columns for filter conditions, a composite index on these columns can boost the query performance, but do it only if you're not satisfied with performance that individual column indices provide.

Promenade - Chezelles - 2010

Every first sunday afternoon at 2.00pm in September (except for the year it was cancelled due to a clash with an agricultural show), there is a "promenade gastronomique" (a walk where you taste local produce) at Chezelles. The walk this Sunday was 9km (thankfully because the thermometer was showing 29 degress centigrade in the shade).

On the walk, we tasted local wines, goats cheese, jams, honey products and beer, and had a great time.

I have never seen this "walk" advertised, but its very popular. If you are in the area next year at the same time, then it might be worth having a go.

Cost this year was 4 Euros per person.