to the same database then using $h-do( "USE new_database" ); But it doesn't
switch to the new database (works fine using DBI.)
I decided as a temporary hack I'd try the following:
sub clean_processes {
my $self = shift;
my $dbs = $self->{dbs};
my $h = $dbs->{local};
my $sth = $h->prepare( "SHOW FULL PROCESSLIST" ) or warn "error " . $h->errstr;
$sth->execute or warn "error " . $h->errstr;
my $tbl_ary_ref = $sth->fetchall_arrayref({});
return unless( int( @$tbl_ary_ref ) > 50 );
foreach( @$tbl_ary_ref ) {
if( $_->{Command} ) {
if( ( $_->{Command} eq "Sleep" ) || ( $_->{Command} eq "Killed" ) ) {
if( $_->{Time} ) {
warn "Killing ID: $_->{Id} USER: $_->{User} DB: $_->{db}";
my $sth2 = $h->prepare( "KILL $_->{Id}" ) or warn "error " . $h->errstr;
$sth2->execute or warn "error " . $h->errstr;
}
}
}
}
return;
}
Not only does it not kill the processes, but it stops allowing for a connection to any database giving the following error:
[error] [client 127.0.0.1] DBD driver has not implemented the AutoCommit attribute at /usr/local/lib/perl5/site_perl/5.8.7/Apache/DBI.pm line 250.\n, referer: http://127.0.0.1/web_address/
When I watch the error_log and the $Apache::DBI::DEBUG = 2 I get one of the two following:
need ping: yes
12844 Apache::DBI new connect to 'database=boysie_habitat;host=localhostrooth1jAcK36AutoCommit=1PrintError=1RaiseError=1Username=root'
12844 Apache::DBI disconnect (overloaded)
or:
need ping: yes
12848 Apache::DBI already connected to 'database=boysie_habitat;host=localhostUsernamePasswordAutoCommit=1PrintError=1RaiseError=1Username=root'
12848 Apache::DBI disconnect (overloaded)
I will be a list of them 5 at a time (I'm guessing one for each child.) Then as long as I only use that database no problem. As soon
as I switch databases which is the only thing that changes in my new connections, I spawns all new database threads. So if I have
100 databases to connect to I get ( 100 * as many child apache process as the requests came in on). I don't think setting my max
connections 700+ makes sense especially since the remaining connections are shown as sleeping.
Is there a way to connect to more than one database without spawning "extra" mysql connections?
Also, my local development is on OSX, where as my production environment is Red Hat Linux.
I'm using Apache2 and MP2 for both, only proxy in the production environment though.
PS For some reason my email responses aren't making it to the list, sorry...
Thanks,
Boysenberry
boysenberrys.com | habitatlife.com | selfgnosis.com
On Feb 8, 2006, at 6:22 PM, Perrin Harkins wrote:
On Wed, 2006-02-08 at 11:51 -0600, Boysenberry Payne wrote:
What I'm seeing is 37 mysql threads being created for 7 apache
children. It would seem as
though I would need to create a 5/1 ratio of mysql connections to
apache children. Is that normal?
No. If you use the same connection info every time, you should only see
one connection per child. If you have two databases, you should see two
connections per child. Maybe you are still not using the same connect
string each time? The debug output should tell you if it thinks it has
a connection already or not.
- Perrin
