Unexpectedly hitting PHP memory limit with a single PDO query?

Question

I have a really simple query that looks something like:

$result = $pdo->query('SELECT * FROM my_table');

foreach($result as $r) {
    // do some stuff
}

But when I run this I get the following error:

Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 32 bytes) in /path/to/myfile.php on line 15

"Line 15" being the $pdo->query line.

If I put die() after the query, I still get the same error.

I thought this was only supposed to fetch one row at at time; why is it using so much memory?

Answer 1

Memory usage is 635384 bytes before calling query. I'm guessing query allocates in chunks, for each record.

Ding ding ding!

When connecting to MySQL, PHP likes using buffered queries. This is true regardless of what method you're using to connect. When using buffered queries, the entire resultset is fetched immediately instead of being fetched when you ask. This is usually good for performance, as there are fewer round-trips.

But like everything in PHP, there's a gotcha. As noted on the buffering page:

When using libmysql as library PHP's memory limit won't count the memory used for result sets unless the data is fetched into PHP variables. With mysqlnd the memory accounted for will include the full result set.

You're using PHP 5.3, which means that there's a good chance that you're using mysqlnd.

You'll want to turn off buffered queries here. It's done differently in every PHP interface to MySQL:

• For PDO, you'll need to set the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute to false.
• For mysqli, you need to pass the MYSQLI_USE_RESULT constant to the query method.
• For mysql, you need to call mysql_unbuffered_query instead of mysql_query.

Full details and examples are on the page.

Big fat unbuffered query gotcha!

You must properly close the statement handle and free the result set before issuing another query:

• In PDO, this means calling closeCursor on the statement handle.
• In mysqli, this means calling free_result on the statement handle or free on the result handle, depending on what you're working with.
• In mysql, this means calling mysql_free_result

Failure to do this will result in an error.