PostgreSQL के बेहतर उपयोग के तरीके

ऑनलाइन गेम में लेख डेटाबेस और विशेष रूप से इस पर टिप्पणियों को पढ़ने के बाद, मैं इस विचार से परेशान था कि कई डेवलपर्स अपनी परियोजना में डेटाबेस को बदल रहे हैं, इस पारी के साथ अपनी समस्याओं को हल करने की कोशिश कर रहे हैं, लेकिन प्रतिकृति डेटाबेस द्वारा प्रदान की गई सभी संभावनाओं को समाप्त नहीं कर रहे हैं। मैं उस परियोजना पर काम में हिस्सा लेता हूं जिसके डेटाबेस की विशेषता है:

• 5'000 के ऑर्डर के लेनदेन की संख्या - 10'000 प्रति सेकंड
• लगभग 100GB (जो बहुत तेजी से बढ़ रहा है)
• लगभग समान संख्या में पढ़ने / लिखने के संचालन
• ज्यादातर छोटे लेनदेन

ऐतिहासिक कारणों के कारण, जैसा कि वे कहते हैं, संपूर्ण आधार है, गम, एक आधार, जो अब तक पैनापन के बिना है, जो कई समस्याओं को हल कर सकता है। अधिक तीव्र समाधान खोजने का सवाल है जो पोस्टग्रेक्यूएल की विशेषताओं में ठीक है। समाधान जो थोड़े से रक्त के साथ लागू किया जा सकता है।

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

यह PostgreSQL 9.2 (9.3 अभी तक परीक्षण नहीं किया गया है) के बारे में होगा। और, हां, कॉन्फ़िगरेशन के इष्टतम कॉन्फ़िगरेशन पर कोई व्यवस्थापक सुझाव नहीं होगा - मुझे यह जानकर खुशी होगी कि उन्हें सही तरीके से कैसे तेज किया जाए।

"सही इंडेक्स बनाएं" जैसी कैप्टन की सिफारिशें भी गायब होंगी। मुझे लगता है कि ये नैनोटेक्नोलॉजीज सतह पर झूठ बोलते हैं।

बड़ी तालिका और सूचकांक आकार

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

बेशक, AUTOVACUUM नियमित रूप से काम करेगा (यदि कॉन्फ़िगर किया गया है), ठीक है, या आप आवश्यकतानुसार अपने हाथों से VACUUM शुरू कर सकते हैं।

समस्या को कम करने के लिए क्या किया जा सकता है?

• भरण कारक का उपयोग करें। यह पैरामीटर डिस्क पर तालिकाओं के पृष्ठों (डेटा ब्लॉक) की पूर्णता की डिग्री निर्धारित करता है और वहां ट्यूपल्स के संशोधित संस्करणों को लिखने के लिए ब्लॉकों में कितना खाली स्थान आवश्यक है। हमारा अनुभव (अपडेट, मुख्य रूप से एक रिकॉर्ड पर, लेकिन कई) यह दर्शाता है कि ध्यान देने योग्य सकारात्मक प्रभाव प्राप्त करने के लिए, 90% का मान पर्याप्त है (डिफ़ॉल्ट रूप से यह 100% है, अर्थात तालिका यथासंभव कसकर पैक की गई है)। इसका मतलब यह है कि तालिका को कम से कम 10% बड़ा होने के लिए जाना जाएगा, लेकिन पंक्तियों को अपडेट करते समय, उनका नया संस्करण न केवल जहां, बल्कि उस ब्लॉक में लिखा जा सकता है जिसमें वह स्थित था, जो तेज और अधिक कॉम्पैक्ट है। दस्तावेज़ में उल्लेख किया गया है कि तालिकाओं के लिए जिसमें रिकॉर्ड कभी नहीं बदलते (या शायद ही कभी), यह भरने के कारक को बदलने के लिए कोई मतलब नहीं है, जो सही है। वैसे, अनुक्रमित के लिए, भरण कारक पहले से ही डिफ़ॉल्ट रूप से 90 है।
• आंशिक सूचकांक का उपयोग करें। यह अक्सर यह पता लगा सकता है कि आप केवल बूलियन फ़ील्ड के एक निश्चित मूल्य या एक निश्चित एनम मान के साथ रिकॉर्ड में रुचि रखते हैं। उदाहरण के लिए, आपने पृष्ठभूमि कार्यों को करने के लिए डेटाबेस में कार्यों के भंडारण का आयोजन किया (जो, हालांकि, एक अच्छा विचार नहीं है, लेकिन अन्य कारणों से) और अक्सर आप वहां से "नए" की स्थिति के साथ कार्यों का चयन करते हैं। तो क्यों पूरी तालिका को अनुक्रमित किया जाए यदि वास्तव में चयन नगण्य संख्या के रिकॉर्ड से किया जाता है (जाहिर है कि पहले से पूर्ण किए गए कार्यों की तुलना में बहुत कम नए कार्य होंगे)? इंडेक्स पर एक सीमा निर्धारित करने से इसका आकार कई गुना कम हो जाएगा, यदि परिमाण के आदेशों द्वारा नहीं।
• कार्यात्मक सूचकांक का उपयोग करें। कार्यात्मक सूचकांक एक शक्तिशाली उपकरण है जो आम तौर पर एक तालिका में कुछ कॉलम बनाने से बच सकता है। उदाहरण के लिए, दिनांक और समय के साथ टाइमस्टैम्प की तालिका में एक फ़ील्ड है। और आपको अक्सर घंटे के हिसाब से डेटा सैंपलिंग के साथ सैंपल बनाने होते हैं। आप टाइमस्टैम्प कॉलम में सीधे खोज सकते हैं (और उस पर एक सूचकांक डाल सकते हैं)। आप एक अलग कॉलम बना सकते हैं जहां आप ट्रिगर या हाथ से घंटे के मूल्य को अलग से बचा सकते हैं। और आप एक कार्यात्मक सूचकांक बना सकते हैं जिसमें केवल घड़ी के मूल्य होंगे और मौजूदा टाइमस्टैम्प कॉलम के आधार पर निर्माण होगा। नतीजतन, कोई अतिरिक्त कॉलम नहीं बनाया गया था, और एक प्रभावी सूचकांक है।

धीमे-धीमे नमूने

• पिछले दो बिंदु अपनी प्रासंगिकता नहीं खोते हैं - आंशिक और कार्यात्मक सूचकांकों का उपयोग।
• क्लस्टरिंग ( ग्राहक )। एक उपयोगी विषय यदि आप अपनी वस्तुओं तक पहुँचने के लिए विशिष्ट विकल्प जानते हैं। एक निर्दिष्ट सूचकांक पर तालिकाओं को जोड़ने की प्रक्रिया में, PostgreSQL हार्ड ड्राइव पर एक व्यवस्थित तरीके से, सूचकांक के अनुसार तालिका डेटा वितरित करता है। उदाहरण के लिए, घरों से बंधे अपार्टमेंट की सूची के साथ एक तालिका है। आप "हाउस आईडी" फ़ील्ड द्वारा एक इंडेक्स बना सकते हैं और इसे क्लस्टर कर सकते हैं, जिसके परिणामस्वरूप अपार्टमेंट के रिकॉर्ड्स को घर द्वारा भौतिक रूप से समूहीकृत किया जाएगा और यदि आप "घर के नंबर 777 के सभी अपार्टमेंट मुझे वापस करने का अनुरोध करते हैं", तो डेटाबेस को अब पूरे टेबल पर अपार्टमेंट इकट्ठा करने की आवश्यकता नहीं होगी। उन्हें कॉम्पैक्ट रूप से संग्रहीत किया जाएगा। सच है, नियमित रूप से क्लस्टरिंग को दोहराया जाना चाहिए। AUTOVACUUM प्रक्रिया में, इसका उत्पादन नहीं किया जाता है।

लेन-देन काउंटर से अधिक

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

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

लंबा लेन-देन

यह काफी स्पष्ट है, लेकिन सिर्फ अगर मैं इसका उल्लेख करूं।

यह लंबे लेन-देन के बारे में नहीं है, लेकिन ऐसी स्थितियों के बारे में, जहां बग के कारण आवेदन, लेन-देन को बंद नहीं किया और आगे अपनी बात करने के लिए चला गया (उदाहरण के लिए, पृष्ठभूमि कार्य हैंडलर जो दिनों के लिए लटका सकते हैं)। सक्रिय PostgreSQL प्रक्रियाओं की सूची में, ऐसा कनेक्शन "लेन-देन में निष्क्रिय" स्थिति के साथ लटका होगा। VACUUM को नियमित रूप से निष्पादित करने से रिक्त स्थान के पुराने संस्करणों द्वारा कब्जा कर लिया जाता है (संपादन या हटाने के बाद शेष), उस लेनदेन की संख्या द्वारा निर्देशित जिसमें विलोपन या संपादन किया गया था। मोटे तौर पर, यदि लेन-देन 1 और 2 द्वारा हटाए गए लाइनें थीं, और वर्तमान खुले लेनदेन के बीच न्यूनतम संख्या 3 थी, तो इन दो लाइनों को "वास्तविक" के लिए हटा दिया जा सकता है, क्योंकि वे किसी भी खुले लेनदेन के दायरे में नहीं आते हैं। एक निलंबित लेनदेन के मामले में, VACUUM इस लेन-देन के शुरू होने के बाद संपादित की गई एक भी पंक्ति को हटाने में सक्षम नहीं होगा, जिसके परिणामस्वरूप डेटाबेस कुछ घंटों में बहुत बढ़ सकता है।

केवल एक सलाह है - वर्तमान प्रक्रियाओं की निगरानी करने और "लेनदेन में निष्क्रिय" के साथ खलनायक को पकड़ने के लिए।

लंबे समय से अवरुद्ध बैकअप

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

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