ग्रीनप्लम में SQL प्रश्नों के अनुकूलन के लिए 5 जीवन हैक

डेटाबेस से संबंधित कोई भी प्रक्रिया, जल्दी या बाद में इस डेटाबेस में प्रश्नों के प्रदर्शन के साथ समस्याओं का सामना करती है।

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


लेख में आगे अनुरोध योजना के कई संदर्भ होंगे। ग्रीनप्लम के लिए जानकारी यहाँ उपलब्ध है ।

ग्रीनप्लम पर अच्छे प्रश्न कैसे लिखें (अच्छी तरह से, या कम से कम काफी दुखी नहीं)

चूंकि हम एक वितरित डेटाबेस के साथ काम कर रहे हैं, इसलिए यह महत्वपूर्ण है कि न केवल एसक्यूएल क्वेरी कैसे लिखी जाए, बल्कि यह भी कि डेटा कैसे संग्रहीत किया जाता है।

1. वितरण

डेटा को अलग-अलग सेगमेंट पर भौतिक रूप से संग्रहीत किया जाता है। आप सेगमेंट द्वारा यादृच्छिक रूप से या किसी फ़ील्ड के हैश फ़ंक्शन के मान या फ़ील्ड के सेट से डेटा को अलग कर सकते हैं।

सिंटैक्स (तालिका बनाते समय):

DISTRIBUTED BY (some_field) 

या तो:

 DISTRIBUTED RANDOMLY 

वितरण क्षेत्र में अच्छी चयनात्मकता होनी चाहिए और इसमें शून्य मान नहीं होना चाहिए (या इस तरह के मूल्य कम से कम हों), क्योंकि ऐसे क्षेत्रों के रिकॉर्ड एक खंड पर वितरित किए जाएंगे, जिससे डेटा विकृतियां हो सकती हैं।

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

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

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

एक अच्छी तरह से चुने हुए वितरण क्षेत्र के साथ, अन्य खंडों को डेटा भेजे बिना, सभी गणना खंड पर की जाएगी। इसके अलावा, तालिकाओं के अधिकतम जुड़ाव (ज्वाइन) के लिए, समान खंड पर समान मान स्थित होने चाहिए।


जुड़ने में उपयोग किए जाने वाले फ़ील्ड का प्रकार सभी तालिकाओं में समान होना चाहिए।
महत्वपूर्ण: वितरण फ़ील्ड के रूप में उपयोग न करें जो प्रश्नों को फ़िल्टर करने के लिए उपयोग किया जाता है, जहां, इस मामले में क्वेरी के दौरान लोड भी समान रूप से वितरित नहीं किया जाएगा।

2. विभाजन

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

तालिकाओं को तार्किक रूप से अनुभागों में विभाजित किया जाना चाहिए, इस उद्देश्य के लिए, अक्सर उस ब्लॉक में उपयोग किए गए फ़ील्ड का चयन करें। वास्तव में तालिकाओं यह अवधि होगी। इस प्रकार, प्रश्नों में तालिका तक उचित पहुंच के साथ, आप केवल संपूर्ण बड़ी तालिका के भाग के साथ काम करेंगे।

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

यह वास्तव में अनुरोधों पर जाने का समय है। अनुरोध एक विशिष्ट योजना के अनुसार खंडों पर निष्पादित किया जाएगा:

3. आशावादी

ग्रीनप्लम में दो ऑप्टिमाइज़र, अंतर्निहित लीगेसी ऑप्टिमाइज़र और तीसरे पक्ष के ओर्का ऑप्टिमाइज़र: GPORCA - Orca - Pivotal Query Optimizer हैं।

अनुरोध पर GPORCA सक्षम करें:

 set optimizer = on; 

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

उदाहरण के लिए, यह क्वेरी योजना को थोड़ा संशोधित करता है, अन्यथा स्कैन किए गए विभाजन को प्रदर्शित करता है:

मानक अनुकूलक:



ओर्का:



GPORCA विभाजन / वितरण फ़ील्ड को अपडेट करने की भी अनुमति देता है। हालांकि ऐसी स्थितियां हैं जब अंतर्निहित ऑप्टिमाइज़र बेहतर प्रदर्शन करते हैं। एक तृतीय-पक्ष अनुकूलक आंकड़ों पर बहुत मांग कर रहा है, यह महत्वपूर्ण है कि विश्लेषण करना न भूलें।

आशावादी कितना भी अच्छा क्यों न हो, एक खराब लिखित क्वेरी भी ओर्का को नहीं खींचेगी:

4. उन फ़ील्ड्स के साथ जोड़तोड़ जहां ब्लॉक या शर्तों में शामिल हों

यह याद रखना महत्वपूर्ण है कि फ़िल्टर फ़ील्ड पर लागू फ़ंक्शन या प्रत्येक रिकॉर्ड पर जुड़ने की शर्तों को लागू किया जाता है।

विभाजन क्षेत्र के मामले में (उदाहरण के लिए, date_trunc से विभाजन फ़ील्ड - तिथि), यहां तक ​​कि GPORCA इस मामले में सही तरीके से काम नहीं कर सकता है, विभाजन क्लिपिंग काम नहीं करेगा।

 --     set optimizer = on; explain select * from edw_ods.t_000045_bills c where date_trunc('month',tech_dt) between to_date('20180101', 'YYYYMMDD') and to_date('20180101', 'YYYYMMDD') + interval '1 month - 1 second' ; 

 --     set optimizer = on; explain select * from edw_ods.t_000045_bills c where tech_dt between to_date('20180101', 'YYYYMMDD') and to_date('20180101', 'YYYYMMDD') + interval '1 month - 1 second' 

मैं विभाजन के प्रदर्शन पर भी ध्यान आकर्षित करता हूं। अंतर्निहित आशावादी सूची में विभाजन प्रदर्शित करेगा:



एक ही विभाजन फिल्टर में स्थिरांक के लिए कार्यों को सावधानीपूर्वक लागू करें। एक उदाहरण एक ही date_trunc है:

 date_trunc('month',to_date($p_some_dt, 'YYYYMMDD')) 

जीपीओआरसीए पूरी तरह से इस तरह के झगड़े का सामना करेगा और सही ढंग से काम करेगा, मानक अनुकूलक अब सामना नहीं करेगा। हालाँकि, एक स्पष्ट प्रकार का रूपांतरण करके, आप इसे काम कर सकते हैं:

 date_trunc('month',to_date($p_some_dt, 'YYYYMMDD'))::timestamp without time zone 

और अगर सब कुछ गलत किया जाता है?

5. मोटेशन

एक अन्य प्रकार का ऑपरेशन जिसे क्वेरी प्लान में देखा जा सकता है, वह है गति। तो खंडों के बीच चिह्नित डेटा आंदोलनों:

• गति को इकट्ठा करें - लगभग हर योजना में प्रदर्शित किया जाएगा, जिसका अर्थ है सभी वर्गों से क्वेरी निष्पादन के परिणामों को एक स्ट्रीम (आमतौर पर मास्टर) में संयोजित करना।
  
  दो कुंजी, जो एक कुंजी द्वारा वितरित की जाती हैं, जो जुड़ने के लिए उपयोग की जाती है, चलती डेटा के बिना, खंडों पर सभी संचालन करते हैं। अन्यथा, प्रसारण गति या पुनर्वितरण गति होती है:
• प्रसारण गति - प्रत्येक खंड अन्य खंडों को डेटा की अपनी प्रति भेजता है। एक आदर्श स्थिति में, प्रसारण केवल छोटी तालिकाओं के लिए होता है।
• पुनर्वितरण गति - विभिन्न कुंजियों में वितरित बड़ी तालिकाओं में शामिल होने के लिए, स्थानीय स्तर पर कनेक्शन बनाने के लिए पुनर्वितरण किया जाता है। बड़ी तालिकाओं के लिए, यह काफी महंगा ऑपरेशन हो सकता है।

प्रसारण और पुनर्वितरण काफी असुविधाजनक संचालन हैं। अनुरोध चलने पर उन्हें हर बार निष्पादित किया जाता है। इनसे बचने की सलाह दी जाती है। क्वेरी योजना में ऐसे बिंदुओं को देखने के बाद, यह वितरण कुंजी पर ध्यान देने योग्य है। विकृत और संघ संचालन भी गति का कारण बनते हैं।

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

यह लेख रोस्टेलकॉम डेटा प्रबंधन टीम द्वारा तैयार किया गया था