perl DBI memory leak

Question

I'm having some memory leak issues in a perl script that I'm running for extended periods of time, where the amount of memory that perl is taking up just continues to grow. Because of this, I am trying to use Devel::Leak to track down the leak. I discovered that whenever I call DBI's prepare method, the number of scalar values returned by Devel::Leak goes up by one. Below is I test script I've put together that does what I'm describing:

#!/usr/bin/perl
use strict;
use Devel::Leak;
use DBI;

START:
my $handle; # apparently this doesn't need to be anything at all
my $leaveCount = 0;
my $enterCount = Devel::Leak::NoteSV($handle);
print "ENTER: $enterCount SVs\n";
{
    # CONFIG VARIABLES
    my $platform = "mysql";
    my $database = "db";
    my $host = "localhost";
    my $port = "3306";
    my $user = "user";
    my $pw = "pass";

    #DATA SOURCE NAME
    my $dsn = "dbi:mysql:$database:$host:3306";

    # PERL DBI CONNECT
    my $dbh = DBI->connect($dsn, $user, $pw);
    $dbh->prepare("SELECT * FROM table"); # The script seems to gain one SV without this
                                          # line here, but since this is my issue in my
                                          # main script I decided to leave it in
    # undef $dbh; I tried undef-ing this, but it made no difference
}
$leaveCount = Devel::Leak::CheckSV($handle);
print "\nLEAVE: $leaveCount SVs\n";
sleep(1);
goto START;

So is there something I'm doing wrong here, or is this a memory leak in the DBI module? Also, I know that adding one SV every time around the loop isn't a huge deal, and that I most likely have larger memory leaks elsewhere that are causing perl to take so much of the server's memory. However, I'd still like to fix this if I could. Coder's curiosity :)

UPDATE:

The first time through it seems to add about 3,000 SV's, and then every time after that it goes up 1 at a time.

Cobbie

I ran your script and couldn't replicate your problem.LEAVE: 12677 SVs ENTER: 12677 SVs (perl v5.14.2 and DBI 1.622) – Belita

What versions of DBI & DBD::mysql? – Pops

hmm..that's interesting. I've run it on two separate servers and have gotten the same issue. I wonder if it could be a DBI version problem? – Cobbie

DBI is version 1.622 and DBD::mysql is 4.022. My perl is v5.10.1 – Cobbie

Have you tried $dbh->disconnect? – Belita

Yes, that doesn't seem to do anything. I found this link: bugs.mysql.com/bug.php?id=41139 which seems to suggest that if I use DBI's prepare_cached instead of prepare it will fix it. I tried it out and it initially jumps from 77452 SV's to 77482 SV's the first time I call prepare, but then stays at 77482. So although I guess this 'works', I'd prefer to have it work like yours does where there are no inconsistencies. – Cobbie

I just ran it under perl v5.10.1, DBD::mysql 4.016 and 4.022, DBI 1.612 and 1.622, and never encountered a memory leak. – Belita

Hmm. Is there another variable that could be different on our machines? I just found another person who seems to be experiencing a similar problem: groups.google.com/forum/?fromgroups=#!topic/perl.dbi.dev/… – Cobbie

I got same leaks with perl 5.14.2, DBI-1.618, DBD-mysql-4.020 – Hosea

So I wonder if it's just a matter of finding the right combination of versions? It does seem to be a DBI issue. – Cobbie

I don't see how it could be the issue, but is there a difference between perl compiled with multiplicity enabled (e.g. threaded perl) or not? DBD::mysql compiled to use the thread safe mysql client library or not? – Melissamelisse

BTW, you might like to check the bugs reported with Devel::Leak as a) it is not defined what is reported after a second CheckSV on the same handle and b) it can segfault (which it did for me at #0 lookup (ht=0x846b5c0, sv=0x84eebf0, tag=0xb7a4d074) at Leak.xs:50 ). You are better calling NoteSV after each CheckSV. The RT is at rt.cpan.org/Public/Bug/Display.html?id=19067 – Homozygote

I have the same leak with perl 5.10.1, DBI 1.612, DBD::mysql 4.016, all installed from debian squeeze repos – Alban

@Belita what OS are you running? I'm running CentOS 6.3 Also, what MySQL version are you running? – Cobbie

What's up with the goto? – Extraordinary

DBD::mysql leaks 1 scalar per loop. DBD::SQLIte leaks 3. – Extraordinary

@Extraordinary the 'goto' is just there because I threw this together quickly :) So it is DBD::mysql and not DBI? – Cobbie

@Cobbie Can't say if its DBI or DBD::mysql or DBD::SQLite or all of them. I was hoping another driver wouldn't have the memory leak, proving its DBD::mysql, but it didn't work out that way. – Extraordinary

just a wild guess: you declare the variable $handle at each iteration and use it as a parameter in the call of D::L::n/cSV (where its used as a call-by-ref param according to the docs), which means the optimizer cannot just assume that the old value is no longer in use. so i would expect a new SV per iteration until you leave the scope of the $handle declaration. easy to check: drop the block in curly brackets and check whether the increment persists. – Glycolysis

Can you move your connect and prepare statements outside of your loop? That would prevent prepare from being called continuously. Alternatively, what if you properly assign it to a statement handle ($sth) and use $sth->finish? – Partin

Normally, you'd use my $sth = $dbh->prepare($sql); capturing the return from the prepare. Does that make any difference? ...No...It reproduces for me with Perl 5.16.0 and DBI 1.622, and with DBD::Informix instead of DBD::MySQL. Consequently, it is likely to be an issue in DBI rather than the DBD modules per se. – Pleinair

Answer 1

There is a instance of DBI::dr (a blessed hash) living at $DBI::lasth. Check out the ChildHandles key.

#!/usr/bin/perl

use strict;
use warnings;
use Devel::Leak;
use Data::Dumper;
use Symbol::Table;
use DBI;

START:
{
    my $handle;
    my $enterCount = Devel::Leak::NoteSV($handle);

    DB:
    {
        my $platform = "mysql";
        my $database = "db";
        my $host     = "localhost";
        my $port     = "3306";
        my $user     = "user";
        my $pw       = "pass";

        my $dsn = "dbi:mysql:$database:$host:3306";

        my $dbh = DBI->connect( $dsn, $user, $pw );

        $dbh->prepare("SELECT * FROM table");

        $dbh->disconnect();
    }

    my $st = Symbol::Table->New( 'SCALAR', 'DBI' );

    for my $subpkg ( keys %{ $st } ) {

        my $val;
        {
            my $var = "DBI::${subpkg}";
            no strict 'refs';
            $val = ${$var};
        }

        print "scalar '$subpkg' => '$val'\n";
    }

    print Dumper( $DBI::lasth );

    $DBI::lasth->{ChildHandles} = []; # <-- reset leaking data structure

    my $leaveCount = Devel::Leak::CheckSV($handle);

    print "\nCOUNT: $enterCount to $leaveCount SVs\n";

    sleep(1);

    redo START;
}

Querist

This fixes the memory leak: $DBI::lasth->{ChildHandles} = []; – Querist

Wow, great detective work! I'll have to remember those modules for the next time I run into something like this. Thanks so much! – Cobbie

Note that the ChildHandles array holds weak references and that 'from time to time' the old slots get freed up. This isn't a leak, it just appears to be if you're not familiar with the caching that DBI does internally. You can rest assured that if the DBI did have a real leak a) a great many people would be affected and b) it would get fixed very quickly. – Transmitter