DBD::mysql

0 views
Skip to first unread message

Ben Kennedy

unread,
Oct 25, 2001, 12:22:52 PM10/25/01
to
Hello all - I am working with some large datasets stored in MySQL - I need
to iterate over all rows in a table, but currently a "SELECT * FROM table"
attempts to cache the entire result set on the client side (my process
baloons to fill all available memory). I tried playing with the
RowCacheSize parameter, but I don't think it does anything. I have noticed
that when you run the mysql command line client with the --quick switch, it
does no cleint side caching, the data just pours right out - this is exactly
what I need. In fact, my workaround launches an external mysql process to
just just that. Does anyone know how to enable the same behavior through
DBI? Thank you --

--Ben Kennedy


Greg Muth

unread,
Oct 26, 2001, 1:50:55 PM10/26/01
to

How are you implementing the query? What DBI method(s) are you using?


"Ben Kennedy" <bkenn...@Home.com> wrote in message
news:w%WB7.155955$5A3.54...@news1.rdc2.pa.home.com...

Ben Kennedy

unread,
Oct 27, 2001, 10:08:06 PM10/27/01
to

"Greg Muth" <gam...@yahoo.com> wrote in message
news:ttj8k3f...@corp.supernews.com...

>
> How are you implementing the query? What DBI method(s) are you using?
>

Its a simple "SELECT * FROM table" - with other drivers (those with
cursors), the process doesn't baloon.

--Ben Kennedy


Jonathan Stowe

unread,
Oct 28, 2001, 5:26:10 AM10/28/01
to

Unfortunately this is the way that MySQL works - it trades off memory for
speed in a rather spectacular manner. You might want to look at the
documentation for the MySQL C API to see if there are ways of controlling
this behaviour - but I fear not.

/J\
--
Jonathan Stowe |
<http://www.gellyfish.com> | This space for rent
|

Garry Williams

unread,
Oct 28, 2001, 9:14:04 AM10/28/01
to
On Thu, 25 Oct 2001 16:22:52 GMT, Ben Kennedy <bkenn...@Home.com> wrote:
> Hello all - I am working with some large datasets stored in MySQL - I need
> to iterate over all rows in a table, but currently a "SELECT * FROM table"
> attempts to cache the entire result set on the client side (my process
> baloons to fill all available memory).

Use the docs, Luke! This is controllable. From the DBD::mysql manual
page:

STATEMENT HANDLES
The statement handles of DBD::mysql and DBD::mSQL support a
number of attributes. You access these by using, for
example,

my $numFields = $sth->{'NUM_OF_FIELDS'};

Note, that most attributes are valid only after a
successfull execute. An "undef" value will returned in that
--> case. The most important exception is the "mysql_use_result"
--> attribute: This forces the driver to use mysql_use_result
--> rather than mysql_store_result. The former is faster and
less memory consuming, but tends to block other processes.
(That's why mysql_store_result is the default.)

To set the "mysql_use_result" attribute, use either of the
following:

my $sth = $dbh->prepare("QUERY", { "mysql_use_result" => 1});

or

my $sth = $dbh->prepare("QUERY");
$sth->{"mysql_use_result"} = 1;

(Note the warning about interfering with other client processes.)

What they are talking about here, is using the mysql_use_result()
function instead of the mysql_store_result() function. The former
allows you to iterate over the selected set on the server; that latter
(the default) retrieves the entire selected set on the client before
allowing you to iterating over it.

See the mysql manual: 8.4.3.209 and 8.4.3.217 in the current manual.
The mysql manual repeats the warning that DBD::mysql has:

8.4.3.217 mysql_use_result()

MYSQL_RES *mysql_use_result(MYSQL *mysql)
...

This reads the result of a query directly from the server without
storing it in a temporary table or local buffer, which is somewhat
faster and uses much less memory than mysql_store_result(). The client
will only allocate memory for the current row and a communication
buffer that may grow up to max_allowed_packet bytes.

On the other hand, you shouldn't use mysql_use_result() if you are
doing a lot of processing for each row on the client side, or if the
output is sent to a screen on which the user may type a ^S (stop
scroll). This will tie up the server and prevent other threads from
updating any tables from which the data is being fetched.

--
Garry Williams

Reply all
Reply to author
Forward
0 new messages