Firebird 4 Connection Pooling (can't get it to work)

[Rudi Feijó]

Hello

I can't seem to get the connection pooling to work.

Windows Server 2019 Datacenter
Firebird 4.0.1.2692-0-x64

firebird 4 conf, everything on default except :
ServerMode = Super
ExtConnPoolSize = 100
ExtConnPoolLifetime = 120

I connect via ibexpert, tried different users and roles, but when I query the pool variables

select
trim(rdb$get_context('SYSTEM','EXT_CONN_POOL_SIZE')) as EXT_CONN_POOL_SIZE
,trim(rdb$get_context('SYSTEM','EXT_CONN_POOL_LIFETIME')) as EXT_CONN_POOL_LIFETIME
,trim(rdb$get_context('SYSTEM','EXT_CONN_POOL_IDLE_COUNT')) as EXT_CONN_POOL_IDLE_COUNT
,trim(rdb$get_context('SYSTEM','EXT_CONN_POOL_ACTIVE_COUNT')) as EXT_CONN_POOL_ACTIVE_COUNT
from rdb$database

EXT_CONN_POOL_SIZE and EXT_CONN_POOL_LIFETIME are correctly displaying the values in firebird.conf, but EXT_CONN_POOL_IDLE_COUNT and EXT_CONN_POOL_ACTIVE_COUNT are always at zero.

I was expecting at least EXT_CONN_POOL_ACTIVE_COUNT = 1, since I'm connected via ibexpert.

I've created a script to launch 100 simultaneous connections to the server, via php (7.4)
$con = ibase_connect('external ip:x:\mydb.GDB','user','pass');

And I can see the overhead on the CPU taking place as connections are established, but EXT_CONN_POOL_IDLE_COUNT and EXT_CONN_POOL_ACTIVE_COUNT remains at zero at all times.

If I launch the script again, the same overhead occurs, hence I assume the connections are not being pooled.


full firebird conf :

ServerMode = Super
ExtConnPoolSize = 100
ExtConnPoolLifetime = 120

TempDirectories = d:\firebird-temp
TempTableDirectory = d:\firebird-temp
#DatabaseAccess = Full
#RemoteAccess = true
#ExternalFileAccess = None
#AuditTraceConfigFile =
#MaxUserTraceLogSize = 10
#DefaultDbCachePages = 2048
#DatabaseGrowthIncrement = 128M
#UseFileSystemCache = true
#FileSystemCacheThreshold = 64K
#FileSystemCacheSize = 0
#RemoteFileOpenAbility = 0
#TempBlockSize = 1M
#TempCacheLimit = 64M
#MaxIdentifierByteLength = 252
#MaxIdentifierCharLength = 63
#InlineSortThreshold = 1000
#AuthServer = Srp256
#UserManager = Srp
#TracePlugin = fbtrace
#WireCryptPlugin = ChaCha, Arc4
#KeyHolderPlugin =
#AllowEncryptedSecurityDatabase = false
#Providers = Remote,Engine13,Loopback
#DeadlockTimeout = 10
#StatementTimeout = 0
#ConnectionIdleTimeout = 0
#MaxUnflushedWrites = 100
#MaxUnflushedWriteTime = 5
#BugcheckAbort = 0
#ClearGTTAtRetaining = 0
#RelaxedAliasChecking = 0
#ReadConsistency = 1
#DataTypeCompatibility = 3.0
#ConnectionTimeout = 180
#WireCrypt = Enabled (for client) / Required (for server)
#WireCompression = false
#DummyPacketInterval = 0
#ClientBatchBuffer = 131072
#DefaultTimeZone =
#RemoteServiceName = gds_db
#RemoteServicePort = 3050
#RemoteAuxPort = 0
#TcpRemoteBufferSize = 8192
#TcpNoNagle = 1
#TcpLoopbackFastPath = 1
#IPv6V6Only = 0
#RemoteBindAddress =
#LockMemSize = 1M
#LockAcquireSpins = 0
#LockHashSlots = 8191
#EventMemSize = 64K
#SnapshotsMemSize = 64K
#TipCacheBlockSize = 4M
#OutputRedirectionFile = /dev/null
#CpuAffinityMask = 0
#GCPolicy = combined
#SecurityDatabase = $(dir_secDb)/security4.fdb
#GuardianOption = 1
#ProcessPriorityLevel = 0
#IpcName = FIREBIRD
#RemotePipeName = interbas
#Redirection = 0
#UdfAccess = Restrict C:\UDF

[Mark Rotteveel]

On 15-04-2022 15:44, Rudi Feijó wrote:
> I can't seem to get the connection pooling to work.
>
> Windows Server 2019 Datacenter
> Firebird 4.0.1.2692-0-x64
>
> firebird 4 conf, everything on default except :
> ServerMode = Super
> ExtConnPoolSize = 100
> ExtConnPoolLifetime = 120
>
> I connect via ibexpert, tried different users and roles, but when I
> query the pool variables
>

[..]

>
> EXT_CONN_POOL_SIZE and EXT_CONN_POOL_LIFETIME are correctly displaying
> the values in firebird.conf, but EXT_CONN_POOL_IDLE_COUNT and
> EXT_CONN_POOL_ACTIVE_COUNT are always at zero.
>
> I was expecting at least EXT_CONN_POOL_ACTIVE_COUNT = 1, since I'm
> connected via ibexpert.

The connection pool is internal within Firebird server, for the Firebird
server connecting to databases locally or remotely from PSQL code using
`EXECUTE STATEMENT ON EXTERNAL` statements. It is not involved with
remote connections from a client to the Firebird server. If you want to
have a connection pool from your client, you need a client-side
connection pool.

Mark
--
Mark Rotteveel

[Rudi Feijó]

Thanks for the reply Mark
I've tried searching for some out-of-the-box solutions for third party connection pooling on firebird, but couldn't find much.
Is there any specific third party solution that you would recommend?

[Mark Rotteveel]

On 15-04-2022 16:03, Rudi Feijó wrote:
> Thanks for the reply Mark
> I've tried searching for some out-of-the-box solutions for third party
> connection pooling on firebird, but couldn't find much.
> Is there any specific third party solution that you would recommend?

For Java, you can use a library like HikariCP, c3p0 or Apache DBCP. For
.NET, FirebirdSql.Data.FirebirdClient has a connection pool built-in. I
have no experience with other languages or frameworks.

Mark
--
Mark Rotteveel

[Dimitry Sibiryakov]

Rudi Feijó wrote 15.04.2022 16:03:
> Is there any specific third party solution that you would recommend?

http://sqlrelay.sourceforge.net

--
WBR, SD.

[Mathias Pannier (unitel)]

FireDAC (Delphi) has also Connection Pooling which I use a lot.

 

Von: firebird...@googlegroups.com [mailto:firebird...@googlegroups.com] Im Auftrag von Rudi Feijó
Gesendet: Freitag, 15. April 2022 16:04
An: firebird-support <firebird...@googlegroups.com>
Betreff: Re: [firebird-support] Firebird 4 Connection Pooling (can't get it to work)

 

ACHTUNG: Diese E-Mail stammt von einem externen Absender. Bitte vermeiden Sie es, Anhänge oder externe Links zu öffnen.

 

--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/941402f9-b686-4f02-b1f9-ab247292ca31n%40googlegroups.com.

ub.unitel GmbH, Schulstraße 16, 06792 Sandersdorf-Brehna
Geschaeftsfuehrung Klaus Richter, Olaf Meyer
Amtsgericht Stendal
HRB 26389 FA Bitterfeld Steuernr. 116/107/08597 Ust.identNr. DE815796778
Deutsche Bank IBAN DE53 86070024 0 6143234 00
Kreissparkasse Anhalt-Bitterfeld IBAN DE69 80053722 0 3050326 82
_____________________________________________________________________
Dieses E-Mail ist nur für den Empfänger bestimmt, an den es gerichtet
ist und kann vertrauliches bzw. unter das Berufsgeheimnis fallendes
Material enthalten. Jegliche darin enthaltene Ansicht oder Meinungs-
äußerung ist die des Autors und stellt nicht notwendigerweise die
Ansicht oder Meinung von ub.unitel GmbH dar.
Sind Sie nicht der Empfänger, so haben Sie diese E-Mail irrtümlich
erhalten und jegliche Verwendung, Veröffentlichung, Weiterleitung,
Abschrift oder jeglicher Druck dieser E-Mail ist strengstens untersagt.
_____________________________________________________________________