ஒரு இன்டெக்ஸ் செய்யப்படாத நெடுவரிசை (Unindexed Column) எங்களது தரவுத்தளத்தை எவ்வாறு முடக்கியது

ஒரு சிறிய இன்டெக்ஸ் விடுபட்டது, 8 ms வேகத்தில் இயங்கிய API-ஐ 8 வினாடி கனவாக மாற்றியது.

ஒரு முக்கிய வெளியீட்டிற்கு (major release) முன்னதாக நாங்கள் ஒரு load test செய்தோம். 100 பதிவுகள் (records) கொண்ட உள்ளூர் மேம்பாட்டில் (local development) எல்லாம் சரியாகவே இருந்தது. பின்னர், production அளவிலான தரவுகளில் உள்ள 1.5 மில்லியன் வரிசைகளை (rows) 500 பயனர்கள் பயன்படுத்துவது போல நாங்கள் உருவகப்படுத்தினோம் (simulated).

முடிவுகள் மோசமாக இருந்தன:

  • API பதிலளிக்கும் நேரம் (response time) 45 ms-லிருந்து 8,000 ms ஆக உயர்ந்தது.
  • Database CPU 100 % ஐ எட்டியது.
  • Connection pools தீர்ந்துபோனதால், timeout பிழைகள் ஏற்பட்டன.

நாங்கள் slow-query log-ஐச் சரிபார்த்தபோது, user ID மற்றும் status மூலம் ஆர்டர் வரலாற்றைப் (order history) பெறும் ஒரு endpoint-ஐக் கண்டறிந்தோம்.

PostgreSQL-இல் EXPLAIN ANALYZE செய்தபோது, அது ஒரு Sequential Scan என்பதைக் காட்டியது. user_id-இல் இன்டெக்ஸ் இல்லாததால், ஒவ்வொரு கோரிக்கைக்கும் (request) இன்ஜின் அனைத்து 1.5 மில்லியன் வரிசைகளையும் வாசித்தது.

100 ஒரே நேரத்தில் வரும் கோரிக்கைகளுடன் (concurrent requests), அது ஒரே நேரத்தில் 150 மில்லியன் வரிசைகளை ஸ்கேன் செய்தது.

இதற்கான தீர்வு ஐந்து நிமிடங்கள் மட்டுமே எடுத்தது.

user_id-இல் ஒரு சாதாரண இன்டெக்ஸ் செய்வதற்குப் பதிலாக, (user_id, status, created_at DESC) என்ற composite index-ஐ உருவாக்கினோம். அது தரவுத்தளத்திற்கு பின்வருவனவற்றைச் செய்ய அனுமதித்தது:

  • user_id மூலம் வடிகட்ட (Filter).
  • status மூலம் வடிகட்ட.
  • புதிய வரிசைகளை உடனடியாகத் திருப்பிக் கொடுக்க.
  • கூடுதல் வரிசைப்படுத்தும் (sorting) படிகளைத் தவிர்க்க.

இந்தச் செயல்பாட்டின் போது அட்டவணை (table) லாக் (lock) ஆகாமல் இருக்க, நாங்கள் CREATE INDEX CONCURRENTLY என்பதைப் பயன்படுத்தினோம்.

தீர்வுக்குப் பிறகு:

  • Query நேரம் 8,150 ms-லிருந்து 0.14 ms ஆகக் குறைந்தது.
  • API latency 8 வினாடிகளிலிருந்து 12 ms ஆகக் குறைந்தது.
  • CPU பயன்பாடு 100 %-லிருந்து 8 % க்கும் குறைவாகக் குறைந்தது.

கற்றுக்கொண்ட பாடங்கள்:

  • உள்ளூர் சோதனை (Local testing) தவறான புரிதலை ஏற்படுத்தலாம்; 100 வரிசைகள் ஒரு மில்லியனைப் பிரதிநிதித்துவப்படுத்தாது.
  • உங்கள் foreign keys-களுக்கு இன்டெக்ஸ் செய்யுங்கள்—பெரும்பாலான ORMs இதைத் தவிர்க்கின்றன.
  • கூடுதல் வன்பொருளை (hardware) வாங்குவதற்கு முன் EXPLAIN ANALYZE செய்து பாருங்கள்.
  • நீங்கள் உண்மையில் இயக்கும் கோரிக்கைகளை (queries) அடிப்படையாகக் கொண்டு இன்டெக்ஸ்களை வடிவமைக்கவும்.

முதலில் சர்வர்களை (servers) பெரிதாக்காதீர்கள். கோரிக்கைகளை (queries) மேம்படுத்துங்கள்.

ஆதாரம்: https://dev.to/mia_keller_ffd2584c046ecb/how-an-unindexed-column-silently-killed-our-database-under-load-and-the-5-minute-fix-m32