🥇Chii chatsanangurirwa chaakanyarara uye kuti oita sei kuti ataure

Mubvunzo wekare uyo mugadziri anounza kuDBA yake kana muridzi webhizinesi anounza kune PostgreSQL chipangamazano anenge anogara achinzwika zvakafanana: "Nei zvikumbiro zvichitora nguva yakareba kuti zvizadziswe pane dhatabhesi?"

Traditional seti yezvikonzero:

inefficient algorithm
paunofunga KUJOYINA akati wandei maCTE pamusoro pemakumi emakumi ezviuru emarekodhi
nhamba dzechinyakare
kana iyo chaiyo yekugovera data mutafura yatove yakasiyana zvakanyanya kubva kune yakaunganidzwa neANALYZE kekupedzisira
"plug" pane zviwanikwa
uye hapasisina kukwana yakakwana yakatsaurirwa simba reCPU, gigabytes yendangariro inogara ichipombwa, kana dhisiki haigone kuenderana ne "zvido" zvese zve database.
ichivharira kubva kumakwikwi maitiro

Uye kana zvivharo zvakaoma kubata uye kuongorora, saka kune zvimwe zvese zvatinoda query plan, iyo inogona kuwanikwa uchishandisa TSANANGURA mushandisi (Zviri nani, hongu, kuti utsanangure nekukurumidza (KUONGORORA, BUFFERS) ...) kana auto_explain module.

Asi, sezvakataurwa mugwaro rimwe chete,

"Kunzwisisa chirongwa hunyanzvi, uye kuti ugone kuchigona kunoda humwe ruzivo..."

Asi iwe unogona kuita pasina iyo kana iwe ukashandisa chaiyo chishandiso!

Ko chirongwa chemubvunzo chinowanzoita sei? Chinhu chakadaro:

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

kana seizvi:

"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"

Asi kuverenga chirongwa muzvinyorwa "kubva papepa" kwakaoma uye hakuna kujeka:

inoratidzwa mu node sum by subtree resources
ndiko kuti, kuti unzwisise kuti inguva yakadii yakatora kuita imwe node, kana kuti yakawanda sei kuverenga uku kubva patafura kwakaunza data kubva kudhisiki, iwe unofanirwa neimwe nzira kubvisa imwe kubva kune imwe.
node nguva inodiwa wedzerai nezvishwe
hongu, kubvisa haisi iyo yakanyanya kuomesesa oparesheni inoda kuitwa "mumusoro" - mushure mezvose, nguva yekuuraya inoratidzwa seavhareji yekuitwa kumwe chete kwenode, uye panogona kunge paine mazana avo.
zvakanaka, uye izvi zvose pamwe chete zvinotitadzisa kupindura mubvunzo mukuru - saka ndiani "the weakest link"?

Patakaedza kutsanangura zvese izvi kumazana akati wandei evagadziri vedu, takaona kuti kubva kunze zvaitaridzika seizvi:

Uye zvinoreva kuti tinoda ...

Tool

Mariri takaedza kuunganidza ese akakosha mechanics anobatsira kunzwisisa "ndiani ane mhosva uye chii chaanofanira kuita" maererano nechirongwa uye chikumbiro. Zvakanaka, uye govana chikamu chechiitiko chako nenharaunda.
Sangana uye shandisa - explain.tensor.ru

Kuonekwa kwezvirongwa

Zviri nyore here kunzwisisa hurongwa kana huchiita seizvi?

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

Kwete chaizvo.

Asi seizvi, muchidimbukana zviratidzo zvakakosha zvakapatsanurwa, zvinonyanya kujeka:

Asi kana chirongwa chacho chakanyanya kuoma, achauya kuzonunura piechart nguva yekugovera ne nodes:

Zvakanaka, kune izvo zvakanyanya kuoma sarudzo ari kukurumidza kubatsira kufambira mberi chati:

Semuyenzaniso, pane mamiriro asiri madiki-diki apo chirongwa chinogona kunge chine midzi inopfuura imwechete chaiyo:

Structural zviratidzo

Zvakanaka, kana chimiro chese chechirongwa uye maronda ayo atove akaiswa uye anoonekwa, wadii kuzvisimbisa kune mugadziri uye uzvitsanangure mu "mutauro weRussia"?

Isu takatounganidza akati wandei gumi nemaviri ekurudziro matemplate.

Mutsara-ne-mutsara mubvunzo weprofiler

Zvino, kana iwe ukaisa mubvunzo wekutanga pachirongwa chakaongororwa, unogona kuona kuti inguva yakadii yakashandiswa pane chimwe nechimwe chirevo - chimwe chinhu chakadai:

... kana kunyange seizvi:

Kutsiva parameter muchikumbiro

Kana iwe "wakanamatira" kwete chete chikumbiro kuchirongwa, asiwo maparamendi ayo kubva kuDETAIL mutsara werogi, unogona kuwedzera kuikopa mune imwe yesarudzo:

pamwe nekutsiviwa ukoshi muchikumbiro
kuti uite zvakananga pachigadziko chako uye nekuwedzera profiling
```
SELECT 'const', 'param'::text;
```
nekutsiva kukosha kuburikidza nePREPARE/EXECUTE
kutevedzera basa remugadziri, apo parametric chikamu chinogona kufuratirwa - semuenzaniso, kana uchishanda pamatafura akapatsanurwa.
```
DEALLOCATE ALL;
PREPARE q(text) AS SELECT 'const', $1::text;
EXECUTE q('param'::text);
```

Archive yezvirongwa

Namira, ongorora, govana nevamwe vaunoshanda navo! Zvirongwa zvicharamba zvakachengetwa uye unogona kudzokera kwavari gare gare: explain.tensor.ru/archive

Asi kana iwe usingade kuti vamwe vaone hurongwa hwako, usakanganwa kutarisa bhokisi rekuti "usaburitse mudura".

Muzvinyorwa zvinotevera ndichataura pamusoro pezvinetso uye zvisarudzo zvinomuka pakuongorora chirongwa.

Source: www.habr.com

Chii tsananguro chisingatauri nezvacho uye kuti woita sei kutaura