Memory leak during bulk insert using DB::table('table_name')->insert() after upgrading to laravel 8 from 5.8

[stockarea]

• Laravel Version: 8.12.3
• PHP Version: 7.3.19
• Database Driver & Version: MySQL 5.7.30

Description:

I was reading a TSV file in chunks (1000 rows ) and inserting into the database. Earlier it never caused any issues. I continuously monitored the memory usage every time the command run. It was well under 30mb.

After upgrading to laravel 8, running the same command first gave me memory exhaustion error (memory_limit = 128M set on php.ini file).

Using Laravel Models was also giving the same issue.

Steps To Reproduce:

[taylorotwell]

This would need a minimum reproducible example. Is it possible that a debug screen like Ignition is causing the memory leak?

[stockarea]

@taylorotwell Not it's not because of that for sure. I am getting this on seeding a database from a file.

[stockarea]

class SeederBigFiles
{
    public function __construct($filename, $delimiter = "\t")
    {
        $this->file = fopen($filename, 'r');
        $this->delimiter = $delimiter;
        $this->iterator = 0;
        $this->header = null;
    }

    public function csvToArray()
    {
        $data = [];
        while (($row = fgetcsv($this->file, 1000, $this->delimiter)) !== false) {
            $mult_of_1000 = false;

            if (! $this->header) {
                $this->header = $row;
            } else {
                $this->iterator++;
                $data[] = array_combine($this->header, $row);
            }
            /**
             * To not include header as well as give the chunks back whenever the
             * number reaches 1000.
             */
            if ($this->iterator != 0 && $this->iterator % 1000 == 0) {
                $mult_of_1000 = true;
                $temp = $data;
                $data = [];
                yield $temp;
            }
        }
        fclose($this->file);
        if (! $mult_of_1000) {
            yield $data;
        }
    }
}

[stockarea]

<?php

namespace Database\Seeders;

use App\Classes\SeederBigFiles;
use App\Models\ServiceList;
use App\Traits\ServiceParametersMapper;
use Illuminate\Database\Seeder;
use Illuminate\Support\Facades\DB;

class ServiceListSeeder extends Seeder
{
    use ServiceParametersMapper;

    public function run()
    {
        $file = database_path('seeder-files/ServiceList.tsv');
        $sh = new SeederBigFiles($file, "\t");

        $cur_time = now();

        DB::statement('SET FOREIGN_KEY_CHECKS = 0');
        DB::table('service_list')->truncate();
        DB::statement('SET FOREIGN_KEY_CHECKS = 1');


        $mapper = $this->getMapper();

        /**
         * This reads the data from the csv in chunks of 1000;.
         */
        foreach ($sh->csvToArray() as $data) {

            error_log(memory_get_usage());

            // Preprocessing of the array.
            foreach ($data as $key => $entry) {
                $data[$key] = array_map(function ($val) {
                    return (empty($val)) ? null : $val;
                }, $data[$key]);
                // Laravel doesn't add timestamps on its own when inserting in chunks.
                $data[$key]['created_at'] = $cur_time;
                $data[$key]['updated_at'] = $cur_time;

                $description = [];

                if ($entry['category']) {
                    $description['category'] = $mapper['category'][$entry['category']];
                }

                if ($entry['billing_cycle']) {
                    $description['billing_cycle'] = $mapper['billing_cycle'][$entry['billing_cycle']];
                }

                if ($entry['pallet_configuration']) {
                    $description['pallet_configuration'] = $mapper['pallet_configuration'][$entry['pallet_configuration']];
                }

                if ($entry['stack_configuration']) {
                    $description['stack_configuration'] = $mapper['stack_configuration'][$entry['stack_configuration']];
                }

                if ($entry['rack_configuration']) {
                    $description['rack_configuration'] = $mapper['rack_configuration'][$entry['rack_configuration']];
                }

                if ($entry['temperature']) {
                    $description['temperature'] = $mapper['temperature'][$entry['temperature']];
                }

                if ($entry['warehouse_zone']) {
                    $description['warehouse_zone'] = $mapper['warehouse_zone'][$entry['warehouse_zone']];
                }

                if ($entry['storage_method']) {
                    $description['storage_method'] = $mapper['storage_method'][$entry['storage_method']];
                }

                if ($entry['shelf_configuration']) {
                    $description['shelf_configuration'] = $mapper['shelf_configuration'][$entry['shelf_configuration']];
                }

                if ($entry['bin_configuration']) {
                    $description['bin_configuration'] = $mapper['bin_configuration'][$entry['bin_configuration']];
                }

                if ($entry['product_configuration']) {
                    $description['product_configuration'] = $mapper['product_configuration'][$entry['product_configuration']];
                }

                if ($entry['order_range']) {
                    $description['order_range'] = $mapper['order_range'][$entry['order_range']];
                }

                if ($entry['inward_box_configuration']) {
                    $description['inward_box_configuration'] = $mapper['inward_box_configuration'][$entry['inward_box_configuration']];
                }

                if ($entry['packaging_configuration']) {
                    $description['packaging_configuration'] = $mapper['packaging_configuration'][$entry['packaging_configuration']];
                }

                if ($entry['label_type']) {
                    $description['label_type'] = $mapper['label_type'][$entry['label_type']];
                }

                if ($entry['area_configuration']) {
                    $description['area_configuration'] = $mapper['area_configuration'][$entry['area_configuration']];
                }

                if ($entry['volume_configuration']) {
                    $description['volume_configuration'] = $mapper['volume_configuration'][$entry['volume_configuration']];
                }

                if ($entry['last_mile_zone']) {
                    $description['last_mile_zone'] = $mapper['last_mile_zone'][$entry['last_mile_zone']];
                }

                if ($entry['freight_configuration']) {
                    $description['freight_configuration'] = $mapper['freight_configuration'][$entry['freight_configuration']];
                }

                if ($entry['container_configuration']) {
                    $description['container_configuration'] = $mapper['container_configuration'][$entry['container_configuration']];
                }

                if ($entry['custom_configuration']) {
                    $description['custom_configuration'] = $mapper['custom_configuration'][$entry['custom_configuration']];
                }
                /**
                 * It doesn't follow casting of models in case of bulk insertions.
                 * So we are inserting here using json_encode, whereas we can access
                 * in the codebase normally as it will follow casting then.
                 */
                $data[$key]['description'] = json_encode($description);
            }

            DB::table('service_list')->insert($data);

        }
    }
}

[stockarea]

@taylorotwell These are the two classes One seeder and one another class which is reading from the TSV file every 1000 entries. When I comment out the DB::table()->insert() statement, memory rise doesn't happen and is consistent. But if included, after every rise memory rise happens. I had the same code on laravel 5.8, but everything was working fine and no issues. I updated to laravel 8 and then faced this issue on seeding.

[dkulyk]

You can simplify code by using LazyCollection and try with less chunk size.

class SeederBigFiles extends \Illuminate\Support\LazyCollection
{
    public function __construct($filename, $delimiter = "\t")
    {
        $file = fopen($filename, 'r');
        $header = fgetcsv($file, 1000, $delimiter);
        parent::__construct(function () use ($file, $delimiter, $header) {
            while (($row = fgetcsv($file, 1000, $delimiter)) !== false) {
                yield array_combine($header, $row);
            }
            fclose($file);
        });
    }
}

(new SeederBigFiles($fileName))
    ->map(function ($entry) { 
        //prepare data
        return $entry;
    })
    ->chunk(100)
    ->each(function($data) {
        DB::table('service_list')->insert($data);
    });

[stockarea]

@dkulyk But why is my memory size increasing on every chunk addition, that's the point. If I extract 1000 rows and give it to seed, for every 1000 it should remain nearly the same. But for every chunk inserted, the memory never gets free, it just keeps on increasing.

Secondly, this was not the issue on the Laravel 5.8. So why on laravel 8?

[dkulyk]

maybe you added a telescope or enabled query log or something like a debug panel

[driesvints]

@stockarea have you tried disabling Telescope and Ignition to see if that solves it?

[stockarea]

Is facade/ignition added by default because of laravel 8. And if yes how to disable that by default.

[crynobone]

I had the same code on laravel 5.8, but everything was working fine and no issues. I updated to laravel 8 and then faced this issue on seeding.

Do you know how much does the memory usage differ between Laravel 5.8 and 8?

---

Few memory intensive usage:

• SeederBigFiles::csvToArray() after the first 1000 data, $temp will always contain the last 1000 records, only $data get set to empty array.
• ServiceListSeeder::run() on foreach($data ..., here 1000 records are put into memory and processed before batch insert to db.

Using LazyCollection as suggested by @dkulyk can make a huge different on memory usage.

[driesvints]

@stockarea just remove it from your composer dependencies

[stockarea]

@driesvints that i have not installed then. Can send you composer.json if needed. But have not installed it.

[stockarea]

@crynobone This lazy collection will implement and tell about it

[driesvints]

@stockarea feel free to report back later when you've evaluated it 👍

[stockarea]

okay

[stockarea]

@driesvints Please see this.

Seeding: ServiceListSeeder
memory usage after loop: 13634616
memory usage after loop: 13670320
memory usage after loop: 13670320
memory usage after loop: 13670320
memory usage after loop: 13670320
memory usage after loop: 13670320
memory usage after loop: 13670320
memory usage after loop: 13670320
memory usage after loop: 13669528
memory usage after loop: 13670320
memory usage after loop: 13670320
memory usage after loop: 13670320
memory usage after loop: 13670320
memory usage after loop: 13670320
memory usage after loop: 13670320
memory usage after loop: 13670320
memory usage after loop: 13665648
memory usage after loop: 13669872
memory usage after loop: 13670320
memory usage after loop: 13670320
memory usage after loop: 13670320
memory usage after loop: 13670320
memory usage after loop: 13670320
memory usage after loop: 13670320
memory usage after loop: 13670248
memory usage after loop: 13670320
memory usage after loop: 13670320
memory usage after loop: 13670320
memory usage after loop: 13670320
memory usage after loop: 13670320
memory usage after loop: 13670320
memory usage after loop: 13667472
memory usage after loop: 13666936
memory usage after loop: 13670320
memory usage after loop: 13670320
memory usage after loop: 13670320
memory usage after loop: 13670320
memory usage after loop: 13670320
memory usage after loop: 13670320
memory usage after loop: 13670320
memory usage after loop: 13670320
memory usage after loop: 13670320
memory usage after loop: 13670320
memory usage after loop: 13670320
memory usage after loop: 13670320
memory usage after loop: 13670320
memory usage after loop: 13670320
memory usage after loop: 13670320
memory usage after loop: 13672096
memory usage after loop: 13670352
memory usage after loop: 13766088

I went back to the previous commit where laravel 5.8 was installed. Updated through composer. And ran the same script for seeding. Memory is consistent and its only 13MB.

While this is for the current laravel 8 version

Seeding: Database\Seeders\ServiceListSeeder
Memory usage after the loop:14473520
Memory usage after the loop:18804480
Memory usage after the loop:24235392
Memory usage after the loop:29666528
Memory usage after the loop:35074624
Memory usage after the loop:40505664
Memory usage after the loop:45913504
Memory usage after the loop:51344576
Memory usage after the loop:56774888
Memory usage after the loop:61959176
Memory usage after the loop:67433064
Memory usage after the loop:72906984
Memory usage after the loop:78380904
Memory usage after the loop:83854792
Memory usage after the loop:89328904
Memory usage after the loop:94803048
Memory usage after the loop:100272616
Memory usage after the loop:104135664
Memory usage after the loop:109586320
Memory usage after the loop:115051536
Memory usage after the loop:120499536
Memory usage after the loop:125964848
Memory usage after the loop:131421072
Memory usage after the loop:136878000
Memory usage after the loop:142339784
Memory usage after the loop:147802280
Memory usage after the loop:153281416
Memory usage after the loop:158752456
Memory usage after the loop:164227848
Memory usage after the loop:169705640
Memory usage after the loop:175173864
Memory usage after the loop:180647944
Memory usage after the loop:185000216
Memory usage after the loop:189280280
Memory usage after the loop:194681176
Memory usage after the loop:200102328
Memory usage after the loop:205523672
Memory usage after the loop:210945048
Memory usage after the loop:216365240
Memory usage after the loop:221786584
Memory usage after the loop:227207960
Memory usage after the loop:232629112
Memory usage after the loop:238050456
Memory usage after the loop:243470872
Memory usage after the loop:248892024
Memory usage after the loop:254313368
Memory usage after the loop:259734744
Memory usage after the loop:265155896
Memory usage after the loop:270551240
Memory usage after the loop:275516008
Memory usage after the loop:279731952

It's the same code on both the cases.

[stockarea]

@dkulyk I am not using facade/ignition package. Only using laravel-debugbar which does not work on cli mode.

Here is my composer.json

{
    "name": "laravel/laravel",
    "type": "project",
    "description": "The Laravel Framework.",
    "keywords": [
        "framework",
        "laravel"
    ],
    "license": "MIT",
    "require": {
        "php": "~7.3.1",
        "ext-intl": "*",
        "ajcastro/eager-load-pivot-relations": "^0.2.2",
        "barryvdh/laravel-cors": "^2.0",
        "barryvdh/laravel-debugbar": "^3.5",
        "barryvdh/laravel-dompdf": "^0.8.6",
        "barryvdh/laravel-httpcache": "^0.3.6",
        "barryvdh/laravel-snappy": "^0.4.7",
        "doctrine/dbal": "^2.11",
        "fideloper/proxy": "^4.2",
        "fruitcake/laravel-cors": "^2.0",
        "fzaninotto/faker": "^1.4",
        "genealabs/laravel-model-caching": "^0.11.0",
        "guzzlehttp/guzzle": "^6.5",
        "h4cc/wkhtmltoimage-amd64": "0.12.x",
        "h4cc/wkhtmltopdf-amd64": "0.12.x",
        "iatstuti/laravel-cascade-soft-deletes": "^4.0",
        "jaybizzle/laravel-crawler-detect": "^1.2",
        "kitetail/zttp": "^0.6.0",
        "laravel/dusk": "^6.8",
        "laravel/framework": "^8.10",
        "laravel/helpers": "^1.3",
        "laravel/passport": "^10.0",
        "laravel/ui": "^3.0",
        "league/flysystem-aws-s3-v3": "^1.0",
        "mratiebatie/laravel-repositories": "^1.0",
        "predis/predis": "^1.1",
        "pusher/pusher-php-server": "^4.1",
        "spatie/crawler": "^4.6",
        "spatie/laravel-activitylog": "^3.16",
        "spatie/laravel-image-optimizer": "^1.6",
        "spatie/laravel-permission": "^3.17",
        "spatie/laravel-searchable": "^1.7",
        "spatie/laravel-sitemap": "^5.8",
        "way/laravel-test-helpers": "dev-master"
    },
    "require-dev": {
        "beyondcode/laravel-dump-server": "^1.5",
        "beyondcode/laravel-er-diagram-generator": "^1.4",
        "brianium/paratest": "^4.0",
        "filp/whoops": "^2.9",
        "fzaninotto/faker": "^1.9.1",
        "mockery/mockery": "^1.3.1",
        "nunomaduro/collision": "^5.0",
        "phpunit/phpunit": "^9.3",
        "laravel/tinker": "^2.0",
        "wemersonjanuario/wkhtmltopdf-windows": "0.12.2.3",
        "facade/ignition": "^2.3.6"
    },
    "config": {
        "optimize-autoloader": true,
        "preferred-install": "dist",
        "sort-packages": true
    },
    "extra": {
        "laravel": {
            "dont-discover": []
        }
    },
    "autoload": {
        "psr-4": {
            "App\\": "app/",
            "Database\\Factories\\": "database/factories/",
            "Database\\Seeders\\": "database/seeders/"
        }
    },
    "autoload-dev": {
        "psr-4": {
            "Tests\\": "tests/"
        }
    },
    "minimum-stability": "dev",
    "prefer-stable": true,
    "scripts": {
        "post-autoload-dump": [
            "Illuminate\\Foundation\\ComposerScripts::postAutoloadDump",
            "@php artisan package:discover --ansi"
        ],
        "post-root-package-install": [
            "@php -r \"file_exists('.env') || copy('.env.example', '.env');\""
        ],
        "post-create-project-cmd": [
            "@php artisan key:generate --ansi"
        ],
        "post-install-cmd": [
            "php artisan clear-compiled",
            "chmod -R 777 storage",
            "composer dump-autoload",
            "php artisan database:refresh",
            "php artisan passport:keys",
            "php artisan caching:route"
        ]
    }
}

[stockarea]

@driesvints Will see removing facade/ignition also. My bad didn't saw in require-dev

[stockarea]

@driesvints Too sorry. It was because of facade/ignition only. Laravel shift added it on require-dev which I missed. Sorry for wasting time.

[qazihamayun]

Man, you didn't waste the time, You saved the time, I took 24Hour to resolve this but was no luck, then found your post, which almost solved my issue, yes it was a memory leakage

[dvlpr91]

I'm in the same situation.

By the way, could the facade/ignition package be the cause?

{ 
    "name": "laravel/laravel",
    "type": "project",
    "description": "The Laravel Framework.",
    "keywords": ["framework", "laravel"],
    "license": "MIT",
    "require": { 
        "php": "^7.3|^8.0",
        "dvlpr91/jwt-auth": "^1.0",
        "elasticsearch/elasticsearch": "^8.2",
        "fideloper/proxy": "^4.4",
        "fruitcake/laravel-cors": "^2.0",
        "guzzlehttp/guzzle": "^7.0.1",
        "http-interop/http-factory-guzzle": "^1.2",
        "illuminate/support": "^8.35",
        "intervention/image": "^2.7",
        "jinseokoh/aligo": "^2.1",
        "lanin/laravel-api-debugger": "^4.0",
        "laravel/framework": "^8.12",
        "laravel/horizon": "^5.7",
        "laravel/scout": "^8.0",
        "laravel/slack-notification-channel": "^2.3",
        "laravel/socialite": "^5.2",
        "laravel/tinker": "^2.5",
        "league/flysystem-aws-s3-v3": "~1.0",
        "league/flysystem-cached-adapter": "~1.0",
        "lisennk/laravel-slack-events-api": "^1.3",
        "lisennk/laravel-slack-web-api": "^0.1.1",
        "meilisearch/meilisearch-php": "^0.23.3",
        "nyholm/psr7": "^1.4",
        "shufo/laravel-opensearch": "^0.1.4",
        "socialiteproviders/apple": "^5.0",
        "socialiteproviders/facebook": "^4.1",
        "socialiteproviders/google": "^4.1",
        "socialiteproviders/kakao": "^4.2",
        "socialiteproviders/manager": "^4.0",
        "socialiteproviders/naver": "^4.1",
        "socialiteproviders/slack": "^4.1",
        "symfony/dom-crawler": "^6.0",
        "tris-nm/laravel-scout-opensearch-engine": "dev-main",
        "vluzrmos/slack-api": "^0.5.5"
    },
    "require-dev": { 
        "fakerphp/faker": "^1.9.1",
        "knuckleswtf/scribe": "^2.5",
        "laravel/sail": "^1.0.1",
        "mockery/mockery": "^1.4.2",
        "nunomaduro/collision": "^5.0",
        "phpunit/phpunit": "^9.3.3"
    },
    "autoload": {
        "files": [
            "app/helpers.php"
        ],
        "psr-4": {
            "App\\": "app/",
            "Database\\Factories\\": "database/factories/",
            "Database\\Seeders\\": "database/seeders/"
        }
    },
    "autoload-dev": {
        "psr-4": {
            "Tests\\": "tests/"
        }
    },
    "scripts": {
        "post-autoload-dump": [
            "Illuminate\\Foundation\\ComposerScripts::postAutoloadDump",
            "@php artisan package:discover --ansi"
        ],
        "post-root-package-install": [
            "@php -r \"file_exists('.env') || copy('.env.example', '.env');\""
        ],
        "post-create-project-cmd": [
            "@php artisan key:generate --ansi"
        ]
    },
    "extra": {
        "laravel": {
            "dont-discover": []
        }
    },
    "config": {
        "optimize-autoloader": true,
        "preferred-install": "dist",
        "sort-packages": true
    },
    "minimum-stability": "dev",
    "prefer-stable": true
}

I removed the facade/ignition package, but the memory leak still occurs.

Also, I didn't add an event to trigger when data is inserted into the model.

---

The lanin/laravel-api-debugger package was the problem.

I got a hint from your comments.

Thank you.

[amcsi]

Now I'm getting this issue in Laravel 11, though it's caused by something new. See #52416

[dgsuperleo]

现在我在 Laravel 11 中遇到了这个问题，虽然这是由一些新东西引起的。请参阅#52416
I'm the same, it took me a long time to find the bug

[dgsuperleo]

I had a memory leak when inserting data in a for loop because I had telescope turned on.