Doctrine query execution memory issue

Question

I'm running a doctrine query in a loop, binding parameters and executing multiple times. The query executes fine, the problem is that the memory usage jumps up about 3mb per set of loops. It doesn't seem like garbage collection is kicking in and eventually the server runs out of memory.

foreach () { .....
    foreach () { .....
        $conn = $this->getEntityManager()->getConnection();

        if ($this->sql == null) {
            $this->sql = $conn->prepare(
                "INSERT INTO table (l_id, a_id) VALUES (:lId, :aId);"
            );
        }
        //Memory usage 200
        foreach($lo as $l) {
            $this->sql->bindParam('lId', $l->getId());
            $this->sql->bindParam('aId', $aId);
            $this->sql->execute();
        }
        //Memory usage ++3mb
    }
}

This entire script is also nested within a loop. So it will get called many many times. But the foreach loop above seems to be where the memory increases.

I'm calling straight inserts to the database, so the entity manager isn't even used as initially I thought this could be slowing it down.

Edit: I've tried changing bindParam to bindValue but the same issue occurs. and moving the second bindParam outside the loop;

I'm not sure how it works on ``postgresql`` but try calling ``$sql->closeCursor()`` after each ``$sql->execute()`` — Tomasz Madeyski, Feb 04 '15 at 08:52
@TomaszMadeyski The memory still climbs, but I'm waiting to see if garbage collection kicks in — iswinky, Feb 04 '15 at 09:00
question is if this memory is used by ``php`` or ``postgresql`` - I had similar issue on ``oracle`` db and it was ``oracle`` that used memory, not ``php`` — Tomasz Madeyski, Feb 04 '15 at 09:02
@TomaszMadeyski Appears to be php5 when checking on the server. — iswinky, Feb 04 '15 at 09:04
make sure you disable query logging and query profiling, see [this](http://stackoverflow.com/questions/9699185/memory-leaks-symfony2-doctrine2-exceed-memory-limit) Hope this help — Matteo, Feb 04 '15 at 09:05
Adding "--no-debug" to the command line didn't make much difference. But turning off sql logging worked a treat. `$conn->getConfiguration()->setSQLLogger(null);` Thanks guys — iswinky, Feb 04 '15 at 09:35
well! good to know :) consider to post an answer for mark question resolved — Matteo, Feb 04 '15 at 09:42

score 2 · Accepted Answer · answered Feb 04 '15 at 09:47

2

By turning off SQL logging in doctrine, the memory leak problem is resolved, considering the amount of queries getting fired off in this application,

$conn = $this->getEntityManager()->getConnection();
$conn->getConfiguration()->setSQLLogger(null);

answered Feb 04 '15 at 09:47

iswinky

1,951
3
16
47

Doctrine query execution memory issue

1 Answers1