The queue table that became a bottleneck at four million rows

The queue had been a database table since 2018 and had been the right choice for four years — one fewer component, transactional dispatch, and a job list anybody could query. In June it started producing lock wait timeouts under normal load, and the table had four million rows in it of which about nine hundred were actually pending.

The symptom

$ tail -3 /var/log/app/worker.log
SQLSTATE[HY000]: Lock wait timeout exceeded; try restarting
transaction (SQL: select * from `jobs` where `queue` = ?
and `reserved_at` is null order by `id` asc limit 1 for update
skip locked)

$ mysql -Nse 'SELECT COUNT(*) FROM jobs'
4102884

$ mysql -Nse 'SELECT COUNT(*) FROM jobs WHERE reserved_at IS NULL'
914

# 4.1 million rows. 914 of them are work.

Four million rows in a table whose useful contents is nine hundred is the whole problem, and it took four years to arrive because the growth was invisible — nobody looks at a queue table when the queue is draining.

Why it happens

A job is inserted, reserved, executed and deleted, which should leave the table small. Three things prevent that: a job that failed and was released keeps its row and its attempt count, a worker killed mid-job leaves the row reserved forever, and a job whose handler throws before the framework can delete it stays.

None of those is a bug and each leaves a row. Over four years at a few hundred a day the residue is millions, and the index that serves the polling query has to walk past all of them.

The fix

What the polling query actually does

mysql> EXPLAIN SELECT * FROM jobs
    -> WHERE queue = 'default' AND reserved_at IS NULL
    -> ORDER BY id ASC LIMIT 1 FOR UPDATE SKIP LOCKED;
| type | key            | rows    | Extra                      |
| ref  | jobs_queue_idx | 4102884 | Using where; Using filesort |

-- the index is on (queue), so every row on that queue is a
-- candidate and reserved_at is filtered afterwards.

ALTER TABLE jobs ADD INDEX idx_poll (queue, reserved_at, id);
| type  | key      | rows | Extra       |
| range | idx_poll |  914 | Using index |

The composite index turns four million examined rows into nine hundred and is a one-line migration, which is the cheap fix and does not solve the growth. It bought about three months and was worth doing immediately.

The rows that never clear

-- reserved and never released: a worker died holding it
SELECT COUNT(*), MIN(FROM_UNIXTIME(reserved_at))
FROM jobs WHERE reserved_at IS NOT NULL;
-- | 8104 | 2021-11-02 |

-- the framework releases these on a timer, disabled in
-- 2020 to stop a duplicate-execution bug with a different
-- cause entirely.
UPDATE jobs SET reserved_at = NULL, attempts = attempts + 1
WHERE reserved_at < UNIX_TIMESTAMP() - 90;

The disabled release timer is the specific cause here and is the kind of thing that survives because the reason it was disabled is in a commit message from 2020. The retry-after window must exceed the longest job or a running job is released and picked up by a second worker, which is the duplicate execution that caused it to be disabled in the first place.

Deleting the history

-- 4.1 million rows, deleted in one statement, is a
-- multi-hour lock and a binlog the replicas cannot keep up
-- with. so: in chunks, with a pause.

DELETE FROM jobs
WHERE reserved_at IS NOT NULL
  AND reserved_at < UNIX_TIMESTAMP() - 2592000
LIMIT 1000;

-- repeated, with a sleep, until it affects 0 rows.

-- and the space, which is not returned:
SELECT data_free/1048576 AS free_mb FROM information_schema.tables
WHERE table_name = 'jobs';
-- 3,812

Chunked deletes with a pause are the only safe shape on a table this size, and the reclaimed space stays inside the file — three point eight gigabytes of it. That is a rebuild to recover, which on a queue table is easier than most because the table can be truncated during a maintenance window once the queue is drained.

Moving to Redis, and what is lost

what the database queue gave, and Redis does not:

  transactional dispatch  a job enqueued in the same
                          transaction as the write that
                          caused it — this is the big one
  durability              only if persistence is configured
  queryability            "what is pending" was a SELECT

what Redis gives:
  an O(1) poll rather than an index range scan
  no residue: a completed job leaves nothing
  a blocking pop, so no polling interval at all

Transactional dispatch is the loss that matters and it is the reason to keep the outbox pattern rather than to abandon it — a job dispatched from inside a transaction that then rolls back is a job that runs against state that does not exist. Moving to Redis means that guarantee has to be rebuilt deliberately.

// the outbox, which survives the move
DB::transaction(function () use ($order) {
    $order->save();

    Outbox::create([
        'job'     => SyncOrder::class,
        'payload' => ['order_id' => $order->id],
    ]);
});

// and a relay, reading the outbox and pushing to Redis.
// at-least-once by construction, which the handlers
// already tolerated.

Keeping the database queue, deliberately

moved to Redis   interactive: resets, confirmations
                 standard: thumbnails, search indexing
                 → high volume, short-lived, at-least-once
                   is a sufficient guarantee

stayed in MySQL  scheduled: the nightly export, the
                 reconciliation
                 → a handful a day, and a SELECT during an
                   incident is worth more than the poll cost

two backends and a routing decision per job class, which
is a real cost and was the right trade.

Keeping the low-volume durable jobs in the database is the decision that gets skipped in favour of moving everything, and it is the one that preserves the property people actually valued: being able to answer “did the nightly export run” with a query rather than with a Redis command nobody remembers.

Verifying it worked

$ php artisan queue:age
  interactive   0.2s
  standard      1.4s
  scheduled     0.0s

$ mysql -Nse 'SELECT COUNT(*) FROM jobs'
41        # the scheduled queue, and that is all

$ redis-cli LLEN queues:interactive
(integer) 3

# and the assertion that the outbox still holds:
$ vendor/bin/phpunit --filter OutboxTransactional
Tests: 4 passed
#   a rolled-back transaction dispatches nothing

The rolled-back-transaction test is the one that protects the property the move put at risk, and it has to assert that nothing was pushed to Redis rather than that no job ran. Forty-one rows in a table that had four million is the visible outcome and the invisible one is that the polling query no longer scans anything.

What this costs

Two queue backends, a routing decision per job class, and a relay process between the outbox and Redis that is now on the critical path for every asynchronous effect. That relay is a new thing that can be behind, and it needs its own lag metric — which is the same metric the queue already had, measured one layer earlier.

Redis persistence is the other assumption that has to be made explicit. A queue in Redis with the default configuration loses up to a second of jobs on an unclean shutdown, which is acceptable for a thumbnail and is not for a payment capture — and the jobs that are not acceptable are exactly the ones that should have stayed in the database.