Unexpectedly hitting PHP memory limit with a single PDO query?
memory, mysql, pdo, php
Solution
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.
Problem
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?