What if you'd write all your SQL-Statements in a fully qualified manner? ---------------8<--------------- SELECT * FROM $DB1.$TABLE1; SELECT * FROM $DB2.$TABLE1; ---------------8<---------------
Tom
Boysenberry Payne wrote:
> The only difference in each connection is the database. I tried connecting
> 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
>
>
>
signature.asc
Description: OpenPGP digital signature
