Subject Re: [IB-Architect] Interbase Culture and Open Source
Author Jason Wharton

>What tuning parameters have you used? Which are most important? Any
>areas where you think you would like parameters and don't have them?

Perhaps the most important case where I needed the ability to define a
tuning parameter was doing full text searching. We take care of the
corporation, partnership, trademark, tradenames, etc. and by law we are
required to assure that each registered name is not deceptively similar to
another name. This entails a fairly involved search system. To make a long
story short, I needed the ability to dynamically create a plan based on the
search criteria the user was submitting. I have to be able to handle exact
match, partial match, soundex match and metaphone match on a word-by-word
basis. Mix into that antonyms and synonyms and you've got quite a little
mess on your hands.

Like I've alluded to before, I would absolutely love it if Jim were to come
up with something that would make it so that I wouldn't have to figure out
the proper plan. (That was a lot of work!) Without this the the optimizer
comes up with on its own which would never complete on any hardware in a
reasonable amount of time. If I wasn't able to take the time and figure out
how to build a PLAN as I build the SQL (dynamically depending on the user's
search requests) I would have had to go with another database product.
Rubicon was unfortunately not sophisticated enough to handle our case
either. In our case, it isn't a matter of satisfying users whims, it's
meeting the statutes written in the law.

Other things have been the ability to get about 8 databases all running on a
single MPS machine. HP NetServer quad processor NT Enterprise OS, etc. It
has been a real challenge to get each application/database to make sensible
cooperative usage of the RAM available on the server. We have 512 MB of RAM
and consistently it is using a max of about 128KB of it because I am only
confident to give each database a portion of RAM based on the crazy
calculation of expected users, buffer sizes, etc. If one database got pegged
it would (theoretically) blow the memory usage out the ceiling. InterBase
seems to expect that only one GDB is active on a machine when it comes to
accurately tuning its memory usage. There doesn't seem to be the ability to
consider multiple databases and their various load requirements. This server
is pretty much our dedicated InterBase machine and it would really be nice
if we could better utilize the memory and processor capacities that it has.

Either more intelligence needs to be build into the server (which would be
super-nice) or there should be a little more flexibility built into the
server that would allow me to tune a database's usage of system RAM more
intelligently by considering the impact and current load of other databases
on the system. I consider this a sore lacking point in InterBase right now.
BUT, we are living with it because I don't forsee that the alternatives are
going to be a whole lot more congenial.

Other more obvious things was weathering out the various bugs encountered
along the way. We got bit by the sweeping bug pretty hard. Once the solution
was discovered it was nice that we had the option to have enough control of
the insides of the database that we could turn off the automatic sweeping
and avoid the bug. If that tuning parameter wasn't there we would have to of
gone back to a previous version of InterBase. Which, unfortunately would
have had other bugs (problems with exception handling in stored procedures)
which would have really upset the way we were doing things.

One other thing was the ability to have true backup capability. I think this
could be a great addition that the current architecture wouldn't have to be
changed too much to accomodate. It could also serve as a crude form of
replication if backup delta's were compressed and shipped out on a daily
basis and applied to the 1,000's of remote locations available. Some years
ago I was tasked to evaluate all the existing client/server databases and
recommend what U-Haul International's next version of their POS would be
founded upon (2nd largest POS in the US at the time). I am certain that if
this capability were in InterBase I could have convinced them that InterBase
was the right solution. Needless to say, they still went with MSSQLServer6.5
against my recommendations and I started looking for a new employer. I knew
that we could write in our own replication without too much trouble which
wouldn't have been as efficient but servicable. The replication that
Synetics provides isn't the kind of replication that this system needed

I'm sure there is more I could add to this and if something notable comes to
mind I'll be sure and share...

Thanks for taking the approach you did! Sometimes I feel like people just
want to disagree with me rather than really get to the heart of what I'm
trying to say... (which I'll admit isn't always that clear)

Kind regards,
Jason Wharton
CPS - Mesa AZ

Developer's Search Site!