🤰🏼 💷 ㊙️ बिल्डरों के लिए बी 2 बी सेवा के उदाहरण पर डेटाबेस प्रश्नों का अनुकूलन 🤺 💼 👨🏿‍🎤

अधिक कुशल सर्वर पर जाने और सिस्टम को काम किए बिना डेटाबेस प्रश्नों की संख्या के तहत 10 गुना कैसे बढ़ें? मैं आपको बताता हूं कि हमने अपने डेटाबेस के प्रदर्शन में गिरावट के साथ कैसे संघर्ष किया, हमने SQL उपयोगकर्ताओं को अधिक से अधिक उपयोगकर्ताओं की सेवा करने के लिए अनुकूलित किया और कंप्यूटिंग संसाधनों की लागत में वृद्धि नहीं की।

मैं निर्माण कंपनियों में व्यावसायिक प्रक्रियाओं के प्रबंधन के लिए एक सेवा करता हूं। हमारे साथ लगभग 3 हजार कंपनियां काम करती हैं। हमारे सिस्टम के साथ हर दिन 4-10 घंटे 10 हजार से ज्यादा लोग काम करते हैं। यह योजना, अलर्ट, चेतावनियाँ, सत्यापन के विभिन्न कार्यों को हल करता है ... हम PostgreSQL 9.6 का उपयोग करते हैं। हमारे पास डेटाबेस में लगभग 300 टेबल हैं और हर दिन 200 मिलियन अनुरोध (10 हजार अलग-अलग) भेजे जाते हैं। औसतन, हमारे पास प्रति सेकंड 3-4 हजार अनुरोध हैं, सबसे सक्रिय क्षणों में प्रति सेकंड 10 हजार से अधिक अनुरोध हैं। अधिकांश अनुरोध OLAP हैं। बहुत कम परिवर्धन, संशोधन और विलोपन हैं, अर्थात्, ओएलटीपी लोड अपेक्षाकृत छोटा है। मैंने ये सभी आंकड़े दिए ताकि आप हमारी परियोजना के दायरे का मूल्यांकन कर सकें और समझ सकें कि हमारा अनुभव आपके लिए कितना उपयोगी हो सकता है।

पहली तस्वीर। गेय

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

दूसरी तस्वीर। सांख्यिकीय

इसलिए हमारे पास लगभग 10 हजार अलग-अलग प्रश्न हैं जो हमारे डेटाबेस पर प्रति दिन निष्पादित होते हैं। इन 10 हज़ार में से, ऐसे राक्षस हैं जो 0.1-0.3 एमएस के औसत रन समय के साथ 2-3 मिलियन बार चलते हैं और 30 सेकंड के औसत रन समय के साथ प्रश्न होते हैं जिन्हें दिन में 100 बार कहा जाता है।

सभी 10 हजार प्रश्नों को अनुकूलित करना संभव नहीं था, इसलिए हमने यह पता लगाने का निर्णय लिया कि डेटाबेस प्रदर्शन को सही ढंग से सुधारने के लिए प्रयासों को कहां निर्देशित करना है। कई पुनरावृत्तियों के बाद, हमने अनुरोधों को प्रकारों में विभाजित करना शुरू किया।

शीर्ष प्रश्न

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

सभी कंपनियों का सामान्य अभ्यास शीर्ष अनुरोधों के साथ काम करना है। उनमें से कुछ हैं, यहां तक कि एक अनुरोध का अनुकूलन 5-10% संसाधनों को मुक्त कर सकता है। हालाँकि, जैसे-जैसे परियोजना बड़ी होती जाती है, TOP प्रश्नों का अनुकूलन एक तेजी से गैर-तुच्छ कार्य बन जाता है। सभी सरल तरीकों को पहले से ही काम किया गया है, और यहां तक कि सबसे "कठिन" अनुरोध "केवल" 3-5% संसाधनों को दूर करता है। यदि कुल में TOP क्वेरीज़ 30-40% से कम समय लेती हैं, तो सबसे अधिक संभावना है कि आपने उन्हें जल्दी से काम करने के लिए पहले ही प्रयास कर लिया है और अगले समूह से प्रश्नों का अनुकूलन करने के लिए आगे बढ़ने का समय है।
यह इस सवाल का जवाब देने के लिए बनी हुई है कि इस समूह में कितने शीर्ष प्रश्न शामिल हैं। मैं आमतौर पर 10 से कम नहीं लेता हूं, लेकिन 20 से अधिक नहीं। मैं यह सुनिश्चित करने की कोशिश करता हूं कि शीर्ष समूह में पहले और आखिरी का समय 10 से अधिक बार न हो। यही है, यदि क्वेरी निष्पादन का समय 1 स्थान से 10 तक तेजी से गिरता है, तो मैं TOP-10 लेता हूं, यदि गिरावट चिकनी है, तो मैं समूह का आकार बढ़ाकर 15 या 20 कर देता हूं।

मध्यम किसान (मध्यम)

ये सभी अनुरोध हैं जो पिछले 5-10% के अपवाद के साथ, TOP के तुरंत बाद जाते हैं। आमतौर पर, इन विशेष अनुरोधों के अनुकूलन में सर्वर प्रदर्शन को बढ़ाने की क्षमता निहित होती है। ये प्रश्न 80% तक "वजन" कर सकते हैं। लेकिन फिर भी अगर उनका हिस्सा 50% से अधिक हो गया है, तो यह समय उन्हें और करीब से देखने का है।

पूंछ (पूंछ)

जैसा कि कहा गया था, ये अनुरोध अंत में चलते हैं और वे 5-10% समय लेते हैं। आप केवल उनके बारे में भूल सकते हैं यदि आप स्वचालित क्वेरी विश्लेषण टूल का उपयोग नहीं करते हैं, तो उनका अनुकूलन भी सस्ता हो सकता है।

प्रत्येक समूह का मूल्यांकन कैसे करें?

मैं एक SQL क्वेरी का उपयोग करता हूं जो PostgreSQL के लिए ऐसा मूल्यांकन करने में मदद करता है (मुझे यकीन है कि कई अन्य DBMS के लिए आप एक समान क्वेरी लिख सकते हैं)

एसक्यूएल क्वेरी TOP-MEDIUM-TAIL समूहों के आकार का अनुमान लगाने के लिए

SELECT sum(time_top) AS sum_top, sum(time_medium) AS sum_medium, sum(time_tail) AS sum_tail FROM ( SELECT CASE WHEN rn <= 20 THEN tt_percent ELSE 0 END AS time_top, CASE WHEN rn > 20 AND rn <= 800 THEN tt_percent ELSE 0 END AS time_medium, CASE WHEN rn > 800 THEN tt_percent ELSE 0 END AS time_tail FROM ( SELECT total_time / (SELECT sum(total_time) FROM pg_stat_statements) * 100 AS tt_percent, query, ROW_NUMBER () OVER (ORDER BY total_time DESC) AS rn FROM pg_stat_statements ORDER BY total_time DESC ) AS t ) AS ts

क्वेरी परिणाम तीन कॉलम हैं, जिनमें से प्रत्येक में इस समूह से प्रसंस्करण अनुरोधों को खर्च करने का एक प्रतिशत शामिल है। क्वेरी के अंदर, दो संख्याएँ होती हैं (मेरे मामले में, 20 और 800) जो एक समूह से दूसरे समूह में अलग-अलग अनुरोध करती हैं।

इस प्रकार अनुकूलन कार्य के प्रारंभ के समय अनुरोधों का अनुपात अब मोटे तौर पर सहसंबद्ध हो गया है।

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

अनुरोधों का पाठ प्राप्त करने के लिए हम इस तरह के अनुरोध का उपयोग करते हैं

 SELECT * FROM ( SELECT ROW_NUMBER () OVER (ORDER BY total_time DESC) AS rn, total_time / (SELECT sum(total_time) FROM pg_stat_statements) * 100 AS tt_percent, query FROM pg_stat_statements ORDER BY total_time DESC ) AS T WHERE rn <= 20 -- TOP -- rn > 20 AND rn <= 800 -- MEDIUM -- rn > 800 -- TAIL

यहां सबसे अधिक इस्तेमाल की जाने वाली ट्रिक्स की सूची दी गई है, जिससे हमें TOP प्रश्नों को गति देने में मदद मिली:

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

कभी-कभी परिवर्तन एक प्रभावशाली रीडिज़ाइन पर खींचे जाते हैं, लेकिन उन्होंने सिस्टम को उतारने का 5-10% दिया और उचित था। समय के साथ, निकास कम और कम हो गया, और रीडिज़ाइन को अधिक से अधिक गंभीर की आवश्यकता थी।

फिर हमने अनुरोधों के दूसरे समूह पर ध्यान आकर्षित किया - मध्यम किसानों का समूह। इसके बहुत अधिक अनुरोध हैं और ऐसा लग रहा था कि पूरे समूह का विश्लेषण करने में बहुत समय लगेगा। हालाँकि, अधिकांश प्रश्न अनुकूलन के लिए बहुत सरल थे, और कई समस्याओं को विभिन्न रूपों में दर्जनों बार दोहराया गया था। यहां कुछ विशिष्ट अनुकूलन के उदाहरण दिए गए हैं, जिन्हें हमने दर्जनों समान प्रश्नों पर लागू किया है और अनुकूलित प्रश्नों के प्रत्येक समूह ने डेटाबेस को 3-5% तक अनलोड किया है।

COUNT और पूर्ण तालिका स्कैन, EXISTS के साथ रिकॉर्ड की उपस्थिति के लिए जाँच करने के बजाय
हमें DISTINCT से छुटकारा मिल गया (कोई सामान्य नुस्खा नहीं है, लेकिन कभी-कभी आप अनुरोध को 10-100 गुना तेज करके आसानी से इससे छुटकारा पा सकते हैं)।

उदाहरण के लिए, बड़े वितरण तालिका (DELIVERY) पर सभी ड्राइवरों का चयन करने के लिए क्वेरी करने के बजाय
```
 SELECT DISTINCT P.ID, P.FIRST_NAME, P.LAST_NAME FROM DELIVERY D JOIN PERSON P ON D.DRIVER_ID = P.ID 
```
अपेक्षाकृत छोटी मेज के लिए एक अनुरोध किया
```
 SELECT P.ID, P.FIRST_NAME, P.LAST_NAME FROM PERSON WHERE EXISTS(SELECT D.ID FROM DELIVERY WHERE D.DRIVER_ID = P.ID) 
```
ऐसा लगता है कि हमने सहसंबंधी उप-वर्ग का इस्तेमाल किया, लेकिन यह 10 गुना से अधिक का त्वरण देता है।
कई मामलों में, COUNT और
अनुमानित मूल्य की गणना के द्वारा प्रतिस्थापित

के बजाय

 UPPER(s) LIKE JOHN%'

उपयोग

 s ILIKE “John%”

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

नतीजतन, हम तीन साल से एक ही हार्डवेयर पर काम कर रहे हैं। औसत दैनिक भार लगभग 30% है, चोटियों पर यह 70% तक पहुंच जाता है। अनुरोधों की संख्या के साथ-साथ उपयोगकर्ताओं की संख्या में लगभग 10 गुना वृद्धि हुई है। और TOP-MEDIUM प्रश्नों के इन बहुत समूहों की निरंतर निगरानी के लिए यह सब धन्यवाद। जैसे ही TOP समूह में एक नया अनुरोध दिखाई देता है, हम तुरंत इसका विश्लेषण करते हैं और इसे गति देने की कोशिश करते हैं। हम सप्ताह में एक बार क्वेरी विश्लेषण लिपियों का उपयोग करके मेडम समूह की समीक्षा करते हैं। यदि आपके पास नए अनुरोध हैं, जिन्हें हम पहले से ही जानते हैं कि कैसे अनुकूलन करना है, तो हम जल्दी से उन्हें बदलते हैं। कभी-कभी हमें नए अनुकूलन के तरीके मिलते हैं जिन्हें एक साथ कई प्रश्नों पर लागू किया जा सकता है।

हमारे पूर्वानुमानों के अनुसार, वर्तमान सर्वर उपयोगकर्ताओं की संख्या में 3-5 गुना वृद्धि करेगा। सच है, हमारे पास आस्तीन में एक और ट्रम्प कार्ड है, हमने अभी भी दर्पण के लिए चयनित प्रश्नों का अनुवाद नहीं किया है, जैसा कि अनुशंसित है। लेकिन हम सचेत रूप से ऐसा नहीं करते हैं, क्योंकि हम पहली बार "भारी तोपखाने" को चालू करने से पहले "स्मार्ट" अनुकूलन की संभावनाओं को पूरी तरह से समाप्त करना चाहते हैं।
किए गए कार्य पर एक महत्वपूर्ण नज़र ऊर्ध्वाधर स्केलिंग का उपयोग करने का सुझाव दे सकती है। विशेषज्ञों के समय को बर्बाद करने के बजाय अधिक शक्तिशाली सर्वर खरीदें। सर्वर में इतना खर्च नहीं हो सकता है, खासकर जब से वर्टिकल स्केलिंग की सीमा समाप्त नहीं हुई है। हालांकि, केवल अनुरोधों की संख्या 10 गुना बढ़ गई। कई वर्षों के लिए, सिस्टम की कार्यक्षमता में वृद्धि हुई है और अब अनुरोधों की अधिक किस्में हैं। कैशिंग के कारण जो कार्यक्षमता थी, वह कम अनुरोधों, इसके अलावा, अधिक कुशल अनुरोधों द्वारा की जाती है। तो आप वास्तविक त्वरण गुणांक प्राप्त करने के लिए सुरक्षित रूप से एक और 5 से गुणा कर सकते हैं। इसलिए, सबसे रूढ़िवादी अनुमानों के अनुसार, हम कह सकते हैं कि त्वरण 50 या अधिक बार था। 50 बार सर्वर को हिलाने से लागत अधिक आएगी। विशेष रूप से यह देखते हुए कि एक बार अनुकूलन हर समय किया जाता है, और हर महीने किराए के सर्वर के लिए एक बिल आता है।

बिल्डरों के लिए बी 2 बी सेवा के उदाहरण पर डेटाबेस प्रश्नों का अनुकूलन

पहली तस्वीर। गेय

दूसरी तस्वीर। सांख्यिकीय

शीर्ष प्रश्न

मध्यम किसान (मध्यम)

पूंछ (पूंछ)

More articles: