On Fri 07 Feb 2003 10:49, Andre Bonhote <[EMAIL PROTECTED]> wrote:
> Hi DBI folks!
>
> I am new to this list, and fairly new to DBI internals, so bear with me
> when I am talking stupid things.
>
> Anyway, I don't think it's /that/ stupid, since I guess I found a bug
> within DBD::Oracle. First about the platform I am using:
>
> - Redhat 8.0
> - Oracle 9i (on a remote machine)
> - Perl 5.8 from Redhat's perl-5.8.0-55 RPM
> - DBI 1.32 from CPAN
> - DBD::Oracle 1.12 from CPAN
>
> On another test system, I have a local installation of postgresql and
> the according DBD::Pg.
>
> Now for the problem. Last week, I rewrote a program which uses
> DBD::Oracle to stay in memory as a daemon. Letting it run a few days, I
> found that the amount of memory it uses increases quite rapidly. The
> queries to the DB are only read queries which result in a few hundred
> rows.
>
> Well, I investigated a bit on that and wrote a small script, in order to
> reproduce the memory loss. For that, I created a tiny table and filled
> it with ("aaaa" .. "zzzz"). The test code looks like that (to be saved
> and ran as 'select.pl'):
Could you test what happens to @test using Devel::Size?
> -8<------
> #!/usr/bin/perl -w
> use DBI;
> use strict;
use Devel::Size qw(size total_size);
> my $dbh = DBI->connect("dbi:Oracle:DB01.DB","Yep","Gotcha")
> || die "Ups: Connect";
>
> foreach (1 .. 10000) {
> my $select=qq(select * from test);
> my @lines=@{$dbh->selectcol_arrayref($select) || die "$DBI::errstr"};
my $test_size = total_size (\@test);
> my $psinfo=`ps --no-headers -o rss -C select.pl`;
> chomp $psinfo;
> printf "Iteration: %.5d - Memory: %s\n",$_,$psinfo;
printf "Iteration: %.5d - Memory: %s (%d)\n", $_, $psinfo, $test_size;
that size should not change.
>
> }
>
> $dbh->disconnect;
>
> ->8------
>
> To avoid other modules, I used the backticks. You can of course comment
> those lines and see the memory consumption grow elsewhere.
>
> It starts with 8884 kbytes. In iteration 2 it's already 8956. It stays
> like that until iteration 52, there it grows again 4k. Now one has to
> wait quite a long time, until iteration 3829. There, another 4k will be
> added. From this point it goes quite fast: The memory needed at the end
> is 9152 kbytes.
>
> Trying to sort out the problem, I set up a small postgres DB with the
> same table. The same code (of course, except the connect string) uses
> exactly 5684 kbytes, from iteration 1 to iteration 10000.
>
> Another test was to put the connect/disconnect inside the loop. The
> result was even worse. Here the first 10 lines of its output:
>
> Iteration: 0001 - Memory: 8888
> Iteration: 0002 - Memory: 9000
> Iteration: 0003 - Memory: 9000
> Iteration: 0004 - Memory: 9000
> Iteration: 0005 - Memory: 9016
> Iteration: 0006 - Memory: 9052
> Iteration: 0007 - Memory: 9088
> Iteration: 0008 - Memory: 9124
> Iteration: 0009 - Memory: 9160
> Iteration: 0010 - Memory: 9192
>
> The actual daemon I wrote grows even faster. It needs the DB every four
> minutes, so I originally put the connect/disconnect inside the loop.
> This was bad: Over the weekend, it growed from 10 megs to 65 megs.
>
> I must admit that I don't have much experience with perl -d or other
> debugging methods. I expect the DBI developers to maybe have an idea on
> where to look at.
>
> Thank you in advance
>
> Andr�
>
> --
> Andr� Bonh�te
> IP Engineer
> COLT Telecom AG
> M�rtschenstrasse 27
> CH-8048 Z�rich
>
> t +41 1 5 600 600
> f +41 1 5 600 610
> e [EMAIL PROTECTED]
> www.colt.ch
--
H.Merijn Brand Amsterdam Perl Mongers (http://amsterdam.pm.org/)
using perl-5.6.1, 5.8.0 & 632 on HP-UX 10.20 & 11.00, AIX 4.2, AIX 4.3,
WinNT 4, Win2K pro & WinCE 2.11 often with Tk800.024 &/| DBD-Unify
ftp://ftp.funet.fi/pub/languages/perl/CPAN/authors/id/H/HM/HMBRAND/
