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 = DBHish::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

• min-spare-connections

  The number of idle connections to keep around. These are ready for immediate use. Busy multi-threaded workloads will want to raise this above 1 as there is a short time between when a connection is disposed and when it will be ready for use again.

• initial-size

  The number of connections to create when the pool is established. This may be kept equal to min-spare-connections unless your app requires a very fast initial response time and is regularly restarted during peak periods.

• max-idle-duration

  Connections which have not been used in this time period will be slowly closed, unless required to meet the min-spare-connections.

• max-connections

  Maximum number of database connections, including those currently being scrubbed of session state for reuse. Overall performance is often better if the database has a consistent load and spikes are smoothed out.

• |%dbiish-connection-parameters are whatever DBIish allows. For a pool for a PostgreSQL driver might be established like this:

  my $pool = DBIish::Pool.new(driver => 'Pg', dbname => 'dbtest', user => 'postgres', port => 5432, max-connections => 20);
  

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:

• inuse ➡ Number of connections currently in use.
• idle ➡ Number available for immediate use.
• starting ➡ Number starting up.
• scrub ➡ Numbers recently disposed, currently being scrubbed.
• total ➡ Total of the inuse, idle, starting, and scrub counters. Due to short race conditions, this may not add up at times.
• waiting ➡ Number of unfilled connect calls.