Connection pool management algorithm

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
kseen
Posts: 9
Joined: Thu 06 Oct 2011 10:16

Connection pool management algorithm

Post by kseen » Thu 06 Oct 2011 10:38

I'm developing high load web service that would provide as faster response as possible. Service should keep a bunch of connections to various databases for faster performance. I'm suggesting using connection pool for that. There's may be connection problems to DB because we have a lot of remote DB accessing through VPN. As I said service should have connection as long as it possible.

What is the connection pool management algorithm?

I have a connection string:

Code: Select all

User Id=inet;Password=somePassw0rd;Data Source=TEST11;Min Pool Size=5;Max Pool Size=15;Pooling=True
Then I simply open and close connection in my code. That's it. At this moment everything is OK. There are five sessions on DB side. Then I kill one session imitating connection problems. And in some cases connection will be restored by pool manager and in some cases it won't.
If I kill all five connections they are never won't restored back.
How can I confiure pooling manager? Any settings for duration between checks DB connections?
I have used "validate connection=true;" it seems to work fine for me, but it needs some efforts to reconnect to DB again and it would be more efficiently to have already good connection.
Thanks in advance!

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Mon 10 Oct 2011 14:23

kseen wrote:What is the connection pool management algorithm?
If Validate Connection=false; (default) - connection is validated only in the pool every ~30 seconds; if Validate Connection=true; - connection is validated only on retrieving it from the pool (less performance, should be used in the environment with unstable connections).

kseen
Posts: 9
Joined: Thu 06 Oct 2011 10:16

Post by kseen » Tue 11 Oct 2011 10:45

How can I make that connection pool manager checks every 15 seconds?

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Wed 12 Oct 2011 13:26

There is no such functionality at the moment. What is a fundamental difference between interval in 30 seconds and the one in 15 seconds? If you want to make sure that a connection, which is returned from the pool, is valid, please use the "Validate Connection=true;" connection string parameter.

kseen
Posts: 9
Joined: Thu 06 Oct 2011 10:16

Post by kseen » Wed 12 Oct 2011 14:57

It's better for performance to get a valid connection from the pool instead of catch a connection, make sure is invalid and then establish it. By the way that will happen if broken connection is getting from the pool (without validating connection option).

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Fri 14 Oct 2011 08:50

kseen wrote:It's better for performance to get a valid connection from the pool instead of catch a connection, make sure is invalid and then establish it.
Please use "Validate Connection=true;" for this purpose.
kseen wrote:By the way that will happen if broken connection is getting from the pool (without validating connection option).
You will get the corresponding exception when there is an attempt to use the connection.

kseen
Posts: 9
Joined: Thu 06 Oct 2011 10:16

Post by kseen » Fri 28 Oct 2011 03:16

Shalex wrote:
kseen wrote:It's better for performance to get a valid connection from the pool instead of catch a connection, make sure is invalid and then establish it.
Please use "Validate Connection=true;" for this purpose.
So there is no way to manage the connections pool?

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Mon 31 Oct 2011 11:19

kseen wrote:So there is no way to manage the connections pool?
Here are connection string parameters for connections pool management: Pooling, Min Pool Size, Max Pool Size, Validate Connection.

Concerning time interval for connection validation in the pool, we have answered to you on Wed Oct 12, 2011 3:26 pm:
Shalex wrote:There is no such functionality at the moment. What is a fundamental difference between interval in 30 seconds and the one in 15 seconds? If you want to make sure that a connection, which is returned from the pool, is valid, please use the "Validate Connection=true;" connection string parameter.

hepek
Posts: 126
Joined: Thu 07 Jul 2011 13:59

Re: Connection pool management algorithm

Post by hepek » Fri 15 Mar 2013 18:18

Is it OK to use Validate Connection set to true with direct connect?
According to documentation they do not recommend it, please see below.

http://www.devart.com/dotconnect/sqlite ... ction.html

thanks

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Connection pool management algorithm

Post by Shalex » Mon 18 Mar 2013 10:06

Yes, it is OK. However, take into account the performance loses which are mentioned in the description of the ValidateConnection property in our documentation.

Post Reply