तपाईलाई याद छ यो सबै कसरी सुरु भयो। सबै कुरा पहिलो पटक र बारम्बार थियो

हामीले कसरी PostgreSQL क्वेरीलाई अप्टिमाइज गर्नुपर्‍यो र यो सबैबाट के आयो भन्ने बारे।
किन गर्नु पर्यो ? हो, किनकि अघिल्लो 4 वर्षको लागि सबै कुरा चुपचाप, शान्त रूपमा, घडीको टिकिङ जस्तै काम गर्यो।
एपिग्राफको रूपमा।

वास्तविक घटनाहरूमा आधारित।
सबै नामहरू परिवर्तन भएका छन्, संयोगहरू अनियमित छन्।

जब तपाइँ एक निश्चित नतिजा प्राप्त गर्नुहुन्छ, यो सँधै याद गर्न रोचक छ कि सुरुको लागि प्रेरणा के थियो, जहाँ यो सबै सुरु भयो।

त्यसोभए, परिणामको रूपमा के भयो लेखमा छोटकरीमा वर्णन गरिएको छ "PostgreSQL कार्यसम्पादन सुधार गर्नका लागि एक विधिको रूपमा संश्लेषण"।

यो सायद अघिल्लो घटनाहरूको श्रृंखला पुन: सिर्जना गर्न रोचक हुनेछ।
इतिहासले सही सुरु मिति बचत गर्यो - 2018-09-10 18:02:48।
साथै, कथामा त्यहाँ एउटा अनुरोध छ जसबाट यो सबै सुरु भयो:
समस्या अनुरोधचयन गर्नुहोस्
p. "PARAMETER_ID" parameter_id को रूपमा,
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" as customer_partnumber,
w"LRM" AS LRM,
w. "LOTID" को रूपमा लोटिड,
w.“RTD_VALUE” AS RTD_value,
w.“LOWER_SPEC_LIMIT” as low_spec_limit,
w.“UPPER_SPEC_LIMIT” as upper_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS spent_name,
s. "SPENT_DATE" AS spent_date,
निकाल्नुहोस् ("SPENT_DATE" बाट वर्ष) AS वर्ष,
निकाल्नुहोस् ("SPENT_DATE" बाट महिना) महिनाको रूपमा,
s."REPORT_NAME" AS report_name,
p."STPM_NAME" AS stpm_name,
p. "CUSTOMERPARAM_NAME" as customerparam_name
wdata w बाट,
बित्यो,
pmtr p,
spent_pd sp,
pd pd
WHERE s.“SPENT_ID” = w.“SPENT_ID”
र p."PARAMETER_ID" = w."PARAMETER_ID"
र s। "SPENT_ID" = sp। "SPENT_ID"
र pd."PD_ID" = sp."PD_ID"
र s। “SPENT_DATE” >= '2018-07-01' र s। “SPENT_DATE” <= '2018-09-30'
र s. "SPENT_DATE" = (MAX (s2. "SPENT_DATE") लाई चयन गर्नुहोस्
खर्च गरेको s2 बाट,
wdata w2
WHERE s2.“SPENT_ID” = w2.“SPENT_ID”
र w2। "LRM" = w. "LRM");

समस्याको विवरण अनुमानित रूपमा मानक छ - "सबै कुरा खराब छ। समस्या के हो भन ।”
मैले तुरुन्तै साढे तीन इन्च ड्राइभको समयको एउटा किस्सा सम्झे:

लेमर ह्याकरमा आउँछ।
- मेरो लागि केहि काम गर्दैन, मलाई समस्या कहाँ छ भन्नुहोस्।
-डीएनएमा...

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

छानबिन सुरु भयो

त्यसोभए, नग्न आँखाले तुरुन्तै के देख्न सकिन्छ, स्पष्टीकरणको सहारा बिना।
1) JOIN प्रयोग गरिदैन। यो खराब छ, विशेष गरी यदि जडानहरूको संख्या एक भन्दा बढी छ।
2) तर अझ नराम्रो कुरा सहसम्बन्धित सबक्वेरीहरू हो, यसबाहेक, एकत्रीकरणको साथ। यो धेरै खराब छ।
यो अवश्य पनि नराम्रो हो। तर यो एकातिर मात्रै हो। अर्कोतर्फ, यो धेरै राम्रो छ, किनभने समस्या स्पष्ट रूपमा समाधान र सुधार गर्न सकिन्छ कि एक अनुरोध छ।
ज्योतिषी (C)कहाँ नजानुहोस्।
क्वेरी योजना त्यति जटिल छैन, तर यो एकदम संकेतात्मक छ:
कार्यान्वयन योजना

सबैभन्दा रोचक र उपयोगी, सामान्य रूपमा, सुरु र अन्त्यमा छ।
नेस्टेड लूप (लागत=935.84..479763226.18 पङ्क्तिहरू=3322 चौडाइ=135) (वास्तविक समय=31.536..8220420.295 पङ्क्तिहरू=8111656 लुप्स=1)
योजना समय: 3.807 एमएस
कार्यान्वयन समय: 8222351.640 ms
पूरा समय 2 घण्टा भन्दा बढी छ।

गलत परिकल्पनाहरू जसले समय लियो

परिकल्पना 1 - अनुकूलकले गल्ती गर्छ र गलत योजना बनाउँछ।

कार्यान्वयन योजना कल्पना गर्न, हामी साइट प्रयोग गर्नेछौं https://explain.depesz.com/। यद्यपि, साइटले केहि रोचक वा उपयोगी देखाएको छैन। पहिलो र दोस्रो नजरमा, त्यहाँ केहि पनि छैन जुन वास्तवमा मद्दत गर्न सक्छ। के यो सम्भव छ कि पूर्ण स्क्यान न्यूनतम छ। अघि बढ।

हाइपोथेसिस २-अटोभ्याक्यूम पक्षबाट आधारमा प्रभाव, तपाईंले ब्रेकबाट छुटकारा पाउन आवश्यक छ।

तर अटोभ्याक्यूम डेमनहरूले राम्रो व्यवहार गर्छन्, त्यहाँ कुनै लामो-झट्ने प्रक्रियाहरू छैनन्। कुनै गम्भीर भार छैन। हामीले अरु केहि खोज्नु पर्छ।

Hypothesis 3 - तथ्याङ्कहरू पुरानो छन्, सबै कुरा पुन: गणना गर्न आवश्यक छ

फेरि, त्यो होइन। तथ्याङ्कहरू अद्यावधिक छन्। जुन, अटोभ्याक्यूमको साथ समस्याहरूको अभावलाई दिइयो, आश्चर्यजनक छैन।

अनुकूलन सुरु गरौं

मुख्य तालिका 'wdata' पक्कै पनि सानो छैन, लगभग 3 मिलियन रेकर्डहरू।
र यो यो तालिका हो जुन पूर्ण स्क्यान पछ्याउँछ।

ह्यास कन्ड: ((w."SPENT_ID" = s."SPENT_ID") र (उपयोजना 1) = s."SPENT_DATE"))
-> Seq स्क्यान wdata w मा (लागत=0.00..574151.49 पङ्क्तिहरू=26886249 चौडाई=46) (वास्तविक समय=0.005..8153.565 पङ्क्तिहरू=26873950 लुप्स=1)
हामी मानक कुरा गर्छौं: "आउनुहोस्, अनुक्रमणिका बनाउनुहोस् र सबै कुरा उड्नेछ।"
"SPENT_ID" फिल्डमा अनुक्रमणिका सिर्जना गरियो
फलस्वरूप:
अनुक्रमणिका प्रयोग गरी कार्यान्वयन योजना क्वेरी गर्नुहोस्

खैर, यसले मद्दत गर्यो?
थियो: 8 222 351.640 ms (२ घण्टा भन्दा अलि बढी)
यो भयो: ६९८५ ४३१.५७५ ms (लगभग २ घण्टा)
सामान्यतया, एउटै स्याउ, साइड दृश्य।
क्लासिक्स सम्झनुहोस्:
"के तपाईसँग एउटै छ, तर पखेटा बिना? खोज्नेछ।"

सिद्धान्त मा, यो एक राम्रो परिणाम, राम्रो, राम्रो छैन, तर स्वीकार्य भन्न सकिन्छ। कम्तिमा, ग्राहकलाई कति काम गरियो र किन राम्रो भयो भनेर वर्णन गर्ने ठूलो रिपोर्ट प्रदान गर्नुहोस्।
तर, अन्तिम निर्णय भने अझै टाढा छ । धेरै टाढा।

र अब सबैभन्दा चाखलाग्दो कुरा - हामी अनुकूलन गर्न जारी राख्छौं, हामी अनुरोध पोलिश गर्नेछौं

पहिलो चरण - JOIN प्रयोग गर्नुहोस्

पुन: लेखिएको अनुरोध अब यस्तो देखिन्छ (राम्रो कम्तिमा अझ सुन्दर):
JOIN प्रयोग गरेर सोध्नुहोस्चयन गर्नुहोस्
p. "PARAMETER_ID" parameter_id को रूपमा,
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" as customer_partnumber,
w"LRM" AS LRM,
w. "LOTID" को रूपमा लोटिड,
w.“RTD_VALUE” AS RTD_value,
w.“LOWER_SPEC_LIMIT” as low_spec_limit,
w.“UPPER_SPEC_LIMIT” as upper_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS spent_name,
s. "SPENT_DATE" AS spent_date,
निकाल्नुहोस् ("SPENT_DATE" बाट वर्ष) AS वर्ष,
निकाल्नुहोस् ("SPENT_DATE" बाट महिना) महिनाको रूपमा,
s."REPORT_NAME" AS report_name,
p."STPM_NAME" AS stpm_name,
p. "CUSTOMERPARAM_NAME" as customerparam_name
भित्री सम्मिलित हुने wdata बाट w.“SPENT_ID”=s।”“SPENT_ID”
INNER Join pmtr p ON p. "PARAMETER_ID" = w. "PARAMETER_ID"
INNER JOIN spent_pd sp ON s। "SPENT_ID" = sp। "SPENT_ID"
भित्री सम्मिलित pd pd ON pd. "PD_ID" = sp. "PD_ID"
कहाँ
s.“SPENT_DATE” >= '2018-07-01' र s। “SPENT_DATE” <= '2018-09-30' र
s. "SPENT_DATE" = (MAX चयन गर्नुहोस्(s2. "SPENT_DATE")
w2 मा w2 भित्री सम्मिलित हुने wdata बाट। "SPENT_ID"=s2। "SPENT_ID"
INNER Join wdata w
ON w2. "LRM" = w. "LRM");
योजना समय: 2.486 एमएस
कार्यान्वयन समय: 1223680.326 ms

त्यसैले, पहिलो परिणाम।
थियो: 6 ms (लगभग 985 घण्टा)।
यो भयो: 1 223 680.326 ms (केवल 20 मिनेट भन्दा बढी)।
राम्रो नतिजा। सिद्धान्तमा, फेरि, हामी त्यहाँ रोक्न सक्छौं। तर यो धेरै चाखलाग्दो छ, तपाईं रोक्न सक्नुहुन्न।
को लागी

चरण दुई - सहसंबद्ध सबक्वेरीबाट छुटकारा पाउनुहोस्

अनुरोध पाठ परिवर्तन:
सहसम्बन्धित सबक्वेरी बिनाचयन गर्नुहोस्
p. "PARAMETER_ID" parameter_id को रूपमा,
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" as customer_partnumber,
w"LRM" AS LRM,
w. "LOTID" को रूपमा लोटिड,
w.“RTD_VALUE” AS RTD_value,
w.“LOWER_SPEC_LIMIT” as low_spec_limit,
w.“UPPER_SPEC_LIMIT” as upper_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS spent_name,
s. "SPENT_DATE" AS spent_date,
निकाल्नुहोस् ("SPENT_DATE" बाट वर्ष) AS वर्ष,
निकाल्नुहोस् ("SPENT_DATE" बाट महिना) महिनाको रूपमा,
s."REPORT_NAME" AS report_name,
p."STPM_NAME" AS stpm_name,
p. "CUSTOMERPARAM_NAME" as customerparam_name
बाट wdata w INNER JOIN खर्च s ON s। "SPENT_ID" = w. "SPENT_ID"
INNER Join pmtr p ON p. "PARAMETER_ID" = w. "PARAMETER_ID"
INNER JOIN spent_pd sp ON s। "SPENT_ID" = sp। "SPENT_ID"
भित्री सम्मिलित pd pd ON pd. "PD_ID" = sp. "PD_ID"
भित्री सामेल हुनुहोस् (w2 चयन गर्नुहोस्। "LRM", MAX(s2. "SPENT_DATE")
बाट s2 INNER W2 ON s2 मा wdata सम्मिलित हुनुहोस्। "SPENT_ID" = w2। "SPENT_ID"
W2 द्वारा समूह। "LRM"
) md on w. "LRM" = md. "LRM"
कहाँ
s।"SPENT_DATE" >= '2018-07-01' र s।"SPENT_DATE" <= '2018-09-30';
योजना समय: 2.291 एमएस
कार्यान्वयन समय: 165021.870 ms

थियो: 1 223 680.326 ms (केवल 20 मिनेट भन्दा बढी)।
यो भयो: 165 021.870 ms (केवल 2 मिनेट भन्दा बढी)।
यो पहिले नै धेरै राम्रो छ।
यद्यपि, बेलायतीहरूले भनेझैं "तर, त्यहाँ सधैं एक तर छ" धेरै राम्रो नतिजाले स्वतः शंका जगाउनुपर्छ। यहाँ केहि गलत छ।

सहसम्बन्धित सबक्वेरीबाट छुटकारा पाउनको लागि क्वेरी सच्याउने बारे परिकल्पना सही छ। तर अन्तिम परिणाम सही हुनको लागि तपाईंले यसलाई अलिकति ट्वीक गर्न आवश्यक छ।
नतिजाको रूपमा, पहिलो मध्यवर्ती परिणाम:
सहसम्बन्धित सबक्वेरी बिना सम्पादन गरिएको क्वेरीचयन गर्नुहोस्
p. "PARAMETER_ID" parameter_id को रूपमा,
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" as customer_partnumber,
w"LRM" AS LRM,
w. "LOTID" को रूपमा लोटिड,
w.“RTD_VALUE” AS RTD_value,
w.“LOWER_SPEC_LIMIT” as low_spec_limit,
w.“UPPER_SPEC_LIMIT” as upper_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS spent_name,
s. "SPENT_DATE" AS spent_date,
निकाल्नुहोस् (s. “SPENT_DATE” बाट वर्ष) AS वर्ष,
निकाल्नुहोस् (s. “SPENT_DATE” बाट महिना) महिनाको रूपमा,
s."REPORT_NAME" AS report_name,
p."STPM_NAME" AS stpm_name,
p. "CUSTOMERPARAM_NAME" as customerparam_name
बाट wdata w INNER JOIN खर्च s ON s। "SPENT_ID" = w. "SPENT_ID"
INNER Join pmtr p ON p. "PARAMETER_ID" = w. "PARAMETER_ID"
INNER JOIN spent_pd sp ON s। "SPENT_ID" = sp। "SPENT_ID"
भित्री सम्मिलित pd pd ON pd. "PD_ID" = sp. "PD_ID"
भित्री सम्मिलित (w2 चयन गर्नुहोस्। "LRM", MAX(s2. "SPENT_DATE") "SPENT_DATE" को रूपमा
बाट s2 INNER W2 ON s2 मा wdata सम्मिलित हुनुहोस्। "SPENT_ID" = w2। "SPENT_ID"
W2 द्वारा समूह। "LRM"
) md ON md। "SPENT_DATE" = s। "SPENT_DATE" AND md। "LRM" = w. "LRM"
कहाँ
s।"SPENT_DATE" >= '2018-07-01' र s।"SPENT_DATE" <= '2018-09-30';
योजना समय: 3.192 एमएस
कार्यान्वयन समय: 208014.134 ms

त्यसोभए, हामीले के साथ अन्त्य गर्छौं त्यो पहिलो स्वीकार्य परिणाम हो, जुन ग्राहकलाई देखाउन लाजको कुरा होइन:
यसबाट सुरु भयो: 8 222 351.640 ms (2 घण्टा भन्दा बढी)
हामीले प्राप्त गर्न सफल भयौं: 1 ms (223 मिनेट भन्दा अलि बढी)।
नतिजा (अन्तरिम): 208 014.134 ms (केवल 3 मिनेट भन्दा बढी)।

उत्कृष्ट परिणाम।

परिणाम

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

त्यसोभए, यो डाटाबेस पुन: डिजाइनको लागि समय हो। क्वेरी ढाँचा आफैंलाई अब अप्टिमाइज गर्न सकिँदैन (यद्यपि, यो पछि देखा पर्यो, त्यहाँ सबै कुरा वास्तवमा असफल हुन्छ भनेर सुनिश्चित गर्न विकल्प छ)। तर डाटाबेस डिजाइनलाई अनुकूलन र विकास गर्न सुरु गर्नु पहिले नै धेरै आशाजनक विचार हो। र सबैभन्दा महत्त्वपूर्ण रोचक। फेरि, आफ्नो जवानी सम्झनुहोस्। आखिर, म तुरुन्तै DBA बन्न सकिन, म एक प्रोग्रामर (बेसिक, एसेम्बलर, C, डबल-प्लस C, Oracle, plsql) को रूपमा हुर्कें। एक रोचक विषय, निस्सन्देह, एक अलग संस्मरण को लागी ;-)।
यद्यपि, विचलित नगरौं।

र त्यसैले,

वा शायद विभाजनले हामीलाई मद्दत गर्नेछ?
स्पोइलर - "हो, यसले मद्दत गर्यो, प्रदर्शनलाई अनुकूलन गर्न सहित।"

तर यो एकदम फरक कथा हो ...

फेरि जारी राखौंला…

स्रोत: www.habr.com