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

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. — Ether, Jul 16 '10 at 18:58
@Ether, your comment should have been an answer so that it could be accepted since it's exactly what the OP asked. — mpeters, Jul 16 '10 at 20:58

Ether · Accepted Answer · 2017-06-19T21:54:12.137

9

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

edited Jun 19 '17 at 21:54

answered Jul 16 '10 at 20:50

Ether

53,118
13
86
159

could you please share the entire code also please provide the solution for achieving with Rose::DB handler – Sethu Jun 10 '17 at 21:24
@Sethu that is the entire code. and I wouldn't recommend using Rose::DB in any case. – Ether Jun 11 '17 at 21:57
is get_connection_info predefined function? if not kindly share that function too. – Sethu Jun 12 '17 at 08:44
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 – Sethu Jun 13 '17 at 15:01
@Sethu you're supposed to write `get_connection_info` yourself, to provide the appropriate credentials given the database name. – Ether Jun 13 '17 at 20:13
$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. – Sethu Jun 16 '17 at 12:39
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. – Sethu Jun 19 '17 at 13:25
It's similar to JDBC connection pool. – Sethu Jun 19 '17 at 13:25
@Sethu `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. – Ether Jun 19 '17 at 21:55
@Sethu answer from Ether uses Moose method delegation a lot. Please have a look at [Moose delegation documentation](https://metacpan.org/pod/distribution/Moose/lib/Moose/Manual/Delegation.pod) which will help understand the code better. – Sachin Dangol Dec 10 '18 at 09:04

score 6 · Answer 2 · answered Aug 14 '10 at 17:15

6

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

answered Aug 14 '10 at 17:15

Jamie McCarthy

755
3
6

I think free version of MySql only supports 200 connections right? – bonez Sep 02 '10 at 13:58
1

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 Sep 06 '10 at 13:23
2

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. – SineSwiper Apr 25 '16 at 21:41

Perl Connection Pooling

2 Answers2

Linked

Related