Optimizing a Delphi 2009 app using Firebird 2.5 Alpha 1

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.


15 thoughts on “Optimizing a Delphi 2009 app using Firebird 2.5 Alpha 1

  1. The FB version is (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.

  2. 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?

    • 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!

  3. 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?

  4. According to the 2.5 releasenotes classic and superclassic are handled by the same binary:

    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.

  5. 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…

    • 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…

  6. 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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s