How fix memory limit problem in laravel migration?

Question

\Rpn\Services\Onv\Models\OnvForm\EmissionsStationarySource::select("onvos_request_emissions_stationary_sources.*")
            ->join('onvs', function ($join) {
                $join->on('onvs.service_request_id', '=', 'onvos_request_emissions_stationary_sources.service_request_id');
            })
            ->whereNotNull('geometry')
            ->chunk(1000, function ($stationaries) {
                \DB::transaction(function () use ($stationaries) {
                    $layer = \Rpn\Services\Map\Models\MapLayer::MAP_LAYER_STATIONARY;
                    $type = \Rpn\Services\Onv\Models\OnvForm\EmissionsStationarySource::class;
                    /** @var \Rpn\Services\Onv\Models\OnvForm\EmissionsStationarySource $stationary */
                    foreach ($stationaries as $stationary) {
                        $id = $stationary->id;

                        if (empty($stationary->geometry)) {
                            continue;
                        }

                        $geo = json_encode($stationary->geometry);

                        try {
                            $point = \GeoJson\GeoJson::jsonUnserialize($stationary->geometry);
                        } catch (\Throwable $e) {
                            continue;
                        }

                        \DB::statement("
                            insert into map_objects(map_layer_id, model_type, model_id, geometry, created_at, updated_at)
                            values(${layer}, '${type}', ${id}, ST_MakeValid(ST_GeomFromGeoJSON('${geo}')), now(), now())
                            on conflict do nothing;
                        ");
                    }
                });
            });

Next code gives me a memory limit error (Allowed memory size of 2147483648 bytes exhausted). Why is it working this way, despite I am using cursor and chunks? How can I fix it?

score 0 · Accepted Answer · answered Jun 28 '22 at 14:30

If you're asking more about how to fix the memory limit, this is semi-answered here in this answer. Depending on what OS it's running on, you'll just to adjust the location accordingly.

If you're asking what's happening internally, there could be multiple things. Yes you're chunking data but it's hard to tell just from code alone without having it to debug (I tinker with issues, personally).

It could be things like how you have

if (empty($stationary->geometry)) {
  continue;
}

When you already check where geometry is not null prior. It could be translating this to anything, to be honest. Looping in SQL is slow because SQL is set based, however, it could be just getting the results and processing them in memory too.

Something else to remember is that you're running an insert statement every single iteration, that could be taxing as well.

How could a conditional statement possibly cause an out of memory error? — miken32, Jun 28 '22 at 21:06
I'm not saying that a conditional is the direct cause of an OOM issue. I said "It could be things like.....It could be translating this to anything, to be honest." It's like how there are times in LINQ that performance issues are created on the SQL side because it's written in a C# manner then translated to poor performing SQL. I'm saying that there could be multiple causes. IICR there was once a point where using Eloquent models was actually much slower in these cases then using just `DB::`. Again, that's if my memory serves me correctly. I assume it's just as performant now. — Matt Hatcher, Jun 28 '22 at 23:07

How fix memory limit problem in laravel migration?

1 Answers1