Memory leak with namedQuery execution? #6199

New Issue

admin · 2026-01-22T15:28:44+01:00

admin commented

2026-01-22 15:28:44 +01:00

Originally created by @lashus on GitHub (Mar 11, 2019).

Originally assigned to: @lashus on GitHub.

Bug Report

Q	A
BC Break	no
Version	2.6

Summary

I am using symfony 3.4 along with doctrine for my project. In one of the commands where I'm generating the ranking for users I've encountered potential memory leak when using namedQuery (though I might be wrong).

In each loop iteration (over 2000 records) I'm running an EntityManager->clear() method along with gc_collect_cycles() (just to ensure proper garbage collection). Although the memory usage just after 10000 iterations jump to around 500mB which is really odd as it works normally in other commands that are using queryBuilder to form queries.

I've made sure that no other code is responsible for this memory issue and extracted the execution to just fetchin results and running named query.

I've also tried disable sql logger in entity manager config but this didn't help. Anyone got ideas what could be wrong?

NamedQuery

SELECT SUM(a.points) FROM __CLASS__ a WHERE a.user = :user GROUP BY a.user

Current behavior

The problematic part of code (up to ~500MB):

$query = $this->createNamedQuery('count_points');
$query->setParameter('user', $user->getId());
$query->setCacheable(false);

return $query->getSingleScalarResult();

How to reproduce

Just iterate over some larger set of data and try to run simple namedQuery along with fetching the scalar result. Sample code extracted from one of commands below.

// fetches total user count for batch processing
$userCount = $userRepo->getTotalCount();

// create it in batches
$batchSize = 2000;
$page = 1;
do {

    $offset = ($page-1)*$batchSize;
    
    $qb = $userRepo->createQueryBuilder('u');
    $qb->orderBy('u.createdAt', 'ASC');
    $qb->setFirstResult($offset);
    $qb->setMaxResults($batchSize);
    $users = $qb->getQuery()->getResult();

    foreach($users as $user) {
        
        $userStats = $statService->recalculateUserPlace($user); // HERE HAPPENS THE QUERY EXECUTION
        $this->em->persist($userStats);

    }
    unset($users);

    $this->em->flush();
    $this->em->clear();
    gc_collect_cycles();
    $page++;

} while ($offset < $userCount);

$this->em->flush();

Expected behavior

The solution w/o memory leak (up to ~60-70MB):

$query = $this->createNamedQuery('count_points');
$query->setParameter('user', $user->getId());
$query->setCacheable(false);

// bypass named query execution through doctrine for memory saving
$sql = $query->getSQL();
$conn = $this->getEntityManager()->getConnection();
$stmt = $conn->prepare($sql);
$stmt->execute([$user->getId()]);
unset($query);

return (int)$stmt->fetch(\PDO::FETCH_COLUMN);

Originally created by @lashus on GitHub (Mar 11, 2019). Originally assigned to: @lashus on GitHub. ### Bug Report  | Q | A |------------ | ------ | BC Break | no | Version | 2.6 #### Summary I am using symfony 3.4 along with doctrine for my project. In one of the commands where I'm generating the ranking for users I've encountered potential memory leak when using namedQuery (though I might be wrong). In each loop iteration (over 2000 records) I'm running an ``EntityManager->clear()`` method along with ``gc_collect_cycles()`` (just to ensure proper garbage collection). Although the memory usage just after 10000 iterations jump to around 500mB which is really odd as it works normally in other commands that are using queryBuilder to form queries. I've made sure that no other code is responsible for this memory issue and extracted the execution to just fetchin results and running named query. I've also tried disable sql logger in entity manager config but this didn't help. Anyone got ideas what could be wrong? ##### NamedQuery ```sql SELECT SUM(a.points) FROM __CLASS__ a WHERE a.user = :user GROUP BY a.user ``` #### Current behavior ##### The problematic part of code (up to ~500MB): ```php $query = $this->createNamedQuery('count_points'); $query->setParameter('user', $user->getId()); $query->setCacheable(false); return $query->getSingleScalarResult(); ``` #### How to reproduce Just iterate over some larger set of data and try to run simple namedQuery along with fetching the scalar result. Sample code extracted from one of commands below.  ```php // fetches total user count for batch processing $userCount = $userRepo->getTotalCount(); // create it in batches $batchSize = 2000; $page = 1; do { $offset = ($page-1)*$batchSize; $qb = $userRepo->createQueryBuilder('u'); $qb->orderBy('u.createdAt', 'ASC'); $qb->setFirstResult($offset); $qb->setMaxResults($batchSize); $users = $qb->getQuery()->getResult(); foreach($users as $user) { $userStats = $statService->recalculateUserPlace($user); // HERE HAPPENS THE QUERY EXECUTION $this->em->persist($userStats); } unset($users); $this->em->flush(); $this->em->clear(); gc_collect_cycles(); $page++; } while ($offset < $userCount); $this->em->flush(); ``` #### Expected behavior ##### The solution w/o memory leak (up to ~60-70MB): ```php $query = $this->createNamedQuery('count_points'); $query->setParameter('user', $user->getId()); $query->setCacheable(false); // bypass named query execution through doctrine for memory saving $sql = $query->getSQL(); $conn = $this->getEntityManager()->getConnection(); $stmt = $conn->prepare($sql); $stmt->execute([$user->getId()]); unset($query); return (int)$stmt->fetch(\PDO::FETCH_COLUMN); ```

admin added the Bug Invalid Missing Tests Question labels 2026-01-22 15:28:44 +01:00

admin closed this issue

2026-01-22 15:28:45 +01:00

admin commented

2026-01-22 15:28:45 +01:00

@Ocramius commented on GitHub (Mar 11, 2019):

I am using symfony 3.4 along with doctrine for my project.

Can the memleak be reproduced with doctrine/orm only? Symfony notoriously adds SQL loggers that cause memleaks (usually only in dev mode).

@Ocramius commented on GitHub (Mar 11, 2019): > I am using symfony 3.4 along with doctrine for my project. Can the memleak be reproduced with `doctrine/orm` only? Symfony notoriously adds SQL loggers that cause memleaks (usually only in dev mode).

admin commented

2026-01-22 15:28:45 +01:00

@lashus commented on GitHub (Mar 11, 2019):

@Ocramius SQL Loggers are disabled in the command but I can prepare some sample repo for this purpose.

@lashus commented on GitHub (Mar 11, 2019): @Ocramius SQL Loggers are disabled in the command but I can prepare some sample repo for this purpose.

admin commented

2026-01-22 15:28:46 +01:00

@Ocramius commented on GitHub (Mar 11, 2019):

sample repo for this purpose.

Please use our existing test suite. See https://github.com/doctrine/orm/tree/v2.6.3/tests/Doctrine/Tests/ORM/Functional/Ticket

@Ocramius commented on GitHub (Mar 11, 2019): > sample repo for this purpose. Please use our existing test suite. See https://github.com/doctrine/orm/tree/v2.6.3/tests/Doctrine/Tests/ORM/Functional/Ticket

admin commented

2026-01-22 15:28:46 +01:00

@lashus commented on GitHub (Mar 11, 2019):

Here is the example created accordingly to the Contribution file in the repo. I hope i haven't missed anything important.

https://github.com/lashus/orm/blob/bug/%237642-memory-leak-namedquery-execution/tests/Doctrine/Tests/ORM/Functional/Ticket/GH7642Test.php

@lashus commented on GitHub (Mar 11, 2019): Here is the example created accordingly to the Contribution file in the repo. I hope i haven't missed anything important. https://github.com/lashus/orm/blob/bug/%237642-memory-leak-namedquery-execution/tests/Doctrine/Tests/ORM/Functional/Ticket/GH7642Test.php

admin commented

2026-01-22 15:28:47 +01:00

@Ocramius commented on GitHub (Mar 11, 2019):

Can these two blocks be changed to not cause any I/O themselves? Hard to understand where the issue is otherwise:

f2e43267af/tests/Doctrine/Tests/ORM/Functional/Ticket/GH7642Test.php (L33-L107)

@Ocramius commented on GitHub (Mar 11, 2019): Can these two blocks be changed to not cause any I/O themselves? Hard to understand where the issue is otherwise: https://github.com/lashus/orm/blob/f2e43267af5ca64317226e57f9621b87ae89331e/tests/Doctrine/Tests/ORM/Functional/Ticket/GH7642Test.php#L33-L107

admin commented

2026-01-22 15:28:47 +01:00

@lashus commented on GitHub (Mar 11, 2019):

Could you please elaborate? I'm not sure what do you mean by I/O themselves. If I don't run queries we won't see the memory footprint they generate.

@lashus commented on GitHub (Mar 11, 2019): Could you please elaborate? I'm not sure what do you mean by ``I/O themselves``. If I don't run queries we won't see the memory footprint they generate.

admin commented

2026-01-22 15:28:47 +01:00

@Ocramius commented on GitHub (Mar 11, 2019):

I'd need the test to be re-structured like this (pseudo-code):

insert_db_data();
clear_everything();

$resultSet = load_data_via_query_that_leaks();
$memoryFootprint1 = measure_memory_footprint();

$resultSet = load_data_via_query_that_leaks();
$memoryFootprint2 = measure_memory_footprint();

assert_within_reasonable_distance($memoryFootprint1, $memoryFootprint2);

@Ocramius commented on GitHub (Mar 11, 2019): I'd need the test to be re-structured like this (pseudo-code): ```php insert_db_data(); clear_everything(); $resultSet = load_data_via_query_that_leaks(); $memoryFootprint1 = measure_memory_footprint(); $resultSet = load_data_via_query_that_leaks(); $memoryFootprint2 = measure_memory_footprint(); assert_within_reasonable_distance($memoryFootprint1, $memoryFootprint2); ```

admin commented

2026-01-22 15:28:47 +01:00

@lashus commented on GitHub (Mar 11, 2019):

Ok, thanks for the help and sorry for wasting time - I'm closing this as it's not related to doctrine apparently as the fixed tests are showing almost 0 difference between two methods. I've uploaded them anyway just in case.

In the original tests I put the return statement by accident in one of the methods which was causing the significant difference in memory output of these two functions .

f2e43267af/tests/Doctrine/Tests/ORM/Functional/Ticket/GH7642Test.php (L61)

@lashus commented on GitHub (Mar 11, 2019): Ok, thanks for the help and sorry for wasting time - I'm closing this as it's not related to doctrine apparently as the fixed tests are showing almost 0 difference between two methods. I've uploaded them anyway just in case. In the original tests I put the return statement by accident in one of the methods which was causing the significant difference in memory output of these two functions . https://github.com/lashus/orm/blob/f2e43267af5ca64317226e57f9621b87ae89331e/tests/Doctrine/Tests/ORM/Functional/Ticket/GH7642Test.php#L61

admin commented

2026-01-22 15:28:47 +01:00

@Ocramius commented on GitHub (Mar 11, 2019):

@lashus no worries! Glad it could be solved somehow :)

@Ocramius commented on GitHub (Mar 11, 2019): @lashus no worries! Glad it could be solved somehow :)

admin commented

2026-01-22 15:28:48 +01:00

@flaushi commented on GitHub (Mar 20, 2019):

I think I have a closely related issue, at least I can reproduce that the memory footprints do not differ even for a tiny byte.
I copied your test and filled in my problem, but the memory_get_peak_usage values are equal. Any other way to measure the difference?

@flaushi commented on GitHub (Mar 20, 2019): I think I have a [closely related issue](https://github.com/doctrine/orm/issues/7649), at least I can reproduce that the memory footprints do not differ even for a tiny byte. I copied your test and filled in my problem, but the `memory_get_peak_usage` values are equal. Any other way to measure the difference?

admin commented

2026-01-22 15:28:48 +01:00

@lashus commented on GitHub (Mar 20, 2019):

Hmmm it's going to be hard to be honest I was relying on that method and on my symfony command the leak still exists. Though I am using mysql in my project and as you pointed out in the related issue - the tests are ran on the sqlite database so it's not really reliable.

I'll try to reproduce this by running this code on external mysql connection but won't be able to do that until next week :/

@lashus commented on GitHub (Mar 20, 2019): Hmmm it's going to be hard to be honest I was relying on that method and on my symfony command the leak still exists. Though I am using mysql in my project and as you pointed out in the related issue - the tests are ran on the sqlite database so it's not really reliable. I'll try to reproduce this by running this code on external mysql connection but won't be able to do that until next week :/

Sign in to join this conversation.

Branches Tags

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: doctrine/archived-orm#6199