PDO/MySQL memory consumption with large result set

Question

I'm having a strange time dealing with selecting from a table with about 30,000 rows.

It seems my script is using an outrageous amount of memory for what is a simple, forward only walk over a query result.

Please note that this example is a somewhat contrived, absolute bare minimum example which bears very little resemblance to the real code and it cannot be replaced with a simple database aggregation. It is intended to illustrate the point that each row does not need to be retained on each iteration.

<?php
$pdo = new PDO('mysql:host=127.0.0.1', 'foo', 'bar', array(
    PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION,
));
$stmt = $pdo->prepare('SELECT * FROM round');
$stmt->execute();

function do_stuff($row) {}

$c = 0;
while ($row = $stmt->fetch()) {
    // do something with the object that doesn't involve keeping 
    // it around and can't be done in SQL
    do_stuff($row);
    $row = null;
    ++$c;
}

var_dump($c);
var_dump(memory_get_usage());
var_dump(memory_get_peak_usage());

This outputs:

int(39508)
int(43005064)
int(43018120)

I don't understand why 40 meg of memory is used when hardly any data needs to be held at any one time. I have already worked out I can reduce the memory by a factor of about 6 by replacing "SELECT *" with "SELECT home, away", however I consider even this usage to be insanely high and the table is only going to get bigger.

Is there a setting I'm missing, or is there some limitation in PDO that I should be aware of? I'm happy to get rid of PDO in favour of mysqli if it can not support this, so if that's my only option, how would I perform this using mysqli instead?

Answer 1

After creating the connection, you need to set PDO::MYSQL_ATTR_USE_BUFFERED_QUERY to false:

<?php
$pdo = new PDO('mysql:host=127.0.0.1', 'foo', 'bar', array(
    PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION,
));
$pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);

// snip

var_dump(memory_get_usage());
var_dump(memory_get_peak_usage());

This outputs:

int(39508)
int(653920)
int(668136)

Regardless of the result size, the memory usage remains pretty much static.

Answer 2

Another option would be to do something like:

$i = $c = 0;
$query = 'SELECT home, away FROM round LIMIT 2048 OFFSET %u;';

while ($c += count($rows = codeThatFetches(sprintf($query, $i++ * 2048))) > 0)
{
    foreach ($rows as $row)
    {
        do_stuff($row);
    }
}

Answer 3

The whole result set (all 30,000 rows) is buffered into memory before you can start looking at it.

You should be letting the database do the aggregation and only asking it for the two numbers you need.

SELECT SUM(home) AS home, SUM(away) AS away, COUNT(*) AS c FROM round

Answer 4

The reality of the situation is that if you fetch all rows and expect to be able to iterate over all of them in PHP, at once, they will exist in memory.

If you really don't think using SQL powered expressions and aggregation is the solution you could consider limiting/chunking your data processing. Instead of fetching all rows at once do something like:

1)  Fetch 5,000 rows
2)  Aggregate/Calculate intermediary results
3)  unset variables to free memory
4)  Back to step 1 (fetch next set of rows)

Just an idea...

Answer 5

I haven't done this before in PHP, but you may consider fetching the rows using a scrollable cursor - see the fetch documentation for an example.

Instead of returning all the results of your query at once back to your PHP script, it holds the results on the server side and you use a cursor to iterate through them getting one at a time.

Whilst I have not tested this, it is bound to have other drawbacks such as utilising more server resources and most likely reduced performance due to additional communication with the server.

Altering the fetch style may also have an impact as by default the documentation indicates it will store both an associative array and well as a numerical indexed array which is bound to increase memory usage.

As others have suggested, reducing the number of results in the first place is most likely a better option if possible.