ഇൻഡെക്സ് ചെയ്യാത്ത ഒരു കോളം എങ്ങനെയാണ് ഞങ്ങളുടെ ഡാറ്റാബേസിനെ തകർത്തത്

ഒരു ഇൻഡെക്സിന്റെ അഭാവം 8 ms വേഗതയുള്ള ഒരു API-യെ 8 സെക്കൻഡ് നീണ്ടുനിൽക്കുന്ന ഒരു ദുരന്തമാക്കി മാറ്റി.

ഒരു പ്രധാന റിലീസിന് മുമ്പ് ഞങ്ങൾ ഒരു ലോഡ് ടെസ്റ്റ് (load test) നടത്തി. 100 റെക്കോർഡുകൾ ഉള്ളപ്പോൾ ലോക്കൽ ഡെവലപ്‌മെന്റിൽ എല്ലാം കൃത്യമായി പ്രവർത്തിച്ചു. എന്നാൽ പിന്നീട് പ്രൊഡക്ഷൻ ഡാറ്റയുടെ വലിപ്പത്തിലുള്ള 1.5 മില്യൺ വരികളുള്ള ഡാറ്റയിൽ 500 ഉപയോക്താക്കളെ ഞങ്ങൾ സിമുലേറ്റ് ചെയ്തു.

ഫലങ്ങൾ മോശമായിരുന്നു:

  • API റെസ്‌പോൺസ് സമയം 45 ms-ൽ നിന്ന് 8,000 ms ആയി ഉയർന്നു.
  • ഡാറ്റാബേസ് CPU 100 % ആയി ഉയർന്നു.
  • കണക്ഷൻ പൂളുകൾ (Connection pools) തീർന്നുപോയി, ഇത് ടൈമൗട്ട് എററുകൾക്ക് (timeout errors) കാരണമായി.

ഞങ്ങൾ സ്ലോ-ക്വറി ലോഗ് (slow-query log) പരിശോധിച്ചപ്പോൾ, യൂസർ ഐഡി (user ID), സ്റ്റാറ്റസ് (status) എന്നിവ ഉപയോഗിച്ച് ഓർഡർ ഹിസ്റ്ററി എടുക്കുന്ന ഒരു എൻഡ്‌പോയിന്റ് (endpoint) കണ്ടെത്തി.

PostgreSQL-ൽ EXPLAIN ANALYZE റൺ ചെയ്തപ്പോൾ ഒരു സീക്വൻഷ്യൽ സ്കാൻ (Sequential Scan) ആണ് കാണിച്ചത്. user_id-യിൽ ഇൻഡെക്സ് ഇല്ലാത്തതിനാൽ, ഓരോ റിക്വസ്റ്റിനും എൻജിൻ 1.5 മില്യൺ വരികളും വായിക്കുന്നുണ്ടായിരുന്നു.

100 ഒരേസമയം വരുന്ന റിക്വസ്റ്റുകൾ (concurrent requests) വന്നപ്പോൾ, അത് ഒരേസമയം 150 മില്യൺ വരികൾ സ്കാൻ ചെയ്തു.

പരിഹാരം കണ്ടെത്താൻ അഞ്ച് മിനിറ്റ് മാത്രമേ എടുത്തുള്ളൂ.

user_id-യിൽ ഒരു സാധാരണ ഇൻഡെക്സ് നൽകുന്നതിന് പകരം, ഞങ്ങൾ (user_id, status, created_at DESC) എന്ന രീതിയിൽ ഒരു കോമ്പോസിറ്റ് ഇൻഡെക്സ് (composite index) നിർമ്മിച്ചു. ഇത് ഡാറ്റാബേസിന് താഴെ പറയുന്നവ ചെയ്യാൻ സഹായിച്ചു:

  • user_id ഉപയോഗിച്ച് ഫിൽട്ടർ ചെയ്യാം.
  • status ഉപയോഗിച്ച് ഫിൽട്ടർ ചെയ്യാം.
  • ഏറ്റവും പുതിയ വരികൾ ഉടൻ തന്നെ ലഭ്യമാക്കാം.
  • അധികമായ സോർട്ടിംഗ് ഘട്ടങ്ങൾ ഒഴിവാക്കാം.

ഓപ്പറേഷൻ നടക്കുമ്പോൾ ടേബിൾ ലോക്ക് (lock) ആകാതിരിക്കാൻ ഞങ്ങൾ CREATE INDEX CONCURRENTLY ഉപയോഗിച്ചു.

പരിഹരിച്ചതിന് ശേഷം:

  • ക്വറി സമയം 8,150 ms-ൽ നിന്ന് 0.14 ms ആയി കുറഞ്ഞു.
  • API ലാറ്റൻസി (latency) 8 സെക്കൻഡിൽ നിന്ന് 12 ms ആയി കുറഞ്ഞു.
  • CPU ഉപയോഗം 100 %-ൽ നിന്ന് 8 %-ൽ താഴെയായി കുറഞ്ഞു.

പഠിച്ച പാഠങ്ങൾ:

  • ലോക്കൽ ടെസ്റ്റിംഗ് തെറ്റിദ്ധരിപ്പിക്കാം; 100 വരികൾ കൊണ്ട് ഒരു മില്യൺ വരികളെ പ്രതിനിധീകരിക്കാൻ കഴിയില്ല.
  • നിങ്ങളുടെ ഫോറിൻ കീകൾ (foreign keys) ഇൻഡെക്സ് ചെയ്യുക—മിക്ക ORM-കളും ഇത് ഒഴിവാക്കാറുണ്ട്.
  • കൂടുതൽ ഹാർഡ്‌വെയർ വാങ്ങുന്നതിന് മുമ്പ് EXPLAIN ANALYZE റൺ ചെയ്യുക.
  • നിങ്ങൾ യഥാർത്ഥത്തിൽ റൺ ചെയ്യുന്ന ക്വറികൾക്ക് അനുസൃതമായി ഇൻഡെക്സുകൾ രൂപകൽപ്പന ചെയ്യുക.

ആദ്യം സെർവറുകൾ സ്കെയിൽ (scale) ചെയ്യരുത്. ക്വറികൾ സ്കെയിൽ ചെയ്യുക.

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