Incident Library

IL-0571 STORES · 2024-07-01 (published)database

STORES reservation returned bursts of 504 errors when a smart-list query held a database process for over 20 minutes

STORES 予約 added smart lists, saved customer search conditions that label matching customers in the customer list. For each of up to 20 smart lists, the customer list runs a database search restricted to the IDs of the up to 50 customers shown, so that at most 1,000 rows are checked. Two weeks before the planned release, QA found that the search by reservation page, reservation count and last reservation date did not behave as intended, and the search logic was rewritten to use an inline view that aggregates reservations per customer for the chosen reservation pages. To guard against unexpected load, the main customer search got the optimizer hint MAX_EXECUTION_TIME(1000). The team checked EXPLAIN, ran it with a test account with a fair amount of data, confirmed the timeout under high load in the verification environment, and released it.

Three points were missed. The per-smart-list query did not get the MAX_EXECUTION_TIME hint, because it was built from the plucked IDs and did not inherit the hint. The IN list of 50 customer IDs restricted only the outer query, so the inline view still aggregated every customer of the tenant. And rewriting the preset data, which had run without errors on the old logic for over a month, switched the presets to the new search query even though their names stayed the same. Most tenants still answered within seconds, but for certain tenants one query held a database process for more than 20 minutes, the servers got stuck and the 504 errors followed.

EXPLAIN had looked fine because in a multi-tenant database with ten thousand tenants, filtering by tenant ID alone would already narrow rows to one in ten thousand, so the index use looked efficient while many rows were still scanned, and the outer query showed the expected 50 rows. Testing had covered only the two extremes, both searches fast or both timing out, never a fast customer search with a slow smart-list search. Smart lists were released in May, later than planned, after the database design was reworked and prevention measures were added.

Severity
–
Scope
Requests to STORES 予約 returned 504 errors (Gateway Timeout) while the database was stuck on long queries
Users affected
–
Region
–
Time to detect
–
Time to resolve
–

Symptoms

About 20 minutes into a batch that rewrote the preset smart-list data of STORES 予約 (STORES reservation), an alert fired for a sudden rise in 504 errors (Gateway Timeout). The work had started a little after 03:00 UTC, and the metrics watched during it, such as web server response latency and the load of the writer database, had shown no large change.

Engineers began investigating but could not find the cause. They reverted the most recent change, and the 504 errors continued. Seeing that the database load appeared high, they also stopped the preset rewrite batch, and the 504 errors still continued.

Two things delayed finding the cause. The team had been watching the database's writer instance during the rewrite, while the slow customer search queries ran on the reader instance, so the writer's load never rose. And the Rails application sent its database access metrics only at the end of each request; the requests stuck for 20 minutes were judged hung and their processes were stopped before sending, so the metrics of exactly those requests were missing.

Symptom High error rateDatabase overload

Root cause

Root cause Faulty deployment

Component Database
Contributing factor Inefficient queryChange with a delayed effectUnrepresentative stagingMonitoring blind spotUnverified assumption
Trigger
The preset smart lists 初回予約の顧客 (first-reservation customers) and 要フォロー顧客 (customers needing follow-up), created more than a month earlier, were rewritten with new search parameters after the search specification changed, which switched them onto the new search query.

Detection

An alert on a sudden increase in 504 errors (Gateway Timeout), about 20 minutes after the preset rewrite batch started.

Remediation

–

Action items

Lessons

Rushing to meet the schedule led to both a failure that affected users and a later release. In a multi-tenant database an EXPLAIN that shows index use can still hide large scans, tests need the case where one query is fast and the next is slow, the metrics to watch during a data change are those of the instance that serves the affected queries, and metrics sent at the end of a request are lost for requests that never finish.

Sources

Related cases

Check your cluster for this

kubegap audits a Kubernetes cluster for conditions like the ones in this case, Database 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.