Too many DB connection with mod_perl2 moose application

Question

I have a mod_perl2 based web app that requires a connection to a mysql database. I have implemented the SQL connection specifics in a moose role.

Simplified, the role looks as follows:

package Project::Role::SQLConnection;

use Moose::Role;
use DBIx::Connector;

has 'connection' => (is => 'rw', lazy_build => 1);
has 'dbh' => (is => 'rw', lazy_build => 1);
has 'db'    => ( is => 'rw', default => 'alcatelRSA');
has 'port'  => ( is => 'rw', default => 3306);
has 'host'  => ( is => 'rw', default => '10.125.1.21');
has 'user'  => ( is => 'rw', default => 'tools');
has 'pwd'   => ( is => 'rw', default => 'alcatel');


#make sure connection is still alive...
before dbh => sub {
    my $self = shift;
    $self->connection->run(fixup => sub { $_->do('show tables') });
};

sub _build_dbh {
    my $self = shift;
    return $self->connection->dbh;
}

sub _build_connection {
    my $self = shift;
    my $dsn = 'DBI:mysql:'.$self->db.';host='.$self->host.';port='.$self->port;
    my $conn = DBIx::Connector->new($dsn, $self->user, $self->pwd);
    return $conn;
}

no Moose::Role;
1;

I then use this role in all moose classes that require a connection to the DB with a

 with qw(Project::Role::SQLConnection);

statement.

While this works well when a few objects are created, i soon run into troubles when to many objects are created. In the httpd log for instance, i get the error:

DBI connect('alcatelRSA;host=10.125.1.21;port=3306','tools',...) failed: Too many connections at C:/Perl/site/lib/DBIx/Connector.pm line 30

I thought about using DBIx::Connectors "disconnect" call to close the connection to the database each time, but the performance impact seem to severe to open / close connections as required.

Do you have any alternative suggestions on this problem?

Answer 1

Are you copying the dbh and using it in places when the out of the DBIx::Connector object goes out of scope? The the documentation specifically says not to do that. Instead, save the DBIx::Connector object itself, and delegate the dbh method call to it with a handles option in the attribute.

This is what I do (I actually just posted this code yesterday in response to another question; funny how DB questions come in packs):

has dbixc => (
    is => 'ro', isa => 'DBIx::Connector',
    lazy_build => 1,
    # DO NOT save a copy of the dbh. Use this accessor every time, as
    # sometimes it will change unexpectedly!
    handles => [ qw(dbh) ],
);

sub _build_dbixc
{
    my $this = shift;
    DBIx::Connector->new(
        $this->dsn,
        $this->username,
        $this->password,
        $this->connect_options,
    );
}

sub call_dbh
{
    my $this = shift;
    my $method = shift;
    my @args = @_;

    # the normal behaviour -- pings on every dbh access
    #return $this->dbh->$method(@args);

    # the smart behaviour -- presume the DB connection still works
    $this->dbixc->run(fixup => sub { $_->$method(@args) });
}

You may also want to look at how many mod_perl processes you are allowing. Each separate process or thread necessarily must have its own DB connection, but there may be more than one -- so you probably also need to ensure that the code above only runs (i.e a db management object is built) only once per process, and each subsequent attempt to build such an object simply returns a copy of the existing one. One easy way to do this is with MooseX::Singleton, but this introduces other design issues of its own.