Apache::DBI and NameVirtualHost

[gb <gb...@icsti.su>]

Hi all!

A have some problems with using apache::dbi and name virtual hosts.
I have four sites, each having its own login into database.
All of them listen to one ip using NameVirtualHost directive
Each site has a startup script with
Apache::DBI->connect_on_init

It seems to me that apache::dbi is not working properly, though in logs 
I can see that some database handlers are reused as it should be.
2228 Apache::DBI             need ping: yes
2228 Apache::DBI             already connected to '...'
2178 Apache::DBI             disconnect (overloaded)
2299 Apache::DBI             disconnect (overloaded)

I've also noticed that database connections number is not equal to httpd 
process count and not even equal to 4xhttpd process count (4 sites)

Am I doing something wrong? Can Apache::DBI hold several handlers for 
one httpd process? I suspect that user connects to different processes 
and the handler (dbname/username/pass) do not always match.

Re: Apache::DBI and NameVirtualHost

[gb <gb...@icsti.su>]

I use prefork mpm
The connection string in a startup script of each site is
Apache::DBI->connect_on_init("dbi:Oracle:SID", "login1", "pass1",{ 
PrintError => 0,AutoCommit => 0 });
I change only login and password for different sites. So 5 different 
connection strings

Interesting thing - raising the MaxClients value make the site work 
better, it doesn't hang, and I don't see errors on web pages, though it 
seems that amount of mistakes doesn't become smaller.

Geoffrey Young wrote:

>>>Apache::DBI will cache connections on a per-connection-string basis,
>>>once in
>>>each child process.  so, if each connection to a database is made using a
>>>different username each will be cached and you'll end up with
>>>users*processes number of cached connections in total.
>>>      
>>>
>
>  
>
>>The problem is that connection number is greater than users*processes! I
>>have 5 db users(one for each of five sites) and MaxClients = 90 but the
>>connection count can reach 500 limit easily on workload peaks and is
>>willing to grow further! This sites are very heavyloaded indeed.
>>I use Apache 2, mp2.0.1 and Apache::DBI -0.9901
>>    
>>
>
>are you using the worker or prefork mpm?  are you certain that your connect
>string is identical in each case (and that means identical - same AutoCommit
>settings, same user/pass, same _everything_)?
>
>--Geoff
>
>  
>

Re: Apache::DBI and NameVirtualHost

[Geoffrey Young <ge...@modperlcookbook.org>]

>> Apache::DBI will cache connections on a per-connection-string basis,
>> once in
>> each child process.  so, if each connection to a database is made using a
>> different username each will be cached and you'll end up with
>> users*processes number of cached connections in total.

>>
> The problem is that connection number is greater than users*processes! I
> have 5 db users(one for each of five sites) and MaxClients = 90 but the
> connection count can reach 500 limit easily on workload peaks and is
> willing to grow further! This sites are very heavyloaded indeed.
> I use Apache 2, mp2.0.1 and Apache::DBI -0.9901

are you using the worker or prefork mpm?  are you certain that your connect
string is identical in each case (and that means identical - same AutoCommit
settings, same user/pass, same _everything_)?

--Geoff

Re: Apache::DBI and NameVirtualHost

[gb <gb...@icsti.su>]

Geoffrey Young wrote:

>>Am I doing something wrong? Can Apache::DBI hold several handlers for
>>one httpd process? I suspect that user connects to different processes
>>and the handler (dbname/username/pass) do not always match.
>>    
>>
>
>Apache::DBI will cache connections on a per-connection-string basis, once in
>each child process.  so, if each connection to a database is made using a
>different username each will be cached and you'll end up with
>users*processes number of cached connections in total.
>
>HTH
>
>--Geoff
>
>  
>
The problem is that connection number is greater than users*processes! I 
have 5 db users(one for each of five sites) and MaxClients = 90 but the 
connection count can reach 500 limit easily on workload peaks and is 
willing to grow further! This sites are very heavyloaded indeed.
I use Apache 2, mp2.0.1 and Apache::DBI -0.9901

Re: Apache::DBI and NameVirtualHost

[Geoffrey Young <ge...@modperlcookbook.org>]

> Am I doing something wrong? Can Apache::DBI hold several handlers for
> one httpd process? I suspect that user connects to different processes
> and the handler (dbname/username/pass) do not always match.

Apache::DBI will cache connections on a per-connection-string basis, once in
each child process.  so, if each connection to a database is made using a
different username each will be cached and you'll end up with
users*processes number of cached connections in total.

HTH

--Geoff

Re: Apache::DBI and NameVirtualHost

["Randal L. Schwartz" <me...@stonehenge.com>]

>>>>> "gb" == gb  <gb...@icsti.su> writes:

gb> Each site has a startup script with
gb> Apache::DBI->connect_on_init

This may be your problem.  If the connection string is identical,
Apache::DBI caches it as "the same connection".  But if you then
do something unique per virthost in your connect_on_init, you're
working against the system there, and you get broken results.

One solution is to ensure that you use a different username/password
per virthost, so that the connection string varies.

-- 
Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095
<me...@stonehenge.com> <URL:http://www.stonehenge.com/merlyn/>
Perl/Unix/security consulting, Technical writing, Comedy, etc. etc.
See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training!