Nheyo dzePostgreSQL yekutarisa. Alexey Lesovsky

Ini ndinokupa zano kuti uverenge chinyorwa chemushumo naAlexey Lesovsky kubva kuData Egret "Zvakakosha zvePostgreSQL yekutarisa"

Mumushumo uyu, Alexey Lesovsky achataura pamusoro pezvinhu zvakakosha zvemashure-gress statistics, zvavanoreva, uye nei vachifanira kuvapo mukutarisa; pamusoro pemagirafu anofanirwa kunge ari mukutariswa, mawedzererwo uye nekuapirikira. Chirevo ichi chichabatsira kune vatariri ve database, masisitimu maneja uye vanogadzira vanofarira Postgres kugadzirisa dambudziko.

Zita rangu ndiAlexey Lesovsky, ndinomiririra kambani yeData Egret.

Mashoko mashoma pamusoro pangu. Ini ndakatanga kare kare ndiri system administrator.

Ndakatungamirira marudzi ose emhando dzakasiyana-siyana dzeLinux, dzakashanda pazvinhu zvakasiyana-siyana zvine chokuita neLinux, i.e. virtualization, monitoring, yakashanda nemaproxies, etc. Asi pane imwe nguva ndakatanga kushanda zvakanyanya ne databases, PostgreSQL. Ndaimuda chaizvo. Uye pane imwe nguva ndakatanga kushanda paPostgreSQL yakawanda yenguva yangu yekushanda. Uye saka zvishoma nezvishoma ndakava PostgreSQL DBA.

Uye muhupenyu hwangu hwese, ndagara ndichifarira misoro yehuwandu, kutarisa, uye telemetry. Uye pandakanga ndiri maneja wehurongwa, ndakashanda zvakanyanya neZabbix. Uye ini ndakanyora diki seti yezvinyorwa senge zabbix-extensions. Akanga akakurumbira munguva yake. Uye ipapo zvaigoneka kutarisa zvakasiyana zvakanyanya zvakakosha, kwete Linux chete, asiwo zvakasiyana zvikamu.

Iye zvino ndiri kushanda paPostgreSQL. Ndiri kutonyora chimwe chinhu chinokutendera kuti ushande nePostgreSQL manhamba. Inonzi pgCenter (chinyorwa paHabré - Post-gress nhamba pasina tsinga uye tension).

Chinyorwa chidiki chenhanganyaya. Ndeapi mamiriro ane vatengi vedu, vatengi vedu? Pane imwe mhando yetsaona ine chekuita nedatabase. Uye kana dura remashoko rakatodzorerwa, mukuru wedhipatimendi kana kuti mukuru wezvebudiriro anouya oti: “Shamwari, tinofanira kuongorora dhatabhesi, nokuti chimwe chinhu chakaipa chakaitika uye tinofanira kudzivisa izvi kuti zvisazoitika mune ramangwana.” Uye pano inotanga iyo inonakidza maitiro ekusarudza yekutarisa sisitimu kana kugadzirisa iripo yekutarisa sisitimu kuti iwe ugone kutarisa yako dhatabhesi - PostgreSQL, MySQL kana vamwe vamwe. Uye vaunoshanda navo vanotanga kupa zano: "Ndakanzwa kuti kune dhatabhesi rakadai. Ngatishandisei." Shamwari dzinotanga kunetsana. Uye pakupedzisira zvinozoitika kuti isu tinosarudza imwe mhando yedatabase, asi PostgreSQL yekutarisa inounzwa mairi kwete zvisina kunaka uye isu tinogara tichifanira kuwedzera chimwe chinhu. Tora mamwe marepositori kubva kuGitHub, zvigadzirise, shandura zvinyorwa, uye neimwe nzira zvigadzirise. Uye pakupedzisira inopedzisira yava imwe mhando yebasa remaoko.

Naizvozvo, muhurukuro iyi ndichaedza kukupa ruzivo rwekusarudza kutarisa kwete yePostgreSQL chete, asiwo yedhatabhesi. Uye kukupa ruzivo rwunokutendera kuti upedze kutarisa kwako kuitira kuti uwane rubatsiro kubva pazviri, kuitira kuti iwe ugone kutarisa dhatabhesi yako nerubatsiro, kuitira kuti udzivise nekukurumidza chero mamiriro ekukurumidzira anouya angamuka.

Uye pfungwa dzichange dziri mumushumo uyu dzinogona kuchinjirwa zvakananga kune chero dhatabhesi, ingave iri DBMS kana noSQL. Naizvozvo, hapana chete PostgreSQL, asi pachave neakawanda mabikirwo ekuita izvi muPostgreSQL. Pachave nemienzaniso yemibvunzo, mienzaniso yemasangano ane PostgreSQL yekutarisisa. Uye kana DBMS yako iine zvinhu zvakafanana zvinokutendera kuti uzviise mukutarisa, unogona zvakare kuzvigadzirisa, wedzera uye zvichave zvakanaka.

Ini handisi mu report
taura nezve kuburitsa uye kuchengetedza metrics. Ini handisi kuzotaura chero chinhu nezve post-kugadzirisa iyo data uye kuiratidza kumushandisi. Uye ini handisi kuzotaura chero chinhu nezve kunyevera.
Asi sezvo nyaya yacho ichifambira mberi, ini ndicharatidza akasiyana skrini ekutarisa aripo uye neimwe nzira kuvashoropodza. Asi zvakadaro, ini ndichaedza kusatumidza mazita emhando kuitira kuti ndisagadzire kushambadzira kana anti-kushambadzira kwezvigadzirwa izvi. Naizvozvo, kusangana kwese kunongoitika uye kunosiiwa kune fungidziro yako.

Kutanga, ngationei kuti kuongorora chii. Monitoring chinhu chakakosha zvikuru kuva nacho. Munhu wose anonzwisisa izvi. Asi panguva imwecheteyo, kutarisa hakunei nechigadzirwa chebhizinesi uye hakukanganise purofiti yekambani zvakanangana, saka nguva inogara ichigoverwa kutarisa pane yakasara. Kana tiine nguva, tobva taita monitoring; kana tisina nguva, saka zvakanaka, tinozviisa mumashure uye rimwe zuva tichadzokera kumabasa aya.

Naizvozvo, kubva pakuita kwedu, kana tichiuya kune vatengi, kutarisa kunowanzo kuve kusina kukwana uye hakuna zvinhu zvinonakidza zvingatibatsira kuita basa riri nani nedhatabhesi. Uye saka kuongorora nguva dzose kunoda kupedzwa.

Databases zvinhu zvakaoma kudaro zvinodawo kutariswa, nekuti dhatabhesi inzvimbo yeruzivo. Uye ruzivo rwakanyanya kukosha kune kambani; haigone kurasikirwa neimwe nzira. Asi panguva imwecheteyo, dhatabhesi zvidimbu zvakaoma zvesoftware. Izvo zvinosanganisira nhamba huru yezvikamu. Uye zvakawanda zvezvikamu izvi zvinoda kuongororwa.

Kana isu tichitaura zvakananga nezvePostgreSQL, saka inogona kumiririrwa muchimiro chechirongwa chinosanganisira nhamba huru yezvikamu. Izvi zvikamu zvinodyidzana. Uye panguva imwecheteyo, PostgreSQL ine inonzi Stats Collector subsystem, iyo inokubvumira kuunganidza nhamba pamusoro pekushanda kweaya ma subsystems uye kupa imwe mhando yehutano kumutungamiri kana mushandisi kuitira kuti aone nhamba idzi.

Huwandu uhu hunounzwa muchimiro cheimwe seti yemabasa uye maonero. Vanogonawo kunzi matafura. Ndiko kuti, uchishandisa psql mutengi wenguva dzose, unogona kubatana kune dhatabhesi, sarudza pane aya mabasa uye maonero, uye uwane dzimwe nhamba dzakananga nezvekushanda kwePostgreSQL subsystems.

Iwe unogona kuwedzera idzi nhamba kune yako yaunofarira yekutarisa sisitimu, kudhirowa magirafu, kuwedzera mabasa uye kuwana analytics munguva refu.

Asi mumushumo uyu handisi kuzovhara mabasa ese aya zvizere, nekuti zvinogona kutora zuva rese. Ini ndichanyatsogadzirisa zvinhu zviviri, zvitatu kana zvina uye ndikuudze kuti zvinobatsira sei kuita kuti kuongorora kuve nani.

Uye kana tikataura nezve database yekutarisa, saka chii chinoda kutariswa? Chekutanga pane zvese, isu tinofanirwa kutarisa kuwanikwa, nekuti dhatabhesi ibasa rinopa mukana kune data kune vatengi uye isu tinofanirwa kutarisa kuwanikwa, uye nekupawo humwe hunhu hwayo uye huwandu hwehunhu.

Isu tinofanirwawo kutarisa vatengi vanobatana nedatabase yedu, nekuti vanogona kunge vari vese vatengi vakajairwa uye vanokuvadza vatengi vanogona kukuvadza dhatabhesi. Vanoda zvakare kutariswa uye mabasa avo achiteverwa.

Kana vatengi vachibatana nedhatabhesi, zviri pachena kuti vanotanga kushanda nedata redu, saka isu tinofanirwa kutarisa kuti vatengi vanoshanda sei nedata: nematafura api, uye zvishoma, nemaindex. Ndiko kuti, isu tinofanirwa kuongorora mutoro webasa unogadzirwa nevatengi vedu.

Asi kuwanda kwebasa kunosanganisirawo, hongu, zvezvikumbiro. Zvishandiso zvinobatana nedhatabhesi, kuwana data uchishandisa mibvunzo, saka zvakakosha kuti tiongorore kuti ndeapi mibvunzo yatinayo mudhatabhesi, tarisa kukwana kwayo, kuti haina kunyorwa zvisirizvo, kuti dzimwe sarudzo dzinoda kunyorwazve uye kuitwa kuti dzishande nekukurumidza. uye nekuita kurinani.

Uye sezvo isu tiri kutaura nezve database, iyo dhatabhesi inogara iri kumashure maitiro. Maitiro ekumashure anobatsira kuchengetedza dhatabhesi kuita padanho rakanaka, saka ivo vanoda huwandu hwezviwanikwa kuti vashande. Uye panguva imwecheteyo, ivo vanogona kupindirana nezviwanikwa zvekukumbira kwevatengi, saka maitiro emakaro ekumashure anogona kukanganisa zvakananga kuita kwezvikumbiro zvevatengi. Naizvozvo, ivo vanofanirwawo kutariswa nekuteverwa kuti pasave nekukanganiswa maererano nemaitiro ekumashure.

Uye zvese izvi maererano nekutarisa dhatabhesi zvinoramba zviri musystem metric. Asi tichifunga kuti mazhinji ezvivakwa zvedu zviri kufamba zvichienda kumakore, masisitimu metrics yemunhu anotambira anogara achipera kumashure. Asi mumadhatabhesi achiri akakosha uye, hongu, zvinodikanwawo kutarisa masisitimu metrics.

Zvese zviri nani kana kushoma nemasystem metrics, ese emazuvano ekutarisa masisitimu anototsigira aya metrics, asi kazhinji, zvimwe zvikamu hazvisati zvakwana uye zvimwe zvinhu zvinoda kuwedzerwa. Iniwo ndichabata pavari, pachava nemasiraidhi akati wandei pamusoro pavo.

Pfungwa yekutanga yechirongwa ndeyekusvikika. Chii chinonzi kusvikika? Kuwanikwa mukunzwisisa kwangu kugona kweiyo base kune sevhisi yekubatanidza, i.e. base inosimudzwa, iyo, sevhisi, inobvuma kubatana kubva kune vatengi. Uye kuwanikwa uku kunogona kuongororwa nemamwe maitiro. Zviri nyore kwazvo kuratidza maitiro aya pamadhibhodhi.

Wese munhu anoziva kuti dashboards chii. Apa ndipo pawakatarisa imwe skrini pane iyo inodiwa ruzivo inopfupikiswa. Uye iwe unogona kuona nekukurumidza kana paine dambudziko mudhatabhesi kana kwete.
Saizvozvo, kuwanikwa kwedhatabhesi uye humwe hunhu hwakakosha hunofanirwa kugara huchiratidzwa pamadhibhodhi kuitira kuti ruzivo urwu rwuve pedyo uye runowanikwa kwauri nguva dzose. Zvimwe zvekuwedzera izvo zvinotobatsira mukuferefetwa kwezviitiko, kana uchiongorora mamwe mamiriro ekukurumidzira, anotoda kuiswa pamadhibhodhi echipiri, kana kuvanzwa mumadhizaini ekudonha anotungamira kune wechitatu-bato rekutarisa masisitimu.

Muenzaniso weimwe inozivikanwa inotarisisa system. Iyi inotonhorera kwazvo yekutarisa system. Anounganidza data rakawanda, asi kubva pakuona kwangu, ane pfungwa inoshamisa yemadhibhodhi. Pane chinongedzo che "kugadzira dashboard". Asi paunogadzira dashboard, unogadzira runyoro rwemakoramu maviri, rondedzero yemagirafu. Uye kana iwe uchida kutarisa chimwe chinhu, unotanga kudzvanya negonzo, kupuruzira, uchitsvaga chaunoda chati. Uye izvi zvinotora nguva, i.e. hapana madhibhodhi akadaro. Pane rondedzero yemachati chete.

Chii chaunofanira kuwedzera kune aya madhibhodhi? Unogona kutanga nehunhu hwakadai senguva yekupindura. PostgreSQL ine pg_stat_statements maonero. Iyo yakaremara nekusarudzika, asi ndeimwe yeakakosha sisitimu maonero anofanirwa kugara achigoneswa uye kushandiswa. Inochengetedza ruzivo nezvese mibvunzo inomhanya iyo yakaitwa mudhatabhesi.

Saizvozvo, isu tinogona kutanga kubva pachokwadi chekuti isu tinokwanisa kutora yakazara nguva yekuuraya zvese zvikumbiro uye toigovanisa nenhamba yezvikumbiro tichishandisa minda iri pamusoro. Asi iyi ndiyo avhareji yekupisa muchipatara. Tinogona kutanga kubva kune mamwe minda - shoma yekubvunza nguva, yakawanda uye yepakati. Uye isu tinogona kunyange kuvaka percentiles; PostgreSQL ine mabasa anoenderana neizvi. Uye isu tinogona kuwana dzimwe nhamba dzinoratidza nguva yekupindura yedhatabhesi yedu yezvikumbiro zvakatopera, kureva kuti hatiite chikumbiro chekunyepedzera 'sarudza 1' uye titarise nguva yekupindura, asi isu tinoongorora nguva yekupindura kune yakatopedzwa zvikumbiro uye kudhirowa. ingave mufananidzo wakasiyana, kana isu tinovaka girafu kubva pairi.

Zvakakoshawo kutarisa nhamba yezvikanganiso zvinogadzirwa nehurongwa. Uye kune izvi unogona kushandisa iyo pg_stat_database maonero. Isu tinotarisa pane xact_rollback ndima. Iyi ndima inongoratidza kwete chete nhamba yezvidzoro zvinoitika mudhatabhesi, asi zvakare inofunga nezvehuwandu hwekukanganisa. Zvichienderana, tinokwanisa kuratidza nhamba iyi mudhibhodhi redu uye kuona kuti zvingani zvikanganiso zvatinazvo parizvino. Kana pane zvikanganiso zvakawanda, saka ichi ndicho chikonzero chakanaka chekutarisa mumatanda uye kuona kuti ndeapi zvikanganiso uye nei zvichiitika, uye wozoisa mari nekuzvigadzirisa.

Iwe unogona kuwedzera chinhu chakadai seTachometer. Iyi ndiyo nhamba yekutengeserana pasekondi uye nhamba yezvikumbiro pasekondi. Zvichienderana, unogona kushandisa nhamba idzi sekuita kwazvino kwedatabase rako uye tarisa kana paine zvikwiriso muzvikumbiro, peaks mukutengeserana, kana, ukuwo, kana dhatabhesi rakaremerwa nekuti imwe backend yatadza. Izvo zvakakosha kuti tigare takatarisa iyi nhamba uye tiyeuke kuti kune purojekiti yedu iyi mhando yekuita yakajairika, asi hunhu huri pamusoro uye pazasi hwatove humwe rudzi rwematambudziko uye asinganzwisisike, zvinoreva kuti tinofanira kutarisa kuti nei nhamba idzi dziri zvakakwirira kwazvo.

Kuti tifungidzire nhamba yekutengeserana, tinogona zvakare kutarisa kune pg_stat_database maonero. Isu tinokwanisa kuwedzera huwandu hwekuita uye huwandu hwekudzosera kumashure uye kuwana huwandu hwekutengesa pasekondi.

Munhu wese anonzwisisa here kuti zvikumbiro zvakati wandei zvinogona kukwana mukutengeserana kumwe chete? Naizvozvo TPS neQPS zvakasiyana zvishoma.

Huwandu hwezvikumbiro pasekondi imwe inogona kuwanikwa kubva pg_stat_statements uye kungoverenga huwandu hwese zvikumbiro zvakapedzwa. Zviri pachena kuti tinofananidza kukosha kwezvino neyekare, kuibvisa, kuwana delta, uye kuwana huwandu.

Iwe unogona kuwedzera mamwe metrics kana uchida, ayo anobatsirawo kuongorora kuwanikwa kwedhatabhesi redu uye kutarisa kana paine nguva yekudzikira.

Imwe yeaya metrics ndeye uptime. Asi uptime muPostgreSQL ine hunyanzvi. Ndichakuudza chikonzero. Kana PostgreSQL yatanga, uptime inotanga kushuma. Asi kana pane imwe nguva, semuenzaniso, rimwe basa raiita husiku, muurayi weOOM akauya ndokumisa nechisimba chirongwa chePostgreSQL chemwana, zvino mune iyi nyaya PostgreSQL inomisa kubatana kwevatengi vese, inogadzirisa nzvimbo yekurangarira uye inotanga kupora kubva. chekupedzisira chekuongorora. Uye nepo kudzoreredza uku kubva pacheki kunogara, dhatabhesi haigamuchire kubatana, i.e. mamiriro aya anogona kuongororwa senguva yekudzikira. Asi iyo uptime counter haizogadzirwe zvakare, nekuti inotora iyo postmaster yekutanga nguva kubva panguva yekutanga. Nokudaro, mamiriro ezvinhu akadaro anogona kurekwa.

Iwe unofanirwawo kutarisa huwandu hwevashandi vevacuum. Munhu wese anoziva here kuti chii chinonzi autovacuum muPostgreSQL? Iyi inonakidza subsystem muPostgreSQL. Zvinyorwa zvakawanda zvakanyorwa pamusoro pake, mishumo yakawanda yakaitwa. Pane hurukuro dzakawanda nezve vacuum uye kuti inofanira kushanda sei. Vakawanda vanozviona sezvakaipa zvakakodzera. Asi ndozvazvinoita. Iyi imhando yeanalogue yemuunganidzi wemarara iyo inosuka ekare emitsara yemitsara isingadikanwi nechero kutengeserana uye inosunungura nzvimbo mumatafura uye indexes yemitsara mitsva.

Sei uchifanira kuiongorora? Nokuti vacuum dzimwe nguva inorwadza zvikuru. Iyo inoshandisa huwandu hukuru hwezviwanikwa uye zvikumbiro zvevatengi zvinotanga kutambura semhedzisiro.

Uye inofanirwa kutariswa kuburikidza ne pg_stat_activity view, yandichataura nezvayo muchikamu chinotevera. Maonero aya anoratidza chiitiko chazvino mudhatabhesi. Uye kuburikidza nechiito ichi tinokwanisa kuronda nhamba yevacuums iri kushanda izvozvi. Tinogona kuronda vacuums uye kuona kuti kana isu takapfuura muganho, saka ichi chikonzero chekutarisa muPostgreSQL marongero uye neimwe nzira kukwidziridza kushanda kwevacuum.

Chimwe chinhu pamusoro pePostgreSQL ndechekuti PostgreSQL inorwara zvakanyanya nekutengesa kwenguva refu. Kunyanya kubva kune matransaction anorembera kwenguva yakareba uye hapana chaanoita. Iyi ndiyo inonzi stat idle-in-transaction. Kutengeserana kwakadaro kunobata makiyi uye kunodzivirira vacuum kushanda. Uye semugumisiro, matafura anowedzera uye anowedzera muhukuru. Uye mibvunzo inoshanda nematafura aya inotanga kushanda zvishoma nezvishoma, nekuti iwe unofanirwa kufoshorosa ese ekare mavhezheni emitsara kubva kundangariro kuenda ku diski uye kumashure. Naizvozvo, iyo nguva, nguva yekurebesa kutengeserana, iyo yakareba mikumbiro yevacuum inodawo kuongororwa. Uye kana tikaona mamwe maitiro anga achimhanya kwenguva yakareba, atopfuura 10-20-30 maminetsi emutoro weOLTP, saka isu tinofanirwa kuateerera uye nekuamisa nechisimba, kana kukwidziridza application kuti ivo haana kudanwa uye usarembera kwenguva refu. Kune yekuongorora basa rekuita, 10-20-30 maminetsi akajairwa; kune zvakare akareba.

Tevere isu tine sarudzo nevatengi vakabatana. Kana isu tatogadzira dashboard uye takaisa makiyi ekuwanika metrics pairi, isu tinogona zvakare kuwedzera rumwe ruzivo nezve macustomer akabatana ipapo.

Ruzivo rwevatengi vakabatana rwakakosha nekuti, kubva paPostgreSQL maonero, vatengi vakasiyana. Kune vatengi vakanaka uye kune vatengi vakaipa.

Muenzaniso wakapfava. Nemutengi ndinonzwisisa application. Iyo application yakabatana nedhatabhesi uye pakarepo inotanga kutumira zvikumbiro zvayo ipapo, iyo dhatabhesi maitiro uye ozviita, uye anodzosera mhinduro kumutengi. Aya makasitoma akanaka uye akarurama.

Pane mamiriro ezvinhu apo mutengi akabatana, anobata chinongedzo, asi haaite chinhu. Iri mukusashanda.

Asi kune vatengi vakaipa. Semuenzaniso, mutengi mumwechete akabatana, akavhura kutengeserana, akaita chimwe chinhu mudhatabhesi ndokubva apinda mukodhi, semuenzaniso, kuwana imwe yekunze kana kugadzirisa data yakagamuchirwa ipapo. Asi haana kuvhara kutengeserana. Uye kutengeserana kunorembera mudhatabhesi uye inobatwa mukiyi pamutsetse. Ichi chimiro chakashata. Uye kana kamwe kamwe application pane imwe nzvimbo mukati mayo pachayo ikatadza kunze, saka kutengeserana kunogona kuramba kwakavhurika kwenguva yakareba. Uye izvi zvinobata zvakananga PostgreSQL kuita. PostgreSQL ichanonoka. Naizvozvo, zvakakosha kuronda vatengi vakadaro nenguva uye nekumisa basa ravo nesimba. Uye iwe unofanirwa kukwidziridza application yako kuti mamiriro akadai arege kuitika.

Vamwe vatengi vakaipa vakamirira vatengi. Asi vanova vakaipa nemhaka yemamiriro ezvinhu. Semuyenzaniso, nyore kusashanda kutengeserana: inogona kuvhura kutengeserana, kutora makiyi pane mamwe mitsara, ipapo pane imwe nzvimbo mukodhi ichakundikana, ichisiya yakarembera kutengeserana. Mumwe mutengi achauya okumbira iyo data yakafanana, asi achasangana nekiyi, nekuti iyo yakaturika kutengeserana inotobata makiyi pamitsetse inodiwa. Uye chechipiri chekutengeserana chicharembera chakamirira kuti kutengeserana kwekutanga kupedze kana kuvhara nechisimba nemutungamiriri. Naizvozvo, zvakamirira kutengeserana zvinogona kuunganidza uye kuzadza iyo database yekubatanidza muganho. Uye kana muganho wazara, chishandiso hachichakwanisa kushanda nedhatabhesi. Iyi yatova mamiriro echimbichimbi epurojekiti. Naizvozvo, vatengi vakaipa vanofanirwa kuteverwa uye kupindurwa nenguva.

Mumwe muenzaniso wekutarisa. Uye panotova nedeshibhodhi rakanaka. Pane ruzivo pamusoro pekubatana. DB kubatana - 8 zvidimbu. Uye ndizvo zvose. Isu hatina ruzivo rwekuti ndevapi vatengi vari kushanda, ndevapi vatengi vanongo shanda, hapana chavari kuita. Iko hakuna ruzivo pamusoro pekumirira kutengeserana uye kumirira kubatana, i.e. iyi inhamba inoratidza huwandu hwekubatanidza uye ndizvozvo. Wobva wazvifungira.

Nekudaro, kuti uwedzere ruzivo urwu pakutarisa, unofanirwa kuwana iyo pg_stat_activity system maonero. Kana iwe uchipedza nguva yakawanda muPostgreSQL, zvino iyi ndiyo maonero akanaka kwazvo anofanira kuva shamwari yako, nokuti inoratidza chiitiko chiripo muPostgreSQL, i.e. zviri kuitika mairi. Pachirongwa chega chega pane mutsara wakasiyana unoratidza ruzivo pamusoro pekuita uku: kubva kune mugadziri wekubatanidza wakaitwa, pasi pemushandisi, pasi pezita ripi, pakatanga kutengeserana, chii chikumbiro chiri kuita izvozvi, chikumbiro chipi chakapedzisira kuitwa. Uye, maererano, isu tinogona kuongorora mamiriro emutengi tichishandisa iyo stat ndima. Zvichienderana nekutaura, isu tinokwanisa kuungana nendima iyi uye towana iwo manhamba ari mudhatabhesi uye nhamba yekubatanidza ine iyi stat mudhatabhesi. Uye isu tinogona kutumira nhamba dzakatogamuchirwa kune yedu yekutarisa uye kudhirowa magirafu zvichibva pazviri.
Zvakakoshawo kuongorora nguva yekutengeserana. Ndakatotaura kuti zvakakosha kuongorora nguva yevacuums, asi kutengeserana kunoongororwa nenzira imwecheteyo. Pane xact_start uye query_start minda. Ivo, nekutaura, vanoratidza nguva yekutanga yekutengeserana uye nguva yekutanga yekukumbira. Isu tinotora ikozvino () basa, iro rinoratidza nguva iripo, uye kubvisa kutengeserana uye kukumbira timestamp. Uye isu tinowana nguva yekutengeserana, nguva yekukumbira.

Kana tikaona kutengeserana kwenguva refu, tinofanira kuapedza kare. Pamutoro weOLTP, kutengeserana kwakareba kwatopfuura maminitsi 1-2-3. Kune basa reOLAP, kutengeserana kwakareba kwakajairika, asi kana vakatora anopfuura maawa maviri kuti vapedze, saka ichi chiratidzo zvakare chekuti isu tine skew kumwe kunhu.

Kana vatengi vangobatana nedatabase, vanotanga kushanda nedata redu. Ivo vanowana matafura, vanowana indexes kuti vawane data kubva patafura. Uye zvakakosha kuti uongorore kuti vatengi vanobatana sei neiyi data.

Izvi zvinodikanwa kuti tiongorore mutoro wedu webasa uye tinzwisise kuti ndeapi matafura "anonyanya kupisa" kwatiri. Semuenzaniso, izvi zvinodikanwa mumamiriro ezvinhu apo tinoda kuisa matafura "anopisa" pane imwe mhando yekutsanya SSD kuchengetedza. Semuenzaniso, mamwe matafura ezvinyorwa zvatisina kushandisa kwenguva yakareba anogona kutamirwa kune imwe mhando ye "inotonhora" archive, kune SATA drives uye kuvarega vachigara ikoko, ivo vanozowanikwa sezvinodiwa.

Izvi zvinobatsirawo pakuona anomalies mushure mekuburitswa uye deployments. Ngatitii purojekiti yakaburitsa chimwe chinhu chitsva. Semuenzaniso, takawedzera hutsva hutsva hwekushanda ne database. Uye kana tikaronga magirafu ekushandisa tafura, tinogona kuona zviri nyore izvi zvisinganzwisisike pane aya magirafu. Semuenzaniso, kugadzirisa kuputika kana kudzima kuputika. Zvichaonekwa zvakanyanya.

Iwe unogona zvakare kuona anomalies mune "inoyangarara" manhamba. Zvinorevei? PostgreSQL ine yakasimba kwazvo uye yakanaka kwazvo yemubvunzo kuronga. Uye vanogadzira vanopa nguva yakawanda mukukura kwayo. Anoshanda sei? Kuti uite zvirongwa zvakanaka, PostgreSQL inounganidza huwandu hwekugovera data mumatafura pane imwe nguva yepakati uye neimwe frequency. Aya ndiwo maitiro akajairika: nhamba yemhando dzakasiyana, ruzivo nezve NULL mutafura, ruzivo rwakawanda.

Zvichienderana nenhamba idzi, murongi anovaka akati wandei mibvunzo, anosarudza iyo yakanyanya kunaka, uye anoshandisa chirongwa chemubvunzo uyu kuita mubvunzo wacho uye kudzorera data.

Uye zvinoitika kuti nhamba "float". Hunhu uye huwandu data neimwe nzira yakachinja mutafura, asi nhamba haina kuunganidzwa. Uye zvirongwa zvakaumbwa zvinogona kunge zvisina kunaka. Uye kana zvirongwa zvedu zvikave zvidiki zvichibva pakutariswa kwakaunganidzwa, zvichibva pamatafura, tinozokwanisa kuona izvi zvisizvo. Semuenzaniso, pane imwe nzvimbo iyo data yakachinja qualitatively uye panzvimbo ye index, sequential kupfuura netafura yakatanga kushandiswa, i.e. kana mubvunzo uchingoda kudzosa mitsara zana chete (pane muganhu we100), ipapo tsvakiridzo yakakwana ichaitwa pamubvunzo uyu. Uye izvi zvinogara zvine mhedzisiro yakaipa kwazvo pakuita.

Uye isu tinogona kuona izvi mukutarisa. Uye gara tarisa mubvunzo uyu, mhanya uchitsanangura, tora manhamba, gadzira imwe nyowani index. Uye pindura kare kune dambudziko iri. Ndiko kusaka zvakakosha.

Mumwe muenzaniso wekutarisa. Ndinofunga kuti vanhu vakawanda vakamuziva nekuti ane mukurumbira. Ndiani anoishandisa mumapurojekiti avo Prometheus? Ndiani anoshandisa chigadzirwa ichi pamwe chete naPrometheus? Icho chokwadi ndechekuti mune yakajairwa repository yekutarisa uku kune dashboard yekushanda nePostgreSQL - postgres_exporter Prometheus. Asi pane chimwe chinhu chakaipa.

Kune akawanda magirafu. Uye mabhayiti anoratidzwa sekubatana, i.e. kune 5 magirafu. Aya ndiwo Isa data, Gadziridza data, Delete data, Tora data uye Dzosera data. Kuyera kweyuniti mabhayiti. Asi chinhu ndechekuti manhamba muPostgreSQL anodzosa data mune tuple (mitsara). Uye, saizvozvo, magirafu aya inzira yakanaka kwazvo yekurerutsa basa rako kakawanda, makumi enguva, nekuti tuple haisi bhaiti, tuple itambo, ine mabhayiti akawanda uye inogara iine urefu hwakasiyana. Ndiko kuti, kuverenga mutoro webasa mumabhaiti uchishandisa tuples ibasa risiri rechokwadi kana rakaoma kwazvo. Naizvozvo, kana iwe ukashandisa dashboard kana yakavakirwa-mukati yekutarisa, zvinogara zvakakosha kuti unzwisise kuti inoshanda nemazvo uye inokudzosera iwe nenzira kwayo yakaongororwa data.

Nzira yekuwana sei nhamba pamatafura aya? Nechinangwa ichi, PostgreSQL ine imwe mhuri yemaonero. Uye maonero makuru ndeokuti pg_stat_user_tables. Mushandisi_matafura - izvi zvinoreva matafura akagadzirirwa mushandisi. Kusiyana neizvi, kune maonero ehurongwa ayo anoshandiswa nePostgreSQL pachayo. Uye kune pfupiso tafura Alltables, iyo inosanganisira ese ari maviri system uye mushandisi iwo. Iwe unogona kutanga kubva kune chero ipi zvayo yaunoda zvakanyanya.

Uchishandisa minda iri pamusoro unogona kufungidzira huwandu hwekuisa, kugadzirisa uye kubvisa. Muenzaniso wedashboard yandakashandisa inoshandisa minda iyi kuongorora maitiro emutoro webasa. Nokudaro, tinogonawo kuvaka pamusoro pazvo. Asi zvakakosha kuti tiyeuke kuti aya matuples, kwete mabheti, saka isu hatigone kuzviita mumabhaiti.

Kubva pane iyi data, tinogona kuvaka anonzi matafura eTopN. Somuenzaniso, Top-5, Pamusoro-10. Uye iwe unogona kuteedzera iwo matafura anopisa anodzoreredzwa kupfuura mamwe. Semuenzaniso, 5 "inopisa" matafura ekuisa. Uye tichishandisa aya matafura eTopN tinoongorora basa redu uye tinokwanisa kuongorora kuputika kwebasa mushure mekuburitswa, zvigadziriso, uye kutumirwa.

Izvo zvakakoshawo kuongorora saizi yetafura, nekuti dzimwe nguva vanogadzira vanoburitsa chinhu chitsva, uye matafura edu anotanga kuzvimba muhukuru hwavo hukuru, nekuti vakasarudza kuwedzera imwe data, asi havana kufanotaura kuti izvi zvaizoita sei. kukanganisa saizi yedatabase. Nyaya dzakadai dzinouyawo sechishamiso kwatiri.

Uye zvino mubvunzo mudiki kwauri. Ndeupi mubvunzo unomuka paunoona mutoro pane yako database server? Mubvunzo unotevera waunawo ndeupi?

Asi chaizvoizvo mubvunzo unomuka sezvinotevera. Ndezvipi zvikumbiro zvinokonzeresa mutoro? Ndiko kuti, hazvifadzi kutarisa maitiro anokonzerwa nemutoro. Zviri pachena kuti kana muenzi ane dhatabhesi, saka dhatabhesi iri kushanda ipapo uye zviri pachena kuti dhatabhesi chete ndiro richaraswa ipapo. Kana tikavhura Pamusoro, tichaona pane runyorwa rwemaitiro muPostgreSQL ari kuita chimwe chinhu. Hazvizojekese kubva kuTop kuti varikuitei.

Saizvozvo, iwe unofanirwa kutsvaga iyo mibvunzo inokonzeresa mutoro wepamusoro, nekuti kugadzirisa mibvunzo, semutemo, inopa purofiti yakawanda kupfuura kugadzirisa iyo PostgreSQL kana sisitimu yekushandisa, kana kurongedza Hardware. Maererano nefungidziro yangu, izvi zvingangoita 80-85-90%. Uye izvi zvinoitwa nekukurumidza zvikuru. Inokurumidza kugadzirisa chikumbiro pane kugadzirisa kurongeka, kuronga kutangazve, kunyanya kana dhatabhesi isingagoni kutangwazve, kana kuwedzera hardware. Zviri nyore kunyora mubvunzo kune imwe nzvimbo kana kuwedzera index kuti uwane mhinduro iri nani kubva pamubvunzo uyu.

Saizvozvo, zvinodikanwa kutarisa zvikumbiro uye kukwana kwavo. Ngatitorei mumwe muenzaniso wekutarisa. Uye pano, zvakare, panoratidzika kuve nekutarisisa kwakanakisa. Pane ruzivo rwekudzokorora, kune ruzivo rwekupfuura, kuvharira, kushandiswa kwezviwanikwa. Zvese zvakanaka, asi hapana ruzivo pane zvikumbiro. Hazvina kujeka kuti ndeipi mibvunzo iri kuita mudhatabhesi redu, kuti inguva yakareba sei, kuti mingani yemibvunzo iyi. Isu tinofanirwa kugara tiine ruzivo urwu mukutarisa kwedu.

Uye kuti tiwane ruzivo urwu tinogona kushandisa iyo pg_stat_statements module. Kubva pairi, unogona kugadzira magirafu akasiyana-siyana. Semuyenzaniso, unogona kuwana ruzivo pamusoro pemivhunzo inowanzoitika, kureva, pane iyo mibvunzo inowanzoitwa. Ehe, mushure mekutumirwa kunobatsirawo zvakanyanya kuzvitarisa uye kunzwisisa kana paine kuvhiya kwezvikumbiro.

Unogona kutarisa mibvunzo yakareba, kureva kuti, iyo mibvunzo inotora nguva refu kupedzisa. Ivo vanomhanya pane processor, vanodya I/O. Tinogona zvakare kuongorora izvi tichishandisa minda total_time, mean_time, blk_write_time uye blk_read_time.

Tinogona kuongorora nekutarisa zvikumbiro zvinorema maererano nekushandisa zviwanikwa, izvo zvinoverenga kubva kudhisiki, zvinoshanda nendangariro, kana, zvakasiyana, kugadzira imwe mhando yekunyora mutoro.

Tinogona kuongorora zvikumbiro zverupo zvakanyanya. Iyi ndiyo mibvunzo inodzosa nhamba huru yemitsara. Semuenzaniso, ichi chinogona kunge chiri chimwe chikumbiro chavakakanganwa kuisa muganhu. Uye inongodzosera zvese zviri mukati metafura kana kubvunza pamatafura akabvunzwa.

Uye iwe unogona zvakare kutarisa mibvunzo inoshandisa mafaira enguva pfupi kana matafura enguva pfupi.

Uye tichine maitiro ekumashure. Maitiro ekumashure anonyanya kutarisa kana anonziwo macheki, aya autovacuum uye kudzokorora.

Mumwe muenzaniso wekutarisa. Kune Kugadzirisa tebhu kuruboshwe, enda kwairi uye tarisira kuona chimwe chinhu chinobatsira. Asi pano ingori nguva yevacuum oparesheni uye manhamba ekuunganidza, hapana chimwe. Iri iruzivo rwakaipa kwazvo, saka isu tinofanirwa kugara tiine ruzivo rwekuti maitiro ekumashure anoshanda sei mudura redu uye kana paine matambudziko kubva kubasa ravo.

Kana isu tikatarisa macheki, isu tinofanirwa kuyeuka kuti macheki anotsvaira mapeji akasviba kubva munzvimbo yakaomeswa memory kuenda kudhisiki, wobva wagadzira cheki. Uye iyi yekutarisa inogona kuzoshandiswa senzvimbo yekudzoreredza kana PostgreSQL yakangoerekana yamiswa munjodzi.

Saizvozvowo, kuitira kuti ubvise mapeji ose "akasviba" ku diski, unoda kuita humwe huwandu hwekunyora. Uye, sekutonga, pane masisitimu ane huwandu hukuru hwekurangarira, izvi zvakawanda. Uye kana tikaita macheckpoints kazhinji munguva pfupi, ipapo disk performance inodonha zvakanyanya. Uye zvikumbiro zvevatengi zvichatambura nekushaikwa kwezviwanikwa. Vachakwikwidzana nezviwanikwa uye kushaya goho.

Saizvozvo, kuburikidza ne pg_stat_bgwriter tichishandisa minda yakatsanangurwa isu tinogona kutarisa huwandu hwekutarisa kunoitika. Uye kana tine nzvimbo dzakawanda dzekutarisa pane imwe nguva yenguva (mumaminitsi 10-15-20, muhafu yeawa), somuenzaniso, 3-4-5, zvino izvi zvinogona kunge zvatova dambudziko. Uye iwe unotofanirwa kutarisa mudhatabhesi, tarisa mukugadzirisa, chii chinokonzera kuwanda kwakadaro kwekutarisa. Pamwe pane imwe mhando yekurekodha huru iri kuitika. Isu tinogona kutoongorora basa, nekuti isu tatowedzera magirafu ebasa. Isu tinogona kutogadzirisa mapeji ekutarisa uye tive nechokwadi chekuti haanyanyi kukanganisa kuita kwemubvunzo.

Ndiri kudzoka ku autovacuum zvakare nekuti chinhu chakadaro, sezvandakataura, chinogona kuwedzera zviri nyore dhisiki uye query performance, saka zvinogara zvakakosha kufungidzira huwandu hwe autovacuum.

Huwandu hwevashandi veautovacuum mudhatabhesi ishoma. Nekutadza, kune matatu acho, saka kana isu tichigara tiine vashandi vatatu vanoshanda mudhatabhesi, izvi zvinoreva kuti autovacuum yedu haina kugadzirwa, isu tinofanirwa kusimudza miganhu, dzokorora zvigadziriso zve autovacuum uye kupinda mukugadzirisa.
Zvakakosha kuongorora kuti ndevapi vacuum vashandi vatinavo. Kunyangwe iyo yakatangwa kubva kumushandisi, iyo DBA yakauya uye nemaoko yakatanga imwe mhando yevacuum, uye izvi zvakagadzira mutoro. Tine imwe mhando yedambudziko. Kana iyi ndiyo nhamba yevacuums iyo inosunungura iyo yekutengeserana counter. Kune mamwe mavhezheni ePostgreSQL aya anorema vacuums. Uye ivo vanogona kuwedzera kuita zviri nyore nekuti vanoverenga tafura yese, tarisa mabhuroki ese ari patafura iyoyo.

Uye, chokwadi, nguva yevacuums. Kana isu tine vacuums inogara kwenguva refu inomhanya kwenguva yakareba, saka izvi zvinoreva kuti isu tinoda zvakare kuterera kune vacuum gadziriso uye pamwe funga zvakare marongero ayo. Nokuti mamiriro ezvinhu anogona kuitika apo vacuum inoshanda patafura kwenguva yakareba (maawa 3-4), asi panguva iyo vacuum yaishanda, yakawanda yemitsara yakafa yakakwanisa kuunganidza mutafura zvakare. Uye kana vacuum yangopera, anofanira kutsvaira tafura iyi zvakare. Uye tinosvika pamamiriro ezvinhu - vacuum isingaperi. Uye munyaya iyi, vacuum haigoni kushanda nebasa rayo, uye matafura zvishoma nezvishoma anotanga kuzvimba muhukuru, kunyange zvazvo vhoriyamu ye data inobatsira mairi inoramba yakafanana. Nokudaro, munguva yevacuums yakareba, tinogara tichitarisa kugadzirisa uye tinoedza kuigadzirisa, asi panguva imwe chete kuitira kuti kushanda kwezvikumbiro zvevatengi zvirege kutambura.

Mazuva ano hapana kuisirwa PostgreSQL iyo isina kudzokorora kutenderera. Kudzokorora ndiyo maitiro ekufambisa data kubva kuna tenzi kuenda kune replica.

Kudzokorora muPostgreSQL kunoitwa kuburikidza nerogi yekutengeserana. Iyo wizard inogadzira chinyorwa chekutengeserana. Iyo dhizaini yedhizaini inofamba pamusoro peiyo network yekubatanidza kune replica, uye yobva yadhindwa pane replica. Zviri nyore.

Saizvozvo, iyo pg_stat_replication yekuona inoshandiswa kutarisa replication lag. Asi hazvisi zvese zviri nyore naye. Mushanduro yegumi, maonero akachinja akati wandei. Chekutanga, mamwe minda akatumidzwa zita. Uye mamwe minda akawedzerwa. Mune vhezheni yegumi, minda yakaoneka inobvumidza iwe kuti ufungidzire kusakara kwekudzokorora mumasekonzi. Yakanyanya kugadzikana. Pamberi pevhezheni 10, zvaigoneka kufungidzira kudzokorora lag mumabhaiti. Iyi sarudzo inoramba iri muvhezheni yegumi, i.e. unogona kusarudza izvo zvakakunakira - fungidzira lag mumabhaiti kana fungidzira lag mumasekonzi. Vanhu vakawanda vanoita zvose zviri zviviri.

Asi zvakadaro, kuti uongorore iyo replication lag, iwe unofanirwa kuziva chinzvimbo chegiyo mukutengesa. Uye idzi transaction log zvinzvimbo zviri chaizvo mu pg_stat_replication maonero. Zvichienderana, tinogona kutora mapoinzi maviri mugidhi rekutengesa tichishandisa pg_xlog_location_diff () basa. Verenga delta pakati pavo uye uwane replication lag mumabhaiti. Zviri nyore chaizvo uye nyore.

Mushanduro yegumi, basa iri rakatumidzwa zita rekuti pg_wal_lsn_diff(). Kazhinji, mumabasa ese, maonero, uye zvishandiso pakaonekwa izwi rekuti "xlog", rakatsiviwa neukoshi "wal". Izvi zvinoshanda kune zvose maonero uye mabasa. Iyi innovation yakadaro.

Uyezve, muvhezheni 10, mitsetse yakawedzerwa inotaridza lag. Aya ndiwo nyora lag, flush lag, replay lag. Kureva kuti zvakakosha kutarisa zvinhu izvi. Kana tikaona kuti isu tine replication lag, saka tinoda kuongorora kuti sei yakaonekwa, kwayakabva uye kugadzirisa dambudziko.

Zvinenge zvese zviri muhurongwa nesystem metrics. Kana chero kutarisa kunotanga, kunotanga nesystem metrics. Uku ndiko kuraswa kwe processors, memory, swap, network uye disk. Nekudaro, akawanda ma parameter haapo nekukasira.

Kana zvinhu zvose zvakarongeka nekugadzirisa zvakare, ipapo kune matambudziko nedhisiki recycling. Sezvo mutemo, vanogadzira vanoongorora vanowedzera ruzivo nezve throughput. Inogona kunge iri iops kana mabheti. Asi ivo vanokanganwa nezve latency uye kushandiswa kwedhisiki zvishandiso. Aya ndiwo akanyanya kukosha ma paramita anotitendera kuti tiongorore kuti madhisiki edu akaremerwa sei uye kuti anononoka sei. Kana isu tine high latency, saka izvi zvinoreva kuti kune mamwe matambudziko ne disks. Kana isu tine kushandiswa kwepamusoro, zvinoreva kuti ma disks haasi kugadzirisa. Aya ndiwo maitiro ari nani pane kungopfuura.

Uyezve, nhamba idzi dzinogona kuwanikwawo kubva ku/proc faira system, sezvinoitwa kudzokorodza processors. Handizive kuti sei ruzivo urwu rusina kuwedzerwa kukutarisa. Asi zvakadaro, zvakakosha kuve neizvi mukutarisisa kwako.

Izvi zvinoshandawo kune network interfaces. Kune ruzivo nezve network throughput mumapaketi, mumabytes, asi zvakadaro hapana ruzivo nezve latency uye hapana ruzivo nezve mashandisiro, kunyangwe ichi chiri zvakare ruzivo ruzivo.

Chero kutarisa kune zvipingamupinyi. Uye kunyangwe imhando ipi yekutarisa yaunotora, inogara isingaite mamwe maitiro. Asi zvakadaro, ivo vari kusimukira, zvinhu zvitsva uye zvinhu zvitsva zviri kuwedzerwa, saka sarudza chimwe chinhu uye chipedze.

Uye kuti upedze, iwe unofanirwa kugara uine zano rezvinoreva nhamba dzakapihwa uye kuti ungadzishandisa sei kugadzirisa matambudziko.

Uye mashoma akakosha mapoinzi:

• Iwe unofanirwa kugara uchitarisa kuwanikwa uye uine dashboards kuitira kuti iwe ugone kukurumidza kuongorora kuti zvese zviri mugwara nedatabase.
• Iwe unogara uchifanirwa kuve neruzivo rwe izvo vatengi vari kushanda nedhatabhesi rako kuitira kuti ubvise vatengi vakaipa uye kuvapfura pasi.
• Zvakakosha kuongorora kuti vatengi ava vanoshanda sei nedata. Iwe unofanirwa kuva neruzivo pamusoro pebasa rako.
• Zvakakosha kuongorora kuti basa iri rinoumbwa sei, nerubatsiro rwemibvunzo ipi. Iwe unogona kuongorora mibvunzo, iwe unogona kuigonesa, kuigadzirisa, kuvakira ma indexes. Chinokosha zvikuru.
• Maitiro ekumashure anogona kukanganisa zvikumbiro zvevatengi, saka zvakakosha kutarisa kuti havasi kushandisa zviwanikwa zvakawandisa.
• Sisitimu metrics inobvumidza iwe kuti uite zvirongwa zvekuyera uye kuwedzera huwandu hwemaseva ako, saka zvakakosha kuti uvateedzere uye uvaongorore zvakare.

Kana iwe uchifarira nyaya iyi, saka unogona kutevera aya ma link.
http://bit.do/stats_collector - aya ndiwo magwaro epamutemo kubva kumuunganidzi wenhamba. Pane tsananguro yezvese maonero enhamba uye tsananguro yeminda yese. Unogona kuverenga, kunzwisisa uye kuongorora. Uye zvichibva pazviri, gadzira magirafu ako uye woawedzera kune yako yekutarisa.

Zvikumbiro zvemuenzaniso:
http://bit.do/dataegret_sql
http://bit.do/lesovsky_sql

Iyi ndiyo corporate repository yedu uye yangu. Vane mibvunzo yemuenzaniso. Iko hakuna mivhunzo kubva pane yakasarudzwa* kubva kunhevedzano ipapo. Iko kune yatove yakagadzirira-yakagadzirwa mibvunzo ine majoini, uchishandisa anonakidza mabasa anobvumidza iwe kushandura nhamba mbishi kuita zvinoverengeka, zviri nyore kukosha, i.e. aya mabhayiti, nguva. Iwe unogona kuvatora, kuvatarisa, kuvaongorora, kuwedzera kune yako yekutarisa, kuvaka yako yekutarisa kubva pavari.

Mibvunzo yako

Mubvunzo: Wati hausi kuzoshambadza mabhurendi, asi ndichiri kuda kuziva - rudzii rwemadhibhodhi aunoshandisa muzvirongwa zvako?
Mhinduro: Zvinosiyana. Zvinoitika kuti tinouya kune mutengi uye iye atova neake monitoring. Uye isu tinoraira mutengi pane izvo zvinoda kuwedzerwa kune yavo yekutarisa. Mamiriro akaipisisa ari neZabbix. Nekuti haina kugona kuvaka TopN magirafu. Isu pachedu tinoshandisa Okmeter, nekuti taibvunzana nevakomana ava pamonitoring. Ivo vakatarisa PostgreSQL zvichibva pane yedu tekinoroji yakatarwa. Ndiri kunyora yangu pet-purojekiti, iyo inounganidza data kuburikidza nePrometheus uye inoishandura mukati grafana. Basa rangu nderekugadzira zvangu mutengesi kunze kwenyika muPrometheus ndozopa zvese muGrafana.

Mubvunzo: Pane here analogues eAWR mishumo kana ... aggregation? Unoziva nezvechinhu chakadai here?
Mhinduro: Hongu, ndinoziva chinonzi AWR, chinhu chinotonhorera. Parizvino kune akasiyana mabhasikoro anoshandisa anenge anotevera modhi. Pane imwe nguva yenguva, mamwe mabhesi anonyorerwa kune imwecheteyo PostgreSQL kana kune imwe nzvimbo yekuchengetedza. Unogona kuvatsvaga paInternet, varipo. Mumwe wevagadziri vechinhu chakadaro akagara pane sql.ru forum muPostgreSQL thread. Unogona kumubata ipapo. Hongu, kune zvinhu zvakadaro, zvinogona kushandiswa. Uyezve mune zvayo pgCenter Ndiri kunyorawo chinhu chinokutendera kuti uite chinhu chimwe chete.

PS1 Kana uri kushandisa postgres_exporter, ndeipi dashboard yauri kushandisa? Kune akati wandei awo. Dzatove dzechinyakare. Pamwe nharaunda ichagadzira yakagadziridzwa template?

PS2 Yakabviswa pganalyze nekuti ndeyemuridzi weSaaS inopa iyo inotarisana nekutarisa kwekuita uye otomatiki tuning mazano.

Vashandisi vakanyoresa chete ndivo vanogona kutora chikamu muongororo. Nyorera mu, Munogamuchirwa.

Ndeipi yega-yakagashira postgresql yekutarisa (ine dashboard) yaunoona yakanyanya kunaka?

• 30,0%Zabbix + kuwedzera kubva kuna Alexey Lesovsky kana zabbix 4.4 kana libzbxpgsql + zabbix libzbxpgsql + zabbix3

• 0,0%https://github.com/lesovsky/pgcenter0

• 0,0%https://github.com/pg-monz/pg_monz0

• 20,0%https://github.com/cybertec-postgresql/pgwatch22

• 20,0%https://github.com/postgrespro/mamonsu2

• 0,0%https://www.percona.com/doc/percona-monitoring-and-management/conf-postgres.html0

• 10,0%pganalyze ndeye muridzi SaaS - ini handikwanise kuidzima1

• 10,0%https://github.com/powa-team/powa1

• 0,0%https://github.com/darold/pgbadger0

• 0,0%https://github.com/darold/pgcluu0

• 0,0%https://github.com/zalando/PGObserver0

• 10,0%https://github.com/spotify/postgresql-metrics1

10 vashandisi vakavhota. 26 vashandisi vakaramba.

Source: www.habr.com