Спомняте ли си как започна всичко. Всичко беше за първи път и отново

За това как трябваше да се справя с оптимизирането на заявки на PostgreSQL и какво излезе от всичко това.
Защо трябваше? Да, защото предните 4 години всичко работеше тихо, спокойно, като часовник тиктакаше.
като епиграф.

Базиран на реални събития.
Всички имена са сменени, съвпаденията са случайни.

Когато се постигне определен резултат, винаги е интересно да си спомним какъв е бил тласъкът за началото, как започна всичко.

И така, какво се случи в резултат на това е описано накратко в статията „Синтезът като един от методите за подобряване на производителността на PostgreSQL".

Вероятно ще бъде интересно да пресъздадем веригата от предишни събития.
Историята запази точната начална дата — 2018-09-10 18:02:48.
Също така в историята има молба, от която всичко започна:
Заявка за проблемМАРКИРАЙ
стр. „PARAMETER_ID“ като parameter_id,
pd. "PD_NAME" КАТО pd_name,
pd. „CUSTOMER_PARTNUMBER“ КАТО номер на клиентска част,
w."LRM" КАТО LRM,
w. "LOTID" КАТО lotid,
w. "RTD_VALUE" КАТО RTD_стойност,
w. „LOWER_SPEC_LIMIT“ КАТО долна_спец_лимит,
w. „UPPER_SPEC_LIMIT“ КАТО горна_спец_лимит,
p."TYPE_CALCUL" КАТО type_calcul,
s."SPENT_NAME" КАТО изразходвано_име,
s."SPENT_DATE" КАТО изразходвана_дата,
извлечение (година от "SPENT_DATE") КАТО година,
извлечение (месец от "SPENT_DATE") като месец,
s."REPORT_NAME" КАТО report_name,
стр. "STPM_NAME" КАТО stpm_name,
p."CUSTOMERPARAM_NAME" КАТО customerparam_name
ОТ wdataw,
изразходвани s,
pmtrp,
изразходван_pdsp,
пд пд
WHERE s."SPENT_ID" = w."SPENT_ID"
И p."PARAMETER_ID" = w."PARAMETER_ID"
И s."SPENT_ID" = sp."SPENT_ID"
И пд. "PD_ID" = пр. "PD_ID"
И 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 и половина инча:

Ламерът идва при хакера.
- Нищо не ми работи, кажете ми къде е проблема.
- В ДНК...

Но, разбира се, това не е начинът за решаване на инциденти с производителността. “Може да не ни разберат“ (Със). Трябва да го разбера.
Е, нека да копаем. Може би това ще се натрупа в резултат.

инвестицията започна

И така, това, което може да се види веднага с просто око, без дори да се прибягва до помощта на EXPLAIN.
1) JOIN не се използват. Това е лошо, особено ако броят на връзките е повече от една.
2) Но това, което е още по-лошо - корелирана подзаявка, освен това с агрегиране. Това е много лошо.
Това е лошо, разбира се. Но това е само от една страна. От друга страна, това е много добре, защото проблемът очевидно има решение и заявката може да бъде подобрена.
Не отивайте при гадателката (C).
Планът на заявката не е толкова сложен, но доста показателен:
План за изпълнение

Най-интересното и полезно, както обикновено, в началото и в края.
Вложен цикъл (цена=935.84..479763226.18 редове=3322 ширина=135) (действително време=31.536..8220420.295 реда=8111656 цикли=1)
Време за планиране: 3.807ms
Време за изпълнение: 8222351.640ms
Времето за изпълнение е повече от 2 часа.

Фалшиви хипотези, които отнеха време

Хипотеза 1- Оптимизаторът греши, изгражда грешен план.

За визуализиране на плана за изпълнение ще използваме сайта https://explain.depesz.com/. Сайтът обаче не показа нищо интересно или полезно. На пръв и втори поглед – нищо, което наистина да помогне. Освен ако - Пълното сканиране е минимално. Продължавай.

Хипотеза 2-Удар върху основата от страната на автовакуума, трябва да се отървете от спирачките.

Но автовакуумните демони се държат добре, няма дълготрайни процеси. Някакво сериозно натоварване - не. Трябва да се търси нещо друго.

Хипотеза 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 (малко над 2 часа)
Стана: 6 985 431.575 ms (почти 2 часа)
Като цяло, същите ябълки, страничен изглед.
Да си припомним класиката:
„Имате ли същия, но без крила? Ще търси".

По принцип това може да се нарече добър резултат, добре, не добър, но приемлив. Най-малкото, предоставете голям отчет на клиента, описващ колко е направено и защо това, което е направено, е добро.
Окончателното решение обаче е още далеч. Много далеч.

И сега най-интересното - продължаваме да оптимизираме, ще изпипаме заявката

Първа стъпка - използвайте JOIN

Пренаписана заявка, сега изглежда така (добре поне по-красива):
Заявка с помощта на JOINМАРКИРАЙ
стр. „PARAMETER_ID“ като parameter_id,
pd. "PD_NAME" КАТО pd_name,
pd. „CUSTOMER_PARTNUMBER“ КАТО номер на клиентска част,
w."LRM" КАТО LRM,
w. "LOTID" КАТО lotid,
w. "RTD_VALUE" КАТО RTD_стойност,
w. „LOWER_SPEC_LIMIT“ КАТО долна_спец_лимит,
w. „UPPER_SPEC_LIMIT“ КАТО горна_спец_лимит,
p."TYPE_CALCUL" КАТО type_calcul,
s."SPENT_NAME" КАТО изразходвано_име,
s."SPENT_DATE" КАТО изразходвана_дата,
извлечение (година от "SPENT_DATE") КАТО година,
извлечение (месец от "SPENT_DATE") като месец,
s."REPORT_NAME" КАТО report_name,
стр. "STPM_NAME" КАТО stpm_name,
p."CUSTOMERPARAM_NAME" КАТО customerparam_name
FROM wdata w INNER JOIN изразходвани s ON w."SPENT_ID"=s."SPENT_ID"
INNER JOIN pmtr p ON p."PARAMETER_ID" = w."PARAMETER_ID"
ВЪТРЕШНО ПРИСЪЕДИНЯВАНЕ, прекарано_pd sp ON s."SPENT_ID" = sp."SPENT_ID"
INNER JOIN pd pd ON pd."PD_ID" = sp."PD_ID"
КЪДЕ
s."SPENT_DATE" >= '2018-07-01' И s."SPENT_DATE" <= '2018-09-30'AND
s."SPENT_DATE" = (SELECT MAX(s2."SPENT_DATE")
FROM wdata w2 INNER JOIN изразходва s2 ON w2."SPENT_ID"=s2."SPENT_ID"
INNER JOIN wdata w
НА w2."LRM" = w."LRM" );
Време за планиране: 2.486ms
Време за изпълнение: 1223680.326ms

И така, ето първия резултат.
Беше: 6 985 431.575 ms (почти 2 часа).
Стана: 1 223 680.326 ms (малко над 20 минути).
Добър резултат. По принцип отново би било възможно да се спре дотук. Но толкова безинтересно, че не можете да спрете.
НБС

Стъпка втора - Отървете се от корелираната подзаявка

Променен текст на заявката:
Няма корелирана подзаявкаМАРКИРАЙ
стр. „PARAMETER_ID“ като parameter_id,
pd. "PD_NAME" КАТО pd_name,
pd. „CUSTOMER_PARTNUMBER“ КАТО номер на клиентска част,
w."LRM" КАТО LRM,
w. "LOTID" КАТО lotid,
w. "RTD_VALUE" КАТО RTD_стойност,
w. „LOWER_SPEC_LIMIT“ КАТО долна_спец_лимит,
w. „UPPER_SPEC_LIMIT“ КАТО горна_спец_лимит,
p."TYPE_CALCUL" КАТО type_calcul,
s."SPENT_NAME" КАТО изразходвано_име,
s."SPENT_DATE" КАТО изразходвана_дата,
извлечение (година от "SPENT_DATE") КАТО година,
извлечение (месец от "SPENT_DATE") като месец,
s."REPORT_NAME" КАТО report_name,
стр. "STPM_NAME" КАТО stpm_name,
p."CUSTOMERPARAM_NAME" КАТО customerparam_name
FROM wdata w INNER JOIN изразходва s ON s."SPENT_ID" = w."SPENT_ID"
INNER JOIN pmtr p ON p."PARAMETER_ID" = w."PARAMETER_ID"
ВЪТРЕШНО ПРИСЪЕДИНЯВАНЕ, прекарано_pd sp ON s."SPENT_ID" = sp."SPENT_ID"
INNER JOIN pd pd ON pd."PD_ID" = sp."PD_ID"
INNER JOIN (SELECT w2."LRM", MAX(s2."SPENT_DATE")
FROM изразходван s2 INNER JOIN wdata w2 ON s2."SPENT_ID" = w2."SPENT_ID"
ГРУПИРАНЕ ПО w2.LRM
) md върху w. "LRM" = md. "LRM"
КЪДЕ
s."SPENT_DATE" >= '2018-07-01' И s."SPENT_DATE" <= '2018-09-30';
Време за планиране: 2.291ms
Време за изпълнение: 165021.870ms

Беше: 1 223 680.326 ms (малко над 20 минути).
Стана: 165 021.870 ms (малко над 2 минути).
Това вече е доста добре.
Но както казват англичаните,Но винаги има едно но". Твърде добрият резултат автоматично трябва да събуди подозрение. Нещо не е наред тук.

Хипотезата за коригиране на заявката, за да се отърве от корелираната подзаявка, е правилна. Но има нужда от малко коригиране, за да се получи правилният краен резултат.
В резултат на това първият междинен резултат:
Редактирана заявка без корелирана подзаявкаМАРКИРАЙ
стр. „PARAMETER_ID“ като parameter_id,
pd. "PD_NAME" КАТО pd_name,
pd. „CUSTOMER_PARTNUMBER“ КАТО номер на клиентска част,
w."LRM" КАТО LRM,
w. "LOTID" КАТО lotid,
w. "RTD_VALUE" КАТО RTD_стойност,
w. „LOWER_SPEC_LIMIT“ КАТО долна_спец_лимит,
w. „UPPER_SPEC_LIMIT“ КАТО горна_спец_лимит,
p."TYPE_CALCUL" КАТО type_calcul,
s."SPENT_NAME" КАТО изразходвано_име,
s."SPENT_DATE" КАТО изразходвана_дата,
извлечение (година от s. "SPENT_DATE") КАТО година,
извлечение (месец от s. "SPENT_DATE") като месец,
s."REPORT_NAME" КАТО report_name,
стр. "STPM_NAME" КАТО stpm_name,
p."CUSTOMERPARAM_NAME" КАТО customerparam_name
FROM wdata w INNER JOIN изразходва s ON s."SPENT_ID" = w."SPENT_ID"
INNER JOIN pmtr p ON p."PARAMETER_ID" = w."PARAMETER_ID"
ВЪТРЕШНО ПРИСЪЕДИНЯВАНЕ, прекарано_pd sp ON s."SPENT_ID" = sp."SPENT_ID"
INNER JOIN pd pd ON pd."PD_ID" = sp."PD_ID"
INNER JOIN ( SELECT w2."LRM", MAX(s2."SPENT_DATE") AS "SPENT_DATE"
FROM изразходван s2 INNER JOIN wdata w2 ON s2."SPENT_ID" = w2."SPENT_ID"
ГРУПИРАНЕ ПО w2.LRM
) md НА md."SPENT_DATE" = s."SPENT_DATE" И md."LRM" = w."LRM"
КЪДЕ
s."SPENT_DATE" >= '2018-07-01' И s."SPENT_DATE" <= '2018-09-30';
Време за планиране: 3.192ms
Време за изпълнение: 208014.134ms

И така, това, което имаме като резултат, е първият приемлив резултат, който не се срамуваме да покажем на клиента:
Започна с: 8 222 351.640 ms (повече от 2 часа)
Постигнато: 1 223 680.326 ms (малко над 20 минути).
Резултат (междинен): 208 014.134 ms (малко над 3 минути).

Отличен резултат.

Общо

Това можеше да спре.
НО…
Апетитът идва с яденето. Пътят ще бъде овладян с ходене. Всеки резултат е междинен. Спря мъртъв. и т.н.
Да продължим с оптимизацията.
Великолепна идея. Особено като се има предвид, че клиентът дори не беше против. И дори силно - за.

И така, време е да преработим базата данни. Самата структура на заявката вече не може да бъде оптимизирана (въпреки че, както се оказа по-късно, има опция всичко наистина да лети). Но сега за оптимизиране и развитие на дизайна на базата данни, това вече е много обещаваща идея. И най-важното интересно. Отново, спомнете си младостта. В крайна сметка не станах веднага DBA, израснах от програмисти (basic, асемблер, si, si два пъти плюс, oracle, plsql). Интересна тема, разбира се, за отделни мемоари ;-).
Нека обаче не се отклоняваме.

По този начин,

И може би разделянето ще ни помогне?
Спойлер - "Да, помогна и за оптимизиране на производителността, включително."

Но това е съвсем различна история...

Следва продължение…

Източник: www.habr.com