Subject Re: [firebird-support] Performance lost with lots of connections
Author Anderson Farias

I have a scenario that in some ways look like yours (and in some ways it
doen't) -- so, I'll describe it and I hope it can be of any help -- sorry
but it may also get too long... =)

We have an application also build with Delphi 7 + IBX + (in some part) DSP
and CDS (in a way to get short transactions most of the time). This app is
kind an ERP so we have users doing Reporting, inserting (like service
orders, invoices, etc) and updating (order info, product inventory, etc) all
together most of the time.

This app servers 5 stores on diferent geografic places (only one FB
server/database) -- most of the client (app) connections are made throug a
Windows TS -- so, about 90% of "users" are remote but the db is accessed

Ok, so usually we have about 100 to 150 concurrent connections -- the
database is small, about 2Gb and we run FB 1.5 Classic Server in a Windows
2003 box.

The hardware is quite similar to yours, 4 HT Xeon single-core processors,
now we have 4Gb of memory but used to be only 2Gb (and Ok). But, something
you didn't mention and I find very important are the hard drives. Even with
a small db (for now, but it will grow), since we have many connections and
for data security we choiced to have: 1 SATA drive for the system (os, fb
binary, and so), 1 SATA drive for temporary files and backup files and 4 SAS
drivers on a RAID 10 configuration for the DB file.

Another important thing is that this (DB) server has gigabit ethernet
connection to a 1000/100 switch. So does the TS as well (to the same
switch). We plan in the future to have 2nd gigabit ethernet on both
dedicated only to connect them together -- but it was not necessary til now.

The 4 processors usage vary (equally), and can go from 20-60% usually, and
on most heavy use thay can go to more than 90% (but its not frequent) and
memory usage is near 2Gb max. This db has a 4k page size and page buffers
configured to something like 500 (I can't remember exactly).

We have a very good performance this way -- but, we have also experienced
bad performance some times, some of the problems we faced was:

1) an accounting application. This app processes the transactions in a batch
way, and becouse of a bad design it was slowing down everything when people
got to start processing weeks of info. As soon the design was reviewed and
also bad triggers were re-written things got a lot better.

2) We also had to much 'lock conflics' and we solve that (at a performance
point of view) by raising the related configuration at firebird.conf -- that
helped A LOT.

3) Another thing we had to 'take care' was some bad codes that were
'raising' the gap bettween OIT and OAT. Sometimes we had huge difference
between them and things got quite slow.

Really, the only bad thing we found using this setup was that it seems
'dead' clients never get their fb_inet_server released. We've already
changed the default on windows from 2 hours to something small (few minutes)
but did not see any effect at all -- thats realy annoying -- and can be
sometimes a problem. =)

Well, I know this is not exactly what you're looking for but I home this
info helps get some idea.