ProHoster > блог > Администрација > Се сеќавате ли како започна се. Сè беше за прв пат и повторно

Се сеќавате ли како започна се. Сè беше за прв пат и повторно

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

Се сеќавате ли како започна се. Сè беше за прв пат и повторно

Врз основа на вистински настани.
Сите имиња се сменети, коинциденциите се случајни.

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

Значи, она што се случи како резултат е накратко опишано во статијата “Синтезата како еден од методите за подобрување на перформансите на PostgreSQL".

Веројатно ќе биде интересно да се рекреира синџирот на претходни настани.
Историјата го зачувала точниот датум на почеток - 2018-09-10 18:02:48.
Исто така, во приказната има барање од кое започна се:
Проблем барањеИЗБЕРЕТЕ
стр.„PARAMETER_ID“ како параметар_ид,
pd."PD_NAME" AS pd_име,
pd"CUSTOMER_PARTNUMBER" AS customer_partnumber,
w. "LRM" AS LRM,
w. "LOTID" AS lotid,
w.„RTD_VALUE“ КАКО RTD_вредност,
w.„LOWER_SPEC_LIMIT“ КАКО пониска_ограничена_специфика,
w.„UPPER_SPEC_LIMIT“ КАКО горна_специфична_граница,
стр."TYPE_CALCUL" AS type_calcul,
s"SPENT_NAME" AS потрошено_име,
s.„SPENT_DATE“ AS spent_date,
извадок (година од „SPENT_DATE“) КАКО година,
екстракт (месец од „SPENT_DATE“) како месец,
s"REPORT_NAME" AS report_name,
стр."STPM_NAME" AS stpm_name,
стр.„CUSTOMERPARAM_NAME“ AS customerparam_name
ОД wdata w,
потрошени с,
pmtr стр,
spent_pd sp,
пд пд
WHERE s.„SPENT_ID“ = w.„SPENT_ID“
И стр."PARAMETER_ID" = w"PARAMETER_ID"
И с.„SPENT_ID“ = сп.„SPENT_ID“
И пд."PD_ID" = сп."PD_ID"
AND s.„SPENT_DATE“ >= „2018-07-01“ И s.„SPENT_DATE“ <= „2018-09-30“
и s.„SPENT_DATE“ = (ИЗБЕРЕТЕ MAX(s2.„SPENT_DATE“)
ОД потрошените с2,
wdata w2
WHERE s2.„SPENT_ID“ = w2.„SPENT_ID“
И w2.„LRM“ = w.„LRM“);


Описот на проблемот е предвидливо стандарден - „Сè е лошо. Кажи ми што е проблемот“.
Веднаш се сетив на една анегдота од времето на возење од 3 и пол инчи:

Ламерот доаѓа кај хакерот.
-Ништо не ми оди, кажи ми каде е проблемот.
-Во ДНК...

Но, се разбира, ова не е начин да се решат инцидентите со перформансите. “Можеби нема да не разберат" (Со). Треба да го сфатиме.
Па, ајде да копаме. Можеби нешто ќе се акумулира како резултат.

Се сеќавате ли како започна се. Сè беше за прв пат и повторно

Почна истрага

Значи, што може да се види веднаш со голо око, без притоа да се прибегне кон ОБјаснување.
1) JOIN не се користат. Ова е лошо, особено ако бројот на врски е повеќе од една.
2) Но, она што е уште полошо е корелираните подпрашања, згора на тоа, со агрегација. Ова е многу лошо.
Ова е лошо секако. Но, ова е само од една страна. Од друга страна, ова е многу добро, бидејќи проблемот очигледно има решение и барање што може да се подобри.
Не оди кај гатачка (В).
Планот за пребарување не е толку комплициран, но е прилично индикативен:
План за извршувањеСе сеќавате ли како започна се. Сè беше за прв пат и повторно

Најинтересно и најкорисно, како и обично, е на почетокот и на крајот.
Вгнездена јамка (цена=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“ како параметар_ид,
pd."PD_NAME" AS pd_име,
pd"CUSTOMER_PARTNUMBER" AS customer_partnumber,
w. "LRM" AS LRM,
w. "LOTID" AS lotid,
w.„RTD_VALUE“ КАКО RTD_вредност,
w.„LOWER_SPEC_LIMIT“ КАКО пониска_ограничена_специфика,
w.„UPPER_SPEC_LIMIT“ КАКО горна_специфична_граница,
стр."TYPE_CALCUL" AS type_calcul,
s"SPENT_NAME" AS потрошено_име,
s.„SPENT_DATE“ AS spent_date,
извадок (година од „SPENT_DATE“) КАКО година,
екстракт (месец од „SPENT_DATE“) како месец,
s"REPORT_NAME" AS report_name,
стр."STPM_NAME" AS stpm_name,
стр.„CUSTOMERPARAM_NAME“ AS customerparam_name
ОД wdata w INNER JOIN потрошени s ON w.“SPENT_ID”=s.”“SPENT_ID“
ВНАТРЕШЕН ПРИКЛУЧУВАЊЕ pmtr p ВКЛУЧЕНО стр.„PARAMETER_ID“ = w.„PARAMETER_ID“
ВНАТРЕШЕН ПРИКЛУЧУВАЊЕ 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“)
ОД wdata w2 ВНАТРЕШЕН ПРИКЛУЧУВАЊЕ потрошен 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 минути).
Добар резултат. Во принцип, повторно, би можеле да застанеме тука. Но, тоа е толку неинтересно, што не можете да запрете.
ЗА

Се сеќавате ли како започна се. Сè беше за прв пат и повторно

Чекор два - ослободете се од поврзаното подпрашање

Променет текст на барањето:
Без поврзано подпрашањеИЗБЕРЕТЕ
стр.„PARAMETER_ID“ како параметар_ид,
pd."PD_NAME" AS pd_име,
pd"CUSTOMER_PARTNUMBER" AS customer_partnumber,
w. "LRM" AS LRM,
w. "LOTID" AS lotid,
w.„RTD_VALUE“ КАКО RTD_вредност,
w.„LOWER_SPEC_LIMIT“ КАКО пониска_ограничена_специфика,
w.„UPPER_SPEC_LIMIT“ КАКО горна_специфична_граница,
стр."TYPE_CALCUL" AS type_calcul,
s"SPENT_NAME" AS потрошено_име,
s.„SPENT_DATE“ AS spent_date,
извадок (година од „SPENT_DATE“) КАКО година,
екстракт (месец од „SPENT_DATE“) како месец,
s"REPORT_NAME" AS report_name,
стр."STPM_NAME" AS stpm_name,
стр.„CUSTOMERPARAM_NAME“ AS customerparam_name
ОД wdata w ВНАТРЕШЕН ПРИКЛУЧУВАЊЕ потрошени s ON s.„SPENT_ID“ = w.„SPENT_ID“
ВНАТРЕШЕН ПРИКЛУЧУВАЊЕ pmtr p ВКЛУЧЕНО стр.„PARAMETER_ID“ = w.„PARAMETER_ID“
ВНАТРЕШЕН ПРИКЛУЧУВАЊЕ spent_pd sp ON s.„SPENT_ID“ = sp.„SPENT_ID“
ВНАТРЕШЕН ПРИКЛУЧУВАЊЕ pd pd ON pd.„PD_ID“ = sp.„PD_ID“
ВНАТРЕШНО ПРИКЛУЧУВАЊЕ (ИЗБЕРЕТЕ w2. „LRM“, MAX(s2. „ПОТРЕБЕН_ДАТУМО“)
ОД потрошени s2 ВНАТРЕШНИ ПРИКЛУЧИ 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.291 ms
Време на извршување: 165021.870 ms

Беше: 1 223 680.326 ms (нешто повеќе од 20 минути).
Стана: 165 021.870 ms (нешто повеќе од 2 минути).
Ова е веќе доста добро.
Сепак, како што велат Британците „Но, секогаш постои но" Премногу добар резултат треба автоматски да предизвика сомнеж. Нешто не е во ред тука.

Хипотезата за корекција на барањето за да се ослободи од корелираното подпрашање е точна. Но, треба малку да го дотерате за конечниот резултат да биде точен.
Како резултат на тоа, првиот среден резултат:
Изменето барање без поврзано подпрашањеИЗБЕРЕТЕ
стр.„PARAMETER_ID“ како параметар_ид,
pd."PD_NAME" AS pd_име,
pd"CUSTOMER_PARTNUMBER" AS customer_partnumber,
w. "LRM" AS LRM,
w. "LOTID" AS lotid,
w.„RTD_VALUE“ КАКО RTD_вредност,
w.„LOWER_SPEC_LIMIT“ КАКО пониска_ограничена_специфика,
w.„UPPER_SPEC_LIMIT“ КАКО горна_специфична_граница,
стр."TYPE_CALCUL" AS type_calcul,
s"SPENT_NAME" AS потрошено_име,
s.„SPENT_DATE“ AS spent_date,
извадок (година од s.„SPENT_DATE“) КАКО година,
екстракт (месец од s.„SPENT_DATE“) како месец,
s"REPORT_NAME" AS report_name,
стр."STPM_NAME" AS stpm_name,
стр.„CUSTOMERPARAM_NAME“ AS customerparam_name
ОД wdata w ВНАТРЕШЕН ПРИКЛУЧУВАЊЕ потрошени s ON s.„SPENT_ID“ = w.„SPENT_ID“
ВНАТРЕШЕН ПРИКЛУЧУВАЊЕ pmtr p ВКЛУЧЕНО стр.„PARAMETER_ID“ = w.„PARAMETER_ID“
ВНАТРЕШЕН ПРИКЛУЧУВАЊЕ spent_pd sp ON s.„SPENT_ID“ = sp.„SPENT_ID“
ВНАТРЕШЕН ПРИКЛУЧУВАЊЕ pd pd ON pd.„PD_ID“ = sp.„PD_ID“
ВНАТРЕШНА ПРИКЛУЧУВА
ОД потрошени s2 ВНАТРЕШНИ ПРИКЛУЧИ wdata w2 ON s2.„SPENT_ID“ = w2.„SPENT_ID“
ГРУПА ПО w2.„LRM“
) md ON md.„SPENT_DATE“ = s.„SPENT_DATE“ И md.„LRM“ = w.„LRM“
КАДЕ
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

Додадете коментар