چگونه یک ستون بدون ایندکس، پایگاه داده ما را از پا درآورد
یک ایندکسِ مفقود شده، یک API با زمان پاسخدهی ۸ میلیثانیه را به یک کابوس ۸ ثانیهای تبدیل کرد.
ما قبل از یک انتشار بزرگ، تست بار (load test) انجام دادیم. در محیط توسعه محلی، کار با ۱۰۰ رکورد به خوبی انجام میشد. سپس ۵۰۰ کاربر را در برابر ۱.۵ میلیون ردیف از دادههای با حجم محیط عملیاتی (production) شبیهسازی کردیم.
نتایج بد بود:
- زمان پاسخدهی API از ۴۵ میلیثانیه به ۸,۰۰۰ میلیثانیه جهش کرد.
- مصرف CPU پایگاه داده به ۱۰۰٪ رسید.
- استخرهای اتصال (connection pools) تخلیه شدند و باعث خطاهای timeout شدند.
ما لاگ کوئریهای کند (slow-query log) را بررسی کردیم و به اندپوینتای رسیدیم که تاریخچه سفارشها را بر اساس user_id و status واکشی میکرد.
اجرای EXPLAIN ANALYZE روی PostgreSQL یک Sequential Scan را نشان داد. موتور پایگاه داده برای هر درخواست، تمام ۱.۵ میلیون ردیف را میخواند، زیرا هیچ ایندکسی روی user_id وجود نداشت.
با ۱۰۰ درخواست همزمان، سیستم در یک لحظه ۱۵۰ میلیون ردیف را اسکن میکرد.
رفع مشکل تنها پنج دقیقه زمان برد.
به جای یک ایندکس ساده روی user_id ، یک ایندکس ترکیبی (composite index) روی (user_id, status, created_at DESC) ایجاد کردیم. این کار به پایگاه داده اجازه داد تا:
- بر اساس
user_idفیلتر کند. - بر اساس
statusفیلتر کند. - جدیدترین ردیفها را بلافاصله برگرداند.
- از مراحل مرتبسازی اضافی صرفنظر کند.
ما از CREATE INDEX CONCURRENTLY استفاده کردیم تا جدول در طول عملیات قفل نشود.
پس از رفع مشکل:
- زمان کوئری از ۸,۱۵۰ میلیثانیه به ۰.۱۴ میلیثانیه کاهش یافت.
- تأخیر (latency) API از ۸ ثانیه به ۱۲ میلیثانیه رسید.
- مصرف CPU از ۱۰۰٪ به زیر ۸٪ کاهش یافت.
درسهای آموخته شده:
- تست محلی میتواند گمراهکننده باشد؛ ۱۰۰ ردیف نمایندهی یک میلیون ردیف نیست.
- روی کلیدهای خارجی (foreign keys) خود ایندکس بگذارید؛ اکثر ORMها این کار را انجام نمیدهند.
- قبل از خرید سختافزار بیشتر،
EXPLAIN ANALYZEرا اجرا کنید. - ایندکسها را بر اساس کوئریهایی که واقعاً اجرا میکنید، طراحی کنید.
ابتدا سرورها را مقیاسبندی نکنید. کوئریها را مقیاسبندی کنید.