Connection Pooling

[muk]

Hi

I am writing a perl module for connection pooling for a standalone application,I am new to perl ,can any body help me on how to go about this........

Thanks in advance........

[moritz]

Which part are you having trouble with?

It's common wisdom to reuse as much tested and reliable code as possible, so take a look at http://search.cpan.org/.

[muk]

Hi

Thanks for replying

Actually I am trying to build a package for connection pooling,I am working in a project where we are developing perl scripts to connect to DB2 database and perform some business logic in a unix environment.

The actual requirement is instead of connecting to the database n number of times,get a connection from the database keep it in a pool and reuse it,so the performance can be improved.

I tried this using resource pool but I am facing some issues,can you please advice on to which cpan module will be helpful.

[moritz]

How are these works related? If only one can be active at a time, just put the DB connection into a variable and reuse it in the different workers.

If not, look into DBI::Proxy and DBI::ProxyServer.

[mrstlee]

What sort of connections? TCP,other?
If you want a server for multiple clients then the POE framework might work for you:
POE

[onelesd]

Please show samples of your code and/or anything showing research you've done on the topic.

[muk]

Hi

Thanks for replying

I wrote a package,the code is as below,it is working fine but the only issue is that every time there is a request a new factory is created.

I want to know how to create the factory only once and reuse the connections in the pool.

use ResourcePool;
use ResourcePool::Factory;
use ResourcePool::Factory::DBI;

$db_timeOut = 120; # wait 60 seconds for connection

#User credentials
$data_source = 'dbi:DB2:databasename';
$username = 'username'; 
$auth = 'password';
eval{
        # set alarm to timeout current operation
        local $SIG{ALRM} = \&input_timed_out;
        alarm $db_timeOut; 
        $factory =  ResourcePool::Factory::DBI->new($data_source,$user
+name,$auth);
        $pool = ResourcePool->new($factory, Max => 2, MaxTry => 3, Sle
+epOnFail => [30, 60] ,MaxExecTry => 3);
    };
    alarm 0;  # reset the alarm
    
sub resource_pool
{
    $resource = $pool->get(); 
    return $resource ;
}

sub release_pool
{
    $pool->free($resource);
}
sub input_timed_out 
{
    print "connection timed out hence exiting\n";
    exit;
}

[onelesd]

If it's opening new connections every time I'd look into why it's closing the connections in the first place.

With 'Max' set to 2, $pool->get() should return undef when you try to exceed 2 connections.

Set up a test where you attempt to get 3 resources simultaneously and watch the database logs to see what's going on with the connections.

[Corion]

As connection pooling always needs knowledge about the underlying protocol for each connection, you will need to tell us more. If it is a database or custom program where each client makes connections to a server, it is quite possible that connection pooling is even prohibited by the license terms of your vendor.

[muk]

Hi

Thanks for replying

Actually I am trying to build a package for connection pooling,I am working in a project where we are developing perl scripts to connect to DB2 database and perform some business logic in a unix environment.

The actual requirement is instead of connecting to the database n number of times,get a connection from the database keep it in a pool where ever there is a request release a connection after using give it back to the pool,if the maximum limit is reached wait in a queue ,so the performance can be improved.

I tried this using resource pool but I am facing some issues,is there any other way to implement this.