You are viewing a plain text version of this content. The canonical link for it is here.
Posted to modperl@perl.apache.org by c....@pobox.com on 2004/05/17 21:16:45 UTC
Apache::DBI and Oracle Sessions.
We are having a problem with the oracle sessions running wild. We
have five web servers that run 6 servers. once we restart all the web
servers, I check and see that we have 30 sessions.
With the Apache::DBI::ping, we should stay steady at 30 sessions (At
least that is how I understand it), but after a couple of hours, the
processes start to grow exponetially, finally pushing some 400
sessions, we restart. We have done nothing new in our setup for the
last year, and we can't seem to find a cause for this sudden explosion
of sessions. Of course the DBA is blaming Apache::DBI, but I can't
find any reason?
These are our settings for the server.
Timeout 300
KeepAlive Off
MaxKeepAliveRequests 0
KeepAliveTimeout 15
MinSpareServers 4
MaxSpareServers 8
StartServers 6
MaxClients 32
MaxRequestsPerChild 65
Listen 10.0.5.7:80
--
C Wayne Huling <c....@pobox.com>
--
Report problems: http://perl.apache.org/bugs/
Mail list info: http://perl.apache.org/maillist/modperl.html
List etiquette: http://perl.apache.org/maillist/email-etiquette.html
Re: Apache::DBI and Oracle Sessions.
Posted by Patrick Mulvany <pa...@firedrake.org>.
On Wed, May 19, 2004 at 09:40:41AM -0400, Geoffrey Young wrote:
> my $dbh = DBI->connect('dbi:Oracle:HELM', 'user', 'password');
>
>
>
> local $dbh->{AutoCommit} = 0;
> local $dbh->{PrintError} = 0;
>
One good use for local as if you just set the attribute the handle gets corrupted for its next usage.
doing :-
$dbh->{AutoCommit} = 0;
looks fine and works but because of the caching you end up with polution of the cached handle.
Some time ago I wrote a patch that worked around that very problem but local is a better solution as it prevents the problem from occuring in the first place.
Paddy
--
Report problems: http://perl.apache.org/bugs/
Mail list info: http://perl.apache.org/maillist/modperl.html
List etiquette: http://perl.apache.org/maillist/email-etiquette.html
Re: Apache::DBI and Oracle Sessions.
Posted by Geoffrey Young <ge...@modperlcookbook.org>.
Perrin Harkins wrote:
> Geoffrey Young wrote:
>
>> as for the the different DBI strings, are you using a post 5.8.0 perl? I
>> have suspected that the random hash foo in 5.8.1 (or whenever it was
>> added,
>> I forget) would muck up Apache::DBI's caching mechanism.
>
>
> No, the hash keys are sorted.
ah, right. thanks.
my memory just isn't what it used to be...
--Geoff
--
Report problems: http://perl.apache.org/bugs/
Mail list info: http://perl.apache.org/maillist/modperl.html
List etiquette: http://perl.apache.org/maillist/email-etiquette.html
Re: Apache::DBI and Oracle Sessions.
Posted by Perrin Harkins <pe...@elem.com>.
Geoffrey Young wrote:
> as for the the different DBI strings, are you using a post 5.8.0 perl? I
> have suspected that the random hash foo in 5.8.1 (or whenever it was added,
> I forget) would muck up Apache::DBI's caching mechanism.
No, the hash keys are sorted.
- Perrin
--
Report problems: http://perl.apache.org/bugs/
Mail list info: http://perl.apache.org/maillist/modperl.html
List etiquette: http://perl.apache.org/maillist/email-etiquette.html
Re: Apache::DBI and Oracle Sessions.
Posted by Geoffrey Young <ge...@modperlcookbook.org>.
please keep responses on list so everyone can benefit from the archives :)
c.w.huling@pobox.com wrote:
> I am exploring this right now. The problem though is the explosion of
> connections. we go from 100->600 in about 2 minutes. We can't
> correlate it to the load, and I can't find anywhere that I am not
> using the same DBInit routine to get a database handle.
your dba should be able to help you figure out if you're connecting under
multiple usernames.
as for the the different DBI strings, are you using a post 5.8.0 perl? I
have suspected that the random hash foo in 5.8.1 (or whenever it was added,
I forget) would muck up Apache::DBI's caching mechanism. IIRC, ask said it
wasn't an issue, but I'm not sure if he meant is was never an issue or isn't
an issue in more recent Apache::DBI releases.
ask?
--Geoff
--
Report problems: http://perl.apache.org/bugs/
Mail list info: http://perl.apache.org/maillist/modperl.html
List etiquette: http://perl.apache.org/maillist/email-etiquette.html
Re: Apache::DBI and Oracle Sessions.
Posted by Geoffrey Young <ge...@modperlcookbook.org>.
c.w.huling@pobox.com wrote:
> We are having a problem with the oracle sessions running wild. We
> have five web servers that run 6 servers.
> StartServers 6
> MaxClients 32
according to the your settings you _start_ 6 servers (well, child
processes). you allow up to 32 processes (each with it's own cached
database connection) with MaxClients 32.
http://httpd.apache.org/docs/mod/core.html#maxclients
so the max number of cached connections that you should see is 160. but as
pointed out by the other response in this thread, that 160 is only the
number of _absolutely identical_ DBI connect strings. make one connection
as user foo and one as user bar and you're up to 320 connections. make
other connections with different AutoCommit or RaiseError settings and
you've got a whole lot of connections sticking around.
one way around this is to change your code such that each DBI connect is
minimal, then setting database attributes later as required.
my $dbh = DBI->connect('dbi:Oracle:HELM', 'user', 'password');
local $dbh->{AutoCommit} = 0;
local $dbh->{PrintError} = 0;
HTH
--Geoff
--
Report problems: http://perl.apache.org/bugs/
Mail list info: http://perl.apache.org/maillist/modperl.html
List etiquette: http://perl.apache.org/maillist/email-etiquette.html
Re: Apache::DBI and Oracle Sessions.
Posted by Patrick Mulvany <pa...@firedrake.org>.
When using Apache::DBI you must remember that it caches the connections by using the connection string it you have two types of connection say one with autocommit on and the other with it off this is cached as two connections.
If you do not have a standard method/module for making your database connections then you have a chance that the two strings don't match. This is also true if you use two schemas these will appear as two cached connections.
Without more information about your system it is hard to advise further.
However I do note that you are using MaxClients 32 which would results in a max or 32 child processes (each with at least one Apache::DBI connection) per server which would max out at 192 processes if the system is under significant load.
Also it is worth looking at whether you are using dedicated listener processes on using a dispatcher but your DBA should be able to tell you more about that.
Some time ago I did do a patch that limited the number of connections a single instance of Apache::DBI could make but I am not sure this will help you here.
Hope it helps
Paddy
On Mon, May 17, 2004 at 03:16:45PM -0400, c.w.huling@pobox.com wrote:
>
> We are having a problem with the oracle sessions running wild. We
> have five web servers that run 6 servers. once we restart all the web
> servers, I check and see that we have 30 sessions.
>
> With the Apache::DBI::ping, we should stay steady at 30 sessions (At
> least that is how I understand it), but after a couple of hours, the
> processes start to grow exponetially, finally pushing some 400
> sessions, we restart. We have done nothing new in our setup for the
> last year, and we can't seem to find a cause for this sudden explosion
> of sessions. Of course the DBA is blaming Apache::DBI, but I can't
> find any reason?
>
> These are our settings for the server.
>
> Timeout 300
> KeepAlive Off
> MaxKeepAliveRequests 0
> KeepAliveTimeout 15
> MinSpareServers 4
> MaxSpareServers 8
> StartServers 6
> MaxClients 32
> MaxRequestsPerChild 65
> Listen 10.0.5.7:80
>
>
> --
> C Wayne Huling <c....@pobox.com>
>
> --
> Report problems: http://perl.apache.org/bugs/
> Mail list info: http://perl.apache.org/maillist/modperl.html
> List etiquette: http://perl.apache.org/maillist/email-etiquette.html
>
--
Report problems: http://perl.apache.org/bugs/
Mail list info: http://perl.apache.org/maillist/modperl.html
List etiquette: http://perl.apache.org/maillist/email-etiquette.html