Subject Re: CPU 100% When TXNs exceed to 200 per sec
Author loupisp
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, "Gary Benade" <hobbit@l...>
> > We have built a system which periodically (once to twice per week)
> > receives between 100 and 300 data records per second for a period
> > 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
> > 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
> > 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