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 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

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=20

-----Original Message-----
From: Artem Kuchin [mailto:matrix@itlegion.ru]=20
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

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.
>
>
> =20

You mean each child process creates a new database CONNECTION (not=20
process) ?
The process is just one multhreaded mysql. But this is exactly what i=20
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=20
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 -=20
disconnect work fine.


> -----Original Message-----
> From: Artem Kuchin [mailto:matrix@itlegion.ru]=20
> 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
>
>
>
> =20

--=20
С уважением,
Артем Кучин
Компания "Ай Ти=
Легион"
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)=20

So, Do I need to call $dbh->disconnect() for each child to close the
connection ?

Raja .=20


-----Original Message-----
From: David E. Wheeler [mailto:david@kineticode.com]=20
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)=20
>=20
> 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=20
has been completed successfully .=20

-Raja=20

-----Original Message-----
From: David E. Wheeler [mailto:david@kineticode.com]=20
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)=20
>=20
> 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

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=20
> has been completed successfully .=20

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 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.=20

-----Original Message-----
From: David E. Wheeler [mailto:david@kineticode.com]=20
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=20
> has been completed successfully .=20

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.=20

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

Hi,

Below is the log of $Apache::DBI::DEBUG =3D 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=3D1PrintError=3D1RaiseError=3D1Username=3Dxxx '
32162 Apache::DBI new connect to
'xxxAutoCommit=3D1PrintError=3D1RaiseError=3D1Username=3Dxxx '
32164 Apache::DBI new connect to
'xxxAutoCommit=3D1PrintError=3D1RaiseError=3D1Username=3Dxxx '
32166 Apache::DBI new connect to
'xxxAutoCommit=3D1PrintError=3D1RaiseError=3D1Username=3Dxxx '
32165 Apache::DBI new connect to
'xxxAutoCommit=3D1PrintError=3D1RaiseError=3D1Username=3Dxxx '
32168 Apache::DBI new connect to
'xxxAutoCommit=3D1PrintError=3D1RaiseError=3D1Username=3Dxxx '
32167 Apache::DBI new connect to
'xxxAutoCommit=3D1PrintError=3D1RaiseError=3D1Username=3Dxxx '
32169 Apache::DBI new connect to
'xxxAutoCommit=3D1PrintError=3D1RaiseError=3D1Username=3Dxxx '
32162 Apache::DBI push PerlCleanupHandler
32162 Apache::DBI need ping: yes
32162 Apache::DBI already connected to
'xxxAutoCommit=3D1PrintError=3D1RaiseError=3D1Username=3Dxxx '
32162 Apache::DBI PerlCleanupHandler

-Raja=20

-----Original Message-----
From: Perrin Harkins [mailto:pharkins@gmail.com]=20
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
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

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?
>>=20
>> Best,
>>=20
>> David
>> =20
> 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 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
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

Perrin Harkins:
> On Fri, Nov 13, 2009 at 4:47 AM, Artem Kuchin 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=3D...
>=20
> ...
>=20
> $db->disconnect();
> }
>=20
> So, everything goes out of scope when handler finishes. I made it so =
to play nice with mod_per. NO globals
> at all.
>=20
> 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=