Subject Re: Firebird 2.5 performance slower then 2.1 especially SuperClassic...
Author hvlad
--- In, "randallsell" wrote:
> 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.

Don't ask people if you don't like answers. Easy, isn't is ? :-D

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

SC (like CS) have very small page cache by default. If your tables have few active indices during INSERT's it will be very slow because of small page cache.

Or compare 2.1 CS against 2.5 SC\CS, or set page cache value equal for all tests.