Connection Pool
Posted: 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):
Then it does some stuff, including reading from the database:
This is fast and uses a connection from the pool.
It goes on:
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.
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?
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
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
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
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