💽 😴 🤖 PostgreSQL: DBA के लिए एनालिटिक्स ☂️ 🎟️ 💘

कई PostgreSQL DBMS उपयोगकर्ताओं को पता है कि सर्वर अपने ऑपरेशन के दौरान कई तरह के आंकड़े एकत्र करता है, लेकिन हर कोई नहीं जानता कि इसका विश्लेषण करना और इसके लिए इसे कैसे निकालना उपयोगी है। इस छोटे टूलकिट में कई उपयोगी प्रश्न हैं जो इस "छिपे हुए ज्ञान" का उपयोग करने के बारे में कुछ जानकारी प्रदान करते हैं जो लगातार जमा हो रहा है। इन प्रश्नों का उपयोग PostgreSQL की स्थिति की निगरानी करने के लिए किया जा सकता है (मैन्युअल रूप से या Nagios, Cacti या Zabbix जैसे मॉनिटरिंग सिस्टम के लिए प्लग-इन का उपयोग करके), सर्वर में अड़चन खोजने के लिए, और कई अन्य समान कार्य। याद रखें कि यह सिर्फ हिमशैल का टिप है; प्रलेखन में आप कई दर्जन सिस्टम दृश्यों के विवरण पा सकते हैं, जो पोस्टग्रेक्यूएल व्यवस्थापक के लिए भी उपयोगी हो सकते हैं।

टूलकिट को सही तरीके से काम करने के लिए, आपको पोस्टग्रैसेक्ल्.कॉन्फ़ में आँकड़े_ब्लॉक_लेवल और स्टेटिस्टिक्स_रोवल विकल्प को सक्षम करना होगा, साथ ही साथ आप अपनी इच्छानुसार आंकड़े_सेसेट_ओन_सर्वर_स्टार्ट पैरामीटर को कॉन्फ़िगर करें। यदि PostgreSQL सर्वर के प्रत्येक पुनरारंभ पर आप इसके कॉन्फ़िगरेशन के कुछ आवश्यक मापदंडों को बदलते हैं, तो यह किए गए परिवर्तनों के प्रभाव को ट्रैक करने के लिए आंकड़ों को रीसेट करने के लिए समझ में आता है। यदि आप लॉन्ग टर्म में रुचि रखते हैं और पोस्टग्रेएसक्यूएल में कॉन्फ़िगरेशन परिवर्तन के कारण रिस्टार्ट नहीं है, तो Analytics_reset_on_server_start पैरामीटर सेट करें।

हिट / रीड रेशियो

क्वेरी निष्पादित करते समय, PostgreSQL पहली बार यह देखता है कि क्या क्वेरी में साझा मेमोरी (साझा बफ़र्स) में डेटा है या नहीं। यदि वे पाए जाते हैं, तो हिट को गिना जाता है, यदि नहीं, तो डिस्क से या ऑपरेटिंग सिस्टम के डिस्क कैश से डेटा उठाने के लिए एक अपेक्षाकृत धीमी फ़्रेड सिस्टम कॉल की जाती है और रीड को गिना जाता है। औसतन, नियम सही है: हिट / रीड रेशियो जितना अधिक होगा, बेहतर पोस्टग्रेएसक्यूएल कॉन्फ़िगर किया गया है, क्योंकि यह डिस्क से बहुत कम पढ़ता है, मुख्य रूप से साझा मेमोरी से डेटा पुनर्प्राप्त करता है। अधिकांश बहुत बड़े डेटाबेस के लिए, यह अनुपात 5000-10000 की सीमा में होना चाहिए। हालाँकि, शेयर्ड_बफर्स सेटिंग को कृत्रिम रूप से अधिक करने की कोशिश न करें, जो सीधे हिट / रीड को निर्धारित करता है: बहुत बड़ी साझा मेमोरी साइज से गहन रिकॉर्डिंग डेटाबेस में प्रदर्शन का नुकसान होता है। यह भी याद रखने योग्य है कि ओएस डिस्क कैश में डेटा होने पर भय काफी तेज हो सकता है।

  का चयन करें 
       datname, 
       मामला 
         जब blks_read = 0 तब 0 
         ELSE blks_hit / blks_read 
       अंत में अनुपात 
     से 
       pg_stat_database;

तालिका में हुए संशोधनों की संख्या

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

     का चयन करें 
       relname, 
       n_tup_ins, 
       n_tup_upd, 
       n_tup_del 
     से 
       pg_stat_user_tables 
     आदेश द्वारा 
       n_tup_upd DESC;

Seq स्कैन / सूचकांक स्कैन आँकड़े

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

इसके अलावा, यह मत भूलो कि सूचकांक बनाने के बाद, तालिका को ANALYZE होने की आवश्यकता है, अन्यथा क्वेरी योजनाकार तालिका संरचना में परिवर्तन को नोटिस नहीं करेगा।

  का चयन करें 
       relname, 
       seq_scan, 
       idx_scan, 
       मामला 
         जब भी idx_scan = 100 हो 
         ELSE seq_scan / idx_scan 
       अंत में अनुपात 
     से 
       pg_stat_user_tables 
     आदेश द्वारा 
       अनुपात DESC;

सूचकांक सांख्यिकी

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

  का चयन करें 
       indexrelname, 
       idx_tup_read, 
       idx_tup_fetch, 
       मामला 
         कब idx_tup_fetch = 100 पर 0 
         ELSE idx_tup_read / idx_tup_fetch 
       अंत में अनुपात 
     से 
       pg_stat_user_indexes 
     आदेश द्वारा 
       अनुपात DESC;

उनकी अवधि के साथ अनुरोध चल रहा है

वर्तमान में चल रहे सर्वर अनुरोधों की एक सरल सूची। यह उपयोगी है जब आप सिस्टम को अच्छी तरह से नहीं जानते हैं या बस इसे कॉन्फ़िगर करने का प्रबंधन नहीं किया है - इसका उपयोग "खराब" अनुरोध को खोजने और बाधित करने के लिए किया जा सकता है जो सर्वर के साथ हस्तक्षेप करता है (procpid कॉलम में प्रक्रिया का PID होता है, जिसे आवश्यक होने पर मार दिया जा सकता है)। हालाँकि, याद रखें कि बस समय-समय पर चल रहे प्रश्नों की समीक्षा करना किसी भी तरह से महान pgFouine लॉग विश्लेषक की जगह नहीं लेगा। इसके अलावा, यह मत भूलो कि जिस प्रक्रिया में आप इस अनुरोध को निष्पादित करते हैं, वह भी परिणामी सूची में आती है।

  का चयन करें 
       datname, 
       अब () - query_start AS अवधि, 
       procpid, 
       current_query	
     से
       pg_stat_activity 
     ORDER BY अवधि DESC;

वर्तमान तालों की सूची

वर्तमान लॉक की सूची के साथ लॉक का प्रकार, तालिका और डेटाबेस जिस पर इसे सेट किया गया है और लेन-देन संख्या जो लॉक सेट करती है। यदि अनुरोध ताले की लंबी सूची देता है तो डरो मत - उनमें से सभी महत्वपूर्ण नहीं हैं और सभी संभावित परिवर्तनों से तालिका को अवरुद्ध करते हैं और यहां तक कि पढ़ते हैं। तालों की सूची का विश्लेषण करने के लिए, आपको निश्चित रूप से डाकग्रेएसक्यूएल में उनके प्रकारों के बारे में प्रलेखन के साथ परिचित होना चाहिए और उनके बारे में कब और क्या प्रश्न जारी किए जाते हैं। एक सामान्य उपयोग मामला जब एक लॉक सूची काम में आ सकती है: ps aux चलाने से | grep ^ पोस्टग्रेज आप देखते हैं कि पोस्टग्रेज प्रक्रियाओं में से एक की स्थिति प्रतीक्षा कहती है - इसका मतलब यह है कि यह प्रक्रिया उस लॉक की प्रतीक्षा कर रही है जिसे इसे हटाने की आवश्यकता है। कौन सा - यह क्वेरी चलाकर पता करें।

  का चयन करें 
       l.mode, 
       d.datname, 
       c.relname, 
       l.granted, 
       l.transactionid 
     से 
       pg_locks AS l 
     LEFT JOIN pg_database as d d l.database = d.oid पर लिखें 
     LEFT JOIN pg_class as c on l.relation = c.oid;

PostgreSQL: DBA के लिए एनालिटिक्स