> 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