Re: Memory leak, DBI 1.55 + DBD::ODBC 1.13 + MS SQL 2000

John,

No. I am running this script just from the shell.

Fi.

On 5/22/07, John Scoles wrote:
> Are you using IIS7?
>
>
> ----- Original Message -----
> From: "Fi Dot"
> To:
> Cc: ; ; ;
>
> Sent: Monday, May 21, 2007 4:42 PM
> Subject: Memory leak, DBI 1.55 + DBD::ODBC 1.13 + MS SQL 2000
>
>
> > Hi All!
> >
> > Recently I have bumped into a memory leak happening in DBI.
> >
> > Running DBI version 1.55, DBD::ODBC version 1.13 on Windows XP SP2,
> > Active State Active Perl 5.8 build 819. More comprehensive build info
> > at the bottom of this email.
> >
> > You could find the code I am executing attached to this message.
> >
> > I was executing queries returning multiple record-sets with fields of
> > text type in them and.. well, the memory usage of my script just grows
> > drastically. Digging into this, I have found that it looks like
> > DBD::ODBC is not freeing up memory buffers allocated for storing the
> > current record when switching to the next record-set.
> >
> > Here are my findings:
> >
> > - Look on foo.sql defining my stored procedure. It is returning a
> > record set of ints, and then another record set of text fields. This
> > code will grow my script's memory footprint a lot every iteration
> > through the for {} loop defined on line 22 of foo.pl.
> > - If I will swap the select's in stored procedure, making text fields
> > go first, memory footprint will still grow, but much slower.
> > - This never happens when I have one single record set returned.
> > - Undeffing the connection and calling DBI's disconnect() method does not
> > help
> > - Notice I set LongReadLen value to 16 megabytes (line 18 of foo.pl)?
> > This value directly affects the amount of memory used by my script.
> > The bigger it is, the larger my mem footprint will be:
> >
> > 16 Megabytes: 150 megabytes of RAM used
> > 8 Megabytes: 80 megabytes of RAM used
> > 4 Megabytes: 40 megabytes of RAM used.
> >
> > - Text fields were taken just as a good example for watching memory
> > usage grow rapidly, in general this is happening with any query
> > having more than one record-set, just with slower rates.
> >
> > This looks like a bug in DBD::ODBC... Had anybody on the list seen
> > something similar? Are there any known fixes for this?
> >
> > Thanx!
> >
> > Fi.
> >
> > My Perl build config (apl is just an alias in BASH):
> >
> > $ apl -v
> >
> > This is perl, v5.8.8 built for MSWin32-x86-multi-thread
> > (with 33 registered patches, see perl -V for more detail)
> >
> > Copyright 1987-2006, Larry Wall
> >
> > Binary build 819 [267479] provided by ActiveState
> > http://www.ActiveState.com
> > Built Aug 29 2006 12:42:41
> >
> > Perl may be copied only under the terms of either the Artistic License or
> > the
> > GNU General Public License, which may be found in the Perl 5 source kit.
> >
> > Complete documentation for Perl, including FAQ lists, should be found on
> > this system using "man perl" or "perldoc perl". If you have access to the
> > Internet, point your browser at http://www.perl.org/, the Perl Home Page.
> >
> > $ apl -V
> > Summary of my perl5 (revision 5 version 8 subversion 8) configuration:
> > Platform:
> > osname=MSWin32, osvers=5.0, archname=MSWin32-x86-multi-thread
> > uname=''
> > config_args='undef'
> > hint=recommended, useposix=true, d_sigaction=undef
> > usethreads=define use5005threads=undef useithreads=define
> > usemultiplicity=de
> > fine
> > useperlio=define d_sfio=undef uselargefiles=define usesocks=undef
> > use64bitint=undef use64bitall=undef uselongdouble=undef
> > usemymalloc=n, bincompat5005=undef
> > Compiler:
> > cc='cl', ccflags
> > ='-nologo -GF -W3 -MD -Zi -DNDEBUG -O1 -DWIN32 -D_CONSOLE -
> > DNO_STRICT -DHAVE_DES_FCRYPT -DNO_HASH_SEED -DUSE_SITECUSTOMIZE -DPERL_IMPLICIT_
> > CONTEXT -DPERL_IMPLICIT_SYS -DUSE_PERLIO -DPERL_MSVCRT_READFIX',
> > optimize='-MD -Zi -DNDEBUG -O1',
> > cppflags='-DWIN32'
> > ccversion='12.00.8804', gccversion='', gccosandvers=''
> > intsize=4, longsize=4, ptrsize=4, doublesize=8, byteorder=1234
> > d_longlong=undef, longlongsize=8, d_longdbl=define, longdblsize=10
> > ivtype='long', ivsize=4, nvtype='double', nvsize=8, Off_t='__int64',
> > lseeksi
> > ze=8
> > alignbytes=8, prototype=define
> > Linker and Libraries:
> > ld='link', ldflags
> > '-nologo -nodefaultlib -debug -opt:ref,icf -libpath:"C:
> > \Perl\lib\CORE" -machine:x86'
> > libpth=\lib
> > libs= oldnames.lib kernel32.lib user32.lib gdi32.lib winspool.lib
> > comdlg32
> > .lib advapi32.lib shell32.lib ole32.lib oleaut32.lib netapi32.lib
> > uuid.lib ws2_
> > 32.lib mpr.lib winmm.lib version.lib odbc32.lib odbccp32.lib msvcrt.lib
> > perllibs= oldnames.lib kernel32.lib user32.lib gdi32.lib winspool.lib
> > comd
> > lg32.lib advapi32.lib shell32.lib ole32.lib oleaut32.lib netapi32.lib
> > uuid.lib
> > ws2_32.lib mpr.lib winmm.lib version.lib odbc32.lib odbccp32.lib
> > msvcrt.lib
> > libc=msvcrt.lib, so=dll, useshrplib=yes, libperl=perl58.lib
> > gnulibc_version=''
> > Dynamic Linking:
> > dlsrc=dl_win32.xs, dlext=dll, d_dlsymun=undef, ccdlflags=' '
> > cccdlflags=' ',
> > lddlflags='-dll -nologo -nodefaultlib -debug -opt:ref,icf -
> > libpath:"C:\Perl\lib\CORE" -machine:x86'
> >
> >
> > Characteristics of this binary (from libperl):
> > Compile-time options: MULTIPLICITY PERL_IMPLICIT_CONTEXT
> > PERL_IMPLICIT_SYS PERL_MALLOC_WRAP
> > PL_OP_SLAB_ALLOC USE_ITHREADS USE_LARGE_FILES
> > USE_PERLIO USE_SITECUSTOMIZE
> > Locally applied patches:
> > ActivePerl Build 819 [267479]
> > Iin_load_module moved for compatibility with build 806
> > PerlEx support in CGI::Carp
> > Less verbose ExtUtils::Install and Pod::Find
> > Patch for CAN-2005-0448 from Debian with modifications
> > Rearrange @INC so that 'site' is searched before 'perl'
> > Partly reverted 24733 to preserve binary compatibility
> > 28671 Define PERL_NO_DEV_RANDOM on Windows
> > 28376 Add error checks after execing PL_cshname or PL_sh_path
> > 28305 Pod::Html should not convert "foo" into ``foo''
> > 27736 Make perl_fini() run with Sun WorkShop compiler
> > 27619 Bug in Term::ReadKey being triggered by a bug in
> > Term::ReadLine
> > 27549 Move DynaLoader.o into libperl.so
> > 27528 win32_pclose() error exit doesn't unlock mutex
> > 27527 win32_async_check() can loop indefinitely
> > 27515 ignore directories when searching @INC
> > 27359 Fix -d:Foo=bar syntax
> > 27210 Fix quote typo in c2ph
> > 27203 Allow compiling swigged C++ code
> > 27200 Make stat() on Windows handle trailing slashes correctly
> > 27194 Get perl_fini() running on HP-UX again
> > 27133 Initialise lastparen in the regexp structure
> > 27034 Avoid "Prototype mismatch" warnings with autouse
> > 26970 Make Passive mode the default for Net::FTP
> > 26921 Avoid getprotobyname/number calls in IO::Socket::INET
> > 26897,26903 Make common IPPROTO_* constants always available
> > 26670 Make '-s' on the shebang line parse -foo=bar switches
> > 26536 INSTALLSCRIPT versus INSTALLDIRS
> > 26379 Fix alarm() for Windows 2003
> > 26087 Storable 0.1 compatibility
> > 25861 IO::File performace issue
> > 25084 long groups entry could cause memory exhaustion
> > 24699 ICMP_UNREACHABLE handling in Net::Ping
> > Built under MSWin32
> > Compiled at Aug 29 2006 12:42:41
> > %ENV:
> >
> > @INC:
> > c:/Perl/site/lib
> > c:/Perl/lib
> > .
> >
>
>

Re: Memory leak, DBI 1.55 + DBD::ODBC 1.13 + MS SQL 2000

Ok that elliminates one source of the problem.
cheers
----- Original Message -----
From: "Fi Dot"
To: "John Scoles"
Cc: ; ; ;
;
Sent: Tuesday, May 22, 2007 7:42 AM
Subject: Re: Memory leak, DBI 1.55 + DBD::ODBC 1.13 + MS SQL 2000


> John,
>
> No. I am running this script just from the shell.
>
> Fi.
>
> On 5/22/07, John Scoles wrote:
>> Are you using IIS7?
>>
>>
>> ----- Original Message -----
>> From: "Fi Dot"
>> To:
>> Cc: ; ; ;
>>
>> Sent: Monday, May 21, 2007 4:42 PM
>> Subject: Memory leak, DBI 1.55 + DBD::ODBC 1.13 + MS SQL 2000
>>
>>
>> > Hi All!
>> >
>> > Recently I have bumped into a memory leak happening in DBI.
>> >
>> > Running DBI version 1.55, DBD::ODBC version 1.13 on Windows XP SP2,
>> > Active State Active Perl 5.8 build 819. More comprehensive build info
>> > at the bottom of this email.
>> >
>> > You could find the code I am executing attached to this message.
>> >
>> > I was executing queries returning multiple record-sets with fields of
>> > text type in them and.. well, the memory usage of my script just grows
>> > drastically. Digging into this, I have found that it looks like
>> > DBD::ODBC is not freeing up memory buffers allocated for storing the
>> > current record when switching to the next record-set.
>> >
>> > Here are my findings:
>> >
>> > - Look on foo.sql defining my stored procedure. It is returning a
>> > record set of ints, and then another record set of text fields. This
>> > code will grow my script's memory footprint a lot every iteration
>> > through the for {} loop defined on line 22 of foo.pl.
>> > - If I will swap the select's in stored procedure, making text fields
>> > go first, memory footprint will still grow, but much slower.
>> > - This never happens when I have one single record set returned.
>> > - Undeffing the connection and calling DBI's disconnect() method does
>> > not
>> > help
>> > - Notice I set LongReadLen value to 16 megabytes (line 18 of foo.pl)?
>> > This value directly affects the amount of memory used by my script.
>> > The bigger it is, the larger my mem footprint will be:
>> >
>> > 16 Megabytes: 150 megabytes of RAM used
>> > 8 Megabytes: 80 megabytes of RAM used
>> > 4 Megabytes: 40 megabytes of RAM used.
>> >
>> > - Text fields were taken just as a good example for watching memory
>> > usage grow rapidly, in general this is happening with any query
>> > having more than one record-set, just with slower rates.
>> >
>> > This looks like a bug in DBD::ODBC... Had anybody on the list seen
>> > something similar? Are there any known fixes for this?
>> >
>> > Thanx!
>> >
>> > Fi.
>> >
>> > My Perl build config (apl is just an alias in BASH):
>> >
>> > $ apl -v
>> >
>> > This is perl, v5.8.8 built for MSWin32-x86-multi-thread
>> > (with 33 registered patches, see perl -V for more detail)
>> >
>> > Copyright 1987-2006, Larry Wall
>> >
>> > Binary build 819 [267479] provided by ActiveState
>> > http://www.ActiveState.com
>> > Built Aug 29 2006 12:42:41
>> >
>> > Perl may be copied only under the terms of either the Artistic License
>> > or
>> > the
>> > GNU General Public License, which may be found in the Perl 5 source
>> > kit.
>> >
>> > Complete documentation for Perl, including FAQ lists, should be found
>> > on
>> > this system using "man perl" or "perldoc perl". If you have access to
>> > the
>> > Internet, point your browser at http://www.perl.org/, the Perl Home
>> > Page.
>> >
>> > $ apl -V
>> > Summary of my perl5 (revision 5 version 8 subversion 8) configuration:
>> > Platform:
>> > osname=MSWin32, osvers=5.0, archname=MSWin32-x86-multi-thread
>> > uname=''
>> > config_args='undef'
>> > hint=recommended, useposix=true, d_sigaction=undef
>> > usethreads=define use5005threads=undef useithreads=define
>> > usemultiplicity=de
>> > fine
>> > useperlio=define d_sfio=undef uselargefiles=define usesocks=undef
>> > use64bitint=undef use64bitall=undef uselongdouble=undef
>> > usemymalloc=n, bincompat5005=undef
>> > Compiler:
>> > cc='cl', ccflags
>> > ='-nologo -GF -W3 -MD -Zi -DNDEBUG -O1 -DWIN32 -D_CONSOLE -
>> > DNO_STRICT -DHAVE_DES_FCRYPT -DNO_HASH_SEED -DUSE_SITECUSTOMIZE -DPERL_IMPLICIT_
>> > CONTEXT -DPERL_IMPLICIT_SYS -DUSE_PERLIO -DPERL_MSVCRT_READFIX',
>> > optimize='-MD -Zi -DNDEBUG -O1',
>> > cppflags='-DWIN32'
>> > ccversion='12.00.8804', gccversion='', gccosandvers=''
>> > intsize=4, longsize=4, ptrsize=4, doublesize=8, byteorder=1234
>> > d_longlong=undef, longlongsize=8, d_longdbl=define, longdblsize=10
>> > ivtype='long', ivsize=4, nvtype='double', nvsize=8, Off_t='__int64',
>> > lseeksi
>> > ze=8
>> > alignbytes=8, prototype=define
>> > Linker and Libraries:
>> > ld='link', ldflags
>> > '-nologo -nodefaultlib -debug -opt:ref,icf -libpath:"C:
>> > \Perl\lib\CORE" -machine:x86'
>> > libpth=\lib
>> > libs= oldnames.lib kernel32.lib user32.lib gdi32.lib winspool.lib
>> > comdlg32
>> > .lib advapi32.lib shell32.lib ole32.lib oleaut32.lib netapi32.lib
>> > uuid.lib ws2_
>> > 32.lib mpr.lib winmm.lib version.lib odbc32.lib odbccp32.lib
>> > msvcrt.lib
>> > perllibs= oldnames.lib kernel32.lib user32.lib gdi32.lib
>> > winspool.lib
>> > comd
>> > lg32.lib advapi32.lib shell32.lib ole32.lib oleaut32.lib netapi32.lib
>> > uuid.lib
>> > ws2_32.lib mpr.lib winmm.lib version.lib odbc32.lib odbccp32.lib
>> > msvcrt.lib
>> > libc=msvcrt.lib, so=dll, useshrplib=yes, libperl=perl58.lib
>> > gnulibc_version=''
>> > Dynamic Linking:
>> > dlsrc=dl_win32.xs, dlext=dll, d_dlsymun=undef, ccdlflags=' '
>> > cccdlflags=' ',
>> > lddlflags='-dll -nologo -nodefaultlib -debug -opt:ref,icf -
>> > libpath:"C:\Perl\lib\CORE" -machine:x86'
>> >
>> >
>> > Characteristics of this binary (from libperl):
>> > Compile-time options: MULTIPLICITY PERL_IMPLICIT_CONTEXT
>> > PERL_IMPLICIT_SYS PERL_MALLOC_WRAP
>> > PL_OP_SLAB_ALLOC USE_ITHREADS USE_LARGE_FILES
>> > USE_PERLIO USE_SITECUSTOMIZE
>> > Locally applied patches:
>> > ActivePerl Build 819 [267479]
>> > Iin_load_module moved for compatibility with build 806
>> > PerlEx support in CGI::Carp
>> > Less verbose ExtUtils::Install and Pod::Find
>> > Patch for CAN-2005-0448 from Debian with modifications
>> > Rearrange @INC so that 'site' is searched before 'perl'
>> > Partly reverted 24733 to preserve binary compatibility
>> > 28671 Define PERL_NO_DEV_RANDOM on Windows
>> > 28376 Add error checks after execing PL_cshname or PL_sh_path
>> > 28305 Pod::Html should not convert "foo" into ``foo''
>> > 27736 Make perl_fini() run with Sun WorkShop compiler
>> > 27619 Bug in Term::ReadKey being triggered by a bug in
>> > Term::ReadLine
>> > 27549 Move DynaLoader.o into libperl.so
>> > 27528 win32_pclose() error exit doesn't unlock mutex
>> > 27527 win32_async_check() can loop indefinitely
>> > 27515 ignore directories when searching @INC
>> > 27359 Fix -d:Foo=bar syntax
>> > 27210 Fix quote typo in c2ph
>> > 27203 Allow compiling swigged C++ code
>> > 27200 Make stat() on Windows handle trailing slashes correctly
>> > 27194 Get perl_fini() running on HP-UX again
>> > 27133 Initialise lastparen in the regexp structure
>> > 27034 Avoid "Prototype mismatch" warnings with autouse
>> > 26970 Make Passive mode the default for Net::FTP
>> > 26921 Avoid getprotobyname/number calls in IO::Socket::INET
>> > 26897,26903 Make common IPPROTO_* constants always available
>> > 26670 Make '-s' on the shebang line parse -foo=bar switches
>> > 26536 INSTALLSCRIPT versus INSTALLDIRS
>> > 26379 Fix alarm() for Windows 2003
>> > 26087 Storable 0.1 compatibility
>> > 25861 IO::File performace issue
>> > 25084 long groups entry could cause memory exhaustion
>> > 24699 ICMP_UNREACHABLE handling in Net::Ping
>> > Built under MSWin32
>> > Compiled at Aug 29 2006 12:42:41
>> > %ENV:
>> >
>> > @INC:
>> > c:/Perl/site/lib
>> > c:/Perl/lib
>> > .
>> >
>>
>>
>