გახსოვთ როგორ დაიწყო ეს ყველაფერი. ყველაფერი პირველად და ისევ იყო

იმის შესახებ, თუ როგორ უნდა გაგვეკეთებინა PostgreSQL შეკითხვის ოპტიმიზაცია და რა გამოვიდა ამ ყველაფრისგან.
რატომ მოგიწია? დიახ, რადგან წინა 4 წლის განმავლობაში ყველაფერი მუშაობდა მშვიდად, წყნარად, საათის წკაპუნების მსგავსად.
როგორც ეპიგრაფი.

რეალურ მოვლენებზე დაფუძნებული.
ყველა სახელი შეიცვალა, დამთხვევები შემთხვევითია.

როცა გარკვეულ შედეგს მიაღწევ, ყოველთვის საინტერესოა გავიხსენოთ, რა იყო იმპულსი დასაწყისისთვის, საიდან დაიწყო ეს ყველაფერი.

ასე რომ, რა მოხდა შედეგად, მოკლედ არის აღწერილი სტატიაში "სინთეზი, როგორც PostgreSQL მუშაობის გაუმჯობესების ერთ-ერთი მეთოდი".

ალბათ საინტერესო იქნება წინა მოვლენების ჯაჭვის ხელახლა შექმნა.
ისტორიამ შეინახა ზუსტი დაწყების თარიღი - 2018-09-10 18:02:48.
ასევე, მოთხრობაში არის თხოვნა, საიდანაც ყველაფერი დაიწყო:
პრობლემის მოთხოვნააირჩიეთ
გვ.„PARAMETER_ID“, როგორც parameter_id,
pd."PD_NAME" AS pd_name,
pd"CUSTOMER_PARTNUMBER" AS კლიენტის_პარტნიმერი,
w. "LRM" AS LRM,
w. "LOTID" AS lotid,
w.„RTD_VALUE“ როგორც RTD_მნიშვნელობა,
w.“LOWER_SPEC_LIMIT” AS ქვედა_spec_limit,
w.“UPPER_SPEC_LIMIT” როგორც ზედა_spec_limit,
p"TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS დახარჯული_სახელი,
s.“SPENT_DATE” AS დახარჯული_თარიღი,
ამონაწერი (წელი „SPENT_DATE“-დან) როგორც წელი,
ამონაწერი (თვე "SPENT_DATE"-დან) თვედ,
s"REPORT_NAME" AS report_name,
p."STPM_NAME" AS stpm_name,
გვ.„CUSTOMERPARAM_NAME“ AS customerparam_name
wdata w-დან,
გაატარა ს,
pmtr p,
დახარჯული_პდ sp,
პდ პდ
WHERE s.„SPENT_ID“ = w.„SPENT_ID“
და გვ."PARAMETER_ID" = w"PARAMETER_ID"
AND s.“SPENT_ID” = sp.“SPENT_ID”
AND PD."PD_ID" = sp."PD_ID"
AND s.„SPENT_DATE“ >= '2018-07-01' და s.„SPENT_DATE“ <= '2018-09-30'
და s.“SPENT_DATE” = (SELECT MAX(s2.“SPENT_DATE”)
დახარჯული s2-დან,
wdata w2
WHERE s2.„SPENT_ID“ = w2.„SPENT_ID“
და w2.“LRM” = w.“LRM”);

პრობლემის აღწერა პროგნოზირებად სტანდარტულია - ”ყველაფერი ცუდია. მითხარი რა პრობლემაა“.
მაშინვე გამახსენდა ანეკდოტი 3 და ნახევარი დიუმიანი დისკების დროიდან:

ლამერი მოდის ჰაკერთან.
-არაფერი არ გამომდის, მითხარი სად არის პრობლემა.
-დნმ-ში...

მაგრამ, რა თქმა უნდა, ეს არ არის შესრულების ინციდენტების გადაჭრის გზა. "მათ შეიძლება არ ესმით ჩვენი" (თან ერთად). ჩვენ უნდა გავარკვიოთ.
აბა, მოდი ვითხრათ. შესაძლოა ამის შედეგად რაღაც დაგროვდეს.

გამოძიება დაიწყო

ასე რომ, რისი დანახვა შეიძლება დაუყოვნებლივ შეუიარაღებელი თვალით, ახსნის გარეშეც კი.
1) JOIN არ გამოიყენება. ეს ცუდია, განსაკუთრებით იმ შემთხვევაში, თუ კავშირების რაოდენობა ერთზე მეტია.
2) მაგრამ რაც კიდევ უფრო უარესია არის დაკავშირებული ქვემოკითხვები, უფრო მეტიც, აგრეგაციასთან. ეს ძალიან ცუდია.
ეს რა თქმა უნდა ცუდია. მაგრამ ეს მხოლოდ ერთი მხრივ. მეორე მხრივ, ეს ძალიან კარგია, რადგან პრობლემას აშკარად აქვს გამოსავალი და მოთხოვნა, რომელიც შეიძლება გაუმჯობესდეს.
არ წახვიდე მკითხავთან (C).
შეკითხვის გეგმა არც ისე რთულია, მაგრამ საკმაოდ საჩვენებელია:
შესრულების გეგმა

ყველაზე საინტერესო და სასარგებლო, როგორც ყოველთვის, არის დასაწყისში და ბოლოს.
ჩადგმული მარყუჟი (ღირებულება=935.84..479763226.18 მწკრივი=3322 სიგანე=135) (ფაქტობრივი დრო=31.536..8220420.295 მწკრივი=8111656 მარყუჟი=1)
დაგეგმვის დრო: 3.807 ms
შესრულების დრო: 8222351.640 ms
დასრულების დრო 2 საათზე მეტია.

მცდარი ჰიპოთეზები, რომლებსაც დრო დასჭირდათ

ჰიპოთეზა 1 - ოპტიმიზატორი უშვებს შეცდომას და ქმნის არასწორ გეგმას.

შესრულების გეგმის ვიზუალიზაციისთვის ჩვენ გამოვიყენებთ საიტს https://explain.depesz.com/. თუმცა, საიტმა არ აჩვენა არაფერი საინტერესო და სასარგებლო. ერთი და მეორე შეხედვით, არაფერია რაც ნამდვილად დაეხმარება. შესაძლებელია თუ არა სრული სკანირება მინიმალური იყოს. Განაგრძე.

ჰიპოთეზა 2-დარტყმა ბაზაზე ავტოვაკუუმის მხრიდან, თქვენ უნდა მოიცილოთ მუხრუჭები.

მაგრამ ავტოვაკუუმის დემონები კარგად იქცევიან, არ არის ხანგრძლივი პროცესები. არანაირი სერიოზული დატვირთვა. სხვა რამე უნდა ვეძებოთ.

ჰიპოთეზა 3 - სტატისტიკა მოძველებულია, ყველაფრის გადათვლაა საჭირო

კიდევ ერთხელ, ეს არა. სტატისტიკა განახლებულია. რაც, ავტოვაკუუმთან დაკავშირებული პრობლემების არარსებობის გათვალისწინებით, გასაკვირი არ არის.

დავიწყოთ ოპტიმიზაცია

მთავარი ცხრილი "wdata" რა თქმა უნდა არ არის პატარა, თითქმის 3 მილიონი ჩანაწერი.
და სწორედ ამ ცხრილს მიჰყვება სრული სკანირება.

ჰეშის მდგომარეობა: ((w."SPENT_ID" = s."SPENT_ID") და ((ქვეგეგმა 1) = s."SPENT_DATE"))
-> თანმიმდევრული სკანირება wdata w-ზე (ღირებულება=0.00..574151.49 სტრიქონი=26886249 სიგანე=46) (ფაქტობრივი დრო=0.005..8153.565 მწკრივი=26873950 მარყუჟი=1)
ჩვენ ვაკეთებთ სტანდარტულ რამეს: "მოდი, გავაკეთოთ ინდექსი და ყველაფერი გაფრინდება".
შექმენით ინდექსი „SPENT_ID“ ველზე
Როგორც შედეგი:
შეკითხვის შესრულების გეგმა ინდექსის გამოყენებით

აბა, დაეხმარა?
იყო: 8 222 351.640 ms (2 საათზე ცოტა მეტი)
გახდა: 6 985 431.575 ms (თითქმის 2 საათი)
ზოგადად, იგივე ვაშლი, გვერდითი ხედი.
გავიხსენოთ კლასიკა:
„იგივე გაქვს, მაგრამ ფრთების გარეშე? დაეძებს“.

პრინციპში, ამას შეიძლება ეწოდოს კარგი შედეგი, კარგი, არა კარგი, მაგრამ მისაღები. სულ მცირე, მიაწოდეთ კლიენტს დიდი ანგარიში, სადაც აღწერილია რამდენი გაკეთდა და რატომ იყო კარგი.
მაგრამ საბოლოო გადაწყვეტილება ჯერ კიდევ შორსაა. Ძალიან შორს.

ახლა კი ყველაზე საინტერესო - ჩვენ ვაგრძელებთ ოპტიმიზაციას, ჩვენ გავაპრიალებთ მოთხოვნას

ნაბიჯი პირველი - გამოიყენეთ JOIN

ხელახლა დაწერილი მოთხოვნა ახლა ასე გამოიყურება (კარგად მაინც უფრო ლამაზი):
შეკითხვა JOIN-ის გამოყენებითაირჩიეთ
გვ.„PARAMETER_ID“, როგორც parameter_id,
pd."PD_NAME" AS pd_name,
pd"CUSTOMER_PARTNUMBER" AS კლიენტის_პარტნიმერი,
w. "LRM" AS LRM,
w. "LOTID" AS lotid,
w.„RTD_VALUE“ როგორც RTD_მნიშვნელობა,
w.“LOWER_SPEC_LIMIT” AS ქვედა_spec_limit,
w.“UPPER_SPEC_LIMIT” როგორც ზედა_spec_limit,
p"TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS დახარჯული_სახელი,
s.“SPENT_DATE” AS დახარჯული_თარიღი,
ამონაწერი (წელი „SPENT_DATE“-დან) როგორც წელი,
ამონაწერი (თვე "SPENT_DATE"-დან) თვედ,
s"REPORT_NAME" AS report_name,
p."STPM_NAME" AS stpm_name,
გვ.„CUSTOMERPARAM_NAME“ AS customerparam_name
FROM wdata w INNER JOIN დახარჯულია ON w.“SPENT_ID”=s.”“SPENT_ID“
შიდა შეერთება pmtr p ON გვ.„PARAMETER_ID“ = w.„PARAMETER_ID“
INNER JOIN spent_pd sp ON s.“SPENT_ID” = sp.“SPENT_ID”
INNER JOIN pd pd ON pd.“PD_ID” = sp.“PD_ID”
WHERE
s.„SPENT_DATE“ >= '2018-07-01' და s.„SPENT_DATE“ <= '2018-09-30'და
s.“SPENT_DATE” = (SELECT MAX(s2.“SPENT_DATE”)
FROM wdata w2 INNER JOIN დახარჯულია s2 ON w2.“SPENT_ID”=s2.“SPENT_ID“
შიდა შეერთება wdata w
ON w2.“LRM” = w.“LRM” );
დაგეგმვის დრო: 2.486 ms
შესრულების დრო: 1223680.326 ms

ასე რომ, პირველი შედეგი.
იყო: 6 ms (თითქმის 985 საათი).
გახდა: 1 223 680.326 ms (20 წუთზე ცოტა მეტი).
კარგი შედეგი. პრინციპში, ისევ იქ შეგვეძლო გაჩერება. მაგრამ ეს იმდენად უინტერესოა, ვერ გაჩერდები.
FOR

ნაბიჯი მეორე - მოიშორეთ დაკავშირებული ქვემოთხოვნა

შეიცვალა მოთხოვნის ტექსტი:
კორელაციური ქვემოკითხვის გარეშეაირჩიეთ
გვ.„PARAMETER_ID“, როგორც parameter_id,
pd."PD_NAME" AS pd_name,
pd"CUSTOMER_PARTNUMBER" AS კლიენტის_პარტნიმერი,
w. "LRM" AS LRM,
w. "LOTID" AS lotid,
w.„RTD_VALUE“ როგორც RTD_მნიშვნელობა,
w.“LOWER_SPEC_LIMIT” AS ქვედა_spec_limit,
w.“UPPER_SPEC_LIMIT” როგორც ზედა_spec_limit,
p"TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS დახარჯული_სახელი,
s.“SPENT_DATE” AS დახარჯული_თარიღი,
ამონაწერი (წელი „SPENT_DATE“-დან) როგორც წელი,
ამონაწერი (თვე "SPENT_DATE"-დან) თვედ,
s"REPORT_NAME" AS report_name,
p."STPM_NAME" AS stpm_name,
გვ.„CUSTOMERPARAM_NAME“ AS customerparam_name
FROM wdata w INNER JOIN დახარჯული s ON s.“SPENT_ID” = w.“SPENT_ID”
შიდა შეერთება pmtr p ON გვ.„PARAMETER_ID“ = w.„PARAMETER_ID“
INNER JOIN spent_pd sp ON s.“SPENT_ID” = sp.“SPENT_ID”
INNER JOIN pd pd ON pd.“PD_ID” = sp.“PD_ID”
შიდა შეერთება (აირჩიეთ w2.“LRM”, MAX(s2.“SPENT_DATE”)
FROM დახარჯული s2 INNER JOIN wdata w2 ON s2.“SPENT_ID“ = w2.“SPENT_ID“
ჯგუფი w2-ის მიხედვით.“LRM”
) md on w.“LRM” = md.“LRM”
WHERE
s."SPENT_DATE" >= '2018-07-01' და s."SPENT_DATE" <= '2018-09-30';
დაგეგმვის დრო: 2.291 ms
შესრულების დრო: 165021.870 ms

იყო: 1 223 680.326 ms (20 წუთზე ცოტა მეტი).
გახდა: 165 021.870 ms (2 წუთზე ცოტა მეტი).
ეს უკვე საკმაოდ კარგია.
თუმცა, როგორც ბრიტანელები ამბობენ "მაგრამ, ყოველთვის არის მაგრამ" ძალიან კარგი შედეგი ავტომატურად უნდა აღძრას ეჭვს. აქ რაღაც არასწორია.

ჰიპოთეზა შეკითხვის შესწორების შესახებ კორელირებული ქვემოთხოვნის თავიდან ასაცილებლად სწორია. მაგრამ თქვენ უნდა შეცვალოთ იგი ცოტათი, რომ საბოლოო შედეგი იყოს სწორი.
შედეგად, პირველი შუალედური შედეგი:
შესწორებული მოთხოვნა კორელირებული ქვემოკითხვის გარეშეაირჩიეთ
გვ.„PARAMETER_ID“, როგორც parameter_id,
pd."PD_NAME" AS pd_name,
pd"CUSTOMER_PARTNUMBER" AS კლიენტის_პარტნიმერი,
w. "LRM" AS LRM,
w. "LOTID" AS lotid,
w.„RTD_VALUE“ როგორც RTD_მნიშვნელობა,
w.“LOWER_SPEC_LIMIT” AS ქვედა_spec_limit,
w.“UPPER_SPEC_LIMIT” როგორც ზედა_spec_limit,
p"TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS დახარჯული_სახელი,
s.“SPENT_DATE” AS დახარჯული_თარიღი,
ამონაწერი (წელი s.“SPENT_DATE”) როგორც წელი,
ამონაწერი (თვე s.“SPENT_DATE”) როგორც თვე,
s"REPORT_NAME" AS report_name,
p."STPM_NAME" AS stpm_name,
გვ.„CUSTOMERPARAM_NAME“ AS customerparam_name
FROM wdata w INNER JOIN დახარჯული s ON s.“SPENT_ID” = w.“SPENT_ID”
შიდა შეერთება pmtr p ON გვ.„PARAMETER_ID“ = w.„PARAMETER_ID“
INNER JOIN spent_pd sp ON s.“SPENT_ID” = sp.“SPENT_ID”
INNER JOIN pd pd ON pd.“PD_ID” = sp.“PD_ID”
შიდა შეერთება ( SELECT w2.“LRM”, MAX(s2.“SPENT_DATE”) როგორც “SPENT_DATE”
FROM დახარჯული s2 INNER JOIN wdata w2 ON s2.“SPENT_ID“ = w2.“SPENT_ID“
ჯგუფი w2-ის მიხედვით.“LRM”
) md ON md.“SPENT_DATE” = s.“SPENT_DATE” AND md.“LRM“ = w.“LRM“
WHERE
s."SPENT_DATE" >= '2018-07-01' და s."SPENT_DATE" <= '2018-09-30';
დაგეგმვის დრო: 3.192 ms
შესრულების დრო: 208014.134 ms

ასე რომ, ის, რაც ჩვენ საბოლოოდ მივიღეთ, არის პირველი მისაღები შედეგი, რომლის ჩვენებაც არ არის სირცხვილი მომხმარებლისთვის:
დაიწყო: 8 222 351.640 ms (2 საათზე მეტი)
ჩვენ მოვახერხეთ: 1 ms (223 წუთზე ცოტა მეტი) მიღწევა.
შედეგი (შუალედური): 208 014.134 ms (სულ რაღაც 3 წუთში).

შესანიშნავი შედეგი.

სულ

შეგვეძლო იქ გავჩერებულიყავით.
მაგრამ…
მადა მოდის ჭამასთან ერთად. ვინც ფეხით მოსიარულე გზას აითვისებს. ნებისმიერი შედეგი შუალედურია. გაჩერდა და გარდაიცვალა. და ა.შ.
გავაგრძელოთ ოპტიმიზაცია.
Შესანიშნავი იდეა. მითუმეტეს იმის გათვალისწინებით, რომ მომხმარებელს არც კი დაუპირისპირდა. და კიდევ მკაცრად ამისთვის.

ასე რომ, დროა მონაცემთა ბაზის ხელახალი დიზაინი. თავად შეკითხვის სტრუქტურის ოპტიმიზაცია აღარ არის შესაძლებელი (თუმცა, როგორც მოგვიანებით გაირკვა, არსებობს შესაძლებლობა უზრუნველყოს, რომ ყველაფერი რეალურად ვერ ხერხდება). მაგრამ მონაცემთა ბაზის დიზაინის ოპტიმიზაციის დაწყება და განვითარება უკვე ძალიან პერსპექტიული იდეაა. და რაც მთავარია საინტერესო. ისევ გაიხსენე შენი ახალგაზრდობა. მე მაშინვე არ გავხდი DBA, გავიზარდე როგორც პროგრამისტი (BASIC, ასამბლეერი, C, double-plus C, Oracle, plsql). საინტერესო თემა, რა თქმა უნდა, ცალკე მემუარისთვის ;-).
თუმცა, ნუ გავფანტავთ.

აქედან გამომდინარე,

ან იქნებ დაყოფა დაგვეხმარება?
სპოილერი - "დიახ, ეს დაეხმარა, მათ შორის შესრულების ოპტიმიზაციაში."

მაგრამ ეს სულ სხვა ამბავია...

Გაგრძელება იქნება…

წყარო: www.habr.com