Optimizing a Delphi 2009 app using Firebird 2.5 Alpha 1
Posted by Kim on Tuesday, February 3, 2009
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.
Firebird News » Optimizing a Delphi 2009 app using Firebird 2.5 Alpha 1 said
[...] last few days I’ve spent optimizing an application written in Delphi 2009 that talks to a Firebird database using the [...]
filip said
are you using Classic, SuperClassic or SuperServer ?
Kim said
The FB version is 2.5.0.20343 (Win64) SuperServer. DBW says “WI-T6.3.0.20343 Firebird 2.5 Alpha 1″. It’s installed on Windows XP 64 bit edition (16 GB RAM).
Configuration options used: CPUAffinity=15, HashSlots=499. PageSize=4096, PageBuffers=65535, ForcedWrites=Off, SweepInterval=20000.
How does one go about installing a SuperClassic version? The installer offers Classic or SuperServer only.
Salim said
I tried using the TIBxxx components in delphi 2009 to connect to Firebird and got some compatibility errors.
Have you tried the same but using the IBProvider and ADO components?
Kim said
I’ve not used the ADO components myself. I’m under the impression that the ADO components do not allow for as great transaction control as I can get with the TIBTransaction components. Anyone who can verify this please reply!
pabloj said
You should be using SuperClassic, see http://www.sinatica.com/blog/en/index.php/articles/firebird-superserver-classicserver-or-superclassic
Also, process explorer from sysinternals should help determine what’s actually happening and if I/O is actually the bottleneck
Kim said
The link provided very good insight into the differances between Classic, SuperServer and SuperClassic!
But I still don’t know how to install a SuperClassic version in windows with the installer?! Or is this a switch or something?
Mark said
According to the 2.5 releasenotes classic and superclassic are handled by the same binary:
“Windows
On Windows, the same fb_inet_server.exe binary delivers either the Classic or the Superclassic working
modes, according to switch settings. Classic is the default mode.
To use the Superclassic mode, add the -m (multi-threaded) switch to the command line, as follows:
When intending to run Superclassic as an application, use
fb_inet_server -a -m
When installing Superclassic to run as a service, you need to include an -m switch in the instsvc.exe command.“
Kim said
Thank you! I’ve been looking for this, and for some reason I’ve not looked in the release notes…
Kim said
Now things are getting interesting – I installed the SuperClassic service version and adjusted pagebuffers to 256 since having 64K pagebuffer took too much memory with 400 users.
After 44 hits/sec (towards my webserver, exact same one) I started seeing a decline on hits/sec (kneepoint reached) and the following errors from the 1st SELECT statement:
2009.02.05 20:57:14:171| bad parameters on attach or create database CHARACTER SET WIN1252 is not defined
2009.02.05 20:57:30:250| CHARACTER SET NONE is not installed
2009.02.05 20:58:42:718| CHARACTER SET UNICODE_FSS is not installed
2009.02.05 20:59:40:968| CHARACTER SET NONE is not installed
2009.02.05 21:06:32:468| bad parameters on attach or create database CHARACTER SET WIN1252 is not defined
This is in effect ~10 hits/sec less towards the application and the same DB machine has an avg. CPU usage of only 45%.
I need to tweak the parameters (pagebuffers etc.) to see if that makes a difference, but I think it will not affect it too much.
With 1024 pagebuffers
I get 39 hits/sec, with same kind of error messages. 45% CPU usage on DB
With 128 pagebuffers
I get 46 hits/sec, with NO error messages. 42% CPU usage on DB
With 64 pagebuffers
I get 43 hits/sec, with same kind of error messages. 43% CPU usage on DB
Any suggestions at this point are very welcome as I’m out of ideas at the moment…
dimitr said
This is a known issue, fixed recently. Your build is much outdated, it could be worth trying a fresher one.
Kim said
OK, installed the newest snapshot build (v2.5.0.22652 x64) in SuperClassic mode. Pagebuffer set to 0128.
Now I got 42 hits/sec at peak to the webserver, and when load increases it drops to a steady 23 hits/sec. No errors however.
Next I’ll test the new build in SuperServer mode… maybe I’ll get more than 50 hits/sec steadily with that?!
In SuperServer mode it peaked at 55 hits/sec and started declining slowly to a steady 47-48 hits/sec. So it seems that at least for my application the SuperServer mode is the most effective. Next I need to do some code changes and add method/function time profiling to measure what is taking time inside the app…
Kim said
After much testing and code profiling I’ve come to the conclusion that the problem is the size of the database and the Disk I/O. I’ve tested the app with large and small DB and the results vary from 15 pages/sec to over 80/sec…
Another conclusion is that the SuperClassic mode is not faster for this application, so I’ll stick with the SuperServer mode for now.
- said
Thank you for good job!