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.
Re: Potential dbi memory leak.
Hi Tim, Oh yes for (1..500) does exactly that. Thank you. No memory leak at all! Regards Steve. On 26/05/15 10:13, Tim Bunce 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. 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 = <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.
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 = <#dbi:ODBC:Driver=Firebird;Dbname=/home/image/Documents/Endoscopia/DB/newEndo.fdb; #ib_dialect=3; #DSN my $loc_dsn = <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 =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;