OOM with Laravel Excel

One of the nice things about PHP is you do not have to manage your process’s memory … except when you do.

We have a job that runs once an hour and dumps a csv generated by Laravel Excel to a customer’s SFTP. Because it uses information for multiple models, we used map(). Easy-peasy. Except that we also don’t have a 1:1 model-found-via-query()-to-row-in-file output. In some cases its 1:1000 or more. Further complicating things is that we eager load a bunch of stuff to be a little more gentle on the database.

Here’s the stripped down version of the class’s map().

<pre class="wp-block-code">```
public function map($contractor): array
{
    $contractor->load([
        'employees' => function ($query) {
            // some conditions and selecting only fields we need
        },
        'employees.user:id,uuid,first_name,last_name',
    ]);

    $employees = $contractor->employees->sortBy('user.last_name', SORT_NATURAL | SORT_FLAG_CASE);

    $rows = [];

    foreach ($employees as $employee) {
        $employee->load([
            'positions' => function ($query) {
                // some conditions and selecting only fields we need
            },
            'positions.requirements' => function ($query) {
                // some conditions and selecting only fields we need
            },
        ]);

        // a bunch of secret sauce logic

        $rows[] = [
            // fields we want
        ];
    }

    return rows;
}

This all worked fine until two weeks ago when the job started to run out of memory. Yay for hitting unknown performance cliffs.

```
[2023-01-01 03:33:14] production.ERROR: Allowed memory size of 2147483648 bytes exhausted (tried to allocate 4096 bytes) {"exception":"[object] (Symfony\\Component\\ErrorHandler\\Error\\FatalError(code: 0): Allowed memory size of 2147483648 bytes exhausted (tried to allocate 4096 bytes) at /path/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Cell/Coordinate.php:34)
[stacktrace]
#0 {main}
"}
```
```

At first I started to refactor the whole thing to use the new approach we have for the secret sauce section of the export, but then I started to actually process what the error was saying.

We can usually ignore memory usage when working with PHP because the processes are short-lived and the memory is returned at the end of it. But this export was run as a not-fast job (~ 6 minutes) and so suddenly we have to worry about such things. In this case, all the eager loaded models just kept stacking up on each other beyond their usefulness.

Thankfully, the `Model` class has this which is essentially the opposite of `load()`

```
```
    /**
     * Unset an attribute on the model.
     *
     * @param  string  $key
     * @return void
     */
    public function __unset($key)
    {
        $this->offsetUnset($key);
    }
```
```

Armed with this, I was able to get the job running again and make it more memory efficient at the same time by explicitly freeing the memory that was no longer needed.

```
```
public function map($contractor): array
{
    $contractor->load([
        'employees' => function ($query) {
            // some conditions and selecting only fields we need
        },
        'employees.user:id,uuid,first_name,last_name',
    ]);

    $employees = $contractor->employees->sortBy('user.last_name', SORT_NATURAL | SORT_FLAG_CASE);

    $rows = [];

    foreach ($employees as $employee) {
        $employee->load([
            'positions' => function ($query) {
                // some conditions and selecting only fields we need
            },
            'positions.requirements' => function ($query) {
                // some conditions and selecting only fields we need
            },
        ]);

        // a bunch of secret sauce logic

        $rows[] = [
            // fields we want
        ];

        // free the eager-loaded memory for this employee
        unset($employee->positions);
    }

    // free the eager-loaded memory for this contractor
    unset($contractor->employees);

    return rows;
}
```
```

TLDR; when eager loading in a long running process (especially when eager loading in nested loops), be sure to clean up after yourself as you go along.