Possible memory leak with large row select statments in sth->execute?

I am wondering if anyone has noticed a memory leak in sth->execute with
2.9003? Given the simple test code below, as soon as sth->execute is
called, memory usage on the machine skyrockets to well over 500M before
the fetch loop even begins.

To isolate things, I took the loop out as well as the binding to make
sure that this isn't the same memory leak already documented on
CPAN...no change, still chews memory. The table has about 1.5M records
with a pretty long row length, but it appears that dbd::mysql is reading
all of the records into memory and then iterating...is this a correct
implementation of the driver? Or is this a memory leak?

My normal dev environment is v5.8.2 build 808 on linux, however, I have
tested this on a windows platform with dbd-odbc and it uses less than 2M
of memory and just cranks through them at record speed with no
additional memory usage, so my hunch is that this is not a dbi problem,
but rather dbd-mysql.

I have also tried other fetch methods such as fetchrow_array, arrayref,
and hashref with the same results. If I comment out the loop and just
leave the execute, it still uses a massive amount of memory.

Has anyone else seen this in select statements with large recordsets?

use strict;
use DBI;
use DBD::mysql;

my ($dbh, $objRS, %row, $count);

$dbh = DBI->connect("DBI:mysql:test:dev", "user", "pw");

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

$objRS->execute();

$objRS->bind_columns( \( @row{ @{$objRS->{NAME} } } ));

while ($objRS->fetchrow_array())
{
$count++;
if (($count % 1000) == 0)
{
print "processed $count records\n";
}
}


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: Possible memory leak with large row select statments in sth->execute?

Scott Reames wrote:

> I am wondering if anyone has noticed a memory leak in sth->execute with
> 2.9003? Given the simple test code below, as soon as sth->execute is
> called, memory usage on the machine skyrockets to well over 500M before
> the fetch loop even begins.

You have to be aware, that the C driver (that is the software layer
on which the Perl driver sits) is most probably loading the *whole*
result set into memory. If the result set is 500M big, it will take
500M. That's not a memory leak. It is your task to reduce the size
of the result set.

In MySQL 5.0 you would use server side cursors for that. In MySQL 4.1
or lower you need client side cursoring (multiple queries with LIMIT
clause) or set the flag "mysql_use_result". See "perldoc DBD::mysql".
Be aware of the side effects.


Jochen


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: Possible memory leak with large row select statments in sth->execute?

Scott Reames wrote:

> I am wondering if anyone has noticed a memory leak in sth->execute with
> 2.9003? Given the simple test code below, as soon as sth->execute is
> called, memory usage on the machine skyrockets to well over 500M before
> the fetch loop even begins.

You have to be aware, that the C driver (that is the software layer
on which the Perl driver sits) is most probably loading the *whole*
result set into memory. If the result set is 500M big, it will take
500M. That's not a memory leak. It is your task to reduce the size
of the result set.

In MySQL 5.0 you would use server side cursors for that. In MySQL 4.1
or lower you need client side cursoring (multiple queries with LIMIT
clause) or set the flag "mysql_use_result". See "perldoc DBD::mysql".
Be aware of the side effects.


Jochen


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org