Batch Processing ,out of memory #6525

imsheng · 2017-06-25T08:16:28Z

http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/batch-processing.html#iterating-large-results-for-data-processing

$q = $this->_em->createQuery('select u from MyProject\Model\User u');
$iterableResult = $q->iterate();
foreach ($iterableResult as $row) {
    //nothing
    $this->_em->detach($row[0]);
}

PHP Fatal error:  Allowed memory size of 134217728 bytes exhausted (tried to all
ocate 1425408 bytes) in C:\mmood\vendor\doctrine\dbal\lib\Doctrine\DBAL\Driver\P
DOConnection.php on line 104


  [Symfony\Component\Debug\Exception\OutOfMemoryException]
  Error: Allowed memory size of 134217728 bytes exhausted (tried to allocate
  1425408 bytes)

Total: 4000,000 rows
I tried the code and found that was out of memory

The text was updated successfully, but these errors were encountered:

Ocramius · 2017-06-25T08:19:16Z

@imsheng this needs more details, such as:

which associations may be involved
whether this is reproducible on master (see DDC-3146 Remove onClear event listener from abstract hydrator in cleanup function, otherwise causing hydrator GC to not kick in #1515)
a stack trace

imsheng · 2017-06-25T08:36:20Z

in symfony console.
doctrine/orm version 2.5
my code is just that：

    protected function execute(InputInterface $input, OutputInterface $output)
    {
        set_time_limit(0);
        $this->em = $this->getContainer()->get('doctrine.orm.default_entity_manager');
        $xxxxDql = $this->em->createQuery('select w from AppBundle:xxxx w');
        $xxxxResult = $xxxxDql ->iterate();
		foreach ($xxxxResult as $row) {
			// do stuff with the data in the row, $row[0] is always the object

			// detach from Doctrine, so that it can be Garbage-Collected immediately
			$this->_em->detach($row[0]);
		}
    }

PHP Fatal error:  Allowed memory size of 134217728 bytes exhausted (tried to all
ocate 1425408 bytes) in C:\mmood\vendor\doctrine\dbal\lib\Doctrine\DBAL\Driver\P
DOConnection.php on line 104

nothing else

Ocramius · 2017-06-25T08:37:45Z

Run it with -vvv

imsheng · 2017-06-25T08:46:43Z

C:\mmood>php bin/console xxxxxxx -e=prod -vvv
PHP Fatal error:  Allowed memory size of 134217728 bytes exhausted (tried to all
ocate 1429504 bytes) in C:\mmood\vendor\doctrine\dbal\lib\Doctrine\DBAL\Driver\P
DOConnection.php on line 104


  [Symfony\Component\Debug\Exception\OutOfMemoryException]
  Error: Allowed memory size of 134217728 bytes exhausted (tried to allocate
  1429504 bytes)


Exception trace:
 () at C:\mmood\vendor\doctrine\dbal\lib\Doctrine\DBAL\Driver\PDOConnection.php:
104

Ocramius · 2017-06-25T09:07:44Z

That still shows no trace, but the fact that it is crashing within the connection may indicate that the resultset is too big.

imsheng · 2017-06-25T09:13:12Z

what should I do? I need to deal with tens of millions of data

Ocramius · 2017-06-25T09:17:39Z

Sadly, PDO does not have cursor-alike operations: mysqli does, for example.

Just a hint though: if you are off doing million records operations, then the ORM is likely the wrong tool for the job...

Ocramius · 2017-06-25T09:18:11Z

Ref: doctrine/dbal#2718

imsheng · 2017-06-25T10:55:53Z

I tried this method, but still out of memory：

$count = $this->em->getRepository('AppBundle:xxxxxx')->createQueryBuilder('xxxxxxx')
            ->select('count(xxxxxxx) c')
            ->getQuery()
            ->getSingleScalarResult();
//count:4000,000
        for ($i = 0; $i <= $count - 1; $i++) {
            $xxx= $this->em->getRepository('AppBundle:xxx')->createQueryBuilder('xx')
                ->setFirstResult($i)
                ->setMaxResults(1)
                ->getQuery()
                ->getSingleResult();
           //nothing
        }

Ocramius · 2017-06-25T10:58:40Z

Are you clearing in between results? Also, that method is unsafe if data is added or deleted during the batch operation On 25 Jun 2017 12:55 PM, "imsheng" <[email protected]> wrote: I tried this method, but still out of memory： $count = $this->em->getRepository('AppBundle:xxxxxx')->createQueryBuilder('xxxxxxx') ->select('count(xxxxxxx) c') ->getQuery() ->getSingleScalarResult(); //count:4000,000 for ($i = 0; $i <= $count - 1; $i++) { $xxx= $this->em->getRepository('AppBundle:xxx')->createQueryBuilder('xx') ->setFirstResult($i) ->setMaxResults(1) ->getQuery() ->getSingleResult(); //nothing } — You are receiving this because you commented. Reply to this email directly, view it on GitHub <#6525 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AAJakJzJ6PgYCote9yrQupLz2vOAdyrkks5sHjy6gaJpZM4OEjCI> .

imsheng · 2017-06-25T11:19:49Z

without update or insert.
The same is true with add this code.

$this->em->detach();
$this->em->clear();
unset();

what I do is a crawler
the php needs to run for a long time.
Thanks for your help :)

Ocramius · 2017-06-25T11:59:55Z

Then there's a memleak somewhere else - this needs debugging either in your codebase, or be reproduced in an isolated test case within this library: I can't help further without any of those.

trickeyone · 2017-07-14T23:14:18Z

Setting the variable to NULL is recommended in place of using unset. It tends to work better with GC (refCount issues). You should also look at if you have any relations set to EAGER. If you do, it would probably be better to use LAZY or EXTRA_LAZY to keep from having too many objects loaded into memory.

Ocramius · 2017-07-15T13:54:48Z

It tends to work better with GC (refCount issues).

It doesn't: refcount works in the exact same way. If it doesn't, report a php-src bug.

elnur · 2017-09-10T10:36:03Z

Running into this problem, too. I can't even get to iteration itself. It fails with OOM right here:

$this->entityManager
    ->getRepository(UploadView::class)
    ->createQueryBuilder('v')
    ->orderBy('v.id', 'asc')
    ->getQuery()
    ->iterate();

Need to process 12M rows.

I expected it to work the same way DBAL does because I don't have a problem iterating over a huge result set with DBAL. This time I need model objects tho.

elnur · 2017-09-10T11:02:25Z

As a workaround, I ended up iterating over 10K rows at a time:

$lastId = 0;

while (true) {
    $iterable = $this->entityManager
        ->getRepository(UploadView::class)
        ->createQueryBuilder('v')
        ->where('v.id > :id')
        ->orderBy('v.id', 'asc')
        ->setMaxResults(10000)
        ->getQuery()
        ->iterate([
            'id' => $lastId,
        ]);

    $iterated = false;
    foreach ($iterable as $row) {
        $iterated = true;
        /** @var UploadView $view */
        $view = $row[0];
        $lastId = $view->getId();
        // do actual processing
        $this->entityManager->detach($view);
    }

    $this->entityManager->clear();

    if (!$iterated) {
        break;
    }
}

Ocramius · 2017-09-10T13:27:51Z

Ah yes, fairly sure that the ORM doesn't lazily iterate over DBAL resultsets: that is a big issue that needs work.

lcobucci · 2017-10-29T18:44:06Z

Ah yes, fairly sure that the ORM doesn't lazily iterate over DBAL resultsets: that is a big issue that needs work.

@Ocramius it would possibly be done for ORM v3.x, right?

Ocramius · 2017-10-29T23:35:21Z

Yeah, not 2.x

…

On 29 Oct 2017 19:44, "Luís Cobucci" ***@***.***> wrote: Ah yes, fairly sure that the ORM doesn't lazily iterate over DBAL resultsets: that is a big issue that needs work. @Ocramius <https://github.com/ocramius> it would possibly be done for ORM v3.x, right? — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#6525 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AAJakADmwebqnVp7XH0c5w0C1JbRQiyQks5sxMd3gaJpZM4OEjCI> .

stouch · 2019-11-24T22:04:49Z

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

worked for me

Cirrusware · 2020-03-03T08:21:20Z

Try this, works for me

$conn = $emd->getConnection();
$conn->getWrappedConnection()
->setAttribute(\PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);
$conn->getConfiguration()->setSQLLogger(null);

Cirrusware · 2020-03-03T12:30:54Z

If you wish persist an entity, then you have this error
SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active. Consider using PDOStatement::fetchAll(). Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute.

To avoid this, create another Entity Manager with PDO::MYSQL_ATTR_USE_BUFFERED_QUERY to true (by default) and use merge.

$emd2->merge($entity);

rafalrudnicki · 2020-04-08T13:31:48Z

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

worked for me

It works perfectly

Ocramius added Bug Missing Tests labels Jun 25, 2017

imsheng closed this as completed Dec 6, 2017

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Batch Processing ,out of memory #6525

Batch Processing ,out of memory #6525

imsheng commented Jun 25, 2017

Ocramius commented Jun 25, 2017 •

edited

Loading

imsheng commented Jun 25, 2017

Ocramius commented Jun 25, 2017

imsheng commented Jun 25, 2017

Ocramius commented Jun 25, 2017

imsheng commented Jun 25, 2017

Ocramius commented Jun 25, 2017

Ocramius commented Jun 25, 2017 •

edited

Loading

imsheng commented Jun 25, 2017

Ocramius commented Jun 25, 2017 via email

imsheng commented Jun 25, 2017

Ocramius commented Jun 25, 2017

trickeyone commented Jul 14, 2017

Ocramius commented Jul 15, 2017 •

edited

Loading

elnur commented Sep 10, 2017 •

edited

Loading

elnur commented Sep 10, 2017 •

edited

Loading

Ocramius commented Sep 10, 2017

lcobucci commented Oct 29, 2017

Ocramius commented Oct 29, 2017 via email

stouch commented Nov 24, 2019

Cirrusware commented Mar 3, 2020

Cirrusware commented Mar 3, 2020

rafalrudnicki commented Apr 8, 2020

Batch Processing ,out of memory #6525

Batch Processing ,out of memory #6525

Comments

imsheng commented Jun 25, 2017

Ocramius commented Jun 25, 2017 • edited Loading

imsheng commented Jun 25, 2017

Ocramius commented Jun 25, 2017

imsheng commented Jun 25, 2017

Ocramius commented Jun 25, 2017

imsheng commented Jun 25, 2017

Ocramius commented Jun 25, 2017

Ocramius commented Jun 25, 2017 • edited Loading

imsheng commented Jun 25, 2017

Ocramius commented Jun 25, 2017 via email

imsheng commented Jun 25, 2017

Ocramius commented Jun 25, 2017

trickeyone commented Jul 14, 2017

Ocramius commented Jul 15, 2017 • edited Loading

elnur commented Sep 10, 2017 • edited Loading

elnur commented Sep 10, 2017 • edited Loading

Ocramius commented Sep 10, 2017

lcobucci commented Oct 29, 2017

Ocramius commented Oct 29, 2017 via email

stouch commented Nov 24, 2019

Cirrusware commented Mar 3, 2020

Cirrusware commented Mar 3, 2020

rafalrudnicki commented Apr 8, 2020

It works perfectly

Ocramius commented Jun 25, 2017 •

edited

Loading

Ocramius commented Jun 25, 2017 •

edited

Loading

Ocramius commented Jul 15, 2017 •

edited

Loading

elnur commented Sep 10, 2017 •

edited

Loading

elnur commented Sep 10, 2017 •

edited

Loading