The last few days I’ve spent optimizing an application written in Delphi 2009 that talks to a Firebird database using the standard TIBDatabase, TIBTransaction and TIBSQL components The application needs to do alot of reading/writing to the database, and was rather slow so I had to optimize it a little …The application is basically an Indy based Web-Server that receives simple GET parameters, does a thing or two in the DB and returns with the Success/Fail and a possible result from the DB.
My need was to have more than 400 simultaneous connections to the web-server (keep-alive=true) and still maintain good performance in regards to reads/writes. For each command I have to do 3 selects and optionally one delete or insert. Further the business logic dictates that in a certain case only one (1) client can do a SELECT+DELETE and thus guarantee to retrieve a unique row from the table.
After several trial/errors I finally got to a situation where I was able to get 50 hits/sec with 400 active connections, and still retain response times of under 0.3 sec/insert and <1 sec for select+delete operations. Each connection has its own TIBDatabase component, and each SELECT or DELETE had it’s own transaction. Adding more load to the application only resulted in longer response times, but no more than 50 GET commands/sec.
The DB server was a QuadCore (4 x 3.0 GHz XEON) machine and the app is running on the same machine. Local connection to Firebird was with TCP. Firebird was configured to use all 4 cores and HashSlots was increased to 499. The machine avg CPU was around 55%-60%. My guess is that the Disk I/O was the limiting factor.