Re: Potential dbi memory leak.
Hi Tim,
Sorry to prolong this.
On 28/05/15 08:58, Tim Bunce wrote:
When a new handle is created the DBI simply pushes a weak reference to
the handle onto the end of the ChildHandles array. Because it's a weak
reference it doesn't interfere with the handle getting destroyed when
the last (non weak) reference is removed. When the handle is
destroyed, the corresponding element in the ChildHandles array is set
to undef by the weak reference mechanism deep in the perl internals.
The issue I have is that I do have real memory leaks in other parts of
my system and this caching makes it hard to detect where the real leaks are.
I have tried to suppress the caching using this code:
$loc_sth-finish();
#
print Dumper( $DBI::lasth );
$DBI::lasth -{ChildHandles} = []; # -- reset leaking data structure
delete $DBI::lasth -{ChildHandles};
But I have not managed to stop it. In order to clarify my real leaks,
how can I suppress the dbi caching for the duration of my tests?
Thanks,
Steve.
Re: Potential dbi memory leak.
On Thu, May 28, 2015 at 03:59:51PM +1200, Duncan McEwan wrote: On Tue, 26 May 2015 14:13:05 +0100 Tim Bunce tim.bu...@pobox.com wrote: I've added this as a note: Note that the ChildHandles array holds weak references and that 'from time to time' the old slots get freed up. This isn't a leak, it just appears to be if you're not familiar with the caching that DBI does internally. You can rest assured that if the DBI did have a real leak a) a great many people would be affected and b) it would get fixed very quickly. I think 'from time to time' is every 120 or so newly created child handles. But seeing this response from Tim about the fact that the DBI can cache up to 120 or so handles made me wonder if this is true for database handles as well as statement handles? Is it possible that our problem was simply the correctly working DBI caching misbehaving due to our application running persistently in multiple fcgid processes. Short answer: no. When a new handle is created the DBI simply pushes a weak reference to the handle onto the end of the ChildHandles array. Because it's a weak reference it doesn't interfere with the handle getting destroyed when the last (non weak) reference is removed. When the handle is destroyed, the corresponding element in the ChildHandles array is set to undef by the weak reference mechanism deep in the perl internals. The DBI isn't involved in that. The apparent 'leak' is because the element in the array still exists, albeit as an undef. The DBI clears out the undef elements occasionally to prevent the array growing indefinitely. Tim.
Re: Potential dbi memory leak.
I haven't tried it but you could probably modify the code I posted to test it. With the statement handle after 120(?) loops of apparent memory leak +1, there is a correction of -119. Good luck. Steve. On 28 May 2015 05:00, Duncan McEwan dun...@ecs.vuw.ac.nz wrote: Apologies for butting in on this thread, but I saw the following response from Tim recently and it made me wonder ... On Tue, 26 May 2015 14:13:05 +0100 Tim Bunce tim.bu...@pobox.com wrote: I've added this as a note: Note that the ChildHandles array holds weak references and that 'from time to time' the old slots get freed up. This isn't a leak, it just appears to be if you're not familiar with the caching that DBI does internally. You can rest assured that if the DBI did have a real leak a) a great many people would be affected and b) it would get fixed very quickly. I think 'from time to time' is every 120 or so newly created child handles. A while ago we had a mysterious problem using DBI in an application that was written as a plugin for the foswiki platform. Since our foswiki instance was running persistently under fcgid it was long-running and over time we'd see a gradual increase in the open connections it held to our mysql database server. Eventually our server would reach it's maximum connection count and reject new connections. The most recent time I tried to debug this was over a year ago (March 2014) and there was a brief exchange of emails on this list with the subject DBI Mysql Driver Handle Mysteriously Changes. Since then we've given up (!) and changed the way our application ran so it is no longer a foswiki plugin. That seems to have fixed the connection leakage and so we are unlikely to ever go back to find out exactly what was going on here. But seeing this response from Tim about the fact that the DBI can cache up to 120 or so handles made me wonder if this is true for database handles as well as statement handles? Is it possible that our problem was simply the correctly working DBI caching misbehaving due to our application running persistently in multiple fcgid processes. I'm not looking to re-open investigating this issue - our environement has now changed sufficiently that recreating the set up with the connection leak to do more debugging would be quite difficult. But I was just curious about whether the above could be the case. If the answer is no I'll be happy to just leave it at that! Thanks. Duncan
Re: Potential dbi memory leak.
Apologies for butting in on this thread, but I saw the following response from Tim recently and it made me wonder ... On Tue, 26 May 2015 14:13:05 +0100 Tim Bunce tim.bu...@pobox.com wrote: I've added this as a note: Note that the ChildHandles array holds weak references and that 'from time to time' the old slots get freed up. This isn't a leak, it just appears to be if you're not familiar with the caching that DBI does internally. You can rest assured that if the DBI did have a real leak a) a great many people would be affected and b) it would get fixed very quickly. I think 'from time to time' is every 120 or so newly created child handles. A while ago we had a mysterious problem using DBI in an application that was written as a plugin for the foswiki platform. Since our foswiki instance was running persistently under fcgid it was long-running and over time we'd see a gradual increase in the open connections it held to our mysql database server. Eventually our server would reach it's maximum connection count and reject new connections. The most recent time I tried to debug this was over a year ago (March 2014) and there was a brief exchange of emails on this list with the subject DBI Mysql Driver Handle Mysteriously Changes. Since then we've given up (!) and changed the way our application ran so it is no longer a foswiki plugin. That seems to have fixed the connection leakage and so we are unlikely to ever go back to find out exactly what was going on here. But seeing this response from Tim about the fact that the DBI can cache up to 120 or so handles made me wonder if this is true for database handles as well as statement handles? Is it possible that our problem was simply the correctly working DBI caching misbehaving due to our application running persistently in multiple fcgid processes. I'm not looking to re-open investigating this issue - our environement has now changed sufficiently that recreating the set up with the connection leak to do more debugging would be quite difficult. But I was just curious about whether the above could be the case. If the answer is no I'll be happy to just leave it at that! Thanks. Duncan
Re: Potential dbi memory leak.
It seems to be further documented here, together with a solution:
http://stackoverflow.com/questions/13338308/perl-dbi-memory-leak,
But the solution does not seem to be reliable. Sometimes it works
sometimes not.
I'll update you when I know more.
Regards,
Steve.
On 26/05/15 07:07, Steve Cookson - gmail wrote:
Hi Guys,
You may have seen part of this post on PerlMonks. If so apologies for
the duplication. This started off as a general search for leaks in my
code, and resulted in a few hits, one of which was attached to every
database access.
A simple select ATT_RECORD_NAME_TXT from TBL_TEST; results in the
leak of one scalar value. It seems to be attached to the -prepare
statement.
At first I assumed it was down to my Firebird driver, which is
relatively new, so I switched the driver to ODBC::Firebird, with the
same result. Finally I changed to mysql and again got a memory leak.
The only thing I can assume is that either my code is generically
wrong (and I hope this is the case), or there is a leak in dbi, which
I would be surprised by.
I would appreciate some advice.
Test code follows. Please install Devel::Leak to pick up leaked
scalars and update the dsn to the dsn of your choice.
Thanks for your help.
Regards,
Steve.
#! /usr/bin/perl
package main;
use strict;
use warnings;
use DBI;
#use DBD::Firebird;
use DBD::ODBC;
use Devel::Leak;
my $handle;
my $count_start;
my $count_stop;
my $gl_dbh;
# Just do this 5 times to make sure there is no contribution to
$handle count from Devel::Leak
for (1..10){
print Handle init: , Devel::Leak::NoteSV($Launch::handle),\n;
}
#my $loc_dsn = DSN;
#dbi:ODBC:Driver=Firebird;Dbname=/home/image/Documents/Endoscopia/DB/newEndo.fdb;
#ib_dialect=3;
#DSN
my $loc_dsn = DSN;
DBI:mysql:database=new_schema_test;
host=localhost;
port=3306;
DSN
$Launch::gl_dbh=DBI-connect($loc_dsn,root,password, {
PrintError = 1,# Report errors via warn
RaiseError = 1# Report errors via Die
}
) or die;
my @loc_sql_string =();
$loc_sql_string[0]=CREATE TABLE TBL_TEST_LEAK (
ATTR_RECORD_ID_TXT VARCHAR(10) NOT NULL, ATT_RECORD_NAME_TXT
VARCHAR(255), CONSTRAINT PK_TBL_TEST_LEAK PRIMARY KEY
(ATTR_RECORD_ID_TXT) ); ;
$loc_sql_string[1]=GRANT DELETE, INSERT, REFERENCES, SELECT,
UPDATE ON TBL_TEST_LEAK TO SYSDBA WITH GRANT OPTION;
$loc_sql_string[2]=INSERT INTO TBL_TEST_LEAK (ATTR_RECORD_ID_TXT,
ATT_RECORD_NAME_TXT) VALUES ('206', 'Delay Test 1' );
$loc_sql_string[3]=select ATT_RECORD_NAME_TXT from TBL_TEST_LEAK; ;
$loc_sql_string[4]= $loc_sql_string[3];
$loc_sql_string[5]= $loc_sql_string[3];
$loc_sql_string[6]= $loc_sql_string[3];
$loc_sql_string[7]= $loc_sql_string[3];
$loc_sql_string[8]= $loc_sql_string[3];
$loc_sql_string[9]=drop table TBL_TEST_LEAK; ;
for (my $i=1;$i=9;$i++){
$count_start=Devel::Leak::NoteSV($Launch::handle);
print DBD start: , $count_start,\n;
print $loc_sql_string[$i], \n;
dbd_select($loc_sql_string[$i]);
# You can use
#$count_stop=Devel::Leak::CheckSV($Launch::handle);
$count_stop=Devel::Leak::NoteSV($Launch::handle);
print Handle stop: , $count_stop,\n;
print Count difference: , $count_stop-$count_start,\n;
}
$Launch::gl_dbh-disconnect;
sub dbd_select{
my $loc_sql_string=shift;
my $loc_sth=$Launch::gl_dbh-prepare($loc_sql_string) or die;
#$loc_sth-execute() or die;
#$loc_sth-finish();
return;
}
1;
Potential dbi memory leak.
Hi Guys,
You may have seen part of this post on PerlMonks. If so apologies for
the duplication. This started off as a general search for leaks in my
code, and resulted in a few hits, one of which was attached to every
database access.
A simple select ATT_RECORD_NAME_TXT from TBL_TEST; results in the
leak of one scalar value. It seems to be attached to the -prepare
statement.
At first I assumed it was down to my Firebird driver, which is
relatively new, so I switched the driver to ODBC::Firebird, with the
same result. Finally I changed to mysql and again got a memory leak.
The only thing I can assume is that either my code is generically wrong
(and I hope this is the case), or there is a leak in dbi, which I would
be surprised by.
I would appreciate some advice.
Test code follows. Please install Devel::Leak to pick up leaked scalars
and update the dsn to the dsn of your choice.
Thanks for your help.
Regards,
Steve.
#! /usr/bin/perl
package main;
use strict;
use warnings;
use DBI;
#use DBD::Firebird;
use DBD::ODBC;
use Devel::Leak;
my $handle;
my $count_start;
my $count_stop;
my $gl_dbh;
# Just do this 5 times to make sure there is no contribution to
$handle count from Devel::Leak
for (1..10){
print Handle init: , Devel::Leak::NoteSV($Launch::handle),\n;
}
#my $loc_dsn = DSN;
#dbi:ODBC:Driver=Firebird;Dbname=/home/image/Documents/Endoscopia/DB/newEndo.fdb;
#ib_dialect=3;
#DSN
my $loc_dsn = DSN;
DBI:mysql:database=new_schema_test;
host=localhost;
port=3306;
DSN
$Launch::gl_dbh=DBI-connect($loc_dsn,root,password, {
PrintError = 1,# Report errors via warn
RaiseError = 1# Report errors via Die
}
) or die;
my @loc_sql_string =();
$loc_sql_string[0]=CREATE TABLE TBL_TEST_LEAK ( ATTR_RECORD_ID_TXT
VARCHAR(10) NOT NULL, ATT_RECORD_NAME_TXT VARCHAR(255), CONSTRAINT
PK_TBL_TEST_LEAK PRIMARY KEY (ATTR_RECORD_ID_TXT) ); ;
$loc_sql_string[1]=GRANT DELETE, INSERT, REFERENCES, SELECT,
UPDATE ON TBL_TEST_LEAK TO SYSDBA WITH GRANT OPTION;
$loc_sql_string[2]=INSERT INTO TBL_TEST_LEAK (ATTR_RECORD_ID_TXT,
ATT_RECORD_NAME_TXT) VALUES ('206', 'Delay Test 1' );
$loc_sql_string[3]=select ATT_RECORD_NAME_TXT from TBL_TEST_LEAK; ;
$loc_sql_string[4]= $loc_sql_string[3];
$loc_sql_string[5]= $loc_sql_string[3];
$loc_sql_string[6]= $loc_sql_string[3];
$loc_sql_string[7]= $loc_sql_string[3];
$loc_sql_string[8]= $loc_sql_string[3];
$loc_sql_string[9]=drop table TBL_TEST_LEAK; ;
for (my $i=1;$i=9;$i++){
$count_start=Devel::Leak::NoteSV($Launch::handle);
print DBD start: , $count_start,\n;
print $loc_sql_string[$i], \n;
dbd_select($loc_sql_string[$i]);
# You can use
#$count_stop=Devel::Leak::CheckSV($Launch::handle);
$count_stop=Devel::Leak::NoteSV($Launch::handle);
print Handle stop: , $count_stop,\n;
print Count difference: , $count_stop-$count_start,\n;
}
$Launch::gl_dbh-disconnect;
sub dbd_select{
my $loc_sql_string=shift;
my $loc_sth=$Launch::gl_dbh-prepare($loc_sql_string) or die;
#$loc_sth-execute() or die;
#$loc_sth-finish();
return;
}
1;
Re: Potential dbi memory leak.
I've added this as a note:
Note that the ChildHandles array holds weak references and that 'from
time to time' the old slots get freed up. This isn't a leak, it just
appears to be if you're not familiar with the caching that DBI does
internally. You can rest assured that if the DBI did have a real leak
a) a great many people would be affected and b) it would get fixed very
quickly.
I think 'from time to time' is every 120 or so newly created child handles.
Tim.
On Tue, May 26, 2015 at 07:57:53AM -0300, Steve Cookson - gmail wrote:
It seems to be further documented here, together with a solution:
http://stackoverflow.com/questions/13338308/perl-dbi-memory-leak,
But the solution does not seem to be reliable. Sometimes it works sometimes
not.
I'll update you when I know more.
Regards,
Steve.
On 26/05/15 07:07, Steve Cookson - gmail wrote:
Hi Guys,
You may have seen part of this post on PerlMonks. If so apologies for the
duplication. This started off as a general search for leaks in my code,
and resulted in a few hits, one of which was attached to every database
access.
A simple select ATT_RECORD_NAME_TXT from TBL_TEST; results in the leak
of one scalar value. It seems to be attached to the -prepare statement.
At first I assumed it was down to my Firebird driver, which is relatively
new, so I switched the driver to ODBC::Firebird, with the same result.
Finally I changed to mysql and again got a memory leak. The only thing I
can assume is that either my code is generically wrong (and I hope this is
the case), or there is a leak in dbi, which I would be surprised by.
I would appreciate some advice.
Test code follows. Please install Devel::Leak to pick up leaked scalars
and update the dsn to the dsn of your choice.
Thanks for your help.
Regards,
Steve.
#! /usr/bin/perl
package main;
use strict;
use warnings;
use DBI;
#use DBD::Firebird;
use DBD::ODBC;
use Devel::Leak;
my $handle;
my $count_start;
my $count_stop;
my $gl_dbh;
# Just do this 5 times to make sure there is no contribution to
$handle count from Devel::Leak
for (1..10){
print Handle init: , Devel::Leak::NoteSV($Launch::handle),\n;
}
#my $loc_dsn = DSN;
#dbi:ODBC:Driver=Firebird;Dbname=/home/image/Documents/Endoscopia/DB/newEndo.fdb;
#ib_dialect=3;
#DSN
my $loc_dsn = DSN;
DBI:mysql:database=new_schema_test;
host=localhost;
port=3306;
DSN
$Launch::gl_dbh=DBI-connect($loc_dsn,root,password, {
PrintError = 1,# Report errors via warn
RaiseError = 1# Report errors via Die
}
) or die;
my @loc_sql_string =();
$loc_sql_string[0]=CREATE TABLE TBL_TEST_LEAK ( ATTR_RECORD_ID_TXT
VARCHAR(10) NOT NULL, ATT_RECORD_NAME_TXT VARCHAR(255), CONSTRAINT
PK_TBL_TEST_LEAK PRIMARY KEY (ATTR_RECORD_ID_TXT) ); ;
$loc_sql_string[1]=GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
ON TBL_TEST_LEAK TO SYSDBA WITH GRANT OPTION;
$loc_sql_string[2]=INSERT INTO TBL_TEST_LEAK (ATTR_RECORD_ID_TXT,
ATT_RECORD_NAME_TXT) VALUES ('206', 'Delay Test 1' );
$loc_sql_string[3]=select ATT_RECORD_NAME_TXT from TBL_TEST_LEAK; ;
$loc_sql_string[4]= $loc_sql_string[3];
$loc_sql_string[5]= $loc_sql_string[3];
$loc_sql_string[6]= $loc_sql_string[3];
$loc_sql_string[7]= $loc_sql_string[3];
$loc_sql_string[8]= $loc_sql_string[3];
$loc_sql_string[9]=drop table TBL_TEST_LEAK; ;
for (my $i=1;$i=9;$i++){
$count_start=Devel::Leak::NoteSV($Launch::handle);
print DBD start: , $count_start,\n;
print $loc_sql_string[$i], \n;
dbd_select($loc_sql_string[$i]);
# You can use
#$count_stop=Devel::Leak::CheckSV($Launch::handle);
$count_stop=Devel::Leak::NoteSV($Launch::handle);
print Handle stop: , $count_stop,\n;
print Count difference: , $count_stop-$count_start,\n;
}
$Launch::gl_dbh-disconnect;
sub dbd_select{
my $loc_sql_string=shift;
my $loc_sth=$Launch::gl_dbh-prepare($loc_sql_string) or die;
#$loc_sth-execute() or die;
#$loc_sth-finish();
return;
}
1;
Re: Potential dbi memory leak.
Hi Again On 26/05/15 10:13, Tim Bunce wrote: Note that the ChildHandles array holds weak references and that 'from time to time' the old slots get freed up. This isn't a leak, it just appears to be if you're not familiar with the caching that DBI does internally. You can rest assured that if the DBI did have a real leak a) a great many people would be affected and b) it would get fixed very quickly. I think 'from time to time' is every 120 or so newly created child handles. Could I suggest that /array holds weak references/ is changed to /array holds a history of weak references /Or something to explain the idea of a growing hash. I misinterpreted your reply at first. Thanks again, Regards Steve.
