Ngeunaan kumaha urang kedah ngaoptimalkeun pamundut PostgreSQL sareng naon anu kaluar tina éta sadayana.
Naha anjeun kedah? Sumuhun, sabab pikeun 4 taun saméméhna sagalana digawé quietly, tenang, kawas jam ticking a.
Salaku hiji epigraph.
Dumasar kana kajadian nyata.
Kabéh ngaran geus robah, coincidences acak.
Lamun anjeun ngahontal hasil nu tangtu, éta salawasna metot pikeun nginget naon nu impetus pikeun awal, dimana eta sadayana dimimitian.
Janten, naon anu kajantenan salaku hasilna dijelaskeun sacara ringkes dina tulisan "".
Éta sigana bakal pikaresepeun pikeun nyiptakeun deui ranté acara-acara saméméhna.
Sajarah disimpen tanggal mimiti pasti - 2018-09-10 18:02:48.
Ogé, dina carita aya pamundut ti mana eta sadayana dimimitian:
Paménta masalahMILIH
p. "PARAMETER_ID" salaku parameter_id,
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 lower_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,
ekstrak(taun ti "SPENT_DATE") AS taun,
ekstrak (bulan ti "SPENT_DATE") salaku bulan,
s."REPORT_NAME" AS report_name,
p."STPM_NAME" AS stpm_name,
p. "CUSTOMERPARAM_NAME" AS customerparam_name
TI wdata w,
dihabiskeun s,
pmtr p,
spent_pd sp,
pd pd
WHERE s.“SPENT_ID” = w.“SPENT_ID”
JEUNG p."PARAMETER_ID" = w."PARAMETER_ID"
AND s.“SPENT_ID” = sp.“SPENT_ID”
JEUNG pd."PD_ID" = sp."PD_ID"
JEUNG s.“SPENT_DATE” >= '2018-07-01' JEUNG s.“SPENT_DATE” <= '2018-09-30'
jeung s.“SPENT_DATE” = (PILIH MAX(s2.“SPENT_DATE”)
TI spent s2,
wdata w2
WHERE s2. "SPENT_ID" = w2. "SPENT_ID"
AND w2. "LRM" = w. "LRM");
Katerangan ngeunaan masalahna diprediksi standar - "Sadayana goréng. Caritakeun naon masalahna.”
Kuring langsung émut hiji anekdot ti jaman drive 3 satengah inci:
Lamer datang ka hacker.
-Euweuh gawéna pikeun kuring, ngabejaan ka kuring dimana masalahna.
- Dina DNA ...
Tapi tangtosna, ieu sanés cara pikeun ngabéréskeun insiden pagelaran. “Éta bisa jadi teu ngarti urang"(Kalayan). Urang kudu angka eta kaluar.
Muhun, hayu urang ngagali. Meureun hal bakal ngumpulkeun salaku hasilna.
Panalungtikan dimimitian
Ku kituna, naon bisa ditempo langsung ku mata taranjang, tanpa malah resorting ka NERANGKEUN.
1) JOINs teu dipaké. Ieu goréng, utamana lamun jumlah sambungan leuwih ti hiji.
2) Tapi anu langkung parah nyaéta subqueries anu dihubungkeun, komo deui, sareng agrégasi. Ieu pisan goréng.
Ieu tangtu goréng. Tapi ieu ngan dina hiji sisi. Di sisi anu sanés, ieu saé pisan, sabab masalahna jelas gaduh solusi sareng pamenta anu tiasa ningkat.
Entong ka tukang nujum (C).
Rencana query henteu pajeulit, tapi cukup nunjukkeun:
Rencana Palaksanaan
Anu paling pikaresepeun sareng mangpaat, sapertos biasa, aya dina awal sareng akhir.
Loop Nested (biaya = 935.84..479763226.18 jajar = 3322 rubak = 135) (waktos sabenerna = 31.536..8220420.295 jajar = 8111656 puteran = 1)
waktos perencanaan: 3.807 mdet
waktos palaksanaan: 8222351.640 mdet
Waktu parantosan langkung ti 2 jam.
Hipotesis palsu anu nyandak waktos
Hipotesis 1 - Optimasi ngalakukeun kasalahan sareng ngawangun rencana anu salah.
Pikeun visualize rencana palaksanaan, urang bakal ngagunakeun loka . Sanajan kitu, loka éta teu némbongkeun nanaon metot atawa mangpaat. Dina glance kahiji sareng kadua, teu aya anu tiasa ngabantosan. Naha mungkin yén Full Scan minimal. Lajengkeun.
Hipotesis 2-Dampak dina basa ti sisi autovacuum, Anjeun kudu meunang leupas tina rem.
Tapi daemon autovacuum kalakuanana saé, henteu aya prosés anu panjang. Taya beban serius. Urang kedah milarian anu sanés.
Hipotesis 3 - Statistik geus tinggaleun jaman, sagalana perlu recalculated
Sakali deui, sanés éta. Statistik anu up to date. Anu, upami kurangna masalah sareng autovacuum, henteu heran.
Hayu urang mimitian ngaoptimalkeun
Méja utama 'wdata' pasti henteu leutik, ampir 3 juta rékaman.
Tur éta tabel ieu nu Full Scan kieu.
Hash Cond: ((w."SPENT_ID" = s."SPENT_ID") JEUNG ((SubPlan 1) = s."SPENT_DATE"))
-> Seq Scan dina wdata w (biaya = 0.00..574151.49 jajar = 26886249 rubak = 46) (waktos sabenerna = 0.005..8153.565 baris = 26873950 puteran = 1)
Kami ngalakukeun hal standar: "hayu, hayu urang ngadamel indéks sareng sadayana bakal ngapung."
Nyiptakeun indéks dina widang "SPENT_ID".
Salaku hasilna:
Rencana palaksanaan pamundut nganggo indéks
Muhun, éta mantuan?
Éta: 8 222 351.640 ms (leuwih ti 2 jam)
janten: 6 985 431.575 mdet (ampir 2 jam)
Sacara umum, apel sarua, view samping.
Hayu urang émut klasik:
"Naha anjeun gaduh anu sami, tapi tanpa jangjang? Bakal neangan".
Sacara prinsip, ieu bisa disebut hasil alus, ogé, teu alus, tapi bisa ditarima. Sahenteuna, nyadiakeun laporan badag ka nasabah ngajéntrékeun sabaraha geus dipigawé sarta naha naon anu dipigawé éta alus.
Tapi tetep, kaputusan ahir masih jauh. Jauh pisan.
Sareng ayeuna anu paling pikaresepeun - urang teraskeun ngaoptimalkeun, kami bakal ngagosok pamundut
Lengkah Hiji - Paké JOIN
Paménta anu ditulis deui ayeuna sapertos kieu (ogé sahenteuna leuwih geulis):
Patarosan ngagunakeun JOINMILIH
p. "PARAMETER_ID" salaku parameter_id,
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 lower_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,
ekstrak(taun ti "SPENT_DATE") AS taun,
ekstrak (bulan ti "SPENT_DATE") salaku bulan,
s."REPORT_NAME" AS report_name,
p."STPM_NAME" AS stpm_name,
p. "CUSTOMERPARAM_NAME" AS customerparam_name
TI wdata w INNER JOIN spent s ON w.“SPENT_ID”=s.”“SPENT_ID”
GABUNGAN BATIN pmtr p ON p.“ID_PARAMETER” = w.“ID_PARAMETER”
INNER JOIN spent_pd sp ON s.“SPENT_ID” = sp.“SPENT_ID”
GABUNGAN BATIN pd pd ON pd.“PD_ID” = sp.“PD_ID”
WHERE
s.“SPENT_DATE” >= '2018-07-01' JEUNG s. "SPENT_DATE" <= '2018-09-30'Jeung
s.“SPENT_DATE” = (PILIH MAX(s2.“SPENT_DATE”)
TI wdata w2 INNER JOIN spent s2 ON w2.“SPENT_ID”=s2.“SPENT_ID”
GABUNGAN BATIN wdata w
ON w2. "LRM" = w. "LRM" );
waktos perencanaan: 2.486 mdet
waktos palaksanaan: 1223680.326 mdet
Ku kituna, hasil munggaran.
Éta: 6 mdet (ampir 985 jam).
janten: 1 223 680.326 mdet (leuwih 20 menit).
hasilna alus. Sacara prinsip, deui, urang bisa eureun di dinya. Tapi éta henteu pikaresepeun, anjeun moal tiasa ngeureunkeun.
Pikeun
Lengkah dua - nyingkirkeun subquery anu aya hubunganana
Teks pamundut anu dirobih:
Tanpa subquery correlatedMILIH
p. "PARAMETER_ID" salaku parameter_id,
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 lower_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,
ekstrak(taun ti "SPENT_DATE") AS taun,
ekstrak (bulan ti "SPENT_DATE") salaku bulan,
s."REPORT_NAME" AS report_name,
p."STPM_NAME" AS stpm_name,
p. "CUSTOMERPARAM_NAME" AS customerparam_name
TI wdata w INNER JOIN spent s ON s.“SPENT_ID” = w.“SPENT_ID”
GABUNGAN BATIN pmtr p ON p.“ID_PARAMETER” = w.“ID_PARAMETER”
INNER JOIN spent_pd sp ON s.“SPENT_ID” = sp.“SPENT_ID”
GABUNGAN BATIN pd pd ON pd.“PD_ID” = sp.“PD_ID”
GABUNGAN BATIN (PILIH w2. "LRM", MAX(s2. "SPENT_DATE")
TI spent s2 INNER JOIN wdata w2 ON s2.“SPENT_ID” = w2. “SPENT_ID”
GROUP KU w2.“LRM”
) md dina w. "LRM" = md. "LRM"
WHERE
s."SPENT_DATE" > = '2018-07-01' JEUNG s."SPENT_DATE" <= '2018-09-30';
waktos perencanaan: 2.291 mdet
waktos palaksanaan: 165021.870 mdet
Éta: 1 223 680.326 mdet (leuwih 20 menit).
janten: 165 021.870 mdet (leuwih 2 menit).
Ieu geus rada alus.
Sanajan kitu, sakumaha Inggris nyebutkeun "Tapi, sok aya tapi" Hasil anu saé teuing kedah otomatis ngahudangkeun kacurigaan. Aya anu henteu leres di dieu.
Hipotesis ngeunaan koréksi query pikeun ngaleungitkeun subquery correlated bener. Tapi anjeun kudu tweak saeutik pikeun hasil ahir jadi bener.
Hasilna, hasil panengah kahiji:
Paménta anu diédit tanpa subquery anu aya hubungananaMILIH
p. "PARAMETER_ID" salaku parameter_id,
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 lower_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,
ekstrak(taun ti s.“SPENT_DATE”) AS taun,
ekstrak(bulan ti s.“SPENT_DATE”) salaku bulan,
s."REPORT_NAME" AS report_name,
p."STPM_NAME" AS stpm_name,
p. "CUSTOMERPARAM_NAME" AS customerparam_name
TI wdata w INNER JOIN spent s ON s.“SPENT_ID” = w.“SPENT_ID”
GABUNGAN BATIN pmtr p ON p.“ID_PARAMETER” = w.“ID_PARAMETER”
INNER JOIN spent_pd sp ON s.“SPENT_ID” = sp.“SPENT_ID”
GABUNGAN BATIN pd pd ON pd.“PD_ID” = sp.“PD_ID”
GABUNGAN BATIN ( PILIH w2. "LRM", MAX(s2. "SPENT_DATE") AS "SPENT_DATE"
TI spent s2 INNER JOIN wdata w2 ON s2.“SPENT_ID” = w2. “SPENT_ID”
GROUP KU w2.“LRM”
) md ON md.“SPENT_DATE” = s. “SPENT_DATE” JEUNG md. “LRM” = w. “LRM”
WHERE
s."SPENT_DATE" > = '2018-07-01' JEUNG s."SPENT_DATE" <= '2018-09-30';
waktos perencanaan: 3.192 mdet
waktos palaksanaan: 208014.134 mdet
Janten, anu ditungtungan ku urang mangrupikeun hasil anu tiasa ditampi, anu henteu éra pikeun nunjukkeun ka nasabah:
Dimimitian ku: 8 222 351.640 mdet (leuwih ti 2 jam)
Urang junun ngahontal: 1 mdet (leuwih saeutik ti 223 menit).
Hasil (interim): 208 014.134 mdet (leuwih 3 menit).
hasilna alus teuing.
hasil
Urang bisa geus dieureunkeun di dinya.
TAPI…
Napsu datang jeung dahar. Anu leumpang bakal ngawasa jalan. Sakur hasilna panengah. Dieureunkeun sarta maot. Jsb.
Hayu urang neruskeun optimasi.
gagasan hébat. Utamana tempo yén konsumén malah teu kapikiran. Komo niatna keur eta.
Janten, waktosna pikeun ngadesain ulang database. Struktur query sorangan teu bisa deui dioptimalkeun (sanajan, sakumaha tétéla engké, aya hiji pilihan pikeun mastikeun yén sagalana sabenerna gagal). Tapi pikeun ngamimitian ngaoptimalkeun sareng ngembangkeun desain database mangrupikeun ide anu ngajangjikeun. Jeung paling importantly metot. Deui, inget nonoman anjeun. Kuring teu langsung jadi DBA a, Kuring tumuwuh nepi salaku programmer a (DASAR, assembler, C, ganda-tambah C, Oracle, plsql). Topik anu pikaresepeun, tangtosna, pikeun memoar anu misah ;-).
Sanajan kitu, hayu urang teu meunang kacau.
Ku kituna,
Atawa meureun partisi bakal nulungan urang?
Spoiler - "Leres, éta ngabantosan, kalebet dina ngaoptimalkeun kinerja."
Tapi éta carita lengkep béda ...
Ngalajengkeun…
sumber: www.habr.com