A long-running CLI import script dies on memory_limit. Diagnose the cause.
A nightly import dies partway through the run with "Allowed memory size exhausted".
Constraints: the source table has 5M rows, the box has 2 GB of RAM and memory_limit is 1G. The same script finishes fine against the 10k-row staging dataset. The printed usage climbs monotonically and never drops back.
$stmt = $pdo->query('SELECT * FROM orders');
$rows = $stmt->fetchAll();
$processed = [];
foreach ($rows as $i => $row) {
$entity = $hydrator->hydrate($row);
$repo->save($entity);
$processed[] = $entity; // kept for the summary printed at the end
if ($i % 1000 === 0) {
echo round(memory_get_usage(true) / 1048576), " MB\n";
}
}
// 214 MB ... 447 MB ... 806 MB ... Fatal error: Allowed memory size exhaustedDiagnose the cause.
This is retention, not a leak. fetchAll() materialises all 5M rows before the loop starts, and $processed[] keeps a live reference to every entity, so refcounts never reach zero and nothing can be freed. Stream instead: fetch() or a generator, and stop accumulating.
- ✗Calling
gc_collect_cycles()— it cannot free anything the script still holds a reference to - ✗Diagnosing a leak when the script is simply retaining the result set and every entity
- ✗Raising
memory_limitinstead of streaming, which only postpones the same fatal error
- →Why does
fetchAll()peak the memory before the first loop iteration even runs? - →How does an unbuffered query in the database extension
PDOchange what PHP holds in memory?
fetchAll() is not "read row by row". It materialises the entire result set into a PHP array before the loop even begins: all 5M rows are resident at once. Then $processed[] = $entity; keeps a live reference to every hydrated entity, so its refcount never drops to zero and the engine has nothing it is allowed to free.
This is retention, not a leak. gc_collect_cycles() cannot help: the cycle collector reclaims only unreachable cycles, and both $rows and $processed are reachable.
The fix is to stream instead of accumulate: an unbuffered fetch() loop (or a generator wrapping it), no accumulator, and no reference to a row surviving the iteration.
$pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);
function streamOrders(PDO $pdo): Generator {
$stmt = $pdo->query('SELECT * FROM orders');
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
yield $row; // one row lives for exactly one iteration
}
}
$summary = new Summary(); // an aggregate, not a list of entities
foreach (streamOrders($pdo) as $row) {
$entity = $hydrator->hydrate($row);
$repo->save($entity);
$summary->add($entity->id); // accumulate the result, not the objects
unset($entity);
}Build the report into an aggregate (counters, sums, ids) rather than a list of objects. In a long-running worker also disable the framework's query log — it retains every statement it executes and leaks in exactly the same way.
