जावा डेव्हलपरच्या नजरेतून PostgreSQL मधील निर्देशांकांचे आरोग्य

नमस्कार

माझे नाव वान्या आहे आणि मी जावा डेव्हलपर आहे. मी PostgreSQL सोबत खूप काम करते—मी डेटाबेस ट्यून करते, त्याची रचना ऑप्टिमाइझ करते, कामगिरी सुधारते आणि आठवड्याच्या शेवटी थोडेसे DBA देखील खेळते.

अलिकडे, मी आमच्या मायक्रोसर्व्हिसेसमधील अनेक डेटाबेस व्यवस्थित करत आहे आणि जावा लायब्ररी लिहित आहे. पीजी-इंडेक्स-हेल्थ, जे हे काम सोपे करते, माझा वेळ वाचवते आणि डेव्हलपर्सकडून होणाऱ्या काही सामान्य चुका टाळण्यास मदत करते. आज मी ज्या लायब्ररीबद्दल बोलणार आहे ती हीच आहे.

अस्वीकरण

मी ज्या PostgreSQL सोबत काम करतो त्याची प्राथमिक आवृत्ती १० आहे. मी वापरत असलेल्या सर्व SQL क्वेरीजची आवृत्ती ११ वर देखील चाचणी केली गेली आहे. किमान समर्थित आवृत्ती ९.६ आहे.

prehistory

हे सर्व जवळजवळ एक वर्षापूर्वी एका विचित्र परिस्थितीने सुरू झाले: स्पर्धात्मक निर्देशांक निर्मिती अचानक अयशस्वी झाली. नेहमीप्रमाणे, निर्देशांक स्वतःच डेटाबेसमध्ये अवैध स्थितीत राहिला. लॉग विश्लेषणात कमतरता आढळली. टेम्प_फाइल_लिमिटआणि म्हणून सुरुवात झाली... खोलवर जाताना, मला डेटाबेस कॉन्फिगरेशनमध्ये अनेक समस्या आढळल्या आणि, माझ्या बाही वर करून, डोळ्यात चमक आणून, मी त्या दुरुस्त करायला सुरुवात केली.

समस्या एक - डीफॉल्ट कॉन्फिगरेशन

कॉफी मशीनवर चालणाऱ्या पोस्टग्रेसची रूपक कदाचित थोडी थकवा आणत आहे, परंतु... डीफॉल्ट कॉन्फिगरेशन अनेक प्रश्न उपस्थित करते. किमान, त्याकडे लक्ष देणे योग्य आहे देखभाल_काम_मेम, टेम्प_फाइल_लिमिट, स्टेटमेंट_टाइमआउट и लॉक_टाइमआउट.

आमच्या बाबतीत देखभाल_काम_मेम डिफॉल्टनुसार ६४ एमबी होते, आणि टेम्प_फाइल_लिमिट सुमारे २ जीबी - आमच्याकडे मोठ्या टेबलवर इंडेक्स तयार करण्यासाठी पुरेशी मेमरी नव्हती.

म्हणून, मध्ये पीजी-इंडेक्स-हेल्थ मी एक पंक्ती गोळा केली. कीमाझ्या मते, प्रत्येक डेटाबेससाठी कॉन्फिगर केलेले पॅरामीटर्स.

समस्या दोन: डुप्लिकेट इंडेक्स

आमचे डेटाबेस SSD ड्राइव्हवर साठवले जातात आणि आम्ही वापरतो HA- एकाधिक डेटा सेंटर्ससह एक कॉन्फिगरेशन, एक मास्टर होस्ट आणि n- ठराविक संख्येने प्रतिकृती. डिस्क स्पेस आमच्यासाठी एक अतिशय मौल्यवान संसाधन आहे; ते कामगिरी आणि CPU वापरापेक्षा कमी महत्त्वाचे नाही. म्हणून, एकीकडे, आम्हाला जलद वाचनासाठी निर्देशांकांची आवश्यकता आहे, परंतु दुसरीकडे, आम्हाला डेटाबेसमध्ये अनावश्यक निर्देशांक नको आहेत, कारण ते जागा खातात आणि डेटा अपडेट्स मंदावतात.

आणि आता, सर्वकाही पुनर्संचयित करून अवैध निर्देशांक आणि पुरेसे पाहिले आहे ओलेग बार्टुनोव्ह यांचे अहवाल, मी एक "उत्कृष्ट" क्लीनअप करण्याचा निर्णय घेतला. असे दिसून आले की डेव्हलपर्सना डेटाबेस डॉक्युमेंटेशन वाचणे आवडत नाही. त्यांना खरोखर आवडत नाही. यामुळे दोन सामान्य चुका होतात: प्राथमिक की वर मॅन्युअली तयार केलेला इंडेक्स आणि एका अद्वितीय कॉलमवर त्याचप्रमाणे "मॅन्युअल" इंडेक्स. गोष्ट अशी आहे की, ते अनावश्यक आहेत—पोस्टग्रेस हे सर्व आपोआप करेल. असे इंडेक्स सुरक्षितपणे हटवले जाऊ शकतात आणि या उद्देशासाठी एक डायग्नोस्टिक जोडण्यात आला आहे. डुप्लिकेट_इंडेक्सेस.

समस्या तीन: छेदणारे निर्देशांक

बहुतेक नवशिक्या डेव्हलपर एकाच कॉलमवर इंडेक्स तयार करतात. हळूहळू, याची आवड निर्माण झाल्यानंतर, लोक त्यांच्या क्वेरीज ऑप्टिमाइझ करू लागतात आणि अनेक कॉलम्स असलेले अधिक जटिल इंडेक्स जोडू लागतात. अशाप्रकारे कॉलमर इंडेक्सचा जन्म होतो. A, ए + बी, A+B+C इत्यादी. यापैकी पहिले दोन निर्देशांक सुरक्षितपणे टाकून दिले जाऊ शकतात, कारण ते तिसऱ्याचे उपसर्ग आहेत. यामुळे डिस्क स्पेसची बरीच बचत होते आणि यासाठी एक निदान देखील आहे. छेदलेले_इंडेक्सेस.

समस्या ४: अनुक्रमणिकांशिवाय परदेशी की

पोस्टग्रेस तुम्हाला सपोर्टिंग इंडेक्स निर्दिष्ट न करता फॉरेन की कंस्ट्रेंट्स तयार करण्याची परवानगी देते. बऱ्याच परिस्थितींमध्ये, ही समस्या नसते आणि ती लक्षणे नसलेली देखील असू शकते... एका विशिष्ट टप्प्यापर्यंत...

आमच्या बाबतीतही हेच घडले: एका क्षणी, चाचणी ऑर्डरचा डेटाबेस साफ करण्याचे नियोजित काम आमच्या मास्टर होस्टला भेडसावू लागले. CPU आणि IO बंद पडले, विनंत्या मंदावल्या आणि कालबाह्य झाल्या आणि सेवा बंद पडली. एक जलद विश्लेषण pg_stat_activity खालील प्रकारच्या क्वेरी लटकत असल्याचे दाखवले:

delete from <table> where id in (…)

स्वाभाविकच, लक्ष्य सारणीतील आयडीवर एक अनुक्रमणिका होती आणि स्थितीनुसार फक्त काही रेकॉर्ड हटवण्यात आले होते. असे वाटत होते की सर्वकाही कार्य करेल, परंतु अरेरे, ते झाले नाही.

एक अद्भुत व्यक्ती मदतीला आली. स्पष्टीकरण द्या विश्लेषण करा आणि म्हटले की लक्ष्य सारणीतील रेकॉर्ड हटवण्याव्यतिरिक्त, एक संदर्भात्मक अखंडता तपासणी देखील आहे आणि लिंक केलेल्या सारण्यांपैकी एकावर ही तपासणी अयशस्वी होते. अनुक्रमिक स्कॅन योग्य निर्देशांकाच्या अभावामुळे. अशाप्रकारे निदानाचा जन्म झाला. परदेशी_कीज_विना_इंडेक्स.

समस्या क्रमांक पाच: निर्देशांकांमधील शून्य मूल्ये

डिफॉल्टनुसार, पोस्टग्रेसमध्ये btree इंडेक्समध्ये शून्य मूल्ये समाविष्ट असतात, परंतु ती सामान्यतः अनावश्यक असतात. म्हणून मी हे शून्य (निदान) टाकून देण्याचा खूप प्रयत्न करतो. शून्य_मूल्यांसह_सूचकांक), प्रकारानुसार रद्द करण्यायोग्य स्तंभांवर आंशिक अनुक्रमणिका तयार करणे where <A> is not nullया पद्धतीचा वापर करून, मी आमच्या एका इंडेक्सचा आकार १८७७ एमबी वरून १६ केबी पर्यंत कमी करू शकलो. आणि एका सेवेमध्ये, इंडेक्समधून शून्य मूल्ये काढून टाकून एकूण डेटाबेस आकार १६% (पूर्णपणे ४.३ जीबी) ने कमी करण्यात आला. अगदी सोप्या बदलांसह डिस्क स्पेसमध्ये मोठी बचत झाली. 🙂

समस्या क्रमांक सहा: प्राथमिक कींचा अभाव

यंत्रणेच्या वैशिष्ट्यांमुळे पोस्टग्रेस मध्ये एमव्हीसीसी अशी परिस्थिती शक्य आहे. फुलणे, जेव्हा तुमच्या टेबलचा आकार मोठ्या संख्येने मृत रेकॉर्डमुळे वेगाने वाढतो. मला भोळेपणाने वाटले की हे आमच्यासाठी धोका नाही आणि आमच्या डेटाबेसलाही होणार नाही, कारण, व्वा, आम्ही चांगले डेव्हलपर आहोत... मी किती मूर्ख आणि भोळा होतो...

एका छान दिवशी, एका अद्भुत मायग्रेशनने एका मोठ्या आणि जास्त वापरल्या जाणाऱ्या टेबलमधील सर्व रेकॉर्ड अपडेट केले. आम्हाला अचानक टेबलच्या आकारात १०० जीबी अतिरिक्त जागा मिळाली. हे आश्चर्यकारकपणे निराशाजनक होते, परंतु आमचे दुर्दैव संपले नव्हते. १५ तासांनंतर या टेबलवरील स्वयंचलित व्हॅक्यूम पूर्ण झाल्यानंतर, हे स्पष्ट झाले की भौतिक जागा पुनर्संचयित केली जाणार नाही. आम्ही सेवा थांबवू शकत नव्हतो आणि पूर्ण व्हॅक्यूम करू शकत नव्हतो, म्हणून आम्ही वापरण्याचा निर्णय घेतला pg_repackआणि मग असे दिसून आले की pg_repack प्रायमरी की किंवा इतर विशिष्टतेच्या मर्यादांशिवाय टेबल्स हाताळू शकत नाहीत आणि आमच्या टेबलमध्ये प्रायमरी की नव्हती. अशाप्रकारे डायग्नोस्टिकचा जन्म झाला. टेबल्स_विना_प्राथमिक_की.

लायब्ररी आवृत्तीमध्ये 0.1.5 टेबल आणि इंडेक्स ब्लोट्सवरील डेटा गोळा करण्याची आणि त्यांना वेळेवर प्रतिसाद देण्याची क्षमता जोडण्यात आली आहे.

समस्या सातवी आणि आठवी - अपुरी निर्देशांक आणि न वापरलेले निर्देशांक

पुढील दोन निदान पद्धती आहेत: गहाळ_इंडेक्सेससह_टेबल и न वापरलेले_इंडेक्सेस - तुलनेने अलीकडेच त्यांच्या अंतिम स्वरूपात दिसले. मुद्दा असा आहे की, ते फक्त जोडले जाऊ शकत नव्हते.

मी आधी सांगितल्याप्रमाणे, आम्ही मल्टी-रेप्लिका कॉन्फिगरेशन वापरतो आणि वेगवेगळ्या होस्टवरील रीड लोड लक्षणीयरीत्या बदलतो. यामुळे काही होस्टवरील काही टेबल्स आणि इंडेक्स जवळजवळ वापरात नसतात, ज्यामुळे विश्लेषणासाठी क्लस्टरमधील सर्व होस्ट्सकडून आकडेवारी गोळा करावी लागते. आकडेवारी रीसेट करा हे क्लस्टरमधील प्रत्येक होस्टवर देखील करणे आवश्यक आहे; हे फक्त मास्टरवरच करता येत नाही.

या दृष्टिकोनामुळे आम्हाला कधीही न वापरलेल्या निर्देशांकांना काढून टाकून तसेच क्वचित वापरल्या जाणाऱ्या टेबलांमध्ये गहाळ निर्देशांक जोडून अनेक दहापट गीगाबाइट्स वाचवता आल्या.

निष्कर्ष म्हणून

अर्थात, जवळजवळ सर्व निदानांसाठी ते कॉन्फिगर करणे शक्य आहे. अपवादांची यादीअशाप्रकारे, तुम्ही तुमच्या अर्जात त्वरित तपासणी करू शकता, नवीन त्रुटी दिसण्यापासून रोखू शकता आणि नंतर हळूहळू जुन्या चुका दुरुस्त करू शकता.

डेटाबेस मायग्रेशननंतर लगेचच काही डायग्नोस्टिक्स फंक्शनल चाचण्यांमध्ये चालवता येतात. हे कदाचित माझ्या लायब्ररीच्या सर्वात शक्तिशाली वैशिष्ट्यांपैकी एक आहे. त्याच्या वापराचे उदाहरण येथे आढळू शकते डेमो.

न वापरलेल्या किंवा गहाळ निर्देशांकांची तपासणी, तसेच ब्लोट, केवळ वास्तविक डेटाबेसवरच अर्थपूर्ण ठरते. गोळा केलेली मूल्ये येथे लिहिता येतात क्लिकहाऊस किंवा देखरेख प्रणालीकडे पाठवले जाते.

मला खरोखर आशा आहे की पीजी-इंडेक्स-हेल्थ उपयुक्त आणि मागणीनुसार असेल. तुम्ही समस्या नोंदवून आणि नवीन निदान सुचवून ग्रंथालयाच्या विकासात योगदान देऊ शकता.

स्त्रोत: www.habr.com