DBD::mysql
Ben Kennedy
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
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
"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
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
> 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