search

laravel / framework

The Laravel Framework.
https://laravel.com
MIT License
32.43k stars 10.99k forks source link

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

Closed stockarea closed 3 years ago

stockarea commented 3 years ago

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 commented 3 years ago

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

stockarea commented 3 years ago

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

stockarea commented 3 years ago 
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 commented 3 years ago 
<?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 commented 3 years ago

@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 commented 3 years ago
You can simplify code by using LazyCollection and try with less chunk size.

```php 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 commented 3 years ago

@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 commented 3 years ago

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

driesvints commented 3 years ago

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

stockarea commented 3 years ago

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

crynobone commented 3 years ago

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:

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

driesvints commented 3 years ago

@stockarea just remove it from your composer dependencies

stockarea commented 3 years ago

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

stockarea commented 3 years ago

@crynobone This lazy collection will implement and tell about it

driesvints commented 3 years ago

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

stockarea commented 3 years ago

okay

stockarea commented 3 years ago

@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 commented 3 years ago

@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 commented 3 years ago

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

stockarea commented 3 years ago

@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 commented 3 years ago

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 commented 1 year ago

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 commented 2 months ago

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