PostgreSQL अंतर्गत आकडेवारीमध्ये खोलवर जा. अलेक्सी लेसोव्स्की

अॅलेक्सी लेसोव्स्की द्वारे 2015 अहवालाचे प्रतिलेखन "पोस्टग्रेएसक्यूएल अंतर्गत आकडेवारीमध्ये खोलवर जा"

अहवालाच्या लेखकाकडून अस्वीकरण: मी लक्षात घेतो की हा अहवाल नोव्हेंबर 2015 चा आहे - 4 वर्षांहून अधिक काळ लोटला आहे आणि बराच वेळ गेला आहे. अहवालात चर्चा केलेली आवृत्ती 9.4 यापुढे समर्थित नाही. गेल्या 4 वर्षांमध्ये, 5 नवीन रिलीझ रिलीझ केले गेले आहेत ज्यामध्ये आकडेवारीच्या संदर्भात अनेक नवकल्पना, सुधारणा आणि बदल दिसून आले आहेत आणि काही सामग्री जुनी आहे आणि संबंधित नाही. मी पुनरावलोकन केल्याप्रमाणे, मी ही ठिकाणे चिन्हांकित करण्याचा प्रयत्न केला जेणेकरून आपण वाचकांची दिशाभूल करू नये. मी ही ठिकाणे पुन्हा लिहिली नाहीत, त्यापैकी बरीच आहेत आणि परिणामी, एक पूर्णपणे भिन्न अहवाल बाहेर येईल.

PostgreSQL DBMS ही एक मोठी यंत्रणा आहे, आणि या यंत्रणेमध्ये अनेक उपप्रणाली असतात, ज्याचे समन्वित कार्य थेट DBMS च्या कार्यक्षमतेवर परिणाम करते. ऑपरेशन दरम्यान, घटकांच्या ऑपरेशनबद्दल आकडेवारी आणि माहिती संकलित केली जाते, जे तुम्हाला PostgreSQL च्या प्रभावीतेचे मूल्यांकन करण्यास आणि कार्यप्रदर्शन सुधारण्यासाठी उपाययोजना करण्यास अनुमती देते. तथापि, यात बरीच माहिती आहे आणि ती एका ऐवजी सरलीकृत स्वरूपात सादर केली गेली आहे. या माहितीवर प्रक्रिया करणे आणि त्याचा अर्थ लावणे हे काहीवेळा पूर्णपणे क्षुल्लक काम असते आणि साधने आणि उपयुक्तता यांचे "प्राणीसंग्रहालय" अगदी प्रगत DBA देखील सहजपणे गोंधळात टाकू शकते.

शुभ दुपार माझे नाव अॅलेक्सी आहे. इल्या म्हटल्याप्रमाणे, मी 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 क्वेरी करणे आवश्यक आहे, म्हणजे तुम्हाला निवडणे, सामील कसे केले जातात हे माहित असणे आवश्यक आहे.

आकडेवारी आपल्याला अनेक गोष्टी सांगते. ते श्रेणींमध्ये विभागले जाऊ शकतात.

• पहिली श्रेणी म्हणजे डेटाबेसमध्ये घडणाऱ्या घटना. जेव्हा डेटाबेसमध्ये काही घटना घडतात तेव्हा असे होते: एक क्वेरी, टेबल ऍक्सेस, ऑटोव्हॅक्यूम, कमिट, नंतर हे सर्व इव्हेंट आहेत. या घटनांशी संबंधित काउंटर वाढवले ​​आहेत. आणि आपण या घटनांचा मागोवा घेऊ शकतो.
• दुसरी श्रेणी म्हणजे टेबल, डेटाबेस यांसारख्या वस्तूंचे गुणधर्म. त्यांच्याकडे गुणधर्म आहेत. हा टेबलांचा आकार आहे. आम्ही टेबल्सच्या वाढीचा, निर्देशांकांच्या वाढीचा मागोवा घेऊ शकतो. आपण गतिशीलतेतील बदल पाहू शकतो.
• आणि तिसरी श्रेणी म्हणजे कार्यक्रमात घालवलेला वेळ. विनंती एक कार्यक्रम आहे. त्याची स्वतःची विशिष्ट कालावधी आहे. इथून सुरुवात झाली, इथेच संपली. आम्ही त्याचा मागोवा घेऊ शकतो. एकतर डिस्कवरून ब्लॉक वाचण्याची किंवा लिहिण्याची वेळ. या गोष्टींचा मागोवाही घेतला जातो.

आकडेवारीचे स्त्रोत खालीलप्रमाणे सादर केले आहेत:

• सामायिक मेमरी (सामायिक बफर) मध्ये स्थिर डेटा ठेवण्यासाठी एक विभाग आहे, तेथे काही काउंटर देखील आहेत जे काही घटना घडतात किंवा डेटाबेसच्या ऑपरेशनमध्ये काही क्षण उद्भवतात तेव्हा सतत वाढतात.
• हे सर्व काउंटर वापरकर्त्यासाठी उपलब्ध नाहीत आणि प्रशासकासाठी देखील उपलब्ध नाहीत. या खालच्या पातळीच्या गोष्टी आहेत. त्यांच्यात प्रवेश करण्यासाठी, PostgreSQL SQL फंक्शन्सच्या स्वरूपात एक इंटरफेस प्रदान करते. आम्ही या फंक्शन्सचा वापर करून निवडक निवडी करू शकतो आणि काही प्रकारचे मेट्रिक (किंवा मेट्रिक्सचा संच) मिळवू शकतो.
• तथापि, ही फंक्शन्स वापरणे नेहमीच सोयीचे नसते, म्हणून फंक्शन्स दृश्यांसाठी (VIEWs) आधार असतात. ही व्हर्च्युअल टेबल्स आहेत जी विशिष्ट सबसिस्टमवर किंवा डेटाबेसमधील काही इव्हेंट्सवर आकडेवारी प्रदान करतात.
• ही अंगभूत दृश्ये (VIEW) आकडेवारीसह कार्य करण्यासाठी मुख्य वापरकर्ता इंटरफेस आहेत. ते कोणत्याही अतिरिक्त सेटिंग्जशिवाय डीफॉल्टनुसार उपलब्ध आहेत, तुम्ही ते त्वरित वापरू शकता, पाहू शकता, तेथून माहिती घेऊ शकता. आणि योगदान देखील आहेत. योगदान अधिकृत आहेत. तुम्ही 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 ला विनंती पाठवलेल्या क्लायंटला थोड्या वेळाने प्रतिसाद मिळेल. जर ही सर्व ऑपरेशन्स मेमरीमध्ये केली गेली, तर पोस्टग्रेस खूप जलद प्रतिसाद देईल आणि क्लायंट कमी प्रतीक्षा करेल.

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 साठी उपयुक्त आहे. आणि चेकपॉईंट दरम्यान अंमलात आणल्या जाणार्‍या विनंत्यांना डिस्क सबसिस्टम व्यस्त असल्याच्या तणावाचा अनुभव येणार नाही.

आणि चेकपॉईंट समायोजित करण्यासाठी तीन पॅरामीटर्स आहेत:

• с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 पेक्षा जास्त असेल, तर हे आधीच एक सूचक आहे की तुम्हाला कुठेतरी अनुक्रमणिका तयार करण्याची आवश्यकता असू शकते जेणेकरून प्रवेश अनुक्रमणिकेनुसार असतील किंवा अशा अनुक्रमिक स्कॅनचा वापर करणार्‍या क्वेरी ऑप्टिमाइझ करणे शक्य होईल जेणेकरून असे होत नाही. होते.

एक साधे उदाहरण - मोठ्या OFFSET आणि LIMIT ची विनंती योग्य आहे असे समजा. उदाहरणार्थ, टेबलमधील 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 - टेबलमधील पंक्ती अपडेट करण्याचे ऑपरेशन. खरं तर, अद्यतन दोन ऑपरेशन्स (किंवा त्याहूनही अधिक) आहे. हे नवीन पंक्ती आवृत्ती घालत आहे आणि जुनी पंक्ती आवृत्ती अप्रचलित म्हणून चिन्हांकित करत आहे. नंतर, ऑटोव्हॅक्यूम येईल आणि ओळींच्या या अप्रचलित आवृत्त्या साफ करेल, हे ठिकाण पुन्हा वापरण्यासाठी उपलब्ध म्हणून चिन्हांकित करा.

तसेच, अपडेट म्हणजे केवळ टेबल अपडेट करणे नव्हे. हे अद्याप निर्देशांक अद्यतन आहे. जर तुमच्याकडे टेबलवर बरीच अनुक्रमणिका असतील, तर अद्यतनासह, क्वेरीमध्ये अद्यतनित केलेल्या फील्डसह सर्व अनुक्रमणिका देखील अद्यतनित करणे आवश्यक आहे. या निर्देशांकांमध्ये अप्रचलित पंक्ती आवृत्त्या देखील असतील ज्या साफ करणे आवश्यक आहे.

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;

आणि त्याच्या डिझाईनमुळे, अपडेट हे हेवीवेट ऑपरेशन आहे. परंतु ते सोपे केले जाऊ शकतात. खा 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));

ऑटोव्हॅक्यूम रांग. ऑटोव्हॅक्यूम ही अशी उपप्रणाली आहे ज्यासाठी PostgreSQL मध्ये फारच कमी आकडेवारी आहे. आम्ही फक्त pg_stat_activity मधील टेबलमध्ये पाहू शकतो की या क्षणी आमच्याकडे किती व्हॅक्यूम आहेत. मात्र, जाताना रांगेत किती टेबल आहेत हे समजणे फार कठीण आहे.

टीप: _पोस्टग्रेस 10 पासून, व्हॅक्यूम व्हॅक्यूमचा मागोवा घेण्याची परिस्थिती खूप सुधारली आहे - pg_stat_progress दृश्य दिसू लागले आहेव्हॅक्यूम, जे ऑटोव्हॅक्यूम मॉनिटरिंगची समस्या मोठ्या प्रमाणात सुलभ करते.

आपण ही सरलीकृत क्वेरी वापरू शकतो. आणि व्हॅक्यूम कधी बनवायचा ते आपण पाहू शकतो. पण, व्हॅक्यूम कसे आणि केव्हा सुरू करावे? मी आधी बोललेल्या स्ट्रिंगच्या या जुन्या आवृत्त्या आहेत. अद्यतन आले आहे, पंक्तीची नवीन आवृत्ती घातली गेली आहे. स्ट्रिंगची एक अप्रचलित आवृत्ती आली आहे. टेबल pg_stat_user_tables असा एक पॅरामीटर आहे n_dead_tup. हे "मृत" पंक्तींची संख्या दर्शवते. आणि मृत पंक्तींची संख्या एका विशिष्ट थ्रेशोल्डपेक्षा जास्त होताच, एक ऑटोव्हॅक्यूम टेबलवर येईल.

आणि हा थ्रेशोल्ड कसा मोजला जातो? सारणीतील एकूण पंक्तींची ही एक अतिशय विशिष्ट टक्केवारी आहे. एक पॅरामीटर आहे autovacuum_vacuum_scale_factor. हे टक्केवारी परिभाषित करते. समजा 10% + 50 ओळींचा अतिरिक्त बेस थ्रेशोल्ड आहे. आणि काय होते? जेव्हा आमच्याकडे टेबलमधील सर्व ओळींपैकी "10% + 50" पेक्षा जास्त मृत पंक्ती असतात, तेव्हा आम्ही टेबल ऑटोव्हॅक्यूमवर ठेवतो.

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 वैयक्तिकरित्या नियुक्त केले जाऊ शकते. आणि, त्यानुसार, थ्रेशोल्ड जागतिक नसेल, परंतु टेबलसाठी वैयक्तिक असेल. म्हणून, गणना करण्यासाठी, तेथे आपल्याला युक्त्या आणि युक्त्या वापरण्याची आवश्यकता आहे. आणि जर तुम्हाला स्वारस्य असेल, तर तुम्ही आमच्या सहकाऱ्यांचे अविटोचे अनुभव पाहू शकता (स्लाइडवरील लिंक अवैध आहे आणि मजकूरात अपडेट केली गेली आहे).

साठी त्यांनी लिहिले मुनिन प्लगइनजे या गोष्टी लक्षात घेते. दोन पत्र्यांवर पायघोळ आहे. परंतु तो योग्यरित्या विचार करतो आणि प्रभावीपणे आम्हाला मूल्यमापन करण्यास अनुमती देतो जेथे टेबलसाठी भरपूर व्हॅक्यूम आवश्यक आहे जेथे थोडे आहे.

त्याबद्दल आपण काय करू शकतो? जर आमच्याकडे लांब रांग असेल आणि ऑटोव्हॅक्यूम सामना करू शकत नसेल, तर आम्ही व्हॅक्यूम कामगारांची संख्या वाढवू शकतो किंवा व्हॅक्यूम अधिक आक्रमक बनवू शकतो.जेणेकरून ते आधी ट्रिगर होईल, टेबलवर लहान तुकड्यांमध्ये प्रक्रिया करेल. आणि त्यामुळे रांग कमी होईल. - येथे मुख्य गोष्ट म्हणजे डिस्कवरील लोडचे निरीक्षण करणे, कारण. व्हॅक्यूम गोष्ट विनामूल्य नाही, जरी 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.

खरे - याचा अर्थ असा आहे की प्रक्रिया प्रतीक्षा करीत आहे, काहीतरी करणे आवश्यक आहे. जेव्हा एखादी प्रक्रिया प्रतीक्षा करत असते, तेव्हा प्रक्रिया सुरू करणारा क्लायंट देखील प्रतीक्षा करत असतो. ब्राउझरमधील क्लायंट बसतो आणि वाट पाहतो.

चेतावणी: पोस्टग्रेस 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, जे तुम्हाला हेवी लॉक ट्रॅक करण्यास अनुमती देते.

आणि पहिली लिंक विनंती मजकूर आहे. ते खूपच लांब आहे.

आणि दुसरी लिंक 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 वर किती खर्च केला, म्हणजेच फाइल सिस्टमसह ऑपरेशन्स आणि प्रोसेसरचा वापर किती आहे याचा अंदाज लावण्याची परवानगी देते. तथापि, मॉड्यूल तरुण (खे-खे) आहे आणि त्याच्या कार्यासाठी पोस्टग्रेएसक्यूएल 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