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'):
-8<------
#!/usr/bin/perl -w
use DBI;
use strict;
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 $psinfo=`ps --no-headers -o rss -C select.pl`;
chomp $psinfo;
printf "Iteration: %.5d - Memory: %s\n",$_,$psinfo;
}
$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
