Rand Stats

DBIish::Pool

zef:rbt

DBIish::Pool - Database connection pooling for DBIish

SYNOPSIS

my %connection-parameters = database => 'foo', user => 'bar', password => secret();
my $pool = DBIish.new(driver => 'Pg', initial-size => 1, max-connections => 10, min-spare-connections => 1,
                     max-idle-duration => Duration.new(60), |%connection-parameters);

my $dbh = $pool.get-connection()

$dbh.do({SELECT 1});

$dbh.dispose;

DESCRIPTION

This module is useful for apps supporting multiple parallel users which arrive at an inconsistent rate, such as a web application. In addition to connection reuse it allow configuring a maximum number of simultaneous connections to ensure the database does not go over capacity.

Database connection reuse improves performance significantly for very simple transactions, or long distance networks using SSL encrypted connections. 300% has been seen within the same network for web requests, where each request was establishing a new connection.

To use, create a pool, then take a connection from that pool. The connection is returned to the pool when dispose is called. Calling dispose is important as otherwise you may exhaust the pool due to garbage collection being unpredictable.

See your database driver for a description of the connection parameters allowed. These are the same as the C<DBIish.connect> call.

my $pool = DBIish::Pool.new(driver => 'Pg', max-connections => 20, max-idle-duration => Duration.new(60),
    min-spare-connections => 3,  initial-size => 5, |%dbiish-connection-parameters);

sub do-db-work() {
  my $dbh = $pool.get-connection();

  my $sth = $dbh.prepare(q{ SELECT session_state FROM sessions WHERE session_id = ? });

  $sth.execute($session-id);
  my $ret = $sth.allrows();
  $dbh.dispose

  return $ret;
}

new

get-connection

Returns a connection from the pool, establishing a new connection if necessary, when one becomes available. The connection is checked for connectivity prior to returning it to the client.

Once max-connections is reached, this routine will not return a connection until one becomes available. Ensure you call dispose after finished using the connection to shorten this timeframe as garbage collection is not predictable.

If preferred, you may obtain a connection asynchronously.

my $dbh = await $pool.get-connection(:async);

Pool Statistics

A small Hash with pool connection statistics is available. This can be useful for automated monitoring purposes.

my %stats = $pool.stats();

Statistics fields include: