Closing a DBI instance
Tim Johnson
FYI: I am an experienced programmer but _not_ a perl programmer.
I am trouble-shooting a problem on this server where mysql connections
do not seem to be closed by cron jobs
Consider the following perl code snippet in a stand-alone script with
executable privileges that is called by cron:
my $data_Source = "DBI:mysql:".$DATABASE .":".$HOSTNAME;
my $dbh = DBI->connect($data_Source,$USERNAME,$PASSWORD)
or die "$DBI::errstr\n";
To properly disconnect, should I not see something like the following:
$dbh->disconnect
somewhere in this script?
TIA
Ben Morrow
Quoth t...@johnsons-web.com:
> Platform: Red Hat Linux server, perl 5.8.8, mysql ver 5.0.45
>
> FYI: I am an experienced programmer but _not_ a perl programmer.
What language(s) do you know? It is useful when explaining things to
know which analogies will be useful and which confusing.
> I am trouble-shooting a problem on this server where mysql connections
> do not seem to be closed by cron jobs
>
> Consider the following perl code snippet in a stand-alone script with
> executable privileges that is called by cron:
>
> my $data_Source = "DBI:mysql:".$DATABASE .":".$HOSTNAME;
> my $dbh = DBI->connect($data_Source,$USERNAME,$PASSWORD)
> or die "$DBI::errstr\n";
>
> To properly disconnect, should I not see something like the following:
> $dbh->disconnect
> somewhere in this script?
Not necessarily. $dbh is a DBI::dbh object, and has a DESTROY method
(like a C++ destructor) that will call ->disconnect when the object goes
out of scope. At that point any transactions in prgress will be rolled
back, so you *should* see a ->commit in the appropriate place (assuming
you are using transactions).
Ben
Kenneth 'Bessarion' Boyd
> Platform: Red Hat Linux server, perl 5.8.8, mysql ver 5.0.45
>
> FYI: I am an experienced programmer but _not_ a perl programmer.
>
> I am trouble-shooting a problem on this server where mysql connections
> do not seem to be closed by cron jobs
>
>
> To properly disconnect, should I not see something like the following:
> $dbh->disconnect
> somewhere in this script?
Ideally it should just disconnect-and-commit when going out of scope.
I was running out of DB connection handles this way, though.
I ended up using an END block in the central file require'd to call
the functions that wrapped opening the DB in a moderately secure way.
Assuming $dbh is a global, something like this:
END {
$dbh->disconnect() if $dbh;
$dbh = undef;
}
I have no reason to believe this is foolproof, but this did
practically eliminate running out of DB connection handles for the
Perl application I work on.
Tim Johnson
>
> Quoth t...@johnsons-web.com:
>> Platform: Red Hat Linux server, perl 5.8.8, mysql ver 5.0.45
>>
>> FYI: I am an experienced programmer but _not_ a perl programmer.
>
> What language(s) do you know? It is useful when explaining things to
> know which analogies will be useful and which confusing.
Currently - Python, rebol, (10 years) newlisp .
Previously, 11 yrs. in C/C++.
<..>
>> Consider the following perl code snippet in a stand-alone script with
>> executable privileges that is called by cron:
>>
>> my $data_Source = "DBI:mysql:".$DATABASE .":".$HOSTNAME;
>> my $dbh = DBI->connect($data_Source,$USERNAME,$PASSWORD)
>> or die "$DBI::errstr\n";
>>
>> To properly disconnect, should I not see something like the following:
>> $dbh->disconnect
>> somewhere in this script?
>
> Not necessarily. $dbh is a DBI::dbh object, and has a DESTROY method
> (like a C++ destructor) that will call ->disconnect when the object goes
> out of scope. At that point any transactions in prgress will be rolled
> back, so you *should* see a ->commit in the appropriate place (assuming
> you are using transactions).
that might prevent the destructor from being triggered?
Ben Morrow
Quoth t...@johnsons-web.com:
> >> my $dbh = DBI->connect($data_Source,$USERNAME,$PASSWORD)
> >> or die "$DBI::errstr\n";
> >>
> >> To properly disconnect, should I not see something like the following:
> >> $dbh->disconnect
> >> somewhere in this script?
> >
> > Not necessarily. $dbh is a DBI::dbh object, and has a DESTROY method
> > (like a C++ destructor) that will call ->disconnect when the object goes
> > out of scope. At that point any transactions in prgress will be rolled
> > back, so you *should* see a ->commit in the appropriate place (assuming
> > you are using transactions).
>
> Thanks for that Ben. Can you think of any syntax or system factors
> that might prevent the destructor from being triggered?
Do you have reason to believe it isn't being? One thing that can cause
confusion is if the perl interpreter reaches global desstuction (after
you program finishes, just before the interpreter exits). The order in
which any remaining objects are destroyed is not guaranteed at that
point, and (IIRC) DBI handles have a problem with this.
Ben
Tim Johnson
>> that might prevent the destructor from being triggered?
>
> Do you have reason to believe it isn't being?
> One thing that can cause
per hour others even more frequently. The mysql server has to be restarted
frequently because MAX_CONNECTIONS is surpassed. One thing that I have
suspected is that connections are not being properly closed. Thus
the code inventory.
> confusion is if the perl interpreter reaches global desstuction (after
> you program finishes, just before the interpreter exits). The order in
> which any remaining objects are destroyed is not guaranteed at that
> point, and (IIRC) DBI handles have a problem with this.
Would you be able to give an example?
thanks again
Ben Morrow
Quoth t...@johnsons-web.com:
> On 2009-12-17, Ben Morrow <b...@morrow.me.uk> wrote:
> > Quoth t...@johnsons-web.com:
> >>
> >> Thanks for that Ben. Can you think of any syntax or system factors
> >> that might prevent the destructor from being triggered?
> >
> > Do you have reason to believe it isn't being?
> > One thing that can cause
> A large number of perl scripts are being run as by cron jobs some once
> per hour others even more frequently. The mysql server has to be restarted
> frequently because MAX_CONNECTIONS is surpassed. One thing that I have
> suspected is that connections are not being properly closed. Thus
> the code inventory.
Sounds reasonable. I don't know how easy it would be, but one thing you
could try is logging the connections as they are made with some
appropriate ID (local port number would do nicely if you are using TCP)
and then go through some time later to see which are not being
disconnected properly.
> > confusion is if the perl interpreter reaches global desstuction (after
> > you program finishes, just before the interpreter exits). The order in
> > which any remaining objects are destroyed is not guaranteed at that
> > point, and (IIRC) DBI handles have a problem with this.
>
> Would you be able to give an example?
> thanks again
Well, in this case
#!/usr/bin/perl
use DBI;
our $dbh = DBI->connect("...");
__END__
$dbh is global, so it won't be DESTROYed until global destruction,
whereas in this case
#!/usr/bin/perl
use DBI;
{
my $dbh = DBI->connect("...");
}
__END__
$dbh is a lexical and is DESTROYed at the end of the scope.
I'm not convinced yet this isn't a red herring. I can't make anything
fail like this; all I can get is 'closing dbh with active statement
handles' warnings, which is expected.
Ben
Tim Johnson
>> thanks again
>
> Well, in this case
>
> #!/usr/bin/perl
>
> use DBI;
>
> our $dbh = DBI->connect("...");
>
> __END__
>
> $dbh is global, so it won't be DESTROYed until global destruction,
> whereas in this case
>
> #!/usr/bin/perl
>
> use DBI;
>
> {
> my $dbh = DBI->connect("...");
> }
>
> __END__
>
> $dbh is a lexical and is DESTROYed at the end of the scope.
> I'm not convinced yet this isn't a red herring.
> I can't make anything
> fail like this; all I can get is 'closing dbh with active statement
> handles' warnings, which is expected.
Thanks a lot for your time.
regards
Jochen Lehmeier
wrote:
> Neither am I. At least I have enough to rule out.
I assume that the perl processes started by your cron actually end (i.e.,
are not listed by "ps" anymore), and you still run out of connections in
MySQL.
I see two possible sources for your problem:
a) You are creating many connections in a perl script, not just one, and
thus running out because they are not Garbage Collected quickly enough
(for whatever reason).
b) Your processes end, but still MySQL thinks the connections are open. I
do not know if the architecture of MySQL allows this to happen, since I
use Oracle (and there this is impossible, as the DB cleans up connections
when the underlying TCP/IP connection goes down, which is guaranteed by
the OS when the process stops).
Here's a test for you to rule out b): write a little test script which
only opens one DBI connection and then sleeps for a long time. Start it,
and then kill it with SIGKILL (kill -9). This will kill it without it
being able to close the connection in a controlled manner, i.e., the
DESTROY will certainly not run. I'd try this first, because it's very
simple and straightforward.
If you do that a lot of times, and do not run out of MySQL connections,
then you know that b) cannot be the case. Then it's time to rule out a),
i.e., take a very good look at your scripts. Maybe one script opens
mulitple connections and does not free them (because it stores them in a
hash or whatever).
Tim Johnson
> On Thu, 17 Dec 2009 22:13:16 +0100, Tim Johnson <t...@johnsons-web.com>
> wrote:
>
>> Neither am I. At least I have enough to rule out.
>
> I assume that the perl processes started by your cron actually end (i.e.,
> are not listed by "ps" anymore), and you still run out of connections in
> MySQL.
> I see two possible sources for your problem:
>
> a) You are creating many connections in a perl script, not just one, and
> thus running out because they are not Garbage Collected quickly enough
> (for whatever reason).
> b) Your processes end, but still MySQL thinks the connections are open. I
> do not know if the architecture of MySQL allows this to happen, since I
> use Oracle (and there this is impossible, as the DB cleans up connections
> when the underlying TCP/IP connection goes down, which is guaranteed by
> the OS when the process stops).
>
> Here's a test for you to rule out b): write a little test script which
> only opens one DBI connection and then sleeps for a long time. Start it,
> and then kill it with SIGKILL (kill -9). This will kill it without it
> being able to close the connection in a controlled manner, i.e., the
> DESTROY will certainly not run. I'd try this first, because it's very
> simple and straightforward.
>
> If you do that a lot of times, and do not run out of MySQL connections,
> then you know that b) cannot be the case. Then it's time to rule out a),
> i.e., take a very good look at your scripts. Maybe one script opens
> mulitple connections and does not free them (because it stores them in a
> hash or whatever).
review when he gets back..
thanks
Peter J. Holzer
> On 2009-12-17, Ben Morrow <b...@morrow.me.uk> wrote:
>> Quoth t...@johnsons-web.com:
>>>
>>> Thanks for that Ben. Can you think of any syntax or system factors
>>> that might prevent the destructor from being triggered?
>>
>> Do you have reason to believe it isn't being?
>> One thing that can cause
> A large number of perl scripts are being run as by cron jobs some once
> per hour others even more frequently. The mysql server has to be restarted
> frequently because MAX_CONNECTIONS is surpassed. One thing that I have
> suspected is that connections are not being properly closed. Thus
> the code inventory.
The connection is closed when the client exits - there is nothing the
client can do to prevent that. So I think you are barking up the wrong
tree. You should check which clients are running when you get near
MAX_CONNECTIONS. Are any of them hung? Can you distribute them better
over time? If the problem happens at unpredictable time, dumping the
output of "mysqladmin processlist" and "lsof" into a file every few
minutes can help you reconstruct what happened just before the problem
occured. (And then you can write a perl script to analyze the data, to
get back on topic)
hp
Peter J. Holzer
> b) Your processes end, but still MySQL thinks the connections are
> open. I do not know if the architecture of MySQL allows this to
> happen, since I use Oracle (and there this is impossible, as the DB
> cleans up connections when the underlying TCP/IP connection goes
> down, which is guaranteed by the OS when the process stops).
That's not quite true. Oracle only notices that the connection has gone
away when it either tries to read from or write to it. If you have
accidentally written a query which takes many hours before returning any
data killing the client doesn't help: Oracle will cheerfully complete
the query and only notice that the connection is gone when it tries to
return the result. I suspect it's the same for MySQL.
But I don't think this is the problem here - nothing in the OP suggests
that clients are killed instead of terminating normally.
hp