PostgreSQL प्रश्नों का थोक अनुकूलन। किरिल बोरोविकोव (टेन्सर)

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

कौन से तकनीकी समाधान हमें इतनी मात्रा में जानकारी को कुशलतापूर्वक संसाधित करने की अनुमति देते हैं, और यह एक सामान्य डेवलपर के जीवन को कैसे आसान बनाता है?

किसकी रुचि है? विशिष्ट समस्याओं और विभिन्न अनुकूलन तकनीकों का विश्लेषण SQL क्वेरीज़ और PostgreSQL में विशिष्ट DBA समस्याओं को हल करना - आप भी कर सकते हैं लेखों की एक श्रृंखला पढ़ें इस विषय पर।


मेरा नाम किरिल बोरोविकोव है, मैं प्रतिनिधित्व करता हूं टेंसर कंपनी. विशेष रूप से, मैं हमारी कंपनी में डेटाबेस के साथ काम करने में विशेषज्ञ हूं।

आज मैं आपको बताऊंगा कि हम क्वेरी को कैसे अनुकूलित करते हैं, जब आपको किसी एक क्वेरी के प्रदर्शन को "अलग करने" की आवश्यकता नहीं होती है, बल्कि समस्या को सामूहिक रूप से हल करने की आवश्यकता होती है। जब लाखों अनुरोध हों और आपको कुछ खोजने की आवश्यकता हो समाधान के दृष्टिकोण यह बड़ी समस्या.

सामान्य तौर पर, हमारे दस लाख ग्राहकों के लिए Tensor है वीएलएसआई हमारा एप्लिकेशन है: कॉर्पोरेट सोशल नेटवर्क, वीडियो संचार के लिए समाधान, आंतरिक और बाहरी दस्तावेज़ प्रवाह के लिए, लेखांकन और गोदामों के लिए लेखांकन प्रणाली,... यानी एकीकृत व्यवसाय प्रबंधन के लिए ऐसा "मेगा-कंबाइन", जिसमें 100 से अधिक विभिन्न हैं आंतरिक परियोजनाएँ.

यह सुनिश्चित करने के लिए कि वे सभी सामान्य रूप से काम करें और विकसित हों, हमारे पास देश भर में 10 विकास केंद्र हैं, जिनमें और भी हैं 1000 डेवलपर्स.

हम 2008 से PostgreSQL के साथ काम कर रहे हैं और हमने जो भी संसाधित किया है, उसमें बड़ी मात्रा में जमा किया है - क्लाइंट डेटा, सांख्यिकीय, विश्लेषणात्मक, बाहरी सूचना प्रणालियों से डेटा - 400TB से अधिक. अकेले उत्पादन में लगभग 250 सर्वर हैं, और कुल मिलाकर लगभग 1000 डेटाबेस सर्वर हैं जिनकी हम निगरानी करते हैं।

SQL एक घोषणात्मक भाषा है. आप यह नहीं बताते कि कोई चीज़ "कैसे" काम करनी चाहिए, बल्कि आप "क्या" हासिल करना चाहते हैं। डीबीएमएस बेहतर जानता है कि जॉइन कैसे करना है - अपनी तालिकाओं को कैसे कनेक्ट करना है, कौन सी शर्तें लगानी हैं, इंडेक्स के माध्यम से क्या जाएगा, क्या नहीं...

कुछ DBMS संकेत स्वीकार करते हैं: "नहीं, इन दो तालिकाओं को ऐसी और ऐसी कतार में कनेक्ट करें," लेकिन PostgreSQL ऐसा नहीं कर सकता। यह अग्रणी डेवलपर्स की सचेत स्थिति है: "डेवलपर्स को किसी प्रकार के संकेतों का उपयोग करने की अनुमति देने के बजाय हम क्वेरी ऑप्टिमाइज़र को समाप्त करना पसंद करेंगे।"

लेकिन, इस तथ्य के बावजूद कि PostgreSQL "बाहरी" को स्वयं को नियंत्रित करने की अनुमति नहीं देता है, यह पूरी तरह से अनुमति देता है देखो उसके अंदर क्या चल रहा हैजब आप अपनी क्वेरी चलाते हैं, और इसमें कहां समस्या आ रही है।

सामान्य तौर पर, एक डेवलपर [डीबीए के लिए] आम तौर पर कौन सी क्लासिक समस्याएं लेकर आता है? “यहाँ हमने अनुरोध पूरा किया, और हमारे यहां सब कुछ धीमा है, सब कुछ लटका हुआ है, कुछ हो रहा है... किसी तरह की परेशानी!'

कारण लगभग हमेशा एक जैसे होते हैं:

• अकुशल क्वेरी एल्गोरिदम
  डेवलपर: "अब मैं उसे जॉइन के माध्यम से एसक्यूएल में 10 टेबल दे रहा हूं..." - और उम्मीद करता हूं कि उसकी शर्तें चमत्कारिक रूप से प्रभावी ढंग से "अनटाइड" हो जाएंगी और उसे सब कुछ जल्दी मिल जाएगा। लेकिन चमत्कार नहीं होते हैं, और ऐसी परिवर्तनशीलता वाला कोई भी सिस्टम (एक FROM में 10 टेबल) हमेशा किसी न किसी प्रकार की त्रुटि देता है। [लेख]
• पुराने आँकड़े
  यह बिंदु विशेष रूप से PostgreSQL के लिए बहुत प्रासंगिक है, जब आप सर्वर पर एक बड़ा डेटासेट "डालते हैं", एक अनुरोध करते हैं, और यह आपके टैबलेट को "सेक्सकैनिट" करता है। क्योंकि कल इसमें 10 रिकॉर्ड थे, और आज 10 मिलियन हैं, लेकिन PostgreSQL को अभी तक इसकी जानकारी नहीं है, और हमें इसके बारे में बताना होगा। [लेख]
• संसाधनों पर "प्लग"।
  आपने एक कमजोर सर्वर पर एक बड़ा और भारी लोडेड डेटाबेस स्थापित किया है जिसमें पर्याप्त डिस्क, मेमोरी या प्रोसेसर प्रदर्शन नहीं है। और बस इतना ही... कहीं न कहीं एक प्रदर्शन सीमा है जिसके ऊपर आप अब छलांग नहीं लगा सकते।
• अवरुद्ध
  यह एक कठिन बिंदु है, लेकिन वे विभिन्न संशोधित प्रश्नों (INSERT, UPDATE, DELETE) के लिए सबसे अधिक प्रासंगिक हैं - यह एक अलग बड़ा विषय है।

एक योजना मिल रही है

...और बाकी सब चीज़ों के लिए हम एक योजना की जरूरत है! हमें यह देखना होगा कि सर्वर के अंदर क्या हो रहा है।

PostgreSQL के लिए एक क्वेरी निष्पादन योजना पाठ प्रतिनिधित्व में क्वेरी निष्पादन एल्गोरिदम का एक पेड़ है। यह बिल्कुल वही एल्गोरिदम है, जो योजनाकार द्वारा विश्लेषण के परिणामस्वरूप सबसे प्रभावी पाया गया।

प्रत्येक ट्री नोड एक ऑपरेशन है: तालिका या इंडेक्स से डेटा पुनर्प्राप्त करना, बिटमैप बनाना, दो तालिकाओं को जोड़ना, जोड़ना, प्रतिच्छेद करना या चयनों को बाहर करना। किसी क्वेरी को निष्पादित करने में इस पेड़ के नोड्स के माध्यम से चलना शामिल है।

क्वेरी योजना प्राप्त करने के लिए, कथन को निष्पादित करना सबसे आसान तरीका है EXPLAIN. सभी वास्तविक विशेषताओं को प्राप्त करने के लिए, यानी वास्तव में आधार पर एक क्वेरी निष्पादित करने के लिए - EXPLAIN (ANALYZE, BUFFERS) SELECT ....

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

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

यदि उसे पता चलता है कि कोई अनुरोध आपके द्वारा बताई गई सीमा से अधिक समय तक चल रहा है, तो वह ऐसा करता है इस अनुरोध की योजना का "स्नैपशॉट" बनाएं और उन्हें लॉग में एक साथ लिखें.

अब सब कुछ ठीक लग रहा है, हम लॉग पर जाते हैं और वहां देखते हैं... [पाठ फ़ुटक्लॉथ]। लेकिन हम इसके बारे में कुछ नहीं कह सकते, सिवाय इस तथ्य के कि यह एक उत्कृष्ट योजना है क्योंकि इसे निष्पादित करने में 11ms लगे।

सब कुछ ठीक लग रहा है - लेकिन कुछ भी स्पष्ट नहीं है कि वास्तव में क्या हुआ। सामान्य समय के अलावा, हम वास्तव में कुछ भी नहीं देखते हैं। क्योंकि सादे पाठ के ऐसे "भेड़ के बच्चे" को देखना आम तौर पर दृश्य नहीं होता है।

लेकिन भले ही यह स्पष्ट न हो, भले ही यह असुविधाजनक हो, फिर भी और भी मूलभूत समस्याएं हैं:

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

ऐसी स्थितियों में, समझें कि "सबसे कमजोर कड़ी कौन है?" लगभग असंभव। इसलिए, यहां तक ​​कि डेवलपर्स स्वयं भी "मैनुअल" में लिखते हैं "किसी योजना को समझना एक कला है जिसे सीखना चाहिए, अनुभव करना चाहिए...".

लेकिन हमारे पास 1000 डेवलपर हैं, और आप उनमें से प्रत्येक को यह अनुभव नहीं बता सकते। मैं, आप, वह जानता है, लेकिन वहां कोई अब नहीं जानता। शायद वह सीख लेगा, या शायद नहीं, लेकिन उसे अभी काम करने की ज़रूरत है - और उसे यह अनुभव कहाँ से मिलेगा?

योजना विज़ुअलाइज़ेशन

इसलिए, हमें एहसास हुआ कि इन समस्याओं से निपटने के लिए हमें इसकी आवश्यकता है योजना का अच्छा दृश्यांकन. [लेख]

हम सबसे पहले "बाज़ार के माध्यम से" गए - आइए इंटरनेट पर देखें कि क्या मौजूद है।

लेकिन यह पता चला कि बहुत कम अपेक्षाकृत "लाइव" समाधान हैं जो कमोबेश विकसित हो रहे हैं - वस्तुतः, केवल एक: व्याख्या.depesz.com ह्यूबर्ट लुबाक्ज़वेस्की द्वारा। जब आप "फ़ीड" फ़ील्ड में योजना का एक पाठ प्रतिनिधित्व दर्ज करते हैं, तो यह आपको पार्स किए गए डेटा के साथ एक तालिका दिखाता है:

• नोड का अपना प्रसंस्करण समय
• संपूर्ण उपवृक्ष के लिए कुल समय
• पुनर्प्राप्त किए गए रिकॉर्ड की संख्या जो सांख्यिकीय रूप से अपेक्षित थी
• नोड बॉडी ही

इस सेवा में लिंक का संग्रह साझा करने की क्षमता भी है। आपने अपनी योजना वहाँ फेंक दी और कहा: "अरे, वास्या, यहाँ एक लिंक है, वहाँ कुछ गड़बड़ है।"

लेकिन छोटी-छोटी समस्याएं भी हैं.

सबसे पहले, "कॉपी-पेस्ट" की एक बड़ी मात्रा। आप लॉग का एक टुकड़ा लें, इसे वहां चिपका दें, और बार-बार।

दूसरा, पढ़े गए डेटा की मात्रा का कोई विश्लेषण नहीं - वही बफ़र्स जो आउटपुट करते हैं EXPLAIN (ANALYZE, BUFFERS), हम इसे यहां नहीं देखते हैं। वह बस यह नहीं जानता कि उन्हें कैसे अलग किया जाए, उन्हें कैसे समझा जाए और उनके साथ काम किया जाए। जब आप बहुत सारा डेटा पढ़ रहे हों और महसूस करें कि आप डिस्क और मेमोरी कैश का गलत आवंटन कर रहे हैं, तो यह जानकारी बहुत महत्वपूर्ण है।

तीसरा नकारात्मक बिंदु इस परियोजना का बहुत कमजोर विकास है। प्रतिबद्धताएँ बहुत छोटी हैं, यह अच्छा है अगर हर छह महीने में एक बार, और कोड पर्ल में हो।

लेकिन यह सब "गीत" हैं, हम किसी तरह इसके साथ रह सकते हैं, लेकिन एक चीज़ है जिसने हमें इस सेवा से बहुत दूर कर दिया है। ये कॉमन टेबल एक्सप्रेशन (सीटीई) और इनिटप्लान/सबप्लान जैसे विभिन्न गतिशील नोड्स के विश्लेषण में त्रुटियां हैं।

यदि आप इस चित्र पर विश्वास करते हैं, तो प्रत्येक व्यक्तिगत नोड का कुल निष्पादन समय संपूर्ण अनुरोध के कुल निष्पादन समय से अधिक है। यह आसान है - इस CTE का उत्पादन समय CTE स्कैन नोड से नहीं घटाया गया था. इसलिए, अब हमें इसका सही उत्तर नहीं पता है कि सीटीई स्कैन में कितना समय लगा।

तब हमें एहसास हुआ कि अब अपना खुद का लिखने का समय आ गया है - हुर्रे! हर डेवलपर कहता है: "अब हम अपना खुद का लिखेंगे, यह बहुत आसान होगा!"

हमने वेब सेवाओं के लिए विशिष्ट स्टैक लिया: Node.js + Express पर आधारित एक कोर, सुंदर आरेखों के लिए बूटस्ट्रैप और D3.js का उपयोग किया। और हमारी उम्मीदें पूरी तरह से उचित थीं - हमें 2 सप्ताह में पहला प्रोटोटाइप प्राप्त हुआ:

• कस्टम योजना पार्सर
  यानी, अब हम PostgreSQL द्वारा जेनरेट किए गए प्लान में से किसी भी प्लान को पार्स कर सकते हैं।
• गतिशील नोड्स का सही विश्लेषण - सीटीई स्कैन, इनिटप्लान, सबप्लान
• बफ़र्स वितरण का विश्लेषण - जहां डेटा पेज मेमोरी से पढ़े जाते हैं, जहां स्थानीय कैश से, जहां डिस्क से
• स्पष्टता मिली
  ताकि लॉग में यह सब "खुदाई" न हो, बल्कि चित्र में तुरंत "सबसे कमजोर लिंक" दिखाई दे।

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

इसे हम संक्षिप्त निरूपण कहते हैं योजना टेम्पलेट.

और क्या सुविधाजनक होगा? यह देखना सुविधाजनक होगा कि हमारे कुल समय का कितना हिस्सा किस नोड को आवंटित किया गया है - और बस इसे किनारे पर चिपका दें पाई चार्ट.

हम नोड की ओर इशारा करते हैं और देखते हैं - यह पता चलता है कि Seq स्कैन में कुल समय का एक चौथाई से भी कम समय लगा, और शेष 3/4 CTE स्कैन द्वारा लिया गया। डरावनी! यदि आप सक्रिय रूप से अपने प्रश्नों में उनका उपयोग करते हैं तो यह सीटीई स्कैन की "फायर रेट" के बारे में एक छोटा सा नोट है। वे बहुत तेज़ नहीं हैं - वे नियमित टेबल स्कैनिंग से भी कमतर हैं। [लेख] [लेख]

लेकिन आमतौर पर ऐसे आरेख अधिक दिलचस्प, अधिक जटिल होते हैं, जब हम तुरंत एक खंड को इंगित करते हैं और देखते हैं, उदाहरण के लिए, आधे से अधिक समय कुछ Seq स्कैन ने "खाया"। इसके अलावा, अंदर किसी प्रकार का फ़िल्टर था, इसके अनुसार बहुत सारे रिकॉर्ड हटा दिए गए थे... आप इस तस्वीर को सीधे डेवलपर के पास फेंक सकते हैं और कह सकते हैं: "वास्या, यहां आपके लिए सब कुछ खराब है!" इसका पता लगाओ, देखो - कुछ गड़बड़ है!”

स्वाभाविक रूप से, इसमें कुछ "रेक" शामिल थे।

पहली चीज़ जो हमारे सामने आई वह थी गोलाई की समस्या। योजना में प्रत्येक व्यक्तिगत नोड का समय 1 μs की सटीकता के साथ दर्शाया गया है। और जब नोड चक्रों की संख्या अधिक हो जाती है, उदाहरण के लिए, 1000 - निष्पादन के बाद PostgreSQL को "सटीकता के भीतर" विभाजित किया जाता है, तो वापस गणना करते समय हमें कुल समय "कहीं 0.95ms और 1.05ms के बीच" मिलता है। जब गिनती माइक्रोसेकंड तक जाती है, तो यह ठीक है, लेकिन जब यह पहले से ही [मिली] सेकंड हो, तो आपको "किसने कितना उपभोग किया" योजना के नोड्स में संसाधनों को "खोलते" समय इस जानकारी को ध्यान में रखना होगा।

दूसरा बिंदु, अधिक जटिल, गतिशील नोड्स के बीच संसाधनों (उन बफ़र्स) का वितरण है। इससे हमें प्रोटोटाइप के पहले 2 सप्ताह और अन्य 4 सप्ताह खर्च करने पड़े।

इस तरह की समस्या आना काफी आसान है - हम एक सीटीई करते हैं और कथित तौर पर इसमें कुछ पढ़ते हैं। वास्तव में, PostgreSQL "स्मार्ट" है और वहां सीधे कुछ भी नहीं पढ़ेगा। फिर हम उसमें से पहला रिकॉर्ड लेते हैं, और उसी सीटीई से एक सौ पहला रिकॉर्ड लेते हैं।

हम योजना को देखते हैं और समझते हैं - यह अजीब है, हमारे पास Seq स्कैन में 3 बफ़र्स (डेटा पेज) "खपत" हैं, CTE स्कैन में 1 और, और दूसरे CTE स्कैन में 2 और हैं। यानी, अगर हम सब कुछ जोड़ दें, तो हमें 6 मिलेंगे, लेकिन टैबलेट से हम केवल 3 ही पढ़ते हैं! सीटीई स्कैन कहीं से भी कुछ नहीं पढ़ता है, बल्कि सीधे प्रोसेस मेमोरी के साथ काम करता है। यानी यहां कुछ तो साफ तौर पर गड़बड़ है!

वास्तव में, यह पता चला है कि यहां डेटा के वे सभी 3 पृष्ठ हैं जो Seq स्कैन से अनुरोध किए गए थे, पहले 1 ने 1 CTE स्कैन के लिए कहा, और फिर दूसरा, और 2 और उसे पढ़े गए। यानी, कुल 2 पृष्ठों का डेटा पढ़ा गया, 3 का नहीं।

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

हमने थोड़ी देर के लिए साँस छोड़ी। उन्होंने कहा: “अब, नियो, तुम कुंग फू जानते हो! अब हमारा अनुभव आपकी स्क्रीन पर है। अब आप इसका उपयोग कर सकते हैं।" [लेख]

लॉग समेकन

हमारे 1000 डेवलपर्स ने राहत की सांस ली। लेकिन हम समझ गए कि हमारे पास केवल सैकड़ों "कॉम्बैट" सर्वर हैं, और डेवलपर्स की ओर से यह सब "कॉपी-पेस्ट" बिल्कुल भी सुविधाजनक नहीं है। हमें एहसास हुआ कि हमें इसे स्वयं इकट्ठा करना होगा।

सामान्य तौर पर, एक मानक मॉड्यूल होता है जो आंकड़े एकत्र कर सकता है, हालांकि, इसे कॉन्फ़िगरेशन में सक्रिय करने की भी आवश्यकता होती है - यह मॉड्यूल pg_stat_statements. लेकिन वह हमें पसंद नहीं आया.

सबसे पहले, यह एक ही डेटाबेस के भीतर विभिन्न योजनाओं का उपयोग करके समान प्रश्नों को असाइन करता है अलग-अलग क्वेरी आईडी. अर्थात्, यदि आप पहली बार ऐसा करते हैं SET search_path = '01'; SELECT * FROM user LIMIT 1;और फिर SET search_path = '02'; और एक ही अनुरोध, तो इस मॉड्यूल के आँकड़ों में अलग-अलग रिकॉर्ड होंगे, और मैं योजनाओं को ध्यान में रखे बिना, विशेष रूप से इस अनुरोध प्रोफ़ाइल के संदर्भ में सामान्य आँकड़े एकत्र नहीं कर पाऊँगा।

दूसरा बिंदु जिसने हमें इसका उपयोग करने से रोका वह है योजनाओं का अभाव. यानी कोई योजना नहीं है, केवल अनुरोध ही है. हम देखते हैं कि क्या धीमा हो रहा था, लेकिन हम यह नहीं समझ पाते कि क्यों। और यहां हम तेजी से बदलते डेटासेट की समस्या पर लौटते हैं।

और आखिरी क्षण - "तथ्यों" का अभाव. अर्थात्, आप क्वेरी निष्पादन के किसी विशिष्ट उदाहरण को संबोधित नहीं कर सकते - ऐसा कुछ भी नहीं है, केवल एकत्रित आँकड़े हैं। हालाँकि इसके साथ काम करना संभव है, लेकिन यह बहुत कठिन है।

इसलिए, हमने कॉपी-पेस्ट से लड़ने का फैसला किया और लिखना शुरू किया एकत्र करनेवाला.

कलेक्टर एसएसएच के माध्यम से जुड़ता है, प्रमाणपत्र का उपयोग करके डेटाबेस के साथ सर्वर से एक सुरक्षित कनेक्शन स्थापित करता है, और tail -F लॉग फ़ाइल में इसे "चिपक जाता है"। तो इस सत्र में हमें संपूर्ण लॉग फ़ाइल का संपूर्ण "दर्पण" मिलता है, जो सर्वर उत्पन्न करता है। सर्वर पर लोड स्वयं न्यूनतम है, क्योंकि हम वहां कुछ भी पार्स नहीं करते हैं, हम केवल ट्रैफ़िक को मिरर करते हैं।

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

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

"केवल एक चीज है जिस पर आपने ध्यान नहीं दिया, वह है हाथी जैसी मधुमक्खियों की एक प्रजाति!.." हमने इस प्रणाली को विकसित करना तब शुरू किया जब हम 10 सर्वरों की निगरानी करना चाहते थे। हमारी समझ में सबसे महत्वपूर्ण, जहां कुछ समस्याएं उत्पन्न हुईं जिनसे निपटना मुश्किल था। लेकिन पहली तिमाही के दौरान, हमें निगरानी के लिए सौ मिले - क्योंकि सिस्टम काम कर रहा था, हर कोई इसे चाहता था, हर कोई आरामदायक था।

यह सब जोड़ने की जरूरत है, डेटा प्रवाह बड़ा और सक्रिय है। वास्तव में, हम जिस चीज की निगरानी करते हैं, जिससे हम निपट सकते हैं, वही हम उपयोग करते हैं। हम PostgreSQL का उपयोग डेटा स्टोरेज के रूप में भी करते हैं। और इसमें डेटा "डालने" के लिए ऑपरेटर से तेज़ कोई चीज़ नहीं है COPY अभी तक नहीं।

लेकिन केवल डेटा "डालना" वास्तव में हमारी तकनीक नहीं है। क्योंकि यदि आपके पास सौ सर्वर पर प्रति सेकंड लगभग 50k अनुरोध हैं, तो यह प्रति दिन 100-150GB लॉग उत्पन्न करेगा। इसलिए, हमें आधार को सावधानीपूर्वक "काटना" पड़ा।

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

दूसरे, हमने सीखा (मजबूर हो गए) लिखने में बहुत, बहुत तेज़ COPY. यानी बस नहीं COPYक्योंकि वह उससे भी तेज है INSERT, और उससे भी तेज.

तीसरा बिंदु - मुझे करना पड़ा क्रमशः ट्रिगर्स और विदेशी कुंजियाँ छोड़ें. यानी, हमारे पास कोई संदर्भात्मक अखंडता नहीं है। क्योंकि यदि आपके पास एक तालिका है जिसमें एफके की एक जोड़ी है, और आप डेटाबेस संरचना में कहते हैं कि "यहां एक लॉग रिकॉर्ड है जो एफके द्वारा संदर्भित है, उदाहरण के लिए, रिकॉर्ड्स के समूह के लिए," तो जब आप इसे सम्मिलित करते हैं, तो PostgreSQL के पास कुछ भी नहीं बचा है लेकिन इसे कैसे लिया जाए और इसे ईमानदारी से कैसे किया जाए SELECT 1 FROM master_fk1_table WHERE ... उस पहचानकर्ता के साथ जिसे आप सम्मिलित करने का प्रयास कर रहे हैं - केवल यह जांचने के लिए कि यह रिकॉर्ड वहां मौजूद है, ताकि आप अपने प्रविष्टि के साथ इस विदेशी कुंजी को "तोड़" न दें।

लक्ष्य तालिका और उसकी अनुक्रमणिका में एक रिकॉर्ड के बजाय, हमें संदर्भित सभी तालिकाओं से पढ़ने का अतिरिक्त लाभ मिलता है। लेकिन हमें इसकी बिल्कुल भी आवश्यकता नहीं है - हमारा काम कम से कम लोड के साथ जितना संभव हो सके और जितनी जल्दी हो सके रिकॉर्ड करना है। तो एफके - नीचे!

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

अब कल्पना करें कि आपके पास एक तालिका है जिसमें आप बस उन अनुरोधों की संख्या गिनते हैं जो एक विशिष्ट होस्ट से गुजरे हैं: +1, +1, +1, ..., +1. और आपको, सिद्धांत रूप में, इसकी आवश्यकता नहीं है - यह सब संभव है कलेक्टर पर स्मृति में योग और एक बार में डेटाबेस को भेजें +10.

हां, कुछ समस्याओं के मामले में, आपकी तार्किक अखंडता "टूट" सकती है, लेकिन यह लगभग अवास्तविक मामला है - क्योंकि आपके पास एक सामान्य सर्वर है, इसके नियंत्रक में एक बैटरी है, आपके पास एक लेनदेन लॉग है, एक लॉग ऑन है फ़ाइल सिस्टम... सामान्य तौर पर, यह इसके लायक नहीं है। ट्रिगर्स/एफके चलाने से आपको होने वाली उत्पादकता की हानि आपके द्वारा किए गए खर्च के लायक नहीं है।

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

टेक्स्ट से एमडी5 का उपयोग करना हमारे लिए एकदम सही था - अनुरोध, योजना, टेम्पलेट,... हम इसे कलेक्टर पक्ष पर गणना करते हैं, और तैयार आईडी को डेटाबेस में "डालते" हैं। एमडी5 की लंबाई और दैनिक विभाजन हमें संभावित टकरावों के बारे में चिंता करने की अनुमति नहीं देते हैं।

लेकिन यह सब जल्दी से रिकॉर्ड करने के लिए, हमें रिकॉर्डिंग प्रक्रिया को ही संशोधित करने की आवश्यकता थी।

आप आमतौर पर डेटा कैसे लिखते हैं? हमारे पास कुछ प्रकार का डेटासेट है, हम इसे कई तालिकाओं में विभाजित करते हैं, और फिर इसे कॉपी करते हैं - पहले पहले में, फिर दूसरे में, तीसरे में... यह असुविधाजनक है, क्योंकि ऐसा लगता है कि हम एक डेटा स्ट्रीम को तीन चरणों में लिख रहे हैं क्रमानुसार. अप्रिय. क्या यह तेजी से किया जा सकता है? कर सकना!

ऐसा करने के लिए, इन प्रवाहों को एक दूसरे के समानांतर विघटित करना ही पर्याप्त है। यह पता चला है कि हमारे पास त्रुटियां, अनुरोध, टेम्पलेट, अवरोधन हैं... अलग-अलग धागों में उड़ रहे हैं - और हम यह सब समानांतर में लिखते हैं। इसके लिए काफी है प्रत्येक व्यक्तिगत लक्ष्य तालिका के लिए एक COPY चैनल लगातार खुला रखें.

यानी कलेक्टर पर वहाँ हमेशा एक धारा है, जिसमें मैं अपनी जरूरत का डेटा लिख ​​सकता हूं। लेकिन ताकि डेटाबेस इस डेटा को देख सके, और कोई इस डेटा के लिखे जाने के इंतजार में न फंस जाए, COPY को निश्चित अंतराल पर बाधित किया जाना चाहिए. हमारे लिए, सबसे प्रभावी अवधि लगभग 100 एमएस थी - हम इसे बंद करते हैं और तुरंत इसे उसी टेबल पर फिर से खोलते हैं। और यदि कुछ शिखरों के दौरान हमारे पास एक प्रवाह पर्याप्त नहीं है, तो हम एक निश्चित सीमा तक पूलिंग करते हैं।

इसके अतिरिक्त, हमें पता चला कि ऐसी लोड प्रोफ़ाइल के लिए, कोई भी एकत्रीकरण, जब रिकॉर्ड बैचों में एकत्र किए जाते हैं, बुरा है। क्लासिक बुराई है INSERT ... VALUES और आगे 1000 रिकॉर्ड। क्योंकि उस समय आपके पास मीडिया पर लिखने का चरम होता है, और डिस्क पर कुछ लिखने की कोशिश करने वाला हर कोई प्रतीक्षा कर रहा होगा।

ऐसी विसंगतियों से छुटकारा पाने के लिए, बस कुछ भी एकत्रित न करें, बिल्कुल भी बफ़र न करें. और यदि डिस्क पर बफ़रिंग होती है (सौभाग्य से, Node.js में स्ट्रीम एपीआई आपको इसका पता लगाने की अनुमति देता है) - इस कनेक्शन को स्थगित कर दें। जब आपको कोई ईवेंट प्राप्त होता है कि यह फिर से मुफ़्त है, तो इसे संचित कतार से लिखें। और जब यह व्यस्त हो, तो पूल से अगला मुफ़्त लें और इसे लिखें।

डेटा रिकॉर्डिंग के लिए इस दृष्टिकोण को शुरू करने से पहले, हमारे पास लगभग 4K राइट ऑप्स थे, और इस तरह हमने लोड को 4 गुना कम कर दिया। अब नए मॉनिटर किए गए डेटाबेस के कारण वे 6 गुना बढ़ गए हैं - 100एमबी/एस तक। और अब हम पिछले 3 महीनों के लॉग को लगभग 10-15टीबी की मात्रा में संग्रहीत करते हैं, उम्मीद करते हैं कि केवल तीन महीनों में कोई भी डेवलपर किसी भी समस्या का समाधान करने में सक्षम होगा।

हम समस्याओं को समझते हैं

लेकिन केवल यह सारा डेटा एकत्र करना अच्छा है, उपयोगी है, प्रासंगिक है, लेकिन पर्याप्त नहीं है - इसे समझने की जरूरत है। क्योंकि ये प्रतिदिन लाखों अलग-अलग योजनाएं हैं।

लेकिन लाखों असहनीय हैं, हमें पहले "छोटा" करना होगा। और, सबसे पहले, आपको यह तय करना होगा कि आप इस "छोटी" चीज़ को कैसे व्यवस्थित करेंगे।

हमने तीन प्रमुख बिंदुओं की पहचान की है:

• कौन यह अनुरोध भेजा
  अर्थात्, यह किस एप्लिकेशन से "आया": वेब इंटरफ़ेस, बैकएंड, भुगतान प्रणाली या कुछ और।
• जहां घटित हुआ
  किस विशिष्ट सर्वर पर? क्योंकि यदि आपके पास एक एप्लिकेशन के तहत कई सर्वर हैं, और अचानक एक "बेवकूफ हो जाता है" (क्योंकि "डिस्क खराब है", "मेमोरी लीक हो गई", कोई अन्य समस्या), तो आपको सर्वर को विशेष रूप से संबोधित करने की आवश्यकता है।
• जैसा समस्या किसी न किसी रूप में स्वयं प्रकट हुई

यह समझने के लिए कि "किसने" हमें अनुरोध भेजा है, हम एक मानक उपकरण का उपयोग करते हैं - एक सत्र चर सेट करना: SET application_name = '{bl-host}:{bl-method}'; - हम उस बिजनेस लॉजिक होस्ट का नाम भेजते हैं जिससे अनुरोध आ रहा है, और उस विधि या एप्लिकेशन का नाम जिसने इसे शुरू किया है।

अनुरोध के "स्वामी" को पारित करने के बाद, इसे लॉग में आउटपुट किया जाना चाहिए - इसके लिए हम वेरिएबल को कॉन्फ़िगर करते हैं log_line_prefix = ' %m [%p:%v] [%d] %r %a'. कौन परवाह करता है, शायद मैनुअल में देखेंइस सबका क्या मतलब है। यह पता चला है कि हम लॉग में देखते हैं:

• समय
• प्रक्रिया और लेनदेन पहचानकर्ता
• डेटाबेस का नाम
• उस व्यक्ति का आईपी जिसने यह अनुरोध भेजा है
• और विधि का नाम

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

तो यहाँ कटौती है "एक सर्वर - एक दिन" यह हमारे लिए किसी भी विश्लेषण के लिए पर्याप्त साबित हुआ।

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

जब हम विशिष्ट उदाहरणों से टेम्पलेट्स की ओर बढ़े, तो हमें एक साथ दो फायदे मिले:

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

शेष विधियाँ उन संकेतकों पर आधारित हैं जिन्हें हम योजना से निकालते हैं: ऐसा पैटर्न कितनी बार हुआ, कुल और औसत समय, डिस्क से कितना डेटा पढ़ा गया, और मेमोरी से कितना...

क्योंकि, उदाहरण के लिए, आप होस्ट के लिए एनालिटिक्स पेज पर आते हैं, देखो - डिस्क पर कुछ बहुत अधिक पढ़ने लगा है। सर्वर पर डिस्क इसे संभाल नहीं सकती - इसे कौन पढ़ता है?

और आप किसी भी कॉलम को क्रमबद्ध कर सकते हैं और तय कर सकते हैं कि आप अभी क्या निपटेंगे - प्रोसेसर या डिस्क पर लोड, या अनुरोधों की कुल संख्या... हमने इसे क्रमबद्ध किया, "शीर्ष" वाले को देखा, इसे ठीक किया और एप्लिकेशन का एक नया संस्करण लॉन्च किया गया।
[वीडियो व्याख्यान]

और तुरंत आप अलग-अलग एप्लिकेशन देख सकते हैं जो एक अनुरोध से समान टेम्पलेट के साथ आते हैं SELECT * FROM users WHERE login = 'Vasya'. फ्रंटएंड, बैकएंड, प्रोसेसिंग... और आपको आश्चर्य है कि यदि उपयोगकर्ता उसके साथ इंटरैक्ट नहीं करता है तो प्रोसेसिंग उसे क्यों पढ़ेगी।

इसका विपरीत तरीका यह है कि एप्लिकेशन से तुरंत देखें कि यह क्या करता है। उदाहरण के लिए, फ्रंटेंड यह है, यह, यह, और यह घंटे में एक बार (समयरेखा मदद करती है)। और सवाल तुरंत उठता है: ऐसा लगता है कि घंटे में एक बार कुछ करना फ्रंटएंड का काम नहीं है...

कुछ समय बाद हमें एहसास हुआ कि हमारे पास समग्रता की कमी है योजना नोड्स द्वारा आँकड़े. हमने योजनाओं से केवल उन नोड्स को अलग किया है जो स्वयं तालिकाओं के डेटा के साथ कुछ करते हैं (उन्हें इंडेक्स द्वारा पढ़ें/लिखें या नहीं)। वास्तव में, पिछली तस्वीर के सापेक्ष केवल एक पहलू जोड़ा गया है - यह नोड हमारे लिए कितने रिकॉर्ड लेकर आया?, और कितनी हटा दी गईं (पंक्तियाँ फ़िल्टर द्वारा हटा दी गईं)।

आपके पास प्लेट पर उपयुक्त इंडेक्स नहीं है, आप उससे अनुरोध करते हैं, वह इंडेक्स को पार कर जाता है, Seq स्कैन में आ जाता है... आपने एक को छोड़कर सभी रिकॉर्ड फ़िल्टर कर दिए हैं। आपको प्रति दिन 100M फ़िल्टर किए गए रिकॉर्ड की आवश्यकता क्यों है? क्या इंडेक्स को रोल अप करना बेहतर नहीं है?

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

प्रश्न लिखने वाले प्रत्येक व्यक्ति को शायद इस पैटर्न का सामना करना पड़ा है: "मुझे वास्या के लिए अंतिम आदेश दें, इसकी तारीख।" और यदि आपके पास तिथि के अनुसार कोई सूचकांक नहीं है, या आपके द्वारा उपयोग किए गए सूचकांक में कोई तारीख नहीं है, तो आप बिल्कुल उसी "रेक" पर कदम रखें।

लेकिन हम जानते हैं कि यह एक "रेक" है - तो क्यों न तुरंत डेवलपर को बताया जाए कि उसे क्या करना चाहिए। तदनुसार, अब एक योजना खोलते समय, हमारे डेवलपर को तुरंत युक्तियों के साथ एक सुंदर तस्वीर दिखाई देती है, जहां वे तुरंत उसे बताते हैं: "आपको यहां और वहां समस्याएं हैं, लेकिन उन्हें इस तरह और उस तरह से हल किया जाता है।"

परिणामस्वरूप, शुरुआत में और अब समस्याओं को हल करने के लिए जिस अनुभव की आवश्यकता थी, उसमें काफी गिरावट आई है। हमारे पास इस प्रकार का उपकरण है।

स्रोत: www.habr.com