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

थप उत्पादक सर्भरमा सार्न र प्रणाली कार्यक्षमता कायम नगरी डेटाबेसमा प्रश्नहरूको संख्या 10 गुणा कसरी बढाउने? म तपाईलाई बताउनेछु कि हामीले कसरी हाम्रो डाटाबेसको कार्यसम्पादनमा आएको गिरावटको सामना गर्यौं, हामीले SQL क्वेरीहरूलाई सकेसम्म धेरै प्रयोगकर्ताहरूलाई सेवा दिन र कम्प्युटिङ स्रोतहरूको लागत नबढाउन कसरी अनुकूलित गर्यौं।

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

तस्विर एक। लिरिकल

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

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

त्यसोभए हामीसँग लगभग 10 हजार विभिन्न प्रश्नहरू छन् जुन हाम्रो डाटाबेसमा प्रति दिन कार्यान्वयन गरिन्छ। यी 10 हजार मध्ये, त्यहाँ दानवहरू छन् जुन 2-3 ms को औसत कार्यान्वयन समयको साथ 0.1-0.3 मिलियन पटक कार्यान्वयन गरिन्छ, र त्यहाँ 30 सेकेन्डको औसत कार्यान्वयन समयका साथ प्रश्नहरू छन् जसलाई दिनको 100 पटक भनिन्छ।

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

शीर्ष अनुरोधहरू

यी सबैभन्दा भारी प्रश्नहरू हुन् जसले सबैभन्दा बढी समय लिन्छ (कुल समय)। यी प्रश्नहरू हुन् जसलाई या त प्रायः भनिन्छ वा क्वेरीहरू जुन कार्यान्वयन गर्न धेरै समय लाग्छ (लामो र बारम्बार प्रश्नहरू गतिको लागि लडाईको पहिलो पुनरावृत्तिमा अनुकूलित गरिएको थियो)। नतिजाको रूपमा, सर्भरले तिनीहरूको कार्यान्वयनमा सबैभन्दा धेरै समय खर्च गर्दछ। यसबाहेक, कुल कार्यान्वयन समय र IO समय द्वारा अलग रूपमा शीर्ष अनुरोधहरू अलग गर्न महत्त्वपूर्ण छ। त्यस्ता प्रश्नहरूलाई अनुकूलन गर्ने तरिकाहरू अलि फरक छन्।

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

मध्यम किसानहरू

यी सबै अनुरोधहरू हुन् जुन TOP पछि तुरुन्तै आउँछन्, अन्तिम 5-10% को अपवाद बाहेक। सामान्यतया, यी प्रश्नहरू अनुकूलन गर्दा सर्भरको कार्यसम्पादनमा ठूलो वृद्धि गर्ने अवसर हुन्छ। यी अनुरोधहरू 80% सम्म तौल गर्न सक्छन्। तर यदि तिनीहरूको अंश 50% नाघेको छ भने, यो तिनीहरूलाई अझ सावधानीपूर्वक हेर्ने समय हो।

पुच्छर

उल्लेख गरिए अनुसार, यी प्रश्नहरू अन्तमा आउँछन् र 5-10% समय लिन्छन्। यदि तपाइँ स्वचालित क्वेरी विश्लेषण उपकरणहरू प्रयोग गर्नुहुन्न भने मात्र तपाइँ तिनीहरूलाई बिर्सन सक्नुहुन्छ, त्यसपछि तिनीहरूलाई अनुकूलन गर्न पनि सस्तो हुन सक्छ।

प्रत्येक समूहलाई कसरी मूल्याङ्कन गर्ने?

म एक SQL क्वेरी प्रयोग गर्छु जसले PostgreSQL को लागि यस्तो मूल्याङ्कन गर्न मद्दत गर्दछ (म निश्चित छु कि यस्तै प्रश्न धेरै अन्य DBMSs को लागी लेख्न सकिन्छ)

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 अनुरोधहरूको अंश घट्यो, र कठिन अनुरोधहरूलाई गति दिन थप प्रयासहरू गर्नुपर्यो।

अनुरोधहरूको पाठ प्राप्त गर्न हामी निम्न अनुरोध प्रयोग गर्दछौं

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

यहाँ सबैभन्दा धेरै प्रयोग हुने प्रविधिहरूको सूची छ जसले हामीलाई शीर्ष प्रश्नहरूको गति बढाउन मद्दत गर्‍यो:

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

कहिलेकाहीँ परिवर्तनहरू प्रभावशाली पुन: डिजाइनमा परिणत भए, तर तिनीहरूले प्रणाली लोडको 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
  

  अपेक्षाकृत सानो तालिका PERSON मा प्रश्न गर्नुभयो

  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 ms लिन्छ, 3rd सय गह्रौं प्रश्नहरू मध्ये एक हो, र समग्र डाटाबेस लोड समयको एक प्रतिशतको शतांश लिन्छ। तर उही प्रकारको प्रश्नहरूको समूहमा उही नुस्खा लागू गरेर, हामीले केही प्रतिशत फिर्ता जित्यौं। सबै सयौं प्रश्नहरूको म्यानुअल रूपमा समीक्षा गर्न समय बर्बाद नगर्नको लागि, हामीले उही प्रकारका प्रश्नहरू फेला पार्न नियमित अभिव्यक्तिहरू प्रयोग गर्ने धेरै सरल लिपिहरू लेख्यौं। नतिजाको रूपमा, स्वचालित रूपमा क्वेरीहरूको समूह खोजीले हामीलाई सामान्य प्रयासको साथ हाम्रो कार्यसम्पादनमा थप सुधार गर्न अनुमति दियो।

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

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

स्रोत: www.habr.com