Arról, hogyan kellett optimalizálnunk a PostgreSQL lekérdezést, és mi jött ki az egészből.
Miért kellett? Igen, mert az előző 4 évben minden csendesen, nyugodtan működött, mint az óra ketyeg.
epigráfként.
Valós események alapján.
Minden név megváltozott, a véletlenek véletlenek.
Amikor elérsz egy bizonyos eredményt, mindig érdekes visszaemlékezni, mi volt a lendület a kezdethez, hol kezdődött minden.
Tehát mi történt ennek eredményeként, röviden leírja a cikk "".
Valószínűleg érdekes lesz újrateremteni a korábbi események láncolatát.
Az előzmények elmentették a pontos kezdési dátumot - 2018-09-10 18:02:48.
Ezenkívül a történetben van egy kérés, amelyből minden kezdődött:
Probléma kérésSELECT
p.„PARAMETER_ID” mint paraméter_azonosítója,
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" AS customer_partnumber,
w. "LRM" AS LRM,
w. "LOTID" AS lotid,
w.„RTD_VALUE” AS RTD_value,
w."LOWER_SPEC_LIMIT" AS alsó_spec_limit,
w.„UPPER_SPEC_LIMIT” AS felső_spec_korlát,
p"TYPE_CALCUL" AS type_calcul,
s"SPENT_NAME" AS elköltött_név,
s.„SPENT_DATE” AS eltöltött_dátum,
kivonat(év from "SPENT_DATE") AS év,
kivonat(month from "SPENT_DATE") mint hónap,
s"REPORT_NAME" AS jelentés_neve,
p."STPM_NAME" AS stpm_name,
p.„CUSTOMERPARAM_NAME” AS ügyfélparam_neve
FROM wdata w,
töltött s,
pmtr p,
spend_pd sp,
pd pd
WHERE s.„SPENT_ID” = w.„SPENT_ID”
ÉS p."PARAMETER_ID" = w."PARAMETER_ID"
ÉS s.„SPENT_ID” = sp.„SPENT_ID”
ÉS pd."PD_ID" = sp."PD_ID"
ÉS s.„SPENT_DATE” >= '2018-07-01' ÉS s.„SPENT_DATE” <= '2018-09-30'
és s."SPENT_DATE" = (SELECT MAX(s2."SPENT_DATE")
AZ elköltött s2-től,
wdata w2
WHERE s2.„ELKÉPTETT_AZONOSÍTÓ” = w2.„ELKEDTETT_ID”
ÉS w2.„LRM” = w.„LRM”);
A probléma leírása előre láthatóan szabványos - „Minden rossz. Mondd el, mi a probléma."
Rögtön eszembe jutott egy anekdota a 3 és fél hüvelykes meghajtók idejéből:
A lamer odajön a hackerhez.
-Nekem semmi sem működik, mondd meg hol a probléma.
- A DNS-ben...
De természetesen ez nem a teljesítményincidensek megoldásának módja. "Lehet, hogy nem értenek meg minket" (Val vel). Ki kell találnunk.
Nos, ássunk. Talán ennek hatására felhalmozódik valami.
Megindult a nyomozás
Tehát, amit szabad szemmel azonnal látni lehet, anélkül, hogy még a MEGmagyarázáshoz is folyamodnánk.
1) A JOIN nem használatos. Ez rossz, különösen, ha a kapcsolatok száma egynél több.
2) De ami még rosszabb, az a korrelált részlekérdezések, ráadásul az összesítéssel. Ez nagyon rossz.
Ez persze rossz. De ez csak egyrészt. Másrészt ez nagyon jó, mert a problémának egyértelműen van megoldása és javítható kérése.
Ne menj jóshoz (C).
A lekérdezési terv nem túl bonyolult, de meglehetősen tájékoztató jellegű:
Végrehajtási terv
A legérdekesebb és leghasznosabb, mint általában, az elején és a végén található.
Beágyazott hurok (költség=935.84..479763226.18 sor=3322 szélesség=135) (tényleges idő=31.536..8220420.295 sor=8111656 hurok=1)
Tervezési idő: 3.807 ms
Végrehajtási idő: 8222351.640 ms
A befejezési idő több mint 2 óra.
Hamis hipotézisek, amelyekhez idő kellett
1. hipotézis – Az optimalizáló hibázik és rossz tervet készít.
A kiviteli terv megjelenítéséhez az oldalt fogjuk használni . Az oldal azonban nem mutatott semmi érdekeset vagy hasznosat. Első és második pillantásra nincs semmi, ami igazán segíthetne. Lehetséges, hogy a Full Scan minimális. Menj tovább.
2. hipotézis – Az autovákuum oldaláról ütközés az alapra, meg kell szabadulni a fékektől.
De az autovákuumos démonok jól viselkednek, nincsenek hosszan tartó folyamatok. Nincs komoly terhelés. Valami mást kell keresnünk.
3. hipotézis - A statisztikák elavultak, mindent újra kell számolni
Megint nem az. A statisztikák naprakészek. Ami, tekintettel az autovákuummal kapcsolatos problémák hiányára, nem meglepő.
Kezdjük az optimalizálást
A 'wdata' főtábla természetesen nem kicsi, csaknem 3 millió rekord.
És ezt a táblázatot követi a Full Scan.
Hash feltétel: ((w."SPENT_ID" = s."SPENT_ID") ÉS ((1. alterv) = s."SPENT_DATE"))
-> Seq Scan a wdata w-n (költség=0.00..574151.49 sor=26886249 szélesség=46) (tényleges idő=0.005..8153.565 sor=26873950 hurok=1)
Mi a szokásos dolgot csináljuk: "gyerünk, csináljunk indexet, és minden repülni fog."
Létrehozott egy indexet a „SPENT_ID” mezőben
Ennek eredményeként:
Lekérdezés végrehajtási terv index használatával
Nos, segített?
Ez volt: 8 222 351.640 ms (kicsit több mint 2 óra)
Lett belőle: 6 985 431.575 ms (majdnem 2 óra)
Általában ugyanaz az alma, oldalnézetben.
Emlékezzünk a klasszikusokra:
„Ugyanaz van, de szárnyak nélkül? Keresni fog".
Ezt elvileg jó eredménynek lehetne nevezni, hát nem jónak, de elfogadhatónak. Legalább készítsen egy nagyméretű jelentést az ügyfélnek, amelyben leírja, hogy mennyit tett meg, és miért volt jó, amit tettek.
A végső döntés azonban még mindig messze van. Nagyon messze.
És most a legérdekesebb dolog - folytatjuk az optimalizálást, csiszoljuk a kérést
Első lépés – A JOIN használata
Az átírt kérés most így néz ki (hát legalább szebb):
Lekérdezés a JOIN használatávalSELECT
p.„PARAMETER_ID” mint paraméter_azonosítója,
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" AS customer_partnumber,
w. "LRM" AS LRM,
w. "LOTID" AS lotid,
w.„RTD_VALUE” AS RTD_value,
w."LOWER_SPEC_LIMIT" AS alsó_spec_limit,
w.„UPPER_SPEC_LIMIT” AS felső_spec_korlát,
p"TYPE_CALCUL" AS type_calcul,
s"SPENT_NAME" AS elköltött_név,
s.„SPENT_DATE” AS eltöltött_dátum,
kivonat(év from "SPENT_DATE") AS év,
kivonat(month from "SPENT_DATE") mint hónap,
s"REPORT_NAME" AS jelentés_neve,
p."STPM_NAME" AS stpm_name,
p.„CUSTOMERPARAM_NAME” AS ügyfélparam_neve
FROM wdata w INNER JOIN elköltött s ON w.„SPENT_ID”=s.”„SPENT_ID”
BELSŐ CSATLAKOZÁS pmtr p ON p.„PARAMETER_ID” = w.„PARAMETER_ID”
BELSŐ CSATLAKOZÁS, töltött_pd sp ON s.“SPENT_ID” = sp.„SPENT_ID”
BELSŐ JOIN pd pd ON pd.“PD_ID” = sp.„PD_ID”
AHOL
s."SPENT_DATE" >= '2018-07-01' ÉS s."SPENT_DATE" <= '2018-09-30'AND
s.„SPENT_DATE” = (MAX. KIVÁLASZTÁS(s2.„SPENT_DATE”)
A wdata w2 BELSŐ CSATLAKOZÁSBÓL elköltött s2 ON W2.„SPENT_ID”=s2.„SPENT_ID”-én
BELSŐ CSATLAKOZÁS wdata w
ON w2.“LRM” = w.„LRM” );
Tervezési idő: 2.486 ms
Végrehajtási idő: 1223680.326 ms
Szóval, az első eredmény.
Ez volt: 6 985 431.575 ms (majdnem 2 óra).
Lett belőle: 1 223 680.326 ms (valamivel több mint 20 perc).
Jó eredmény. Elvileg itt is megállhatnánk. De ez annyira érdektelen, hogy nem lehet abbahagyni.
FOR
Második lépés – megszabaduljon a korrelált részlekérdezéstől
Módosított kérés szövege:
Korrelált részlekérdezés nélkülSELECT
p.„PARAMETER_ID” mint paraméter_azonosítója,
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" AS customer_partnumber,
w. "LRM" AS LRM,
w. "LOTID" AS lotid,
w.„RTD_VALUE” AS RTD_value,
w."LOWER_SPEC_LIMIT" AS alsó_spec_limit,
w.„UPPER_SPEC_LIMIT” AS felső_spec_korlát,
p"TYPE_CALCUL" AS type_calcul,
s"SPENT_NAME" AS elköltött_név,
s.„SPENT_DATE” AS eltöltött_dátum,
kivonat(év from "SPENT_DATE") AS év,
kivonat(month from "SPENT_DATE") mint hónap,
s"REPORT_NAME" AS jelentés_neve,
p."STPM_NAME" AS stpm_name,
p.„CUSTOMERPARAM_NAME” AS ügyfélparam_neve
FROM wdata w INNER JOIN elköltve s ON s.„SPENT_ID” = w.„SPENT_ID”
BELSŐ CSATLAKOZÁS pmtr p ON p.„PARAMETER_ID” = w.„PARAMETER_ID”
BELSŐ CSATLAKOZÁS, töltött_pd sp ON s.“SPENT_ID” = sp.„SPENT_ID”
BELSŐ JOIN pd pd ON pd.“PD_ID” = sp.„PD_ID”
BELSŐ CSATLAKOZÁS (SELECT w2.“LRM”, MAX(s2.„SPENT_DATE”)
FROM elköltött s2 BELSŐ CSATLAKOZÁS wdata w2 ON s2.„ELKÉPTETT_AZONOSÍTÓ” = w2.„ELTÖLT_ID”
GROUP BY w2.„LRM”
) md on w.„LRM” = md.„LRM”
AHOL
s."SPENT_DATE" >= '2018-07-01' ÉS s."SPENT_DATE" <= '2018-09-30';
Tervezési idő: 2.291 ms
Végrehajtási idő: 165021.870 ms
Ez volt: 1 223 680.326 ms (valamivel több mint 20 perc).
Lett belőle: 165 021.870 ms (valamivel több mint 2 perc).
Ez már egész jó.
De ahogy a britek mondják "De mindig van egy de" A túl jó eredménynek automatikusan gyanút kell keltenie. Valami nem stimmel.
Helyes az a hipotézis, hogy a lekérdezést korrelált részlekérdezéstől való megszabadulás érdekében korrigáljuk. De egy kicsit módosítanod kell, hogy a végeredmény helyes legyen.
Ennek eredményeként az első köztes eredmény:
Szerkesztett lekérdezés korrelált részlekérdezés nélkülSELECT
p.„PARAMETER_ID” mint paraméter_azonosítója,
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" AS customer_partnumber,
w. "LRM" AS LRM,
w. "LOTID" AS lotid,
w.„RTD_VALUE” AS RTD_value,
w."LOWER_SPEC_LIMIT" AS alsó_spec_limit,
w.„UPPER_SPEC_LIMIT” AS felső_spec_korlát,
p"TYPE_CALCUL" AS type_calcul,
s"SPENT_NAME" AS elköltött_név,
s.„SPENT_DATE” AS eltöltött_dátum,
kivonat(év from s."SPENT_DATE") AS év,
kivonat(month from s."SPENT_DATE") mint hónap,
s"REPORT_NAME" AS jelentés_neve,
p."STPM_NAME" AS stpm_name,
p.„CUSTOMERPARAM_NAME” AS ügyfélparam_neve
FROM wdata w INNER JOIN elköltve s ON s.„SPENT_ID” = w.„SPENT_ID”
BELSŐ CSATLAKOZÁS pmtr p ON p.„PARAMETER_ID” = w.„PARAMETER_ID”
BELSŐ CSATLAKOZÁS, töltött_pd sp ON s.“SPENT_ID” = sp.„SPENT_ID”
BELSŐ JOIN pd pd ON pd.“PD_ID” = sp.„PD_ID”
BELSŐ CSATLAKOZÁS ( SELECT w2.“LRM”, MAX(s2.“SPENT_DATE”) AS „SPENT_DATE”
FROM elköltött s2 BELSŐ CSATLAKOZÁS wdata w2 ON s2.„ELKÉPTETT_AZONOSÍTÓ” = w2.„ELTÖLT_ID”
GROUP BY w2.„LRM”
) md ON md.„SPENT_DATE” = s.„SPENT_DATE” ÉS md.„LRM” = w.„LRM”
AHOL
s."SPENT_DATE" >= '2018-07-01' ÉS s."SPENT_DATE" <= '2018-09-30';
Tervezési idő: 3.192 ms
Végrehajtási idő: 208014.134 ms
Így végül az első elfogadható eredményre jutunk, amelyet nem szégyen megmutatni az ügyfélnek:
Ezzel kezdődött: 8 222 351.640 ms (több mint 2 óra)
Sikerült elérni: 1 223 680.326 20 ms (kicsit több mint XNUMX perc).
Eredmény (köztes): 208 014.134 ms (valamivel több mint 3 perc).
Kiváló eredmény.
Teljes
Ott megállhattunk volna.
DE…
Az étvágy evéssel jön. Aki jár, az uralja az utat. Bármilyen eredmény köztes. Megállt és meghalt. Stb.
Folytassuk az optimalizálást.
Jó ötlet. Főleg, ha figyelembe vesszük, hogy az ügyfél nem is bánta. És még erősen is érte.
Tehát itt az ideje az adatbázis újratervezésének. Magát a lekérdezési struktúrát már nem lehet optimalizálni (bár, mint később kiderült, van lehetőség arra, hogy valóban minden meghiúsuljon). De az adatbázis-terv optimalizálásának és fejlesztésének megkezdése már nagyon ígéretes ötlet. És ami a legfontosabb érdekes. Ismét emlékezz a fiatalságodra. Hiszen nem lettem azonnal DBA, hanem programozóként nőttem fel (BASIC, assembler, C, dupla plusz C, Oracle, plsql). Érdekes téma persze külön visszaemlékezésnek ;-).
Azonban ne tereljük el a figyelmünket.
Így
Vagy talán a particionálás segít nekünk?
Spoiler – „Igen, segített, beleértve a teljesítmény optimalizálását is.”
De ez egy teljesen más történet...
Folytatjuk…
Forrás: will.com