Firebird already has SMP support - it's called classic.

-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com]On Behalf Of Larry Hengen
Sent: Thursday, 25 January 2007 9:42 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Single Query drives CPU to 100% starving other
connections


I am working on a 3 tier Delphi application that I didn't author, and
has some performance issues. Some of the SQL statements executed by
the appserver are dynamically generated from client criteria. If you
optimize the SQL for one, the performance of other statements suffer.

That said, performance on FB2 is great for the most part. The
community is to be commended on the work done to make the product
faster, and more robust while adding features. I have been running
Firebird now since version 1.0 and have had practically no grief with
upgrades.

I posted about this particular issue a long time ago, and still have
no resolution. I recently read another post about 100% CPU
utilization, and have googled all to no avail.

The situation is that 1 query (malformed or not), can drive the
Firebird process to use 100% CPU. When that happens, any other
connection to the database is starved, and if the query in question
takes long enough, the other connections are dropped. This behaviour
has been present since Firebird 1.00, and while responses to my
previous post indicated that SuperServer should not have this issue,
it certainly does.

It was thought that SMP support would negate the problem, but a recent
effort on my part to port the application to use Interbase 2007 showed
that it does not. A query that took a reasonable amount of time to
run under FB2 took 47 minutes to run under Interbase. Since CPU
resources are finite, I would expect that completing the port may
reduce the # of effective application hangs, but not eliminate it.
Even combing through all the SQL and making changes for the
differences in optimizers would not eliminate the issue, due to the
dynamic nature and complexity of some queries. I cannot change the
database design. It is tightly coupled to the application, and the
application is sizeable.

The environment is:
Delphi 7
Firebird 2.0
IBX using GDS32.dll client library
database connections are pooled using a custom class

I have a few questions:

1) When would it be realistic to expect a Firebird 3.0 (with SMP
support) release?
2) Why is it possible for 1 query to monopolize the CPU? What are the
thread and process priorities that Firebird uses? Is there any way
for a user to control this?
3) If I managed to kill the thread in the appserver that was making
the query request, would the Firebird connection be severed, and it's
thread terminated?
4) How do I get this issue addressed? If it's a matter money, my
employer would be happy to pay for it. It's just a question of
payback time.

If you need a test case replicating the issue I would be happy to
provide it, but I may require a ND agreement if I need to supply a
production database to re-create the issue.






[Non-text portions of this message have been removed]