PostgreSQL आन्तरिक तथ्याङ्कहरूमा गहिरो डुब्नुहोस्। एलेक्सी लेसोव्स्की

Alexey Lesovsky द्वारा 2015 रिपोर्ट को ट्रान्सक्रिप्शन "पोष्टग्रेएसक्यूएल आन्तरिक तथ्याङ्क मा गहिरो डुबकी"

प्रतिवेदनको लेखकबाट अस्वीकरण: म नोट गर्छु कि यो प्रतिवेदन नोभेम्बर 2015 को हो - 4 वर्ष भन्दा बढी बितिसकेको छ र धेरै समय बितिसकेको छ। रिपोर्टमा छलफल गरिएको संस्करण 9.4 अब समर्थित छैन। विगत 4 वर्षहरूमा, 5 नयाँ विमोचनहरू जारी गरिएका छन् जसमा तथ्याङ्कको सन्दर्भमा धेरै आविष्कारहरू, सुधारहरू र परिवर्तनहरू देखा परेका छन्, र केही सामग्रीहरू पुरानो र सान्दर्भिक छैनन्। मैले समीक्षा गर्दा, मैले यी ठाउँहरूलाई चिन्ह लगाउने प्रयास गरें ताकि तपाईं पाठकलाई भ्रममा नपरोस्। मैले यी ठाउँहरू पुन: लेखेको छैन, त्यहाँ धेरै छन्, र नतिजाको रूपमा, पूर्ण रूपमा फरक रिपोर्ट बाहिर आउनेछ।

PostgreSQL DBMS एक विशाल संयन्त्र हो, र यो संयन्त्रमा धेरै उपप्रणालीहरू हुन्छन्, जसको समन्वयित कार्यले प्रत्यक्ष रूपमा DBMS को कार्यसम्पादनलाई असर गर्छ। सञ्चालनको क्रममा, तथ्याङ्कहरू र घटकहरूको सञ्चालनको बारेमा जानकारी सङ्कलन गरिन्छ, जसले तपाईंलाई PostgreSQL को प्रभावकारिता मूल्याङ्कन गर्न र कार्यसम्पादन सुधार गर्न उपायहरू लिन अनुमति दिन्छ। यद्यपि, त्यहाँ धेरै जानकारी छ र यो एक बरु सरल रूपमा प्रस्तुत गरिएको छ। यस जानकारीलाई प्रशोधन गर्ने र यसलाई व्याख्या गर्ने कहिलेकाहीं पूर्ण रूपमा गैर-तुच्छ कार्य हो, र उपकरण र उपयोगिताहरूको "चिडियाघर" ले उन्नत DBA पनि सजिलै भ्रमित गर्न सक्छ।

शुभ दिउँसो मेरो नाम Aleksey हो। इल्याले भने जस्तै, म PostgreSQL तथ्याङ्कको बारेमा कुरा गर्नेछु।

PostgreSQL गतिविधि तथ्याङ्क। PostgreSQL सँग दुई तथ्याङ्कहरू छन्। गतिविधि तथ्याङ्क, जसमा छलफल गरिनेछ। र डेटा वितरण बारे अनुसूचक तथ्याङ्क। म विशेष गरी PostgreSQL गतिविधि तथ्याङ्कको बारेमा कुरा गर्नेछु, जसले हामीलाई प्रदर्शनको न्याय गर्न र यसलाई सुधार गर्न अनुमति दिन्छ।

म तपाईंलाई बताउछु कि कसरी प्रभावकारी रूपमा तथ्याङ्कहरू प्रयोग गर्ने विभिन्न समस्याहरू समाधान गर्नका लागि जुन तपाईंसँग हुन सक्छ।

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

र त्यहाँ कुनै उपकरण समीक्षा हुनेछैन, म एक उत्पादन अर्कोसँग तुलना गर्नेछैन। त्यहाँ कुनै विज्ञापन हुनेछैन। यसलाई छोडौं।

म तपाईलाई देखाउन चाहन्छु कि तथ्याङ्क प्रयोग गर्नु उपयोगी छ। यो आवश्यक छ। यसलाई निडरतापूर्वक प्रयोग गर्नुहोस्। हामीलाई सादा SQL र SQL को आधारभूत ज्ञान चाहिन्छ।

र हामी समस्याहरू समाधान गर्न कुन तथ्याङ्कहरू छनौट गर्ने बारे कुरा गर्नेछौं।

यदि हामीले PostgreSQL हेर्छौं र प्रक्रियाहरू हेर्न अपरेटिङ सिस्टममा कमाण्ड चलाउँछौं भने, हामीले "ब्ल्याक बक्स" देख्नेछौं। हामी केहि प्रक्रियाहरू देख्नेछौं जसले केहि गर्छ, र नामबाट हामी लगभग कल्पना गर्न सक्छौं कि तिनीहरू त्यहाँ के गरिरहेका छन्, तिनीहरू के गरिरहेका छन्। तर, वास्तवमा, यो ब्ल्याक बक्स हो, हामी भित्र हेर्न सक्दैनौं।

हामी CPU लोड मा हेर्न सक्छौं top, हामी केहि प्रणाली उपयोगिताहरु द्वारा मेमोरी उपयोगिता देख्न सक्छौं, तर हामी PostgreSQL भित्र हेर्न सक्षम हुनेछैनौं। यसका लागि हामीलाई अन्य उपकरण चाहिन्छ।

र अगाडि बढ्दै, म तपाईलाई समय कहाँ बिताएको छ भन्नेछु। यदि हामीले PostgreSQL लाई यस्तो योजनाको रूपमा प्रतिनिधित्व गर्छौं भने, समय कहाँ बित्यो भन्ने जवाफ दिन सम्भव हुनेछ। यी दुई चीजहरू छन्: यो अनुप्रयोगहरू र पृष्ठभूमि कार्यहरूबाट ग्राहक अनुरोधहरूको प्रशोधन हो जुन PostgreSQL यसलाई चलिरहेको राख्नको लागि गर्दछ।

यदि हामीले माथिल्लो बायाँ कुनामा हेर्न थाल्यौं भने, हामी कसरी क्लाइन्ट अनुरोधहरू प्रशोधन गरिन्छ भनेर देख्न सक्छौं। अनुरोध अनुप्रयोगबाट आउँछ र ग्राहक सत्र थप कामको लागि खोलिएको छ। अनुरोध अनुसूचकलाई पास गरिएको छ। योजनाकारले प्रश्न योजना बनाउँछ। कार्यान्वयनको लागि थप पठाउँछ। त्यहाँ तालिका र अनुक्रमणिका संग सम्बन्धित ब्लक I / O डाटा को केहि प्रकार छ। आवश्यक डाटा डिस्कबाट मेमोरीमा "साझेदारी बफर" भनिने विशेष क्षेत्रमा पढिन्छ। क्वेरी नतिजाहरू, यदि तिनीहरू अपडेट, मेटाइएका छन्, WAL मा लेनदेन लगमा रेकर्ड गरिएका छन्। केहि सांख्यिकीय जानकारी लग वा तथ्याङ्क कलेक्टर मा जान्छ। र अनुरोधको परिणाम ग्राहकलाई फिर्ता दिइन्छ। त्यस पछि, ग्राहकले नयाँ अनुरोधको साथ सबै कुरा दोहोर्याउन सक्छ।

हामीसँग पृष्ठभूमि कार्यहरू र पृष्ठभूमि प्रक्रियाहरूसँग के छ? हामीसँग धेरै प्रक्रियाहरू छन् जसले डाटाबेसलाई सामान्य अपरेटिङ मोडमा राख्छ। यी प्रक्रियाहरू पनि रिपोर्टमा कभर गरिनेछन्: यी हुन् अटोभ्याक्यूम, चेकपोइन्टर, प्रतिकृतिसँग सम्बन्धित प्रक्रियाहरू, पृष्ठभूमि लेखक। मैले रिपोर्ट गर्दा म ती प्रत्येकलाई छुनेछु।

तथ्याङ्कमा के समस्या छन् ?

• धेरै जानकारी। PostgreSQL 9.4 ले तथ्याङ्क डेटा हेर्नको लागि 109 मेट्रिक्स प्रदान गर्दछ। यद्यपि, यदि डाटाबेसले धेरै तालिकाहरू, स्कीमाहरू, डाटाबेसहरू भण्डार गर्दछ भने, त्यसपछि यी सबै मेट्रिकहरू तालिकाहरू, डाटाबेसहरूको अनुरूप संख्याद्वारा गुणन गर्नुपर्नेछ। अर्थात्, त्यहाँ थप जानकारी छ। र यसमा डुब्न धेरै सजिलो छ।
• अर्को समस्या यो हो कि तथ्याङ्कहरू काउन्टरहरू द्वारा प्रतिनिधित्व गरिन्छ। यदि हामीले यी तथ्याङ्कहरू हेर्‍यौं भने, हामी लगातार बढ्दो काउन्टरहरू देख्नेछौं। र यदि तथ्याङ्कहरू रिसेट भएदेखि धेरै समय बितिसकेको छ भने, हामी अरबौं मानहरू देख्नेछौं। र उनीहरूले हामीलाई केही पनि भनेनन्।
• कुनै इतिहास छैन। यदि तपाइँसँग केहि प्रकारको विफलता छ भने, केहि 15-30 मिनेट पहिले खस्यो, तपाइँ तथ्याङ्कहरू प्रयोग गर्न र 15-30 मिनेट पहिले के भयो हेर्न सक्षम हुनुहुने छैन। यो समस्या हो।
• PostgreSQL मा निर्मित उपकरण को अभाव एक समस्या हो। कर्नेल विकासकर्ताहरूले कुनै पनि उपयोगिता प्रदान गर्दैनन्। तिनीहरूसँग त्यस्तो केही छैन। तिनीहरू केवल डाटाबेसमा तथ्याङ्क दिन्छन्। यसलाई प्रयोग गर्नुहोस्, यसलाई अनुरोध गर्नुहोस्, जे चाहानुहुन्छ, त्यसपछि गर्नुहोस्।
• PostgreSQL मा निर्मित कुनै उपकरण नभएकोले, यसले अर्को समस्या निम्त्याउँछ। धेरै तेस्रो पक्ष उपकरणहरू। प्रत्येक कम्पनी जसको कम वा कम प्रत्यक्ष हातहरू छन् आफ्नै कार्यक्रम लेख्ने प्रयास गर्दैछन्। र नतिजाको रूपमा, समुदायसँग धेरै उपकरणहरू छन् जुन तपाईंले तथ्याङ्कहरूसँग काम गर्न प्रयोग गर्न सक्नुहुन्छ। र केही उपकरणहरूमा केही सुविधाहरू छन्, अन्य उपकरणहरूमा त्यहाँ कुनै अन्य सुविधाहरू छैनन्, वा त्यहाँ केही नयाँ सुविधाहरू छन्। र एक स्थिति उत्पन्न हुन्छ कि तपाईंले दुई, तीन, वा चार उपकरणहरू प्रयोग गर्न आवश्यक छ जुन एकअर्कालाई ओभरल्याप गर्दछ र फरक प्रकार्यहरू छन्। यो धेरै अप्रिय छ।

यसबाट के हुन्छ ? तथ्याङ्कहरू प्रत्यक्ष रूपमा लिन सक्षम हुनु महत्त्वपूर्ण छ ताकि कार्यक्रमहरूमा निर्भर नहोस्, वा कुनै न कुनै रूपमा यी कार्यक्रमहरू आफैंमा सुधार गर्नुहोस्: आफ्नो फाइदा प्राप्त गर्न केही प्रकार्यहरू थप्नुहोस्।

र तपाईलाई SQL को आधारभूत ज्ञान चाहिन्छ। तथ्याङ्कहरूबाट केही डेटा प्राप्त गर्न, तपाईंले SQL क्वेरीहरू बनाउन आवश्यक छ, अर्थात् तपाईंले कसरी चयन गर्ने, सामेल हुने भनेर जान्न आवश्यक छ।

तथ्याङ्कले हामीलाई धेरै कुराहरू बताउँछ। तिनीहरू कोटिहरूमा विभाजन गर्न सकिन्छ।

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

तथ्याङ्कका स्रोतहरू निम्नानुसार प्रस्तुत गरिएका छन्:

• साझा मेमोरीमा (साझेदारी बफरहरू) त्यहाँ स्थिर डाटा राख्नको लागि एक खण्ड छ, त्यहाँ ती काउन्टरहरू पनि छन् जुन निश्चित घटनाहरू देखा पर्दा, वा डाटाबेसको सञ्चालनमा केही क्षणहरू उत्पन्न हुँदा लगातार बढाइन्छ।
• यी सबै काउन्टरहरू प्रयोगकर्ताको लागि उपलब्ध छैनन् र प्रशासकलाई पनि उपलब्ध छैनन्। यी तल्लो तहका कुरा हुन् । तिनीहरूलाई पहुँच गर्न, PostgreSQL ले SQL प्रकार्यहरूको रूपमा इन्टरफेस प्रदान गर्दछ। हामी यी प्रकार्यहरू प्रयोग गरेर चयन चयनहरू गर्न सक्छौं र मेट्रिकको कुनै प्रकार (वा मेट्रिक्सको सेट) प्राप्त गर्न सक्छौं।
• यद्यपि, यी प्रकार्यहरू प्रयोग गर्न सधैं सुविधाजनक हुँदैन, त्यसैले कार्यहरू दृश्यहरू (VIEWs) को आधार हुन्। यी भर्चुअल तालिकाहरू हुन् जसले एक विशेष उपप्रणालीमा, वा डेटाबेसमा भएका घटनाहरूको केही सेटमा तथ्याङ्कहरू प्रदान गर्दछ।
• यी बिल्ट-इन दृश्यहरू (VIEWs) तथ्याङ्कहरूसँग काम गर्ने मुख्य प्रयोगकर्ता इन्टरफेस हुन्। तिनीहरू कुनै पनि अतिरिक्त सेटिङहरू बिना पूर्वनिर्धारित रूपमा उपलब्ध छन्, तपाइँ तिनीहरूलाई तुरुन्तै प्रयोग गर्न सक्नुहुन्छ, हेर्न सक्नुहुन्छ, त्यहाँबाट जानकारी लिन सक्नुहुन्छ। र योगदानहरू पनि छन्। योगदानहरू आधिकारिक छन्। तपाईंले postgresql-contrib प्याकेज स्थापना गर्न सक्नुहुन्छ (उदाहरणका लागि, postgresql94-contrib), कन्फिगरेसनमा आवश्यक मोड्युल लोड गर्नुहोस्, यसको लागि प्यारामिटरहरू निर्दिष्ट गर्नुहोस्, PostgreSQL पुन: सुरु गर्नुहोस् र तपाईंले यसलाई प्रयोग गर्न सक्नुहुन्छ। (नोट। वितरणको आधारमा, योगदानको भर्खरका संस्करणहरूमा प्याकेज मुख्य प्याकेजको अंश हो).
• र अनौपचारिक योगदानहरू छन्। तिनीहरू मानक PostgreSQL वितरणसँग आपूर्ति गरिएको छैन। तिनीहरू या त कम्पाइल वा पुस्तकालयको रूपमा स्थापित हुनुपर्छ। यस अनौपचारिक योगदानको विकासकर्ता के लिएर आएका आधारमा विकल्पहरू धेरै फरक हुन सक्छन्।

यो स्लाइडले ती सबै दृश्यहरू (VIEWs) र PostgreSQL 9.4 मा उपलब्ध केही कार्यहरू देखाउँछ। हामी देख्न सक्छौं, तिनीहरूमध्ये धेरै छन्। र यदि तपाइँ पहिलो पटक यो अनुभव गर्दै हुनुहुन्छ भने भ्रमित हुन धेरै सजिलो छ।

यद्यपि, यदि हामी अघिल्लो तस्वीर लिन्छौं Как тратится время на PostgreSQL र यो सूची संग उपयुक्त, हामी यो तस्वीर प्राप्त। प्रत्येक दृश्य (VIEWs), वा प्रत्येक प्रकार्य, हामीले PostgreSQL चलिरहेको बेला उपयुक्त तथ्याङ्कहरू प्राप्त गर्न एक उद्देश्य वा अर्कोको लागि प्रयोग गर्न सक्छौं। र हामी पहिले नै उपप्रणाली को सञ्चालन बारे केही जानकारी प्राप्त गर्न सक्नुहुन्छ।

हामीले हेर्ने पहिलो कुरा हो pg_stat_database। हामी देख्न सक्छौं, यो एक प्रतिनिधित्व हो। यसमा धेरै जानकारी समावेश छ। सबैभन्दा विविध जानकारी। र यसले डेटाबेसमा हामीले के गरिरहेको छ भनेर धेरै उपयोगी ज्ञान दिन्छ।

हामी त्यहाँबाट के लिन सक्छौं? सबै भन्दा साधारण कुराहरु संग सुरु गरौं।

select
sum(blks_hit)*100/sum(blks_hit+blks_read) as hit_ratio
from pg_stat_database;

हामीले हेर्न सक्ने पहिलो कुरा क्यास हिट प्रतिशत हो। क्यास हिट प्रतिशत एक उपयोगी मेट्रिक हो। यसले तपाइँलाई साझा बफर क्यासबाट कति डाटा लिइन्छ, र डिस्कबाट कति पढिन्छ भनेर अनुमान गर्न अनुमति दिन्छ।

भन्ने प्रष्ट छ हामीसँग जति धेरै क्यास हिट हुन्छ, त्यति राम्रो। हामी यो मेट्रिकलाई प्रतिशतको रूपमा मूल्याङ्कन गर्छौं। र, उदाहरणका लागि, यदि हामीसँग यी क्यासहरूको प्रतिशत 90% भन्दा बढी हिट छ भने, यो राम्रो छ। यदि यो 90% भन्दा तल खस्यो भने, हामीसँग मेमोरीमा डाटाको तातो हेड राख्नको लागि पर्याप्त मेमोरी छैन। र यो डाटा प्रयोग गर्नको लागि, PostgreSQL लाई डिस्क पहुँच गर्न बाध्य पारिएको छ र यो डाटा मेमोरीबाट पढेको भन्दा ढिलो छ। र तपाईंले मेमोरी बढाउने बारे सोच्न आवश्यक छ: या त साझा बफरहरू बढाउनुहोस्, वा फलामको मेमोरी (RAM) बढाउनुहोस्।

select
datname,
(xact_commit*100)/(xact_commit+xact_rollback) as c_ratio,
deadlocks, conflicts,
temp_file, pg_size_pretty(temp_bytes) as temp_size
from pg_stat_database;

यो प्रदर्शनबाट अरू के लिन सकिन्छ? तपाईले डेटाबेसमा हुने विसंगतिहरू देख्न सक्नुहुन्छ। यहाँ के देखाइएको छ? त्यहाँ कमिटहरू, रोलब्याकहरू, अस्थायी फाइलहरूको सिर्जना, तिनीहरूको आकार, अवरोधहरू र विवादहरू छन्।

हामी यो अनुरोध प्रयोग गर्न सक्छौं। यो SQL धेरै सरल छ। र हामी आफ्नो लागि यो डाटा देख्न सक्छौं।

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

द्वन्द्वहरू प्रतिकृतिसँग सम्बन्धित छन्। र तिनीहरूबाट पनि बच्नुपर्छ। यदि तपाईंसँग प्रतिकृतिमा कार्यान्वयन गरिएका केही प्रश्नहरू छन् र द्वन्द्वहरू उत्पन्न हुन्छन् भने, तपाईंले यी विवादहरू विश्लेषण गर्न र के हुन्छ हेर्नुहोस्। विवरणहरू लगहरूमा फेला पार्न सकिन्छ। र विवादहरू समाधान गर्नुहोस् ताकि एप्लिकेसन अनुरोधहरूले त्रुटिहरू बिना काम गर्दछ।

गतिरोध पनि खराब अवस्था हो । जब अनुरोधहरू स्रोतहरूको लागि प्रतिस्पर्धा गर्छन्, एउटा अनुरोधले एउटा स्रोत पहुँच गर्यो र लक लियो, दोस्रो अनुरोधले दोस्रो स्रोतमा पहुँच गर्यो र लक पनि लियो, र त्यसपछि दुवै अनुरोधहरूले एक-अर्काको स्रोतहरू पहुँच गर्यो र छिमेकीलाई लक जारी गर्नको लागि पर्खने अवरुद्ध भयो। यो पनि समस्याग्रस्त अवस्था हो । तिनीहरूलाई पुन: लेख्ने अनुप्रयोगहरू र स्रोतहरूमा पहुँच क्रमबद्ध गर्ने स्तरमा सम्बोधन गर्न आवश्यक छ। र यदि तपाइँ देख्नुहुन्छ कि तपाइँको गतिरोधहरू लगातार बढ्दै गएको छ भने, तपाइँ लगहरूमा विवरणहरू हेर्नु पर्छ, उत्पन्न भएका परिस्थितिहरूको विश्लेषण गर्नुहोस् र समस्या के हो हेर्नुहोस्।

अस्थायी फाइलहरू (temp_files) पनि खराब छन्। जब प्रयोगकर्ता अनुरोधसँग परिचालन, अस्थायी डाटा समायोजन गर्न पर्याप्त मेमोरी हुँदैन, यसले डिस्कमा फाइल सिर्जना गर्दछ। र मेमोरीमा अस्थायी बफरमा प्रदर्शन गर्न सक्ने सबै कार्यहरू, उसले डिस्कमा पहिले नै प्रदर्शन गर्न थाल्छ। यो ढिलो छ। यसले क्वेरी कार्यान्वयन समय बढाउँछ। र PostgreSQL लाई अनुरोध पठाउने ग्राहकले केहि पछि प्रतिक्रिया प्राप्त गर्नेछ। यदि यी सबै कार्यहरू मेमोरीमा गरिन्छ भने, Postgres ले धेरै छिटो प्रतिक्रिया दिनेछ र ग्राहकले कम पर्खनेछ।

pg_stat_bgwriter - यो दृश्यले दुई PostgreSQL पृष्ठभूमि उपप्रणालीहरूको सञ्चालनको वर्णन गर्दछ: checkpointer и background writer.

सुरु गर्न, नियन्त्रण बिन्दुहरू विश्लेषण गरौं, तथाकथित। checkpoints। चेकपोस्टहरू के हुन्? चेकपोइन्ट लेनदेन लग मा एक स्थिति हो कि लग मा प्रतिबद्ध सबै डेटा परिवर्तन सफलतापूर्वक डिस्क मा डाटा संग सिंक्रोनाइज गरिएको छ भनेर संकेत गर्दछ। प्रक्रिया, कार्यभार र सेटिङहरूमा निर्भर गर्दै, लामो हुन सक्छ र प्राय: डिस्कमा डाटा फाइलहरूसँग साझा बफरहरूमा फोहोर पृष्ठहरू सिङ्क्रोनाइज गर्ने समावेश गर्दछ। यो के को लागि हो? यदि PostgreSQL ले डिस्कमा सबै समय पहुँच गरिरहेको थियो र त्यहाँबाट डाटा लिइरहेको थियो, र प्रत्येक पहुँचमा डाटा लेख्दै थियो, यो ढिलो हुनेछ। त्यसैले, PostgreSQL सँग मेमोरी खण्ड छ, जसको साइज कन्फिगरेसनमा भएका प्यारामिटरहरूमा निर्भर गर्दछ। Postgres ले थप प्रशोधन वा क्वेरीको लागि यस मेमोरीमा परिचालन डेटा आवंटित गर्दछ। डाटा परिवर्तन अनुरोधहरूको अवस्थामा, तिनीहरू परिवर्तन हुन्छन्। र हामीले डाटाको दुई संस्करणहरू पाउँछौं। एउटा मेमोरीमा छ, अर्को डिस्कमा छ। र आवधिक रूपमा तपाईंले यो डाटा सिङ्क्रोनाइज गर्न आवश्यक छ। हामीलाई डिस्कमा सिङ्क्रोनाइज गर्न मेमोरीमा परिवर्तन भएको कुरा चाहिन्छ। यसका लागि चेकपोइन्टहरू चाहिन्छ।

चेकपोइन्ट साझा बफरहरू मार्फत जान्छ, फोहोर पृष्ठहरूलाई चिन्ह लगाउँदछ जुन तिनीहरू चेकपोइन्टको लागि आवश्यक छन्। त्यसपछि यो साझा बफर मार्फत दोस्रो पास सुरु हुन्छ। र चेकपोइन्टको लागि चिन्ह लगाइएका पृष्ठहरू, उसले पहिले नै तिनीहरूलाई सिङ्क्रोनाइज गर्दछ। यसरी, डाटा पहिले नै डिस्क संग सिंक्रोनाइज गरिएको छ।

त्यहाँ दुई प्रकारका नियन्त्रण बिन्दुहरू छन्। एक चेकपोइन्ट टाइमआउटमा कार्यान्वयन गरिएको छ। यो चेकपोइन्ट उपयोगी र राम्रो छ - checkpoint_timed। र त्यहाँ मागमा चेकपोइन्टहरू छन् - checkpoint required। यस्तो चेकपोइन्ट तब हुन्छ जब हामीसँग धेरै ठूलो डाटा रेकर्ड हुन्छ। हामीले धेरै लेनदेन लगहरू रेकर्ड गर्यौं। र PostgreSQL विश्वास गर्दछ कि यो सबैलाई सकेसम्म चाँडो सिङ्क्रोनाइज गर्न आवश्यक छ, एक चेकपोइन्ट बनाउनुहोस् र अगाडि बढ्नुहोस्।

र यदि तपाईंले तथ्याङ्क हेर्नुभयो भने pg_stat_bgwriter र तपाईसँग के छ हेर्नुहोस् checkpoint_req checkpoint_timed भन्दा धेरै ठूलो छ, त्यसपछि यो खराब छ। किन नराम्रो? यसको मतलब यो हो कि PostgreSQL निरन्तर तनावमा छ जब यसलाई डिस्कमा डाटा लेख्न आवश्यक छ। टाइमआउट द्वारा चेकपोइन्ट कम तनावपूर्ण छ र आन्तरिक तालिका अनुसार कार्यान्वयन गरिन्छ र, जस्तै, समयको साथ फैलिएको छ। PostgreSQL सँग काममा पज गर्ने र डिस्क सबसिस्टमलाई तनाव नगर्ने क्षमता छ। यो PostgreSQL को लागी उपयोगी छ। र चेकपोइन्टको समयमा कार्यान्वयन गरिएका अनुरोधहरूले डिस्क सबसिस्टम व्यस्त छ भन्ने तथ्यबाट तनाव अनुभव गर्दैन।

र चेकपोइन्ट समायोजन गर्न तीन प्यारामिटरहरू छन्:

• сheckpoint_segments.

• сheckpoint_timeout.

• сheckpoint_competion_target.

तिनीहरूले तपाईंलाई नियन्त्रण बिन्दुहरूको सञ्चालन नियन्त्रण गर्न अनुमति दिन्छ। तर म तिनीहरूमा बस्ने छैन। तिनीहरूको प्रभाव एक अलग मुद्दा हो।

चेतावनी: प्रतिवेदनमा विचार गरिएको संस्करण 9.4 अब सान्दर्भिक छैन। PostgreSQL को आधुनिक संस्करणहरूमा, प्यारामिटर checkpoint_segments प्यारामिटरहरू द्वारा प्रतिस्थापित min_wal_size и max_wal_size.

अर्को उपप्रणाली पृष्ठभूमि लेखक हो - background writer। ऊ के गर्दै छ? यो अनन्त लूपमा निरन्तर चल्छ। यसले पृष्ठहरूलाई साझा बफरहरूमा स्क्यान गर्दछ र फोहोर पृष्ठहरूलाई डिस्कमा फेला पार्छ। यस तरिकाले, यसले चेकपोइन्टिङको समयमा कम काम गर्न चेकपोइन्टरलाई मद्दत गर्दछ।

उसलाई अरू के चाहिन्छ? यसले साझा बफरहरूमा सफा पृष्ठहरूको आवश्यकताको लागि प्रदान गर्दछ यदि तिनीहरू अचानक आवश्यक पर्दछ (ठूलो मात्रामा र तुरुन्तै) डाटा समायोजन गर्न। मानौं कि अनुरोधलाई सफा पृष्ठहरू आवश्यक हुँदा स्थिति उत्पन्न भयो र तिनीहरू पहिले नै साझा बफरहरूमा छन्। पोस्टग्रेस backend उसले केवल तिनीहरूलाई लिन्छ र प्रयोग गर्दछ, उसले आफैंले केहि सफा गर्नु पर्दैन। तर यदि अचानक त्यस्ता पृष्ठहरू छैनन् भने, ब्याकइन्डले पज गर्दछ र पृष्ठहरू खोज्न थाल्छ तिनीहरूलाई डिस्कमा फ्लश गर्न र तिनीहरूलाई आफ्नै आवश्यकताहरूको लागि लैजानको लागि - जसले वर्तमान कार्यान्वयन अनुरोधको समयलाई नकारात्मक रूपमा असर गर्छ। यदि तपाइँ देख्नुहुन्छ कि तपाइँसँग प्यारामिटर छ maxwritten_clean ठूलो, यसको मतलब पृष्ठभूमि लेखकले आफ्नो काम गरिरहेको छैन र तपाईंले प्यारामिटरहरू बढाउन आवश्यक छ bgwriter_lru_maxpagesताकि उसले एक चक्रमा धेरै काम गर्न सकोस्, थप पृष्ठहरू खाली गर्नुहोस्।

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

चेतावनी: _निम्न पाठले प्रतिकृतिसँग सम्बन्धित सांख्यिकीय दृश्यहरू वर्णन गर्दछ। पोस्टग्रेस 10 मा अधिकांश दृश्य र प्रकार्य नामहरू पुन: नामाकरण गरिएको छ। पुन: नामाकरणको सार प्रतिस्थापन गर्न थियो। xlog मा wal и location मा lsn प्रकार्य/दृश्य नामहरू, आदिमा। विशेष उदाहरण, प्रकार्य pg_xlog_location_diff() मा पुन: नामाकरण गरिएको थियो pg_wal_lsn_diff()._

हामीसँग यहाँ पनि धेरै छ। तर हामीलाई स्थानसँग सम्बन्धित वस्तुहरू मात्र चाहिन्छ।

यदि हामीले देख्यौं कि सबै मानहरू बराबर छन्, तब यो आदर्श हो र प्रतिकृति मास्टरको पछि लाग्दैन।

यहाँ यो हेक्साडेसिमल स्थिति लेनदेन लग मा स्थिति हो। डाटाबेसमा केही गतिविधि भएमा यो निरन्तर बढ्छ: घुसाउने, मेटाउने, इत्यादि।

сколько записано xlog в байтах
$ select
pg_xlog_location_diff(pg_current_xlog_location(),'0/00000000');
лаг репликации в байтах
$ select
client_addr,
pg_xlog_location_diff(pg_current_xlog_location(), replay_location)
from pg_stat_replication;
лаг репликации в секундах
$ select
extract(epoch from now() - pg_last_xact_replay_timestamp());

यदि यी चीजहरू फरक छन् भने, त्यहाँ एक प्रकारको ढिलाइ छ। Lag भनेको मास्टरबाट प्रतिकृतिको ढिलाइ हो, अर्थात् डाटा सर्भरहरू बीच फरक हुन्छ।

ढिलाइको तीन कारणहरू छन्:

• यो डिस्क सबसिस्टम हो जसले फाइल सिंक लेखहरू ह्यान्डल गर्न सक्दैन।
• यी सम्भावित सञ्जाल त्रुटिहरू, वा नेटवर्क ओभरलोडहरू हुन्, जब डाटासँग प्रतिकृतिमा पुग्न समय हुँदैन र यसले यसलाई पुन: उत्पादन गर्न सक्दैन।
• र प्रोसेसर। प्रोसेसर एक धेरै दुर्लभ मामला हो। र मैले त्यो दुई वा तीन पटक देखेको छु, तर त्यो पनि हुन सक्छ।

र यहाँ तीन प्रश्नहरू छन् जसले हामीलाई तथ्याङ्कहरू प्रयोग गर्न अनुमति दिन्छ। हामी हाम्रो लेनदेन लगमा कति रेकर्ड गरिएको छ भनेर अनुमान गर्न सक्छौं। यस्तो प्रकार्य छ pg_xlog_location_diff र हामी बाइट्स र सेकेन्डमा प्रतिकृति ढिलाइ अनुमान गर्न सक्छौं। हामी यसको लागि यो दृश्य (VIEWs) बाट मान पनि प्रयोग गर्छौं।

नोट: pg_xlog_location को सट्टाdiff() प्रकार्य, तपाइँ घटाउन अपरेटर प्रयोग गर्न सक्नुहुन्छ र अर्को स्थानबाट एक स्थान घटाउन सक्नुहुन्छ। आरामदायी।

एक ढिलाइ संग, जो सेकेन्ड मा छ, त्यहाँ एक क्षण छ। यदि मास्टरमा कुनै गतिविधि छैन भने, लेनदेन त्यहाँ 15 मिनेट पहिले थियो र त्यहाँ कुनै गतिविधि छैन, र यदि हामीले प्रतिकृतिमा यो ढिलाइ हेर्छौं भने, हामी 15 मिनेटको ढिलाइ देख्नेछौं। यो सम्झना लायक छ। र तपाईंले यो ढिलो हेर्नुभयो भने यसले मूर्खता निम्त्याउन सक्छ।

pg_stat_all_tables अर्को उपयोगी दृश्य हो। यसले तालिकाहरूमा तथ्याङ्कहरू देखाउँछ। जब हामीसँग डेटाबेसमा तालिकाहरू हुन्छन्, त्यहाँ केही गतिविधिहरू हुन्छन्, केही कार्यहरू, हामी यो दृश्यबाट यो जानकारी प्राप्त गर्न सक्छौं।

select
relname,
pg_size_pretty(pg_relation_size(relname::regclass)) as size,
seq_scan, seq_tup_read,
seq_scan / seq_tup_read as seq_tup_avg
from pg_stat_user_tables
where seq_tup_read > 0 order by 3,4 desc limit 5;

हामीले हेर्न सक्ने पहिलो कुरा अनुक्रमिक तालिका स्क्यान हो। यी खण्डहरू पछिको संख्या आफैंमा खराब छैन र हामीले पहिले नै केहि गर्न आवश्यक छ भनेर संकेत गर्दैन।

यद्यपि, त्यहाँ दोस्रो मेट्रिक छ - seq_tup_read। यो क्रमिक स्क्यानबाट फर्काइएका पङ्क्तिहरूको संख्या हो। यदि औसत संख्या 1, 000, 10, 000 भन्दा बढि छ भने, यो पहिले नै एक सूचक हो कि तपाइँले कतै अनुक्रमणिका निर्माण गर्न आवश्यक हुन सक्छ ताकि पहुँचहरू अनुक्रमणिका द्वारा होस्, वा यस्तो क्रमिक स्क्यानहरू प्रयोग गर्ने क्वेरीहरूलाई अनुकूलन गर्न सम्भव छ। यस्तो हुदैन।

एउटा साधारण उदाहरण - ठूलो अफसेट र लिमिट भएको अनुरोधको लायक छ भनौं। उदाहरणका लागि, तालिकामा 100 पङ्क्तिहरू स्क्यान गरिन्छ र त्यसपछि 000 आवश्यक पङ्क्तिहरू लिइन्छ, र अघिल्लो स्क्यान गरिएका पङ्क्तिहरू खारेज गरिन्छ। यो पनि नराम्रो मामला हो। र त्यस्ता अनुरोधहरूलाई अनुकूलित गर्न आवश्यक छ। र यहाँ यस्तो सरल SQL क्वेरी छ जसमा तपाइँ यसलाई हेर्न सक्नुहुन्छ र प्राप्त संख्याहरू मूल्याङ्कन गर्न सक्नुहुन्छ।

select
relname,
pg_size_pretty(pg_total_relation_size(relname::regclass)) as
full_size,
pg_size_pretty(pg_relation_size(relname::regclass)) as
table_size,
pg_size_pretty(pg_total_relation_size(relname::regclass) -
pg_relation_size(relname::regclass)) as index_size
from pg_stat_user_tables
order by pg_total_relation_size(relname::regclass) desc limit 10;

तालिका आकारहरू पनि यो तालिका प्रयोग गरेर र थप प्रकार्यहरू प्रयोग गरेर प्राप्त गर्न सकिन्छ pg_total_relation_size(), pg_relation_size().

सामान्यतया, त्यहाँ मेटाकमान्डहरू छन् dt и di, जुन तपाइँ PSQL मा प्रयोग गर्न सक्नुहुन्छ र तालिका र अनुक्रमणिका आकारहरू पनि हेर्न सक्नुहुन्छ।

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

गतिविधि लेख्नुहोस्। रेकर्ड भनेको के हो? सञ्चालन हेरौं UPDATE - तालिकामा पङ्क्तिहरू अद्यावधिक गर्ने कार्य। वास्तवमा, अपडेट दुई अपरेशनहरू (वा अझ बढी) हो। यसले नयाँ पङ्क्ति संस्करण घुसाउँदै र पुरानो पङ्क्ति संस्करणलाई अप्रचलित भनी चिन्ह लगाउँदैछ। पछि, autovacuum आउनेछ र रेखाहरूको यी अप्रचलित संस्करणहरू सफा गर्नेछ, यो ठाउँलाई पुन: प्रयोगको लागि उपलब्ध भनी चिन्ह लगाउनुहोस्।

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

select
s.relname,
pg_size_pretty(pg_relation_size(relid)),
coalesce(n_tup_ins,0) + 2 * coalesce(n_tup_upd,0) -
coalesce(n_tup_hot_upd,0) + coalesce(n_tup_del,0) AS total_writes,
(coalesce(n_tup_hot_upd,0)::float * 100 / (case when n_tup_upd > 0
then n_tup_upd else 1 end)::float)::numeric(10,2) AS hot_rate,
(select v[1] FROM regexp_matches(reloptions::text,E'fillfactor=(\d+)') as
r(v) limit 1) AS fillfactor
from pg_stat_all_tables s
join pg_class c ON c.oid=relid
order by total_writes desc limit 50;

र यसको डिजाइनको कारण, UPDATE एक हेवीवेट अपरेशन हो। तर तिनीहरूलाई सजिलो बनाउन सकिन्छ। खानुहोस् hot updates। तिनीहरू PostgreSQL संस्करण 8.3 मा देखा पर्‍यो। र यो के हो? यो एक हल्का अपडेट हो जसले अनुक्रमणिकाहरूलाई पुन: निर्माण गर्न दिँदैन। त्यो हो, हामीले रेकर्ड अपडेट गर्‍यौं, तर पृष्ठमा रहेको रेकर्ड मात्र (जुन तालिकासँग सम्बन्धित छ) अद्यावधिक गरिएको थियो, र अनुक्रमणिकाहरूले अझै पनि पृष्ठमा उही रेकर्डलाई संकेत गर्दछ। कामको यस्तो चाखलाग्दो तर्क छ, जब शून्यता आउँछ, तब त्यसमा यी चेनहरू हुन्छन् hot पुन: निर्माण गर्दछ र सबै चीजहरू अनुक्रमणिकाहरू अद्यावधिक नगरी काम गर्न जारी राख्छन्, र सबै कुरा स्रोतहरूको कम अपशिष्टको साथ हुन्छ।

र जब तपाईंसँग छ n_tup_hot_upd ठूलो, यो धेरै राम्रो छ। यसको मतलब यो हो कि हल्का अपडेटहरू प्रबल हुन्छन् र यो हाम्रो लागि स्रोतहरूको हिसाबले सस्तो छ र सबै कुरा ठीक छ।

ALTER TABLE table_name SET (fillfactor = 70);

भोल्युम कसरी बढाउने hot updateov? प्रयोग गर्न सक्छौं fillfactor। INSERTs प्रयोग गरेर तालिकामा पृष्ठ भर्दा यसले आरक्षित खाली ठाउँको आकार निर्धारण गर्छ। जब सम्मिलितहरू तालिकामा जान्छन्, तिनीहरूले पूर्ण रूपमा पृष्ठ भर्छन्, यसमा खाली ठाउँ नछोड्नुहोस्। त्यसपछि नयाँ पृष्ठ हाइलाइट गरिएको छ। डाटा फेरि भरिएको छ। र यो पूर्वनिर्धारित व्यवहार हो, fillfactor = 100%।

हामी फिलफ्याक्टर 70% मा सेट गर्न सक्छौं। त्यो हो, सम्मिलित संग, नयाँ पृष्ठ आवंटित गरिएको थियो, तर पृष्ठको 70% मात्र भरिएको थियो। र हामीसँग 30% रिजर्भ बाँकी छ। जब तपाइँ एक अद्यावधिक गर्न आवश्यक छ, यो सम्भवतः उही पृष्ठमा हुनेछ, र पङ्क्तिको नयाँ संस्करण उही पृष्ठमा फिट हुनेछ। र hot_update गरिनेछ। यसले टेबुलमा लेख्न सजिलो बनाउँछ।

select c.relname,
current_setting('autovacuum_vacuum_threshold') as av_base_thresh,
current_setting('autovacuum_vacuum_scale_factor') as av_scale_factor,
(current_setting('autovacuum_vacuum_threshold')::int +
(current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples))
as av_thresh,
s.n_dead_tup
from pg_stat_user_tables s join pg_class c ON s.relname = c.relname
where s.n_dead_tup > (current_setting('autovacuum_vacuum_threshold')::int
+ (current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples));

Autovacuum लाम। Autovacuum यस्तो उपप्रणाली हो जसको लागि PostgreSQL मा धेरै थोरै तथ्याङ्कहरू छन्। हामी pg_stat_activity को तालिकामा मात्र देख्न सक्छौं कि हामीसँग अहिले कतिवटा भ्याकुमहरू छन्। तर, यो चालमा लाममा कति टेबलहरू छन् भनेर बुझ्न धेरै गाह्रो छ।

नोट: _ Postgres 10 देखि, भ्याकुम भ्याकुम ट्र्याक गर्ने स्थितिमा धेरै सुधार भएको छ - pg_stat_progress दृश्य देखा पर्‍योवैक्यूम, जसले अटोभ्याक्यूम निगरानीको मुद्दालाई धेरै सरल बनाउँछ।

हामी यो सरल प्रश्न प्रयोग गर्न सक्छौं। र हामी देख्न सक्छौं कि भ्याकुम कहिले बनाउनुपर्छ। तर, भ्याकुम कसरी र कहिले सुरु गर्नुपर्छ? यी स्ट्रिङका पुराना संस्करणहरू हुन् जुन मैले पहिले कुरा गरेको थिएँ। अद्यावधिक भएको छ, पङ्क्तिको नयाँ संस्करण सम्मिलित गरिएको छ। स्ट्रिङको अप्रचलित संस्करण देखा परेको छ। तालिका pg_stat_user_tables यस्तो प्यारामिटर छ n_dead_tup। यसले "मृत" पङ्क्तिहरूको संख्या देखाउँछ। र चाँडै मृत पङ्क्तिहरूको संख्या एक निश्चित थ्रेसहोल्ड भन्दा बढि भएको छ, एक autovacuum टेबलमा आउनेछ।

र यो थ्रेसहोल्ड कसरी गणना गरिन्छ? यो तालिकामा पङ्क्तिहरूको कुल संख्याको धेरै विशिष्ट प्रतिशत हो। त्यहाँ एक प्यारामिटर छ autovacuum_vacuum_scale_factor। यसले प्रतिशत परिभाषित गर्दछ। मानौं १०% + त्यहाँ ५० रेखाहरूको अतिरिक्त आधार थ्रेसहोल्ड छ। अनि के हुन्छ? जब हामीसँग तालिकामा सबै पङ्क्तिहरूको "10% + 50" भन्दा धेरै मृत पङ्क्तिहरू हुन्छन्, हामी तालिकालाई autovacuum मा राख्छौं।

select c.relname,
current_setting('autovacuum_vacuum_threshold') as av_base_thresh,
current_setting('autovacuum_vacuum_scale_factor') as av_scale_factor,
(current_setting('autovacuum_vacuum_threshold')::int +
(current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples))
as av_thresh,
s.n_dead_tup
from pg_stat_user_tables s join pg_class c ON s.relname = c.relname
where s.n_dead_tup > (current_setting('autovacuum_vacuum_threshold')::int
+ (current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples));

यद्यपि, त्यहाँ एउटा बिन्दु छ। प्यारामिटरहरूको लागि आधारभूत थ्रेसहोल्डहरू av_base_thresh и av_scale_factor व्यक्तिगत रूपमा नियुक्त गर्न सकिन्छ। र, तदनुसार, थ्रेसहोल्ड ग्लोबल हुनेछैन, तर तालिकाको लागि व्यक्तिगत। त्यसकारण, गणना गर्न, त्यहाँ तपाईंले चाल र चालहरू प्रयोग गर्न आवश्यक छ। र यदि तपाइँ इच्छुक हुनुहुन्छ भने, तपाइँ Avito बाट हाम्रा सहकर्मीहरूको अनुभव हेर्न सक्नुहुन्छ (स्लाइडमा लिङ्क अमान्य छ र पाठमा अद्यावधिक गरिएको छ)।

लागि लेखे munin प्लगइनजसले यी कुराहरूलाई ध्यानमा राख्छ। दुईवटा पानाहरूमा खुट्टाको कपडा छ। तर उहाँले सही रूपमा विचार गर्नुहुन्छ र धेरै प्रभावकारी रूपमा हामीलाई मूल्याङ्कन गर्न अनुमति दिन्छ जहाँ हामीलाई टेबलहरूको लागि धेरै खाली ठाउँ चाहिन्छ, जहाँ थोरै छ।

हामी यसको बारेमा के गर्न सक्छौं? यदि हामीसँग लामो लाम छ र अटोभ्याकुमले सामना गर्न सक्दैन भने, हामी भ्याकुम कार्यकर्ताहरूको संख्या बढाउन सक्छौं, वा केवल भ्याकुमलाई थप आक्रामक बनाउन सक्छौं।ताकि यो पहिले ट्रिगर हुन्छ, टेबललाई सानो टुक्रामा प्रशोधन गर्दछ। र यसरी लाइन कम हुनेछ। - यहाँ मुख्य कुरा डिस्क मा लोड निगरानी गर्न को लागी छ, किनभने। भ्याकुम कुरा नि: शुल्क छैन, यद्यपि SSD / NVMe उपकरणहरूको आगमनको साथ, समस्या कम उल्लेखनीय भएको छ।

pg_stat_all_indexes अनुक्रमणिका मा तथ्याङ्क हो। उनी ठूली होइनन्। र हामी यसबाट अनुक्रमणिकाको प्रयोगको बारेमा जानकारी प्राप्त गर्न सक्छौं। र उदाहरण को लागी, हामी कुन अनुक्रमणिका अतिरिक्त छ भनेर निर्धारण गर्न सक्छौं।

मैले पहिले नै भनेझैं, अपडेटले केवल तालिकाहरू अद्यावधिक गर्दैन, यसले अनुक्रमणिकाहरू पनि अद्यावधिक गर्दैछ। तदनुसार, यदि हामीसँग तालिकामा धेरै अनुक्रमणिकाहरू छन्, तब तालिकामा पङ्क्तिहरू अद्यावधिक गर्दा, अनुक्रमित क्षेत्रहरूको अनुक्रमणिकाहरू पनि अद्यावधिक गर्न आवश्यक छ, र यदि हामीसँग अप्रयुक्त अनुक्रमणिकाहरू छन् जसको लागि कुनै अनुक्रमणिका स्क्यानहरू छैनन् भने, तिनीहरू हामीसँग गिट्टीको रूपमा झुण्डिएका छन्। र तपाईंले तिनीहरूलाई छुटकारा पाउन आवश्यक छ। यसका लागि हामीलाई फिल्ड चाहिन्छ idx_scan। हामी केवल अनुक्रमणिका स्क्यानहरूको संख्या हेर्छौं। यदि अनुक्रमणिकाहरूसँग तुलनात्मक रूपमा लामो अवधिको तथ्याङ्क भण्डारण (कम्तिमा 2-3 हप्ता) मा शून्य स्क्यानहरू छन्, तब प्रायः सम्भवतः यी खराब अनुक्रमणिकाहरू हुन्, हामीले तिनीहरूलाई हटाउन आवश्यक छ।

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

दुई लिङ्कहरू:

https://github.com/dataegret/pg-utils/blob/master/sql/low_used_indexes.sql

http://www.databasesoup.com/2014/05/new-finding-unused-indexes-query.html

यी अप्रयुक्त अनुक्रमणिकाहरू कसरी हेर्ने भनेर थप उन्नत क्वेरी उदाहरणहरू हुन्।

दोस्रो लिङ्क एक बरु रोचक प्रश्न हो। त्यहाँ एक धेरै गैर-तुच्छ तर्क छ। म यसलाई समीक्षाको लागि सिफारिस गर्दछु।

अनुक्रमणिका द्वारा अरू के संक्षेप गर्नुपर्छ?

• प्रयोग नगरिएका अनुक्रमणिकाहरू खराब छन्।

• तिनीहरूले ठाउँ लिन्छन्।

• अद्यावधिक कार्यहरू सुस्त गर्नुहोस्।

• वैक्यूमको लागि अतिरिक्त काम।

यदि हामीले प्रयोग नगरिएका अनुक्रमणिकाहरू हटायौं भने, हामी केवल डाटाबेसलाई अझ राम्रो बनाउनेछौं।

अर्को दृश्य हो pg_stat_activity। यो उपयोगिता को एक एनालॉग छ ps, PostgreSQL मा मात्र। यदि ps'ओम तपाईले अपरेटिङ सिस्टममा प्रक्रियाहरू हेर्नुहुन्छ, त्यसपछि pg_stat_activity तपाईंलाई PostgreSQL भित्रको गतिविधि देखाउनेछ।

हामी त्यहाँबाट के लिन सक्छौं?

select
count(*)*100/(select current_setting('max_connections')::int)
from pg_stat_activity;

हामी डाटाबेसमा भइरहेको समग्र गतिविधि देख्न सक्छौं। हामी नयाँ परिनियोजन गर्न सक्छौं। त्यहाँ सबै विस्फोट भयो, नयाँ जडानहरू स्वीकार गरिँदैन, त्रुटिहरू एप्लिकेसनमा खन्याइरहेका छन्।

select
client_addr, usename, datname, count(*)
from pg_stat_activity group by 1,2,3 order by 4 desc;

हामी यस प्रकारको क्वेरी चलाउन सक्छौं र अधिकतम जडान सीमासँग सम्बन्धित जडानहरूको कुल प्रतिशत हेर्न सक्छौं र हामीसँग सबैभन्दा बढी जडानहरू कोसँग छन् भनेर हेर्न सक्छौं। र यो दिइएको अवस्थामा, हामी त्यो प्रयोगकर्ता देख्छौं cron_role 508 जडान खोलियो। र उसलाई केहि भयो। तपाईंले यसलाई व्यवहार गर्न र हेर्न आवश्यक छ। र यो धेरै सम्भव छ कि यो जडान को एक प्रकार को असामान्य संख्या हो।

यदि हामीसँग OLTP लोड छ भने, प्रश्नहरू छिटो, धेरै छिटो हुनुपर्छ, र त्यहाँ लामो प्रश्नहरू हुनु हुँदैन। यद्यपि, यदि त्यहाँ लामो अनुरोधहरू छन् भने, त्यसपछि छोटो अवधिमा चिन्ताको लागि केहि छैन, तर लामो समयको लागि, लामो प्रश्नहरूले डाटाबेसलाई हानि पुर्‍याउँछ, तिनीहरूले तालिकाको ब्लोट प्रभाव बढाउँछन् जब तालिका विखण्डन हुन्छ। दुवै ब्लोट र लामो प्रश्नहरू समाधान गर्न आवश्यक छ।

select
client_addr, usename, datname,
clock_timestamp() - xact_start as xact_age,
clock_timestamp() - query_start as query_age,
query
from pg_stat_activity order by xact_start, query_start;

कृपया ध्यान दिनुहोस्: यस्तो अनुरोधको साथ, हामी लामो अनुरोध र लेनदेन परिभाषित गर्न सक्छौं। हामी प्रकार्य प्रयोग गर्छौं clock_timestamp() काम गर्ने समय निर्धारण गर्न। हामीले फेला पारेका लामो अनुरोधहरू, हामी तिनीहरूलाई सम्झन सक्छौं, कार्यान्वयन गर्न सक्छौं explain, योजनाहरू हेर्नुहोस् र कुनै न कुनै रूपमा अनुकूलन गर्नुहोस्। हामी हालको लामो अनुरोधहरू शूट गर्छौं र लाइभ गर्छौं।

select * from pg_stat_activity where state in
('idle in transaction', 'idle in transaction (aborted)';

खराब लेनदेनहरू लेनदेनमा निष्क्रिय र लेनदेनमा निष्क्रिय (रद्द गरिएको) लेनदेनहरू छन्।

यसको मतलब के हो? लेनदेनको धेरै राज्यहरू छन्। र यी मध्ये एक राज्यले कुनै पनि समयमा लिन सक्छ। राज्यहरू परिभाषित गर्ने क्षेत्र छ state यस दृश्यमा। र हामी यसलाई राज्य निर्धारण गर्न प्रयोग गर्छौं।

select * from pg_stat_activity where state in
('idle in transaction', 'idle in transaction (aborted)';

र, मैले माथि भनेझैं, यी दुई राज्यहरू लेनदेनमा निष्क्रिय र लेनदेनमा निष्क्रिय (रद्द गरिएको) खराब हो। यो के हो? यो तब हुन्छ जब एप्लिकेसनले लेनदेन खोल्यो, केही कार्यहरू गर्यो र यसको व्यापारको बारेमा जान्छ। कारोबार खुला रहन्छ। यो ह्याङ्ग हुन्छ, यसमा केहि हुँदैन, यसले जडान लिन्छ, परिवर्तन गरिएका पङ्क्तिहरूमा लक गर्दछ र सम्भावित रूपमा अझै पनि अन्य तालिकाहरूको ब्लोट बढाउँछ, पोस्टर्जेस लेनदेन इन्जिनको वास्तुकलाको कारण। र त्यस्ता लेनदेनहरूलाई पनि गोली हान्नु पर्छ, किनकि तिनीहरू कुनै पनि अवस्थामा सामान्य रूपमा हानिकारक छन्।

यदि तपाइँ तपाइँको डाटाबेसमा ती मध्ये 5-10-20 भन्दा बढी देख्नुहुन्छ भने, तपाइँ चिन्तित हुन आवश्यक छ र तिनीहरूसँग केहि गर्न सुरु गर्न आवश्यक छ।

यहाँ हामी गणना समयको लागि पनि प्रयोग गर्छौं clock_timestamp()। हामी लेनदेन शूट गर्छौं, हामी अनुप्रयोगलाई अनुकूलन गर्छौं।

मैले माथि भनेझैं, लकहरू जब दुई वा बढी लेनदेनहरू एक वा स्रोतहरूको समूहको लागि प्रतिस्पर्धा गर्छन्। यसका लागि हामीसँग फिल्ड छ waiting बुलियन मूल्य संग true वा false.

साँचो - यसको मतलब यो प्रक्रिया पर्खिरहेको छ, केहि गर्न आवश्यक छ। जब एक प्रक्रिया पर्खिरहेको छ, तब प्रक्रिया सुरु गर्ने ग्राहक पनि पर्खिरहेको छ। ब्राउजरमा ग्राहक बस्छ र पर्खन्छ।

चेतावनी: _ Postgres 9.6 बाट सुरु गर्दै, क्षेत्र waiting हटाइयो र दुई थप जानकारीमूलक क्षेत्रहरु द्वारा प्रतिस्थापित wait_event_type и wait_event._

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

र एक चरम, तर सम्भावित घातक मामला होइन गतिरोध को घटना। दुई लेनदेनहरूले दुई स्रोतहरू अद्यावधिक गरेका छन्, त्यसपछि तिनीहरूले तिनीहरूलाई फेरि पहुँच गर्छन्, पहिले नै विपरीत स्रोतहरूमा। यस अवस्थामा PostgreSQL ले लेनदेन आफैलाई बन्द गर्छ र अर्कोले काम जारी राख्न सक्छ। यो अन्तिम अवस्था हो र उसले आफैलाई बुझ्दैन। त्यसैले, PostgreSQL चरम उपायहरू लिन बाध्य छ।

https://github.com/lesovsky/uber-scripts/blob/master/postgresql/sql/c4_06_show_locked_queries.sql

https://github.com/lesovsky/uber-scripts/blob/master/postgresql/sql/show_locked_queries_95.sql

https://github.com/lesovsky/uber-scripts/blob/master/postgresql/sql/show_locked_queries_96.sql

http://big-elephants.com/2013-09/exploring-query-locks-in-postgres/

र यहाँ दुई प्रश्नहरू छन् जसले तपाईंलाई लकहरू ट्र्याक गर्न अनुमति दिन्छ। हामी दृश्य प्रयोग गर्छौं pg_locks, जसले तपाईंलाई भारी तालाहरू ट्र्याक गर्न अनुमति दिन्छ।

र पहिलो लिङ्क अनुरोध पाठ हो। यो धेरै लामो छ।

र दोस्रो लिङ्क लकहरूमा लेख हो। यो पढ्न उपयोगी छ, यो धेरै रोचक छ।

त्यसोभए हामी के देख्छौं? हामी दुईवटा अनुरोधहरू देख्छौं। संग लेनदेन ALTER TABLE अवरुद्ध लेनदेन हो। यो सुरु भयो, तर अन्त्य भएन, र यो लेनदेन पोस्ट गर्ने अनुप्रयोगले कतै अन्य चीजहरू गरिरहेको छ। र दोस्रो अनुरोध अपडेट हो। यसले आफ्नो काम जारी राख्नु अघि परिवर्तन तालिका समाप्त हुनको लागि पर्खन्छ।

यसरी हामी कसले कसलाई तालाबन्दी गरेको छ, कसले कसलाई समातेको छ भनेर पत्ता लगाउन सक्छौं र हामी यसलाई अगाडि बढाउन सक्छौं।

अर्को मोड्युल हो pg_stat_statements। मैले भने जस्तै, यो एक मोड्युल हो। यसलाई प्रयोग गर्न, तपाईंले कन्फिगरेसनमा यसको पुस्तकालय लोड गर्न आवश्यक छ, PostgreSQL पुन: सुरु गर्नुहोस्, मोड्युल स्थापना गर्नुहोस् (एउटा आदेशको साथ), र त्यसपछि हामीसँग नयाँ दृश्य हुनेछ।

Cреднее время запроса в милисекундах
$ select (sum(total_time) / sum(calls))::numeric(6,3)
from pg_stat_statements;

Самые активно пишущие (в shared_buffers) запросы
$ select query, shared_blks_dirtied
from pg_stat_statements
where shared_blks_dirtied > 0 order by 2 desc;

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

हामी डाटाबेसमा सबैभन्दा सक्रिय लेखन लेनदेनहरू देख्न सक्छौं जसले साझा बफरहरूमा डाटा परिवर्तन गर्दछ। त्यहाँ कसले डाटा अपडेट गर्छ वा मेटाउँछ हेर्नुहोस्।

र हामी यी प्रश्नहरूको लागि विभिन्न तथ्याङ्कहरू हेर्न सक्छौं।

https://github.com/dataegret/pg-utils/blob/master/sql/global_reports/query_stat_total.sql

हामी pg_stat_statements रिपोर्टहरू निर्माण गर्न प्रयोग गरिन्छ। हामी तथ्याङ्कहरू दिनमा एक पटक रिसेट गर्छौं। यसलाई जम्मा गरौं। अर्को पटक तथ्याङ्कहरू रिसेट गर्नु अघि, हामी एउटा रिपोर्ट बनाउँछौं। यहाँ रिपोर्टको लिङ्क छ। तपाईं यसलाई हेर्न सक्नुहुन्छ।

हामी के गर्दै छौ? हामी सबै अनुरोधहरूको लागि समग्र तथ्याङ्क गणना गर्छौं। त्यसपछि, प्रत्येक प्रश्नको लागि, हामी यस समग्र तथ्याङ्कमा यसको व्यक्तिगत योगदान गणना गर्छौं।

र हामी के देख्न सक्छौं? हामी अन्य सबै अनुरोधहरूको पृष्ठभूमिमा एक विशेष प्रकारका सबै अनुरोधहरूको कुल कार्यान्वयन समय देख्न सक्छौं। हामी समग्र तस्वीरको सम्बन्धमा CPU र I/O प्रयोग हेर्न सक्छौं। र पहिले नै यी अनुरोधहरू अनुकूलन गर्न। हामी यस रिपोर्टको आधारमा शीर्ष प्रश्नहरू निर्माण गर्दैछौं र के अनुकूलन गर्ने भनेर सोच्नको लागि पहिले नै खाना पाउँदैछौं।

हामीसँग पर्दा पछाडि के छ? त्यहाँ अझै केही सबमिशनहरू छन् जुन मैले विचार गरेन, किनभने समय सीमित छ।

छन् pgstattuple मानक योगदान प्याकेजबाट एक अतिरिक्त मोड्युल पनि हो। यसले तपाईंलाई मूल्याङ्कन गर्न अनुमति दिन्छ bloat टेबल, तथाकथित। तालिका विखंडन। र यदि विखंडन ठूलो छ भने, तपाईंले यसलाई हटाउन आवश्यक छ, विभिन्न उपकरणहरू प्रयोग गर्नुहोस्। र प्रकार्य pgstattuple लामो समय काम गर्दछ। र अधिक तालिकाहरू, लामो समय यो काम गर्नेछ।

अर्को योगदान हो pg_buffercache। यसले तपाइँलाई साझा बफरहरू निरीक्षण गर्न अनुमति दिन्छ: कसरी गहन रूपमा र कुन टेबल बफर पृष्ठहरू प्रयोग गरिन्छ। र यसले तपाईंलाई साझा बफरहरू हेर्न र त्यहाँ के भइरहेको छ भनेर मूल्याङ्कन गर्न अनुमति दिन्छ।

अर्को मोड्युल हो pgfincore। यसले तपाइँलाई प्रणाली कल मार्फत निम्न-स्तरको तालिका सञ्चालन गर्न अनुमति दिन्छ mincore(), अर्थात् यसले तपाइँलाई साझा बफरहरूमा तालिका लोड गर्न वा अनलोड गर्न अनुमति दिन्छ। र यसले, अन्य चीजहरू बीच, अपरेटिङ सिस्टमको पृष्ठ क्यास निरीक्षण गर्न अनुमति दिन्छ, त्यो हो, पृष्ठ क्यासमा तालिकाले कति ओगटेको छ, साझा बफरहरूमा, र केवल तपाइँलाई टेबलमा लोड मूल्याङ्कन गर्न अनुमति दिन्छ।

अर्को मोड्युल हो pg_stat_kcache। यसले प्रणाली कल पनि प्रयोग गर्दछ getrusage()। र अनुरोध कार्यान्वयन हुनु अघि र पछि यसलाई कार्यान्वयन गर्दछ। र प्राप्त तथ्याङ्कहरूमा, यसले हामीलाई हाम्रो अनुरोधले डिस्क I / O मा खर्च गरेको अनुमान गर्न अनुमति दिन्छ, अर्थात्, फाइल प्रणालीसँग सञ्चालन र प्रोसेसरको प्रयोगलाई हेर्छ। यद्यपि, मोड्युल जवान छ (खे-खे) र यसको कामको लागि यसलाई PostgreSQL 9.4 र pg_stat_statements चाहिन्छ, जुन मैले पहिले उल्लेख गरेको छु।

• तथ्याङ्क प्रयोग गर्ने क्षमता उपयोगी छ। तपाईंलाई तेस्रो पक्ष सफ्टवेयरको आवश्यकता पर्दैन। तपाईं हेर्न सक्नुहुन्छ, हेर्न सक्नुहुन्छ, केहि गर्न सक्नुहुन्छ, प्रदर्शन गर्न सक्नुहुन्छ।

• तथ्याङ्कहरू प्रयोग गर्न सजिलो छ, यो सादा SQL हो। तपाईंले अनुरोध सङ्कलन गर्नुभयो, यसलाई कम्पाइल गर्नुभयो, पठाउनुभयो, यसलाई हेर्नुभयो।

• तथ्याङ्कहरूले प्रश्नहरूको जवाफ दिन मद्दत गर्छ। यदि तपाइँसँग प्रश्नहरू छन् भने, तपाइँ तथ्याङ्कहरूमा फर्कनुहोस् - हेर्नुहोस्, निष्कर्षहरू कोर्नुहोस्, परिणामहरू विश्लेषण गर्नुहोस्।

• र प्रयोग। धेरै अनुरोध, धेरै डाटा। तपाइँ जहिले पनि केहि अवस्थित क्वेरीलाई अनुकूलन गर्न सक्नुहुन्छ। तपाईले अनुरोधको आफ्नै संस्करण बनाउन सक्नुहुन्छ जुन तपाईलाई मूल भन्दा राम्रो सुहाउँछ र यसलाई प्रयोग गर्नुहोस्।

सन्दर्भ

लेखमा फेला परेका मान्य लिङ्कहरू, जसको आधारमा, रिपोर्टमा थिए।

लेखक थप लेख्नुहोस्
https://dataegret.com/news-blog (eng)

तथ्याङ्क संकलनकर्ता
https://www.postgresql.org/docs/current/monitoring-stats.html

प्रणाली प्रशासन कार्यहरू
https://www.postgresql.org/docs/current/functions-admin.html

योगदान मोड्युलहरू
https://www.postgresql.org/docs/current/pgstatstatements.html
https://www.postgresql.org/docs/current/pgstattuple.html
https://www.postgresql.org/docs/current/pgbuffercache.html
https://github.com/klando/pgfincore
https://github.com/dalibo/pg_stat_kcache

SQL उपयोगिताहरू र sql कोड उदाहरणहरू
https://github.com/dataegret/pg-utils

तपाईंको ध्यानको लागि सबैलाई धन्यवाद!

स्रोत: www.habr.com