Postgres उत्पादन ट्यूनिंग जो विलंबता को कम करती है

postgresql.conf, अपनी डिफ़ॉल्ट स्थिति में, किसी इंस्टॉलेशन को विफल न करने के लिए डिज़ाइन किया गया है, न कि आपके ट्रैफ़िक को अवशोषित करने के लिए। shared_buffersका ऐतिहासिक मूल्य, 128 एमबी, पोस्टग्रेज़ को किसी भी महत्वपूर्ण संसाधन को आरक्षित किए बिना न्यूनतम मशीन पर शुरू करने की अनुमति देता है। 100 पर max_connections एक छोटे साझा सर्वर पर फिट बैठता है। इनमें से किसी को भी आपके वास्तविक भार के लिए नहीं चुना गया था।

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

पहला प्रोजेक्ट मेमोरी नहीं है, यह कनेक्शन है। प्रत्येक पोस्टग्रेज़ कनेक्शन एक समर्पित सर्वर प्रक्रिया खोलता है जो रैम और संदर्भ सीपीयू समय की खपत करता है, भले ही निष्क्रिय हो। "बहुत सारे कनेक्शन" प्रकार की त्रुटियों से बचने के लिए max_connections को बढ़ाने से समस्या बदल जाती है: एक साथ सक्रिय कनेक्शन की एक निश्चित संख्या से परे, सीपीयू विवाद सबसे तेज़ सहित सभी अनुरोधों की विलंबता को कम कर देता है।

सही दृष्टिकोण सामान्य क्रम को उलट देता है: वास्तविक सर्वर समवर्ती पर स्केल max_connections, फिर PgBouncer जैसे पूलर के साथ एप्लिकेशन-साइड संगामिति को pool_mode=transactionमें अवशोषित करें। पूलर सैकड़ों क्लाइंट कनेक्शनों को कुछ वास्तविक सक्रिय सर्वर कनेक्शनों पर मल्टीप्लेक्स करता है। हमारा समर्पित लेख लेनदेन मोड तंत्र और इसकी सीमाएं (तैयार विवरण, सुनें/सूचित करें), और कार्यान्वयन चुनने के लिए PgBouncer, Supavisor और PgCat के बीच तुलना का विवरण देता है।

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

चार मेमोरी सेटिंग्स का वजन अन्य सभी संयुक्त सेटिंग्स से अधिक है: shared_buffers, effective_cache_size, work_mem और maintenance_work_mem। पहले तीन यह निर्धारित करते हैं कि डिस्क पर लौटने से पहले पोस्टग्रेज कितना डेटा मेमोरी में रखता है; चौथा VACUUM या सूचकांक निर्माण की गति निर्धारित करता है।

shared_buffers सभी कनेक्शनों द्वारा साझा किया गया आंतरिक कैश सेट करता है। आमतौर पर PostgreSQL प्रोजेक्ट द्वारा प्रलेखित बेंचमार्क एक समर्पित डेटाबेस सर्वर पर उपलब्ध RAM का लगभग 25% है। इसके अलावा, लाभ कम हो जाता है और ऑपरेटिंग सिस्टम का डिस्क कैश हावी हो जाता है। effective_cache_size कुछ भी आवंटित नहीं करता है: यह कैश के लिए उपलब्ध कुल मेमोरी (पोस्टग्रेज और ओएस संयुक्त) का एक अनुमान है, जो क्वेरी प्लानर को दिया गया है। इसे छोटा करने से अनुसूचक को अनुक्रमिक स्कैन की ओर धकेल दिया जाता है जबकि एक सूचकांक काफी हद तक कैश हो जाता है; वर्तमान बेंचमार्क 50 से 75% रैम के बीच है।

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

केवल shared_buffers को रीबूट की आवश्यकता है। अन्य तीन को हॉट रीलोड किया गया है, जिसमें एक पृथक सत्र शामिल है: SET work_mem = '64MB'; वैश्विक सेटिंग को प्रभावित किए बिना, एकल लालची अनुरोध की अवधि के लिए।

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

autovacuum_vacuum_scale_factor का डिफ़ॉल्ट मान (ट्रिगर करने से पहले 20% मृत पंक्तियाँ) एक छोटी तालिका के लिए उपयुक्त है, न कि लेखन-गहन मल्टी-मिलियन पंक्ति तालिका के लिए। 10 मिलियन पंक्तियों की तालिका पर, यह 20% पहली पास से पहले जमा हुई 2 मिलियन मृत पंक्तियों का प्रतिनिधित्व करता है। संपूर्ण डेटाबेस के समग्र मान को बदलने के बजाय इस सीमा तालिका को तालिका दर तालिका कम करें।

-- लेखन-भारी तालिका पर सीमा कम करें, समग्र रूप से नहीं
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.05,
  autovacuum_vacuum_cost_limit = 2000
);

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

उत्पादन में विलंबता की अधिकांश समस्याएं न तो सीपीयू से आती हैं और न ही रैम से: वे अनुपस्थित या खराब तरीके से चुने गए सूचकांक से आती हैं। postgresql.confके एकल पैरामीटर को छूने से पहले, प्रश्न में क्वेरी पर EXPLAIN (ANALYZE, BUFFERS) सबसे विश्वसनीय निदान बना हुआ है। यह संसाधनों को जोड़ने से पहले पोस्टग्रेज अनुरोध विलंबता को काफी कम कर देता है।

-- कॉन्फ़िगरेशन को छूने से पहले धीमी क्वेरी का निदान करें
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, o.total
FROM orders o
WHERE o.customer_id = '…'
ORDER BY o.created_at DESC
LIMIT 20;

मल्टी-मिलियन पंक्ति तालिका पर एक Seq Scan, जहां एक Index Scan अपेक्षित था, लगभग हमेशा एक सूचकांक समस्या का संकेत देता है। तीन कारण अक्सर सामने आते हैं: एक गायब सूचकांक, मौजूदा सूचकांक के साथ असंगत एक कॉलम प्रकार, या ANALYZEके बिना बड़े पैमाने पर आयात के बाद अप्रचलित आँकड़े। RAM जोड़ने या work_mem बढ़ाने से कभी-कभी डेटा की थोड़ी मात्रा पर यह लक्षण छिप जाता है; जैसे ही तालिका बढ़ती है समस्या फिर से प्रकट हो जाती है।

इन अनुरोधों को एक-एक करके खोजे बिना उनका पता लगाने के लिए, pg_stat_statements सर्वर के सभी अनुरोधों के निष्पादन आंकड़ों को एकत्रित करता है। एक सामान्य ख़तरा: एक्सटेंशन को पहले shared_preload_librariesमें सूचीबद्ध किया जाना चाहिए, एक "पोस्टमास्टर" संदर्भ पैरामीटर जिसके लिए पुनरारंभ की आवश्यकता होती है। इस चरण के बिना, CREATE EXTENSION pg_stat_statements; चुपचाप सफल हो जाता है लेकिन कुछ भी एकत्र नहीं करता है।

# पूर्ण पुनरारंभ की आवश्यकता है ("पोस्टमास्टर" पैरामीटर)
shared_preload_libraries = 'pg_stat_statements'

-- एक बार सर्वर पुनरारंभ हो जाने पर, प्रत्येक डेटाबेस में ऑडिट करने के लिए:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT query, calls, mean_exec_time, max_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

यह बिल्कुल वही त्रुटि है जो ऑराबेस बैकएंड इस एक्सटेंशन के अनुपस्थित होने पर लौटाता है: एक मूक खाली सूची के बजाय एक स्पष्ट संदेश, जिसे "कोई धीमी क्वेरी नहीं" के साथ भ्रमित किया जा सकता है। स्टूडियो सलाहकार आगे बढ़ता है: यह स्वचालित रूप से 150 एमएस से अधिक औसत समय वाले किसी भी अनुरोध को चेतावनी के रूप में और 500 एमएस से अधिक को महत्वपूर्ण के रूप में वर्गीकृत करता है। ये सीमाएँ समान pg_stat_statementsआँकड़ों पर आधारित हैं।

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

checkpoint_completion_target दो चौकियों के बीच के अंतराल पर इस लेखन के प्रसार को नियंत्रित करता है। पुरानी चेकलिस्ट से एक विवरण गायब है: 2021 में जारी PostgreSQL 14 ने अपना डिफ़ॉल्ट मान 0.5 से 0.9 में बदल दिया। PostgreSQL 16 उदाहरण पर, जैसे कि समर्पित Aurabase टैनेंट क्लस्टर, इसलिए यह सेटिंग डिफ़ॉल्ट रूप से पहले से ही सही है; इसे मैन्युअल रूप से समायोजित करना केवल 14 से पहले के संस्करण पर ही समझ में आता है। ट्यूनिंग को प्रभावित करने वाले अन्य संस्करण परिवर्तनों के लिए हमारी तुलना PostgreSQL 16 बनाम 17 बनाम 18 देखें।

max_wal_size उसी दिशा में कार्य करता है: बहुत कम मान अपेक्षा से अधिक बार चेकप्वाइंट ट्रिगर करता है, तब भी जब checkpoint_timeout अभी तक नहीं पहुंचा है। इसे बढ़ाने से चौकियों की आवृत्ति कम हो जाती है, दुर्घटना के बाद पुनर्प्राप्ति समय में अधिक समय लगता है क्योंकि फिर से चलाने के लिए अधिक वाल होते हैं। अनुपलब्धता के प्रति आपकी सहनशीलता के अनुसार निर्णय लिया जाने वाला समझौता, सार्वभौमिक मूल्य नहीं।

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

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

स्टूडियो का ऑब्जर्वेबिलिटी टैब किसी भी ऑराबेस प्रोजेक्ट के लिए इस फाउंडेशन के हिस्से को कवर करता है, जिसमें इंस्टॉल करने के लिए कोई तृतीय-पक्ष टूल नहीं है। यह धीमे अनुरोधों को सूचीबद्ध करता है, आपको पीआईडी ​​द्वारा सक्रिय अनुरोध को रद्द करने या समाप्त करने की अनुमति देता है, और एक पूल संतृप्ति संकेतक प्रदर्शित करता है जो 80% उपयोग से ऊपर चेतावनी में जाता है। स्व-होस्टेड इंस्टेंस पर, यही मॉनिटरिंग हाथ से बनाई जाती है, जिसमें pg_stat_statements सक्रिय होता है और एक बाहरी मॉनिटरिंग टूल इसमें प्लग किया जाता है।

आठ सेटिंग्स, क्रम में वे सबसे अधिक भुगतान करती हैं, उन्हें छूने से पहले आपको क्या जानना आवश्यक है।

यहां उद्धृत सीमाएँ, जैसे कि 150 एमएस और 80% पूल संतृप्ति, जिसे ऑराबेस स्टूडियो एडवाइजर मॉनिटर करता है, एक कोड-सत्यापित प्रारंभिक बिंदु है, एक सार्वभौमिक सत्य नहीं। आपका वास्तविक आरोप ही एकमात्र अंतिम निर्णायक है। सामान्य नियम का पालन करने के बजाय max_connections को सटीक आकार देने के लिए, समर्पित लेख सूत्र और इसकी सीमाओं का विवरण देता है।

पहली बार चेकलिस्ट लागू करने पर तीन प्रश्न व्यवस्थित रूप से सामने आते हैं।