skip to content

A PHP import script dies with "Allowed memory size exhausted" after about 200,000 rows; what usually holds the memory, and how do you fix it without raising memory_limit?

level: seniorimportance: should knowfreq 45%

answer

  1. load everything versus one row at a time
  2. file() and fetchAll() build whole arrays
  3. buffered MySQL results count with mysqlnd
  4. arrays that grow on every iteration
  5. log memory_get_usage() every N rows

basics

~20 s

The script usually holds every row at once: file() or fetchAll(), a buffered result set, or an array collecting processed records. Stream instead: read lines one by one, fetch rows singly or in keyed batches, and keep nothing per row.

solid answer

~50 s

An import that fails at a steady row count is almost always **loading** rather than **streaming**. Typical holders: `file()` or `file_get_contents()` on the whole CSV; `fetchAll()`, which turns the result into one big array; a buffered MySQL result, which with mysqlnd sits in PHP's memory and counts against `memory_limit` before you fetch a row; and arrays that grow per row — collected IDs, error lists, a query log, a data-mapper's cache of every loaded entity. The fix keeps memory flat: read with `fgetcsv()` or `SplFileObject` line by line; `fetch()` in a loop over an unbuffered query (`Pdo\Mysql::ATTR_USE_BUFFERED_QUERY => false`) or over keyset batches (`WHERE id > :last ORDER BY id LIMIT 1000`); write each batch and drop it; clear per-batch caches. I confirm by logging `memory_get_usage()` every 10 000 rows: the staircase should become a flat line.

code

php · 16 lines
php
<?php
declare(strict_types=1);

$pdo = new Pdo\Mysql($dsn, $user, $password);
$select = $pdo->prepare('SELECT id, email FROM customers WHERE id > ? ORDER BY id LIMIT 1000');

$last = 0;
do {
    $select->execute([$last]);
    $batch = $select->fetchAll(PDO::FETCH_ASSOC); // at most 1000 rows
    foreach ($batch as $row) {
        exportRow($row);
        $last = $row['id'];
    }
    error_log(sprintf('up to id %d, %.1f MB', $last, memory_get_usage() / 1048576));
} while (count($batch) === 1000);

go deeper

for a junior

Know that loading a whole file or result set into an array can exceed memory_limit, and that reading row by row avoids it.

for a middle

Name the usual holders — file(), fetchAll(), buffered results, growing arrays — and rewrite the loop with fgetcsv() and fetch() or batches.

for a senior

Diagnose with periodic memory_get_usage() logs, choose unbuffered queries or keyset batches knowing their trade-offs, and remove per-row accumulation including library caches.

for a principal

Set expectations that batch jobs run in bounded memory by design, with resumable checkpoints, so data growth never turns into an outage.

## The shape of the failure An import that dies at roughly the same row count every time, with a steadily rising memory figure before that, is holding something **per row**. It is not a leak in PHP itself; it is data the script keeps reachable. `memory_limit` (default `128M`) is simply where the growth becomes visible. ## Where the memory usually goes | Pattern | Why it grows | |---|---| | `file($path)` or `file_get_contents($path)` | the entire file becomes one array or string before processing starts | | `$stmt->fetchAll()` | every row becomes a PHP array at once | | a buffered MySQL result | with mysqlnd the whole result set is copied into PHP's memory at query time and counts against `memory_limit` | | `$done[] = $row` or `$errors[] = …` | an array that grows by one entry per row | | a debug query log or profiler collector | records every statement executed | | a data-mapper's identity cache | keeps every entity it has loaded until cleared | The PHP manual's MySQL concepts page states it directly: buffered queries are the default, keep the result in the PHP process, and with mysqlnd the memory accounted for includes the full result set. ## Streaming the input file Read one line at a time instead of the whole file: ```php $in = fopen($path, 'rb'); while (($row = fgetcsv($in, escape: '')) !== false) { importRow($row); } fclose($in); ``` `fgetcsv()` returns `false` at the end of the file, so the loop holds one row at a time no matter how large the file is. `SplFileObject` with the `READ_CSV` flag does the same in object form. ## Streaming query results Two approaches keep a large result from sitting in memory: 1. **Unbuffered query.** With PDO and MySQL, set `Pdo\Mysql::ATTR_USE_BUFFERED_QUERY` to `false` (the older `PDO::MYSQL_ATTR_USE_BUFFERED_QUERY` constant is deprecated in PHP 8.5) and `fetch()` row by row. The catch: the connection cannot run another query until the result is fully read, so writes need a second connection. 2. **Keyset batches.** Query `WHERE id > :last ORDER BY id LIMIT 1000`, process the batch, remember the last `id`, repeat. Each batch is small, buffered queries work normally, and the job can resume after a crash. Avoid growing `OFFSET` values, which make the database rescan skipped rows. ## Keeping the loop flat - **Write in batches and let them go.** Build at most one batch array, insert it, then reassign it to `[]`. - **Do not accumulate for reporting.** Count errors and keep a bounded sample instead of every failing row. - **Clear caches between batches.** If a data-mapping library keeps loaded entities, clear it after each batch; the details belong to that library. - **Turn off query logging** in the job's configuration. - **Cycles**: if row objects reference each other, `gc_collect_cycles()` after each batch frees them sooner, though usually the fix is not to keep them at all. ## Less obvious holders - **Output built in memory.** Concatenating every exported line into one string with `.=` grows without bound; write each line to the destination stream as it is produced. - **Output buffering.** In a CLI job, an output buffer left active holds everything printed until it is flushed. - **Closures and event listeners** registered per row, which keep their captured variables alive for the lifetime of the dispatcher that holds them. ## Proving the fix Log memory while the job runs: ```php if ($count % 10_000 === 0) { error_log(sprintf('%d rows, %.1f MB', $count, memory_get_usage() / 1048576)); } ``` Before the fix the figure climbs with each log line; after it, the figure should stay within a narrow band regardless of row count. Only when a job genuinely needs a large working set — sorting everything in memory, say — is raising `memory_limit` for that job the right answer, and then it should be a measured, documented number rather than `-1`.

  • Why can a buffered MySQL query exhaust memory_limit before the first fetch()?
    In buffered mode, the default, the whole result set is transferred to the PHP process when the query executes. With the mysqlnd driver that memory is allocated through PHP's manager and counts against `memory_limit`, so a huge result can fail at `execute()` time, before any row reaches your loop.
  • Why prefer keyset batches over LIMIT with a growing OFFSET?
    With `OFFSET`, the database still walks past all skipped rows on each batch, so later batches get slower. `WHERE id > :last ORDER BY id LIMIT n` jumps straight to the next page through the index, keeps each batch cheap, and gives a natural resume point.

saying these in an interview costs you the question

  • Setting memory_limit to -1 is the proper fix for any import
  • fetchAll() streams rows lazily from the server
  • Buffered MySQL results do not count against memory_limit
  • Calling unset() on the loop variable is enough when a result array holds everything
  • gc_collect_cycles() frees arrays that are still referenced