At 11:03 AM 21-02-01 +1300, you wrote:

>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
>tense.
>
>Here are some of the things that we've encountered:
>
>- Super-Server problems on Linux requiring a switch to Classic-Server,
>about August 2000.

Good move. Linux-SS at that stage was very early beta.

>- 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.

..meaning something is intermittently interfering with your disk writes. A bad network card or cable connector somewhere, probably; worse, maybe even some flukey memory or disk....or, if you have Windows clients, look for ambiguities in connection strings.

>- 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.

Look at unfinished transactions in threads. Threads will eat all the available idle timeslices they can get. Look at that 135,600 difference between the oldest active transaction and the next available transaction. Something in your application architecture could be blocking things up, e.g. snapshot transactions with lock settings on 'WAIT' with no timers on the clients to intervene. As long as you have this type of transaction sitting around, there will be no garbage collection.

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

Consider all of the above - large numbers of infinitely deadlocked transactions may be getting in the way of garbage collection...gbak will keep going back to try...how long does a "normal" gbak take? How much CPU is the gbak thread getting on the occasions when it is slow? Do you have any users who end their working day by switching off the power as soon as the clock strikes five?

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

Look for hardware faults or bad connection strings.

>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).

Do these nosy administrators keep isql queries open and uncommitted for long periods? Do any of them change metadata when users are logged in?

>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
>can.

This generally indicates a structural problem: other indices duplicating the automatic index on the primary is a common offender. Foreign key relationships on columns with low selectivity can defeat the optimizer too. You can do yourself a lot of good by using a tool that displays the plans when you prepare queries. There's an art to tuning your indices to help rather than hinder the optimizer. One advantage of going to Oracle would be that Oracle provides training for people who want to specialize in tuning the DB. One disadvantage is that such specialists are horrendously expensive and you need to have them on your staff full-time because this task is never-ending on Oracle.

>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
>wrong?

I doubt anybody could give you a single set of arguments to convince you. This is powerful software and, like all such products, demands a lot of RDBMS expertise to be perfect. IB gives a lot of satisfaction in the "questing for perfect" zone. On good hardware, a well-tuned InterBase database does not disappoint.

There is a lot of literature at http://www.ibphoenix.com that can help you towards identifying and fixing what ails you. Good expertise (the best!) is available from that site, too - some well-timed professional assistance could be a gold investment at this period of your transition.

Cheers,
Helen

All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
_______________________________________________________