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

नमस्कार

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

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

जबाबदारी नाकारणे

PostgreSQL ची मुख्य आवृत्ती मी ज्यावर काम करतो ती 10 आहे. मी वापरत असलेल्या सर्व SQL क्वेरींची आवृत्ती ११ वर देखील चाचणी केली जाते. किमान समर्थित आवृत्ती 11 आहे.

prehistory

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

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

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

आमच्या बाबतीत देखभाल_कार्य_मेम डीफॉल्ट 64 MB होते, आणि temp_file_limit सुमारे 2 GB - मोठ्या टेबलवर अनुक्रमणिका तयार करण्यासाठी आमच्याकडे पुरेशी मेमरी नव्हती.

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

समस्या दोन - डुप्लिकेट अनुक्रमणिका

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

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

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

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

समस्या चार - निर्देशांकांशिवाय परदेशी की

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

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

delete from <table> where id in (…)

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

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

समस्या पाच - अनुक्रमणिका मध्ये शून्य मूल्य

डीफॉल्टनुसार, पोस्टग्रेसमध्ये btree इंडेक्सेसमध्ये शून्य मूल्यांचा समावेश असतो, परंतु तेथे त्यांची आवश्यकता नसते. म्हणून, मी परिश्रमपूर्वक हे शून्य (निदान indexes_with_null_values), प्रकारानुसार रद्द करण्यायोग्य स्तंभांवर आंशिक अनुक्रमणिका तयार करणे where <A> is not null. अशा प्रकारे मी आमच्या एका निर्देशांकाचा आकार 1877 MB वरून 16 KB पर्यंत कमी करू शकलो. आणि एका सेवेमध्ये, निर्देशांकांमधून शून्य मूल्ये वगळल्यामुळे डेटाबेसचा आकार एकूण 16% (निरपेक्ष संख्येत 4.3 GB ने) कमी झाला. अतिशय सोप्या सुधारणांसह डिस्क स्पेसमध्ये प्रचंड बचत. 🙂

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

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

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

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

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

खालील दोन निदान आहेत: tables_with_missing_indexes и unused_indexes - तुलनेने अलीकडे त्यांच्या अंतिम स्वरूपात दिसू लागले. मुद्दा असा आहे की ते फक्त घेतले आणि जोडले जाऊ शकत नाहीत.

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

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

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

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

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

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

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

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