Production was down ~2 hours (7:30–9:30 AM ET). No data loss.
Cause: ES license expired → web workers hung → thundering herd on recovery → exposed latent N+1-style COUNT queries in the asset show endpoint that couldn't survive 130 concurrent connections. DB pinned at 100% CPU. Compounded by ~100K queued Sidekiq jobs and a Kintzing API client hitting us at 140 req/s.
Fix: Shipped 5 targeted fixes during the incident — eliminated 6+ expensive COUNT queries per asset show (upload progress counts, contribution counts, requirement checks). These now use cached counter columns or are skipped entirely for done uploads and API traffic.
Still in progress: asset_analyzer workers paused while the job backlog drains. FTP upload processing delayed until cleared. Need to add ES license monitoring, auto-tag query index, and circuit breakers for ES failures.
Mediagraph experienced a full production outage lasting approximately 2 hours (approx 11:30–13:30 UTC) on April 8, 2026. Both web servers (rat, moth) returned "This website is under heavy load (queue full)" errors to all users. The root cause was a cascading failure triggered by an expired Elasticsearch license, which hung all 130 Passenger workers across both web servers, and on recovery triggered a thundering herd that saturated PostgreSQL with concurrent expensive COUNT queries.
Why did an Elasticsearch failure crash the database?
- ES license expired → ES returned 403 on all queries
- Passenger workers hung waiting for ES responses (no timeout/circuit breaker) → all 130 workers stuck → 503 for all users
- Users refreshed repeatedly during the outage, building up a backlog of pending requests
- When we fixed ES, all backed-up requests + user refreshes hit PostgreSQL simultaneously (thundering herd)
- Each
/api/assets/:guidrequest triggered 6+ expensive COUNT queries via the upload and contribution partials — queries that work fine at normal concurrency (~20) but collapse at 130+ concurrent - PostgreSQL saturated at 100% CPU → every request took 15-20s → workers stayed busy → queue stayed full → users refreshed more → feedback loop
- Sidekiq had 100K+ queued jobs from the outage. When workers resumed, they added more slow queries on top of web traffic
The expensive queries were a latent vulnerability. Under normal load they were invisible. The ES failure created the conditions that exposed them.
| Time | Event |
|---|---|
| ~10:50 | Elasticsearch license expires, ES returns 403 on all queries |
| ~11:00 | Passenger workers on rat and moth get stuck waiting for ES responses |
| 11:30 | Both servers at 100 requests queued, returning 503s. Incident reported. |
| 11:38 | Investigation begins. Servers appear healthy (low CPU, memory, disk). |
| 11:39 | Passenger status reveals 100 requests queued on both servers, all workers stuck in WAITING_FOR_APP_OUTPUT |
| 11:40 | ES license identified as expired (security_exception: current license is non-compliant) |
| 11:40 | Fix 1: ES switched to basic license (_license/start_basic). ES goes green. |
| 11:41 | Passenger workers remain stuck on old connections. Restarted Passenger on both servers. |
| 11:43 | Workers respawn but queues fill back to 100. Sidekiq queues discovered: 98,740 jobs in processor_ftp_4, 16,445 in indexing |
| 11:44 | Sidekiq workers quieted to reduce DB load |
| 11:50 | PostgreSQL identified as new bottleneck: 139 active connections, all running slow COUNT queries |
| 11:51 | Root cause query identified from RDS Performance Insights: SELECT COUNT(*) FROM assets JOIN asset_data_versions WHERE assets.upload_id = $1 AND aasm_state = $2 — runs 5x per asset show request via upload partial |
| 12:00 | ANALYZE run on assets, uploads, asset_data_versions tables (47s for ADV — severely stale stats) |
| 12:02 | Statement timeout set on PostgreSQL role to prevent runaway queries |
| 12:05 | Composite index created concurrently: index_assets_on_upload_id_and_head_dv_not_trashed |
| 12:10 | Passenger pool reduced from 65→10 per server to limit concurrent DB queries |
| 12:15 | Code fix 1: Upload partial skips state count queries for done uploads |
| 12:20 | Index build completes. DB active connections drop significantly. |
| 12:36 | Full deploy attempted via Cloud66 — fails health check (servers still returning 503 during recovery) |
| 12:40 | Code manually deployed to both servers via cx run |
| 12:47 | Deploy restarts Sidekiq — workers resume processing 100K+ queued jobs, DB spikes again |
| 12:48 | Sidekiq quieted again |
| 12:55 | Second query identified from RDS: contribution.assets_count running expensive COUNT through uploads join |
| 13:00 | Code fix 2: Contribution partial wrapped in unless upload.done?, contribution.assets_count uses read_attribute from cached column |
| 13:05 | Code fix 3: Upload partial uses attributes.key?('pending_count') guard — counts only render when pre-loaded via with_state_counts scope |
| 13:10 | Active DB connections drop to 8. Response times: 1-2 seconds. Pool increased to 40. |
| 13:15 | Sidekiq resumed — DB spikes again within minutes |
| 13:20 | Sidekiq asset_analyzer workers paused. Sidekiq queries identified: auto_tags ICU collation lookups + upload count queries from AssetProcessorJob callbacks |
| 13:25 | Code fix 4: upload.assets_count uses read_attribute from cached column instead of live COUNT |
| 13:27 | Queries killed, Passenger restarted. Both servers: 0 queue, sub-3ms response. |
| 13:30 | Pool restored to 65. Site fully stable with asset_analyzer workers paused. |
| 13:40 | RDS spikes again. Nginx logs reveal single IP (31.221.114.194) sending 140 req/s — Kintzing org (id: 2489) Node API client doing bulk asset sync against a non-done upload |
| 13:45 | Code fix 5: Upload partial and show template skip all_requirements_met, contribution, and tag_suggester queries for PAT (API) traffic (@pat guard) |
| 13:47 | Fix deployed to both servers. RDS stabilizes. |
All of these were fine under normal concurrency but catastrophic at 130+ concurrent:
| Query | Source | Per-request | Fix |
|---|---|---|---|
SELECT COUNT(*) FROM assets JOIN asset_data_versions WHERE upload_id = ? AND aasm_state = ? |
Upload partial state counts (5x) | Every asset show | Only render when pre-loaded via with_state_counts scope |
SELECT COUNT(*) FROM assets JOIN uploads JOIN asset_data_versions WHERE contribution_id = ? AND aasm_state = ? |
contribution.assets_count |
Every asset show (non-done uploads) | Use read_attribute from cached column; skip contribution partial for done uploads |
SELECT COUNT(*) FROM assets WHERE upload_id = ? AND trashed_at IS NULL |
upload.assets_count |
Every asset show | Use read_attribute from cached counter column |
SELECT COUNT(*) FROM assets WHERE upload_id = ? AND submitted = ? |
all_requirements_met? |
Non-done uploads | Skipped for done uploads and PAT traffic |
SELECT auto_tags WHERE lower(trim(name) collate "und-x-icu") IN (?) |
AssetAutoTagJob (Sidekiq) |
Per job | Needs functional index (TODO) |
After all fixes were deployed and Sidekiq paused, RDS continued spiking. Nginx logs revealed a single API client (Kintzing org, IP 31.221.114.194, user agent node) sending 140 requests/second to /api/assets/:guid — a bulk asset sync via personal access token. Each request hit a non-done upload (counter cache = 0), triggering the all_requirements_met? COUNT queries that the upload.done? guard couldn't skip. This was the final piece: PAT traffic now skips upload requirement checks entirely since API clients don't need upload progress UI state.
- Upload partial (
_upload.json.jbuilder): State counts only render when pre-loaded viawith_state_countsscope (attributes.key?('pending_count')guard). Contribution and tag_suggesters wrapped inunless upload.done?. - Upload model (
upload.rb):assets_countusesread_attribute(:assets_count)to prefer cached counter column - Contribution model (
contribution.rb):assets_countusesread_attribute(:assets_count)to prefer cached counter column - Show template + upload partial: Skip
all_requirements_met, contribution, and tag_suggester queries for PAT (API) traffic — API clients don't need upload progress UI state - Composite index:
index_assets_on_upload_id_and_head_dv_not_trashedonassets(upload_id, head_data_version_id) WHERE trashed_at IS NULL
- ES switched to basic license (permanent fix)
- Passenger pool size adjusted multiple times (restored to 65)
- Statement timeout set/removed on PostgreSQL role (removed)
| Metric | Before fixes | After fixes |
|---|---|---|
| Active DB connections | 139 | 3-8 |
| Asset show response time | 18+ seconds | 1-3ms |
| COUNT queries per asset show | 6+ | 0 (done uploads) |
| Passenger queue | 100 (max) | 0 |
All users were affected. The site was completely unreachable for approximately 60 minutes, then intermittently available with degraded performance for another 60 minutes during iterative fixes. No data was lost or corrupted.
- Commit and properly deploy all code changes via Cloud66 (currently manually patched on servers)
- Add migration for the composite index
index_assets_on_upload_id_and_head_dv_not_trashed - Resume
asset_analyzerworkers (currently paused) — monitor RDS CPU - Monitor ES license — set up alerting for license expiration
- Add a functional index on
auto_tagsforlower(trim(name) collate "und-x-icu")— these queries are 0.8-1.9s each from Sidekiq jobs - Review
AssetProcessorJob/AssetDataVersionstate machine callbacks that trigger upload COUNT queries during processing - Ensure
ANALYZEruns regularly on large tables (assets, asset_data_versions, uploads) - Add a database statement timeout in
database.yml(e.g., 30s) as a safety net - Add monitoring/alerting for Passenger queue depth and RDS CPU
- Gradually drain the 100K+ Sidekiq backlog during off-peak hours
- Refactor upload state counts to use counter caches instead of COUNT queries
- Consider connection pooling (PgBouncer) to limit max concurrent DB connections
- Add a circuit breaker for ES connectivity so Passenger workers don't hang indefinitely
- Load test the asset show endpoint under high concurrency
- ES license expiration can cascade into a full outage — the ES failure was silent (no alerts) and caused workers to hang indefinitely, creating a thundering herd when resolved
- COUNT queries on large tables are a latent risk — they worked fine under normal load but became catastrophic under thundering herd conditions. Several models had
def method_name; relation.count; endoverriding cached counter columns. - Passenger pool size is a double-edged sword — 65 workers per server provides great throughput, but when every worker hits a slow query, it becomes 130 concurrent slow queries that saturate the DB
- Cloud66 deploys restart Sidekiq — during an incident with large job backlogs, this re-triggers DB overload
- Manual server patching works in emergencies —
cx runwith base64 pipe can deploy code changes without a full deploy cycle - RDS Performance Insights is invaluable — the SQL annotations (
controller:assets,action:show,job:AssetProcessorJob) made it possible to trace queries back to specific code paths quickly - Iterative diagnosis is necessary — fixing one slow query revealed the next one. The outage involved 5 different expensive query patterns that each had to be found and fixed.