Firebird 4.0 - ConnectionIdleTimeout vs ExtConnPoolLifeTime

[Luciano Rodrigues Nunes Mendes]

Hi Guys,

I was reading the Firebird 4.0 documentation and I didn't know the best way to close a connection when it has been idle for a few minutes (e.g.: 15 minutes). Should I use ConnectionIdleTimeout or ExtConnPoolLifeTime? What are the main differences between these two configurations?

Thanks in advance,

Luciano

[Dimitry Sibiryakov]

Luciano Rodrigues Nunes Mendes wrote 05.05.2024 18:04:
> Should I use ConnectionIdleTimeout or ExtConnPoolLifeTime? What are the main
> differences between these two configurations?

The former controls client connection established *to* the server.
The latter controls connection established for ESonEDS *from* the server.

--
WBR, SD.

[Mark Rotteveel]

On 05/05/2024 18:04, Luciano Rodrigues Nunes Mendes wrote:
> I was reading the Firebird 4.0 documentation and I didn't know the best
> way to close a connection when it has been idle for a few minutes (e.g.:
> 15 minutes). Should I use ConnectionIdleTimeout or ExtConnPoolLifeTime?
> What are the main differences between these two configurations?

The ExtConnPoolLifeTime setting is only for connections the server makes
(e.g. in EXECUTE STATEMENT ON EXTERNAL in PSQL), and it only controls
the time a connection can be inactive in the pool before it is released,
it doesn't control the maximum lifetime of a connection.

The setting you're looking for is ConnectionIdleTimeout. That said, if
your application is using a connection pool, setting it too low may have
negative effects on performance.

Mark
--
Mark Rotteveel

[Luciano Rodrigues Nunes Mendes]

Hi Mark,

My application uses Connection Pooling and there is a timer in it that performs queries every 3 minutes. Would setting the ConnectionIdleTimeout to 15 minutes cause any performance issues?

Thanks in advance,

Luciano

--
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/3a55c59f-7a16-4d30-8a62-51680ecedd86%40lawinegevaar.nl.

[Mark Rotteveel]

On 07/05/2024 04:37, Luciano Rodrigues Nunes Mendes wrote:
> Hi Mark,
>
> My application uses Connection Pooling

> <https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql-server-connection-pooling> and there is a timer in it that performs queries every 3 minutes. Would setting the ConnectionIdleTimeout to 15 minutes cause any performance issues?

If a connection is pooled, but not used, it will be idle. The whole
point of a connection pool is to avoid the overhead of creating new
connections, so using a low idle timeout (and I consider 15 minutes to
be low) could conflict with your use of a connection pool: if
connections can be idle in the pool for more than 15 minutes, the
ConnectionIdleTimeout will invalidate those connections. If they do get
checked out from the pool eventually, they will be invalid, and a whole
new connection will need to be established. That assumes the pool is
actually checking validity before returning a connection, otherwise you
might even be confronted with connection errors in your application.

So, first ask yourself *why* you want to set the ConnectionIdleTimeout.
What problem are you trying to solve with it?

Mark
--
Mark Rotteveel

[Luciano Rodrigues Nunes Mendes]

Hi Mark,

Thank you very much for your quick response!

Let me describe the issue I'm facing:

I have around 150 clients who access the database through a VPN over the internet (high latency network). Under certain conditions, failures in the internet connection of these clients leave transactions open on the server, preventing this client from repeating the transaction due to deadlock errors. Quite often I need to force the disconnection of all connections from this client (DELETE FROM MON$ATTACHMENTS WHERE MON$USER='UserName') so that it can use the system again since Firebird 3.0 waits for the transaction to complete for a very long time. When migrating the database to Firebird 4.0 I discovered the existence of the ConnectionIdleTimeout setting and thought it would help me with the issue described above. The customer system automatically communicates with the database every 3 minutes regardless of other actions by those customers. Therefore, if a connection is idle for more than 3 minutes it means that it is probably a lost connection (network failure) and this could be closed by Firebird side. In this way, I set the ConnectionIdleTimeout to 15 minutes, well beyond the 3 minutes, which in my opinion would be more than enough to prove that it was a lost connection and would not be such a long time for the client to wait to return to use the system. I don't know if this would be the best approach to solving this issue. If you have a better solution please explain it to me.

Thanks in advance,
Luciano

--
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/9d1db66b-712c-4c29-ad70-9330f6f3bede%40lawinegevaar.nl.

[Dimitry Sibiryakov]

Luciano Rodrigues Nunes Mendes wrote 07.05.2024 19:36:
> Under certain conditions, failures in the internet connection of these clients
> leave transactions open on the server, preventing this client from repeating the
> transaction due to deadlock errors.

For this there are keep alive setting in OS and DummyPacketInterval in
firebird.conf. Attachment timeout has different purpose and not needed in this case.

--
WBR, SD.

[Luciano Rodrigues Nunes Mendes]

Hi Dimitry,

Is it enough to set DummyPacketInterval = 900 so that the error I mentioned in the previous email no longer happens or do I have to additionally adjust this time in Windows as well?

Thanks in advance,

Luciano

--
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/e988e4d7-6577-48a4-8d96-bf7c92b50995%40ibphoenix.com.

[Dimitry Sibiryakov]

Luciano Rodrigues Nunes Mendes wrote 07.05.2024 21:31:
> Is it enough to set DummyPacketInterval = 900 so that the error I mentioned in
> the previous email no longer happens or do I have to additionally adjust this
> time in Windows as well?

They are independent. You can set any or both and see which will work for you
better.

--
WBR, SD.

[Luciano Rodrigues Nunes Mendes]

Hi Dimitry,

I tested the DummyPacketInterval setting and it's exactly what I need.

Once again, thank you very much for your support!

Best Regards,

Luciano

--
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/7a76225d-75a0-4269-b609-a07937d90382%40ibphoenix.com.