ProHoster > Π‘Π»ΠΎΠ³ > Kev tswj hwm > QHOV TSEEB yog dab tsi ntsiag to thiab yuav ua li cas kom tau nws tham

QHOV TSEEB yog dab tsi ntsiag to thiab yuav ua li cas kom tau nws tham

Cov lus nug classic uas tus tsim tawm coj mus rau nws DBA lossis tus tswv lag luam coj mus rau PostgreSQL tus kws pab tswv yim yuav luag ib txwm zoo ib yam: "Vim li cas thov siv sijhawm ntev los ua kom tiav ntawm cov ntaub ntawv?"

Cov txheej txheem ntawm cov laj thawj:

  • inefficient algorithm
    thaum koj txiav txim siab koom nrog ob peb CTEs dhau ob peb kaum tawm txhiab cov ntaub ntawv
  • cov txheeb cais tsis cuam tshuam
    yog tias qhov kev faib tawm tiag tiag ntawm cov ntaub ntawv hauv lub rooj twb txawv heev ntawm qhov sau los ntawm ANALYZE lub sijhawm dhau los
  • "plug" ntawm cov ntaub ntawv
    thiab tsis muaj kev mob siab rau lub zog ntawm CPU ntxiv lawm, gigabytes ntawm lub cim xeeb tau txuas mus tas li, lossis lub disk tsis tuaj yeem khaws tag nrho cov "xav tau" ntawm cov ntaub ntawv.
  • thaiv los ntawm cov txheej txheem sib tw

Thiab yog tias blockings nyuaj heev los ntes thiab txheeb xyuas, tom qab ntawd rau txhua yam peb xav tau query plan, uas tuaj yeem siv tau EXPLAIN tus neeg ua haujlwm (Nws yog qhov zoo dua, tau kawg, kom piav qhia tam sim (Tshaj tawm, BUFFERS) ...) lossis auto_explain module.

Tab sis, raws li tau hais hauv tib cov ntaub ntawv,

"Kev nkag siab txog txoj kev npaj yog kev kos duab, thiab kom paub nws yuav tsum muaj qee qhov kev paub ..."

Tab sis koj tuaj yeem ua yam tsis muaj nws yog tias koj siv cov cuab yeej zoo!

Lub phiaj xwm nug feem ntau zoo li cas? Ib yam li ntawd:

Index Scan using pg_class_relname_nsp_index on pg_class (actual time=0.049..0.050 rows=1 loops=1)
  Index Cond: (relname = $1)
  Filter: (oid = $0)
  Buffers: shared hit=4
  InitPlan 1 (returns $0,$1)
    ->  Limit (actual time=0.019..0.020 rows=1 loops=1)
          Buffers: shared hit=1
          ->  Seq Scan on pg_class pg_class_1 (actual time=0.015..0.015 rows=1 loops=1)
                Filter: (relkind = 'r'::"char")
                Rows Removed by Filter: 5
                Buffers: shared hit=1

los yog zoo li no:

"Append  (cost=868.60..878.95 rows=2 width=233) (actual time=0.024..0.144 rows=2 loops=1)"
"  Buffers: shared hit=3"
"  CTE cl"
"    ->  Seq Scan on pg_class  (cost=0.00..868.60 rows=9972 width=537) (actual time=0.016..0.042 rows=101 loops=1)"
"          Buffers: shared hit=3"
"  ->  Limit  (cost=0.00..0.10 rows=1 width=233) (actual time=0.023..0.024 rows=1 loops=1)"
"        Buffers: shared hit=1"
"        ->  CTE Scan on cl  (cost=0.00..997.20 rows=9972 width=233) (actual time=0.021..0.021 rows=1 loops=1)"
"              Buffers: shared hit=1"
"  ->  Limit  (cost=10.00..10.10 rows=1 width=233) (actual time=0.117..0.118 rows=1 loops=1)"
"        Buffers: shared hit=2"
"        ->  CTE Scan on cl cl_1  (cost=0.00..997.20 rows=9972 width=233) (actual time=0.001..0.104 rows=101 loops=1)"
"              Buffers: shared hit=2"
"Planning Time: 0.634 ms"
"Execution Time: 0.248 ms"

Tab sis kev nyeem cov phiaj xwm hauv cov ntawv "los ntawm daim ntawv" yog qhov nyuaj heev thiab tsis meej:

  • yog tso tawm nyob rau hauv lub node sum los ntawm subtree resources
    uas yog, kom nkag siab ntau npaum li cas nws tau siv sijhawm los tua ib qho tshwj xeeb, lossis ntau npaum li cas qhov kev nyeem ntawv no los ntawm lub rooj nqa cov ntaub ntawv los ntawm disk, koj yuav tsum tau rho tawm ib qho los ntawm lwm qhov.
  • lub sijhawm node xav tau multiply los ntawm loops
    yog, kev rho tawm tsis yog txoj haujlwm nyuaj tshaj plaws uas yuav tsum tau ua "hauv lub taub hau" - tom qab tag nrho, lub sijhawm ua tiav tau qhia tias qhov nruab nrab rau ib qho kev tua ntawm ib lub ntawm, thiab tuaj yeem muaj ntau pua ntawm lawv.
  • zoo, thiab tag nrho cov no ua ke tiv thaiv peb los ntawm kev teb cov lus nug tseem ceeb - yog li leej twg "lub weakest link"?

Thaum peb sim piav qhia tag nrho cov no rau ntau pua ntawm peb cov neeg tsim khoom, peb pom tau tias los ntawm sab nraud nws zoo li qhov no:

QHOV TSEEB yog dab tsi ntsiag to thiab yuav ua li cas kom tau nws tham

Thiab qhov ntawd txhais tau tias peb xav tau ...

Tool

Nyob rau hauv nws peb sim sau tag nrho cov tseem ceeb mechanics uas pab to taub "leej twg yuav liam thiab yuav ua li cas" raws li txoj kev npaj thiab thov. Zoo, thiab qhia ib feem ntawm koj qhov kev paub nrog cov zej zog.
Sib ntsib thiab siv - piav.tensor.ru

Kev pom ntawm cov phiaj xwm

Nws puas yooj yim to taub txoj kev npaj thaum nws zoo li no?

Seq Scan on pg_class (actual time=0.009..1.304 rows=6609 loops=1)
  Buffers: shared hit=263
Planning Time: 0.108 ms
Execution Time: 1.800 ms

Tsis yog.

Tab sis zoo li no, nyob rau hauv daim ntawv abbreviatedThaum cov ntsuas tseem ceeb tau sib cais, nws pom tseeb dua:

QHOV TSEEB yog dab tsi ntsiag to thiab yuav ua li cas kom tau nws tham

Tab sis yog tias txoj kev npaj yuav nyuaj dua, nws yuav los cawm piechart lub sij hawm faib los ntawm nodes:

QHOV TSEEB yog dab tsi ntsiag to thiab yuav ua li cas kom tau nws tham

Zoo, rau cov kev xaiv nyuaj tshaj plaws nws yog maj nrawm los pab daim ntawv qhia kev vam meej:

QHOV TSEEB yog dab tsi ntsiag to thiab yuav ua li cas kom tau nws tham

Piv txwv li, muaj cov xwm txheej tsis tseem ceeb thaum lub phiaj xwm yuav muaj ntau tshaj ib lub hauv paus:

QHOV TSEEB yog dab tsi ntsiag to thiab yuav ua li cas kom tau nws thamQHOV TSEEB yog dab tsi ntsiag to thiab yuav ua li cas kom tau nws tham

Cov ntsiab lus tsim nyog

Zoo, yog tias tag nrho cov qauv ntawm txoj kev npaj thiab nws qhov mob tau raug tso tawm thiab pom, vim li cas ho tsis qhia rau tus tsim tawm thiab piav qhia lawv ua "Lavxias teb sab"?

QHOV TSEEB yog dab tsi ntsiag to thiab yuav ua li cas kom tau nws thamPeb twb tau sau ob peb lub kaum os cov qauv kev pom zoo.

Line-by-line query profiler

Tam sim no, yog tias koj superimpose thawj cov lus nug mus rau qhov kev tshuaj ntsuam xyuas, koj tuaj yeem pom tias siv sijhawm npaum li cas rau txhua tus neeg nqe lus - qee yam zoo li no:

QHOV TSEEB yog dab tsi ntsiag to thiab yuav ua li cas kom tau nws tham

...los yog zoo li no:

QHOV TSEEB yog dab tsi ntsiag to thiab yuav ua li cas kom tau nws tham

Hloov cov parameter rau hauv kev thov

Yog tias koj "txuas" tsis yog tsuas yog thov rau txoj kev npaj, tab sis kuj nws cov kev txwv los ntawm DETAIL kab ntawm lub cav, koj tuaj yeem luam tawm hauv ib qho ntawm cov kev xaiv:

  • nrog tus nqi hloov pauv hauv qhov kev thov
    rau kev ua ncaj qha rau ntawm koj lub hauv paus thiab ntxiv profileing

    SELECT 'const', 'param'::text;
  • nrog tus nqi hloov pauv ntawm PREPARE/EXECUTE
    kom ua raws li kev ua haujlwm ntawm tus teem sijhawm, thaum qhov parametric tuaj yeem tsis quav ntsej - piv txwv li, thaum ua haujlwm ntawm cov rooj sib faib

    DEALLOCATE ALL;
PREPARE q(text) AS SELECT 'const', $1::text;
EXECUTE q('param'::text);

Archive ntawm kev npaj

Muab tshuaj txhuam, tshuaj xyuas, qhia rau cov npoj yaig! Cov phiaj xwm yuav nyob twj ywm thiab koj tuaj yeem rov qab mus rau lawv tom qab: piav qhia.tensor.ru/archive

Tab sis yog tias koj tsis xav kom lwm tus pom koj txoj kev npaj, tsis txhob hnov ​​​​qab kos lub "tsis txhob tshaj tawm hauv archive" lub thawv.

Hauv kab lus hauv qab no kuv yuav tham txog cov teeb meem thiab kev txiav txim siab uas tshwm sim thaum txheeb xyuas ib txoj kev npaj.

Tau qhov twg los: www.hab.com

Ntxiv ib saib