mysqlperlapachemod-perl

Perl DBI connection pooling


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


Solution

  • 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;
    }