Re: Possible memory leak using $sth->{NAME} ?

I'm still not able to replicate on mysql. However, I have created a
test script (included below) which, given access to an empty mssql
database, should do everything necessary to demonstrate the behavior.

Please let me know if there is any further information I could provide
or tests I could create that might be helpful.

Thanks,

David Brewer

###########################################################################
# mssql_leak.t
#
# Script for demonstrating substantial memory leak with DBI, DBD::ODBC, and
# a MSSQL 2000 database.
#
# To use this script you must have DBI 1.50, DBD::ODBC 1.13, and owner
# access to an MSSQL database. A table called 'leak_test' will be created
# and destroyed in the database. See the CONFIGURATION section below to set
# up the desired connection.
#
# In order to reproduce the leak, you must meet four criteria:
#
# 1) In your query, you must create a table variable.
# 2) You must insert at least one row into the table variable. After that,
# you can completely ignore the table variable!
# 3) You must select from a table that contains 'text' columns. The more
# text columns you select, the larger the leak. Note that you don't
# actually have to select any rows to cause the leak!
# 4) After executing the query, you must do something which triggers the
# lookup of column information. I've found that either $sth->{NAME} or
# $sth->fetchrow_hashref() will cause the leak, for instance.
#
# In addition, the 'LongReadLen' property of the database handle is somehow
# related because the higher this number, the greater the leak.
#
# Once you've verified that the leak is occuring using the code below, you
# can try commenting out various lines to see how it affects things.
# The two clearest examples are commenting out the insertion into the
# table variable, and commenting out the $sth->{NAME} line.
#
# I wasn't able to figure out how to get at the memory usage of perl
# programmatically on Windows, so rather than writing tests to test the
# memory usage, I just made a loop that demonstrates the leak. It pauses
# after the first iteration and then again at the end so you can observe the
# memory used by perl using the task manager or 'Process Explorer' from
# sysinternals.com.
#
# Author: David Brewer, Second Story Interactive
# Date: June 7, 2006
###########################################################################

use strict;
use warnings;

###########################################################################
# CONFIGURATION
###########################################################################

# Database connection
my $dsn = "DBI:ODBC:driver={SQL Server};server=localhost;database=test;";
my $user = "test";
my $pass = "test";

# number of times to perform the leaky query in the loop
my $leak_iterations = 1200;

# seconds to pause at beginning and end of loop to permit memory observation
my $pause = 7;

# LongReadLen value to use on the database handle; this is somehow related
# to the leak because the greater this value, the greater the leak.
my $LongReadLen = 20000;


###########################################################################
# SETUP
###########################################################################

use Test::More tests => 8;

# Verify we have the correct versions of the modules
BEGIN {
use_ok( 'DBI 1.50' );
use_ok( 'DBD::ODBC 1.13' );
}

# Prepare the database by creating the test table. Drop it first if it
# already exists.

my $create_sql = q{
IF OBJECT_ID('leak_test','U')IS NOT NULL DROP TABLE leak_test;
CREATE TABLE [dbo].[leak_test] (
ID int NOT NULL,
LongText1 text,
LongText2 text,
LongText3 text,
LongText4 text,
LongText5 text,
PRIMARY KEY (ID)
)
};

my $dbh = DBI->connect($dsn, $user, $pass);
isa_ok($dbh, 'DBI::db');
ok($dbh->do($create_sql), 'Created leak_test table');
ok($dbh->disconnect, 'Disconnected from database');


###########################################################################
# THE TEST
###########################################################################

my $leaky_sql = q{
DECLARE @table_variable TABLE (
TestInteger int DEFAULT(0)
);

-- Commenting the line below prevents the leak!
INSERT INTO @table_variable (TestInteger) VALUES (1);

SELECT TOP 0
*
FROM
leak_test;
};

my $iterations = 1200;
print(qq{
We will run the leaky query $leak_iterations times, pausing $pause seconds
after the first run so you can observe the memory usage.
});

$dbh = DBI->connect($dsn, $user, $pass);
$dbh->{LongReadLen} = $LongReadLen;

for my $i (1..$leak_iterations) {
# print a dot for each run through the query
print "\n" if ($i % 60 == 1);
print ".";

# run our leaky query...
my $sth = $dbh->prepare($leaky_sql);
$sth->execute();

# Getting column names triggers link; you can comment out to verify.
my $names = $sth->{NAME};

# clean up
$sth->finish;
undef $sth;

# pause after the first run so you can observe the memory usage
sleep($pause) if ($i == 1);
}

$dbh->disconnect();
undef $dbh;

print(qq{
\nPausing $pause seconds so you can observe final memory usage.
});
sleep($pause);


###########################################################################
# CLEANUP
###########################################################################

# Remove the leak_test table
my $cleanup_sql = q{
IF OBJECT_ID('leak_test','U') IS NOT NULL DROP TABLE leak_test;
};

$dbh = DBI->connect($dsn, $user, $pass);
isa_ok($dbh, 'DBI::db');
ok($dbh->do($cleanup_sql), 'Removed leak_test table');
ok($dbh->disconnect, 'Disconnected from database');
###########################################################################
.

Relevant Pages

  • Re: Possible memory leak using $sth->{NAME} ?
    ... database, should do everything necessary to demonstrate the behavior. ... # In order to reproduce the leak, ... # memory usage, I just made a loop that demonstrates the leak. ... # seconds to pause at beginning and end of loop to permit memory observation ...
    (perl.dbi.users)
  • Aspnet Worker Thread Memory Usage
    ... hosted at discount asp. ... queries a database a lot and uses ... ajax.asp.net to spice up interactivity. ... Now I already tried to strip down memory usage, ...
    (microsoft.public.dotnet.framework.aspnet)
  • RE: Funcitonality to pause macro?!
    ... then bringing that data back down to update the database. ... He has asked me to create the functionality to pause the macro? ... the progressbar ...
    (microsoft.public.excel.programming)
  • Problem with Form Timer
    ... I am designing a database for our Financial Dept. ... is the pause. ... Private Sub Form_Timer ...
    (microsoft.public.access.formscoding)
  • Problem with Form Timer
    ... I am designing a database for our Financial Dept. ... is the pause. ... Private Sub Form_Timer ...
    (microsoft.public.access.forms)