Subject Re: [firebird-support] Firebird, ODBC, web access and various beginner
Author Thomas Løcke
Svein Erling Tysvaer wrote:
> Sure, that reduces your options.

Well, I'm sure if I had more skills, I would've had more options - or
even better: If I had a lot more money, I could afford to re-program the
entire thing! :o)

> But there are things that you should still be able to do, you ought to
> be able to get the database statistics when Firebird is working heavily
> (the difference between oldest active/interesting and next transaction
> and index selectivity can at least be useful).

Well, I have access to the Firebird database file, so if there are ways
to keep an eye on the database while it's working, I would love to hear
about them.

> And I also think you know
> a lot more than what you've told us so far about (approximately) how
> many concurrent users are using the program simultaneously and whether
> the - for both of us - unknown processes within Firebird should be
> demanding or not (e.g. should it select/insert/update/delete one record
> or one million records and are there any complex calculations
> required?).

I'm a secretive guy! :o)

Well, I do of course know how many concurrent users that access the
database: 1.

The only "client" accessing the database is the custom build piece of
software I've told you about. It does so everytime a call is being made
to the system, a call here meaning an actual phone call. Somebody calls
my business, and the first thing that happens is that this software hits
the database to dig out all the data needed to figure out how to handle
this call, based on who is calling, who are they calling, who is
available to handle the call, what voicemail systems are in place to
handle the call. Stuff like that. This query is all reads, no writes (I
believe). When the call is done, some writes are done to the database,
mostly for logging purposes.

As far as I can see, it's not very complicated, heck it isn't even very
resource demanding, except when several calls happen to crash at the
same time. Then Firebird sucks up all my CPU, leaving the rest of the
system in a very poor state. My main goal here and now is getting rid of
that problem. In my mind, and given my circumstances, moving the
database to another server seems like the fastest and easiest solution.

> This of course doesn't improve your program, but may help discovering
> problems with the current implementation or confirming that you really
> have demands that are demanding and do require more hardware or a
> separate Firebird server.
> Set

My way of thinking is this: The developers I've paid to create this
application might not be the brightest DB designers on the planet, but
even so, the damn thing does indeed work. It's just that it, apparently,
doesn't scale very well.

I personally don't think the application lives in a very demanding
enviroment. It doesn't handle millions and millions of hits each day. It
handles a few thousand. The problem is when some of these thousand hits
happen at the exact same time, then the rest of the system suffers,
primarily because the database is eating too much CPU for too long. "Too
long" here is perhaps 5-6 seconds at most, but in those 5-6 seconds we
can easily end up playing the same amount of different .vox files, with
huge amounts of stuttering.

It's impossible for me to move the .vox system unto another server, as
it is impossible to move most of the various programs unto different
servers. The only thing I can move, is the database, and seeing as the
database is also the cause of my "problems", be it due to bad design or
whatnot, to me that seems like the sensible thing to do.

Also there's one thing I believe I've failed to mention: The demand for
online access (web) from my clients are going up. Currently this isn't
something I'm comfortable offering, as the thought of more hits on my
database makes me cry out on pain. Moving the database unto a more
powerful server might enable me to offer some of those online services
requested by my customers.

Is there an easy way to extract the database design, so you can all see
how it's build? It's on Windows, and I currently have iSQL available.