Shopify ने inventory reservation system को Redis से MySQL में बदला

• inventory reservation system checkout processing के दौरान एक ही product के दो बार बिक जाने वाली oversell स्थिति को रोकने वाला core infrastructure है, और Shopify इसे कई वर्षों से Redis-आधारित रूप में चला रहा था
• MySQL 8 के SKIP LOCKED feature का उपयोग करके, per-item quantity column के बजाय per-sale-unit 1-row structure के रूप में redesign किया गया, और Redis के बिना भी high-performance processing हासिल की गई
• composite primary key, READ COMMITTED isolation level, consistent lock order, UNION ALL batch processing जैसी MySQL optimization techniques को मिलाकर lock contention और deadlock को दूर किया गया
• वास्तविक bottleneck reservation query नहीं बल्कि connection occupancy था, और पूरे checkout path को instrument करके DB read में 50% और transaction में 33% कमी हासिल की गई
• 2025 Black Friday peak के आधार पर, प्रति मिनट $5.1 million revenue process करते हुए writer CPU को 50% से नीचे और reader CPU को 16% से नीचे बनाए रखते हुए target throughput से अधिक हासिल किया गया

---

पृष्ठभूमि: oversell prevention system की आवश्यकताएँ

• checkout पूरा होने के समय inventory वास्तव में उपलब्ध है, यह सुनिश्चित करने के लिए oversell prevention system की आवश्यकता है
  • Reserve: payment शुरू होने पर कुछ मिनटों के लिए उस item को अस्थायी रूप से lock करना
  • Claim: payment पूरा होने पर inventory ledger से quantity को स्थायी रूप से घटाना
• दोनों दिशाओं में error की कोई गुंजाइश नहीं
  • गलती होने पर एक ही product दो लोगों को बिक सकता है, या inventory मौजूद होने के बावजूद out of stock दिखाकर revenue loss हो सकता है
• scale requirement: Shopify अमेरिकी ecommerce का 14% से अधिक संभालता है, और 2025 Black Friday में पिछले वर्ष की तुलना में 11% बढ़कर प्रति मिनट $5.1 million revenue दर्ज किया
• multi-location inventory, ACID guarantee, high-performance throughput, और correctness-first approach प्रमुख आवश्यकताएँ हैं

मौजूदा Redis model की सीमाएँ

• Redis में प्रत्येक item के लिए quantity key होती है, और reservation DECR से तथा release INCR से संभाला जाता है
• मुख्य समस्या: reservation data (Redis) और inventory ledger (MySQL) दो अलग systems में मौजूद थे
  • Claim चरण में MySQL update और Redis cleanup को एक single atomic transaction में बाँधा नहीं जा सकता था
  • execution order के अनुसार oversell (product बिक गया लेकिन ledger में deduction नहीं) या undersell (ledger से deduction हो गया लेकिन reservation अभी भी बना रहा) हो सकता था
• multi-location inventory awareness की कमी, और अलग Redis cluster चलाने की operational cost भी थी

मुख्य समाधान: SKIP LOCKED आधारित MySQL redesign

मूल संरचना: प्रति unit 1 row (One Row Per Unit)

• per-item quantity column के बजाय प्रति sellable unit 1 row संरचना अपनाई गई
  • 10 inventory वाले item → 10 rows; 3 units reserve करने पर एक single transaction में 3 rows को select और move किया जाता है
• reservation और inventory ledger को एक ही MySQL DB में रखकर reserve और claim को ACID transaction के रूप में process किया गया, जिससे Redis में दिखने वाले bug patterns समाप्त हुए
• SKIP LOCKED: दूसरे transaction द्वारा locked rows को छोड़कर उपलब्ध rows तुरंत लौटाता है → उसी row के लिए wait किए बिना contention कम होता है

pool size limit: प्रति location अधिकतम 1,000 rows

• item/location combination के लिए available rows को अधिकतम 1,000 तक सीमित रखा गया, ताकि table size और scan performance नियंत्रित रहे
  • उदाहरण: 50,000 inventory × 10 locations = 500,000 rows जैसी स्थिति से बचाव
• pool समाप्त होने पर inline replenishment trigger होता है; केवल एक transaction replenish कर सके, इसके लिए lock लगाया जाता है, ताकि कई transactions एक साथ row insert करने वाली thundering herd स्थिति न बने
• यदि pool पूरी तरह खाली हो जाए, तो delay सिर्फ उसी reservation में होगा; वास्तविक inventory होने पर भी buyer को out of stock नहीं दिखाया जाएगा

4 प्रमुख तकनीकी निर्णय

1. composite primary key से lock की संख्या घटाना

• शुरुआती prototype में auto-increment ID को primary key रखने पर, InnoDB secondary index और clustered index दोनों को lock करता था, जिससे प्रति reservation 2 row locks लगते थे
• shop_id, inventory_item_id, inventory_group_id, id से बनी composite primary key लागू की गई → filter columns primary key में शामिल होने से lock घटकर 1 रह गया
• प्रति सेकंड हजारों reservations वाले environment में index और primary key design lock count और throughput को सीधे प्रभावित करते हैं

2. READ COMMITTED से gap lock हटाना

• खाली table पर SELECT ... FOR UPDATE SKIP LOCKED चलाने पर gap lock (supremum सहित) लगता था, जो replenishment transaction के INSERT को block करता और deadlock पैदा करता था
• isolation level को MySQL default REPEATABLE READ से बदलकर READ COMMITTED किया गया → gap lock behavior बदलने से replenishment transaction सामान्य रूप से चल सका
• इस codebase में यह पहला non-default isolation level था, इसलिए per-transaction isolation level set करने के लिए छोटे framework support की जरूरत पड़ी

3. consistent lock order से deadlock रोकना

• reserve और claim दो tables को अलग-अलग क्रम में access कर रहे थे, जिससे deadlock हुआ
  • reserve: reserved_quantities INSERT → reservation_units DELETE
  • claim: reserved_quantities DELETE
• समाधान: reserve हमेशा पहले units table में DELETE करे, और बाद में reserved_quantities में INSERT करे — इस तरह order standardize करके circular wait हटाया गया

4. UNION ALL batch से round trip कम करना

• cart में कई line items होने पर UNION ALL के जरिए reservation queries को single round trip में batch किया गया
• कुल round trips घटने से heavy load में latency बेहतर हुई

वास्तविक bottleneck: query नहीं, connection occupancy

समस्या कैसे मिली

• production environment में target throughput से पहले ही ceiling आ रही थी, P90 latency ठीक थी, CPU max से नीचे था, और queries भी optimize हो चुकी थीं
• load test में देखे गए लक्षण:
  • MySQL के अंदर thread queuing
  • queued work चलने पर CPU spike
  • ProxySQL layer में MySQL backend connections की कमी

connection visibility हासिल करना

• application layer: सभी SQL statements में /* conn_tag:checkout_completion */ जैसे business-process पहचान comment जोड़े गए
• ProxySQL layer: tag parsing और caller के अनुसार connection occupancy time aggregation जोड़ी गई
• परिणाम: कौन-सा process कितनी देर connection पकड़े हुए है, यह तुरंत दिखने लगा

क्या मिला और क्या सुधारा गया

• reservation के अलावा checkout path का अन्य code connections को जरूरत से ज्यादा देर तक पकड़े हुए था
  • ये वे code paths थे जो पहले limit तक नहीं पहुँच रहे थे, इसलिए optimization scope से छूट गए थे
• checkout path cleanup के बाद: primary DB reads 50% कम, transactions 33% कम
• कई साल पहले conservatively set किया गया और फिर review न किया गया InnoDB thread concurrency setting बदली गई, जिससे अतिरिक्त bottleneck भी हट गया
• सुधार के बाद high-volume flash sale में writer CPU 50% से नीचे और reader CPU 16% से नीचे बना रहा

migration तरीका: Shadow Mode

• Redis से MySQL में तुरंत switch करने के बजाय, Shadow Mode में दोनों systems को parallel चलाया गया
  • सभी reservations Redis और MySQL दोनों में एक साथ लिखे गए, जबकि Redis source of truth बना रहा
  • वास्तविक production traffic पर MySQL की correctness और performance को parallel verify किया गया
• in-flight reservations migrate किए बिना transition संभव हुआ, क्योंकि दोनों systems साथ-साथ सक्रिय थे
• MySQL को source of truth बनाने के बाद भी kill switch रखा गया, और dual-write path के जरिए Redis को हमेशा up to date रखा गया
• rollout low-traffic pods से शुरू होकर highest-volume merchants तक pod-by-pod धीरे-धीरे किया गया

सीख

1. पुराने निर्णयों की दोबारा समीक्षा करें

• 5 साल पहले जो MySQL से संभव नहीं था, वह आज SKIP LOCKED जैसे नए features के कारण संभव है
• thread limit जैसी "rule of thumb" settings को workload और hardware बदलने पर फिर से evaluate करना चाहिए
• यदि CPU कम है लेकिन queuing हो रही है, तो कारण की गहराई से जाँच करनी चाहिए

2. छोटा शुरू करें और observe करें

• पूरे Rails framework के बिना, छोटे Ruby script और MySQL से minimal prototype बनाया गया
• दूसरे terminal में lock behavior को सीधे देखना, theory से अधिक सिखाता है
• connection occupancy instrumentation pattern (app-layer tag + proxy aggregation) लागू करना आसान है और तुरंत उपयोगी साबित हो सकता है

[hso2341]

काफ़ी समय बाद कोई सचमुच development जैसा लेख आया है।