Centos 5 specific DBI memory leak

Krambambuli has asked for the wisdom of the Perl Monks concerning the following question:

Dear Perlmonkers,

I'm fighting a memory leak with DBI/DBD::Oracle which I do not really know how to tackle. I tracked it down to a simple code sequence like

{
my $sth;

sub _read_from_db {
    my ($array_ref) = @_;

    if (not defined $sth) {
        $sql = qq/ ... /;
        $sth = $dbh->prepare( $sql );
    }
    $sth->bind_param( ... );
    $sth->execute();
   
    @$array_ref = $sth->fetchrow_array();    
} 
}
[download]

The problem is that the script using the code steadily grows over runtime, always in 4 byte increments; the increment happens not with each iteration, but (apparently random) after 5, 6 or 7 iterations. I also found that it's the call to $sth->execute() that causes the increase in memory footprint.

The very same code runs OK on another (White Box Enterprise Linux release 3.0) system.

On the problematic system CentOS release 5 (Final) is installed.

I've tried to upgrade DBI, DBD::Oracle and Perl to the latest versions; no improvement. I downgraded DBI, DBD::Oracle and Perl with a few version numbers - same behavior.

But when I then compiled Perl without threads - SUCCESS! - problem gone, code worked OK.

No joy however: I specifically want and need to use threads once the script stops to leak memory.

It seems clear that it is a OS-specific problem; I'm just a bit lost for now not knowing really which direction to head.

I'm pretty sure that some of the monastery veterans have fought such wars before; so what would others do in this situation ?

Many thanks in advance.

Final update: it turned out to be not at all CentOS specific, not even a Perl or thread problem, but just an Oracle bug. I was able to overcome the problem without re-installing anything on server-side Oracle after (correctly - that was rather harder than expected!) rebuilding DBD::Oracle with Oracle Instant Client 10.2.0.3 (might work with other versions as well, it's just the version I happened to use). It was gsiems's suggestion that helped me find the solution. Thanks to all those who lent a helpful hand!

Update: after further googling and looking through documentation, it seems that it is more an Oracle 9iR2 problem (which is what I use):

Same problem on Mac OS
Same problem on Linux Suse 9.0

As I won't be able to change the installed Oracle at least for some time, I guess I won't have a quick answer here.

Update 2: Seems that HP-UX 11 has seen this too. But this last link also might bring the long awaited light on the matter:

There is a memory leak from the Oracle Client Interface on HP-UX 11

    * Scenario: A memory leak has been observed from the Oracle Client
+ Interface (OCI) 
              when using Oracle 9i Release 2 (9.2.0.1) on HP-UX 11.

    * Solution: This problem has been fixed by Oracle and the fix is a
+vailable by upgrading 
              to Oracle 9i Release 2 Database Server Patch Set 2 for H
+P9000 Series HP-UX (64-bit).
                This has the Oracle patch number of 2761332.
                To install the above Oracle patch, you might need to u
+pgrade your Oracle installer (OUI) 
              to version 2.2.0.18.0 first. The Oracle patch number for
+ this upgrade is 2878462.
[download]

Now, as it seems that the Oracle I deal with hasn't the released patches installed, it looks like there is nothing more to do or try before getting those patches applied.

Thanks and sorry for the noise ;)

Krambambuli

Comment on Centos 5 specific DBI memory leak Select or Download Code

Replies are listed 'Best First'.
Re: Centos 5 specific DBI memory leak by cdarke (Prior) on Aug 14, 2008 at 07:40 UTC
Call `$sth->finish();` before your exit the subroutine.	[reply] [d/l]
Re^2: Centos 5 specific DBI memory leak by Burak (Chaplain) on Aug 14, 2008 at 08:07 UTC
Using finish() after fetching the values can be good but I don't think this is necessary here since all data is fetched and it must finish automatically. Btw, I seem to experience some weird behaviour with CentOS5 too, but since all perls are non-threaded I can't confirm this DBI issue... Update: on a second thought, I think the definiton of $sth can cause problems since it becomes a static var. I don't see any reason to define it outside the sub. So, it is better to define it inside the sub...	[reply]
Re^3: Centos 5 specific DBI memory leak by Krambambuli (Curate) on Aug 14, 2008 at 08:43 UTC
Making $sth static is intentional. It allows to prepare() the statement only once, even if using it afterwards billions of times. Defining it in the sub would imply a heavy performance penalty, even if it would circumvent the problem faced now - but thank you anyway.	[reply]
Re^2: Centos 5 specific DBI memory leak by Krambambuli (Curate) on Aug 14, 2008 at 08:37 UTC
I did that already, but I forgot to mention it; no difference seen.	[reply]
Re: Centos 5 specific DBI memory leak by jbert (Priest) on Aug 14, 2008 at 16:01 UTC
Since CentOS is Linux, have you tried valgrind? It should be able to tell you where the leaked memory was allocated, i.e. if it was from within perl or an external lib.	[reply]
Re^2: Centos 5 specific DBI memory leak by Krambambuli (Curate) on Aug 15, 2008 at 07:57 UTC
I never used valgrind before, so I'm into new territories with this, but I'll probably give it a try. Thanks for your suggestion. Update: valgrind rocks! Now I just cannot understand how come I never used it till now ... ?! To all the fellow cowards like me (as I guess it was plain fear of yet-such-another-hard-to-use-debugging-tool that made me overlook it for so long): it's nothing to be afraid of! It's strong, but very gentle with newbies too - somewhat like Perl in this regard. And yes, it gave me in just a few minutes the information I was trying to find for a few days already: it's an Oracle shared library that causes my headache: libclntsh.so.9.0. Now if that isn't of help, I don't know what would be... Thanks again for pointing me this marveluous tool. I definitely became a fan of it in no time.	[reply]
Re: Centos 5 specific DBI memory leak by gsiems (Deacon) on Aug 14, 2008 at 18:45 UTC
Would compiling DBD::Oracle against the 10g instant client fix the memory leak?	[reply]
Re^2: Centos 5 specific DBI memory leak by Krambambuli (Curate) on Aug 15, 2008 at 08:02 UTC
That's hard to find out, for the time being I'm stuck with 9.2. I'm rather confident that even 9.2.0.8 - the latest patched Oracle 9.2 version - would be ok, but I'll have to wait to get that in reach for me. Currently, it's 9.2.0.1 on the machine I work on. Update: Much to my disappointment, the first attempt to follow your suggestion brought no improvement. That's not quite a final word on it so far, as there are at least two more things to try/investigate: first, the Oracle libraries are expecting a libstdc++.so.5, whereas on my system there are only some libstdc++-4.1.2-14.el5; I've tried with Oracle Instant Client 10.2.0.3 - and there is also an 10.2.0.4 available (and an 10.1.0.5, and an 11.1.0.6.0...) Krambambuli	[reply]