Subject CPU 100% When TXNs exceed to 200 per sec
Author loupisp
We have built a system which periodically (once to twice per week)
receives between 100 and 300 data records per second for a period of
about 2-3 hours. The records are inserted by a multi-threaded (20
cached threads) D7 application where each thread has it own db
connection. For each record inserted into the database 2 triggers
and one stored procedure (4 small sql queries) are run with the last
trigger raising an event. A windows service "hears" the event via a
TIBEvent object and then collects unprocessed records from the
database. Each record is then "given" to a thread in a thread pool
within the windows service for processing. Each of these threads also
use their own database connection to alter the record that it has
been assigned.

The problem: When "burst" volumes of data are pumped into the
database the CPU usage shoots up to 100% and stays there causing time-
out errors which has a severe knock-on affect. We are using Firebird on a 3Ghz server with 2Gb ram and 2 SCSI HDDs. The drives
report VERY LOW usage but the CPU is through the roof. The
applications "pumping" the data into the database and the Windows
Service run on a separate machine (Windows 2000 Server, 2.4 Ghz, 1Gb
RAM, 80GB IDE drive). I would like the following information from
anyone that can help.

1-How many concurrent connections can/should a Firebird Server be
able to cater for? I have currently got about 120 connections to the
one database for the above architecture!
2-Raising Events so often in Firebird/D7, has anyone else
successfully done these kind of volumes using events?
3-Has anyone else run more than one database on one server that are
simultaneously receiving large volumes of traffic?
4-Any information or ideas of what else to look at would be very
appreciated at this point.

Thanking you in advance