Laravel / Eloquent memory leak retrieving the same record repeatedly

Question

I am trying to write a laravel function that gets lots of records (100,000+) from one database and puts it in another database. Towards that end, I need to query my database and see if the user already exists. I repeatedly call this code:

$users = User::where('id', '=', 2)->first();

And then after that happens a few hundred times, I run out of memory. So, I made a minimalist example of it using up all the available memory, and it looks like this:

<?php

use Illuminate\Console\Command;

class memoryleak extends Command
{
    protected $name = 'command:memoryleak';
    protected $description = 'Demonstrates memory leak.';

    public function fire()
    {
        ini_set("memory_limit","12M");

        for ($i = 0; $i < 100000; $i++)
        {
            var_dump(memory_get_usage());
            $this->external_function();
        }
    }

    function external_function()
    {
        // Next line causes memory leak - comment out to compare to normal behavior
        $users = User::where('id', '=', 2)->first();

        unset($users);
        // User goes out of scope at the end of this function
    }
}

And the output of this script (executed by 'php artisan command:memoryleak') looks something like this:

int(9298696)
int(9299816)
int(9300936)
int(9302048)
int(9303224)
int(9304368)
....
int(10927344)
int(10928432)
int(10929560)
int(10930664)
int(10931752)
int(10932832)
int(10933936)
int(10935072)
int(10936184)
int(10937320)
....
int(12181872)
int(12182992)
int(12184080)
int(12185192)
int(12186312)
int(12187424)
PHP Fatal error:  Allowed memory size of 12582912 bytes exhausted (tried to allocate 89 bytes) in /Volumes/Mac OS/www/test/vendor/laravel/framework/src/Illuminate/Database/Connection.php on line 275

If I comment out the line "$users = User::where('id', '=', 2)->first();" then the memory usage stays stable.

Does anyone have any insight as to why this line would use memory like this, or know a smarter way to accomplish what I am trying to do?

Thank you for your time.

Chronometer

Answer 1

I recreated your script and stepped through it with a debugger because I couldn't fathom what sort of horrible thing would cause this type of memory issue. As I stepped through, I came across this:

// in Illuminate\Database\Connection
$this->queryLog[] = compact('query', 'bindings', 'time');

It seems every query you run in Laravel is stored in a persistent log, which explains your increasing memory usage after each query. Just above that, is the following line:

if ( ! $this->loggingQueries) return;

A little more digging determined that the loggingQueries property is set to true by default, and can be changed via the disableQueryLog method, so that means, if you call:

 DB::connection()->disableQueryLog();

before you're going to execute all your queries, you won't see ever increasing memory usage; it solved the problem when I ran my test based on your example code. When you're done, if you don't want to affect the rest of the application you could call

DB::connection()->enableQueryLog();

to renable logging.

Nae

Can you post memory usage with query logs off? This isn't the result of not freeing mysql results, right? – Heeheebiejeebies

@Heeheebiejeebies : No, running the code as the original poster had it, plus disabling logging, prevents the ever-increasing-memory use issue. Laravel is freeing the mySQL results. – Nae

As a shortcut, you could also do DB::disableQueryLog() directly instead of retrieving the connection object. – Phocis

I want to append this comment for anyone who stumbles upon this (like me). Currently working with Laravel 8, in which the query log is disabled by default, yet I was still getting increased memory usage. This is because it also (always) dispatches an event, and although I was able to narrow it down to the loop of listeners in the Dispatcher::dispatch method, I wasn't able to find a nice way of fixing it, so instead I'm using DB::setEventDispatcher with a class overriding the default Dispatcher::dispatch and basically doing nothing... – Cleasta

Answer 2

I can't say why it isn't releasing memory. Your best bet is to follow the code and learn how it does what it does for that one. Or ask Taylor.

As for other things you can do:

Cache the query If you're calling the same query over and over and over, then use the query cache. It's as simple as adding ->remember($time_to_cache) to your query.

Make the DBMS do all the hard work. Ideally, you'd just do an insert into select statement, but that gets hairy when you're crossing databases. In lieu of that, batch both the select and the insert queries so that you're making fewer calls to the databases and creating fewer objects. This offloads more of the heavy lifting to the database management system, which is arguably more efficient at these types of tasks.

Kornegay

this looks like an example to show a behavior - nobody is going to select the same row based on a hardcoded ID 10,000 times – Nae

@Nae - I can only go by what he's posting, since he hasn't posted what he's actually looking to do, in the same detail as the example code that causes a memory leak. He said he's looking to copy data from one DB to another, which is what I answered for. He also mentioned that he needs to check if a user actually exists, but without any other information, it's entirely possible that the same user ID is referenced multiple times (as is typical of 1:N relationships), in which cases, my answer is entirely relevant. – Kornegay