At 13:40 24/02/2009, you wrote:
Message-ID: <C4.82.23283.768E2A94@xxxxxxxxxxxx>
To: php-db@xxxxxxxxxxxxx,php-general@xxxxxxxxxxxxx
Date: Mon, 23 Feb 2009 18:16:01 +0000
From: Stewart Duncan <docuk@xxxxxxx>
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Subject: PDO buffered query problem
Hi there,
I'm having some serious problems with the PHP Data Object functions.
I'm trying to loop through a sizeable result set (~60k rows, ~1gig)
using a buffered query to avoid fetching the whole set.
No matter what I do, the script just hangs on the PDO::query() - it
seems the query is running unbuffered (why else would the change in
result set size 'fix' the issue?).
Just for completeness in understanding - the 60k rows does not
correspond to 1Gb data - that's for the full rows right ?
If not, that would imply your "ID" field below is ~18000 characters
long which seems (cough) extreme !
If your ID field is still of substantial size, you could consider a
surrogate key, eg an autoincrement integer to keep sizes small.
IMO, requiring 60000 rows in a result set might indicate that your
application is configured to do much of the work otherwise done by
the DB. Without any specifics it's hard to say, but be sure you
actually ~need~ to fetch all the rows instead of returning a more
limited resultset by better specifying the SQL.
As one other poster noted, you *could* be reaching PHP's memory limit
: It's not configured that high in default installs - often 2 or 8MB
- and the script will terminate silently in that case, unless you
have error_reporting(E_ALL) and/or error logging switched on in your
application to debug this.
If it's a production server, you'll need to check the error logs, as
you won't be displaying the errors to screen ( -right - ? ;-))
Finally - try to make sure you're running the mysqldnd MySQL client
driver with a recent (PHP 5.3+) install with MySQL4.1
http://dev.mysql.com/downloads/connector/php-mysqlnd/
http://uk.php.net/manual/en/mysqli.mysqlnd.php
http://uk.php.net/manual/en/mysqli.installation.php
That tends to use 1/2 the memory as the resultset is only stored in
one place rather than 2
Oh - and I imagine that is repro code, but check if you had code in
the script prior to those isolated lines which doesn't close
resultsets or release statement resources, to make sure you're not
consuming and not releasing memory during the script execution.
On the MySQL side, if you're really returning large BLOBs and not the
ID you specified below, read
http://dev.mysql.com/doc/refman/5.1/en/packet-too-large.html
One final top place to search for tips and ideas is always
http://www.mysqlperformanceblog.com (buy the book if you get the
chance, it's excellent)
HTH
Cheers - Neil
Here is my code to reproduce the problem:
<?php
$Database = new PDO(
'mysql:host=localhost;port=3306;dbname=mydatabase',
'root',
'',
array(
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true
)
);
$rQuery = $Database->query('SELECT id FROM mytable');
// This is never reached because the result set is too large
echo 'Made it through.';
foreach($rQuery as $aRow) {
print_r($aRow);
}
?>
If I limit the query with some reasonable number, it works fine:
$rQuery = $Database->query('SELECT id FROM mytable LIMIT 10');
I have tried playing with PDO::MYSQL_ATTR_MAX_BUFFER_SIZE and using
the PDO::prepare() and PDO::execute() as well (though there are no
parameters in the above query), both to no avail.
Any help would be appreciated,
Stewart
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
