Identify a memory problem in Perl/DBI code

Question

To start out with - this is not my code - there was a problem with the code and I'm trying to find out how to debug the problem. There are plenty of changes I would make to the code if I was given the chance (excessive braces, global variables, use the join function instead of foreach, etc., etc. etc.). It is full of bad practice, but that is not what I need help with.

Here is a snippet of the Perl code (There are no subroutines, nothing special - basically open a file for the results of the query, execute the query, and dump the results to a file):

# earlier in the program, @row, $field, and $output are all declared globally, like this:
my @row;
my $field;
my $output;

# a file is opened for output, with filehandle ROWOUT
# a database statement handle (DBD::DB2) is executed

while ( @{row} = ${sth}->fetchrow_array ) {
    foreach ${field}( @{row} ) {
        ${field} =~ s/\s+$//;
        ${output} = "${output}\~${field}";
    }

    ${output} =~ s/\~//;
    print ROWOUT "${output}\n";
    undef ${output};
}

Somewhere in the while loop, the Perl script is crashing with an Out of Memory! error (not a clean crash - it just stops running with that message.)

In most runs, the volume on this query is very small. The results of the query this time when the script crashed is a lot bigger (still not huge): 150,000 rows, and each row is about 1200 bytes wide.

Things that I have thought of:

1. The fetchrow_array function of DBI is smart enough to not pull the complete dataset into memory, correct? My assumption is that the data is on the database, and fetchrow_array retrieves one row at a time, so that even if you had 10 billion rows, you should not have a memory problem - is that correct?
2. Calling undef on the $output variable will free the memory that it was using, correct? If it doesn't, that could be another place where a memory problem could exist.
3. The memory the @row variable is using will get re-used(?) each time a new row is retrieved, correct? If not, I could see how using a global array to store each row could blow out the memory.

I am hoping there is something obvious that I am just not understanding. If there is not something obvious by looking at the code, what are some techniques I can use to debug this problem?

Thanks in advance!

Answer 1

It might be that you're (perhaps inadvertently) caching too many rows. You can find out how many have been brought in by checking $sth->{RowsInCache}. If it's undef, then there is no cache, otherwise you'll be given the number of rows.

You can also get away from the gymnastics you're having to do with $output by rewriting it as follows:

while ( my @this_row = $sth->fetchrow_array ) {
    # Get rid of this line once you figure out your memory problem.
    print STDERR "Using ", ($sth->{RowsInCache} || 0), " rows in cache\n";

    print ROWOUT join('~', map { s/\s+$// } @this_row), "\n";
}

So, assuming you have too many rows in your cache, you can limit it via:

my $dbh = DBI->connect($dsn, $user, $pass, { RowCacheSize => 20 })
    or die "Cannot connect to $dsn: $DBI::errstr\n";

From the DBI documentation, you can control the cache (assuming your driver supports it) by using a value as follows:

 0 - Automatically determine a reasonable cache size for each C<SELECT>
 1 - Disable the local row cache
>1 - Cache this many rows
<0 - Cache as many rows that will fit into this much memory for each C<SELECT>.