Ọkara afọ gara aga - ọha na PostgreSQL.
N'ime ọnwa gara aga, anyị emeela banyere ya , kwadebere nchịkọta dabere na ndụmọdụ ndị ọ na-enye ... mana nke kachasị mkpa, anyị chịkọtara nzaghachi gị wee lelee ikpe eji eme ihe n'ezie.
Ma ugbu a, anyị dịla njikere ịgwa gị maka atụmatụ ọhụrụ ị nwere ike iji.
Nkwado maka ụdị atụmatụ dị iche iche
Mee atụmatụ site na ndekọ, yana arịrịọ ahụ
Kpọmkwem site na njikwa, anyị na-ahọrọ ngọngọ dum, malite na akara na Ederede ajụjụ, yana oghere niile na-eduga:
Query Text: INSERT INTO dicquery_20200604 VALUES ($1.*) ON CONFLICT (query)
DO NOTHING;
Insert on dicquery_20200604 (cost=0.00..0.05 rows=1 width=52) (actual time=40.376..40.376 rows=0 loops=1)
Conflict Resolution: NOTHING
Conflict Arbiter Indexes: dicquery_20200604_pkey
Tuples Inserted: 1
Conflicting Tuples: 0
Buffers: shared hit=9 read=1 dirtied=1
-> Result (cost=0.00..0.05 rows=1 width=52) (actual time=0.001..0.001 rows=1 loops=1)
... ma tụba ihe niile e depụtaghachiri ozugbo n'ọhịa maka atụmatụ ahụ, na-enweghị ikewapụ ihe ọ bụla:
Na mmepụta, anyị na-enwetakwa ego na atụmatụ agbasasị okirikiri nhọrọ ukwuu, ebe a na-egosi arịrịọ anyị n'ebube ya niile:
JSON na YAML
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT * FROM pg_class;"[
{
"Plan": {
"Node Type": "Seq Scan",
"Parallel Aware": false,
"Relation Name": "pg_class",
"Alias": "pg_class",
"Startup Cost": 0.00,
"Total Cost": 1336.20,
"Plan Rows": 13804,
"Plan Width": 539,
"Actual Startup Time": 0.006,
"Actual Total Time": 1.838,
"Actual Rows": 10266,
"Actual Loops": 1,
"Shared Hit Blocks": 646,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0
},
"Planning Time": 5.135,
"Triggers": [
],
"Execution Time": 2.389
}
]"Ọbụlagodi na nkwuputa mpụga, dị ka mbipụta pgAdmin, ọbụlagodi na-enweghị - anyị na-atụba n'otu ubi, mmepụta bụ ịma mma:
Ọhụụ dị elu
Oge nhazi / Oge mmezu
Ugbu a ị nwere ike ịhụ ebe mgbakwunye oge gara mgbe ị na-eme ajụjụ a:
Oge I/O
Mgbe ụfọdụ, ị ga-emerịrị ọnọdụ ebe, n'ihe gbasara akụrụngwa, ọ dị ka ọ bụghị nke ukwuu ka a na-agụ ma dee ya, mana ọ dị ka oge igbu egbu na-enweghị oke maka ihe ụfọdụ.
Ekwesịrị ikwu ebe a:Oh, n'oge ahụ, diski dị na sava ahụ nwere ike na-arụsi ọrụ ike, ya mere o ji were ogologo oge ịgụ ihe!"Ma n'ụzọ ụfọdụ, ọ bụghị nnọọ ezi ...
Mana enwere ike ikpebi ya kpamkpam. Nke bụ eziokwu bụ na n'etiti nhọrọ nhazi nke ihe nkesa PG nwere :
Na-akwado ọrụ I/O nwere oge. Akwụsịghị ntọala a site na ndabara, n'ihi na ọ na-achọ ka sistemụ arụmọrụ na-ajụ ajụjụ oge ọ bụla ugbu a, nke nwere ike ibelata ihe dị ukwuu na nyiwe ụfọdụ. Ị nwere ike iji pg_test_timing utility iji chọpụta oke oge nke n'elu ikpo okwu gị. Enwere ike nweta ọnụ ọgụgụ I/O site na nlele pg_stat_database, na mmepụta EXPLAIN (mgbe ejiri paramita BUFFERS) na site na nlele pg_stat_statements.
Enwere ike ịme nhọrọ a n'ime nnọkọ mpaghara:
SET track_io_timing = TRUE;Ọfọn, ugbu a akụkụ kachasị mma bụ na anyị amụtala ịghọta na igosipụta data a, na-eburu n'uche mgbanwe niile nke osisi igbu egbu:
N'ebe a, ị ga-ahụ na n'ime 0.790ms nke ngụkọta oge igbu oge, 0.718ms gụrụ otu ibe data, 0.044ms - na-ede ya, na naanị 0.028ms ejiri mee ihe na ọrụ ndị ọzọ bara uru!
Ọdịnihu na PostgreSQL 13
Maka nkọwa zuru ezu nke ihe dị ọhụrụ, hụ , na anyị na-ekwu kpọmkwem banyere mgbanwe na atụmatụ.
Nhazi ihe nchekwa
A na-egosipụta ndekọ ego maka akụrụngwa ekenyela onye nhazi oge na patch ọzọ na-emetụtaghị pg_stat_statements. Kọwaa site na nhọrọ BUFFERS ga-akọpụta ọnụọgụ nchekwa ejiri n'oge nhazi atụmatụ:
Seq Scan on pg_class (actual rows=386 loops=1) Buffers: shared hit=9 read=4 Planning Time: 0.782 ms Buffers: shared hit=103 read=11 Execution Time: 0.219 ms
Ụdị mmụba
N'ọnọdụ ebe a na-achọ nhazi site na ọtụtụ igodo (k1, k2, k3 ...), onye na-eme atụmatụ nwere ike nweta uru ugbu a n'ịmara na ahazilarị data ahụ site na ọtụtụ igodo mbụ (dịka k1 na k2). N'okwu a, ị nweghị ike ịhazigharị data niile ọzọ, mana kewaa ha n'otu n'otu na otu ụkpụrụ nke k1 na k2, wee “dogharịa” ha site na igodo k3.
Ya mere, nhazi ahụ dum na-ekewa n'ime ụdị dị iche iche na-esote nke obere nha. Nke a na-ebelata ọnụọgụ ebe nchekwa achọrọ, ma na-enye gị ohere iweghachite data mbụ tupu ụdị ahụ emecha.
Incremental Sort (actual rows=2949857 loops=1) Sort Key: ticket_no, passenger_id Presorted Key: ticket_no Full-sort Groups: 92184 Sort Method: quicksort Memory: avg=31kB peak=31kB -> Index Scan using tickets_pkey on tickets (actual rows=2949857 loops=1) Planning Time: 2.137 ms Execution Time: 2230.019 ms
Mmelite UI/UX
Nseta ihuenyo dị ebe niile!
Ugbu a na taabụ ọ bụla enwere ohere ngwa ngwa Were nseta ihuenyo nke taabụ gaa na klipbọọdụ maka dum obosara na omimi nke taabụ - "anya" aka nri-elu:
N'ezie, e nwetara ọtụtụ foto maka akwụkwọ a n'ụzọ dị otú a.
Nkwanye na ọnụ ọnụ
Enwere ọ bụghị naanị ọtụtụ n'ime ha, mana gbasara nke ọ bụla ị nwere ike site na isoro njikọ a:
Wepụ na ebe nchekwa
Ụfọdụ arịọla maka ikike hichapụ "kpamkpam" ọbụlagodi atụmatụ ndị na-ebipụtaghị na ebe nchekwa - biko, pịa akara ngosi kwekọrọ:
Ọfọn, ka anyị ghara ichefu na anyị nwere ebe ị nwere ike dee okwu gị na aro gị.
isi: www.habr.com