> SuperServer should not have this issue

Classic SMP should not have this issue, unless the query is slamming
other resources also.

-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Larry Hengen
Sent: Wednesday, January 24, 2007 5:42 PM
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.





++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Resources item on the
main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Yahoo! Groups Links




Disclaimer: This message (including attachments) is confidential and may be privileged. If you have received it by mistake please notify the sender by return e-mail and delete this message from your system. Any unauthorized use or dissemination of this message in whole or in part is strictly prohibited. Please note that e-mails are susceptible to change. RxStrategies, Inc. shall not be liable for the improper or incomplete transmission of the information contained in this communication or for any delay in its receipt or damage to your system. RxStrategies, Inc. does not guarantee that the integrity of this communication has been maintained nor that this communication is free from viruses, interceptions or interference.