Subject Why shouldn't I switch to Oracle, SQL-Server, ...
Author Adrian Pronk
We've been running with IB6.0 on Linux for about 6 to 9 months (including
production Linux for three months).

During that time, our experience with IB would have to be described as

Here are some of the things that we've encountered:

- Super-Server problems on Linux requiring a switch to Classic-Server,
about August 2000.

- Occasionally, the database suddenly reports some "database corruption:
invalid page type" kind of message. These tend not to be repeatable, at
least, not in the same place.

- Frequently, a normal function will take an abnormally long time to
complete. In fact, it often happens that it simply doesn't complete. When
killed, there is a good chance that a gds_inet_server or interserver will
be left dangling out there using all the CPU on the machine.

- gbak/sweep occasionally refuses to complete. We have left them for over
12 hours and they just relentlessly burn CPU time.

- gbak (restore) failing with a database corruption error. Luckily, this
didn't happen on the second attempt!

Our database is 1.8Gb. We have about 80 tables and 220 indexes (mostly to
support foreign key relationships). Our production client is
java/interclient 1.6 (with ad-hoc isql for nosy administrators).

We run sweep daily (with automatic sweep turned off).

We have had a lot of trouble with the query optimiser making bad decisions
-- the worst being to fail to use a primary key index by itself when it

The trouble is, we simply have lost a lot of confidence in Interbase's
ability to behave predictably. Can you convince me that Interbase is the
solid database that you believe it to be? Therefore, what am I doing

Following is the output of gstat -header for our production database -- We
haven't managed to complete a sweep for a couple of days now, but we've
started one again and it has been running for a few hours.

Database "/xxxx.gdb"

Database header page information:
Flags 0
Checksum 12345
Generation 7901055
Page size 4096
ODS version 10.0
Oldest transaction 142
Oldest active 7765311
Oldest snapshot 7765311
Next transaction 7900933
Bumped transaction 1
Sequence number 0
Next attachment ID 0
Implementation ID 19
Shadow count 0
Page buffers 12000
Next header page 0
Database dialect 1
Creation date Dec 23, 2000 12:09:55
Attributes force write

Variable header data:
Sweep interval: 0
Continuation file: /data/1/xxxx01.gdb
Last logical page: 249999

We are running IB6.0 on Linux (Classic Server). Our database has 79
tables, 79 primary key constraints, 127 foreign key constraints, and 13
other indexes. We use "pure" SQL so we have no triggers, procedures or any
other non-portable constructs. Neither do we explicitly code plans, which
makes query optimisations a nightmare!

We use Java clients using Interclient 1.6, so our database is Dialect 1.

The Production system was started at the beginning of December, 2000 and
the database size is currently 1.8Gb. About five or six tables have more
than 1,000,000 rows.

The database updates consist mostly of inserts, but there are also a
significant number of updates which, of course, change foreign key fields
and alternate index fields.

We have automatic sweeping turned off and manually run sweep every night.

Occasionally, the database suddenly seems to hang. The first time it
happened, one of our jobs that joins a couple of the very large tables to
extract the previous day's data hung. Normally, it would take about 10
seconds to run. This time we killed it after an hour or so. A sweep did
not fix it. In the end, we had to back up the database (without garbage
collection) and restore it. The first restore attempt failed with a data
corruption (that was scary) but it succeeded the second time (anyone know

I have just read that large delete operations mixed with duplicate index
values can kill the garbage collector. Would the updates have the same

Are there practical steps that we can take to prevent these extremely scary
hanging events from occurring?