Apache::DBI: number of mysql connections vary wildly

[John Cameron <jo...@n6.com.au>]

Hi, I am using mod_perl (1.24_01) with Apachi::DBI and Mysql (3.23.37).

I am finding that the number of open number of database connections I have
open at one time is varying wildly.

When I first start Apache and Mysql there are only 8 connections made. Why
8? I have specified that there should be 20 Apache child processes. Should
the db connections match that?

After load on the webserver starts, the number of connections grows, usually
to 20 or below. This is fine, I would expect that.

Then, Suddenly, the number of connctions jumps up to 50 or more! Sometimes
this falls back to below 20, but sometimes this keeps climbing. Server load
hits over 50 (99.9% taken by mysql) and the system grinds to a halt.

Since I am using Apache::DBI, I thought there was meant to be 20 db
connections always, and no more or less.

I am pretty sure that we are always disconnecting our database connections
manually (Though doesn't Apache::DBI override that anyway, and keep one open
per apache process?).

Has anybody had similar issues? Any suggestions? Our apache startup script
is below.

Thanks,
John

--------------------------------------------------------
/etc/httpd/perl/startup.pl
--------------------------------------------------------
#!/usr/bin/perl
use strict;

# load up necessary perl function modules to be able to call from Perl-SSI
# files.  These objects are reloaded upon server restart (SIGHUP or SIGUSR1)
# if PerlFreshRestart is "On" in httpd.conf (as of mod_perl 1.03).

# only library-type routines should go in this directory.

use lib "/harnesslink/live_html/lib";

# make sure we are in a sane environment.
$ENV{GATEWAY_INTERFACE} =~ /^CGI-Perl/ or die "GATEWAY_INTERFACE not Perl!";

use Apache::Registry ();       # for things in the "/programs" URL
use Apache::DBI;
use CGI ();
use Data::Dumper ();
use POSIX ();
use Carp ();
use Image::Magick ();
use HTML::Template ();
use Benchmark::Timer ();
use Date::Manip ();
use Class::Date ();
use MD5 ();
use FreezeThaw ();
use File::Scan ();

# pull in things we will use in most requests so it is read and compiled
# exactly once
use DBD::mysql ();

# Create persistant database connections

Apache::DBI->connect_on_init
(
"DBI:mysql:harnesslink",
"root",
"",
{
 AutoCommit => 1,
}
);

$Apache::DBI::DEBUG = 1;

1;

Re: Apache::DBI: number of mysql connections vary wildly

[Ged Haywood <ge...@www2.jubileegroup.co.uk>]

Hi there,

On Thu, 17 Oct 2002, John Cameron wrote:

> I am finding that the number of open number of database connections I have
> open at one time is varying wildly.
> 
> Then, Suddenly, the number of connctions jumps up to 50 or more! Sometimes
> this falls back to below 20, but sometimes this keeps climbing. Server load
> hits over 50 (99.9% taken by mysql) and the system grinds to a halt.

Your Apache is spawning new children to serve multiple concurrent
requests.  It's supposed to do that.  The extra children are opening
connections to the database.  When it has more children than it needs
it kills off surplus ones which closes the extra database connections.
If your machine can't handle the load you need to reduce the possible
load.  Check the value of MaxClients in httpd.conf.  You can read more
about this in the (admittedly intimidating:) documentation, see the
mod_perl home page http://perl.apache.org for some links.

73,
Ged.