You are viewing a plain text version of this content. The canonical link for it is here.
Posted to modperl@perl.apache.org by Artem Kuchin <ma...@itlegion.ru> on 2009/10/20 18:59:13 UTC
DBI Connectons accumulate under Mod_perl
Hello!
I have a very weird situation. I use MYSQL. Apache 2.2 with mod_perl2
O use Modperl::RegistryBB to run the script, but the script itself is
written very nicely
for mod_perl. That is no globals at all. Everything is in sub handler.
I do not use Apache::DBI, just plain "use DBI;"
So, what it does (basic idea)
sub handler {
db->connect();
.. does all the thing...
db->disconnect();
print $output;
}
however, whe i do in mysql
show processlist;
I see many connection hanging in sleep state and they grow and grow slowly!
Any idea why this happens?
Artem
Re: DBI Connectons accumulate under Mod_perl
Posted by William T <di...@gmail.com>.
On Tue, Oct 20, 2009 at 9:59 AM, Artem Kuchin <ma...@itlegion.ru> wrote:
>
> however, whe i do in mysql
>
> show processlist;
>
> I see many connection hanging in sleep state and they grow and grow slowly!
> Any idea why this happens?
Could be many things. Maybe multiple hits to different Apache
children are opening new DB connections before anyone of them finish.
If your experiencing slow page load as time goes on you may also have
table locking or row locking issues.
Or it could be that some code other than yours brings in Apache DBI.
I suggest turning on DBI debugging for a short while.
-wjt
Re: DBI Connectons accumulate under Mod_perl
Posted by Perrin Harkins <ph...@gmail.com>.
On Thu, Nov 12, 2009 at 2:22 AM, Kulasekaran, Raja
<Ra...@netapp.com> wrote:
> Below is the log of $Apache::DBI::DEBUG = 2;
This looks fine. I don't think anything is wrong with your setup.
I'm guessing Oracle keeps those processes around for a while in case
they are needed again. You might want to see if you can tune Oracle
to kill the child processes sooner.
- Perrin
RE: DBI Connectons accumulate under Mod_perl
Posted by "Kulasekaran, Raja" <Ra...@netapp.com>.
Hi,
Below is the log of $Apache::DBI::DEBUG = 2;
[Thu Nov 12 12:45:34 2009] [notice] SIGHUP received. Attempting to
restart
32162 Apache::DBI PerlChildInitHandler
32162 Apache::DBI need ping: yes
32163 Apache::DBI PerlChildInitHandler
32163 Apache::DBI need ping: yes
32164 Apache::DBI PerlChildInitHandler
32164 Apache::DBI need ping: yes
32165 Apache::DBI PerlChildInitHandler
32165 Apache::DBI need ping: yes
32166 Apache::DBI PerlChildInitHandler
32166 Apache::DBI need ping: yes
32167 Apache::DBI PerlChildInitHandler
32167 Apache::DBI need ping: yes
32168 Apache::DBI PerlChildInitHandler
32168 Apache::DBI need ping: yes
32169 Apache::DBI PerlChildInitHandler
[Thu Nov 12 12:45:35 2009] [notice] Apache/2.2.13 (Unix) PHP/5.2.10
mod_perl/2.0.4 Perl/v5.8.8 configured -- resuming normal operations
32169 Apache::DBI need ping: yes
32163 Apache::DBI new connect to
'xxxAutoCommit=1PrintError=1RaiseError=1Username=xxx'
32162 Apache::DBI new connect to
'xxxAutoCommit=1PrintError=1RaiseError=1Username=xxx'
32164 Apache::DBI new connect to
'xxxAutoCommit=1PrintError=1RaiseError=1Username=xxx'
32166 Apache::DBI new connect to
'xxxAutoCommit=1PrintError=1RaiseError=1Username=xxx'
32165 Apache::DBI new connect to
'xxxAutoCommit=1PrintError=1RaiseError=1Username=xxx'
32168 Apache::DBI new connect to
'xxxAutoCommit=1PrintError=1RaiseError=1Username=xxx'
32167 Apache::DBI new connect to
'xxxAutoCommit=1PrintError=1RaiseError=1Username=xxx'
32169 Apache::DBI new connect to
'xxxAutoCommit=1PrintError=1RaiseError=1Username=xxx'
32162 Apache::DBI push PerlCleanupHandler
32162 Apache::DBI need ping: yes
32162 Apache::DBI already connected to
'xxxAutoCommit=1PrintError=1RaiseError=1Username=xxx'
32162 Apache::DBI PerlCleanupHandler
-Raja
-----Original Message-----
From: Perrin Harkins [mailto:pharkins@gmail.com]
Sent: Wednesday, November 11, 2009 11:26 PM
To: Artem Kuchin
Cc: Kulasekaran, Raja; modperl@perl.apache.org
Subject: Re: DBI Connectons accumulate under Mod_perl
On Tue, Nov 10, 2009 at 10:04 AM, Artem Kuchin <ma...@itlegion.ru>
wrote:
> The weirdest thing is that
> there are two sites, running
> pretty much the same software (minor changes to user part, no changes
to db
> part). Connections
> from one site accumulate, connection from other site do not accmulate
-
> disconnect work fine.
Your disconnect() calls should not not do anything if Apache::DBI is
loaded. Make sure you load it before you load DBI.
To see what's happening, turn on debugging by setting
$Apache::DBI::DEBUG to 2 after you load Apache::DBI.
- Perrin
Re: DBI Connectons accumulate under Mod_perl
Posted by Perrin Harkins <ph...@gmail.com>.
On Tue, Nov 10, 2009 at 10:04 AM, Artem Kuchin <ma...@itlegion.ru> wrote:
> The weirdest thing is that
> there are two sites, running
> pretty much the same software (minor changes to user part, no changes to db
> part). Connections
> from one site accumulate, connection from other site do not accmulate -
> disconnect work fine.
Your disconnect() calls should not not do anything if Apache::DBI is
loaded. Make sure you load it before you load DBI.
To see what's happening, turn on debugging by setting
$Apache::DBI::DEBUG to 2 after you load Apache::DBI.
- Perrin
Re: DBI Connectons accumulate under Mod_perl
Posted by "David E. Wheeler" <da...@kineticode.com>.
On Nov 11, 2009, at 6:22 PM, Kulasekaran, Raja wrote:
> No. I could see that oracle instances are still alive.
That shouldn't be, of course. Did you run the DBI trace mode as Perrin suggested?
Best,
David
RE: DBI Connectons accumulate under Mod_perl
Posted by "Kulasekaran, Raja" <Ra...@netapp.com>.
No. I could see that oracle instances are still alive.
-----Original Message-----
From: David E. Wheeler [mailto:david@kineticode.com]
Sent: Wednesday, November 11, 2009 11:20 PM
To: Kulasekaran, Raja
Cc: Artem Kuchin; modperl@perl.apache.org
Subject: Re: DBI Connectons accumulate under Mod_perl
On Nov 10, 2009, at 9:34 PM, Kulasekaran, Raja wrote:
> I'm connecting against oracle. So for every request it establish the
> connection and it remains stable even though the request
> has been completed successfully .
That sounds right, as the Apache process that handles the requests will
stick around to handle more requests. But when that process dies, it
should disconnect from the database. Does it?
Best,
David
Re: DBI Connectons accumulate under Mod_perl
Posted by "David E. Wheeler" <da...@kineticode.com>.
On Nov 10, 2009, at 9:34 PM, Kulasekaran, Raja wrote:
> I'm connecting against oracle. So for every request it establish the
> connection and it remains stable even though the request
> has been completed successfully .
That sounds right, as the Apache process that handles the requests will stick around to handle more requests. But when that process dies, it should disconnect from the database. Does it?
Best,
David
RE: DBI Connectons accumulate under Mod_perl
Posted by "Kulasekaran, Raja" <Ra...@netapp.com>.
I'm connecting against oracle. So for every request it establish the
connection and it remains stable even though the request
has been completed successfully .
-Raja
-----Original Message-----
From: David E. Wheeler [mailto:david@kineticode.com]
Sent: Tuesday, November 10, 2009 11:03 PM
To: Kulasekaran, Raja
Cc: Artem Kuchin; modperl@perl.apache.org
Subject: Re: DBI Connectons accumulate under Mod_perl
On Nov 10, 2009, at 9:19 AM, Kulasekaran, Raja wrote:
> I'm using Apache::DBI and DBI->connect (persistence connection)
>
> So, Do I need to call $dbh->disconnect() for each child to close the
> connection ?
No, because Apache::DBI turns it into a no-op.
Apache::DBI is a very weird beast, with unanticipated
action-at-a-distance issues that crop up here and there. It's crazy
stuff like this that led me to abandon it -- at first for
DBI->connect_cached (though I had to do some extra work to make sure it
was safe), and more recently for DBIx::Connector, which does that extra
work for me.
Can you see what PIDs MySQL is accepting connections from? Are they the
same as currently-running Apache processes? Or are (some of them) from
dead Apache child processes?
Best,
David
Re: DBI Connectons accumulate under Mod_perl
Posted by "David E. Wheeler" <da...@kineticode.com>.
On Nov 10, 2009, at 9:19 AM, Kulasekaran, Raja wrote:
> I'm using Apache::DBI and DBI->connect (persistence connection)
>
> So, Do I need to call $dbh->disconnect() for each child to close the
> connection ?
No, because Apache::DBI turns it into a no-op.
Apache::DBI is a very weird beast, with unanticipated action-at-a-distance issues that crop up here and there. It's crazy stuff like this that led me to abandon it -- at first for DBI->connect_cached (though I had to do some extra work to make sure it was safe), and more recently for DBIx::Connector, which does that extra work for me.
Can you see what PIDs MySQL is accepting connections from? Are they the same as currently-running Apache processes? Or are (some of them) from dead Apache child processes?
Best,
David
RE: DBI Connectons accumulate under Mod_perl
Posted by "Kulasekaran, Raja" <Ra...@netapp.com>.
I'm using Apache::DBI and DBI->connect (persistence connection)
So, Do I need to call $dbh->disconnect() for each child to close the
connection ?
Raja .
-----Original Message-----
From: David E. Wheeler [mailto:david@kineticode.com]
Sent: Tuesday, November 10, 2009 10:43 PM
To: Artem Kuchin
Cc: Kulasekaran, Raja; modperl@perl.apache.org
Subject: Re: DBI Connectons accumulate under Mod_perl
On Nov 10, 2009, at 7:04 AM, Artem Kuchin wrote:
> You mean each child process creates a new database CONNECTION (not
process) ?
> The process is just one multhreaded mysql. But this is exactly what i
want. I do not want any
> connection sharing because of the locking issues (lock tables). But
they still accumulate and after a
> couple of hours mysql runs out of connections. The weirdest thing is
that there are two sites, running
> pretty much the same software (minor changes to user part, no changes
to db part). Connections
> from one site accumulate, connection from other site do not accmulate
- disconnect work fine.
Might you have connections starting in parent processes and not getting
dropped? Are you using Apache::DBI or DBI->connect_cached or
DBIx::Connector?
Best,
David
Re: DBI Connectons accumulate under Mod_perl
Posted by "David E. Wheeler" <da...@kineticode.com>.
On Nov 13, 2009, at 1:47 AM, Artem Kuchin wrote:
>> Might you have connections starting in parent processes and not getting dropped? Are you using Apache::DBI or DBI->connect_cached or DBIx::Connector?
>>
>> Best,
>>
>> David
>>
> Nope, i don't use those. Just plain DBI. Parent process does not open up any connections.
Then you need to call DBI->disconnect when a process shuts down.
Best,
David
Re: DBI Connectons accumulate under Mod_perl
Posted by "David E. Wheeler" <da...@kineticode.com>.
On Nov 16, 2009, at 1:10 AM, Artem Kuchin wrote:
> I am the original poster. Someone else has stolen my thread.
> Anyway.
> I AM calling disconnect and the thing is wrapped in
> sub handler {
> my $db=...
>
> ...
>
> $db->disconnect();
> }
>
> So, everything goes out of scope when handler finishes. I made it so to play nice with mod_per. NO globals
> at all.
>
> The apache run two site with such code. One site does not have a problem another builds up the connection.
> It might be related to windows (it is all under windows and i never had such problem under freebsd).
Maybe you have a system call or some other forking call that can lead to stray handles? Some Perl modules will do it without you knowing. I suggest switching to DBIx::Connector, which detects such things, to see if it helps.
Best,
David
Re: DBI Connectons accumulate under Mod_perl
Posted by Artem Kuchin <ma...@itlegion.ru>.
Perrin Harkins:
> On Fri, Nov 13, 2009 at 4:47 AM, Artem Kuchin <ma...@itlegion.ru> wrote:
>
>> Nope, i don't use those. Just plain DBI. Parent process does not open up any
>> connections.
>>
>
> This is getting really confusing because two of you are on this thread
> with completely different problems.
>
> Artem, if you aren't using Apache::DBI and your connections are
> building up, it means you're keeping the connections around in a
> global or closure variable and not calling disconnect. Since you
> aren't using something to keep your connections persistent, you should
> call disconnect at the end of every request. You should also look at
> your code to see if you put $dbh in a global somewhere.
>
> - Perrin
>
I am the original poster. Someone else has stolen my thread.
Anyway.
I AM calling disconnect and the thing is wrapped in
sub handler {
my $db=...
...
$db->disconnect();
}
So, everything goes out of scope when handler finishes. I made it so to
play nice with mod_per. NO globals
at all.
The apache run two site with such code. One site does not have a problem
another builds up the connection.
It might be related to windows (it is all under windows and i never had
such problem under freebsd).
Artem
Re: DBI Connectons accumulate under Mod_perl
Posted by Perrin Harkins <ph...@gmail.com>.
On Fri, Nov 13, 2009 at 4:47 AM, Artem Kuchin <ma...@itlegion.ru> wrote:
> Nope, i don't use those. Just plain DBI. Parent process does not open up any
> connections.
This is getting really confusing because two of you are on this thread
with completely different problems.
Artem, if you aren't using Apache::DBI and your connections are
building up, it means you're keeping the connections around in a
global or closure variable and not calling disconnect. Since you
aren't using something to keep your connections persistent, you should
call disconnect at the end of every request. You should also look at
your code to see if you put $dbh in a global somewhere.
- Perrin
Re: DBI Connectons accumulate under Mod_perl
Posted by Artem Kuchin <ma...@itlegion.ru>.
David E. Wheeler:
> On Nov 10, 2009, at 7:04 AM, Artem Kuchin wrote:
>
>
>> You mean each child process creates a new database CONNECTION (not process) ?
>> The process is just one multhreaded mysql. But this is exactly what i want. I do not want any
>> connection sharing because of the locking issues (lock tables). But they still accumulate and after a
>> couple of hours mysql runs out of connections. The weirdest thing is that there are two sites, running
>> pretty much the same software (minor changes to user part, no changes to db part). Connections
>> from one site accumulate, connection from other site do not accmulate - disconnect work fine.
>>
>
> Might you have connections starting in parent processes and not getting dropped? Are you using Apache::DBI or DBI->connect_cached or DBIx::Connector?
>
> Best,
>
> David
>
Nope, i don't use those. Just plain DBI. Parent process does not open up
any connections.
Artem
Re: DBI Connectons accumulate under Mod_perl
Posted by "David E. Wheeler" <da...@kineticode.com>.
On Nov 10, 2009, at 7:04 AM, Artem Kuchin wrote:
> You mean each child process creates a new database CONNECTION (not process) ?
> The process is just one multhreaded mysql. But this is exactly what i want. I do not want any
> connection sharing because of the locking issues (lock tables). But they still accumulate and after a
> couple of hours mysql runs out of connections. The weirdest thing is that there are two sites, running
> pretty much the same software (minor changes to user part, no changes to db part). Connections
> from one site accumulate, connection from other site do not accmulate - disconnect work fine.
Might you have connections starting in parent processes and not getting dropped? Are you using Apache::DBI or DBI->connect_cached or DBIx::Connector?
Best,
David
Re: DBI Connectons accumulate under Mod_perl
Posted by Artem Kuchin <ma...@itlegion.ru>.
Kulasekaran, Raja:
> It's like each child process create a new database process and it
> remains stable. Instead of establishing a database connection, there is
> a possibility to share the connection between the child threads using
> sqlrelay (http://sqlrelay.sourceforge.net/ ). But I have no idea about
> sqlrelay.
>
> Is this issue resolved in Mod_perl 2.0? . I have read the article
> "DB::Pool". Can anyone help me out on this.
>
>
>
You mean each child process creates a new database CONNECTION (not
process) ?
The process is just one multhreaded mysql. But this is exactly what i
want. I do not want any
connection sharing because of the locking issues (lock tables). But they
still accumulate and after a
couple of hours mysql runs out of connections. The weirdest thing is
that there are two sites, running
pretty much the same software (minor changes to user part, no changes to
db part). Connections
from one site accumulate, connection from other site do not accmulate -
disconnect work fine.
> -----Original Message-----
> From: Artem Kuchin [mailto:matrix@itlegion.ru]
> Sent: Tuesday, October 20, 2009 10:29 PM
> To: modperl@perl.apache.org
> Subject: DBI Connectons accumulate under Mod_perl
>
> Hello!
>
> I have a very weird situation. I use MYSQL. Apache 2.2 with mod_perl2
>
> O use Modperl::RegistryBB to run the script, but the script itself is
> written very nicely
> for mod_perl. That is no globals at all. Everything is in sub handler.
> I do not use Apache::DBI, just plain "use DBI;"
>
> So, what it does (basic idea)
>
> sub handler {
> db->connect();
>
> .. does all the thing...
>
> db->disconnect();
>
> print $output;
>
> }
>
> however, whe i do in mysql
>
> show processlist;
>
> I see many connection hanging in sleep state and they grow and grow
> slowly!
> Any idea why this happens?
>
> Artem
>
>
>
>
--
С уважением,
Артем Кучин
Компания "Ай Ти Легион"
www.itlegion.ru
www.hostilla.ru
+7 (495) 232-0338
RE: DBI Connectons accumulate under Mod_perl
Posted by "Kulasekaran, Raja" <Ra...@netapp.com>.
It's like each child process create a new database process and it
remains stable. Instead of establishing a database connection, there is
a possibility to share the connection between the child threads using
sqlrelay (http://sqlrelay.sourceforge.net/ ). But I have no idea about
sqlrelay.
Is this issue resolved in Mod_perl 2.0? . I have read the article
"DB::Pool". Can anyone help me out on this.
- Raja
-----Original Message-----
From: Artem Kuchin [mailto:matrix@itlegion.ru]
Sent: Tuesday, October 20, 2009 10:29 PM
To: modperl@perl.apache.org
Subject: DBI Connectons accumulate under Mod_perl
Hello!
I have a very weird situation. I use MYSQL. Apache 2.2 with mod_perl2
O use Modperl::RegistryBB to run the script, but the script itself is
written very nicely
for mod_perl. That is no globals at all. Everything is in sub handler.
I do not use Apache::DBI, just plain "use DBI;"
So, what it does (basic idea)
sub handler {
db->connect();
.. does all the thing...
db->disconnect();
print $output;
}
however, whe i do in mysql
show processlist;
I see many connection hanging in sleep state and they grow and grow
slowly!
Any idea why this happens?
Artem