👩🏿‍🤝‍👩🏾 👩‍🍳 📋 विभाजन और अनुक्रमण के साथ Zabbix + PostgreSQL प्रदर्शन में सुधार 👩‍👦 👨‍👩‍👦 🍳

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

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

मैं इस लेख में किए गए कार्यों के परिणामों को साझा करना चाहता हूं। यह लेख Zabbix और PostgreSQL DBA प्रशासकों, साथ ही उन सभी के लिए उपयोगी होगा जो लोकप्रिय PosgreSQL DBMS को बेहतर ढंग से समझना और समझना चाहते हैं।

एक छोटा स्पॉइलर: प्रति मिनट 200 हजार मापदंडों के भार के साथ एक कमजोर मशीन पर, हमने सीपीयू आयोवाइट को 20% से 2% तक कम करने में कामयाब रहा, प्राथमिक डेटा तालिकाओं में भागों में रिकॉर्डिंग समय को 250 गुना तक कम कर दिया और 32 बार डेटा तालिकाओं को कम करके, अनुक्रमित के आकार को कम कर दिया। 5-10 बार और 18 बार तक कुछ मामलों में ऐतिहासिक नमूनों की प्राप्ति में तेजी लाएं।

लोड परीक्षण

लोड परीक्षण योजना के अनुसार किया गया था: एक ज़ैबिक्स सर्वर, एक सक्रिय ज़ैबिक्स प्रॉक्सी, दो एजेंट। प्रत्येक एजेंट को प्रति मिनट 50 टन पूर्णांक और 50 टन स्ट्रिंग पैरामीटर (प्रति मिनट 200 टन पैरामीटर या प्रति सेकंड 3333 पैरामीटर) देने के लिए कॉन्फ़िगर किया गया था। एजेंट पैरामीटर उत्पन्न करने के लिए हमने Zabbix के लिए प्लग-इन का उपयोग किया । यह जांचने के लिए कि एक एजेंट कितने पैरामीटर उत्पन्न कर सकता है, आपको उसी प्लग-इन लेखक zabbix_module_stress से एक विशेष स्क्रिप्ट का उपयोग करने की आवश्यकता है। Zabbix वेब-व्यवस्थापक को बड़े टेम्पलेट को पंजीकृत करने में कठिनाइयाँ होती हैं, इसलिए हमने मापदंडों को 5 टन मापदंडों (2500 संख्यात्मक और 2500 स्ट्रिंग) के साथ 20 टेम्पलेट्स में विभाजित किया है।

अजगर में लोड परीक्षण के लिए स्क्रिप्ट जनरेटर टेम्पलेट

import argparse """     .   20   5000    ( 2500  :  echo,  ;  ping,  ) """ TEMP_HEAD = """ <?xml version="1.0" encoding="UTF-8"?> <zabbix_export> <version>2.0</version> <date>2015-08-17T23:15:01Z</date> <groups> <group> <name>Templates</name> </group> </groups> <templates> <template> <template>Template Zabbix Srv Stress {count} passive {char}</template> <name>Template Zabbix Srv Stress {count} passive {char}</name> <description/> <groups> <group> <name>Templates</name> </group> </groups> <applications/> <items> """ TEMP_END = """</items> <discovery_rules/> <macros/> <templates/> <screens/> </template> </templates> </zabbix_export> """ TEMP_ITEM = """<item> <name>{k}</name> <type>0</type> <snmp_community/> <multiplier>0</multiplier> <snmp_oid/> <key>{k}</key> <delay>1m</delay> <history>3</history> <trends>365</trends> <status>0</status> <value_type>{t}</value_type> <allowed_hosts/> <units/> <delta>0</delta> <snmpv3_contextname/> <snmpv3_securityname/> <snmpv3_securitylevel>0</snmpv3_securitylevel> <snmpv3_authprotocol>0</snmpv3_authprotocol> <snmpv3_authpassphrase/> <snmpv3_privprotocol>0</snmpv3_privprotocol> <snmpv3_privpassphrase/> <formula>1</formula> <delay_flex/> <params/> <ipmi_sensor/> <data_type>0</data_type> <authtype>0</authtype> <username/> <password/> <publickey/> <privatekey/> <port/> <description/> <inventory_link>0</inventory_link> <applications/> <valuemap/> <logtimefmt/> </item> """ TMP_FNAME_DEFAULT = "Template_App_Zabbix_Server_Stress_{count}_passive_{char}.xml" chars = "ABCDEFGHIJKLMNOPQRSTUVWXYZ" if __name__ == "__main__": parser = argparse.ArgumentParser( description='     zabbix') parser.add_argument('--items', dest='items', type=int, default=1000, help='-   (default: 1000)') parser.add_argument('--templates', dest='templates', type=int, default=1, help=f'-  [1-{len(chars)}] (default: 1)') args = parser.parse_args() items_count = args.items tmps_count = args.templates if not (tmps_count >= 1 and tmps_count <= len(chars)): sys.exit(f"Templates must be in range 1 - {len(chars)}") for i in range(tmps_count): fname = TMP_FNAME_DEFAULT.format(count=items_count, char=chars[i]) with open(fname, "w") as output: output.write(TEMP_HEAD.format(count=items_count, char=chars[i])) for k,t in [('stress.ping[{}-I-{:06d}]',3), ('stress.echo[{}-S-{:06d}]',4)]: for j in range(int(items_count/2)): output.write(TEMP_ITEM.format(k=k.format(chars[i],j),t=t)) output.write(TEMP_END)

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

यहाँ सीपीयू इवाइट मेट्रिक के लिए चित्र है जो हमें तीन दिन बाद शुरू में मिला:

लेकिन एक ही मेट्रिक के लिए जो चित्र हमें भी मिला वह तीन दिनों के भीतर अंत में सभी अनुकूलन उपायों के बाद किया गया है, जिनके बारे में नीचे चर्चा की जाएगी:

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

Zabbix प्रदर्शन ड्रॉप के कारण

प्राथमिक डेटा की प्रत्येक तालिका में 10 मिलियन से अधिक पैरामीटर मानों के संचय के साथ, यह देखा गया कि मॉनिटरिंग सिस्टम का प्रदर्शन निम्न कारणों से तेजी से गिरता है:

सर्वर सीपीयू के लिए iowait मीट्रिक 20% से अधिक की वृद्धि हुई है, जो उस समय में वृद्धि को इंगित करता है जिसके दौरान सीपीयू डिस्क पढ़ने और लिखने के संचालन तक पहुंच की उम्मीद करता है।
तालिकाओं की अनुक्रमणिका जिसमें डेटा की निगरानी बहुत बढ़ जाती है
निगरानी डेटा के साथ डिस्क के लिए उपयोग मीट्रिक 100% तक बढ़ा दिया गया है, जो पढ़ने और लिखने के संचालन के साथ डिस्क के पूर्ण लोड को इंगित करता है
हाउसकीपर के शेड्यूल के अनुसार सफाई करते समय अप्रचलित मानों को हिस्ट्री टेबल से हटाने का समय नहीं है

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

Zabbix में मॉनिटरिंग डेटा वेयरहाउस के आयोजन पर थोड़ी मदद। यह प्राथमिक डेटा और अलग-अलग तालिकाओं में डेटा एकत्र करता है, इसके अलावा, मापदंडों के प्रकारों के पृथक्करण के साथ। प्रत्येक तालिका एक आइटमिड फ़ील्ड (सिस्टम में पंजीकृत डेटा आइटम का एक अंतर्निहित संदर्भ), यूनिक्स टाइमस्टैम्प प्रारूप (एक अलग कॉलम में मिलीसेकंड) और एक अलग कॉलम में एक मान दर्ज करने के लिए एक टाइमस्टैम्प स्टोर करती है (अपवाद लॉग टेबल है, इसमें अधिक फ़ील्ड हैं - यह ईवेंट लॉग की तरह है ):

टेबल का नाम	नियुक्ति	डेटा प्रकार
इतिहास	प्राथमिक निगरानी डेटा	संख्यात्मक (16.4)
history_uint	प्राथमिक निगरानी डेटा	संख्यात्मक (20.0)
history_str	प्राथमिक निगरानी डेटा	वर्चर (255)
history_text	प्राथमिक निगरानी डेटा	टेक्स्ट
history_logs	प्राथमिक निगरानी डेटा	पाठ और int फ़ील्ड
प्रवृत्तियों	एकत्रित निगरानी डेटा	संख्यात्मक (16.4)
trends_uint	एकत्रित निगरानी डेटा	संख्यात्मक (20.0)

अनुकूलन क्रियाएँ

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

महत्वपूर्ण नोट। लेख की सामग्री एकत्र करने के समय, हमने ज़ैबिक्स संस्करण 4.0 का उपयोग किया था, हालांकि संस्करण 4.2 पहले ही जारी किया जा चुका है और संस्करण 4.4 को रिलीज़ के लिए तैयार किया जा रहा है। इसका उल्लेख करना क्यों महत्वपूर्ण है? संस्करण 4.2 से शुरू होने के कारण, ज़ैबिक्स ने TimescaleDB टाइम सीरीज़ के साथ काम करने के लिए एक विशेष शक्तिशाली विस्तार का समर्थन करना शुरू कर दिया, लेकिन अब तक प्रायोगिक मोड में: इस एक्सटेंशन का उपयोग करने के सभी लाभों के लिए, यह माना जाता है कि कुछ अनुरोध अधिक धीमी गति से काम करना शुरू कर चुके हैं और अभी भी अनसुलझी प्रदर्शन समस्याएं हैं (वहाँ होगा) 4.4 संस्करण में हल) - इस लेख को पढ़ें । अगले लेख में मैं इस समाधान के मामले की तुलना में TimescaleDB एक्सटेंशन का उपयोग करके लोड परीक्षण के परिणामों के बारे में लिखने की योजना बना रहा हूं। PostgreSQL संस्करण 10 का उपयोग किया गया था, लेकिन दी गई सभी जानकारी 11 और 12 संस्करणों (हम इंतजार कर रहे हैं!) के लिए प्रासंगिक है।

इसलिए, पहली चीजें पहले:

pgtune उपयोगिता का उपयोग करके कॉन्फ़िगरेशन फ़ाइल सेट करना
डेटाबेस को एक अलग भौतिक डिस्क पर रखना
pg_pathman के साथ इतिहास सारणी का विभाजन
इतिहास सारणी के बदलते प्रकारों को ब्रिन (घड़ी) और बीटी-जिन (आइटम) के लिए बदलना
क्वेरी निष्पादन आंकड़ों का संग्रह और विश्लेषण pg_stat_statements
भौतिक डिस्क मॉनिटरिंग पैरामीटर सेट करना
हार्डवेयर प्रदर्शन में सुधार
वितरित क्लस्टर का निर्माण (इस लेख के दायरे से परे सामग्री)

Pgtune उपयोगिता का उपयोग करके कॉन्फ़िगरेशन फ़ाइल को कॉन्फ़िगर करना

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

काश, सभी DBMS को ट्यून करने के लिए कोई एकल सूत्र नहीं है, लेकिन कुछ नियम और पैटर्न हैं जो अधिकांश कॉन्फ़िगरेशन के लिए उपयुक्त हैं (महीन ट्यूनिंग पहले से ही एक विशेषज्ञ का काम है)। DBA के जीवन को सरल बनाने के लिए, pgtune उपयोगिता को लिखा गया था, जिसे PostgreSQL प्रशासन पर एक दिलचस्प और उपयोगी पुस्तक के लेखक le0pard द्वारा वेब संस्करण द्वारा पूरक किया गया था।

"डेटा वेयरहाउस" एप्लिकेशन के लिए कंसोल में उपयोगिता को 100 कनेक्शन (Zabbix में एक डिमांड वेब एडमिन) के साथ चलाने का एक उदाहरण है:

  pgtune -i postgresql.conf -o new_postgresql.conf -T DWT 100

कॉन्फ़िगरेशन पैरामीटर जो pgtune उपयोगिता उद्देश्य के विवरण के साथ बदलता है (मान एक उदाहरण के रूप में दिए गए हैं)

 # DB संस्करण: 11
 # OS प्रकार: लिनक्स
 # DB प्रकार: वेब
 # कुल मेमोरी (RAM): 8 जीबी
 # सीपीयू संख्या: 1
 # कनेक्शन संख्या: 100
 # डेटा स्टोरेज: hdd

 max_connections = 100 # समवर्ती डेटाबेस कनेक्शन की अधिकतम संख्या
 शेयर्ड मेमोरी में विभिन्न बफ़र्स (मुख्य रूप से टेबल ब्लॉक्स और इंडेक्स ब्लॉक का कैश) के लिए share_buffers = 2GB # मेमोरी साइज़
 प्रभावी_cache_size = 6GB # अनुक्रमित का उपयोग करके क्वेरी निष्पादन के लिए आवश्यक मेमोरी का अधिकतम आकार
 रखरखाव_वर्क_म = 512 एमबी # VACUUM, ANALYZE, CREATE INDEX संचालन की गति को प्रभावित करता है
 चेकपॉइंट प्रक्रिया पूरी करने के लिए checkpoint_completion_target = 0.7 # लक्ष्य समय
 wal_buffers = 16MB # मेमोरी की राशि जो लेन-देन लॉग को बनाए रखने के लिए साझा मेमोरी द्वारा उपयोग की जाती है
 Default_statistics_target = ANALYZE कमांड द्वारा एकत्र किए गए आँकड़ों की 100 # राशि - जब बढ़ती है, तो आशावादी अधिक धीरे-धीरे प्रश्न बनाता है, लेकिन बेहतर
 random_page_cost = 4 # डेटा पृष्ठों तक सूचकांक पहुंच की सशर्त लागत - सूचकांक के उपयोग के निर्णय को प्रभावित करती है
 प्रभावी_io_concurrency = 2 # अतुल्यकालिक I / O संचालन की संख्या जो DBMS एक अलग सत्र में प्रदर्शन करने की कोशिश करेगा
 work_mem = 10485kB # डिस्क पर अस्थायी फ़ाइलों का उपयोग करने से पहले छँटाई और हैश टेबल के लिए उपयोग की जाने वाली मेमोरी की मात्रा
 min_wal_size = 1GB # वाल फ़ाइलों की संख्या के नीचे की सीमाएँ जिन्हें भविष्य में उपयोग के लिए पुनर्नवीनीकरण किया जाएगा
 max_wal_size = 2GB # वाल फ़ाइलें जो भविष्य में उपयोग के लिए पुनर्नवीनीकरण की जाएंगी, की शीर्ष सीमा

कुछ उपयोगी postgresql विन्यास विकल्प

 # समवर्ती अनुरोध संचालकों का प्रबंधन
 max_worker_processes = 8 # पृष्ठभूमि प्रक्रियाओं की अधिकतम संख्या - प्रति डेटाबेस कम से कम एक
 max_parallel_workers_per_gather = 4 # एकल अनुरोध के भीतर समानांतर प्रक्रियाओं की अधिकतम संख्या
 max_parallel_workers = 8 # कार्य प्रक्रियाओं की अधिकतम संख्या जो सिस्टम समानांतर संचालन के लिए समर्थन कर सकता है

 # लॉगिंग सेटिंग्स (pg_stat_statements एक्सटेंशन का उपयोग किए बिना अनुरोधों के निष्पादन समय के बारे में जानने का एक आसान तरीका)
 log_min_duration_statement = 3000 # लॉग उन सभी कमांड्स के निष्पादन की अवधि को लिखते हैं जिनके संचालन का समय> = ms में निर्दिष्ट मान का =
 log_duration = off # प्रत्येक पूर्ण कमांड की अवधि रिकॉर्ड करें
 log_statement = 'कोई नहीं' # जो SQL कमांड को लॉग में लिखने के लिए, मान: कोई नहीं (अक्षम), ddl, mod और सभी (सभी कमांड)
 debug_print_plan = आगे के विश्लेषण के लिए क्वेरी प्लान ट्री का # आउटपुट

 # डेटाबेस से अधिकतम को निचोड़ें और किसी भी विफलता के लिए इसे तैयार रहें (सबसे दमित के लिए, जो एसएसडी और एक वितरित क्लस्टर के अस्तित्व की उपेक्षा करते हैं)
 #fsync = ऑफ # चेंज डिस्क में फिजिकल राइट, fsync को डिसेबल करने से स्पीड में तेजी आती है, लेकिन स्थायी विफलता हो सकती है
 #synchronous_commit = off # लेन-देन की जानकारी Wal में होने से पहले ही आपको क्लाइंट को जवाब देने की अनुमति देता है - fsync को अक्षम करने का लगभग सुरक्षित विकल्प
 #full_page_writes = off # शटडाउन सामान्य संचालन को गति देता है, लेकिन सिस्टम क्रैश होने पर डेटा भ्रष्टाचार या डेटा भ्रष्टाचार को जन्म दे सकता है

एक अलग भौतिक डिस्क पर एक डेटाबेस सूचीबद्ध करना

यह आइटम वैकल्पिक है और बल्कि पूरी तरह से वितरित क्लस्टर के लिए एक संक्रमणकालीन समाधान है, लेकिन इस संभावना के बारे में जानना उपयोगी होगा। डेटाबेस को गति देने के लिए, आप इसे एक अलग डिस्क पर रख सकते हैं। हमने पूरी डिस्क को आधार निर्देशिका में माउंट किया है, जहां सभी पोस्टग्रेक्यूएल डेटाबेस संग्रहीत हैं, लेकिन सामान्य तौर पर इसे अलग तरीके से किया जा सकता है: एक नया टेबलबेस बनाएं और डेटाबेस को स्थानांतरित करें (या इसका केवल एक हिस्सा - एक अलग डिस्क पर इस टेबलबेस के लिए प्राथमिक और एकत्रित निगरानी डेटा की तालिकाएं)।

पर्वत उदाहरण

सबसे पहले आपको ext4 फाइल सिस्टम के साथ डिस्क को फॉर्मेट करना होगा और इसे सर्वर से कनेक्ट करना होगा। डेटाबेस के लिए डिस्क को noatime लेबल के साथ माउंट करें:

  आरोह / देव / sdc1 / var / lib / pgsql / 10 / data / base -o noatime

स्थायी बढ़ते के लिए, लाइन को / etc / fstab फ़ाइल में जोड़ें:

 # जहां UUID डिस्क की पहचानकर्ता है, आप इसे Blkid उपयोगिता का उपयोग करके देख सकते हैं
 UUID = 121efe29-70bf-410b-bc71-90704568ce3b / var / lib / pgsql / 10 / data / base ext4 चूक, noatime 0 0

Pg_pathman के साथ इतिहास सारणी का विभाजन

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

विभाजन दो समस्याओं को एक साथ हल करता है:

1. संपूर्ण तालिकाओं को हटाकर अप्रचलित डेटा को हटाने में तेजी लाएं

2. प्रत्येक संयुक्त तालिका के लिए विभाजन विभाजित करना

PostgreSQL में विभाजन के लिए चार तंत्र हैं:

1. मानक बाधा

2. विस्तार pg_partman ( pg_pathman के साथ भ्रमित न करें )

3. विस्तार pg_pathman

4. मैन्युअल रूप से स्वयं द्वारा विभाजन बनाएं और बनाए रखें

हमारी राय में, सबसे सुविधाजनक, विश्वसनीय और अनुकूलित विभाजन समाधान, pg_pathman एक्सटेंशन है। इस विभाजन विधि के साथ, क्वेरी प्लानर लचीले ढंग से यह निर्धारित करता है कि कौन से विभाजन डेटा की खोज करते हैं। अफवाह यह है कि PostgreSQL के 12 वें संस्करण में पहले से ही एक उत्कृष्ट विभाजन होगा।

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

PostgreSQL 10 के लिए विभाजन को स्थापित और कॉन्फ़िगर करें

मानक OS रिपॉजिटरी से pg_pathman एक्सटेंशन को स्थापित और कॉन्फ़िगर करें (स्रोतों से एक्सटेंशन के नवीनतम संस्करण के निर्माण के निर्देशों के लिए, github पर उसी रिपॉजिटरी में देखें):

 yum इंस्टॉल pg_pathman10
 nano /var/pgsqldb/postgresql.conf
 share_preload_lbooks = 'pg_pathman' # महत्वपूर्ण - यहाँ सूची में अंतिम pg_pathman लिखें

हम DBMS को रिबूट करते हैं, डेटाबेस के लिए एक्सटेंशन बनाते हैं और विभाजन को कॉन्फ़िगर करते हैं (प्राथमिक निगरानी डेटा के लिए 1 दिन और एकत्रित निगरानी डेटा के लिए 3 दिन - यह 1 दिन के लिए किया जा सकता है):

 systemctl पुनः आरंभ postgresql-10.service
 psql -d zabbix -U पोस्टग्रेज करता है
 सृजन विस्तार pg_pathman;
 # प्राथमिक निगरानी डेटा की तालिकाओं के लिए एक दिन कॉन्फ़िगर करें
 # 1552424400 - यूनिक्स टाइमस्टैम्प के रूप में उलटी गिनती, 86400 - सेकंड दिनों में
 create_range_partitions ('इतिहास', 'घड़ी', 1552424400, 86400) चुनें;
 create_range_partitions ('history_uint', 'clock', 1552424400, 86400) चुनें;
 create_range_partitions ('history_text', 'clock', 1552424400, 86400) चुनें;
 create_range_partitions ('history_str', 'clock', 1552424400, 86400) चुनें;
 create_range_partitions ('history_log', 'clock', 1552424400, 86400) चुनें;
 # एकीकृत निगरानी डेटा तालिकाओं के लिए तीन दिनों के लिए कॉन्फ़िगर करें
 # 1552424400 - यूनिक्स टाइमस्टैम्प के रूप में उलटी गिनती, 259200 - तीन दिनों में सेकंड
 create_range_partitions ('रुझान', 'घड़ी', 1545771600, 259200) का चयन करें;  
 create_range_partitions ('Trends_uint', 'clock', 1545771600, 259200) चुनें;

यदि अभी तक किसी भी तालिका में कोई डेटा नहीं है, तो create_range_partitions फ़ंक्शन को कॉल करते समय, एक और अतिरिक्त तर्क p_count = 0_ पास होना चाहिए।

विभाजन की निगरानी और प्रबंधन के लिए उपयोगी प्रश्न:

 # विभाजन तालिका की मुख्य सूची, मुख्य विन्यास भंडारण:
 pathman_config से * का चयन करें;
 # सभी मौजूदा वर्गों, साथ ही उनके माता-पिता और सीमा सीमाओं का प्रतिनिधित्व:
 pathman_partition_list से * चुनें;
 # अतिरिक्त पैरामीटर जो मानक pg_pathman व्यवहार को ओवरराइड करते हैं:
 pathman_config_params से * का चयन करें;
 # सामग्री को मूल तालिका में वापस कॉपी करें और विभाजन हटाएं:
 drop_partitions ('table_name' :: regclass, false) चुनें;

विभाजन की संख्या और आकार पर आँकड़े देखने के लिए उपयोगी स्क्रिप्ट:

 /*       */ SELECT nspname AS schemaname, relname, relkind, cast (reltuples as int), pg_size_pretty(pg_relation_size(C.oid)) AS "size" FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE nspname NOT IN ('pg_catalog', 'information_schema') and (relname like 'history%' or relname like 'trends%') and relkind = 'r' -- and reltuples > 0 -- and pg_relation_size(C.oid) >= 0 ORDER BY schemaname, relname

अप्रचलित विभाजनों को हटाने का ऑटो-ट्यूनिंग (एंथुंग - एक बड़ा SQL फ़ंक्शन)

विभाजन के ऑटो-विलोपन को कॉन्फ़िगर करने के लिए, आपको डेटाबेस में एक फ़ंक्शन बनाने की आवश्यकता है
(विस्तृत पाठ, इसलिए मुझे वाक्य रचना हाइलाइटिंग को हटाना पड़ा):

 सृजन या समीक्षा सार्वजनिक करें .de_old_partitions (history_days पूर्णांक, Trends_days पूर्णांक, str_days पूर्णांक)
  पाठ पाठ
  भाषा plpgsql
 $ समारोह $ के रूप में
 / *
 फ़ंक्शन निर्दिष्ट दिनों की तुलना में पुराने सभी विभाजनों को हटा देता है:
 history_days - विभाजन के लिए history_x, history_uint_x
 Trends_days - विभाजन के लिए Trends_x, Trends_uint_x
 str_days - विभाजन के लिए history_str_x, history_text_x, history_log_x
 * /
 घड़ी की घोषणा घड़ी_टोडे_स्टार्ट इंट;
 घड़ी की घोषणा घड़ी_ले_ले_हिस्ट्री int = 0;
 घड़ी की घोषणा घड़ी_डेले_लेस_ट्रेंड्स इंट = 0;
 घड़ी की घोषणा घड़ी_ले_ले_स्ट्रिंग int = 0;
 clock_delete_less int = 0;
 घोषित itter int = 0;
 result_str text = '' घोषित करें;
 buf_table_size पाठ घोषित करें;
 buf_table_len पाठ घोषित करें;
 विभाजन_नाम पाठ घोषित करें;
 घड़ी_मेक्स पाठ घोषित करें;
 ir_detail टेक्स्ट घोषित करें;
 tarestart timestamp = clock_timestamp () घोषित करें;
 घोषित t_end टाइमस्टैम्प;
 शुरू करना
     यदि $ 1 <= 0 तो फिर वापसी, कुछ गलत: history_days तर्क सकारात्मक पूर्णांक मूल्य होना चाहिए ';  अंत अगर;
     यदि $ 2 <= 0 तो फिर वापसी, कुछ गलत: ट्रेंड्स_डे तर्क सकारात्मक पूर्णांक मान होना चाहिए ';  अंत अगर;
     अगर $ 3 <= 0 तो फिर वापसी, कुछ गलत: str_days तर्क सकारात्मक पूर्णांक मूल्य होना चाहिए ';  अंत अगर;
     clock_today_start = extract (दिनांक_trunc ('दिन', अब) ()) :: int;
     clock_delete_less_history = extract (date_trunc ('दिन', अब) () से - ($ 1 :: पाठ || 'दिन') :: अंतराल) :: int
     clock_delete_less_trends = extract (date_trunc ('दिन', अब) () से - ($ 2 :: पाठ || 'दिन') :: अंतराल) :: int
     clock_delete_less_strings = extract (date_trunc ('दिन', अब) () से - ($ 3 :: पाठ || 'दिन') :: अंतराल) :: int
     clock_delete_less = कम से कम (clock_delete_less_history, clock_delete_less_trends, clock_delete_less_strings);
     - ध्यान दें 'घड़ी_टोडे_स्टार्ट% (%)', to_timestamp (घड़ी_टोडे_स्टार्ट), घड़ी_टोडे_स्टार्ट;
     - सूचना का नोटिस 'clock_delete_less_history% (%)% दिन', to_timestamp (clock_delete_less_history), clock_delete_less_history, $ 1;
     - सूचना का नोटिस 'घड़ी_डेली_लेस_ट्रेंड्स% (%)% दिन', to_timestamp (घड़ी_डेली_लेस_ट्रेंड्स), घड़ी_डेलेटी_लेस_ट्रेंड्स, $ 2;
     - सूचना का नोटिस 'clock_delete_less_strings% (%)% days', to_timestamp (clock_delete_less_strings), clock_delete_less_strings, $ 3;
     विभाजन_नाम के लिए, घड़ी विभाजन में select_max, path_max pathman_partition_list जहां से 
     range_max :: int <= सबसे बड़ा (घड़ी_delete_less_history, clock_delete_less_trends, clock_delete_less_strings) 
     (विभाजन :: पाठ जैसे 'इतिहास%' या विभाजन :: पाठ जैसे 'रुझान%') विभाजन asc द्वारा आदेश
     पाश
         अगर (विभाजन_नाम ~ 'history_uint_ \ d' और clock_max :: int <= clock_delete_less_istory)
         या (part_name ~ 'history_ \ d' और clock_max :: int <= clock_delete_less_history)
         या (part_name ~ 'Trends_ \ d' और clock_max :: int <= clock_delete_less_trends)
         या (विभाजन_नाम ~ 'history_log_ \ d' और clock_max :: int <= clock_delete_less_strants)
         या (विभाजन_नाम ~ 'history_str_ \ d' और clock_max :: int <= clock_delete_less_strax)
         या (विभाजन_नाम ~ 'history_text_ \ d' और clock_max :: int <= clock_delete_less_strings)
         तो 
             itter = iterator + 1;
             नोटिस '%', प्रारूप (''% s% s ', पार्टीशन_नाम, घड़ी_मैक्स) बढ़ाएं;
             अधिकतम का चयन करें (reltuples :: int), pg_size_pretty (राशि (pg_relation_size (pg_class.oid))) के रूप में "आकार" से pg_class जहाँ relname जैसे पार्टीशन / नाम ||  '%' सख्त buf_table_len, buf_table_size में;
             if result_str! = '' तो result_str = result_str ||  '';  अंत अगर;
             result_str = result_str ||  स्वरूप ('% s (dt <% s, len% s,% s)', विभाजन_नाम, to_char (to_timestamp (clock_max :: int), 'YYYY-MM-DD'), buf_table_len, buf_table_size);
             निष्पादन प्रारूप ('% s मौजूद होने पर ड्रॉप टेबल', पार्टीशन_नाम);
         अंत अगर;
     अंत लूप;
     अगर iterator = 0 तो result_str = स्वरूप ('पुराने को हटाने के लिए कोई विभाजन नहीं है, तो% s दिनांक', to_char (to_timestamp (clock_delete_less), 'YYYY-MM-DD-DD)); 
     और परिणाम_ स्वरूप = प्रारूप ('% s सेकंड में% s विभाजन हटाए गए:', पुनरावृत्ति, ट्रंक (अर्क (सेकंड से (घड़ी_स्टिमैम्प) -) - t_start)) :: संख्यात्मक, 3)) ||  result_str;
     अंत अगर;
     - ध्यान दें '%', result_str;
     वापसी result_str;
 अपवाद तब जब अन्य
    स्टैक किए गए डायग्नॉस्टिक्स इरेटी_डेटेल = PG_EXCEPTION_CONTEXT;
    रिटर्न फॉर्मेट ('अप, कुछ गड़बड़:% s [इर कोड% s],% s', sqlerrm, sqlstate, अनियमित_detail);
 अंत; 
 $ समारोह $;

स्वतः-स्वच्छ विभाजन फ़ंक्शन को स्वचालित रूप से कॉल करने के लिए, आपको निम्नलिखित सेटिंग्स के साथ "डेटाबेस मॉनिटर" प्रकार के ज़ैबिक्स सर्वर होस्ट के लिए एक डेटा आइटम बनाने की आवश्यकता है:

 - प्रकार: डेटाबेस मॉनिटर
 - नाम: delete_old_history_partitions
 - कुंजी: db.odbc.select [delete_old_history_partitions, zabbix]
 - sql अभिव्यक्ति: delete_old_partitions (3, 30, 30) का चयन करें;
 # यहां, delete_old_partitions फ़ंक्शन कॉल के पैरामीटर दिनों में भंडारण समय का संकेत देते हैं 
 # संख्यात्मक मानों के लिए, संयुक्त संख्यात्मक मान और स्ट्रिंग मान
 - डेटा प्रकार: पाठ
 - अद्यतन अंतराल: 0
 - उपयोगकर्ता अंतराल: h2 में अनुसूचित
 - इतिहास भंडारण अवधि: 90 दिन
 - डेटा एलिमेंट ग्रुप: डेटाबेस

नतीजतन, हम इस प्रकार के विभाजन की सफाई के आंकड़े प्राप्त करेंगे:

  2019-09-16 02:00:00, 0.024 सेकंड में 3 विभाजन हटाए गए: Trends_78 (dt <2019-08-17, len 1, 48 kB), history_193 (dt <2019-09-13, len 85343, 9448 kB ), History_uint_186 (dt <2019-09-13, लेन 27969, 3480 kB)

महत्वपूर्ण! डेटा तत्व और उपयोगकर्ता फ़ंक्शन के माध्यम से विभाजनों के ऑटो-विलोपन को स्थापित करने के बाद, आपको घर की नौकरानी Zabbix कार्य अनुसूचक में इतिहास और प्रवृत्ति की सफाई को बंद करने की आवश्यकता है: zabbix मेनू आइटम के माध्यम से, "प्रशासन" -> सामान्य विवरण -> कोने में सूची से "स्पष्ट इतिहास" चुनें -> अक्षम करें सभी वर्गों में "इतिहास" और "परिवर्तन की गतिशीलता" चेकबॉक्स।

इतिहास सारणी के सूचकांक प्रकारों को ब्रिन (घड़ी) और बीटी-जिन (आइटम) में बदलना

PostgreSQL इंडेक्स पर अवलोकन लेखों की उत्कृष्ट श्रृंखला के लिए एर्गोव को विशेष धन्यवाद। और वास्तव में पूरी PostgresPRO टीम।इन लेखों से प्रभावित होकर, हमने मॉनिटरिंग डेटा टेबलों पर विभिन्न प्रकार के अनुक्रमितों के साथ खेला और इस निष्कर्ष पर पहुंचे कि किन क्षेत्रों पर किस प्रकार के सूचकांक अधिकतम प्रदर्शन को बढ़ावा देंगे।

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

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

विभिन्न सूचकांकों के परीक्षण के दौरान, सूचकांकों का सबसे सफल संयोजन सामने आया था: निगरानी डेटा के सभी तालिकाओं के लिए घड़ी क्षेत्र पर बैगन सूचकांक और आइटम फ़ील्ड पर btree-gin सूचकांक।

ब्रिन इंडेक्स नीरस रूप से बढ़ते डेटा के लिए आदर्श है, जैसे कि किसी घटना के तथ्य का टाइमस्टैम्प, यानी। समय श्रृंखला के लिए। और btree-gin index मूल रूप से मानक डेटा प्रकारों पर एक gin इंडेक्स है, जो आमतौर पर क्लासिक ब्रिग्री से बहुत तेज है क्योंकि नए मूल्यों को जोड़ने के दौरान जिन इंडेक्स का पुनर्निर्माण नहीं किया गया है, लेकिन केवल उनके द्वारा पूरक है। Btree-gin index को PostgreSQL के एक्सटेंशन के रूप में रखा गया है।

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

विभाजन का नाम	MLN में पंक्तियों की संख्या	एमबी में आकार
history_uint_1	81.3	4119
history_uint_2	74.9	4426
history_uint_3	100.7	5387

परिणामों का मूल्यांकन करने के लिए, तीन प्रकार के प्रश्नों का प्रदर्शन किया गया:

एक विशिष्ट पैरामीटर आइटम के लिए, पिछले महीने का डेटा, वास्तव में पिछले तीन दिन (कुल 1660 रिकॉर्ड)

विश्लेषण का चयन करें इतिहास से चुनें * जहां आइटम = 313300
और घड़ी> = अर्क ('2019-03-09 00:00:00' :: टाइमस्टैम्प से) :: int
और घड़ी <= निकालने ('2019-04-09 12:00:00' का समय :: टाइमस्टैम्प) :: int;

एक दिन के 12 घंटों के लिए एक विशिष्ट पैरामीटर डेटा के लिए (कुल 649 प्रविष्टियाँ)

विश्लेषण का चयन करें इतिहास से चुनें * जहां आइटम = 310650
और घड़ी> = अर्क ('2019-04-09 00:00:00' :: टाइमस्टैम्प से) :: int
और घड़ी <= निकालने ('2019-04-09 12:00:00' का समय :: टाइमस्टैम्प) :: int;

एक घंटे के लिए एक विशिष्ट पैरामीटर डेटा के लिए (कुल 61 रिकॉर्ड):

इतिहास_टेक्स्ट से चुनिंदा गणना (*) का विश्लेषण करें जहां आइटमिड = 336540
और घड़ी> = अर्क ('2019-04-08 11:00:00' :: टाइमस्टैम्प से) :: int
और घड़ी <= अर्क ('2019-04-08 12:00:00' का समय :: टाइमस्टैम्प) :: int;

परीक्षा परिणाम नीचे दिए गए थे:

सूचकांक प्रकार	MB में आकार *	अनुरोध 1 ** एमएस में	अनुरोध 2 ** एमएस में	अनुरोध 3 ** एमएस में
btree (घड़ी, आइटमिद)	14741	7154.3	2205.3	1860.4
brin (घड़ी), btree-gin (आइटमिड)	0.42 और 1329	2958.2	1820.4	102.1

* MB में आकार तीन विभाजनों के लिए कुल में इंगित किया गया है
** टाइप 1 अनुरोध - 3 दिनों के लिए डेटा, टाइप 2 अनुरोध - 12 घंटे के लिए डेटा, टाइप 3 अनुरोध - एक घंटे के लिए डेटा

तुलना तालिका से यह देखा जा सकता है कि रिकॉर्ड की संख्या के साथ बड़े डेटा तालिकाओं के लिए। 100 मिलियन से अधिक स्पष्ट रूप से दिखाता है कि मानक कम्पोजिट इंडेक्स बीटीआर को दो इंडेक्स ब्रिन और बीट्री-जिन में बदलने से इंडेक्स के आकार को कम करने और क्वेरी निष्पादन समय में तेजी लाने पर लाभकारी प्रभाव पड़ता है।

इंडेक्सिंग और पार्टीशनिंग की दक्षता इतिहास_उंट और ट्रेंड्स_इंट टेबल में नए रिकॉर्ड जोड़ने के अनुरोध के उदाहरण पर नीचे दिखाई गई है (जोड़ प्रति क्वेरी औसतन 2000 मान होते हैं)।

तालिका	सुधार के लिए औसत अनुरोध समय, एमएस	सुधार के बाद औसत अनुरोध समय, एमएस
trends_uint	2201.48	8.72
trends_uint	1997.27	62.16

ज़ैबिक्स सिस्टम मॉनिटरिंग डेटा टेबलों के लिए अनुक्रमित के विभिन्न विन्यासों के परीक्षणों के परिणामों को संक्षेप में, हम कह सकते हैं कि ज़ैबिक्स मॉनीटरिंग डेटा टेबलों के लिए मानक सूचकांक में एक समान परिवर्तन समग्र प्रणाली के प्रदर्शन को सकारात्मक रूप से प्रभावित करता है, जो सबसे अधिक महसूस किया जाता है कि 10 मिलियन से अधिक के डेटा वॉल्यूम जमा नहीं होते हैं। आपको डिफ़ॉल्ट रूप से मानक बीटीआरई सूचकांक के "सूजन" के अप्रत्यक्ष प्रभाव के बारे में भूल जाना चाहिए - बहु-गीगाबाइट सूचकांक के लगातार पुनर्निर्माण से हार्ड डिस्क का भारी भार होता है (उपयोगिता मेट्रिक) ation), जो अंततः डिस्क संचालन के समय और CPU (iowait मीट्रिक) से डिस्क तक पहुंचने के लिए प्रतीक्षा करने का समय बढ़ाता है।

हालांकि, ताकि btree-gin index बिगिंट (in8) डेटा टाइप के साथ काम कर सके, जो कि आइटमिड कॉलम है, आपको btree-gin इंडेक्स के लिए bigint टाइप के ऑपरेटर्स के परिवार को रजिस्टर करना होगा।

Btree-gin index के लिए एक bigint ऑपरेटर परिवार का पंजीकरण करना

/*
     gin    biginteger  integer    .
 -   gin     int2, int4, int8,
       bigint     ,     bigint (<= 2147483647)
        intger_ops,  :
create index on tablename using gin(columnname int8_family_ops) with (fastupdate = false);
*/

--       btree_gin
CREATE EXTENSION btree_gin;

CREATE OPERATOR FAMILY integer_ops using gin;

CREATE OPERATOR CLASS int4_family_ops
FOR TYPE int4 USING gin FAMILY integer_ops
AS
    OPERATOR 1 <,
    OPERATOR 2 <=,
    OPERATOR 3 =,
    OPERATOR 4 >=,
    OPERATOR 5 >,
    FUNCTION 1 btint4cmp(int4,int4),
    FUNCTION 2 gin_extract_value_int4(int4, internal),
    FUNCTION 3 gin_extract_query_int4(int4, internal, int2, internal, internal),
    FUNCTION 4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal),
    FUNCTION 5 gin_compare_prefix_int4(int4,int4,int2, internal),
STORAGE int4;

CREATE OPERATOR CLASS int8_family_ops
FOR TYPE int8 USING gin FAMILY integer_ops
AS
    OPERATOR 1 <,
    OPERATOR 2 <=,
    OPERATOR 3 =,
    OPERATOR 4 >=,
    OPERATOR 5 >,
    FUNCTION 1 btint8cmp(int8,int8),
    FUNCTION 2 gin_extract_value_int8(int8, internal),
    FUNCTION 3 gin_extract_query_int8(int8, internal, int2, internal, internal),
    FUNCTION 4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal),
    FUNCTION 5 gin_compare_prefix_int8(int8,int8,int2, internal),
STORAGE int8;

ALTER OPERATOR FAMILY integer_ops USING gin add
  OPERATOR 1 <(int4,int8),
  OPERATOR 2 <=(int4,int8),
  OPERATOR 3 =(int4,int8),
  OPERATOR 4 >=(int4,int8),
  OPERATOR 5 >(int4,int8);

ALTER OPERATOR FAMILY integer_ops USING gin add
  OPERATOR 1 <(int8,int4),
  OPERATOR 2 <=(int8,int4),
  OPERATOR 3 =(int8,int4),
  OPERATOR 4 >=(int8,int4),
  OPERATOR 5 >(int8,int4);

यह स्क्रिप्ट ऊपर वर्णित इष्टतम कॉन्फ़िगरेशन के लिए डिफ़ॉल्ट कॉन्फ़िगरेशन से Zabbix के लिए PostgreSQL डेटाबेस में सभी अनुक्रमों को फिर से वितरित करती है।

/*
        
*/

--   
drop index history_1;
drop index history_uint_1;
drop index history_str_1;
drop index history_text_1;
drop index history_log_1;
--          PK 
-- (   ,         )
alter table trends drop constraint trends_pk;
alter table trends_uint drop constraint trends_uint_pk;

--     bree-gin   itemid    
--   btree-gin  bigint       
-- https://habr.com/ru/company/postgrespro/blog/340978/#comment_10545932
--    create extension btree_gin;
create index on history using gin(itemid int8_family_ops) with (fastupdate = false);
create index on history_uint using gin(itemid int8_family_ops) with (fastupdate = false);
create index on history_str using gin(itemid int8_family_ops) with (fastupdate = false);
create index on history_text using gin(itemid int8_family_ops) with (fastupdate = false);
create index on history_log using gin(itemid int8_family_ops) with (fastupdate = false);
create index on trends using gin(itemid int8_family_ops) with (fastupdate = false);
create index on trends_uint using gin(itemid int8_family_ops) with (fastupdate = false);

--     bree-gin   itemid    
--     brin    128 ,    
--           ,
--      https://habr.com/ru/company/postgrespro/blog/346460/
create index on history using brin(clock) with (pages_per_range = 128);
create index on history_uint using brin(clock) with (pages_per_range = 128);
create index on history_str using brin(clock) with (pages_per_range = 128);
create index on history_text using brin(clock) with (pages_per_range = 128);
create index on history_log using brin(clock) with (pages_per_range = 128);
create index on trends using brin(clock) with (pages_per_range = 128);
create index on trends_uint using brin(clock) with (pages_per_range = 128);

100 टन प्रति मिनट (इतिहास में 100 टन और history_uint में 100 टन) की तीव्रता पर हमारे डेटा वॉल्यूम के लिए ब्रिन इंडेक्स के लिए, यह देखा गया कि सूचकांक 512 पृष्ठों के ज़ोन आकार के साथ प्राथमिक निगरानी डेटा की तालिकाओं पर दो बार तेजी से काम करता है। 128 पृष्ठों के मानक आकार के साथ, लेकिन यह व्यक्तिगत है और तालिकाओं और सर्वर कॉन्फ़िगरेशन के आकार पर निर्भर करता है। किसी भी मामले में, ब्रिन इंडेक्स बहुत कम जगह लेता है, लेकिन ज़ोन के आकार को ठीक-ठीक करके इसकी गति को थोड़ा बढ़ाया जा सकता है, लेकिन बशर्ते कि डेटा प्रवाह दर में बहुत बदलाव न हो।

नतीजतन, यह ध्यान देने योग्य है कि ज़ैबिक्स की वास्तुकला से जुड़ी एक सीमा है: टैब "हाल के डेटा" पर, प्रत्येक पैरामीटर के अंतिम दो मानों को फ़िल्टरिंग में लेते हुए एकत्र किया जाता है। प्रत्येक पैरामीटर के लिए, डेटाबेस में मानों का अनुरोध अलग से किया जाता है। इसलिए, जितने अधिक ऐसे पैरामीटर चुने जाते हैं, उतनी ही लंबी क्वेरी चलेगी। सबसे हालिया डेटा तब खोजा जाता है जब btree index (itemid, clock desc) को इतिहास सारणी में समय के अनुसार रिवर्स सॉर्टिंग के साथ सेट किया जाता है, लेकिन डिस्क पर बेशक "swells" का इंडेक्स और आमतौर पर अप्रत्यक्ष रूप से डेटाबेस को धीमा कर देता है, जो एक समस्या का कारण बनता है, ऊपर वर्णित है।

इसलिए, तीन तरीके हैं:

« » 100 (.. , « » )
Zabbix , , « »
डिफ़ॉल्ट रूप से अनुक्रमणिका को छोड़ दें, और कई मापदंडों के लिए एक ही समय में हाल के डेटा टैब पर काफी बड़े चयन प्राप्त करने के लिए केवल विभाजन के लिए खुद को सीमित करें (हालांकि, यह देखा गया था कि ज़ैबिक्स वेब सर्वर में अभी भी एक साथ प्रदर्शित पैरामीटर मानों की संख्या पर एक सीमा है टैब "हाल के डेटा" पर - इसलिए, जब मैंने 5000 मानों को प्रदर्शित करने का प्रयास किया, तो डेटाबेस ने परिणाम की गणना की, लेकिन सर्वर वेब पेज तैयार नहीं कर सका और इतनी बड़ी मात्रा में डेटा प्रदर्शित कर सका)।

क्वेरी निष्पादन आंकड़ों का संग्रह और विश्लेषण pg_stat_statements

Pg_stat_statements पूरे सर्वर पर क्वेरी प्रदर्शन पर आंकड़े एकत्र करने के लिए एक विस्तार है। इस एक्सटेंशन का लाभ यह है कि इसे PostgreSQL लॉग को इकट्ठा करने और पार्स करने की आवश्यकता नहीं है।

Pg_stat_statements एक्सटेंशन का उपयोग करना

psql:

 CREATE EXTENSION pg_stat_statements;

postgresql.conf:

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000 #   sql ,     (     );
pg_stat_statements.track = all # all -   (    ), top -   /, none -  
pg_stat_statements.save = true #

 SELECT pg_stat_statements_reset();

 select substring(query from '[^(]*') as query_sub, sum(calls) as calls, avg(mean_time) as mean_time from pg_stat_statements where query ~ 'insert into' or query ~ 'update trends' group by substring(query from '[^(]*') order by calls desc

Zabbix में हार्ड ड्राइव की निगरानी के लिए, केवल vfs.dev.read और vfs.dev.write पैरामीटर बॉक्स से बाहर दिए गए हैं। ये विकल्प डिस्क उपयोग के बारे में जानकारी प्रदान नहीं करते हैं। अपनी हार्ड ड्राइव के प्रदर्शन के साथ समस्याओं को खोजने के लिए उपयोगी मानदंड उपयोग कारक, प्रतीक्षा समय और डिस्क लोड कतार लोड हैं।

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

जबकि पुल अनुरोध लंबित है, आप मेरे कांटे के माध्यम से विस्तृत निर्देशों के अनुसार डिस्क मापदंडों की निगरानी का विस्तार करने का प्रयास कर सकते हैं ।

सभी वर्णित चरणों के बाद, आप मुख्य Zabbix सर्वर मॉनिटरिंग पैनल के लिए सिस्टम डिस्क और डेटाबेस से डिस्क (यदि वे अलग हैं) के लिए iowait cpu और उपयोग के मापदंडों के साथ एक कस्टम ग्राफ जोड़ सकते हैं। परिणाम इस तरह दिख सकता है (sda मुख्य डिस्क है, sdc डेटाबेस के साथ डिस्क है):

हार्डवेयर प्रदर्शन में सुधार

DBMS की स्थापना, अनुक्रमण और विभाजन के बाद, आप वर्टिकल स्केलिंग के लिए आगे बढ़ सकते हैं - सर्वर की हार्डवेयर विशेषताओं को बेहतर बनाने के लिए: RAM जोड़ें, ड्राइव को सॉलिड-स्टेट में बदलें और प्रोसेसर कोर जोड़ें। यह एक गारंटीकृत प्रदर्शन वृद्धि है, लेकिन सॉफ्टवेयर अनुकूलन के बाद ही ऐसा करना बेहतर है।

एक वितरित क्लस्टर बनाना

मध्यम ऊर्ध्वाधर स्केलिंग के बाद, आपको क्षैतिज शुरू करने की आवश्यकता है - एक वितरित क्लस्टर बनाएं: या तो शार्प करें या मास्टर दास को दोहराएं। लेकिन यह एक अलग विषय और एक अलग लेख की सामग्री है ~~(कैसे गंदगी और लाठी के एक समूह को ढालना)~~ , साथ ही साथ ऊपर वर्णित Zabbix डेटाबेस अनुकूलन तकनीक की तुलना में pg_pathman का उपयोग करके और TimescaleDB एक्सटेंशन को लागू करने की तकनीक के साथ अनुक्रमण किया गया है।

इस बीच, कोई केवल यह आशा कर सकता है कि इस लेख की सामग्री उपयोगी और ज्ञानवर्धक हो!

विभाजन और अनुक्रमण के साथ Zabbix + PostgreSQL प्रदर्शन में सुधार