view release on metacpan or  search on metacpan

lib/Apache2/Controller/DBI/Connector.pm  view on Meta::CPAN

=head1 WARNING - DATABASE MEMORY USAGE

Because a reference persists in package space, the database handle
will remain connected after a request ends.

Usually Apache will rotate requests through child processes.

This means that on a lightly-loaded server with a lot of spare child processes,
you will quickly get a large number of idle database connections, one per child.

To solve this you need to set your database handle idle timeout
to some small number of seconds, say 5 or 10.  Then you load
L<Apache::DBI> in your Apache config file so they automatically
get reconnected if needed.  

Then when you get a load increase, handles are connected that persist
across requests long enough to handle the next request, but during
idle times, your database server conserves resources.

There are various formulas for determining how much memory is
needed for the maximum number of connections your database server 
provides.  MySQL has a formula in their docs somewhere to calculate
memory needed for InnoDB handles. It is weird. 

When using
persistent database connections, it's a good idea to limit the
max number of Apache children to the max number of database connections
that your server can provide.  Find a formula from your vendor's 
documentation, if one exists, or wing it.

=cut

use strict;
use warnings FATAL => 'all';
use English '-no_match_vars';

use base qw( 
    Apache2::Controller::NonResponseBase 
    Apache2::Controller::Methods
);

use Log::Log4perl qw(:easy);
use YAML::Syck;

use Apache2::Const -compile => qw( OK SERVER_ERROR );

use Apache2::Controller::X;
#use Apache2::Controller::DBI;
use DBI;

=head1 METHODS

=head2 process

Gets DBI connect arguments by calling C<< $self->dbi_connect_args() >>,
then connects C<< $dbh >> and stashes it in C<< $r->pnotes->{a2c}{dbh} >>
or the name you select.

The $dbh has a reference in package space, so controllers using it
should always call commit or rollback.  It's good practice to use
C<< eval >> anyway and throw an L<Apache2::Controller::X> or
your subclass of it (using C<< a2cx() >>,
so you can see the function path trace in the logs when the error occurs.

The package-space $dbh for the child persists across requests, so
it is never destroyed.  However, it is assigned with C<< DBI->connect() >>
on every request, so that L<Apache::DBI> will cache the database handle and
actually connect it only if it cannot be pinged.

=cut

# the dbh is always connected, but there is only one instance of it.
my $dbh;
sub process {
    my ($self) = @_;

    my $r = $self->{r};

    # connect the database:
    my @args        = $self->dbi_connect_args;
    my $pnotes_name = $self->dbi_pnotes_name;

    a2cx "Already a dbh in pnotes->{$pnotes_name}"
        if exists $r->pnotes->{a2c}{$pnotes_name};

    my $dbi_subclass = $self->get_directive('A2C_DBI_Class');

    if ($dbi_subclass) {
        eval '$r->pnotes->{a2c}{'.$pnotes_name.'} = '.$dbi_subclass.'->connect(@args)';
    }
    else {
        eval { $r->pnotes->{a2c}{$pnotes_name} = DBI->connect(@args) };
    }
    a2cx $EVAL_ERROR if $EVAL_ERROR;

    # push the log rollback handler if requested
    if ($self->dbi_cleanup) {
        # using a closure on '$pnotes_name' ... is this kosher?
        # maybe this should push a class name of a separate cleanup class,
        # which calls get_directives()?
        # or, re-emulate getting the directive name?  argh
        $r->push_handlers(PerlLogHandler => sub {
            my ($r) = @_;
            my $dbh = $r->pnotes->{a2c}{$pnotes_name} || return Apache2::Const::OK;
            if ($dbh->FETCH('BegunWork')) {
                DEBUG("Cleanup handler: in txn.  Rolling back...");
                eval { $dbh->rollback() };
                if ($EVAL_ERROR) {
                    my $error = "cleanup handler cannot roll back: $EVAL_ERROR";
                    ERROR($error);
                    $r->status_line(__PACKAGE__." $error");
                    return Apache2::Const::SERVER_ERROR;
                }
                else {
                    DEBUG("Cleanup handler rollback successful.");
                }
            }
            else {
                DEBUG("Cleanup handler not in txn.");
            }
            return Apache2::Const::OK;