Why Our PHP 8.2 Queue Workers Leaked Memory Through PDO

We run PHP 8.2.15 CLI workers on Debian 12, consuming from a MySQL 8.0 queue table via long-lived processes managed by Supervisor. Each worker handles roughly 40k jobs per day, and we cap them at 400MB before recycling. Over three weeks, steady-state RSS crept from 90MB to 380MB, forcing a restart every six hours. The obvious suspects—unbounded arrays, leaked closures—were all clean.

I reproduced it locally by running a tight loop against a real table:

$pdo = new PDO('mysql:host=127.0.0.1;dbname=jobs', 'app', 'secret', [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_EMULATE_PREPARES => false,
]);
for ($i = 0; $i < 200000; $i++) {
    $stmt = $pdo->prepare('SELECT id, payload FROM jobs WHERE status = ? LIMIT 10');
    $stmt->execute(['pending']);
    $stmt->fetchAll(PDO::FETCH_ASSOC);
    $stmt->closeCursor();
}

Memory climbed in lockstep with iterations, and gc_collect_cycles() did nothing. The fix was not a code change but a connection reset: we wrapped each job batch in a reconnect when the worker had processed more than 5000 jobs or had been alive for 10 minutes. After that, RSS flatlined at 110MB and we removed the six-hour recycle.

The mechanism matters. With native prepared statements (EMULATE_PREPARES => false), MySQL allocates server-side prepared statement resources per connection. PDO caches statement handles internally, and while closeCursor() frees the result set, the prepared statement itself stays bound for the connection lifetime. On a fast loop this is invisible, but on a worker that runs for days it becomes a slow leak of both client-side statement objects and server-side memory. Reconnecting drops all of it in one go.

The trade-off is real: reconnecting costs a TCP handshake and auth round trip, roughly 2–5ms on a local network. For 5000 jobs that is noise. But if your jobs are already sub-millisecond, reconnecting every 5000 iterations may cost more than it saves; measure before copying this.

This will fail if you rely on session state or temporary tables per connection—a reconnect discards them. We hit that when a worker used CREATE TEMPORARY TABLE for batching; the table vanished mid-batch. Also, if you use persistent connections (PDO::ATTR_PERSISTENT => true), this fix does not work because PDO reuses the same underlying connection and the statement cache survives your new PDO() call. Our workers do not use persistent connections, deliberately, but I have seen teams add them for speed and then wonder why memory still grows.

One more edge: closeCursor() on a statement that returned no rows is a no-op, so do not use it as a leak detector. I spent an afternoon on that.

What is your threshold for recycling long-running CLI workers—time-based, job-count-based, or RSS-based—and how do you avoid killing a worker mid-transaction?