PERL Beginners - Large DBI query filling up memory

Author

Large DBI query filling up memory

Robin Sheat

2006-10-07, 7:58 am

Jeff Pang

2006-10-07, 7:58 am

Can you add some statements to 'eat' the query results line by line?Like:

$sth->execute;
while(my $q_ref = $sth->fetchrow_hashref) {
...
}

I think it'd resolve the memory problems.

>
>and then later I'm doing:
>
> my $query="select user_id, preference from $opt{table}";
> $sth = $dbh->prepare($query);
> print "Executing query\n";
> $sth->execute();
> print "Executed\n";
>
>this query will return (literally) millions of rows. More than can fit into
>memory. When the execute() method is called, it seems to want to put all the
>data into RAM, which causes issues. I've tried playing with
>$dbh->{RowCacheSize}, but it seems to be ignored. I'm using the MySQL driver.
>
>Is this expected behaviour, and what can I do about it?
>

--
Jeff Pang
NetEase AntiSpam Team
http://corp.netease.com

Mumia W.

2006-10-07, 7:58 am

On 10/07/2006 04:11 AM, Robin Sheat wrote:
> I have the following to open a database:
>
> my $dbh = DBI->connect("DBI:$dbconn", $dbuser, $dbpass) or
> die "Couldn't connect to db $dbconn:" . DBI->errstr;
>
> and then later I'm doing:
>
> my $query="select user_id, preference from $opt{table}";
> $sth = $dbh->prepare($query);
> print "Executing query\n";
> $sth->execute();
> print "Executed\n";
>
> this query will return (literally) millions of rows. More than can fit into
> memory. When the execute() method is called, it seems to want to put all the
> data into RAM, which causes issues. I've tried playing with
> $dbh->{RowCacheSize}, but it seems to be ignored. I'm using the MySQL driver.
>
> Is this expected behaviour, and what can I do about it?
>

Use a LIMIT clause to restrict the number of records that are returned,
e.g.:

SELECT id, first_name from users LIMIT 100, 200;

....returns records 100 through 200 of the results.

You would use LIMIT to process the data "page by page."

Robin Sheat

2006-10-07, 7:58 am

Rob Dixon

2006-10-07, 7:00 pm

Robin Sheat wrote:
>
> I have the following to open a database:
>
> my $dbh = DBI->connect("DBI:$dbconn", $dbuser, $dbpass) or
> die "Couldn't connect to db $dbconn:" . DBI->errstr;
>
> and then later I'm doing:
>
> my $query="select user_id, preference from $opt{table}";
> $sth = $dbh->prepare($query);
> print "Executing query\n";
> $sth->execute();
> print "Executed\n";
>
> this query will return (literally) millions of rows. More than can fit into
> memory. When the execute() method is called, it seems to want to put all the
> data into RAM, which causes issues. I've tried playing with
> $dbh->{RowCacheSize}, but it seems to be ignored. I'm using the MySQL driver.
>
> Is this expected behaviour, and what can I do about it?

Well I wouldn't expect it: MySQL is usually good about this sort of thing.
Exactly what sort of 'issues' is it causing? And are they on the machine your
database server is running on or on the client?

First of all, I'd start your program with

use IO::Handle;
STDOUT->autoflush;

just to make sure that you're seeing the 'Executed' message as soon as the
execute method call has completed instead of when the message is finally flushed
out of its buffer.

Also take a look at the value of $sth->{RowsInCache} after the execute to see if
it's the cached data that's really your problem. And do the same thing after
you've set $dbh->{RowCacheSize} = 1 to disable row caching to see if it makes
the difference it should.

Let us know the results!

Cheers,

Rob

Robin Sheat

2006-10-07, 7:00 pm

Sponsored Links