Doctrine findBy() out of memory error

问题:

I have a table containing roughly 300k rows describing devices for Apple's Push Notification service. I use Doctrine 2 as an ORM.

Inserting devices is no problem, however, retrieving them is a whole different story. Using a simple MySQL SELECT I can get them in a few seconds, where the WiFi is the main bottleneck. However, if I try to fetch them via Doctrine, it runs out of memory even if I allow PHP up to 1 gigabyte. I have created getters and setters and protected properties for the Doctrine entities, as per the documentation.

I have no clue what I'm doing wrong. This is fine:

$devices = mysql_query("SELECT * FROM `Devices` WHERE `deviceProperty`='someValue'");

$message = new Message();
while($device = mysql_fetch_array($devices))
{
    $message->addRecipient($device['pushToken']);
}

but this runs out of memory on the first line (it never reaches a breakpoint on the next line):

$devices = self::$entityManager->getRepository('Device')->findBy(array("deviceProperty" => "someValue"));
$message = new Message();
foreach($devices as $device)
{
    $message->addRecipient($device->getPushToken);
}

回答1:

You're pulling in 300k objects, it'll consume way too much memory, try processing in chunks...

$message = new Message();

$limit = 50;
$offset = 0;
while($devices = self::$entityManager->getRepository('Device')->findBy(array("deviceProperty" => "someValue"), array(), $limit, $offset))
{
   foreach($devices as $device)
   {
       $message->addRecipient($device->getPushToken);
   }
   $offset += $limit;
}

回答2:

If you're using DQL you can use the iterate() function to iterate through the results and flush each result after processing:

$message = new Message();
$cleaner = 0;

$q = self::$entityManager->createQuery(
    'SELECT d from Device d
     WHERE
     d.deviceProperty = :devicePropertyValue
    ');
$q->setParameter('devicePropertyValue', 'someValue');
//$q->setFirstResult(10000);
//$q->setMaxResults(5000);

$devices = $q->iterate();

foreach ($devices as $row) {
    $device = $row[0];

    $message->addRecipient($device->getPushToken);

    self::$entityManager->detach($device);

    if ($cleaner++ > 100) {
        self::$entityManager->clear();
        $cleaner = 0;
    }

}

For the batch reindexing job I'm running, this reduced my memory requirement down from 3GB to under 256M.

According to the Doctrine documentation, "Iterating results is not possible with queries that fetch-join a collection-valued association" - I presume this means built-in methods such as findBy().

Note the commented-out setFirstResult() and setMaxResults() which you can use for offset and limit.