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.

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.

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.