मजबूर PostgreSQL क्वेरी अनुकूलन

जब एक बंद स्रोत अनुप्रयोग है जो डेटाबेस को सबसे इष्टतम तरीके से एक्सेस नहीं करता है तो क्या करें? एप्लिकेशन को बदलने के बिना प्रश्नों को कैसे ट्यून करें, और संभवतः डेटाबेस ही?

यदि आपने ऐसे प्रश्न नहीं पूछे हैं, तो आप बहुत सफल और कठोर डीबीए हैं।

ठीक है, अगर पूछा जाए, तो मुझे दुख और अनुभव साझा करने दें।

अधिक डेटा संग्रहीत करने, या कार्य सेट करने की आवश्यकता है

यदि समस्या का इतिहास दिलचस्प नहीं है, तो आप इस अनुभाग को सुरक्षित रूप से स्क्रॉल कर सकते हैं।

प्रारंभ में, हमारे पास एक स्वामित्व प्रणाली थी जो पोस्टग्रेएसक्यूएल डेटाबेस में एक बंद प्रारूप से अपना डेटा पार्स करती थी, जहां से हमने इस डेटा को पढ़ा, विश्लेषण और संसाधित किया।

इसके अलावा, इस प्रणाली के उपकरण भी कुछ कार्यों के लिए इस आधार का उपयोग करते हैं, इसलिए इसे त्यागने और इसकी संरचना के साथ एक प्रतिलिपि बनाने के लिए एक निरर्थक विचार लग रहा था।

डिफ़ॉल्ट रूप से, सिस्टम ने स्वचालित रूप से एक सप्ताह से अधिक पुराने रिकॉर्ड हटा दिए, इसलिए स्टैंड पर कोई प्रदर्शन समस्याएं नहीं थीं।

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

इसलिए, स्पष्ट निर्णय INSERT संचालन पर विभाजन और ट्रिगर करना था। फोकस काफी सरल और प्रभावी है। डेटा को आवश्यक विभाजन में डाला जाता है, पुराने रिकॉर्ड को हटाना अक्षम होता है, सब कुछ ठीक लगता है।

जब तक कुछ साल बीत चुके हैं और डेटा अच्छी तरह से जमा नहीं हुआ है।

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

नतीजतन, अनुरोध लंबे समय तक निष्पादित किया जाता है, अन्य सभी अनुरोधों को नीचे खींचता है, लोड तेजी से बढ़ रहा है।

बेशक, पहली बात जो दिमाग में आती है वह है डेवलपर से संपर्क करना।

हालांकि, ज्यादातर मामलों में यह या तो पहुंच क्षेत्र में नहीं है, या यह कई लाइनों में पूरा करने के लिए इस तरह की एक और प्रणाली की लागत मांगेगा।

इसलिए, यह विचार सामने आया कि संभवतः पहले से ही किसी प्रकार का प्रॉक्सी है जो हमारी मदद कर सकता है।

हमें एक प्रॉक्सी की जरूरत है

क्विक गूग्लिंग को इस सवाल का स्पष्ट जवाब नहीं मिला कि पोस्टग्रेक्यूएल या किसी तीसरे पक्ष के सॉफ़्टवेयर की ओर से आने वाली क्वेरी को कैसे फिर से लिखना है।

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

जीथब के लिए एक लिंक साझा करना

जबकि मैंने कोई बाइनरी पैकेज नहीं बनाया, मेरे हाथ नहीं पहुंचे। लेकिन विधानसभा काफी सरल है। सब कुछ C ++ / Qt में लिखा है, क्योंकि मैं इस पर लंबे समय से लिख रहा हूं ...

विन्यास बहुत सरल है:

निर्दिष्ट करें कि किस इंटरफ़ेस और पोर्ट को सुनना है:

listen_address=0.0.0.0 listen_port=5433 

हम पोस्टग्रेज़ SQL सर्वर से सीधे कनेक्ट करने के बजाय निर्दिष्ट पते से कनेक्ट करने के लिए लापरवाह सॉफ़्टवेयर को मजबूर करते हैं।

हम लिखते हैं कि आगे के कनेक्शन कहां हैं (इस उदाहरण में, प्रॉक्सी उसी मशीन पर पोस्टग्रेक्यूएल सर्वर के रूप में स्थित है):

 dst_address=127.0.0.1 dst_port=5432 

हम वांछित अनुरोध को पकड़ने के लिए एक नियमित अभिव्यक्ति निर्धारित करते हैं:

 query = SELECT \* FROM tablename WHERE (.+) 

हम कहते हैं कि हमें इसे फिर से लिखने की जरूरत है:

 action = rewrite 

हम कहते हैं कि कैसे फिर से लिखना है:

 rewrite = SELECT * FROM tablename WHERE (col3 >= '$(now-1M)') AND $(1) 

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

कोई इस तरह लिख सकता है:

 rewrite = SELECT * FROM tablename WHERE (col3 >= now() - interval '1 month') AND $(1) 

लेकिन तब अनुरोध अब () फ़ंक्शन की उपस्थिति के कारण इष्टतम नहीं होगा - खोज अभी भी सभी विभाजनों पर प्रदर्शन की जाएगी। केवल आवश्यक में खोज करने के लिए, आपको एक निरंतर मान निर्दिष्ट करना होगा। इसलिए, हमारा प्रॉक्सी $ (अब -1M) निर्माण के बजाय एक महीने की पारी के साथ टाइमस्टैम्प को प्रतिस्थापित करता है।

परिणाम (लॉग से):

 ORIGINAL query: SELECT * FROM tablename WHERE id=1; MODIFIED query (rule 1): SELECT * FROM tablename WHERE (col3 >= '2018-11-12 11:25:23.0+00') AND id=1; 

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

लॉग के लिए ब्याज के सभी अनुरोधों को आसानी से प्रिंट करना भी संभव है:

 query = .+ action = log 

रिपॉजिटरी में उदाहरणों के साथ एक कॉन्फ़िगरेशन और अधिक विस्तृत विवरण है।

वैसे, यह निर्धारित करना आसान है कि डेवलपर डेटाबेस के साथ काम करने के लिए कितनी अच्छी तरह से लिखता है। उदाहरण के लिए, यदि आप ऐसा अक्सर निष्पादित अनुरोध देखते हैं, तो किसी के लिए मैनुअल धूम्रपान करने का समय है।

 INSERT INTO tablename (col1, col2, col3) VALUES('value1', 1, '2018-12-31') 

यह इस तरह होना चाहिए:

 INSERT INTO tablename (col1, col2, col3) VALUES($1::varchar, $2::integer, $3::date) 

दुर्भाग्य से, अब तक हमारा प्रॉक्सी इस तरह से लिखने में सक्षम नहीं है: / लेकिन यह करना मुश्किल नहीं है। शायद भविष्य में यह संभव होगा कि पहले अनुरोध को दूसरे को फिर से लिखा जाए।

हां, महत्वपूर्ण बिंदु यह है कि एसएसएल अभी तक समर्थित नहीं है, इसलिए क्लाइंट से प्रॉक्सी तक के सभी कनेक्शन एन्क्रिप्शन के बिना होंगे।

मुझे टिप्पणियों और टिप्पणियों पर खुशी होगी।

यदि उपयोगकर्ताओं की सक्रिय रुचि है, तो शायद मैं इस परियोजना को और विकसित करूंगा।

आप अन्य डेटाबेस के साथ काम जोड़ सकते हैं।