Perl Connection Pooling

Question

Right now we have a large perl application that is using raw DBI to connect to MySQL and execute SQL statements. It creates a connection each time and terminates. Were starting to approach mysql's connection limit (200 at once)

It looks like DBIx::Connection supports application layer connection pooling.

Has anybody had any experience with DBIx::Connection?. Are there any other considerations for connection pooling?

I also see mod_dbd which is an Apache mod that looks like it handles connection pooling. http://httpd.apache.org/docs/2.1/mod/mod_dbd.html

Mindful

I use DBIx::Connector (what DBIx::Class uses internally) and it's wonderful... I pool these connections with a Moose object wrapper that hands back existing object instances if the connection parameters are identical. It's not difficult to roll your own. – Chaetognath

@Chaetognath - worth an answer, IMHO – Recoil

@DVK: ok, I expanded on this with an answer... – Chaetognath

@Ether, your comment should have been an answer so that it could be accepted since it's exactly what the OP asked. – Charentemaritime

what is raw DBI, how does it differ from DBI? – Atavism

Answer 1

I don't have any experience with DBIx::Connection, but I use DBIx::Connector (essentially what DBIx::Class uses internally, but inlined) and it's wonderful...

I pool these connections with a Moose object wrapper that hands back existing object instances if the connection parameters are identical (this would work the same for any underlying DB object):

package MyApp::Factory::DatabaseConnection;
use strict;
use warnings;

use Moose;

# table of database name -> connection objects
has connection_pool => (
    is => 'ro', isa => 'HashRef[DBIx::Connector]',
    traits  => ['Hash'],
    handles => {
        has_pooled_connection => 'exists',
        get_pooled_connection => 'get',
        save_pooled_connection => 'set',
    },
    default => sub { {} },
);

sub get_connection
{
    my ($self, %options) = @_;

    # some application-specific parsing of %options here...

    my $obj;
    if ($options{reuse})
    {
        # extract the last-allocated connection for this database and pass it
        # back, if there is one.
        $obj = $self->get_pooled_connection($options{database});
    }

    if (not $obj or not $obj->connected)
    {
        # look up connection info based on requested database name
        my ($dsn, $username, $password) = $self->get_connection_info($options{database});
        $obj = DBIx::Connector->new($dsn, $username, $password);

        return unless $obj;

        # Save this connection for later reuse, possibly replacing an earlier
        # saved connection (this latest one has the highest chance of being in
        # the same pid as a subsequent request).
        $self->save_pooled_connection($options{database}, $obj) unless $options{nosave};
    }

    return $obj;
}

Chaetognath

could you please share the entire code also please provide the solution for achieving with Rose::DB handler – Auster

@Auster that is the entire code. and I wouldn't recommend using Rose::DB in any case. – Chaetognath

is get_connection_info predefined function? if not kindly share that function too. – Auster

I have tried ResourcePool Perl module for database pooling. for every HTTP request, it creates new pool instead of reusing it. find my code below – Auster

@Auster you're supposed to write get_connection_info yourself, to provide the appropriate credentials given the database name. – Chaetognath

$obj = $self->get_pooled_connection($database); Can you tell me what exactly the $database variable holds? I tried but getting the following errors Global symbol "$database" requires explicit package name at ttdb5.pl line 32. – Auster

Does your connection pool stores in web server? It reuses the database handle across the user request. One final thing what's your socket connection for MySQL i.e either in Established or TIME_WAIT state. – Auster

It's similar to JDBC connection pool. – Auster

@Auster database is the database name, that is used to get credentials info (dsn, username, password) and to as the caching key for to save the connection object. – Chaetognath

@Auster answer from Ether uses Moose method delegation a lot. Please have a look at Moose delegation documentation which will help understand the code better. – Duplex

Answer 2

Just making sure: you know about DBI->connect_cached(), right? It's a drop-in replacement for connect() that reuses dbh's, where possible, over the life of your perl script. Maybe your problem is solvable by adding 7 characters :)

And, MySQL's connections are relatively cheap. Running with your DB at max_connections=1000 or more won't by itself cause problems. (If your clients are demanding more work than your DB can handle, that's a more serious problem, one which a lower max_connections might put off but of course not solve.)

Roseannaroseanne

I think free version of MySql only supports 200 connections right? – Mindful

The free version of MySQL isn't crippled in any way. (Unless you count the GPL, haha.) Even large and poorly-written applications shouldn't need more than a couple thousand, but you can set max_connections as high as you want, if you have the memory and file descriptors. – Roseannaroseanne

There is no "paid" version of MySQL. It's all GPL. At best, Oracle might have a support contract that you could pay for, but the software itself is completely free and uncrippled. – Defensible