Expected behaviour for ConnectionPooling

[trynn]

Hi,
while upgrading from 7.10.1.0 to 9.0.1 we are having issues with the connection-pooling.
Mostly running into following exception.

Application_Start System.InvalidOperationException
Connection pool is full.
   bei FirebirdSql.Data.FirebirdClient.FbConnectionPoolManager.Pool.GetOrCreateConnectionImpl(Boolean& createdNew)
   bei FirebirdSql.Data.FirebirdClient.FbConnectionPoolManager.Pool.GetConnection(Boolean& createdNew)
   bei FirebirdSql.Data.FirebirdClient.FbConnection.<OpenAsync>d__74.MoveNext()

Now, i'm aware the this is thrown when the connection-pool reached the configured MaxPoolSize-Value (by default 100).
We have to restrict this even further to 10-connections per active application, since Firebird itself has massive issues when concurrent active connections raise above 300 (in our experience). But i'm digressing ..

We basically access all database queries similiar to this example.

// code for 9.0.1
using var connection = new FbConnection(connectionBuilder.ConnectionString);
await connection.OpenAsync();

using var transaction = await connection.BeginTransactionAsync(DbTransactionOptions.Read);
await executeSomeSqlWithTransaction(transaction);
await transaction.CommitAsync();

// code-replacement for 7.10.1.0 (no async transaction)
// using var transaction = connection.BeginTransaction(DbTransactionOptions.Read);
// transaction.Commit();

So for earch query retrieving/returning a connection to the FbConnectionPoolManager.
I dont think it's viable to not use connection-pooling at all. So i've not evaluated this option further.

behaviour change

With 7.10.1.0 we're able to configure MaxPoolSize=1 and any application will work and somehow queue/block database queries. they get serialized and share the existing connection. (i guess it has something todo with the non-async-transaction)

Since 9.0.1 (i guess since v8) this has changed. As soon as the parallel execution of queries exceeds MaxPoolSize, the Connection pool is full.-exception is thrown. Logically thats correct, since all connections are in use.
But with a limited ressource of parallel connections, thats a real-world-problem.

In a async web-api scenario it's easly possible that 100+ requests are made concurrently.
And each would require a database-query in the backend. At some point, some kind of wait()/blocking has to occure.

my personal expectation

i would have assumed that when 2 async queries are requested, and only one db-connection is allowed/exists, that the queries will be serialized and one have to wait until the first is completed.

question

what is the expected behaviour for the ConnectionPooling inside theFirebirdSql.Data.FirebirdClient?
is this a missing feature in the library yet, or where am i wrong here?

While implementing Polling the connection.Open() until not exception thrown would be possible, i don't think it's either a good idea to handle this with exceptions (overhead), nor do i think this should be implemented by every application itself.
For me this seems like a common problem. (it's covered/solved in Npgsql for example)

[cincuranet]

since Firebird itself has massive issues when concurrent active connections raise above 300 (in our experience).

That's more question of configuration and HW. I have Firebird deployments where # of connections peaks between 400-500 and it's fine. And I know about setups with over 1000 active connections, but that needs a careful tuning.

With 7.10.1.0 we're able to configure MaxPoolSize=1 and any application will work and somehow queue/block database queries. they get serialized and share the existing connection. (i guess it has something todo with the non-async-transaction)

The somehow part is important. I guess, because of the blocking, you never had multiple concurrent connections opened. That said. It's the same behavior in 7.10.1 as well as newer versions.

You can check that with 7.10.1 with simple piece of code (Open and OpenAsync give same outcome):

using FirebirdSql.Data.FirebirdClient;

const string ConnectionString = "database=localhost:test.fdb;user=sysdba;password=masterkey;pooling=true;maxpoolsize=1";
Console.WriteLine(typeof(FbConnection).Assembly.GetName().Version);
using (var conn = new FbConnection(ConnectionString))
{
	await conn.OpenAsync();
	using (var conn2 = new FbConnection(ConnectionString))
	{
		await conn2.OpenAsync();
	}
}

And the result will be:

7.10.1.0
Unhandled exception. System.InvalidOperationException: Connection pool is full.
   at FirebirdSql.Data.FirebirdClient.FbConnectionPoolManager.Pool.GetOrCreateConnectionImpl(Boolean& createdNew)
   at FirebirdSql.Data.FirebirdClient.FbConnectionPoolManager.Pool.GetConnection(FbConnection owner)
   at FirebirdSql.Data.FirebirdClient.FbConnectionPoolManager.Get(ConnectionString connectionString, FbConnection owner)
   at FirebirdSql.Data.FirebirdClient.FbConnection.Open()
   at System.Data.Common.DbConnection.OpenAsync(CancellationToken cancellationToken)
--- End of stack trace from previous location ---
   at Program.<Main>$(String[] args) in Program.cs:line 10
   at Program.<Main>(String[] args)

In a async web-api scenario it's easly possible that 100+ requests are made concurrently.

Correct. But usually number of requests does not relate to number of connections. You want to open and close (hence return to pool) as quickly as possible so it can be reused elsewhere.

And each would require a database-query in the backend. At some point, some kind of wait()/blocking has to occure.

Why would you want blocking? For high-throughput scenarios you want to avoid blocking as much as possible.

While implementing Polling the connection.Open() until not exception thrown would be possible, i don't think it's either a good idea to handle this with exceptions (overhead), nor do i think this should be implemented by every application itself.
For me this seems like a common problem. (it's covered/solved in Npgsql for example)

IIRC Npgsql has a timeout for getting connection from the pool. Once that expires, same exception is thrown. FirebirdClient has basically 0 timeout. You can create feature request for that. But bear in mind, that's not a general solution to your scenario. Connection pooling is here for performance reasons, not for resource limiting scenarios.

[trynn]

Hi, yes you are correct.
because of blocking when using a transaction we never had those multiple connections.

// using var transaction = connection.BeginTransaction(DbTransactionOptions.Read);
// transaction.Commit();

With v9.0.1 this changed tho.
await connection.BeginTransactionAsync is indented to not block, sure and expected.
but connection.BeginTransaction doesnt block either anymore.

And yes, pooling is for high-throughput scenarios.
But only when keeping the connections limited, you can harvest the performance. Because connections are no free ressource.
Quite the difference indeed. Firebird is loosing massivly performance when the connection count is getting too high.
I'll stop to pinpoint numbers now, because sure it's dependent on the system/load you have, but i guess you dont deny it completely(?)

When the connection count is allowed to skyrocket, you loose more performance than you gain by the pooling, because of firebird-server issues to handle the high connection count.
And thats the reason why it is highly problematic in (my/a) real world scenario.

For Npgsql i also would confirm you. They have two mechanics to produce

• getConnection when MaxPool not reached (firebird has too)
• await Connection until specified timeout
• throw exception when timeout reached (firebird has too)

It's clear that you need a timeout, you cant just wait to get a connection forever.
But even the smallest of timeouts improves the situation dramatically when async queries are executed.
It just spreads the queries over a short amount of time and keeps the database stable.

alternatives i can think of

implemention a complete individual connection-pool.
(then we may just drop the driver-side pool because it's not good enought?)

try getting connections inside a loop with try/catch until a connection is free
(counters the performance-reason for the pool)

restrict on web-api level, to never reach a load that the database cant handle
(this moves a database problem to places, where it does not belong to)

whats next

this issue was originally not indented to report a bug/trigger development.
we just need a documentation about the current state of the pool and how it should behave.
there is always the option that it's just used wrongly.

bute sure, i would like to create a feature-request for this problem, if that's what is needed.
just no idea where you collect them :D Would appreciate some direction.

[cincuranet]

With v9.0.1 this changed tho.
await connection.BeginTransactionAsync is indented to not block, sure and expected.
but connection.BeginTransaction doesnt block either anymore.

Show me the code.

Firebird is loosing massivly performance when the connection count is getting too high.
I'll stop to pinpoint numbers now, because sure it's dependent on the system/load you have, but i guess you dont deny it completely(?)

I think it's best to show some scenarios and report to https://github.com/FirebirdSQL/firebird.

implemention a complete individual connection-pool.
(then we may just drop the driver-side pool because it's not good enought?)
try getting connections inside a loop with try/catch until a connection is free
(counters the performance-reason for the pool)
restrict on web-api level, to never reach a load that the database cant handle
(this moves a database problem to places, where it does not belong to)

I think you should first work on your database/server configuration. Because if you have "massive" problems with 100 connections, you have something wrong.

bute sure, i would like to create a feature-request for this problem, if that's what is needed.
just no idea where you collect them :D Would appreciate some direction.

https://github.com/FirebirdSQL/NETProvider/issues/

[trynn]

Show me the code.

Maybe when i find a time window. needs work to isolate the problem.

I think it's best to show some scenarios and report to https://github.com/FirebirdSQL/firebird.

We've already worked with Khorsun/Yemanov in 2017. I'm pretty sure they are aware. Currently no time to invest months of work again to showcase the same old problems.

I think you should first work on your database/server configuration. Because if you have "massive" problems with 100 connections, you have something wrong.

I've never said that we have or would have problems with only a total of 100 connections.

Thanks for the hint. I've create the feature-request issue.
#1058

[trynn]

Show me the code.

https://github.com/trynn/Firebird.AsyncPoolExample

[cincuranet]

There's nothing about (non)blocking BeginTransaction[Async]. In 7.10.1 asynchronous implementation was not present, thus the synchronous path was used as a fallback (i.e. here). In this case, as I already mentioned, you don't have concurrent connections/operations, because there's blocking all the way down. You can see it in your example.

execute load
execute load
execute load
execute load
got mary
got fuck
got kill
Unhandled exception. System.InvalidOperationException: Connection pool is full.
...

execute load
got mary
got fuck
got kill
execute load
got mary
got fuck
got kill
execute load
got mary
got fuck
got kill
execute load
got mary
got fuck
got kill
execute load
got mary
got fuck
got kill
fin.

[trynn]

Honestly, maybe my english is not good enought, but i do not really understand your point in your last post.

Yes, in 7.10.1 async was not present.
Yes, System.Data.Common did use the Sync option as a fallback.
Yes, there were no concurrent connections in my example.

But still, the behaviour of the driver changed in this example/regard?

[cincuranet]

Yes, the observed behavior changed. But it's an expected change. Before your code was working "by accident".

[trynn]

If thats "expected change", i would love to be able to read such changes for upcoming releases. Would have helped greatly.

I've pushed another branch to showcase the behaviour with multiple connections.
https://github.com/trynn/Firebird.AsyncPoolExample/tree/multiconnections

Simply, by increasing the poolSize, everything is too fast and no additional connections are created.
By adding a delay "to simulate longer" SQL execution, it does create those connections up to poolSize limit.

query for open connections while app is running:

select * from MON$ATTACHMENTS

In that Szenario, both drivers to behave the same way.

Now, with that said, as long as the queries are "fast enough" - yes its working "by accident".
As a user, i can tweak the pool around an estimated load tho, and end up in "kind of stable/ok situation".

[trynn]

I hope we've found the bottom/root of the "actual issue" here.

[trynn]

For me, at least the ConnectionPooling in 7.10.1 was usable.
Yes, i wasn't fully aware "why" that even worked. Thanks for working that out.
But at least it behaved in a similiar way i expected it to do (and other DBMS behave).

With 9.0.1, i feel like that is not possible anymore.
I dont see the "try/catch exception" (Connection pool is full) as a valid option in any form or shape, if the goal is to have a "running" application.

So i have to conclude, that the current ConnectionPooling is just not usable (with a limited connection count).
I can't really understand how anyone can use it. Would appreciate to hear from others, "how" they managed to use it.

Clearly, i would like to see a higher priority on #1058 as a result.
Ending it here.