Rabin shekara da ta wuce - jama'a zuwa PostgreSQL.
A cikin watannin da suka gabata mun yi game da shi , shirya taƙaitaccen bayani bisa ga shawarwarin da yake bayarwa ... amma mafi mahimmanci, mun tattara ra'ayoyin ku kuma mun kalli shari'o'in amfani na gaske.
Kuma yanzu mun shirya don magana game da sababbin damar da za ku iya amfani da su.
Taimako don nau'ikan tsari daban-daban
Tsara daga log ɗin, tare da buƙatar
Kai tsaye daga na'ura wasan bidiyo, zaɓi duk toshe, farawa daga layi tare da Rubutun tambaya, tare da duk manyan wurare:
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)
... kuma sanya duk abin da aka kwafi kai tsaye cikin filin shirin, ba tare da raba komai ba:
A ƙarshe muna samun kari ga shirin da aka lalata kuma "context" tab, inda aka gabatar da bukatar mu cikin dukkan daukakarta:
JSON dan 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
}
]"Ko dai tare da ƙididdiga na waje, azaman kwafin pgAdmin, ko ba tare da - mun jefa shi cikin filin guda ba, kuma fitarwa shine kyakkyawa:
Babban Kayayyakin gani
Lokacin Tsara/Lokacin Kisa
Yanzu za ku iya ganin inda aka kashe ƙarin lokacin don aiwatar da tambayar:
Lokacin I/O
Wani lokaci dole ne ku fuskanci yanayin da, dangane da albarkatun, da alama ba a karanta da rubutu da yawa ba, amma lokacin aiwatarwa yana da alama yana da tsayi.
A nan sai mu ce: "Oh, mai yiwuwa a lokacin faifan da ke kan uwar garken ya yi yawa fiye da kima, shi ya sa ya ɗauki lokaci mai tsawo ana karantawa!"Amma ko ta yaya wannan ba daidai ba ne ...
Amma ana iya ƙaddara wannan gabaɗaya da dogaro. Gaskiyar ita ce daga cikin zaɓuɓɓukan daidaitawar uwar garken PG akwai :
Yana ba da damar lokacin ayyukan I/O. An kashe wannan zaɓi ta tsohuwa saboda yana buƙatar koyaushe tambayar tsarin aiki na yanzu, wanda zai iya rage aiki sosai akan wasu dandamali. Don ƙididdige farashin lokaci akan dandalin ku, kuna iya amfani da kayan aikin pg_test_timing. Ana iya samun kididdigar I/O ta hanyar kallon pg_stat_database, a cikin fitowar EXPLAIN (lokacin da ake amfani da sigar BUFFERS) kuma ta hanyar pg_stat_statements view.
Hakanan za'a iya kunna wannan zaɓi a cikin zaman gida:
SET track_io_timing = TRUE;To, yanzu mafi kyawun sashi shine mun koyi fahimta da kuma nuna wannan bayanan la'akari da duk canje-canjen bishiyar kisa:
Anan za ku iya ganin cewa daga cikin 0.790ms na jimlar lokacin kisa, 0.718ms sun karanta shafi ɗaya na bayanai, 0.044ms sun rubuta, kuma 0.028ms kawai aka kashe akan duk sauran ayyuka masu amfani!
Gaba tare da PostgreSQL 13
Kuna iya samun cikakken bayyani na sabbin abubuwa , kuma muna magana musamman game da canje-canje a cikin tsare-tsaren.
Shirye-shiryen buffers
Ana nuna lissafin albarkatun da aka keɓe ga mai tsarawa a cikin wani facin da bashi da alaƙa da pg_stat_statements. BAYYANA tare da zaɓin BUFFERS zai ba da rahoton adadin abubuwan da aka yi amfani da su yayin lokacin tsarawa:
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
Rarrabuwa
A cikin lokuta inda ake buƙatar rarraba maɓallai da yawa (k1, k2, k3 ...), mai tsarawa zai iya amfani da ilimin cewa an riga an jera bayanan akan yawancin maɓallan farko (misali, k1 da k2). A wannan yanayin, ba za ku iya sake tsara duk bayanan ba, amma raba shi zuwa ƙungiyoyi masu zuwa tare da ma'auni iri ɗaya na k1 da k2, kuma "sake-sake" ta maɓallin k3.
Don haka, an raba rarrabuwar gabaɗaya zuwa nau'ikan ƙananan girma dabam dabam. Wannan yana rage adadin ƙwaƙwalwar ajiya da ake buƙata kuma yana ba da damar fitar da bayanan farko kafin a gama rarrabuwa gabaɗaya.
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
UI/UX ingantawa
Screenshot, suna ko'ina!
Yanzu akan kowane shafin akwai damar da sauri Ɗauki hoton allo na shafin zuwa allon allo Duk faɗin da zurfin shafin - "gani" a kan dama-saman:
A gaskiya ma, yawancin hotuna na wannan ɗaba'ar an samo su ta wannan hanya.
Shawarwari akan nodes
Ba wai kawai an sami ƙarin su ba, amma kuna iya magana game da kowannensu ta hanyar bin hanyar:
Share daga rumbun adana bayanai
Wasu mutane sun nemi da gaske su ƙara zaɓin share "gaba daya" har ma da tsare-tsaren da ba a buga su a cikin ma'ajiyar bayanai - don Allah kawai danna gunkin da ya dace:
To, kar ku manta cewa muna da , inda zaku iya rubuta sharhi da shawarwarinku.
source: www.habr.com