प्रॉम्प्ट और संदर्भ
वही क्वेरी टेस्टिंग में तेज़ है लेकिन प्रोडक्शन में धीमी है। EXPLAIN (ANALYZE, BUFFERS) और इसके जोड़े गए मेमोरी, डिस्क एवं I/O विवरणों का उपयोग करके एक खराब प्लान, सॉर्ट स्पिल (sort spill), कैश मिस और स्टोरेज लेटेंसी के बीच अंतर करने के लिए PostgreSQL 18 डायग्नोसिस प्लान डिज़ाइन करें।
यह डेटा इंजीनियरिंग, बैकएंड और डेटाबेस ऑपरेशंस की भूमिकाओं के लिए उपयुक्त है। PostgreSQL 18 अधिक नोड्स के लिए मेमोरी और डिस्क विवरण के साथ EXPLAIN का विस्तार करता है और निष्पादन बफर एक्सेस विवरण दिखाता है; आधिकारिक EXPLAIN गाइड अनुमानों (estimates), वास्तविक पंक्तियों (actual rows), लूप्स, BUFFERS और ANALYZE को परिभाषित करता है। यह लेख सार्वजनिक दस्तावेज़ों पर आधारित है, किसी कंपनी के इंटरव्यू बैंक का दावा नहीं है।
इंटरव्यूअर क्या मूल्यांकन करता है
इंटरव्यूअर स्वचालित इंडेक्स अनुशंसा के बजाय निष्पादन योजना (plan) से साक्ष्य श्रृंखला की अपेक्षा करता है। एक मजबूत उत्तर संसाधन की कमी से अनुमान त्रुटि को अलग करता है, shared hit/read/dirtied/written, सॉर्ट या हैश मेमोरी और I/O टाइमिंग की व्याख्या करता है, और इसमें प्रोडक्शन सैंपलिंग, अनुमतियाँ और रोलबैक शामिल होते हैं।
स्पष्टीकरण हेतु प्रश्न
- क्या क्वेरी को रीड रेप्लिकेट या डी-आइडेंटिफाइड डेटा पर दोबारा चलाया (replay) जा सकता है?
- क्या यह रिग्रेशन औसत लेटेंसी, टेल लेटेंसी (tail latency), या पैरामीटर-विशिष्ट प्लान चयन से संबंधित है?
- प्रोडक्शन में
EXPLAIN ANALYZEके लिए कितना निष्पादन ओवरहेड स्वीकार्य है? - क्या सहसंबंध (correlation) के लिए क्वेरी फिंगरप्रिंट्स, सांख्यिकी रीफ्रेश और डिस्क/कैश मेट्रिक्स उपलब्ध हैं?
30-सेकंड का उत्तर
"पहले प्रोडक्शन पैरामीटर्स और क्वेरी फिंगरप्रिंट को सुरक्षित रखें। रेप्लिकेट पर, EXPLAIN (ANALYZE, BUFFERS, VERBOSE) चलाएं और अनुमानित बनाम वास्तविक पंक्तियों, लूप्स, बफर हिट/रीड और PostgreSQL 18 मेमोरी/डिस्क फ़ील्ड्स की तुलना करें। बड़ी अनुमान त्रुटि सांख्यिकी (statistics) की ओर इशारा करती है; सॉर्ट या हैश स्पिल वर्क मेमोरी, समवर्तीता (concurrency), या डेटा विषमता (skew) की ओर इशारा करता है; उच्च रीड्स के लिए कैश और स्टोरेज साक्ष्य की आवश्यकता होती है। इंडेक्स, सांख्यिकी, या पैरामीटर परिवर्तनों को रेप्लिकेट पर मान्य करें, फिर टेल लेटेंसी पर नज़र रखते हुए कैनरी रोलआउट करें।"
चरण-दर-चरण समाधान
पहले सैंपल तय करें। SQL, बाउंड पैरामीटर्स, प्लानिंग टाइम, एक्ज़ीक्यूशन टाइम, पंक्ति गणना (row count), और डेटाबेस वर्ज़न रिकॉर्ड करें; अलग-अलग पैरामीटर्स अलग-अलग प्लान चुन सकते हैं। EXPLAIN बिना निष्पादित किए अनुमान लगाता है, जबकि ANALYZE स्टेटमेंट को चलाता है। राइट्स (writes) के लिए, रीड रेप्लिकेट, लागू होने पर रीड-ओनली ट्रांज़ैक्शन, या एक सुरक्षित रोलबैक का उपयोग करें ताकि निदान डेटा को न बदले।
अनुमानित और वास्तविक पंक्ति परिमाण (orders of magnitude) की तुलना करके प्लान को पढ़ें, फिर loops का हिसाब लगाएं। BUFFERS शेयर्ड हिट, रीड, डर्टिड (dirtied) और रिटन (written) पेजों को अलग करता है। उच्च हिट काउंट यह साबित नहीं करता कि क्वेरी तेज़ है; रीड्स को डेटा-वॉल्यूम और स्टोरेज-लेटेंसी संदर्भ की आवश्यकता होती है। PostgreSQL 18 अधिक नोड्स में मेमोरी और डिस्क उपयोग विवरण जोड़ता है, जिससे सॉर्ट्स, विंडो एग्रीगेट्स, CTEs और Materialize नोड्स के वर्किंग सेट की पहचान करने में मदद मिलती है।
यदि सॉर्ट या हैश डिस्क का उपयोग करता है, तो निर्धारित करें कि क्या work_mem बहुत छोटा है, समवर्तीता अधिक है, या डेटा विषम है। इसे विश्व स्तर पर (globally) न बढ़ाएं क्योंकि प्रत्येक ऑपरेटर और समवर्ती सत्र मेमोरी की खपत करता है। यदि बफर रीड्स और I/O लेटेंसी अधिक है, तो कैश क्षमता, टेबल ब्लोट (bloat), इंडेक्स सेलेक्टिविटी और स्टोरेज का निरीक्षण करें। कम लेटेंसी वाले रीड्स केवल एक कोल्ड कैश को दर्शा सकते हैं; स्थिर रीप्ले और बार-बार लिए गए नमूनों से इसकी पुष्टि करें।
अनुमान त्रुटियां अक्सर पुरानी सांख्यिकी, सहसंबद्ध कॉलमों के लिए विस्तारित सांख्यिकी (extended statistics) की कमी, पैरामीटर संवेदनशीलता, या बदले हुए डेटा वितरण का संकेत देती हैं। जॉइन ऑर्डर को बाध्य करने से पहले ANALYZE, विस्तारित सांख्यिकी, या क्वेरी रीराइट का परीक्षण करें। इंडेक्स परिवर्तन का मूल्यांकन राइट एम्प्लीफिकेशन (write amplification), रखरखाव लागत और कवरेज के लिए किया जाना चाहिए; एक बेहतर प्लान बेहतर समग्र थ्रूपुट की गारंटी नहीं देता है।
प्रोडक्शन निदान के लिए सैंपलिंग और अनुमति सीमाओं की आवश्यकता होती है। EXPLAIN ANALYZE आवृत्ति और समवर्तीता को सीमित करें, लिटरल्स और परिणामों को डी-आइडेंटिफ़ाई करें, और pg_stat_statements के साथ फिंगरप्रिंट्स को एकत्र करें। योजनाओं, मेमोरी, बफर और I/O मेट्रिक्स को p95/p99 लेटेंसी के साथ सहसंबद्ध करें। परिवर्तनों को कैनरी करें और यदि लॉक प्रतीक्षा, मेमोरी दबाव, या टेल लेटेंसी में गिरावट आती है तो तुरंत वापस (revert) लें।
मॉडल उत्तर
मैं रीड रेप्लिकेट पर निश्चित पैरामीटर्स को दोबारा चलाऊंगा और अनुमानित/वास्तविक पंक्तियों, लूप्स, बफर hit/read/dirtied/written, और PostgreSQL 18 नोड मेमोरी/डिस्क फ़ील्ड्स को एकत्र करूंगा। बड़े अनुमान अंतर सांख्यिकी कार्य की ओर ले जाते हैं; सॉर्ट/हैश स्पिल work_mem, समवर्तीता और विषमता के विश्लेषण की ओर ले जाते हैं; उच्च रीड्स कैश और स्टोरेज साक्ष्य की ओर ले जाते हैं। रेप्लिकेट पर इंडेक्स, सांख्यिकी या पैरामीटर परिवर्तनों को मान्य करें, फिर कैनरी करें और p99, लॉक वेट्स, मेमोरी और I/O की निगरानी करें।
सामान्य गलतियाँ
- गलती → प्राइमरी पर सीधे
EXPLAIN ANALYZEचलाना; यह क्यों विफल होता है → यह वास्तविक स्टेटमेंट निष्पादित करता है और लोड या साइड इफेक्ट्स जोड़ता है; समाधान → रेप्लिकेट, रीड-ओनली ट्रांज़ैक्शन या सुरक्षित रोलबैक का उपयोग करें। - गलती → बफर रीड्स देखने के तुरंत बाद एक इंडेक्स जोड़ना; यह क्यों विफल होता है → रीड्स कोल्ड कैश, सांख्यिकी त्रुटि, या स्टोरेज लेटेंसी हो सकते हैं; समाधान → बार-बार लिए गए नमूनों को I/O मेट्रिक्स के साथ सहसंबद्ध करें।
- गलती → ग्लोबल
work_memको बहुत अधिक सेट करना; यह क्यों विफल होता है → प्रत्येक ऑपरेटर और समवर्ती सत्र इसका उपभोग करता है; समाधान → एक समवर्तीता बजट की गणना करें और सेशन/क्वेरी सेटिंग्स को कैनरी करें। - गलती → केवल निष्पादन समय की तुलना करना; यह क्यों विफल होता है → टेल लेटेंसी, राइट एम्प्लीफिकेशन और प्लान स्थिरता छिपी रहती है; समाधान → p99, संसाधन मेट्रिक्स और रिग्रेशन नमूनों का एक साथ मूल्यांकन करें।
फ़ॉलो-अप प्रश्न
उच्च shared hit काउंट के बावजूद भी कोई क्वेरी धीमी क्यों हो सकती है?
हिट का अर्थ है कि पेज शेयर्ड बफ़र्स से आए हैं; यह CPU, सॉर्टिंग, लॉक प्रतीक्षा या ऑपरेटर प्रोसेसिंग को सस्ता नहीं बनाता है। अड़चन (bottleneck) का पता लगाने के लिए लूप्स, नोड मेमोरी/डिस्क, निष्पादन-समय वितरण और प्रतीक्षा घटनाओं (wait events) को संयोजित करें।
work_mem को भौतिक मेमोरी के आधे पर क्यों सेट न करें?
एक क्वेरी में कई ऑपरेटर और कई समवर्ती सत्र हो सकते हैं, जिनमें से प्रत्येक work_mem का उपभोग करता है। एक साधारण भिन्न (fraction) पीक बजट को पार कर सकता है और OOM ट्रिगर कर सकता है। समवर्तीता, ऑपरेटर गणना, पूल आकार और नोड बजट से गणना करें, फिर निगरानी के साथ मान्य करें।
आपको SQL को दोबारा लिखने के बजाय सांख्यिकी कब रीफ्रेश करनी चाहिए?
यदि डेटा बदलने या सहसंबद्ध कॉलमों में सांख्यिकी की कमी के कारण अनुमान वास्तविक वितरण से दूर रहते हैं, तो पहले रीफ्रेश करें या विस्तारित सांख्यिकी जोड़ें। केवल तभी रीराइट या इंडेक्स करें जब अनुमान विश्वसनीय हों और ऑपरेटर का चयन फिर भी विफल हो रहा हो।