একটি ইনডেক্সবিহীন কলাম কীভাবে আমাদের ডেটাবেসকে অচল করে দিয়েছিল
একটি মাত্র ইনডেক্সের অভাব একটি ৮ মিলি-সেকেন্ডের API-কে ৮ সেকেন্ডের দুঃস্বপ্নে পরিণত করেছিল।
একটি বড় রিলিজের আগে আমরা একটি লোড টেস্ট (load test) চালিয়েছিলাম। ১০০টি রেকর্ড নিয়ে লোকাল ডেভেলপমেন্টে সব ঠিকঠাক কাজ করছিল। এরপর আমরা প্রোডাকশন-সাইজের ডেটাতে ১.৫ মিলিয়ন রো (row)-এর বিপরীতে ৫০০ জন ব্যবহারকারীর সিমুলেশন চালিয়েছিলাম।
ফলাফল ছিল ভয়াবহ:
- API রেসপন্স টাইম ৪৫ মিলি-সেকেন্ড থেকে বেড়ে ৮,০০০ মিলি-সেকেন্ড হয়ে যায়।
- ডেটাবেস CPU ১০০% এ পৌঁছে যায়।
- কানেকশন পুল শেষ হয়ে যায়, যার ফলে টাইমআউট এরর (timeout errors) দেখা দেয়।
আমরা স্লো-কোয়েরি লগ (slow-query log) পরীক্ষা করে দেখলাম যে একটি এন্ডপয়েন্ট (endpoint) ইউজার আইডি (user ID) এবং স্ট্যাটাস (status) অনুযায়ী অর্ডারের ইতিহাস সংগ্রহ করছিল।
PostgreSQL-এ EXPLAIN ANALYZE রান করার পর দেখা গেল একটি সিকোয়েন্সিয়াল স্ক্যান (Sequential Scan) হচ্ছে। user_id-তে কোনো ইনডেক্স না থাকায় ইঞ্জিন প্রতিটি রিকোয়েস্টের জন্য পুরো ১.৫ মিলিয়ন রো পড়ে ফেলছিল।
১০০টি কনকারেন্ট রিকোয়েস্টের (concurrent requests) মাধ্যমে এটি একসাথে ১৫০ মিলিয়ন রো স্ক্যান করছিল।
সমাধান নিতে মাত্র পাঁচ মিনিট সময় লেগেছিল।
user_id-তে একটি সাধারণ ইনডেক্সের পরিবর্তে, আমরা (user_id, status, created_at DESC)-এর ওপর একটি কম্পোজিট ইনডেক্স (composite index) তৈরি করলাম। এর ফলে ডেটাবেসটি যা করতে পারল:
user_idদিয়ে ফিল্টার করতে পারে।statusদিয়ে ফিল্টার করতে পারে।- তাৎক্ষণিকভাবে নতুনতম রো-গুলো রিটার্ন করতে পারে।
- অতিরিক্ত সর্টিং ধাপগুলো এড়িয়ে যেতে পারে।
আমরা CREATE INDEX CONCURRENTLY ব্যবহার করেছি যাতে অপারেশনের সময় টেবিলটি আনলকড (unlocked) থাকে।
সমাধানের পর:
- কোয়েরি টাইম ৮,১৫০ মিলি-সেকেন্ড থেকে কমে ০.১৪ মিলি-সেকেন্ডে নেমে আসে।
- API ল্যাটেন্সি (latency) ৮ সেকেন্ড থেকে কমে ১২ মিলি-সেকেন্ডে নেমে আসে।
- CPU ব্যবহার ১০০% থেকে কমে ৮%-এর নিচে চলে আসে।
যা শিখলাম:
- লোকাল টেস্টিং বিভ্রান্তিকর হতে পারে; ১০০টি রো দিয়ে মিলিয়ন রো-এর চিত্র বোঝা সম্ভব নয়।
- আপনার ফরেন কী (foreign keys)-গুলোতে ইনডেক্স ব্যবহার করুন—বেশিরভাগ ORM এটি এড়িয়ে যায়।
- আরও হার্ডওয়্যার কেনার আগে
EXPLAIN ANALYZEরান করুন। - আপনি বাস্তবে যে কোয়েরিগুলো চালান, সেগুলোর ওপর ভিত্তি করে ইনডেক্স ডিজাইন করুন।
প্রথমে সার্ভার স্কেল করবেন না। কোয়েরি স্কেল করুন।