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

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

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

चित्र एक. गेय

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

चित्र दो. सांख्यिकीय

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

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

शीर्ष अनुरोध

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

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

मध्यम किसान

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

पूँछ

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

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

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

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

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

यहां सबसे अधिक उपयोग की जाने वाली तकनीकों की एक सूची दी गई है जिससे हमें टॉप क्वेरीज़ को तेज़ करने में मदद मिली:

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

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

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

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

  उदाहरण के लिए, डिलीवरी की एक बड़ी तालिका से सभी ड्राइवरों का चयन करने के लिए एक क्वेरी के बजाय (डिलीवरी)

  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 ग्रुप में कोई नया अनुरोध आता है, हम तुरंत उसका विश्लेषण करते हैं और उसे गति देने का प्रयास करते हैं। हम क्वेरी विश्लेषण स्क्रिप्ट का उपयोग करके सप्ताह में एक बार मीडियम समूह की समीक्षा करते हैं। यदि हमारे सामने नई क्वेरीज़ आती हैं जिन्हें हम पहले से ही जानते हैं कि कैसे अनुकूलित किया जाए, तो हम उन्हें तुरंत बदल देते हैं। कभी-कभी हमें नई अनुकूलन विधियाँ मिलती हैं जिन्हें एक साथ कई प्रश्नों पर लागू किया जा सकता है।

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

स्रोत: www.habr.com