💩 🖍️ ♦️ PostgreSQL डेटाबेस में आश्चर्य क्वेरी अनुसूचक 🧑🏾‍🤝‍🧑🏽 👩🏽‍🤝‍👨🏻 👲🏿

चार्ट, रिपोर्ट और एनालिटिक्स - यह सब किसी भी तरह किसी भी छोटे, उद्यम के बैक-ऑफिस में मौजूद है। जब यह एक्सेल / नंबरों / लिबरे में नियमित तालिकाओं में भीड़ हो जाती है, लेकिन डेटा अभी भी बहुत बड़ा नहीं है, आंतरिक कंपनी की जरूरतों के लिए पारंपरिक समाधान अक्सर PostgreSQL, MySQL या MariaDB जैसे रिलेशनल डेटाबेस का उपयोग करके बनाया जाता है।

ये डेटाबेस स्वतंत्र हैं, एसक्यूएल के लिए धन्यवाद वे सिस्टम में अन्य घटकों के साथ आसानी से एकीकृत कर सकते हैं, वे लोकप्रिय हैं और अधिकांश डेवलपर्स और विश्लेषक उनके साथ काम कर सकते हैं। वे लोड (ट्रैफ़िक और वॉल्यूम) को पर्याप्त रूप से शांत करने के लिए स्वैच्छिक रूप से पकड़ सकते हैं, जब तक कि कंपनी एनालिटिक्स और रिपोर्ट के लिए अधिक जटिल (और महंगे) समाधान नहीं दे सकती।

प्रारंभिक स्थिति

हालांकि, यहां तक कि एक तकनीक में जो बार-बार अध्ययन किया गया है, वहां हमेशा अलग-अलग बारीकियां होती हैं जो अचानक इंजीनियरों की चिंताओं को जोड़ सकती हैं। विश्वसनीयता के अलावा, डेटाबेस के साथ सबसे अक्सर वर्णित समस्या उनका प्रदर्शन है। जाहिर है, डेटा की मात्रा में वृद्धि के साथ, डेटाबेस प्रतिक्रिया दर कम हो जाती है, लेकिन अगर यह अनुमानित रूप से होता है और लोड में वृद्धि के अनुरूप है, तो यह इतना बुरा नहीं है। आप हमेशा अग्रिम में देख सकते हैं जब डेटाबेस ध्यान देना शुरू करता है और एक मौलिक रूप से अलग डेटाबेस में उन्नयन या संक्रमण की योजना बनाता है। बहुत बुरा है अगर डेटाबेस का प्रदर्शन अप्रत्याशित रूप से कम हो जाए।

डेटाबेस प्रदर्शन में सुधार करने का विषय दुनिया के रूप में पुराना है और बहुत व्यापक है, और इस लेख में मैं केवल एक दिशा पर ध्यान देना चाहूंगा। अर्थात्, एक PostgreSQL डेटाबेस में क्वेरी योजनाओं की प्रभावशीलता का मूल्यांकन करने के साथ-साथ डेटाबेस अनुसूचक के व्यवहार को और अधिक पूर्वानुमान बनाने के लिए समय के साथ इस दक्षता को बदल रहा है।

इस तथ्य के बावजूद कि जिन चीजों पर चर्चा की जाएगी उनमें से कई इस डेटाबेस के सभी हाल के संस्करणों पर लागू होती हैं, मतलब संस्करण 11.2 से नीचे के उदाहरण, इस समय उत्तरार्द्ध।
इससे पहले कि हम विवरण में डुबकी लगाते हैं, यह कुछ अर्थों के बारे में बताता है और कहता है कि संबंधपरक डेटाबेस में प्रदर्शन की समस्याएं कहां से आ सकती हैं। जब डेटाबेस "धीमा" होता है तो वास्तव में क्या व्यस्त होता है? मेमोरी की कमी (डिस्क या नेटवर्क एक्सेस की एक बड़ी संख्या), एक कमजोर प्रोसेसर, ये सभी स्पष्ट समाधानों के साथ स्पष्ट समस्याएं हैं, लेकिन क्वेरी निष्पादन गति को और क्या प्रभावित कर सकता है?

यादें ताजा करो

SQL क्वेरी का जवाब देने के लिए डेटाबेस के लिए, इसमें एक क्वेरी प्लान (जिसमें टेबल्स और कॉलम को देखने की जरूरत है कि इंडेक्स की क्या जरूरत है, वहां से क्या चुनना है, क्या तुलना करनी है, कितनी मेमोरी की आवश्यकता है, इत्यादि)। यह योजना एक पेड़ के रूप में बनाई गई है, जिनमें से नोड्स केवल कुछ विशिष्ट संचालन हैं, विभिन्न कम्प्यूटेशनल जटिलता के साथ। यहाँ उनमें से कुछ हैं, उदाहरण के लिए (एन लाइनों की संख्या है जिसके साथ ऑपरेशन करना है):

आपरेशन	क्या किया जाता है	निवेश अनुपात
का चयन करें ... कहाँ ... डेटा संचालन
Seq स्कैन	हम तालिका से प्रत्येक पंक्ति को लोड करते हैं और स्थिति की जांच करते हैं।	ओ (एन)
सूचकांक स्कैन (बी-ट्री इंडेक्स)	डेटा सीधे इंडेक्स में होता है, इसलिए हम इंडेक्स के आवश्यक तत्वों के लिए स्थिति से खोजते हैं और डेटा को वहां से लेते हैं।	ओ (लॉग (एन)), एक हल के पेड़ में एक तत्व की खोज करें।
सूचकांक स्कैन (हैश इंडेक्स)	डेटा सीधे इंडेक्स में होता है, इसलिए हम इंडेक्स के आवश्यक तत्वों के लिए स्थिति से खोजते हैं और डेटा को वहां से लेते हैं।	हे (1), हैश तालिका में एक आइटम के लिए खोज, हैश बनाने की लागत को छोड़कर
बिटमैप हीप स्कैन	हम सूचकांक द्वारा आवश्यक लाइनों की संख्या का चयन करते हैं, फिर हम केवल आवश्यक लाइनों को लोड करते हैं और उनके साथ अतिरिक्त जांच करते हैं।	सूचकांक स्कैन + Seq स्कैन (एम), जहां M इंडेक्स स्कैन के बाद मिलने वाली पंक्तियों की संख्या है। यह माना जाता है कि एम << एन, अर्थात्। सूचकांक Seq Scan की तुलना में अधिक उपयोगी है।
ऑपरेशन में शामिल हों (JOIN, कई टेबल से चुनें)
नेस्टेड लूप	बाईं तालिका से प्रत्येक पंक्ति के लिए, दाईं तालिका में एक उपयुक्त पंक्ति देखें।	ओ (एन ² )। लेकिन अगर तालिकाओं में से एक दूसरे (शब्दकोश) की तुलना में बहुत छोटा है और व्यावहारिक रूप से समय के साथ नहीं बढ़ता है, तो वास्तविक लागत ओ (एन) में घट सकती है।
हश सम्मिलित हों	बाएं और दाएं तालिकाओं से प्रत्येक पंक्ति के लिए, हम हैश पर विचार करते हैं, जो संभावित कनेक्शन विकल्पों की खोजों की संख्या को कम करता है।	ओ (एन), लेकिन कनेक्शन के लिए एक बहुत ही अक्षम हैश फ़ंक्शन या बड़ी संख्या में समान फ़ील्ड के मामले में, ओ (एन ² ) हो सकता है।
मिलाना	शर्त के अनुसार, हम बाईं और दाईं टेबल को क्रमबद्ध करते हैं, जिसके बाद हम दो क्रमबद्ध सूचियों को जोड़ते हैं	ओ (एन * लॉग (एन)) छँटाई लागत + सूची के माध्यम से जा रहा है।
एकत्रीकरण संचालन (ग्रुप बाय, DISTINCT)
समूह समुच्चय	हम एकत्रीकरण की स्थिति के अनुसार तालिका को सॉर्ट करते हैं और फिर सॉर्ट की गई सूची में हम आसन्न पंक्तियों को समूहित करते हैं।	ओ (एन * लॉग (एन))
हैश एग्रीगेट	हम प्रत्येक पंक्ति के लिए एकत्रीकरण स्थिति के लिए हैश पर विचार करते हैं। समान हैश वाली पंक्तियों के लिए, हम एकत्रीकरण करते हैं।	ओ (एन)

जैसा कि आप देख सकते हैं, क्वेरी की लागत बहुत कुछ इस बात पर निर्भर करती है कि डेटा तालिकाओं में कैसे स्थित है और यह आदेश किस तरह उपयोग किए गए हैश संचालन से मेल खाता है। नेस्टेड लूप ओ (एन ² ) में अपनी लागत के बावजूद, हाश ज्वाइन या मर्ज ज्वाइन से अधिक लाभदायक हो सकता है जब ज्वाइन टेबल में से कोई एक या कई पंक्तियों को पतित करता है।

सीपीयू संसाधनों के अलावा, लागत में मेमोरी उपयोग भी शामिल है। दोनों सीमित संसाधन हैं, इसलिए क्वेरी प्लानर को एक समझौता खोजना होगा। यदि हाश ज्वाइन के माध्यम से जुड़ने के लिए दो टेबल गणितीय रूप से अधिक लाभदायक हैं, लेकिन इतने बड़े हैश टेबल के लिए मेमोरी में बस कोई जगह नहीं है, तो डेटाबेस को मर्ज ज्वाइन का उपयोग करने के लिए मजबूर किया जा सकता है, उदाहरण के लिए। एक "धीमा" नेस्टेड लूप को आमतौर पर अतिरिक्त मेमोरी की आवश्यकता नहीं होती है और लॉन्च के ठीक बाद परिणाम तैयार करने के लिए तैयार है।

इन ऑपरेशनों की सापेक्ष लागत ग्राफ पर अधिक स्पष्ट रूप से दिखाई देती है। ये पूर्ण संख्याएं नहीं हैं, बस विभिन्न परिचालनों का अनुमानित अनुपात है।

नेस्टेड लूप चार्ट "नीचे" शुरू होता है, क्योंकि इसके लिए अतिरिक्त गणना या मेमोरी आवंटन या मध्यवर्ती डेटा की प्रतिलिपि बनाने की आवश्यकता नहीं है, लेकिन इसमें O (N ² ) लागत है। मर्ज जॉइन और हाश जॉइन में उच्च प्रारंभिक लागत होती है, हालांकि, कुछ एन मूल्यों के बाद, वे समय में नेस्टेड लूप को पीटना शुरू कर देते हैं। शेड्यूलर सबसे कम लागत के साथ योजना का चयन करने की कोशिश करता है और ऊपर दिए गए चार्ट पर अलग-अलग एन (हरे धराशायी तीर) के साथ विभिन्न कार्यों का पालन करता है। N1 तक की पंक्तियों की संख्या के साथ, नेस्टेड लूप का उपयोग करना अधिक लाभदायक है, N1 से N2 तक यह मर्ज जॉइन के लिए अधिक लाभदायक है, फिर N2 के बाद यह हैश जॉइन के लिए अधिक लाभदायक हो जाता है, हालांकि हैश जॉइन को .h टेबल बनाने के लिए मेमोरी की आवश्यकता होती है। और एन 3 तक पहुंचने पर, यह स्मृति अपर्याप्त हो जाती है, जो मर्ज जॉइन के मजबूर उपयोग की ओर जाता है।

योजना चुनते समय, शेड्यूलर डेटाबेस में कुछ "परमाणु" ऑपरेशनों के सापेक्ष लागतों के सेट का उपयोग करके योजना में प्रत्येक ऑपरेशन की लागत का अनुमान लगाता है। उदाहरण के लिए, गणना, तुलना, स्मृति में एक पृष्ठ लोड करना, आदि। यहाँ डिफ़ॉल्ट कॉन्फ़िगरेशन से इन मापदंडों में से कुछ की एक सूची है, उनमें से कई नहीं हैं:

सापेक्ष लागत स्थिर	डिफ़ॉल्ट मान
seq_page_cost	1.0
random_page_cost	4.0
cpu_tuple_cost	0.01
cpu_index_tuple_cost	0.005
cpu_operator_cost	0.0025
parallel_tuple_cost	0.1
parallel_setup_cost	1000.0

सच है, ये स्थिरांक कुछ ही हैं, आपको अभी भी बहुत "एन" जानने की जरूरत है, अर्थात, पिछले परिणाम से कितने पंक्तियों को प्रत्येक ऐसे ऑपरेशन में संसाधित करना होगा। यहां ऊपरी सीमा स्पष्ट है - डेटाबेस "जानता है" किसी भी तालिका में कितना डेटा है और हमेशा "अधिकतम" की गणना कर सकता है। उदाहरण के लिए, यदि आपके पास प्रत्येक 100 पंक्तियों की दो तालिकाएँ हैं, तो उनके साथ जुड़ने से आउटपुट में 0 से 10,000 पंक्तियों तक उत्पादन हो सकता है। तदनुसार, अगले इनपुट ऑपरेशन में 10,000 तक लाइनें हो सकती हैं।

लेकिन अगर आप तालिकाओं में डेटा की प्रकृति के बारे में कम से कम जानते हैं, तो पंक्तियों की यह संख्या अधिक सटीक रूप से भविष्यवाणी की जा सकती है। उदाहरण के लिए, ऊपर दिए गए उदाहरण से 100 पंक्तियों की दो तालिकाओं के लिए, यदि आप पहले से जानते हैं कि जुड़ने से 10 हजार पंक्तियों का उत्पादन नहीं होगा, लेकिन उसी 100, अगले ऑपरेशन की अनुमानित लागत बहुत कम हो जाती है। इस मामले में, यह योजना दूसरों की तुलना में अधिक प्रभावी हो सकती है।

बॉक्स अनुकूलन से बाहर

शेड्यूलर के लिए मध्यवर्ती परिणामों के आकार का अधिक सटीक अनुमान लगाने में सक्षम होने के लिए, पोस्टग्रेक्यूएल ने तालिकाओं पर आंकड़ों के संग्रह का उपयोग किया है, जो कि pg_statistic, या इसके अधिक पठनीय संस्करण में - pg_stats में संचित है। वैक्यूम शुरू होने पर, या स्पष्ट रूप से ANALYZE कमांड के साथ यह स्वचालित रूप से अपडेट किया जाता है। यह तालिका तालिकाओं में किस डेटा और किस प्रकार की प्रकृति के बारे में जानकारी संग्रहीत करती है। विशेष रूप से, मूल्यों का हिस्टोग्राम, खाली खेतों का प्रतिशत और अन्य जानकारी। योजनाकार योजना के पेड़ में प्रत्येक ऑपरेशन के लिए डेटा की मात्रा का सही अनुमान लगाने के लिए यह सब उपयोग करता है, और इस प्रकार अधिक सटीक रूप से संचालन की लागत और समग्र रूप से योजना की गणना करता है।

उदाहरण के लिए क्वेरी लें:

SELECT t1.important_value FROM t1 WHERE t1.a > 100

मान लें कि "t1.a" कॉलम में मानों का हिस्टोग्राम पता चला है कि 100 से अधिक मूल्य तालिका के लगभग 1% पंक्तियों में पाए जाते हैं। तब हम यह अनुमान लगा सकते हैं कि ऐसा नमूना "t1" तालिका से सभी पंक्तियों के सौवें हिस्से में वापस आ जाएगा।
डेटाबेस आपको EXPLAIN कमांड के माध्यम से योजना की अनुमानित लागत और इसके संचालन के वास्तविक समय को देखने का अवसर देता है - EXPLAIN ANALYZE का उपयोग करते हुए।

ऐसा लगता है कि स्वचालित आंकड़ों के साथ अब सब कुछ ठीक होना चाहिए, लेकिन मुश्किलें हो सकती हैं। साइटस डेटा से इस बारे में एक अच्छा लेख है , स्वचालित आंकड़ों की अक्षमता और क्रिएट स्टेटिस्टिक्स (पीजी 10.0 के साथ उपलब्ध) का उपयोग करके अतिरिक्त आंकड़ों के संग्रह के साथ।

इसलिए, अनुसूचक के लिए, लागतों की गणना करने में त्रुटियों के दो स्रोत हैं:

आदिम परिचालनों की सापेक्ष लागत (seq_page_cost, cpu_operator_cost, और इसी तरह) डिफ़ॉल्ट रूप से वास्तविकता (cpu लागत 0.01, srq पृष्ठ लोड लागत - यादृच्छिक पृष्ठ लोड के लिए 1 या 4) से बहुत भिन्न हो सकती है। इस तथ्य से बहुत दूर कि 100 तुलनाएं 1 पेज लोड के बराबर होंगी।
मध्यवर्ती संचालन में पंक्तियों की संख्या की भविष्यवाणी करने में त्रुटि। इस मामले में ऑपरेशन की वास्तविक लागत पूर्वानुमान से बहुत अलग हो सकती है।

जटिल प्रश्नों में, सभी संभावित योजनाओं को तैयार करना और पूर्वानुमान लगाना अपने आप में बहुत समय ले सकता है। 1 सेकंड में डेटा लौटने का क्या उपयोग है यदि डेटाबेस केवल एक मिनट के अनुरोध की योजना बना रहा था? इस स्थिति के लिए PostgreSQL के पास एक Geqo ऑप्टिमाइज़र है, यह एक शेड्यूलर है जो योजनाओं के लिए सभी संभावित विकल्पों का निर्माण नहीं करता है, लेकिन कुछ यादृच्छिक लोगों के साथ शुरू होता है और सर्वोत्तम को पूरा करता है, लागत को कम करने के तरीकों की भविष्यवाणी करता है। यह सब भी पूर्वानुमान की सटीकता में सुधार नहीं करता है, हालांकि यह कम से कम कुछ अधिक या कम इष्टतम योजना की खोज को गति देता है।

अचानक योजना - प्रतियोगियों

यदि सबकुछ ठीक हो जाता है, तो आपका अनुरोध जल्द से जल्द पूरा होता है। जैसे-जैसे डेटा की मात्रा बढ़ती है, डेटाबेस में क्वेरी निष्पादन की गति धीरे-धीरे बढ़ती जाती है, और कुछ समय बाद, इसका अवलोकन करते हुए, आप मोटे तौर पर अनुमान लगा सकते हैं कि मेमोरी बढ़ाने या सीपीयू कोर की संख्या बढ़ाने या क्लस्टर का विस्तार करने के लिए आवश्यक होगा, आदि।

लेकिन हमें इस तथ्य को ध्यान में रखना चाहिए कि इष्टतम योजना में करीब निष्पादन लागत के साथ प्रतिस्पर्धी हैं, जो हम नहीं देखते हैं। और अगर डेटाबेस अचानक क्वेरी प्लान को दूसरे में बदलता है, तो यह आश्चर्य की बात है। यदि डेटाबेस अधिक कुशल योजना में कूदता है तो यह अच्छा है। और अगर नहीं? उदाहरण के लिए, चित्र को देखें। यह दो योजनाओं (लाल और हरे) के कार्यान्वयन की अनुमानित लागत और वास्तविक समय है:

यहाँ, एक योजना को हरे रंग में दिखाया गया है और इसके निकटतम "प्रतियोगी" को लाल रंग में। बिंदीदार रेखा अनुमानित लागतों का एक ग्राफ दिखाती है, ठोस रेखा वास्तविक समय है। ग्रे धराशायी तीर योजनाकार चयन को दर्शाता है।

मान लीजिए कि एक ठीक शुक्रवार की रात कुछ मध्यवर्ती ऑपरेशन में पंक्तियों की अनुमानित संख्या एन 1 तक पहुंच जाती है और "लाल" पूर्वानुमान "हरे" को मात देना शुरू कर देता है। शेड्यूलर इसका उपयोग करना शुरू कर देता है। वास्तविक क्वेरी निष्पादन समय तुरंत कूदता है (एक हरे रंग की ठोस रेखा से लाल एक पर स्विच करना), यानी, डेटाबेस की गिरावट अनुसूची एक कदम (या शायद "दीवार") का रूप लेती है। व्यवहार में, इस तरह की "दीवार" परिमाण या अधिक के आदेश से क्वेरी निष्पादन समय को बढ़ा सकती है।

यह ध्यान देने योग्य है कि यह स्थिति संभवतः बैक ऑफिस और एनालिटिक्स के लिए सामने के छोर के लिए अधिक विशिष्ट है, क्योंकि उत्तरार्द्ध आमतौर पर एक साथ अधिक प्रश्नों के अनुकूल होता है और इसलिए, डेटाबेस में सरल प्रश्नों का उपयोग करता है, जहां योजना पूर्वानुमान में त्रुटि कम है। यदि यह रिपोर्टिंग या एनालिटिक्स के लिए एक डेटाबेस है, तो क्वेरीज़ मनमाने ढंग से जटिल हो सकती हैं।

इसके साथ कैसे रहना है?

सवाल उठता है: क्या इस तरह की "पानी के नीचे" अदृश्य योजनाओं को दूर करना संभव था? आखिरकार, समस्या यह नहीं है कि वे इष्टतम नहीं हैं, लेकिन किसी अन्य योजना पर स्विच करना अप्रत्याशित रूप से हो सकता है, और, इस के लिए सबसे दुर्भाग्यपूर्ण क्षण में, कानून के अनुसार।

दुर्भाग्य से, आप उन्हें सीधे नहीं देख सकते हैं, लेकिन आप वास्तविक वजन बदलकर वैकल्पिक योजनाओं की तलाश कर सकते हैं, जिसके द्वारा उनका चयन किया जाता है। इस दृष्टिकोण का अर्थ वर्तमान योजना को देखने से दूर करना है, जिसे अनुसूचक इष्टतम मानता है, ताकि उसके सबसे करीबी प्रतियोगियों में से एक इष्टतम हो जाए, और इस प्रकार, उसे EXPLAIN टीम के माध्यम से देखा जा सके। समय-समय पर ऐसे "प्रतियोगियों" और मुख्य योजना में लागत में परिवर्तन की जाँच करते हुए, आप इस संभावना का आकलन कर सकते हैं कि डेटाबेस जल्द ही किसी अन्य योजना में "कूद" जाएगा।

वैकल्पिक योजनाओं के पूर्वानुमानों पर डेटा एकत्र करने के अलावा, आप उन्हें चला सकते हैं और उनके प्रदर्शन को माप सकते हैं, जो डेटाबेस के आंतरिक "कल्याण" का भी विचार देता है।
आइए देखें कि ऐसे प्रयोगों के लिए हमारे पास क्या उपकरण हैं।

सबसे पहले, आप स्पष्ट रूप से सत्र चर का उपयोग करके विशिष्ट कार्यों को "निषिद्ध" कर सकते हैं। आसानी से, उन्हें कॉन्फ़िगरेशन में बदलने की आवश्यकता नहीं है और डेटाबेस को फिर से लोड किया गया है, उनका मूल्य केवल वर्तमान खुले सत्र में बदलता है और अन्य सत्रों को प्रभावित नहीं करता है, इसलिए आप सीधे वास्तविक डेटा के साथ प्रयोग कर सकते हैं। यहां डिफ़ॉल्ट मानों के साथ उनकी एक सूची दी गई है। लगभग सभी ऑपरेशन शामिल हैं:

संचालन किया गया	डिफ़ॉल्ट मान
enable_bitmapscan enable_hashagg enable_hashjoin enable_indexscan enable_indexonlyscan enable_material enable_mergejoin enable_nestloop enable_parallel_append enable_seqscan enable_sort enable_tidscan enable_parallel_hash enable_partition_pruning	पर
enable_partitionwise_join enable_partitionwise_aggregate	बंद

कुछ कार्यों को निषिद्ध करने या अनुमति देने से, हम अनुसूचक को अन्य योजनाओं का चयन करने के लिए बाध्य करते हैं, जिन्हें हम एक ही उदाहरण के साथ देख सकते हैं। वास्तव में, संचालन का "निषेध" उनके उपयोग को प्रतिबंधित नहीं करता है, लेकिन बस उनकी लागत को बहुत बढ़ाता है। PostgreSQL में, प्रत्येक "निषिद्ध" ऑपरेशन स्वचालित रूप से 10 बिलियन पारंपरिक इकाइयों के बराबर लागत का ढेर करता है। इसके अलावा, EXPLAIN में, योजना का कुल वजन निषेधात्मक रूप से अधिक हो सकता है, लेकिन इन दसियों अरबों की पृष्ठभूमि के खिलाफ, शेष संचालन का वजन स्पष्ट रूप से दिखाई देता है, क्योंकि यह आमतौर पर छोटे आदेशों में फिट होता है।

विशेष रुचि निम्नलिखित में से दो प्रचालनों में हैं:

हश ज्वाइन करें। इसकी जटिलता ओ (एन) है, लेकिन परिणाम की मात्रा में पूर्वानुमान के साथ एक त्रुटि के साथ, आप स्मृति में फिट नहीं हो सकते हैं और आपको ओ (एन * लॉग (एन)) की लागत के साथ मर्ज जॉइन करना होगा।
नेस्टेड लूप। इसकी जटिलता ओ (एन ² ) है, इसलिए, आकार के पूर्वानुमान में त्रुटि इस तरह के कनेक्शन की गति को प्रभावित करती है।

उदाहरण के लिए, आइए प्रश्नों में से कुछ वास्तविक संख्याओं को लें, जिसका अनुकूलन हम अपनी कंपनी में कर रहे थे।

योजना 1. सभी अनुमत कार्यों के साथ, सबसे इष्टतम योजना की कुल लागत 274962.09 इकाई थी।

योजना 2. "निषिद्ध" नेस्टेड लूप के साथ, लागत बढ़कर 40000534153.85 हो गई। प्रतिबंध के बावजूद लागत के थोक बनाने वाले ये 40 बिलियन नेस्टेड लूप का इस्तेमाल करते हैं। और शेष 534153.85 - यह योजना में अन्य सभी कार्यों की लागत का सटीक अनुमान है। जैसा कि हम देखते हैं, यह इष्टतम योजना की लागत से लगभग 2 गुना अधिक है, अर्थात, यह इसके लिए पर्याप्त है।

योजना 3. "निषिद्ध" हैश जॉइन के साथ, लागत 383253.77 थी। योजना वास्तव में हैश जॉइन ऑपरेशन का उपयोग किए बिना बनाई गई थी, क्योंकि हमें कोई अरबों नहीं दिखते हैं। हालाँकि, इसकी लागत इष्टतम से 30% अधिक है, जो कि बहुत करीब भी है।

वास्तव में, क्वेरी निष्पादन समय इस प्रकार थे:

योजना 1 (सभी संचालन की अनुमति) ~ 9 मिनट में पूरा हो गया।
योजना 2 ("निषिद्ध" नेस्टेड लूप के साथ) 1.5 सेकंड में पूरी हुई।
योजना 3 ("निषिद्ध" हैश ज्वाइन के साथ) ~ 5 मिनट में पूरी हो गई।

कारण, जैसा कि आप देख सकते हैं, नेस्टेड लूप की लागत की गलत भविष्यवाणी है। दरअसल, जब EXPLAIN ANALYZE के साथ तुलना की जाती है, तो इसमें एक त्रुटि का पता चलता है, जो कि मध्यवर्ती ऑपरेशन में उस दुर्भावनापूर्ण N की परिभाषा के साथ है। एक अनुमानित एकल पंक्ति के बजाय, नेस्टेड लूप को कई हजार पंक्तियों का सामना करना पड़ा, जिससे परिमाण के आदेशों के एक जोड़े द्वारा क्वेरी निष्पादन का समय बढ़ गया।

"निषिद्ध" हैश जॉइनिंग के साथ बचत छँटाई और मर्ज जॉइन के साथ हैशिंग के प्रतिस्थापन के साथ जुड़ी हुई है, जिसने हैश जॉइन की तुलना में इस मामले में तेजी से काम किया। ध्यान दें कि यह योजना 2 वास्तविकता में "इष्टतम" योजना 1 से लगभग दो गुना तेज है। हालांकि यह भविष्यवाणी की गई थी कि यह धीमी होगी।

व्यवहार में, यदि आपका अनुरोध अचानक (एक डीबी अपग्रेड के बाद या केवल अपने आप से) पहले से ज्यादा लंबा चलने लगा, तो पहले हाश जॉइन या नेस्टेड लूप को अस्वीकार करने का प्रयास करें और देखें कि यह क्वेरी की गति को कैसे प्रभावित करता है। एक सफल मामले में, आप कम से कम एक नई गैर-इष्टतम योजना पर प्रतिबंध लगाने में सक्षम होंगे, और पिछले तेजी से वापस आ जाएंगे।

ऐसा करने के लिए, आपको डेटाबेस पुनरारंभ के साथ PostgreSQL कॉन्फ़िगरेशन फ़ाइलों को बदलने की आवश्यकता नहीं है, यह डेटाबेस से एक खुले सत्र के लिए वांछित चर के मूल्य को बदलने के लिए किसी भी कंसोल में काफी सरल है। शेष सत्र प्रभावित नहीं होंगे, कॉन्फ़िगरेशन केवल आपके वर्तमान सत्र के लिए बदल जाएगा। उदाहरण के लिए, इस तरह:

 SET enable_hashjoin='on'; SET enable_nestloop='off'; SELECT … FROM … (    )

योजना की पसंद को प्रभावित करने का दूसरा तरीका निम्न-स्तरीय संचालन के भार को बदलना है। यहां कोई सार्वभौमिक नुस्खा नहीं है, लेकिन, उदाहरण के लिए, यदि आपके पास "वार्म अप" कैश वाला एक डेटाबेस है और पूरे डेटा को मेमोरी में संग्रहीत किया जाता है, तो संभावना है कि अनुक्रमिक पृष्ठ लोडिंग की लागत एक यादृच्छिक पृष्ठ लोड करने की लागत से भिन्न नहीं होती है। जबकि डिफॉल्ट कॉन्फिगर में, क्रमिक की तुलना में यादृच्छिक 4 गुना अधिक महंगा है।

या, एक अन्य उदाहरण, समानांतर प्रसंस्करण चलाने की सशर्त लागत डिफ़ॉल्ट रूप से 1000 है, जबकि एक पृष्ठ को लोड करने की लागत 1.0 है। यह एक समय में केवल एक पैरामीटर को बदलकर शुरू करने के लिए समझ में आता है कि क्या यह योजना की पसंद को प्रभावित करता है। सबसे आसान तरीके पैरामीटर को 0 या कुछ उच्च मूल्य (1 मिलियन) पर सेट करके शुरू करना है।

हालांकि, ध्यान रखें कि एक अनुरोध में प्रदर्शन में सुधार करके आप इसे दूसरे में नीचा दिखा सकते हैं। सामान्य तौर पर, प्रयोगों के लिए एक विस्तृत क्षेत्र है। एक समय में एक, एक बार उन्हें बदलने की कोशिश करना बेहतर है।

वैकल्पिक उपचार के विकल्प

एक शेड्यूलर के बारे में एक कहानी कम से कम दो पोस्टग्रेक्यूएल एक्सटेंशन का उल्लेख किए बिना अधूरी होगी।

गणना योजना को बचाने और इसके आगे उपयोग के लिए मजबूर करने के लिए पहला SR_PLAN है । यह योजना विकल्पों के संदर्भ में डेटाबेस के व्यवहार को अधिक अनुमानित बनाने में मदद करता है।

दूसरा है एडेप्टिव क्वेरी ऑप्टिमाइज़र , जो क्वेरी के वास्तविक समय से शेड्यूलर के लिए प्रतिक्रिया को लागू करता है, यानी, शेड्यूलर निष्पादित क्वेरी के वास्तविक परिणामों को मापता है और भविष्य में इसकी योजनाओं को ध्यान में रखते हुए इसे समायोजित करता है। डेटाबेस इस प्रकार विशिष्ट डेटा और प्रश्नों के लिए "आत्म-ट्यूनिंग" है।

जब यह धीमा हो जाता है तो डेटाबेस और क्या करता है?

अब जब हम क्वेरी प्लानिंग को कम या ज्यादा हल कर रहे हैं, तो हम देखेंगे कि डेटाबेस में और उन अनुप्रयोगों में और क्या सुधार किया जा सकता है जो इसका अधिकतम प्रदर्शन प्राप्त करने के लिए इसका उपयोग करते हैं।

मान लीजिए कि क्वेरी प्लान पहले से ही इष्टतम है। यदि हम सबसे स्पष्ट समस्याओं (कम मेमोरी या धीमी डिस्क / नेटवर्क) को बाहर करते हैं, तो हैश की गणना के लिए अभी भी लागतें हैं। PostgreSQL (GPU या यहां तक कि SSE2 / SSE3 / AVX निर्देश CPU का उपयोग करके) के लिए भविष्य में सुधार के लिए बहुत अच्छे अवसर हैं, लेकिन अभी तक ऐसा नहीं किया गया है और हैश गणना लगभग कभी भी हार्डवेयर की हार्डवेयर क्षमताओं का उपयोग नहीं करती है। आप इस डेटाबेस में थोड़ी मदद कर सकते हैं।

यदि आप ध्यान दें, तो PostgreSQL में डिफ़ॉल्ट अनुक्रमितों को बी-ट्री के रूप में बनाया जाता है। उनकी उपयोगिता यह है कि वे काफी बहुमुखी हैं। इस तरह के सूचकांक का उपयोग समानता स्थितियों और तुलनात्मक स्थितियों (कम या ज्यादा) दोनों के साथ किया जा सकता है। इस तरह के सूचकांक में एक आइटम ढूँढना एक लघुगणकीय लागत है। लेकिन अगर आपकी क्वेरी में केवल एक समानता की स्थिति है, तो अनुक्रमित को हैश इंडेक्स के रूप में भी बनाया जा सकता है, जिसकी लागत स्थिर है।

इसके अलावा, आप अभी भी अनुरोध को संशोधित करने का प्रयास कर सकते हैं ताकि इसके समानांतर निष्पादन का उपयोग किया जा सके। यह समझने के लिए कि इसे फिर से कैसे लिखा जाए, अपने आप को उन मामलों की सूची से परिचित कराना सबसे अच्छा है, जब समापक द्वारा स्वचालित रूप से निषेध किया जाता है और ऐसी स्थितियों से बचा जाता है। इस विषय पर मैनुअल सभी स्थितियों का संक्षेप में वर्णन करता है, इसलिए उन्हें यहां दोहराने का कोई मतलब नहीं है।

यदि अनुरोध अभी भी समानांतर बनाने में अच्छा नहीं है तो क्या करें? यह देखकर बहुत दुख होता है कि आपके शक्तिशाली मल्टी-कोर डेटाबेस में, जहां आप एकमात्र ग्राहक हैं, एक कोर 100% पर कब्जा कर लिया गया है, और अन्य सभी गुठली बस इसे देखते हैं। इस मामले में, आपको एप्लिकेशन के किनारे से डेटाबेस की मदद करनी होगी। चूंकि प्रत्येक सत्र को अपना स्वयं का कोर सौंपा गया है, आप उनमें से कई को खोल सकते हैं और सामान्य क्वेरी को भागों में विभाजित कर सकते हैं, छोटी और तेज चयन कर सकते हैं, उन्हें पहले से ही आवेदन में एक सामान्य परिणाम में जोड़ सकते हैं। यह PostgreSQL डेटाबेस में अधिकतम उपलब्ध सीपीयू संसाधनों पर कब्जा कर लेगा।

अंत में, मैं यह नोट करना चाहूंगा कि उपरोक्त नैदानिक और अनुकूलन क्षमताएं हिमशैल की नोक हैं, हालांकि वे उपयोग करने में काफी आसान हैं और कॉन्फ़िगरेशन को खराब करने या अन्य अनुप्रयोगों के संचालन को बाधित किए बिना सीधे परिचालन डेटा पर समस्या की पहचान करने में आपकी सहायता कर सकते हैं।

सटीक और छोटी योजनाओं के साथ सफल पूछताछ।

PostgreSQL डेटाबेस में आश्चर्य क्वेरी अनुसूचक