SNOW-1914311: Getting "Could not obtain a connection from the pool within a given timeout" once upgraded to 4.3.0

[gdoron]

After upgrading our .snowflake nuget package from 3.1.0 to 4.3.0, we are starting to get randomly error of:
Could not obtain a connection from the pool within a given timeout

You can see from the graph below, once a pod gets to this state, nothing works anymore and it must be restarted, which fixes it immediately.
Any idea what this is all about and how it can be addressed?

1. What version of .NET driver are you using?
   4.3.0

2. What operating system and processor architecture are you using?
   Ubuntu ARM

3. What version of .NET framework are you using?
   E.g. .NET core 8

4. What did you do?

Snowflake.Data.Client.SnowflakeDbException (0x80004005): Error: Snowflake Internal Error: Unable to connect SqlState: 08006, VendorCode: 270001, QueryId:
---> System.AggregateException: One or more errors occurred. (Could not obtain a connection from the pool within a given timeout)
---> System.Exception: Could not obtain a connection from the pool within a given timeout
at Snowflake.Data.Core.Session.SessionPool.WaitForSession(String connStr)
at Snowflake.Data.Core.Session.SessionPool.GetIdleSession(String connStr, Int32 maxSessions)
at Snowflake.Data.Core.Session.SessionPool.GetSessionAsync(String connStr, SecureString password, SecureString passcode, CancellationToken cancellationToken)
--- End of inner exception stack trace ---
at Snowflake.Data.Client.SnowflakeDbConnection.b__55_0(Task`1 previousTask)
at System.Threading.ExecutionContext.RunFromThreadPoolDispatchLoop(Thread threadPoolThread, ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location ---
at System.Threading.ExecutionContext.RunFromThreadPoolDispatchLoop(Thread threadPoolThread, ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
--- End of stack trace from previous location ---

[AndrewRybka]

I'm getting the same error on Windows 10 and .NET Framework 4.8 after upgrading to 4.3.0 from 2.2.0. The driver works fine while I execute queries consecutively but fails when I try to open two parallel connections. After the first error, all subsequent attempts to open a connection result in the same error.

[sfc-gh-dszmolka]

hi, thanks for submitting this issue. the pooling has been significantly reworked in the new versions at v4 and above, and there's a couple of parameters now which you can consider to use, to fine-tune the behaviour.

Please take a look at https://github.com/snowflakedb/snowflake-connector-net/blob/master/doc/ConnectionPooling.md. What one can experiment with is perhaps specifying a bigger MaxPoolSize . It is also important to close every connection at the end of using it, because if connection is not closed then the connection pool thinks that it is still being used, and count this connection as the used one. If connections are not properly closed it is very easy to exhaust the pool.

If there is a suspicion that connections are not properly closed so the busy sessions counter is too high, then the pool can be reset by SnowflakeDbConnectionPool.GetPool(connectionString).ClearPool().

Furthermore, ExpirationTimeout is also something to look at; it's by default set to wait 1 hours to expire the connection after it becomes idle. Maybe it's not necessary to wait that long in this particular use-case.

Please also note that now the connection string (options, and the order of options) now uniquely identifies the pool, and it's possible to create multiple pools for e.g. multiple use-cases within the application (e.g. one needs 5 connections and the other 15, it's absolutely possible to configure 2 different pools for the both of them)

Hope this helps in tackling this issue - everything is configurable now, so hopefully with some testing, the optimal configuration can be found in each of your use-cases.