Subject Firebird 2.5 performance slower then 2.1 especially SuperClassic...
Author randallsell
Hello all,

I recently upgraded FB from 2.1.5 to 2.5.1. In order to port data we essentially write the contents of the database to a file, then read it back in again. In short, this amounts to many thousands of INSERT statements. (Yes I know how to use GBak, this process was designed to make metadata mods easy).

Anyway, this process has been working nicely for many years, and I expected that the performance of 2.5 would be more or less on par with 2.1, possibly better (SMP, SuperClassic). But it isn't. In fact it ranges from 25% worse to 4x worse (400%). So I thought I'd raise the issue. I'm sure a bunch of nay-sayers will argue that my tests are invalid, blah blah. But rest assured I ran my tests many times over on the same machine to ensure the results are accurate. And the only things varied in the tests were the backend server version / architecture / Forced-Writes. Front-end app and import file always the same.

Here are the results of the import runs:

FB 2.1.5 SuperServer Force-Writes Off 3 min 40 sec
FB 2.1.5 SuperServer Force-Writes On 7 min 15 sec

FB 2.5.1 SuperServer Force-Writes Off 4 min 30 sec
FB 2.5.1 SuperServer Force-Writes On NOT TESTED

FB 2.5.1 SuperClassic Force-Writes Off 17 min 35 sec
FB 2.5.1 SuperClassic Force-Writes On 20 minutes 4 seconds

So a pure upgrade from 2.1 to 2.5 (same SuperServer architecture) made the process 25% slower (looking at Forced Writes Off only). The real killer above is the new SuperClassic architecture. OK sure this test should have absoluely no benefit from SuperClassic since it is a single threaded app throwing INSERT statements to the server. But over 4x worse performance?!?! That's a serious performance hit!

FWIW, the firebird.conf is the same for all test cases (well the same less changes made between 2.1 and 2.5). The critical settings (modified from their default values) are these:

CpuAffinityMask = 7
MaxUnflushedWrites = 200
MaxUnflushedWriteTime = 10

Has anyone else experienced performance degredation with FB 2.5? Although I would love to take advantage of some of the new features of 2.5, the poor performance has me going back to 2.1 for now.

-randall sell