Thanks for your feedback and insight into your experiences with high
txn volumes, much appreciated. I also must apologise the volumes
that I indicated are per minute not per second. Apologies to all...

My suspicions are being confirmed, I am convinced that there are too
many concurrent transactions or that firing an event for every record
is causing issues.

Based on your response I am settling on the the number of concurrent
connections to the database. They all get very active during this
busy period and this must be killing the CPU!!

I am now looking into the best way to utilise connections using the
TIBDatabase object, probably a limited connection-pool of some sort.



--- In firebird-support@yahoogroups.com, "Gary Benade" <hobbit@l...>
wrote:

> > 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.
> > 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.
>
> My 2c. Wow. If you are handling 300 records per second on one CPU,

the CPU

> only has 0,003 seconds to handle each record. Thats if you are

running only

> one thread. With 20 threads the situation is 20 times worse in the

region of

> 0,0001 sec. There is no way that what is essentially a desktop

machine can

> handle the insert and fire 2 triggers and run 4 queries in that

time. I

> can't think of anything that could.
> If you are caching the 300 records per second in some kind of raw

format and

> then allowing the data to be inserted by the 20 threads as fast as

they can

> the only real problem I can see is that a 20 threads is far too

many for one

> CPU. My server apps have 8 threads running batch inserts and

updates and

> that seems to be about the limit, and CPU usage of 100% is to be

expected. A

> multi processor system is essential so that FB and your app dont

starve each

> other of CPU time.
>
> This always makes me wonder what kind of hardware the boys at

Google are

> running :)
>
> Hope you get it sorted out,
> Gary