कैसे एक PostgreSQL विन्यास परिवर्तन धीमी क्वेरी प्रदर्शन 50 टाइम्स में सुधार करता है

हैलो, खब्रोविट्स! मैं आपके ध्यान में पावन पाटीबांधला द्वारा लेख "कैसे एक एकल पोस्टग्रेक्ज़ विन्यास को 50x तक धीमी गति से बेहतर प्रदर्शन को बदल सकता हूं " का अनुवाद लाता हूं। इससे मुझे PostgreSQL के प्रदर्शन को बेहतर बनाने में बहुत मदद मिली।

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

विभिन्न स्तरों पर देरी को ट्रैक करके, हमने महसूस किया कि एक विशेष PostgreSQL क्वेरी को पूरा करने में 20 सेकंड का समय लगा। यह हमारे लिए एक आश्चर्य की बात है, क्योंकि दोनों तालिकाओं में शामिल होने वाले कॉलम में अनुक्रमित हैं।

धीरे-धीरे निवेदन



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

धीमी क्वेरी निष्पादन योजना



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

किसी अन्य क्लाइंट पर समान अनुरोध के लिए निष्पादन योजना



दिलचस्प बात यह है कि एप्लिकेशन A को केवल एप्लिकेशन B की तुलना में 10 गुना अधिक डेटा प्राप्त हुआ, लेकिन प्रतिक्रिया समय 3,000 गुना अधिक था।

वैकल्पिक PostgreSQL क्वेरी योजनाओं को देखने के लिए, मैंने हैश कनेक्शन को बंद कर दिया और क्वेरी को पुनः आरंभ किया।

धीमी क्वेरी के लिए वैकल्पिक निष्पादन योजना



खैर यहाँ! एक ही अनुरोध एक हैश जॉइन के बजाय नेस्टेड लूप का उपयोग करते समय 50 गुना तेजी से पूरा होता है। तो PostgreSQL ने एप्लिकेशन A के लिए सबसे खराब योजना क्यों चुनी?

दोनों योजनाओं की अनुमानित लागत और वास्तविक लीड समय पर करीब से देखने के साथ, लागत और वास्तविक लीड समय का अनुमानित अनुपात बहुत अलग था। इस विसंगति के लिए मुख्य अपराधी क्रमिक स्कैनिंग की लागत का अनुमान था। PostgreSQL का अनुमान है कि अनुक्रमिक स्कैन 4000+ इंडेक्स स्कैन से बेहतर होगा, लेकिन वास्तव में, इंडेक्स स्कैन 50 गुना तेज थे।

यह मुझे random_page_cost और seq_page_cost कॉन्फ़िगरेशन विकल्पों की ओर ले गया । डिफ़ॉल्ट PostgreSQL मान random_page_cost , seq_page_cost के लिए 4 और 1 हैं, जो HDD के लिए कॉन्फ़िगर किए गए हैं, जहां डिस्क तक यादृच्छिक पहुंच अनुक्रमिक पहुंच से अधिक महंगी है। हालाँकि, ये लागत हमारी तैनाती के लिए गलत थे gp2 EBS वॉल्यूम, जो ठोस राज्य ड्राइव हैं। हमारी तैनाती के लिए, यादृच्छिक और अनुक्रमिक पहुंच लगभग समान है।

मैंने random_page_cost का मान 1 में बदल दिया और अनुरोध को वापस ले लिया। इस बार, PostgreSQL ने नेस्टड लूप का उपयोग किया, और क्वेरी 50 गुना तेजी से चली। परिवर्तन के बाद, हमने PostgreSQL से अधिकतम प्रतिक्रिया समय में भी महत्वपूर्ण कमी देखी।

धीमी गति से अनुरोध के समग्र प्रदर्शन में काफी सुधार हुआ है।



यदि आप SSD का उपयोग करते हैं और डिफ़ॉल्ट कॉन्फ़िगरेशन के साथ PostgreSQL का उपयोग करते हैं, तो मैं आपको random_page_cost और seq_page_cost सेट करने का प्रयास करने की सलाह देता हूं । आप नाटकीय प्रदर्शन सुधार पर आश्चर्यचकित हो सकते हैं।

मैं खुद से जोड़ता हूं कि मैंने प्रोसेसर के संचालन में मेमोरी (कैश) में डेटा को प्राथमिकता देने के लिए न्यूनतम पैरामीटर seq_page_cost = random_page_cost = 0.1 निर्धारित किया है, क्योंकि मैंने PostgreSQL के लिए बड़ी मात्रा में RAM आवंटित किया है (RAM का आकार डिस्क पर डेटाबेस के आकार से अधिक है)। यह बहुत स्पष्ट नहीं है कि पोस्टग्रेज समुदाय अभी भी डिफ़ॉल्ट सेटिंग्स का उपयोग करता है जो कि सर्वर के लिए कम मात्रा में रैम और एचडीडी के साथ प्रासंगिक हैं, और आधुनिक सर्वर के लिए नहीं। उम्मीद है कि इसे जल्द ही ठीक कर लिया जाएगा।