{"id":"IL-0571","org":"STORES","date":"2024-07-01","docs":[],"title":{"en":"STORES reservation returned bursts of 504 errors when a smart-list query held a database process for over 20 minutes","ko":"STORES 予約의 스마트리스트 검색 쿼리가 특정 테넌트에서 20분 넘게 DB 프로세스를 붙잡아 504 에러가 급증했다"},"domain":["database"],"impact":{"scope":"Requests to STORES 予約 returned 504 errors (Gateway Timeout) while the database was stuck on long queries","users":"unknown","region":"unknown"},"review":{"written_by":"session","reviewed_at":null,"reviewed_by":null},"lessons":{"en":"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.","ko":"실행 계획을 보고 실제 데이터로 동작을 확인해도, 멀티테넌트 환경에서는 테넌트 인덱스만으로 크게 좁혀지는 것처럼 보여 실제로 훑는 양을 놓칠 수 있다. 검증은 양극단만이 아니라 일부만 느린 경우까지 봐야 한다. 작업 중 모니터링하는 대상이 실제 부하가 걸리는 곳인지도 확인해야 한다. 일정을 너무 서두르면 이런 부분이 빠지기 쉽고, 결국 일정이 늦어지고 사용자에게 피해를 준다."},"related":[],"sources":[{"url":"https://product.st.inc/entry/2024/07/01/120000","kind":"blog","archived":"https://web.archive.org/web/20260216041156/https://product.st.inc/entry/2024/07/01/120000"}],"summary":{"en":"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.\n\nThree 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.\n\nEXPLAIN 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.","ko":"STORES 予約의 스마트리스트는 고객 검색 조건을 저장해 두고 고객 목록에서 조건에 맞는 고객에게 라벨을 붙이는 기능이다. 팀은 고객 목록을 검색할 때마다, 화면에 보이는 최대 50명만 대상으로 스마트리스트마다 DB를 검색해 라벨을 정하도록 만들었다. 출시 2주 전 QA에서 팀은 예약 페이지별 예약 횟수와 최종 예약일 검색이 의도와 다르게 동작한다는 것을 알았다. 그래서 고객과 예약 페이지별 예약 횟수와 최종 예약일을 모으는 인라인 뷰로 검색을 다시 만들었다. 고객 검색에는 옵티마이저 힌트 MAX_EXECUTION_TIME(1000) 을 넣었고, 검증 환경에서 EXPLAIN과 타임아웃을 확인한 뒤 운영 환경에 배포했다.\n\n그 뒤 사양 변경에 맞춰 미리 만들어 둔 프리셋 데이터의 검색 조건을 고쳐 쓰자 프리셋이 새 검색 쿼리를 타게 됐다. 놓친 것은 세 가지였다. 스마트리스트 대상 고객을 찾는 쿼리에는 MAX_EXECUTION_TIME 힌트를 넣지 않았다. pluck 결과로 ID만 넘겨 힌트가 이어지지 않았기 때문이다. 바깥 쿼리는 고객 ID 50개로 좁혔지만 인라인 뷰 안쪽은 좁히지 않아 테넌트의 모든 고객을 훑었다. 프리셋 이름은 그대로라 엔지니어는 큰 변경이라고 생각하지 않았다. 대부분의 테넌트는 몇 초 안에 응답했지만, 특정 테넌트에서는 쿼리 하나가 20분 넘게 DB 프로세스를 붙잡아 서버가 막혔다.\n\nEXPLAIN에서는 인덱스가 쓰이고 수만 분의 1로 좁혀지는 것처럼 보였다. 그러나 멀티테넌트 환경에서는 테넌트가 1만 개만 돼도 테넌트 ID 인덱스만으로 1만 분의 1로 좁혀지므로, 크게 좁혀진 것처럼 보여도 실제로는 상당한 행을 훑고 있었다. 동작 확인은 두 검색이 모두 통과하는 경우와 모두 실패하는 경우만 봤고, 첫 검색은 통과하고 스마트리스트 검색만 오래 걸리는 경우는 보지 않았다. 모니터링은 라이터 인스턴스만 봤는데 문제의 쿼리는 리더 인스턴스로 갔다. 막힌 요청은 지표를 보내기 전에 프로세스가 중지돼 원인을 찾는 데 시간이 걸렸다. 팀은 이후 DB 설계부터 다시 봤다. 스마트리스트 출시는 5월로 미뤄져 5월 6일에 나왔다."},"trigger":{"en":"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.","ko":"검색 사양 변경에 맞춰 스마트리스트 프리셋 데이터인 初回予約の顧客 와 要フォロー顧客 의 검색 조건을 새 조건으로 고쳐 쓰는 배치를 실행했다. 프리셋 이름은 그대로였지만 검색 파라미터가 달라져 새 검색 쿼리를 타게 됐다."},"severity":"unknown","symptoms":["high-error-rate","database-overloaded"],"timeline":{"detected_after":null,"resolved_after":null},"date_kind":"published","detection":{"en":"An alert on a sudden increase in 504 errors (Gateway Timeout), about 20 minutes after the preset rewrite batch started.","ko":"고쳐 쓰기 배치를 시작하고 20분쯤 지나 504 에러가 늘었다는 알림으로 알았다. 작업 중 보던 라이터 인스턴스 지표에는 변화가 없었다."},"components":["database"],"root_cause":"deploy-rollout","remediation":[],"action_items":[{"text":{"en":"Rework the database design so the inline-view search logic is no longer used","ko":"DB 설계부터 다시 봤고, 이번 글의 인라인 뷰 검색 로직은 더 이상 쓰지 않는다."}},{"text":{"en":"Add a test that checks this API's EXPLAIN output for scans larger than expected","ko":"이 API의 EXPLAIN을 확인해 훑는 행 수가 예상보다 많지 않은지 검사하는 테스트를 더했다."}},{"text":{"en":"Provide an environment where the timing of this logic can be measured easily","ko":"해당 로직의 실제 실행 시간을 재기 쉬운 환경을 갖췄다."}}],"contributing":["inefficient-query","delayed-effect-change","staging-not-representative","monitoring-blind-spot","assumption-unverified"],"recurrence_of":null,"symptoms_text":{"en":"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.\n\nEngineers 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.\n\nTwo 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.","ko":"스마트리스트 프리셋 데이터를 고쳐 쓰는 배치를 20분쯤 돌린 시점에 504 Gateway Timeout 에러가 갑자기 늘었다는 알림이 왔다. 작업은 03:00 UTC 조금 넘어 시작했다.\n\n작업 중에는 Web 서버 응답 지연과 쓰기용 DB의 부하 같은 지표를 보고 있었는데 큰 변화가 없었다. 문제의 쿼리는 리더 인스턴스로 갔기 때문에 모니터링하던 라이터 인스턴스의 부하는 전혀 오르지 않았다.\n\n엔지니어들은 원인을 찾지 못한 채 직전 변경을 되돌렸지만 504 에러는 그치지 않았다. DB 부하가 높아 보여 프리셋 고쳐 쓰기 배치도 멈췄지만 역시 그치지 않았다.\n\n막힌 요청의 지표도 볼 수 없었다. Rails의 DB 접근 지표는 요청 처리가 끝날 때 한꺼번에 보내는데, DB 응답을 20분 기다리던 프로세스가 멈춘 것으로 판정돼 지표를 보내기 전에 중지됐다. 이 때문에 원인을 찾는 데 시간이 더 걸렸다."},"updated_at":"2026-11-14T12:34:21.861175Z","url":"https://benchlet.com/incidentlib/app/cases/IL-0571","org_slug":"stores"}