[PHP-DB] Re: [PHP] PDO buffered query problem
Stewart Duncan wrote: 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?). 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 ) ); Don't you want it the other way around? You want it unbuffered so it doesn't get retrieved in mysql, send the whole result set to php then you can use it. You want to start using it immediately - so make it unbuffered. Either way if the result set is going to be large your MySQL's memory or PHP's memory may exceed. So if you use either you may need to fine-tune PHP by increasing the per process memory (memory_limit in php.ini) or MySQL. In case you use unbuffered queries you cannot use transactions as far as I can recall. -- Thodoris -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: [PHP] PDO buffered query problem
Thodoris wrote: Stewart Duncan wrote: 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?). 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 ) ); Don't you want it the other way around? You want it unbuffered so it doesn't get retrieved in mysql, send the whole result set to php then you can use it. You want to start using it immediately - so make it unbuffered. Either way if the result set is going to be large your MySQL's memory or PHP's memory may exceed. So if you use either you may need to fine-tune PHP by increasing the per process memory (memory_limit in php.ini) or MySQL. The point of unbuffered queries is that they only take up memory while transferring a particular row's data. It won't get the whole lot from mysql, store it all in php (taking up memory for all x rows in the result set), then start working on it. So it won't take up that much memory - you'll only ever have one row of data in memory. Of course, the downside is that you can't do another query until the whole result has been processed. Personally I'd never try to work on 60k items at once, I'd chunk my results (work on 1000 items at a time) and go from there, but that of course depends on what needs to be done. -- Postgresql php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] comparing stored timestamp with NOW()
Hi I need help formatting a sql statement that compares my stored timestamp
$stamp with NOW()
what I'm trying to do is generate a SELECT statement that pulls only records
within 7 days of NOW()
should i use
*SELECT DATEDIFF('stamp','NOW()')=7d; not sure how the difference
part is syntaxed*
Thanks
Terion
Happy Freecycling
Free the List !!
www.freecycle.org
Over Moderation of Freecycle List Prevents Post Timeliness.
Twitter?
http://twitter.com/terionmiller
Facebook:
a href=http://www.facebook.com/people/Terion-Miller/1542024891;
title=Terion Miller's Facebook profile target=_TOPimg src=
http://badge.facebook.com/badge/1542024891.237.919247960.png; border=0
alt=Terion Miller's Facebook profile/a
Vince Lombardi - Winning is habit. Unfortunately, so is losing.
Re: [PHP-DB] comparing stored timestamp with NOW()
If your timestamps is an UNIX Timestamp, and your DB is MySQL, this should do the trick $days = 7; $sql = 'SELECT (UNIX_TIMESTAMP() - ' . $timeStamp . ') = (3600*24*' . $days . ');'; 3600*24 is one day in seconds, so multiply it by the days limit (7 days). Example Query: mysql SELECT (UNIX_TIMESTAMP() - 1235861716) = (3600*24*7) AS valid FROM dual; +---+ | valid | +---+ | 1 | +---+ Enjoy. -- Atte, Andrés G. Montañez Zend Certified Engineer Montevideo - Uruguay -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] comparing stored timestamp with NOW()
$days = 7;
$now = date(Y-m-d);
$timestamp = /*your defined stamp */;
$query = SELECT * from TABLE WHERE DATEDIFF('$timestamp', '$now')=7;
I think that is the query you want? Unless I'm misreading the manual -
which is entirely possible.
Conversely you can just use DATEDIFF('$timestamp', DATE())=7 as well.
The only thing you need to make sre is that your $timestamp is in
-MM-DD format.
On Tue, Feb 24, 2009 at 4:35 PM, Terion Miller webdev.ter...@gmail.com wrote:
Hi I need help formatting a sql statement that compares my stored timestamp
$stamp with NOW()
what I'm trying to do is generate a SELECT statement that pulls only records
within 7 days of NOW()
should i use
*SELECT DATEDIFF('stamp','NOW()')=7d; not sure how the difference
part is syntaxed*
Thanks
Terion
Happy Freecycling
Free the List !!
www.freecycle.org
Over Moderation of Freecycle List Prevents Post Timeliness.
Twitter?
http://twitter.com/terionmiller
Facebook:
a href=http://www.facebook.com/people/Terion-Miller/1542024891;
title=Terion Miller's Facebook profile target=_TOPimg src=
http://badge.facebook.com/badge/1542024891.237.919247960.png; border=0
alt=Terion Miller's Facebook profile/a
Vince Lombardi - Winning is habit. Unfortunately, so is losing.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Subject: PDO buffered query problem
At 13:40 24/02/2009, you wrote:
Message-ID: c4.82.23283.768e2...@pb1.pair.com
To: php-db@lists.php.net,php-gene...@lists.php.net
Date: Mon, 23 Feb 2009 18:16:01 +
From: Stewart Duncan do...@gmx.net
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 6 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
