Re: Subject: PDO buffered query problem

At 13:40 24/02/2009, you wrote:
>Message-ID: <[email protected]>
>To: [email protected],[email protected]
>Date: Mon, 23 Feb 2009 18:16:01 +0000
>From: Stewart Duncan <[email protected]>
>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