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