Memory leak when executing Doctrine query in loop

Question

I'm having trouble in locating the cause for a memory leak in my script. I have a simple repository method which increments a 'count' column in my entity by X amount:

public function incrementCount($id, $amount)
{
    $query = $this
        ->createQueryBuilder('e')
        ->update('MyEntity', 'e')
        ->set('e.count', 'e.count + :amount')
        ->where('e.id = :id')
        ->setParameter('id', $id)
        ->setParameter('amount', $amount)
        ->getQuery();

    $query->execute();
}

Problem is, if I call this in a loop the memory usage balloons on every iteration:

$doctrineManager = $this->getContainer()->get('doctrine')->getManager();
$myRepository = $doctrineManager->getRepository('MyEntity');
while (true) {
    $myRepository->incrementCount("123", 5);
    $doctrineManager->clear();
    gc_collect_cycles();
}

What am I missing here? I've tried ->clear(), as per Doctrine's advice on batch processing. I even tried gc_collect_cycles(), but still the issue remains.

I'm running Doctrine 2.4.6 on PHP 5.5.

Answer 1

Doctrine keeps logs of any query you make. If you make lots of queries (normally happens in loops) Doctrine can cause a huge memory leak.

You need to disable the Doctrine SQL Logger to overcome this.

I recommend doing this only for the loop part.

Before loop, get current logger:

$sqlLogger = $em->getConnection()->getConfiguration()->getSQLLogger();

And then disable the SQL Logger:

$em->getConnection()->getConfiguration()->setSQLLogger(null);

Do loop here: foreach() / while() / for()

After loop ends, put back the Logger:

$em->getConnection()->getConfiguration()->setSQLLogger($sqlLogger);

Answer 2

I resolved this by adding --no-debug to my command. It turns out that in debug mode, the profiler was storing information about every single query in memory.

Answer 3

You're wasting memory for each iteration. A much better way would be to prepare the query once and swap arguments many times. For example:

class MyEntity extends EntityRepository{
    private $updateQuery = NULL;

    public function incrementCount($id, $ammount)
    {
        if ( $this->updateQuery == NULL ){
            $this->updateQuery = $this->createQueryBuilder('e')
                ->update('MyEntity', 'e')
                ->set('e.count', 'e.count + :amount')
                ->where('e.id = :id')
                ->getQuery();
        }

        $this->updateQuery->setParameter('id', $id)
                ->setParameter('amount', $amount);
                ->execute();
    }
}

As you mentioned, you can employ batch processing here, but try this out first and see how well (if at all) performs...

Answer 4

For me it was clearing doctrine, or as the documentation says, detaching all entities:

$this->em->clear(); //Here em is the entity manager.

So inside my loop y flush every 1000 iterations and detach all entities (I don't need them anymore):

    foreach ($reader->getRecords() as $position => $value) {
        $this->processValue($value, $position);
        if($position % 1000 === 0){
            $this->em->flush();
            $this->em->clear();
        }
        $this->progress->advance();
    }

Hope this helps.

PS: here's the documentation.

Answer 5

I just ran into the same issue, these are the things that fixed it for me:

--no-debug

As the OP mentioned in their answer, setting --no-debug (ex: php app/console <my_command> --no-debug) is crucial for performance/memory in Symfony console commands. This is especially true when using Doctrine, as without it, Doctrine will go into debug mode which consumes a huge amount of additional memory (that increases on each iteration). See the Symfony docs here and here for more info.

--env=prod

You should also always specify the environment. By default, Symfony uses the dev environment for console commands. The dev environment usually isn't optimized for memory, speed, cpu etc. If you want to iterate over thousands of items, you should probably be using the prod environment (ex: php app/console <my_command> --no-debug). See the here and here for more info.

Tip: I created an environment called console that I specifically configured for running console commands. Here is info about how to create additional Symfony environments.

php -d memory_limit=YOUR_LIMIT

If running a big update, you should probably choose how much memory is acceptible for it to consume. This is especially important if you think there might be a leak. You can specify the memory for the Command by using php -d memory_limit=x (ex: php -d memory_limit=256M). Note: you can set the limit to -1 (usually the default for the php cli) to let the command run with no memory limit but this is obviously dangerous.

A Well Formed Console Command For Batch Processing

A well formed console command for running a big update using the above tips would look like:

php -d memory_limit=256M app/console <acme>:<your_command> --env=prod --no-debug

Use Doctrine's IterableResult

Another huge one, when using Doctrine's ORM in a loop, is to use doctrine's IterableResult (see the Doctrine Batch Processing docs). This won't help in the example provided but usually when doing processing like this it is over results from a query.

Output the memory usage as you go.

It can be really helpful to keep track of how much memory your command is consuming while it is running. You can do that by outputing the response returned by PHP's built in memory_get_usage() function.

Good luck!