views:

73

answers:

2

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

+3  A: 

I don't have any experience with DBIx::Connection, but 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 (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 ($this, %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 = $this->get_pooled_connection($database);
    }

    if (not $obj or not $obj->connected)
    {
        # look up connection info based on requested database name
        my ($dsn, $username, $password) = $this->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).
        $this->save_pooled_connection($database, $obj) unless $options{nosave};
    }

    return $obj;
}
Ether
+1  A: 

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.)

Jamie McCarthy
I think free version of MySql only supports 200 connections right?
bonez
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](http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_max_connections) as high as you want, if you have the memory and file descriptors.
Jamie McCarthy