Incident Library

IL-0211 Mailchimp · 2019-02-04database

A Mandrill PostgreSQL shard shut down at transaction ID wraparound and email sending was impaired for about 40 hours

Every write in PostgreSQL gets a transaction ID (XID) from a 32-bit counter that is compared modulo 2^32, so old XIDs must be cleared by the autovacuum process before about 2 billion newer transactions make them wrap around and look like the future. Mandrill keeps its data in a set of PostgreSQL databases that act as one key-value store sharded by key. The hashing algorithm favoured shard4, which took a higher write load than the other shards. In November 2018 Mandrill engineers had seen XIDs climb to about half the limit at peak load, judged that wraparound was not an immediate threat and put extra monitoring on the backlog. On shard4 autovacuum likely fell behind under the higher load, or it may have failed; Mailchimp could not tell which. At 05:35 UTC on 4 February the XID reached the limit and the database went into safety shutdown mode.

The first exception was lost among unrelated exceptions, and engineers started investigating at 11:58 UTC, when retries of failed writes had already raised the load on shard4. The wraparound was found at 12:56 UTC and a VACUUM in standalone mode was started at 14:16 UTC. Its runtime was estimated in days or weeks, so a dump and restore without the large Search and Url tables was started alongside it, also too slowly. Failed jobs queued on the app servers' disks, so storage volumes were replaced across the app servers, and the job-queue tables were moved from shard4 to other shards, which helped little.

At 16:30 UTC on 5 February responders met to look at more radical options. Having established that the app could run without the Search and Url tables, and verified that dropping them would free their XIDs, the team truncated the tables on the locked database at 19:12 UTC. The vacuum finished about an hour later, sending from the backlog began at 21:36 UTC and the outage was announced as resolved at 22:09 UTC. Alerting for XID wraparound was in place by 22:40 UTC, and by 01:00 UTC on 6 February the queues were drained. Mailchimp refunded affected users for Mandrill purchases made between 1 January and 13 February 2019 and credited Mandrill blocks for February.

Severity
–
Scope
Mandrill email sending: only 80% of queued emails were sent from 05:35 UTC on 4 February until 22:09 UTC on 5 February 2019
Users affected
Mandrill users; affected users were refunded for purchases made between 1 January and 13 February 2019
Region
–
Time to detect
6h 23m
Time to resolve
40h 34m

Symptoms

From 05:35 UTC on 4 February 2019 Mandrill, Mailchimp's transactional email service, sent only 80% of queued emails, because one of its PostgreSQL shards, shard4, stopped accepting writes. When Mandrill engineers opened an investigation at 11:58 UTC, they saw a sharply elevated load on shard4.

The first exception had been logged overnight, but unrelated exceptions from other systems buried it in the centralized error reporting. The rising load on shard4 came from retries of the failed writes, and while responders looked for its source, they found at 12:56 UTC that shard4 had reached the upper limit of its transaction IDs (XID) and gone into safety shutdown mode.

The failure spread to the Mandrill app servers. Jobs that had to write rows to shard4 failed and were queued on the app servers' local disks, and those jobs and the logs of their failures filled the disks until every app server was low on space. Logs of malformed API requests were hard to tell apart from real application errors, job health was hard to see, and the low disk space made partial code deployments for triage risky.

The standard recovery was too slow. VACUUM in standalone mode, started at 14:16 UTC, was estimated to need many days even after it was restarted with settings that sped it up, with a highest estimate of 40 days. A dump and restore of the database without the Search and Url tables, which were terabytes in size while most tables were megabytes, was faster but still far too slow. Moving the job-queue tables kept on shard4 to other shards helped little, because those jobs still had to write other data to shard4. Mandrill deletes queued mail older than 48 hours by default, so time was running out for the backlog.

Symptom Writes rejectedThroughput degradationLoad imbalanceNode disk full

Root cause

Root cause Capacity exhaustion

Component DatabasePostgreSQL vacuum
Contributing factor Ignored early signalNo alert on limitSignal buried in noiseRetry stormMonitoring blind spot
Trigger
At 05:35 UTC on 4 February the transaction ID counter of shard4, the busiest Mandrill PostgreSQL shard, reached its upper limit and PostgreSQL shut the database down to protect against wraparound.

Detection

The first exception was logged overnight but was buried by unrelated exceptions in the centralized error reporting. Mandrill engineers opened an investigation at 11:58 UTC and, while looking for the source of the rising load on shard4, found the wraparound at 12:56 UTC.

Remediation

Action items

–

Lessons

Mailchimp named the factors that slowed its response: knowledge of and access to Mandrill systems and monitoring rested with a few people, Mandrill logging needed a locally writable disk, logs of malformed API requests were hard to separate from real application errors, job health was hard to see, and the disk space problem made partial deployments for triage risky. What helped was trained incident commanders with clear roles (incident commanders in 3-hour rotations, scribes, technical liaisons and points of contact between teams), periodic summaries with a list of recent and open decisions, and taking time in a video meeting to reassess, which led to the truncation. Running in the cloud let the team expand storage quickly, and the team was lucky that the largest table holding up the vacuum held data it could delete.

Sources

Related cases

Check your cluster for this

kubegap audits a Kubernetes cluster for conditions like the ones in this case, Database, PostgreSQL vacuum among them, read-only, from Claude Code or Codex. Its checks cite public reports like this one.

Open kubegap

For AI tools and scripts

The API needs no key. The MCP server offers search_incidents, get_incident, list_incidents, get_topic and vocab; both are explained on API and MCP.