Lazy collections and the report that would not fit in memory

The monthly export job died on the fourth of every month with a memory exhaustion error, and the fix each time was to raise the limit. It had been raised to 1,024 megabytes and the pattern was obvious to everybody: the dataset grows, the limit follows, and at some point the machine runs out.

The symptom

$ php artisan export:monthly 2019-05
PHP Fatal error:  Allowed memory size of 1073741824 bytes exhausted
(tried to allocate 262144 bytes) in /app/vendor/.../Collection.php on line 1204

$ php -r 'echo ini_get("memory_limit");'
1024M

$ mysql -Nse 'SELECT COUNT(*) FROM orders WHERE placed_at >= "2019-05-01"'
412088

Four hundred thousand orders hydrated into Eloquent models, each with a relation loaded, held simultaneously so that a map could run over them. The peak is proportional to the month, so the failure date moves earlier every year and the limit has to move with it.

Why it happens

$rows = Order::with('lines')
    ->whereBetween('placed_at', [$from, $to])
    ->get()                                   // every row, now
    ->map(function (Order $order) {            // a second array, also now
        return $this->toExportRow($order);
    })
    ->filter(function (array $row) {
        return $row['total'] > 0;
    });                                        // and a third

A collection is a wrapper around an array, and every method returns a new collection — which means a chain of three operations holds three copies of a four-hundred-thousand-element structure at its peak. The models are the expensive part: an Eloquent model with attributes, original attributes, relations and a connection reference is a few kilobytes each, and four hundred thousand of them is most of a gigabyte before any transformation.

The fix

A generator behind the collection API

$rows = Order::with('lines')
    ->whereBetween('placed_at', [$from, $to])
    ->cursor()                                 // a LazyCollection
    ->map(function (Order $order) {
        return $this->toExportRow($order);
    })
    ->filter(function (array $row) {
        return $row['total'] > 0;
    });

foreach ($rows as $row) {                      // nothing has run until here
    fputcsv($handle, $row);
}

The API is identical and the execution model is not: map and filter on a lazy collection return another lazy collection describing the operation rather than performing it, and nothing happens until the foreach pulls the first element. Each row is then hydrated, mapped, filtered and written before the next one is read.

Peak memory becomes the size of one row plus the driver’s buffer rather than the size of the result set, which is a constant rather than a function of the month. The job that needed a gigabyte finished in 38 megabytes.

cursor() versus chunkById(), and the one that is wrong when you write

// cursor: one query, one row at a time, result set held open
foreach (Order::where('exported', false)->cursor() as $order) {
    $order->markExported();       // MODIFIES the rows being iterated
}

// chunkById: repeated queries with a WHERE id > ?, safe to modify
Order::where('exported', false)->chunkById(1000, function ($orders) {
    foreach ($orders as $order) {
        $order->markExported();
    }
});

// chunk() — NOT chunkById — uses OFFSET, and modifying rows so they
// no longer match the WHERE shifts everything and skips records.

That last comment is the trap and it is silent: plain chunk paginates with OFFSET, so a loop that updates rows out of the result set causes the next page to start past records it never saw. On a job marking rows as exported it skips roughly half of them, and the count looks plausible.

cursor holds the result set open on the connection for the duration of the loop, which matters when the body is slow — a two-hour export holds a connection for two hours, and on a server with a modest connection limit that is a resource nobody accounted for. For a read-only export it is the right tool; for anything that writes, chunkById is safer and does more queries.

The methods that cannot be lazy

$lazy = Order::cursor();

$lazy->filter(...)->map(...);     // lazy — nothing runs
$lazy->take(100);                 // lazy — stops the source early

$lazy->sort();                    // NOT lazy: needs every element
$lazy->count();                   // consumes the whole thing
$lazy->groupBy('customer_id');    // materialises
$lazy->reverse();                 // materialises

// and the one that catches people: a lazy collection can only be
// walked ONCE. a second foreach yields nothing.

Sorting requires seeing every element before emitting the first, so it cannot be lazy by any implementation — the operation is inherently eager and the collection quietly becomes an array again. If the export needs to be sorted, the sort belongs in the ORDER BY where the database can use an index.

The single-traversal limit is the other thing to internalise, and it produces a confusing bug: a function that counts the rows and then iterates them gets a correct count and an empty loop. Anything needing both wants either two queries or an array.

Where the memory actually goes

Switching to a cursor and still running out of memory usually means the models are being retained by something the loop does not own.

// the query log, which is on by default in some setups and holds
// every query and its bindings for the life of the process
DB::connection()->disableQueryLog();

// events, which a job dispatching per row will accumulate if
// something is collecting them
Order::withoutEvents(function () { /* ... */ });

// and the measurement that finds it
foreach ($rows as $i => $row) {
    if ($i % 10000 === 0) {
        Log::info('export.progress', [
            'row'  => $i,
            'peak' => memory_get_peak_usage(true),
        ]);
    }
}

The query log is the usual culprit on a long-running command and it is on by default when debugging is enabled — four hundred thousand queries with bindings is a substantial array that nothing releases. Logging peak memory every ten thousand rows turns “it runs out of memory” into a graph with a slope, and a flat slope means the loop is fine and something else is growing.

Verifying it worked

$ /usr/bin/time -v php artisan export:monthly 2019-05 2>&1 | grep -E 'Maximum resident|Elapsed'
  Elapsed (wall clock) time: 4:12.88
  Maximum resident set size (kbytes): 38204

# before: 1,048,576 kB and a fatal on the fourth

$ wc -l storage/exports/2019-05.csv
398214 storage/exports/2019-05.csv

$ diff <(sort old-2019-04.csv) <(sort new-2019-04.csv) | wc -l
0

Diffing the output of the old and new implementations against a month that both could handle is the assertion that matters — the memory number proves it fits and says nothing about correctness. Sorting both first removes any ordering difference introduced by the cursor, which is a legitimate difference and not one anybody wants to debug at the same time.

Wall clock went up slightly, from 3:48 to 4:12, which is the expected trade: a cursor does more round trips to the driver than one large fetch. That is the right direction for a job whose alternative was not completing.

What this costs

A lazy collection can only be walked once, and nothing about the type says so at the call site. A function accepting a Collection and iterating it twice works; the same function given a LazyCollection silently processes nothing on the second pass. Type-hinting Enumerable and documenting the single-traversal expectation is the best available mitigation, and it is weaker than a compiler would be.

The deeper cost is that the identical API hides a genuinely different execution model, which is the same criticism that applies to any lazy sequence. A chain that looks like six operations over an array is one pass with six transformations, and reasoning about when a query actually runs requires knowing which methods are terminal. That knowledge is not in the code and has to be in the reader — which is worth the trade for a job that otherwise cannot run, and is not worth it for a controller returning twenty rows.