slower connect times with Firebird 5 vs 2.5

[Hamish Moffatt]

In migrating from Firebird 2.5 to 5, I've noticed that just connecting to the database takes longer.

I wrote a simple Python program to make 1000 connections to the server, then ran it with the v2.5 and v5.0 fbclient libraries, connecting to a v2.5 and v5.0 server all running on localhost:

import fdb

import sys

from timeit import timeit

def test():

    con = fdb.connect(dsn=sys.argv[1])

    cur = con.cursor()

    con.close()

print(timeit('test()', setup="from __main__ import test", number=1000))

Then run it with: bench.py localhost/<port>:/path/to/db.fdb

Most of the delay was due to the different server, though the newer client also slowed things down slightly connecting to v2.5:

Configuration           Time for 1000 connects

2.5 client, 2.5 server  4.97s

5.0 client, 2.5 server  5.954s

2.5 client, 5.0 server  21.04s

5.0 client, 5.0 server  26.857s

The extra time seems to be spent at the Firebird server, which uses a lot of CPU while the test runs.

My server is running SuperServer, configuration is from the ib-aid configuration generator at https://cc.ib-aid.com/democalc.html. I tried turning off WireCrypt and it made a tiny bit of difference but not much. I tried using 127.0.0.1 instead of localhost to ensure it's not a network issue (noting that 2.5 had no IPv6 support and 5 does) but that also changed nothing.

Is there anything else I should tweak or check?

Thanks

Hamish

[Dmitry Yemanov]

30.05.2024 11:50, 'Hamish Moffatt' via firebird-support wrote:

> In migrating from Firebird 2.5 to 5, I've noticed that just connecting
> to the database takes longer.

This is kinda expected, as newer versions has more system tables /
fields to be pre-loaded into the metadata cache.


Dmitry

[Dimitry Sibiryakov]

'Hamish Moffatt' via firebird-support wrote 30.05.2024 10:50:
> Is there anything else I should tweak or check?

List of auth plugins in firebird.conf for both AuthServer and AuthClient.
Longer list = longer connection time. More secure plugin = longer connection time.
Firebird doesn't use hardware acceleration for SHA and acceleration of RSA is
not available on Intel/AMD processors.

--
WBR, SD.

[Hamish Moffatt]

I have the following in my config (courtesy the ib-aid generator);

AuthServer = Srp256, Legacy_Auth
UserManager = Srp, Legacy_UserManager

AuthClient is not set.

On 30/05/2024 6:57 pm, Dmitry Yemanov wrote:
> This is kinda expected, as newer versions has more system tables /
> fields to be pre-loaded into the metadata cache.
>

Does either of these explain the 5x longer connect times I saw?


I will work on better connection pooling in my applications.

Thanks

Hamish

[Dimitry Sibiryakov]

'Hamish Moffatt' via firebird-support wrote 30.05.2024 12:05:
>>
> I have the following in my config (courtesy the ib-aid generator);
>
> AuthServer = Srp256, Legacy_Auth

Taking into account that you tried "client 2.5 -> server 5.0" case, I guess
you only using legacy auth. It worth trying to leave it alone in config for both
client and server.

--
WBR, SD.

[Hamish Moffatt]

I tried Srp256 and Legacy_Auth alone and also the default and it didn't
make more than 1 second difference in 25. Also tried connecting to an
empty database and that saved a few seconds, but it is still about 22ms
per connection, which is quite a lot I think.


Here is my whole config;

ServerMode = Super
DefaultDbCachePages = 50K # pages (SuperServer) - increase pages in
databases.conf, not here

LockMemSize = 20M # bytes (SuperServer)
LockHashSlots = 40099 # slots

MaxUnflushedWrites = -1 # default for posix (non-Windows)
MaxUnflushedWriteTime = -1 # default for posix (non-Windows)
ParallelWorkers = 1 # default parallel threads
MaxParallelWorkers = 64 # parallel threads for sweep, backup, restore
MaxStatementCacheSize=32M
OuterJoinConversion = true
OptimizeForFirstRows = false
UseFileSystemCache = true
TempCacheLimit = 256M
RemoteServicePort = 3052
InlineSortThreshold = 16384 # use REFETCH plan for big sortings

ExtConnPoolSize = 64 # external connections pool size
ExtConnPoolLifeTime = 3600 # seconds

#set DataTypeCompatibility according Migration Guide
https://ib-aid.com/download/docs/fb5migrationguide.html
#DataTypeCompatibility =
#WireCryptPlugin = ChaCha64, ChaCha, Arc4
#WireCrypt = Enabled (for client) / Required (for server)
#WireCrypt = Enabled
WireCrypt = Disabled
#WireCompression = false
RemoteAuxPort = 3053
#authentication plugin setup
#Recommendation - use SELECT * FROM SEC$USERS
#to check that you have users for all plugins
#AuthServer = Srp256, Legacy_Auth
UserManager = Srp, Legacy_UserManager

#MaxIdentifierByteLength = 252
#MaxIdentifierCharLength = 63
#DefaultTimeZone =
#SnapshotsMemSize = 64K # bytes
#TipCacheBlockSize = 4M # bytes


Hamish

[Vlad Khorsun]

I tried Srp256 and Legacy_Auth alone and also the default and it didn't
make more than 1 second difference in 25. Also tried connecting to an
empty database and that saved a few seconds, but it is still about 22ms
per connection, which is quite a lot I think.

  Just for note - test includes disconnect time too.

  Why it is a problem for you ? Does your apps creates a lot of short-lived (<= 50ms) connections ?

  Anyway, I doubt they are not overlapped in time - and it is very important for such kind of "testing".

Try to retain at least one alive separate connection during such test or set database linger - it allows

to retain many "heavy" structures in memory and avoid perf penalty on connect. Of course, it works

for SuperServer only.

Regards,

Vlad

[Hamish Moffatt]

On 30/05/2024 9:17 pm, Vlad Khorsun wrote:

I tried Srp256 and Legacy_Auth alone and also the default and it didn't
make more than 1 second difference in 25. Also tried connecting to an
empty database and that saved a few seconds, but it is still about 22ms
per connection, which is quite a lot I think.

  Just for note - test includes disconnect time too.

  Why it is a problem for you ? Does your apps creates a lot of short-lived (<= 50ms) connections ?

My unittests were making a new connection for each test, and were significantly slower with Firebird 5 as a result. I've changed them to reuse the connections and now they're actually faster than when run against 2.5.

  Anyway, I doubt they are not overlapped in time - and it is very important for such kind of "testing".

Try to retain at least one alive separate connection during such test or set database linger - it allows

to retain many "heavy" structures in memory and avoid perf penalty on connect. Of course, it works

for SuperServer only.

Indeed, if I keep a master connection open then open/close 1000  more, those run very quickly.

My application has hundreds of databases so it's not feasible to connect to them all in advance, and latency is important, but I keep a connection after the first use so only the first connection is slower. This is unavoidable.

Thanks

Hamish