🦇 🔓 ◽️ बड़े पैमाने पर PostgreSQL क्वेरी अनुकूलन। किरिल बोरोविकोव (टेन्सर) 🍿 🙅🏿 ❕

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

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

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

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

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

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

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

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

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

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

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

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

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

: « SQL 10 JOIN...» — , «», . , (10 FROM) - . []
PostgreSQL, «» , — «» . 10 , 10 , PostgreSQL , . []
«»
, , , . … - , .
, (INSERT, UPDATE, DELETE) — .

... और सब कुछ के लिए, हमें एक योजना की आवश्यकता है ! हमें यह देखने की जरूरत है कि सर्वर के अंदर क्या हो रहा है।

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

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

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

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

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

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

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

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

लेकिन भले ही यह प्रिय हो, भले ही असहज हो, लेकिन इससे भी बड़ी समस्याएं हैं:

. , Index Scan — , - . , «» , CTE — « ».
: , , — . , , , , loops — . . , , , — - « ».

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

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

दृश्य की योजना बनाएं

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

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

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

उचित नोड काम करने का समय
संपूर्ण उप-समय पर कुल समय
रिकॉर्ड की संख्या जो पुनर्प्राप्त की गई थी और जो सांख्यिकीय रूप से अपेक्षित थी
नोड शरीर ही

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

लेकिन कुछ छोटी समस्याएं हैं।

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

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

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

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

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

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

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

खुद की योजना पार्सर
है, अब हम आम तौर पर PostgreSQL द्वारा उत्पन्न किसी भी योजना को पार्स कर सकते हैं।
डायनेमिक नोड्स का सही विश्लेषण - CTE स्कैन, InitPlan, SubPlan
बफ़र्स के वितरण का विश्लेषण - जहां मेमोरी से डेटा के पृष्ठ पढ़े जाते हैं, जहां स्थानीय कैश से, जहां डिस्क से
दृश्यता प्राप्त हुई
ताकि यह "लॉग में" न हो कि यह "खुदाई" है, लेकिन यह कि आप तस्वीर में "सबसे कमजोर लिंक" तुरंत देखते हैं।

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

यह संक्षिप्त दृश्य वह है जिसे हम योजना टेम्पलेट कहते हैं ।

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

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

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

स्वाभाविक रूप से, एक "रेक" था।

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

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

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

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

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

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

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

समेकन लॉग करें

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

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

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

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

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

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

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

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

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

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

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

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

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

दूसरे, हमने सीखा (बहुत मजबूर थे) बहुत तेजी से उपयोग करके लिखनाCOPY । यानी सिर्फ COPYइसलिए नहीं कि यह तेज है INSERT, बल्कि इससे भी तेज है।

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

समय
प्रक्रिया और लेन-देन पहचानकर्ता
आधार नाम
इस अनुरोध को भेजने वाले व्यक्ति का आई.पी.
और विधि का नाम

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

तो, "एक सर्वर - एक दिन" अनुभाग किसी भी विश्लेषण के लिए पर्याप्त निकला।

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

जब हम विशिष्ट उदाहरणों से टेम्प्लेट में चले गए, तो हमें तुरंत दो फायदे हुए:

, .
, «» - , . , - , , , — , , — , , . , , .

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

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

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

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

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

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

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

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

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

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

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

बड़े पैमाने पर PostgreSQL क्वेरी अनुकूलन। किरिल बोरोविकोव (टेन्सर)

दृश्य की योजना बनाएं

समेकन लॉग करें

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

More articles: