DEVHIDE
Login Or Sign up

How to handle large amount of INSERT in Symfony2/Doctrine2 to avoid "Allowed memory size of 2147483648 bytes exhausted"

1.9k Views Asked by At

I need to handle a huge amount of INSERT operations in Doctrine2 and by huge I mean around 130k or more records. This is how my code looks:

$this->_batchSize = 1000;

$entTarget = $this->_em->getRepository('PDOneBundle:Target')->findAll();

$currTargetArr = [];
foreach ($entTarget as $currTarget) {
    $currTargetArr[] = $currTarget->getVeevaAccountId();
}

// $currTargetArr => is empty because there is nothing on that table

echo count($finalOutput);

for ($i = 0; $i < count($finalOutput); $i++) {
    echo count($finalOutput[$i]['records']), "\n";
}

for ($i = 0; $i < count($finalOutput); $i++) {
    for ($j = 0; $j < count($finalOutput[$i]['records']); $j++) {
        $veevaAccountId = $finalOutput[$i]['records'][$j]['Id'];

        // if there is no territory, then we add
        if ($veevaAccountId !== null && !in_array($veevaAccountId, $currTargetArr, true)) {
            // we set the values from veeva
            if (
                $finalOutput[$i]['records'][$j]['Id'] !== null &&
                $finalOutput[$i]['records'][$j]['FirstName'] !== null &&
                $finalOutput[$i]['records'][$j]['LastName'] !== null
            ) {
                $newTarget = new Entity\Target();

                $newTarget->setVeevaAccountId($finalOutput[$i]['records'][$j]['Id']);
                $newTarget->setNpi($finalOutput[$i]['records'][$j]['NPI_vod__c']);
                $newTarget->setFirst(ucfirst(strtolower($finalOutput[$i]['records'][$j]['FirstName'])));
                $newTarget->setLast(ucfirst(strtolower($finalOutput[$i]['records'][$j]['LastName'])));
                $newTarget->setTitle($finalOutput[$i]['records'][$j]['Title__c']);
                $newTarget->setDisplayName(
                    ucfirst(strtolower($finalOutput[$i]['records'][$j]['FirstName'])).' '.ucfirst(
                        strtolower($finalOutput[$i]['records'][$j]['LastName'])
                    )
                );

                $newTarget->setLastSyncAt(new \DateTime());

                $this->_em->persist($newTarget);

                if (($i % $this->_batchSize) === 0) {
                    echo 'Flushing batch...'."\n";
                    echo 'Memory: '.$this->getReadableSize(memory_get_usage())."\n";

                    $this->_em->flush();
                    $this->_em->clear(); // Detaches all objects from Doctrine!

                    echo 'After batch...'."\n";
                    echo 'Memory: '.$this->getReadableSize(memory_get_usage())."\n";
                }
            }

            $targetArr[] = $newTarget->getId();
            $targetFailArr[] = $finalOutput[$i]['records'][$j]['FirstName'].' '.$finalOutput[$i]['records'][$j]['LastName'];
        } else {
            $entTarget = $this->_em->getRepository('PDOneBundle:Target')->find($veevaAccountId);

            $lastModifiedDate = new \DateTime(
                $finalOutput[$i]['records'][$j]['LastModifiedDate']
            );

            if ($lastModifiedDate > $entTarget->getUpdatedAt()) {
                // obtained a territory, we update its data
                // $entTarget->setName($finalOutput[$i]['records'][$j]['Name']);
            }

            $targetArr[] = $entTarget->getId();
        }
    }
}

$this->_em->flush();
$this->_em->clear();

When it reach 2000 records script stop execution and fail with this message:

Fatal error: Allowed memory size of 2147483648 bytes exhausted (tried to allocate 18 bytes) in /var/www/html/reptooln_admin/vendor/doctrine/orm/lib/Doctrine/ORM/UnitOfWork.php on line 542

Call Stack:
    0.0001     229280   1. {main}() /var/www/html/reptooln_admin/app/console:0
    0.0152    3246832   2. Symfony\Component\Console\Application->run() /var/www/html/reptooln_admin/app/console:27
    0.0167    3449664   3. Symfony\Bundle\FrameworkBundle\Console\Application->doRun() /var/www/html/reptooln_admin/vendor/symfony/symfony/src/Symfony/Component/Console/Application.php:126
    1.8824   54241840   4. Symfony\Component\Console\Shell->run() /var/www/html/reptooln_admin/vendor/symfony/symfony/src/Symfony/Bundle/FrameworkBundle/Console/Application.php:91
   14.7210   54278272   5. Symfony\Component\Console\Application->run() /var/www/html/reptooln_admin/vendor/symfony/symfony/src/Symfony/Component/Console/Shell.php:112
   14.7211   54278480   6. Symfony\Bundle\FrameworkBundle\Console\Application->doRun() /var/www/html/reptooln_admin/vendor/symfony/symfony/src/Symfony/Component/Console/Application.php:126
   14.7213   54278736   7. Symfony\Component\Console\Application->doRun() /var/www/html/reptooln_admin/vendor/symfony/symfony/src/Symfony/Bundle/FrameworkBundle/Console/Application.php:96
   14.7214   54279656   8. Symfony\Component\Console\Application->doRunCommand() /var/www/html/reptooln_admin/vendor/symfony/symfony/src/Symfony/Component/Console/Application.php:195
   14.7248   54502144   9. Symfony\Component\Console\Command\Command->run() /var/www/html/reptooln_admin/vendor/symfony/symfony/src/Symfony/Component/Console/Application.php:886
   14.7250   54507096  10. PDI\PDOneBundle\Command\PDOneSyncCommand->execute() /var/www/html/reptooln_admin/vendor/symfony/symfony/src/Symfony/Component/Console/Command/Command.php:259
   14.7251   54508672  11. PDI\PDOneBundle\Service\SyncController->syncVeevaData() /var/www/html/reptooln_admin/src/PDI/PDOneBundle/Command/PDOneSyncCommand.php:29
   15.8199   54512624  12. PDI\PDOneBundle\Service\SyncController->syncTargets() /var/www/html/reptooln_admin/src/PDI/PDOneBundle/Service/SyncController.php:95
  269.3835  719438056  13. EntityManager55881a77c7652_546a8d27f194334ee012bfe64f629947b07e4919\__CG__\Doctrine\ORM\EntityManager->flush() /var/www/html/reptooln_admin/src/PDI/PDOneBundle/Service/SyncController.php:486
  269.3835  719438104  14. Doctrine\ORM\EntityManager->flush() /var/www/html/reptooln_admin/app/cache/dev/jms_diextra/doctrine/EntityManager_55881a77c7652.php:305
  269.3835  719438104  15. Doctrine\ORM\UnitOfWork->commit() /var/www/html/reptooln_admin/vendor/doctrine/orm/lib/Doctrine/ORM/EntityManager.php:340
  269.3835  719438104  16. Doctrine\ORM\UnitOfWork->computeChangeSets() /var/www/html/reptooln_admin/vendor/doctrine/orm/lib/Doctrine/ORM/UnitOfWork.php:297
  269.3835  719438104  17. Doctrine\ORM\UnitOfWork->computeScheduleInsertsChangeSets() /var/www/html/reptooln_admin/vendor/doctrine/orm/lib/Doctrine/ORM/UnitOfWork.php:711
  299.1056 2146967320  18. Doctrine\ORM\UnitOfWork->computeChangeSet() /var/www/html/reptooln_admin/vendor/doctrine/orm/lib/Doctrine/ORM/UnitOfWork.php:404
  299.1056 2146970736  19. ReflectionProperty->getValue() /var/www/html/reptooln_admin/vendor/doctrine/orm/lib/Doctrine/ORM/UnitOfWork.php:542

I'm executing this as a Symfony2 tasks so it uses CLI and not web and I call it setting 2G as max memory:

php -d memory_limit=2G app/console --shell

I have read several docs (1, 2, 3, 4, 5 ...) but is not helping me at all, can any give me some ideas in how to handle this?

Update: memory consumption is less but only got 2k inserts

After write some debug info to see what is happening on my logic I notice that only the first 2k items are persisted and not the rest and I can't find the cause. See this output:

php -d memory_limit=1G app/console pdone:sync --no-debug

iterations
------------------------------------------------------------------
0- instancePath: /services/data/v28.0/query/01g8000002eusZCAAY-2000
1- instancePath: /services/data/v28.0/query/01g8000002eusZCAAY-4000
2- instancePath: /services/data/v28.0/query/01g8000002eusZCAAY-6000
3- instancePath: /services/data/v28.0/query/01g8000002eusZCAAY-8000
4- instancePath: /services/data/v28.0/query/01g8000002eusZCAAY-10000
5- instancePath: /services/data/v28.0/query/01g8000002eusZCAAY-12000
6- instancePath: /services/data/v28.0/query/01g8000002eusZCAAY-14000
7- instancePath: /services/data/v28.0/query/01g8000002eusZCAAY-16000
8- instancePath: /services/data/v28.0/query/01g8000002eusZCAAY-18000
9- instancePath: /services/data/v28.0/query/01g8000002eusZCAAY-20000
10- instancePath: /services/data/v28.0/query/01g8000002eusZCAAY-22000
11- instancePath: /services/data/v28.0/query/01g8000002eusZCAAY-24000
12- instancePath: /services/data/v28.0/query/01g8000002eusZCAAY-26000
13- instancePath: /services/data/v28.0/query/01g8000002eusZCAAY-28000
14- instancePath: /services/data/v28.0/query/01g8000002eusZCAAY-30000
15- instancePath: /services/data/v28.0/query/01g8000002eusZCAAY-32000
16- instancePath: /services/data/v28.0/query/01g8000002eusZCAAY-34000
17- instancePath: /services/data/v28.0/query/01g8000002eusZCAAY-36000
18- instancePath: /services/data/v28.0/query/01g8000002eusZCAAY-38000
19- instancePath: /services/data/v28.0/query/01g8000002eusZCAAY-40000
20- instancePath: /services/data/v28.0/query/01g8000002eusZCAAY-42000
21- instancePath: /services/data/v28.0/query/01g8000002eusZCAAY-44000
22- instancePath: /services/data/v28.0/query/01g8000002eusZCAAY-46000
23- instancePath: /services/data/v28.0/query/01g8000002eusZCAAY-48000
24- instancePath: /services/data/v28.0/query/01g8000002eusZCAAY-50000
25- instancePath: /services/data/v28.0/query/01g8000002eusZCAAY-52000
26- instancePath: /services/data/v28.0/query/01g8000002eusZCAAY-54000
27- instancePath: /services/data/v28.0/query/01g8000002eusZCAAY-56000
28- instancePath: /services/data/v28.0/query/01g8000002eusZCAAY-58000
29- instancePath: /services/data/v28.0/query/01g8000002eusZCAAY-60000
30- instancePath: /services/data/v28.0/query/01g8000002eusZCAAY-62000
31- instancePath: /services/data/v28.0/query/01g8000002eusZCAAY-64000
32- instancePath: /services/data/v28.0/query/01g8000002eusZCAAY-66000
33- instancePath: /services/data/v28.0/query/01g8000002eusZCAAY-68000
34- instancePath: /services/data/v28.0/query/01g8000002eusZCAAY-70000
35- instancePath: /services/data/v28.0/query/01g8000002eusZCAAY-72000
36- instancePath: /services/data/v28.0/query/01g8000002eusZCAAY-74000
37- instancePath: /services/data/v28.0/query/01g8000002eusZCAAY-76000
38- instancePath: /services/data/v28.0/query/01g8000002eusZCAAY-78000
39- instancePath: /services/data/v28.0/query/01g8000002eusZCAAY-80000
40- instancePath: /services/data/v28.0/query/01g8000002eusZCAAY-82000
41- instancePath: /services/data/v28.0/query/01g8000002eusZCAAY-84000
42- instancePath: /services/data/v28.0/query/01g8000002eusZCAAY-86000
43- instancePath: /services/data/v28.0/query/01g8000002eusZCAAY-88000
44- instancePath: /services/data/v28.0/query/01g8000002eusZCAAY-90000
45- instancePath: /services/data/v28.0/query/01g8000002eusZCAAY-92000
46- instancePath: /services/data/v28.0/query/01g8000002eusZCAAY-94000
47- instancePath: /services/data/v28.0/query/01g8000002eusZCAAY-96000
48- instancePath: /services/data/v28.0/query/01g8000002eusZCAAY-98000
49- instancePath: /services/data/v28.0/query/01g8000002eusZCAAY-100000
50- instancePath: /services/data/v28.0/query/01g8000002eusZCAAY-102000
51- instancePath: /services/data/v28.0/query/01g8000002eusZCAAY-104000
52- instancePath: /services/data/v28.0/query/01g8000002eusZCAAY-106000
53- instancePath: /services/data/v28.0/query/01g8000002eusZCAAY-108000
54- instancePath: /services/data/v28.0/query/01g8000002eusZCAAY-110000
55- instancePath: /services/data/v28.0/query/01g8000002eusZCAAY-112000
56- instancePath: /services/data/v28.0/query/01g8000002eusZCAAY-114000
57- instancePath: /services/data/v28.0/query/01g8000002eusZCAAY-116000
58- instancePath: /services/data/v28.0/query/01g8000002eusZCAAY-118000
59- instancePath: /services/data/v28.0/query/01g8000002eusZCAAY-120000
60- instancePath: /services/data/v28.0/query/01g8000002eusZCAAY-122000
61- instancePath: /services/data/v28.0/query/01g8000002eusZCAAY-124000
62- instancePath: /services/data/v28.0/query/01g8000002eusZCAAY-126000
63- instancePath: /services/data/v28.0/query/01g8000002eusZCAAY-128000
64- instancePath: /services/data/v28.0/query/01g8000002eusZCAAY-130000
65- instancePath: /services/data/v28.0/query/01g8000002eusZCAAY-132000
66- instancePath: /services/data/v28.0/query/01g8000002eusZCAAY-134000

total count:
------------------------------------------------------------------
682000

count per iteration:
------------------------------------------------------------------
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
1733

Why just the first 2k are persisted? What happen with the rest of items on each iteration?

php symfony doctrine-orm doctrine batch-processing
Original Q&A
2

There are 2 best solutions below

3
On BEST ANSWER

You are obviously in a loop here that you have not shown us.

Have you considered releasing the memory used by $newTarget at the end of each loop using 

unset($newtarget);

This should allow the garbage collector to release the memory once you start to push up towards the memory limit.

If this actually is your issue, you will probably find you dont need to run with such a hugh memory allocation either.

0
On

Make sure to run the command with the --no-debug option, this will drastically reduce memory usage.

Related Questions in PHP

Related Questions in SYMFONY

Related Questions in DOCTRINE-ORM

Related Questions in DOCTRINE

Related Questions in BATCH-PROCESSING

Trending Questions

Popular # Hahtags

javascript python java c# php android html jquery c++ css ios sql mysql r reactjs node.js arrays c asp.net json python-3.x ruby-on-rails .net sql-server swift django angular objective-c pandas excel

Popular Questions

Copyright © 2021 Jogjafile Inc.