Connection Pool

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
PatrikAhlquist
Posts: 21
Joined: Thu 11 Feb 2010 08:40

Connection Pool

Post by PatrikAhlquist » Thu 25 Nov 2010 09:35

I'm trying different settings for connection pool (direct mode).
I am using version devart 5.60.124 and Oracle 10.2.0.4.0 on OpenVMS 8.3 Itanium.

At startup of my application it creates 10 connection and adds them to the pool (as in my config file):

Code: Select all

  2010-11-25 09:47:35 n/a dotConnect for Oracle monitoring is started Complete
  2010-11-25 09:47:35 0,063 Creating pool manager Complete
  2010-11-25 09:47:35 0,000 Creating pool with connections string: "User Id=pl;Password=***;Server=pliutv;Direct=True;Sid=pldb;Persist Security Info=True;Pooling=true;Min Pool Size=10;Max Pool Size=100;Connection Lifetime=0;" Complete
  2010-11-25 09:47:35 0,015 Creating object Complete
  2010-11-25 09:47:36 22,750 Open connection: "User Id=pl;Password=***;Server=pliutv;Direct=True;Sid=pldb;Persist Security Info=True;Pooling=true;Min Pool Size=10;Max Pool Size=100;Connection Lifetime=0;" Complete
  2010-11-25 09:47:36 2,422 Connect: "User Id=pl;Password=***;Server=pliutv;Direct=True;Sid=pldb;Persist Security Info=True;Pooling=true;Min Pool Size=10;Max Pool Size=100;Connection Lifetime=0;" Complete
  2010-11-25 09:47:39 2,250 Connect: "User Id=pl;Password=***;Server=pliutv;Direct=True;Sid=pldb;Persist Security Info=True;Pooling=true;Min Pool Size=10;Max Pool Size=100;Connection Lifetime=0;" Complete
  2010-11-25 09:47:41 2,328 Connect: "User Id=pl;Password=***;Server=pliutv;Direct=True;Sid=pldb;Persist Security Info=True;Pooling=true;Min Pool Size=10;Max Pool Size=100;Connection Lifetime=0;" Complete
  2010-11-25 09:47:43 2,250 Connect: "User Id=pl;Password=***;Server=pliutv;Direct=True;Sid=pldb;Persist Security Info=True;Pooling=true;Min Pool Size=10;Max Pool Size=100;Connection Lifetime=0;" Complete
  2010-11-25 09:47:46 2,234 Connect: "User Id=pl;Password=***;Server=pliutv;Direct=True;Sid=pldb;Persist Security Info=True;Pooling=true;Min Pool Size=10;Max Pool Size=100;Connection Lifetime=0;" Complete
  2010-11-25 09:47:48 2,235 Connect: "User Id=pl;Password=***;Server=pliutv;Direct=True;Sid=pldb;Persist Security Info=True;Pooling=true;Min Pool Size=10;Max Pool Size=100;Connection Lifetime=0;" Complete
  2010-11-25 09:47:50 2,234 Connect: "User Id=pl;Password=***;Server=pliutv;Direct=True;Sid=pldb;Persist Security Info=True;Pooling=true;Min Pool Size=10;Max Pool Size=100;Connection Lifetime=0;" Complete
  2010-11-25 09:47:52 2,250 Connect: "User Id=pl;Password=***;Server=pliutv;Direct=True;Sid=pldb;Persist Security Info=True;Pooling=true;Min Pool Size=10;Max Pool Size=100;Connection Lifetime=0;" Complete
  2010-11-25 09:47:55 2,234 Connect: "User Id=pl;Password=***;Server=pliutv;Direct=True;Sid=pldb;Persist Security Info=True;Pooling=true;Min Pool Size=10;Max Pool Size=100;Connection Lifetime=0;" Complete
  2010-11-25 09:47:57 2,235 Connect: "User Id=pl;Password=***;Server=pliutv;Direct=True;Sid=pldb;Persist Security Info=True;Pooling=true;Min Pool Size=10;Max Pool Size=100;Connection Lifetime=0;" Complete
  2010-11-25 09:47:59 0,031 Taking connection from connection pool: "User Id=pl;Password=***;Server=pliutv;Direct=True;Sid=pldb;Persist Security Info=True;Pooling=true;Min Pool Size=10;Max Pool Size=100;Connection Lifetime=0;" Complete
  2010-11-25 09:47:59 0,000 Connection is taken from pool. Pool has 10 connection(s). Complete
Then it does some stuff, including reading from the database:

Code: Select all


  2010-11-25 09:48:01 0,016 Creating object Complete
  2010-11-25 09:48:01 0,047 Open connection: "User Id=pl;Password=***;Server=pliutv;Direct=True;Sid=pldb;Persist Security Info=True;Pooling=true;Min Pool Size=10;Max Pool Size=100;Connection Lifetime=0;" Complete
  2010-11-25 09:48:01 0,016 Taking connection from connection pool: "User Id=pl;Password=***;Server=pliutv;Direct=True;Sid=pldb;Persist Security Info=True;Pooling=true;Min Pool Size=10;Max Pool Size=100;Connection Lifetime=0;" Complete
  2010-11-25 09:48:01 0,000 Connection is taken from pool. Pool has 10 connection(s). Complete
  2010-11-25 09:48:01 0,015 Creating object Complete
  2010-11-25 09:48:01 0,016 Prepare: SELECT ...  Complete
  2010-11-25 09:48:01 0,000 Close connection Complete
  2010-11-25 09:48:01 0,000 Connection is returned to pool. Pool has 10 connection(s). Complete
This is fast and uses a connection from the pool.

It goes on:

Code: Select all

  2010-11-25 09:49:00 0,016 Connection is returned to pool. Pool has 10 connection(s). Complete
  2010-11-25 09:49:00 0,031 Open connection: "User Id=pl;Password=***;Server=pliutv;Direct=True;Sid=pldb;Persist Security Info=True;Pooling=true;Min Pool Size=10;Max Pool Size=100;Connection Lifetime=0;" Complete
  2010-11-25 09:49:00 0,000 Taking connection from connection pool: "User Id=pl;Password=***;Server=pliutv;Direct=True;Sid=pldb;Persist Security Info=True;Pooling=true;Min Pool Size=10;Max Pool Size=100;Connection Lifetime=0;" Complete
  2010-11-25 09:49:00 0,016 Connection is taken from pool. Pool has 10 connection(s). Complete
  2010-11-25 09:49:00 0,015 Creating object Complete
  2010-11-25 09:49:00 0,016 Prepare: SELECT ... Complete
  2010-11-25 09:49:00 0,016 Close connection Complete
  2010-11-25 09:49:00 0,000 Connection is returned to pool. Pool has 10 connection(s). Complete
  2010-11-25 09:53:05 0,000 Disconnect Complete
  2010-11-25 09:53:05 0,015 Disconnect Complete
  2010-11-25 09:53:05 0,000 Disconnect Complete
  2010-11-25 09:53:05 0,000 Disconnect Complete
  2010-11-25 09:53:05 0,032 Disconnect Complete
  2010-11-25 09:53:05 0,031 Disconnect Complete
  2010-11-25 09:53:05 0,000 Disconnect Complete
  2010-11-25 09:53:05 0,000 Disconnect Complete
  2010-11-25 09:53:05 0,031 Disconnect Complete
  2010-11-25 09:53:05 0,063 Disconnect Complete
Here, after a while, it closes all the connections in the pool.

Then (as a test) I start a timer that reads from the database every 5 minutes. Everytime it reads, it has to create 10 connections and add them to the pool. The creation is not done in the background, so it blocks my database call.

Code: Select all

  2010-11-25 09:55:49 22,469 Open connection: "User Id=pl;Password=***;Server=pliutv;Direct=True;Sid=pldb;Persist Security Info=True;Pooling=true;Min Pool Size=10;Max Pool Size=100;Connection Lifetime=0;" Complete
  2010-11-25 09:55:49 2,250 Connect: "User Id=pl;Password=***;Server=pliutv;Direct=True;Sid=pldb;Persist Security Info=True;Pooling=true;Min Pool Size=10;Max Pool Size=100;Connection Lifetime=0;" Complete
  2010-11-25 09:55:51 2,234 Connect: "User Id=pl;Password=***;Server=pliutv;Direct=True;Sid=pldb;Persist Security Info=True;Pooling=true;Min Pool Size=10;Max Pool Size=100;Connection Lifetime=0;" Complete
  2010-11-25 09:55:54 2,250 Connect: "User Id=pl;Password=***;Server=pliutv;Direct=True;Sid=pldb;Persist Security Info=True;Pooling=true;Min Pool Size=10;Max Pool Size=100;Connection Lifetime=0;" Complete
  2010-11-25 09:55:56 2,235 Connect: "User Id=pl;Password=***;Server=pliutv;Direct=True;Sid=pldb;Persist Security Info=True;Pooling=true;Min Pool Size=10;Max Pool Size=100;Connection Lifetime=0;" Complete
  2010-11-25 09:55:58 2,250 Connect: "User Id=pl;Password=***;Server=pliutv;Direct=True;Sid=pldb;Persist Security Info=True;Pooling=true;Min Pool Size=10;Max Pool Size=100;Connection Lifetime=0;" Complete
  2010-11-25 09:56:00 2,234 Connect: "User Id=pl;Password=***;Server=pliutv;Direct=True;Sid=pldb;Persist Security Info=True;Pooling=true;Min Pool Size=10;Max Pool Size=100;Connection Lifetime=0;" Complete
  2010-11-25 09:56:03 2,250 Connect: "User Id=pl;Password=***;Server=pliutv;Direct=True;Sid=pldb;Persist Security Info=True;Pooling=true;Min Pool Size=10;Max Pool Size=100;Connection Lifetime=0;" Complete
  2010-11-25 09:56:05 2,234 Connect: "User Id=pl;Password=***;Server=pliutv;Direct=True;Sid=pldb;Persist Security Info=True;Pooling=true;Min Pool Size=10;Max Pool Size=100;Connection Lifetime=0;" Complete
  2010-11-25 09:56:07 2,250 Connect: "User Id=pl;Password=***;Server=pliutv;Direct=True;Sid=pldb;Persist Security Info=True;Pooling=true;Min Pool Size=10;Max Pool Size=100;Connection Lifetime=0;" Complete
  2010-11-25 09:56:09 2,266 Connect: "User Id=pl;Password=***;Server=pliutv;Direct=True;Sid=pldb;Persist Security Info=True;Pooling=true;Min Pool Size=10;Max Pool Size=100;Connection Lifetime=0;" Complete
  2010-11-25 09:56:12 0,000 Taking connection from connection pool: "User Id=pl;Password=***;Server=pliutv;Direct=True;Sid=pldb;Persist Security Info=True;Pooling=true;Min Pool Size=10;Max Pool Size=100;Connection Lifetime=0;" Complete
  2010-11-25 09:56:12 0,016 Connection is taken from pool. Pool has 10 connection(s). Complete
  2010-11-25 09:56:12 0,000 Creating object Complete
  2010-11-25 09:56:12 0,000 Prepare: SELECT ... Complete
  2010-11-25 09:56:12 0,000 Close connection Complete
  2010-11-25 09:56:12 0,000 Connection is returned to pool. Pool has 10 connection(s). Complete
  2010-11-25 10:00:05 0,000 Disconnect Complete
  2010-11-25 10:00:05 0,000 Disconnect Complete
  2010-11-25 10:00:05 0,000 Disconnect Complete
  2010-11-25 10:00:05 0,015 Disconnect Complete
  2010-11-25 10:00:05 0,000 Disconnect Complete
  2010-11-25 10:00:05 0,016 Disconnect Complete
  2010-11-25 10:00:35 0,000 Disconnect Complete
  2010-11-25 10:00:35 0,000 Disconnect Complete
  2010-11-25 10:00:35 0,016 Disconnect Complete
  2010-11-25 10:00:35 0,000 Disconnect Complete
  2010-11-25 10:00:49 22,500 Open connection: "User Id=pl;Password=***;Server=pliutv;Direct=True;Sid=pldb;Persist Security Info=True;Pooling=true;Min Pool Size=10;Max Pool Size=100;Connection Lifetime=0;" Complete
  2010-11-25 10:00:49 2,250 Connect: "User Id=pl;Password=***;Server=pliutv;Direct=True;Sid=pldb;Persist Security Info=True;Pooling=true;Min Pool Size=10;Max Pool Size=100;Connection Lifetime=0;" Complete
  2010-11-25 10:00:51 2,250 Connect: "User Id=pl;Password=***;Server=pliutv;Direct=True;Sid=pldb;Persist Security Info=True;Pooling=true;Min Pool Size=10;Max Pool Size=100;Connection Lifetime=0;" Complete
  2010-11-25 10:00:54 2,234 Connect: "User Id=pl;Password=***;Server=pliutv;Direct=True;Sid=pldb;Persist Security Info=True;Pooling=true;Min Pool Size=10;Max Pool Size=100;Connection Lifetime=0;" Complete
  2010-11-25 10:00:56 2,235 Connect: "User Id=pl;Password=***;Server=pliutv;Direct=True;Sid=pldb;Persist Security Info=True;Pooling=true;Min Pool Size=10;Max Pool Size=100;Connection Lifetime=0;" Complete
  2010-11-25 10:00:58 2,250 Connect: "User Id=pl;Password=***;Server=pliutv;Direct=True;Sid=pldb;Persist Security Info=True;Pooling=true;Min Pool Size=10;Max Pool Size=100;Connection Lifetime=0;" Complete
  2010-11-25 10:01:00 2,250 Connect: "User Id=pl;Password=***;Server=pliutv;Direct=True;Sid=pldb;Persist Security Info=True;Pooling=true;Min Pool Size=10;Max Pool Size=100;Connection Lifetime=0;" Complete
  2010-11-25 10:01:03 2,234 Connect: "User Id=pl;Password=***;Server=pliutv;Direct=True;Sid=pldb;Persist Security Info=True;Pooling=true;Min Pool Size=10;Max Pool Size=100;Connection Lifetime=0;" Complete
  2010-11-25 10:01:05 2,266 Connect: "User Id=pl;Password=***;Server=pliutv;Direct=True;Sid=pldb;Persist Security Info=True;Pooling=true;Min Pool Size=10;Max Pool Size=100;Connection Lifetime=0;" Complete
  2010-11-25 10:01:07 2,234 Connect: "User Id=pl;Password=***;Server=pliutv;Direct=True;Sid=pldb;Persist Security Info=True;Pooling=true;Min Pool Size=10;Max Pool Size=100;Connection Lifetime=0;" Complete
  2010-11-25 10:01:09 2,266 Connect: "User Id=pl;Password=***;Server=pliutv;Direct=True;Sid=pldb;Persist Security Info=True;Pooling=true;Min Pool Size=10;Max Pool Size=100;Connection Lifetime=0;" Complete
  2010-11-25 10:01:12 0,000 Taking connection from connection pool: "User Id=pl;Password=***;Server=pliutv;Direct=True;Sid=pldb;Persist Security Info=True;Pooling=true;Min Pool Size=10;Max Pool Size=100;Connection Lifetime=0;" Complete
  2010-11-25 10:01:12 0,015 Connection is taken from pool. Pool has 10 connection(s). Complete
  2010-11-25 10:01:12 0,000 Creating object Complete
  2010-11-25 10:01:12 0,000 Prepare: SELECT ... Complete
  2010-11-25 10:01:12 0,016 Close connection Complete
  2010-11-25 10:01:12 0,000 Connection is returned to pool. Pool has 10 connection(s). Complete
This takes a lot of time. Of course, it´s no idea having 10 connections in a pool, if I only use one every 5 minutes. But this was a test. My application doesn't use many connections at the same time (rarely more than one), so 1 or 2 in the pool should be sufficient. But even 1 connection is closed after a while, and it takes a couple of seconds to connect again. I did a small hack and used a timer that every 3 minutes opens 2 connections and then closes them. But is that really the way to do it?
Top
PatrikAhlquist
Posts: 21
Joined: Thu 11 Feb 2010 08:40

Post by PatrikAhlquist » Thu 25 Nov 2010 10:03

Correction, I have some threads so I get the connection pool up to at least 4 connections (Min Pool Size = 2) from time to time.
Top
StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by StanislavK » Fri 26 Nov 2010 15:20

Thank you for your report, we will consider changing this behaviour in such way that the number of connections specified in the Min Pool Size parameter is never removed from the pool. We will inform you about the results here.
Top
StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by StanislavK » Fri 10 Dec 2010 12:58

We have implemented these changes: the pool is not cleared completely now, the number of connections specified in the 'Min Pool Size' parameter remains in the pool indefinitely. This feature will be available in the nearest build of dotConnect for Oracle.
Top
StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by StanislavK » Fri 17 Dec 2010 10:29

We have released the new 6.0.69 version of dotConnect for Oracle where these changes are available. The new build can be downloaded from
http://www.devart.com/dotconnect/oracle/download.html
(the trial version) or from Registered Users' Area (for users with active subscription only):
http://secure.devart.com/

For more information about the fixes and improvements available in dotConnect for Oracle 6.0.69, please refer to
http://www.devart.com/forums/viewtopic.php?t=19791
Top
Post Reply

Return to “dotConnect for Oracle”