કેવી રીતે એક અન-ઇન્ડેક્સડ કોલમે અમારા ડેટાબેઝને તોડી નાખ્યો

એક ખૂટતા ઇન્ડેક્સને કારણે 8 ms ની API 8-સેકન્ડના кошાળમાં ફેરવાઈ ગઈ.

અમે એક મોટા રિલીઝ પહેલા લોડ ટેસ્ટ (load test) કર્યો. 100 રેકોર્ડ્સ સાથે લોકલ ડેવલપમેન્ટ બરાબર ચાલતું હતું. ત્યારબાદ અમે પ્રોડક્શન-સાઈઝના ડેટામાં 1.5 મિલિયન રોઝ (rows) સામે 500 યુઝર્સનું સિમ્યુલેશન કર્યું.

પરિણામો ખરાબ હતા:

  • API રિસ્પોન્સ ટાઈમ 45 ms થી વધીને 8,000 ms થઈ ગયો.
  • ડેટાબેઝ CPU 100% પર પહોંચી ગયો.
  • કનેક્શન પૂલ્સ (connection pools) ખાલી થઈ ગયા, જેના કારણે ટાઈમઆઉટ એરર્સ આવવા લાગ્યા.

અમે સ્લો-ક્વેરી લોગ (slow-query log) તપાસ્યો અને અમને એક એન્ડપોઈન્ટ મળ્યું જે user ID અને status દ્વારા ઓર્ડર હિસ્ટ્રી મેળવી રહ્યું હતું.

PostgreSQL પર EXPLAIN ANALYZE ચલાવવાથી Sequential Scan જોવા મળ્યું. user_id પર કોઈ ઇન્ડેક્સ ન હોવાને કારણે, એન્જિન દરેક રિક્વેસ્ટ માટે તમામ 1.5 મિલિયન રોઝ વાંચતું હતું.

100 કન્કરન્ટ (concurrent) રિક્વેસ્ટ સાથે, તેણે એકસાથે 150 મિલિયન રોઝ સ્કેન કર્યા.

આ સમસ્યાનું નિરાકરણ લાવવામાં પાંચ મિનિટ લાગી.

user_id પર સાદા ઇન્ડેક્સને બદલે, અમે (user_id, status, created_at DESC) પર એક કોમ્પોઝિટ ઇન્ડેક્સ (composite index) બનાવ્યો. જેનાથી ડેટાબેઝ આ કરી શક્યો:

  • user_id દ્વારા ફિલ્ટર કરવું.
  • status દ્વારા ફિલ્ટર કરવું.
  • તરત જ નવીનતમ (newest) રોઝ રિટર્ન કરવી.
  • વધારાના સોર્ટિંગ સ્ટેપ્સ સ્કીપ કરવા.

અમે CREATE INDEX CONCURRENTLY નો ઉપયોગ કર્યો જેથી ઓપરેશન દરમિયાન ટેબલ અનલોક રહે.

ફિક્સ કર્યા પછી:

  • ક્વેરી ટાઈમ 8,150 ms થી ઘટીને 0.14 ms થઈ ગયો.
  • API લેટન્સી (latency) 8 સેકન્ડથી ઘટીને 12 ms થઈ ગઈ.
  • CPU વપરાશ 100% થી ઘટીને 8% થી નીચે આવી ગયો.

શીખવા મળેલા પાઠ:

  • લોકલ ટેસ્ટિંગ ભ્રામક હોઈ શકે છે; 100 રોઝ લાખો રોઝનું પ્રતિનિધિત્વ કરતા નથી.
  • તમારા ફોરેન કીઝ (foreign keys) ને ઇન્ડેક્સ કરો—મોટાભાગના ORMs આ કરવાનું ભૂલી જાય છે.
  • વધુ હાર્ડવેર ખરીદતા પહેલા EXPLAIN ANALYZE ચલાવો.
  • તમે ખરેખર જે ક્વેરીઝ ચલાવો છો તેના આધારે ઇન્ડેક્સ ડિઝાઇન કરો.

પહેલા સર્વર્સ સ્કેલ ન કરો. ક્વેરીઝ સ્કેલ કરો.

સ્ત્રોત: https://dev.to/mia_keller_ffd2584c046ecb/how-an-unindexed-column-silently-killed-our-database-under-load-and-the-5-minute-fix-m32