Re: DBI Connectons accumulate under Mod_perl
Perrin Harkins:
On Fri, Nov 13, 2009 at 4:47 AM, Artem Kuchin mat...@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
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
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
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
On Fri, Nov 13, 2009 at 4:47 AM, Artem Kuchin mat...@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
On Thu, Nov 12, 2009 at 2:22 AM, Kulasekaran, Raja raja.kulaseka...@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
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
On Tue, Nov 10, 2009 at 10:04 AM, Artem Kuchin mat...@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
No. I could see that oracle instances are still alive. -Original Message- From: David E. Wheeler [mailto:da...@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
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
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:mat...@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
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
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:da...@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
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
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:da...@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
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:mat...@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
Re: DBI Connectons accumulate under Mod_perl
On Tue, Oct 20, 2009 at 9:59 AM, Artem Kuchin mat...@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
