Iziseko zokubeka iliso kwiPostgreSQL. Alexey Lesovsky

Ndicebisa ukuba ufunde ushicilelo lwengxelo ka-Alexey Lesovsky kwi-Data Egret "Iisiseko sokubeka iliso kwi-PostgreSQL"

Kule ngxelo, u-Alexey Lesovsky uya kuthetha malunga neengongoma eziphambili ze-post-gress statistics, zithetha ntoni, kwaye kutheni kufuneka zibe khona ekubekeni iliso; malunga nokuba zeziphi iigrafu emazibe kuhlolo, ukuba zongezwa njani kwaye zitolikwa njani. Ingxelo iya kuba luncedo kubalawuli bedatha, abalawuli benkqubo kunye nabaphuhlisi abanomdla kwi-Postgres yokulungisa iingxaki.

Igama lam ngu-Alexey Lesovsky, ndimele inkampani yeData Egret.

Amagama ambalwa malunga nam. Ndaqala kudala njengomlawuli wenkqubo.

Ndalawula zonke iintlobo zeenkqubo zeLinux ezahlukeneyo, zasebenza kwizinto ezahlukahlukeneyo ezinxulumene neLinux, oko kukuthi i-virtualization, ukubeka iliso, ukusebenza kunye ne-proxies, njl. Bendimthanda nyani. Kwaye ngaxa lithile ndaqala ukusebenza kwiPostgreSQL uninzi lwexesha lam lokusebenza. Kwaye ke ngokuthe ngcembe ndaba yi-PostgreSQL DBA.

Kwaye kuwo wonke umsebenzi wam, bendisoloko ndinomdla kwizihloko zamanani, ukubeka iliso, kunye ne-telemetry. Kwaye xa ndingumlawuli wenkqubo, ndasebenza ngokusondeleyo neZabbix. Kwaye ndabhala iseti encinci yezikripthi ezifana zabbix-izandiso. Wayedume kakhulu ngexesha lakhe. Kwaye kwakunokwenzeka ukubeka esweni izinto ezibalulekileyo ezahlukeneyo, kungekuphela nje iLinux, kodwa kunye nezinto ezahlukeneyo.

Ngoku ndisebenza kwiPostgreSQL. Sele ndibhala enye into ekuvumela ukuba usebenze ngezibalo zePostgreSQL. Ibizwa ngokuba pgCenter (inqaku likaHabré - Izibalo ze-post-gress ngaphandle kwee-nerve kunye noxinzelelo).

Inqaku lentshayelelo encinci. Zeziphi iimeko abanazo abathengi bethu, abathengi bethu? Kukho uhlobo oluthile lwengozi olunxulumene nesiseko sedatha. Kwaye xa uvimba weenkcukacha sele ubuyiselwe, intloko yesebe okanye intloko yezophuhliso ifike ithi: “Bahlobo, kufuneka sibeke iliso kuluhlu lwedatha, kuba kukho into embi eyenzekileyo kwaye kufuneka sikuthintele oku kungenzeki kwixesha elizayo.” Kwaye apha iqala inkqubo enomdla yokukhetha inkqubo yokubeka iliso okanye ukulungelelanisa inkqubo ekhoyo yokubeka iliso ukuze ukwazi ukubeka iliso kwisiseko sedatha yakho-PostgreSQL, MySQL okanye abanye. Kwaye oogxa baqala ukucebisa: “Ndivile ukuba kukho isiseko sedatha esinje. Masiyisebenzise." Oogxa babo baqalisa ukuxambulisana. Kwaye ekugqibeleni kuye kwavela ukuba sikhetha uhlobo oluthile lwedatha, kodwa ukubeka iliso kwe-PostgreSQL kunikezelwa kuyo kunokuba kubi kwaye kufuneka sihlale songeze into. Thatha ezinye zokugcina ezivela kwi-GitHub, zihlanganise, zilungelelanise izikripthi, kwaye ngandlel 'ithile uzenze ngokwezifiso. Kwaye ekugqibeleni iphelela ekubeni uhlobo oluthile lomsebenzi wezandla.

Ngoko ke, kule ntetho ndiya kuzama ukukunika ulwazi malunga nendlela yokukhetha ukubeka iliso kungekuphela nje kwi-PostgreSQL, kodwa nakwi-database. Kwaye kukunika ulwazi oluya kukuvumela ukuba uzalise iliso lakho ukuze ufumane inzuzo ethile kuyo, ukuze ukwazi ukubeka iliso kwisiseko sedatha yakho ngenzuzo, ukwenzela ukuba uthintele ngokukhawuleza naziphi na iimeko ezingxamisekileyo ezizayo ezinokuvela.

Kwaye iingcamango eziya kuba kule ngxelo zinokuhlengahlengiswa ngokuthe ngqo kuyo nayiphi na i-database, ingaba yi-DBMS okanye i-noSQL. Ke ngoko, akukho PostgreSQL kuphela, kodwa kuya kubakho iindlela zokupheka ezininzi malunga nendlela yokwenza oku kwiPostgreSQL. Kuya kubakho imizekelo yemibuzo, imizekelo yamaqumrhu iPostgreSQL enawo yokubeka iliso. Kwaye ukuba i-DBMS yakho inezinto ezifanayo ezikuvumela ukuba uzibeke esweni, unokuziqhelanisa nazo, zongeze kwaye kuya kuba kuhle.

Andiyi kuba kwingxelo
Thetha ngendlela yokuhambisa kunye nokugcinwa kweemetrics. Andiyi kuthetha nantoni na malunga nokugqithiswa kwedatha kwaye ndiyibonise kumsebenzisi. Kwaye andiyi kuthetha nantoni na malunga nokulumkisa.
Kodwa njengoko ibali liqhubeka, ndiza kubonisa izikrini ezahlukeneyo zokubeka iliso ezikhoyo kwaye ngandlela-thile ndibagxeke. Kodwa nangona kunjalo, ndiza kuzama ukungazibizi iibrendi ukuze ndingenzi intengiso okanye intengiso echasayo kwezi mveliso. Ke ngoko, zonke iziganeko ziyenzeka ngokungacwangciswanga kwaye zishiywe kwintelekelelo yakho.

Okokuqala, makhe sibone ukuba yintoni na ukubeka iliso. Ukubeka iliso yinto ebaluleke kakhulu ukuba nayo. Wonke umntu uyayiqonda le nto. Kodwa kwangaxeshanye, ukubeka iliso akuhambelani nemveliso yeshishini kwaye ayichaphazeli ngokuthe ngqo inzuzo yenkampani, ngoko ke ixesha lihlala libekwe esweni ngokushiyekileyo. Ukuba sinexesha, ngoko siyabeka esweni; ukuba asinaxesha, kulungile, siya kuyibeka kumqolo kwaye ngenye imini siya kubuyela kule misebenzi.

Ke ngoko, kwizenzo zethu, xa sifika kubathengi, ukubeka iliso kuhlala kungaphelelanga kwaye akukho naziphi na izinto ezinomdla eziya kusinceda senze umsebenzi ongcono ngesiseko sedatha. Kwaye ke ngoko ukubeka esweni kuhlala kufuneka kugqitywe.

Oovimba beenkcukacha zizinto ezintsonkothileyo ezikwafuna ukujongwa, kuba oovimba beenkcukacha baluvimba wolwazi. Kwaye ulwazi lubaluleke kakhulu kwinkampani; ayinakulahleka nangayiphi na indlela. Kodwa kwangaxeshanye, ii-database zizinto ezintsonkothileyo kakhulu zesoftware. Ziquka inani elikhulu lamacandelo. Kwaye amaninzi kula macandelo kufuneka abekwe esweni.

Ukuba sithetha ngokuthe ngqo ngePostgreSQL, ngoko inokumelwa ngendlela yeskimu equkethe inani elikhulu lamacandelo. La macandelo ayasebenzisana. Kwaye kwangaxeshanye, i-PostgreSQL inento ebizwa ngokuba yi-Stats Collector subsystem, ekuvumela ukuba uqokelele izibalo malunga nokusebenza kwezi zikhokelo kwaye unikeze uhlobo oluthile lojongano kumlawuli okanye kumsebenzisi ukuze akwazi ukujonga ezi zibalo.

Ezi nkcukacha-manani zinikwe ngokohlobo oluthile lwemisebenzi neembono. Zisenokubizwa ngokuba ziitafile. Oko kukuthi, usebenzisa umthengi oqhelekileyo we-psql, unokudibanisa kwisiseko sedatha, wenze ukhetho kule misebenzi kunye neembono, kwaye ufumane amanani athile malunga nokusebenza kwe-PostgreSQL subsystems.

Unokongeza la manani kwinkqubo yakho yokubeka iliso oyithandayo, udwebe iigrafu, ungeze imisebenzi kwaye ufumane uhlalutyo kwixesha elide.

Kodwa kule ngxelo andizukuyigubungela ngokupheleleyo yonke le misebenzi, kuba ingathatha imini yonke. Ndiza kulungisa izinto ezimbini, ezintathu okanye ezine kwaye ndikuxelele indlela ezinceda ngayo ukwenza ukubeka iliso kube ngcono.

Kwaye ukuba sithetha ngokubeka iliso kwisiseko sedatha, yintoni ekufuneka ibekwe esweni? Okokuqala, kufuneka sibeke iliso ekufumanekeni, kuba i-database yinkonzo enikezela ukufikelela kwidatha kubathengi kwaye kufuneka sibeke iliso ekufumanekeni, kwaye sikwabonelela ngeempawu zayo ezisemgangathweni kunye nobuninzi.

Kwakhona kufuneka sibeke iliso kubathengi abaqhagamshela kwisiseko sedatha yethu, kuba banokuba ngabathengi abaqhelekileyo kunye nabaxhasi abayingozi abanokwenzakalisa isiseko sedatha. Kananjalo kufuneka zibekwe esweni kwaye zilandelelwe imisebenzi yazo.

Xa abathengi bexhuma kwisiseko sedatha, kuyacaca ukuba baqala ukusebenza ngedatha yethu, ngoko ke kufuneka sibeke iliso kwindlela abaxhasi basebenza ngayo kunye nedatha: ngeziphi iitafile, kunye nomlinganiselo omncinci, ngeziphi izalathi. Oko kukuthi, kufuneka sivavanye umthwalo womsebenzi owenziwe ngabathengi bethu.

Kodwa umthwalo womsebenzi uquka, ewe, zizicelo. Izicelo ziqhagamshela kwi-database, ukufikelela kwidatha usebenzisa imibuzo, ngoko kubalulekile ukuvavanya ukuba yeyiphi imibuzo esinayo kwisiseko sedatha, ukubeka esweni ukufaneleka kwazo, ukuba azibhalwanga ngokugwenxa, ukuba ezinye iinketho kufuneka zibhalwe kwakhona kwaye zenziwe ukuze zisebenze ngokukhawuleza. kunye nokusebenza ngcono.

Kwaye ekubeni sithetha ngesiseko sedatha, i-database ihlala iyinkqubo yangasemva. Iinkqubo zemvelaphi zinceda ukugcina ukusebenza kwedatha kumgangatho olungileyo, ngoko ke zifuna isixa esithile sezibonelelo ukuze zisebenze. Kwaye kwangaxeshanye, banokudibana nezibonelelo zokucela umxhasi, ngoko ke iinkqubo ezibawayo zingachaphazela ngokuthe ngqo ukusebenza kwezicelo zabaxhasi. Ke ngoko, kufuneka zibekwe esweni kwaye zilandelelwe ukuze kungabikho zigqwetha ngokweenkqubo zangasemva.

Kwaye konke oku ngokokubekwa kweliso kwisiseko sedatha kuhlala kumetriki wenkqubo. Kodwa ngokuqwalasela ukuba uninzi lweziseko zethu zifudukela emafini, i-metrics yenkqubo yomninimzi ihlala ingena ngasemva. Kodwa kuluhlu lweenkcukacha zisasebenza kwaye, ngokuqinisekileyo, kuyafuneka ukuba kubekwe iliso kwiimethrikhi zesistim.

Yonke into ilungile okanye incinci ngeemetriki zesistim, zonke iinkqubo zokubeka iliso zanamhlanje sele zixhasa ezi metrics, kodwa ngokubanzi, amanye amacandelo awakanelanga kwaye ezinye izinto kufuneka zongezwe. Ndiza kuzichukumisa, kuya kubakho izilayidi ezininzi malunga nazo.

Inqaku lokuqala lesicwangciso kukufikeleleka. Yintoni ukufikelela? Ukufumaneka ekuqondeni kwam ukukwazi kwesiseko kunxibelelwano lwenkonzo, oko kukuthi isiseko siphakanyisiwe, sona, njengenkonzo, samkela uxhulumaniso oluvela kubaxhasi. Kwaye oku kufikeleleka kunokuvavanywa ngeempawu ezithile. Kulunge kakhulu ukubonisa ezi mpawu kwiideshibhodi.

Wonke umntu uyazi ukuba zeziphi iidashbhodi. Oku kuxa uthe wajonga enye kwiscreen apho ulwazi oluyimfuneko lushwankathelwa. Kwaye unokugqiba ngokukhawuleza ukuba kukho ingxaki kwisiseko sedatha okanye hayi.
Ngokufanelekileyo, ubukho besiseko sedatha kunye nezinye iimpawu eziphambili kufuneka zihlale ziboniswa kwiideshibhodi ukuze olu lwazi lusondele kwaye luhlale lufumaneka kuwe. Ezinye iinkcukacha ezongezelelweyo esele zincede kuphando lweziganeko, xa kuphanda ezinye iimeko ezingxamisekileyo, sele kufuneka zibekwe kwiideshibhodi zesibini, okanye zifihlwe kwiikhonkco ze-drilldown ezikhokelela kwiinkqubo zokubeka iliso zeqela lesithathu.

Umzekelo wenkqubo enye eyaziwayo yokubeka iliso. Le yinkqubo yokubeka iliso epholileyo. Uqokelela idatha eninzi, kodwa ngokombono wam, unengcamango engaqhelekanga yeedeshibhodi. Kukho ikhonkco "lokudala ideshibhodi". Kodwa xa usenza ideshibhodi, wenza uluhlu lweekholamu ezimbini, uluhlu lweegrafu. Kwaye xa ufuna ukujonga into ethile, uqala ukucofa ngemouse, uskrola, ujonge itshathi oyifunayo. Kwaye oku kuthatha ixesha, o.t. akukho dashboards zinjalo. Kukho uluhlu lweetshathi kuphela.

Kufuneka wongeze ntoni kwezi dashbhodi? Unokuqala ngeempawu ezifana nexesha lokuphendula. I-PostgreSQL inombono we-pg_stat_statements. Ikhubaziwe ngokungagqibekanga, kodwa yenye yeemboniselo zenkqubo ebalulekileyo ekufuneka isoloko yenziwe kwaye isetyenziswa. Igcina ulwazi malunga nayo yonke imibuzo esebenzayo ethe yasetyenziswa kuvimba weenkcukacha.

Ngokufanelekileyo, sinokuqala kwinto yokuba sinokuthatha ixesha elipheleleyo lokuphunyezwa kwazo zonke izicelo kwaye silahlule ngenani lezicelo ngokusebenzisa le mihlaba ingentla. Kodwa lo ngumlinganiselo wobushushu esibhedlele. Sinokuqala ukusuka kweminye imimandla - ubuncinci bexesha lokwenziwa kombuzo, ubuninzi kunye ne-median. Kwaye sinokwakha ipesenti; I-PostgreSQL inemisebenzi ehambelana nale nto. Kwaye sinokufumana amanani athile abonisa ixesha lokuphendula kwidatha yethu yezicelo ezisele zigqityiwe, oko kukuthi, asikwenzi isicelo sobuxoki 'khetha i-1' kwaye sijonge ixesha lokuphendula, kodwa sihlalutya ixesha lokuphendula kwizicelo esele zigqityiwe kwaye udwebe. nokuba ngumfanekiso owahlukileyo, okanye sakha igrafu esekelwe kuyo.

Kwakhona kubalulekile ukubeka esweni inani leempazamo eziveliswa ngoku yinkqubo. Kwaye ngale nto ungasebenzisa imbono ye-pg_stat_database. Sigxila kumhlaba xact_rollback. Le ntsimi ayibonisi kuphela inani lee-rollbacks ezenzeka kwisiseko sedatha, kodwa kwakhona ithathela ingqalelo inani leempazamo. Xa sithetha ngokuthelekisa, sinokubonisa eli nani kwideshibhodi yethu kwaye sibone ukuba zingaphi iimpazamo esinazo ngoku. Ukuba kukho iimpazamo ezininzi, ke esi sizathu esihle sokujonga kwiilogi kwaye ubone ukuba ziphi na iimpazamo kwaye kutheni zenzeka, kwaye emva koko utyala imali kwaye uzisombulule.

Unokongeza into enjalo njengeTachometer. Ezi linani lentengiselwano ngesekhondi kunye nenani lezicelo ngesekhondi. Ngokuthetha ngokufanelekileyo, ungasebenzisa la manani njengomsebenzi wangoku wesiseko sakho sedatha kwaye ujonge ukuba kukho iincopho zezicelo, iincopho zentengiselwano, okanye, ngokuchaseneyo, ukuba i-database ilayishwe ngaphantsi ngenxa yokuba enye i-backend ayiphumelelanga. Kubalulekile ukuba sisoloko sijonga lo mzobo kwaye sikhumbule ukuba kwiprojekthi yethu olu hlobo lomsebenzi luqhelekile, kodwa amaxabiso angentla nangezantsi sele sele enengxaki kwaye engaqondakaliyo, oku kuthetha ukuba kufuneka sijonge ukuba kutheni la manani phezulu kakhulu.

Ukuze uqikelele inani leentengiselwano, sinokuphinda sibhekiselele kumbono we-pg_stat_database. Singongeza inani lokuzibophelela kunye nenani le-rollbacks kwaye sifumane inani leentengiselwano ngesekhondi.

Ngaba wonke umntu uyaqonda ukuba izicelo ezininzi zinokungena kwintengiselwano enye? Ke ngoko i-TPS kunye ne-QPS zahluke kancinci.

Inani lezicelo ngesekhondi ngalinye linokufumaneka kwi pg_stat_statements kwaye ngokulula kubalwe isixa sazo zonke izicelo ezigqityiweyo. Kucacile ukuba sithelekisa ixabiso langoku kunye nelidlulileyo, likhuphe, lifumane i-delta, kwaye ufumane ubuninzi.

Unokongeza iimethrikhi ezongezelelweyo ukuba uyanqwena, nto leyo ekwanceda ekuvavanyeni ubukho bedatabase yethu kwaye ubeke iliso ukuba bekukho naziphi na ixesha lokwehla.

Enye yezi metrics lixesha. Kodwa ixesha lokuphumla kwiPostgreSQL liyinkohliso. Ndiza kukuxelela ukuba kutheni. Xa i-PostgreSQL iqalile, ixesha lokuphumla liqala ukunika ingxelo. Kodwa ukuba ngaxa lithile, umzekelo, umsebenzi othile wawuqhuba ebusuku, umbulali we-OOM weza kwaye wayiphelisa ngenkani inkqubo yomntwana ye-PostgreSQL, ngoko ke kule meko i-PostgreSQL iphelisa uxhulumaniso lwabo bonke abaxumi, imisela kwakhona indawo yenkumbulo eyohluliweyo kwaye iqalise ukuchacha. indawo yokugqibela yokujonga. Kwaye ngelixa le ngxelo yokubuyisela kwi-checkpoint ihlala, i-database ayiwamkeli uxhulumaniso, oko kukuthi le meko inokuhlolwa njengexesha lokuphumla. Kodwa ikhawuntala ye-uptime ayiyi kuphinda isetyenziswe, kuba ithathela ingqalelo ixesha lokuqalisa lomphathi weposi ukusuka kumzuzu wokuqala. Ke ngoko, iimeko ezinjalo zinokutsitywa.

Kufuneka kwakhona ubeke iliso kwinani labasebenzi bevacuum. Ngaba wonke umntu uyazi ukuba yintoni i-autovacuum kwi-PostgreSQL? Le yindlela esezantsi enomdla kwi-PostgreSQL. Amanqaku amaninzi abhalwe ngaye, iingxelo ezininzi zenziwe. Kukho iingxoxo ezininzi malunga ne-vacuum kunye nendlela ekufuneka isebenze ngayo. Abaninzi bakugqala njengobubi obuyimfuneko. Kodwa kunjalo. Olu luhlobo lwe-analogue yomqokeleli wenkunkuma ecoca iinguqulelo eziphelelwe lixesha zemigca engadingekiyo nayiphi na intengiselwano kwaye ikhulule indawo kwiitheyibhile kunye nezalathisi zemigca emitsha.

Kutheni kufuneka uyibeke iliso? Kuba ivacuum ngamanye amaxesha iba buhlungu kakhulu. Idla isixa esikhulu sezibonelelo kwaye izicelo zabaxumi ziqala ukubandezeleka ngenxa yoko.

Kwaye kufuneka ibekwe iliso nge- pg_stat_activity view, endiya kuthetha ngayo kwicandelo elilandelayo. Lo mbono ubonisa umsebenzi wangoku kwisiseko sedatha. Kwaye ngalo msebenzi sinokulandelela inani leevacuum ezisebenzayo ngoku. Siyakwazi ukulandelela i-vacuums kwaye sibone ukuba sigqithise umda, ngoko esi sisizathu sokujonga kwiisetingi ze-PostgreSQL kwaye ngandlela-thile sandise ukusebenza kwe-vacuum.

Enye into malunga nePostgreSQL kukuba iPostgreSQL igula kakhulu kuthengiselwano olude. Ngokukodwa kwiintengiselwano ezihlala ixesha elide kwaye zingenzi nto. Le yinto ebizwa ngokuba yi-stat-idle-in-transaction. Intengiselwano enjalo ibamba izitshixo kwaye ithintele ivacuum ekusebenzeni. Kwaye ngenxa yoko, iitafile zidumba kwaye zanda ngobukhulu. Kwaye imibuzo esebenza ngezi tafile iqala ukusebenza kancinci, kuba kufuneka uhlakulele zonke iinguqulelo zakudala zemigca ukusuka kwimemori ukuya kwidiski kunye nomva. Ke ngoko, ixesha, ixesha leentengiselwano ezinde, ezona zicelo zevacuum zide nazo kufuneka zibekwe esweni. Kwaye ukuba sibona ezinye iinkqubo eziye zasebenza ixesha elide, sele zingaphezulu kwe-10-20-30 imizuzu yomthwalo we-OLTP, ngoko kufuneka sinikele ingqalelo kuzo kwaye siziphelise ngamandla, okanye sisebenzise isicelo ukuze azibizwa kwaye azixhonywanga ixesha elide. Kumsebenzi wohlalutyo, imizuzu eyi-10-20 ukuya kwengama-30 iqhelekile; kukwakho nemide.

Okulandelayo sinokukhetha kunye nabaxhasi abaxhunyiwe. Xa sele senze ideshibhodi kwaye sithumele iimetriki eziphambili zokufumaneka kuyo, singongeza ulwazi olongezelelweyo malunga nabathengi abaxhunyiwe apho.

Ulwazi malunga nabaxhasi abaxhunyiwe lubalulekile kuba, kwimbono yePostgreSQL, abathengi bahluke. Kukho abathengi abalungileyo kwaye kukho abathengi abangalunganga.

Umzekelo olula. Ngomxhasi ndiyasiqonda isicelo. Isicelo siqhagamshele kwisiseko sedatha kwaye ngokukhawuleza siqala ukuthumela izicelo zaso apho, iinkqubo zesiseko sedatha kwaye sizenze, kwaye zibuyisela iziphumo kumxhasi. Aba ngabaxhasi abalungileyo nabachanekileyo.

Kukho iimeko xa umxhasi eqhagamshele, ubambe uxhulumaniso, kodwa akenzi nto. Ikwimo yokungenzi nto.

Kodwa kukho abaxhasi ababi. Ngokomzekelo, umthengi ofanayo uqhagamshelwe, wavula ukuthengiselana, wenza into ethile kwisiseko sedatha kwaye wangena kwikhowudi, umzekelo, ukufikelela kumthombo wangaphandle okanye ukucubungula idatha efunyenweyo apho. Kodwa akazange ayivale intengiselwano. Kwaye i-transaction ixhomekeke kwi-database kwaye ibanjwe kwi-lock kumgca. Le yimeko embi. Kwaye ukuba ngequbuliso isicelo kwindawo ethile ngaphakathi ngokwaso asiphumelelanga ngaphandle, intengiselwano inokuhlala ivulekile ixesha elide. Kwaye oku kuchaphazela ngokuthe ngqo ukusebenza kwePostgreSQL. I-PostgreSQL iyakucotha. Ke ngoko, kubalulekile ukulandelela abathengi abanjalo ngexesha elifanelekileyo kwaye uphelise umsebenzi wabo ngamandla. Kwaye kufuneka ukhulise isicelo sakho ukuze iimeko ezinjalo zingenzeki.

Abanye abathengi abangalunganga balinde abathengi. Kodwa baba mbi ngenxa yeemeko. Ngokomzekelo, ukuthengiselana okulula okungenzi lutho: kunokuvula ukuthengiselana, ukuthatha izitshixo kwimigca ethile, ngoko kwindawo ethile kwikhowudi iya kuphumelela, ishiya ukuthengiselana okujingayo. Omnye umxhasi uya kuza kwaye acele idatha efanayo, kodwa uya kudibana nesitshixo, kuba loo ntengiselwano ejingayo sele ibambe izitshixo kwimiqolo efunekayo. Kwaye intengiselwano yesibini iya kuxhoma ilindele ukuba intengiselwano yokuqala igqitywe okanye iyivale ngenkani ngumlawuli. Ngoko ke, iintengiselwano ezilindileyo zinokuqokelela kwaye zigcwalise umda woqhagamshelwano lwesiseko sedatha. Kwaye xa umda ugcwele, isicelo asikwazi ukusebenza kunye nesiseko sedatha. Oku sele kuyimeko engxamisekileyo yeprojekthi. Ke ngoko, abathengi abangalunganga kufuneka balandelelwe kwaye baphendulwe ngexesha elifanelekileyo.

Omnye umzekelo wokubeka iliso. Kwaye sele kukho ideshbhodi efanelekileyo apha. Kukho ulwazi kunxibelelwano olungentla. Uxhulumaniso lweDB - iziqwenga ezi-8. Kwaye konke. Asinalo ulwazi malunga nokuba ngabaphi abathengi abasebenzayo, abaxumi abangenzi nto, abangenzi nto. Akukho lwazi malunga neentengiselwano ezilindileyo kunye noqhagamshelo olulindileyo, oko kukuthi lo ngumfanekiso obonisa inani loqhagamshelo kwaye yiloo nto. Kwaye ke uzicingele.

Ngokufanelekileyo, ukongeza olu lwazi ekubekeni iliso, kufuneka ufikelele kwi-pg_stat_activity yokujonga inkqubo. Ukuba uchitha ixesha elininzi kwi-PostgreSQL, ke lo ngumbono omhle kakhulu omele ube ngumhlobo wakho, kuba ubonisa umsebenzi wangoku kwi-PostgreSQL, oko kukuthi kwenzekani kuyo. Kwinkqubo nganye kukho umgca owahlukileyo obonisa ulwazi malunga nale nkqubo: kwi-host host uxhulumaniso lwenziwe, phantsi kowuphi umsebenzisi, phantsi kweliphi igama, xa ukuthengiswa kwaqalwa, sesiphi isicelo esisebenzayo ngoku, sesiphi isicelo esagqitywa ukwenziwa. Kwaye, ngokufanelekileyo, sinokuvavanya imeko yomthengi sisebenzisa indawo yezibalo. Ukuthetha ngokuthelekisayo, singenza amaqela ngokwalo mmandla kwaye sifumane ezo zibalo ezikhoyo ngoku kwisiseko sedatha kunye nenani loqhagamshelwano olunale stat kwisiseko sedatha. Kwaye sinokuthumela amanani asele efunyenwe kuhlolo lwethu kwaye sizobe iigrafu ezisekelwe kuzo.
Kukwabalulekile ukuvavanya ixesha lentengiselwano. Sele nditshilo ukuba kubalulekile ukuvavanya ixesha le-vacuums, kodwa ukuthengiselana kuhlolwa ngendlela efanayo. Kukho iindawo ze-xact_start kunye ne-query_start. Bona, xa bethetha, babonisa ixesha lokuqala lokuthengiselana kunye nexesha lokuqala lesicelo. Sithatha ngoku() umsebenzi, obonisa isitampu sexesha sangoku, kwaye sithabatha itransekshini kunye nesicelo sesitampu sexesha. Kwaye sifumana ixesha lentengiselwano, ixesha lesicelo.

Ukuba sibona iintengiselwano ezinde, kufuneka sizigqibe. Ngomthwalo we-OLTP, ukuthengiselana okude sele kungaphezulu kwe-1-2-3 imizuzu. Ngomthwalo womsebenzi we-OLAP, ukuthengiselana okude kuqhelekile, kodwa ukuba kuthatha iiyure ezingaphezu kweeyure ezimbini ukugqiba, ke oku kwakhona kubonisa ukuba sine-skew kwindawo ethile.

Nje ukuba abathengi baqhagamshele kwisiseko sedatha, baqala ukusebenza ngedatha yethu. Bafikelela kwiitafile, bafikelela kwizalathisi ukufumana idatha kwitafile. Kwaye kubalulekile ukuvavanya indlela abathengi abasebenzisana ngayo nale datha.

Oku kuyimfuneko ukuze sivavanye umthwalo wethu womsebenzi kwaye siqonde ukuba zeziphi iitheyibhile “zezona zishushu” kuthi. Ngokomzekelo, oku kuyadingeka kwiimeko apho sifuna ukubeka iitafile "ezishushu" kuhlobo oluthile lokugcinwa kwe-SSD ngokukhawuleza. Ngokomzekelo, ezinye iitafile ze-archive esingakhange sizisebenzise ixesha elide zingafuduselwa kuhlobo oluthile "lwendawo ebandayo", kwii-SATA drives kwaye zivumele ukuba zihlale apho, ziya kufumaneka njengoko zifuneka.

Oku kukwaluncedo ekuboneni izinto ezingaqhelekanga emva kokukhutshwa kunye nokuthunyelwa. Masithi iprojekthi ikhuphe into entsha. Ngokomzekelo, songeze umsebenzi omtsha wokusebenza kunye nedathabheyisi. Kwaye ukuba sicwangcisa iigrafu zokusetyenziswa kwetafile, sinokubona lula ezi mpazamo kwezi grafu. Umzekelo, ukuhlaziya ukugqabhuka okanye ukucima ukugqabhuka. Kuya kubonakala kakhulu.

Ungakwazi kwakhona ukubona izinto ezingaqhelekanga kwizibalo "ezidadayo". Ingaba ithetha ntoni? I-PostgreSQL inomcwangcisi wombuzo onamandla kakhulu kwaye olungileyo kakhulu. Kwaye abaphuhlisi banikela ixesha elininzi ekuphuhlisweni kwayo. Usebenza njani? Ukuze wenze izicwangciso ezilungileyo, i-PostgreSQL iqokelela izibalo malunga nokuhanjiswa kwedatha kwiitheyibhile ngexesha elithile kunye nexesha elithile. Lawa awona maxabiso aqhelekileyo: inani lamaxabiso akhethekileyo, ulwazi malunga ne-NULL kwitheyibhile, ulwazi oluninzi.

Ngokusekwe kwezi nkcukacha-manani, umcwangcisi wenza imibuzo emininzi, akhethe eyona ilungileyo, kwaye asebenzise olu cwangciso lombuzo ukwenza umbuzo ngokwawo kunye nokubuyisela idatha.

Kwaye kwenzeka ukuba izibalo "zihamba". Umgangatho kunye nedatha yobuninzi ngandlela-thile itshintshile kwitheyibhile, kodwa izibalo azizange ziqokelelwe. Kwaye izicwangciso ezenziweyo zisenokungabi zezona zilungileyo. Kwaye ukuba izicwangciso zethu zithe zajika zibe kumgangatho ophantsi ngokusekwe kuhlolo oluqokelelweyo, olusekwe kwiitheyibhile, siya kukwazi ukubona ezi ziphazamiso. Ngokomzekelo, kwindawo ethile idatha yatshintshile ngokomgangatho kwaye endaweni yesalathisi, ukudlula okulandelelanayo kwitheyibhile kwaqala ukusetyenziswa, okt. ukuba umbuzo ufuna ukubuyisela kuphela imiqolo eyi-100 (kukho umda we-100), uphendlo olupheleleyo luya kwenziwa kulo mbuzo. Kwaye oku kuhlala kunempembelelo embi kakhulu ekusebenzeni.

Kwaye sinokukubona oku ekubekeni iliso. Kwaye sele ujonge lo mbuzo, sebenzisa i-icacis yayo, qokelela izibalo, yakha isalathiso esitsha esongezelelweyo. Kwaye sele uphendule kule ngxaki. Yiyo loo nto ibalulekile.

Omnye umzekelo wokubeka iliso. Ndicinga ukuba abantu abaninzi baye bamazi kuba ethandwa kakhulu. Ngubani oyisebenzisayo kwiiprojekthi zabo Prometheus? Ngubani osebenzisa le mveliso ngokubambisana nePrometheus? Inyani kukuba kwindawo yokugcina esemgangathweni yolu hlolo kukho ideshibhodi yokusebenza nePostgreSQL - postgres_exporter Prometheus. Kodwa kukho inkcukacha enye embi.

Kukho iigrafu ezininzi. Kwaye ii-bytes ziboniswa njengobunye, okt kukho iigrafu ezi-5. Ezi yiFaka idatha, Hlaziya idatha, Cima idatha, Landa idatha kwaye Buyisela idatha. Umlinganiselo weyunithi ziibhayithi. Kodwa into yokuba izibalo kwi-PostgreSQL zibuyisela idatha kwi-tuple (imigca). Kwaye, ngokufanelekileyo, ezi grafu ziyindlela entle kakhulu yokujongela phantsi umthwalo wakho wokusebenza amaxesha amaninzi, amaxesha angamashumi, kuba i-tuple ayiyo-byte, i-tuple ngumtya, zininzi ii-bytes kwaye zihlala zinobude obuguquguqukayo. Oko kukuthi, ukubala umthwalo womsebenzi kwii-bytes usebenzisa ii-tuples ngumsebenzi ongenakwenzeka okanye unzima kakhulu. Ngoko ke, xa usebenzisa ideshibhodi okanye ukubeka iliso eyakhelwe-ngaphakathi, kusoloko kubalulekile ukuqonda ukuba isebenza ngokuchanekileyo kwaye ikubuyisela idatha ehlolwe ngokuchanekileyo.

Uzifumana njani izibalo kwezi theyibhile? Ngale njongo, iPostgreSQL inosapho oluthile lweembono. Kwaye imbono ephambili pg_stat_iitheyibhile_zabasebenzisi. User_tables - oku kuthetha iitafile ezenziwe egameni lomsebenzisi. Ngokwahlukileyo, kukho iimbono zenkqubo ezisetyenziswa yiPostgreSQL ngokwayo. Kwaye kukho itafile yesishwankathelo Alltables, ebandakanya zombini inkqubo kunye nabasebenzisi. Ungaqala kuyo nayiphi na kuzo othanda kakhulu.

Ukusebenzisa le mihlaba ingasentla ungaqikelela inani lofakelo, uhlaziyo kunye nocimo. Umzekelo wedeshibhodi endiyisebenzisileyo usebenzisa le mimandla ukuvavanya iimpawu zomthwalo womsebenzi. Ngoko ke, sinokwakhela phezu kwabo. Kodwa kufanelekile ukukhumbula ukuba ezi zii-tuples, hayi ii-byte, ngoko ke asinakuyenza ngee-bytes.

Ngokusekelwe kule datha, sinokwakha okubizwa ngokuba yi-TopN tables. Umzekelo, i-Top-5, i-Top-10. Kwaye ungalandelela ezo tafile zishushu ziphinda zisetyenziswe ngakumbi kunezinye. Umzekelo, iitafile ezi-5 "ezishushu" zokufakwa. Kwaye sisebenzisa ezi tafile ze-TopN sivavanya umthwalo wethu kwaye sinokuvavanya ukugqabhuka komsebenzi emva kwako nakuphi na ukukhutshwa, uhlaziyo, kunye nokuthunyelwa.

Kukwabalulekile ukuvavanya ubungakanani betafile, kuba ngamanye amaxesha abaphuhlisi bakhupha into entsha, kwaye iitafile zethu ziqala ukudumba kubungakanani bazo obukhulu, kuba bagqibe kwelokuba bongeze inani elongezelelweyo ledatha, kodwa abazange baqikelele ukuba oku kuya kwenzeka njani. ichaphazela ubungakanani bedatabase. Iimeko ezinjalo nazo ziyasimangalisa.

Kwaye ngoku umbuzo omncinci kuwe. Nguwuphi umbuzo ovelayo xa ubona umthwalo kumncedisi wakho wedatha? Ngowuphi umbuzo olandelayo onawo?

Kodwa eneneni umbuzo uvela ngolu hlobo lulandelayo. Zeziphi izicelo ezibangelwa ngumthwalo? Oko kukuthi, akunomdla ukujonga iinkqubo ezibangelwa ngumthwalo. Kucacile ukuba umninimzi unesiseko sedatha, ngoko i-database isebenza apho kwaye kuyacaca ukuba kuphela i-databases iya kuchithwa apho. Ukuba sivula Phezulu, siya kubona kukho uluhlu lweenkqubo kwi-PostgreSQL ezenza into ethile. Ayizocaca kwaPhezulu into abayenzayo.

Ngokufanelekileyo, kufuneka ufumane loo mibuzo ibangela owona mthwalo uphakamileyo, kuba imibuzo yokulungisa, njengomthetho, inika inzuzo engaphezulu kunokulungisa iPostgreSQL okanye uqwalaselo lwenkqubo yokusebenza, okanye nokulungisa ihardware. Ngokoqikelelo lwam, oku malunga ne-80-85-90%. Kwaye oku kwenziwa ngokukhawuleza okukhulu. Ukukhawuleza ukulungisa isicelo kunokulungisa ukucwangciswa, cwangcisa ukuqaliswa kwakhona, ngakumbi ukuba i-database ayikwazi ukuqaliswa kwakhona, okanye yongeza i-hardware. Kulula ukubhala umbuzo kwenye indawo okanye wongeze isalathiso ukuze ufumane iziphumo ezingcono kulo mbuzo.

Ngako oko, kuyimfuneko ukubeka esweni izicelo kunye nokwanela kwazo. Masithathe omnye umzekelo wokubeka iliso. Kwaye apha, kwakhona, kubonakala kukho ukubeka iliso okugqwesileyo. Kukho ulwazi malunga nokuphindaphinda, kukho ulwazi malunga ne-throughput, blocking, ukusetyenziswa kwezibonelelo. Yonke into ilungile, kodwa akukho lwazi kwizicelo. Akukacaci ukuba yeyiphi imibuzo ekhoyo kwisiseko sedatha yethu, iqhuba ixesha elingakanani, mingaphi le mibuzo. Sisoloko sifuna ukuba olu lwazi kuhlolo lwethu.

Kwaye ukufumana olu lwazi sinokusebenzisa i-pg_stat_statements modyuli. Ngokusekelwe kuyo, unokwakha iindidi zeegrafu. Umzekelo, ungafumana ulwazi kweyona mibuzo ixhaphakileyo, oko kukuthi, kule mibuzo isenziwa rhoqo. Ewe, emva kokuthunyelwa kuluncedo kakhulu ukuyijonga kwaye uqonde ukuba kukho naluphi na utyando kwizicelo.

Uyakwazi ukubeka esweni eyona mibuzo inde, oko kukuthi, loo mibuzo ithatha ixesha elide ukuyigqiba. Basebenza kwiprosesa, badla i-I/O. Sinokuyivavanya oku kusetyenziswa iindawo total_time, mean_time, blk_write_time kunye blk_read_time.

Sinokuvavanya kwaye sibeke iliso ezona zicelo zinzima ngokwemigaqo yokusetyenziswa kwezixhobo, ezo zifundeka kwidiski, ezisebenza ngememori, okanye, ngokuchaseneyo, zenze uhlobo oluthile lomthwalo wokubhala.

Sinokuzivavanya ezona zicelo zesisa. Le yimibuzo ebuyisela inani elikhulu lemiqolo. Umzekelo, oku kunokuba sisicelo esithile apho balibale ukumisela umda. Kwaye ibuyisela yonke imixholo yetafile okanye umbuzo ngapha kweetafile ezibuziweyo.

Kwaye unokujonga kwakhona imibuzo esebenzisa iifayile zexeshana okanye iitafile zexeshana.

Kwaye siseneenkqubo zangasemva. Iinkqubo zemvelaphi ngokuyintloko ziindawo zokukhangela okanye zikwabizwa ngokuba ziindawo zokukhangela, ezi zi-autovacuum kunye nokuphindaphinda.

Omnye umzekelo wokubeka iliso. Kukho ithebhu yoLondolozo ekhohlo, yiya kuyo kwaye unethemba lokubona into eluncedo. Kodwa nali kuphela ixesha lokusebenza kwe-vacuum kunye nokuqokelela izibalo, akukho okunye. Olu lulwazi olubi kakhulu, ngoko kufuneka sisoloko sinolwazi malunga nendlela iinkqubo zemvelaphi esebenza ngayo kwisiseko sedatha yethu kwaye ingaba kukho naziphi na iingxaki kumsebenzi wabo.

Xa sijonga iindawo zokukhangela, kufuneka sikhumbule ukuba iindawo zokukhangela zigungxula amaphepha amdaka ukusuka kwindawo yememori eyotyiweyo ukuya kwidiski, emva koko yenze indawo yokukhangela. Kwaye le ndawo yokukhangela inokuthi isetyenziswe njengendawo yokubuyisela ukuba i-PostgreSQL yapheliswa ngequbuliso kwimeko kaxakeka.

Ngokufanelekileyo, ukuze uhlambe onke amaphepha "angcolileyo" kwidiski, kufuneka wenze inani elithile lokubhala. Kwaye, njengomthetho, kwiinkqubo ezineenkumbulo ezininzi, oku kuninzi. Kwaye ukuba sijonga iindawo zokukhangela rhoqo kwithuba elifutshane, ukusebenza kwediski kuya kuhla kakhulu. Kwaye izicelo zabaxumi ziya kubandezeleka ngenxa yokunqongophala kwezixhobo. Baya kukhuphisana ngezibonelelo kunye nokungabikho kwemveliso.

Ngokufanelekileyo, nge-pg_stat_bgwriter sisebenzisa iindawo ezikhankanyiweyo sinokubeka iliso inani leendawo zokukhangela ezenzekayo. Kwaye ukuba sineendawo ezininzi zokuhlola ngexesha elithile (kwimizuzu eyi-10-15-20, kwisiqingatha seyure), umzekelo, 3-4-5, ngoko oku kunokuba yingxaki. Kwaye sele kufuneka ujonge kwisiseko sedatha, jonga kuqwalaselo, yintoni ebangela ubuninzi beendawo zokukhangela. Mhlawumbi kukho uhlobo oluthile lokurekhoda okukhulu okuqhubekayo. Singakwazi kakade ukuvavanya umthwalo womsebenzi, kuba sele songeze iigrafu zomsebenzi. Sisenokuthi sitshintshe iiparamitha ze-checkpoint kwaye siqinisekise ukuba azichaphazeli kakhulu ukusebenza kombuzo.

Ndibuyela kwi-autovacuum kwakhona kuba iyinto enjalo, njengoko benditshilo, enokudibanisa ngokulula zombini idiski kunye nokusebenza kombuzo, ke kusoloko kubalulekile ukuqikelela isixa se-autovacuum.

Inani labasebenzi be-autovacuum kwiziko ledatha lilinganiselwe. Ngokungagqibekanga, kukho ezintathu zazo, ngoko ukuba sihlala sinabasebenzi abathathu abasebenza kwisiseko sedatha, oku kuthetha ukuba i-autovacuum yethu ayicwangciswanga, kufuneka siphakamise imida, sihlaziye izicwangciso ze-autovacuum kwaye singene kuqwalaselo.
Kubalulekile ukuvavanya ukuba ngabaphi na abasebenzi bevacuum esinabo. Nokuba yaziswa kumsebenzisi, i-DBA yeza kwaye yazisa ngesandla uhlobo oluthile lwe-vacuum, kwaye oku kudala umthwalo. Sinohlobo oluthile lwengxaki. Okanye eli linani leevacuums ezikhulula ikhawunta yetransekshini. Kwezinye iinguqulelo zePostgreSQL ezi ziivacuum ezinzima kakhulu. Kwaye banokudibanisa ngokulula ukusebenza kuba bayifunda yonke itafile, baskena zonke iibhloko ezikule theyibhile.

Kwaye, ngokuqinisekileyo, ixesha le-vacuums. Ukuba sinee-vacuums ezihlala ixesha elide ezisebenza ixesha elide, ngoko oku kuthetha ukuba kufuneka siphinde sihlawule ingqalelo kwi-vacuum configuration kwaye mhlawumbi siphinde siphinde sihlolisise izicwangciso zayo. Ngenxa yokuba imeko inokuvela xa i-vacuum isebenza etafileni ixesha elide (iiyure ezingama-3-4), kodwa ngexesha le-vacuum yayisebenza, inani elikhulu lemiqolo efile likwazi ukuqokelela etafileni kwakhona. Kwaye kamsinya nje ukuba ivacuum igqityiwe, kufuneka aphinde acoce le tafile kwakhona. Kwaye sifika kwimeko - i-vacuum engapheliyo. Kwaye kule meko, i-vacuum ayihambisani nomsebenzi wayo, kwaye iitheyibhile ngokuthe ngcembe ziqala ukuvuvukala ngobukhulu, nangona umthamo wedatha eluncedo kuwo uhlala ufana. Ngoko ke, ngexesha le-vacuums emide, sihlala sijonga ukucwangciswa kwaye sizame ukuyiphucula, kodwa ngexesha elifanayo ukwenzela ukuba ukusebenza kwezicelo zabaxhasi kungahlupheki.

Kule mihla akukho fakelo lwePostgreSQL olungenakuphindwa kokusasazwa. Ukuphindaphinda yinkqubo yokuhambisa idatha ukusuka kwinkosi ukuya kwi-replica.

Ukuphindaphinda kwi-PostgreSQL kwenziwa ngelog yentengiselwano. Iwizard yenza ilog yetransekshini. Ilog yentengiselwano ihamba phezu koqhagamshelwano lwenethiwekhi ukuya kwi-replica, kwaye emva koko iveliswe kwi-replica. Ilula.

Ngokuhambelanayo, i pg_stat_replication imboniselo isetyenziselwa ukujonga uphindaphindo lag. Kodwa akusiyo yonke into elula kunye naye. Kuguqulelo lwe-10, imboniselo iye yatshintsha kaninzi. Okokuqala, amanye amabala athiywe ngokutsha. Kwaye ezinye iindawo zongeziwe. Kuguqulelo lwe-10, iindawo ziye zavela ezikuvumela ukuba uqikelele uphindaphindo lag kwimizuzwana. Ikhululekile kakhulu. Phambi kwenguqulo ye-10, bekunokwenzeka ukuqikelela uphindaphindo kwi-bytes. Olu khetho luhleli kuguqulelo lwe-10, o.k.t. ungakhetha eyona nto ikulungeleyo - qikelela i-lag kwi-bytes okanye uqikelele ukushiyeka ngemizuzwana. Abantu abaninzi benza zombini.

Kodwa nangona kunjalo, ukuze uvavanye i-replication lag, kufuneka wazi indawo yelogi kwintengiselwano. Kwaye ezi zithuba zelog yentengiselwano zikwindawo pg_stat_replication. Ngokuthetha ngokuthelekisa, sinokuthatha amanqaku amabini kwilog yentengiselwano sisebenzisa pg_xlog_location_diff () umsebenzi. Bala i-delta phakathi kwabo kwaye ufumane i-replication lag in bytes. Iluncedo kakhulu kwaye ilula.

Kuguqulelo lwe-10, lo msebenzi uthiywe kwakhona ukuba pg_wal_lsn_diff(). Ngokubanzi, kuyo yonke imisebenzi, iimbono, kunye nezinto eziluncedo apho igama elithi "xlog" livele khona, lithatyathelwe indawo ngexabiso elithi "wal". Oku kusebenza kuzo zombini iimboniselo kunye nemisebenzi. Olu lutshintsho olunjalo.

Ukongeza, kwinguqulo ye-10, imigca yongezwa ebonisa ngokuthe ngqo i-lag. Ezi zizinto zokubhala, i-flush lag, i-replay lag. Oko kukuthi, kubalulekile ukubeka esweni ezi zinto. Ukuba sibona ukuba sine-replication lag, ngoko kufuneka siphande ukuba kutheni ibonakala, ivela phi kwaye silungise ingxaki.

Phantse yonke into ime ngolungelelwano ngeemetriki zesistim. Xa naluphi na uhlolo luqala, luqala ngeemetriki zesixokelelwano. Oku kukulahla abaqhubekekisi, inkumbulo, utshintsho, inethiwekhi kunye nediski. Nangona kunjalo, iiparameters ezininzi azikho ngokungagqibekanga.

Ukuba yonke into ilungile kunye nenkqubo yokuphinda isetyenziswe, ngoko kukho iingxaki ngokuhlaziywa kwediski. Njengomthetho, abaphuhlisi bokubeka iliso bongeza ulwazi malunga nokuphuma. Inokuba kwii-iops okanye ii-bytes. Kodwa bayalibala malunga ne-latency kunye nokusetyenziswa kwezixhobo zediski. Ezi zezona parameters zibaluleke kakhulu ezisivumela ukuba sivavanye ukuba zilayishwe njani iidiski zethu kwaye zicotha kangakanani na. Ukuba sine-latency ephezulu, ke oku kuthetha ukuba kukho iingxaki kwiidiski. Ukuba sinosetyenziso oluphezulu, oko kuthetha ukuba iidiski azijongani. Ezi ziimpawu ezingcono kune-output.

Ngaphaya koko, ezi nkcukacha-manani zinokufunyanwa kwi/kwinkqubo yefayile yeproc, njengoko isenziwa kuhlaziyo lwabasebenzi. Andazi ukuba kutheni le ngcaciso ingadityaniswanga ekubekeni iliso. Kodwa nangona kunjalo, kubalulekile ukuba nale nto kuhlolo lwakho.

Kukwasebenza okufanayo kwinethiwekhi yojongano. Kukho ulwazi malunga nokugqithiswa kwenethiwekhi kwiipakethi, kwii-bytes, kodwa nangona kunjalo akukho lwazi malunga ne-latency kwaye akukho lwazi malunga nokusetyenziswa, nangona oku kuluncedo.

Nakuphi na ukubeka esweni kunemiqobo. Kwaye kungakhathaliseki ukuba luhlobo luni lokubeka iliso oluthathayo, aluyi kuhlangabezana nemilinganiselo ethile. Kodwa nangona kunjalo, bayaphuhlisa, izinto ezintsha kunye nezinto ezintsha zongezwa, ke khetha into kwaye uyigqibe.

Kwaye ukuze ugqibezele, kufuneka uhlale unombono wokuba izibalo ezinikiweyo zithetha ntoni kwaye ungazisebenzisa njani ukusombulula iingxaki.

Kwaye amanqaku ambalwa abalulekileyo:

• Kufuneka uhlale ubeka esweni ukufumaneka kwaye ube needashboards ukuze ukwazi ukuvavanya ngokukhawuleza ukuba yonke into ilungile ngesiseko sedatha.
• Uhlala ufuna ukuba nombono wokuba yintoni na abathengi abasebenza kunye nedatha yakho ukuze ukhuphe abathengi ababi kwaye ubadubule.
• Kubalulekile ukuvavanya indlela aba bathengi basebenza ngayo ngedatha. Kuya kufuneka ube nombono malunga nomsebenzi wakho.
• Kubalulekile ukuvavanya indlela lo msebenzi owenziwe ngayo, ngoncedo lweyiphi imibuzo. Unokuyiphonononga imibuzo, unokuyiphucula, uyilinganise kwakhona, wakhele izalathisi. Ibaluleke kakhulu.
• Iinkqubo zemvelaphi zinokuchaphazela kakubi izicelo zabaxhasi, ngoko ke kubalulekile ukujonga ukuba abasebenzisi izixhobo ezininzi kakhulu.
• Iimetriki zesistim zikuvumela ukuba wenze izicwangciso zokwandisa kunye nokunyusa umthamo weeseva zakho, ngoko ke kubalulekile ukuzilandela nokuzivavanya nazo.

Ukuba unomdla kwesi sihloko, ngoko unokulandela ezi khonkco.
http://bit.do/stats_collector - la ngamaxwebhu asemthethweni avela kumqokeleli weenkcukacha-manani. Kukho inkcazo yazo zonke iimbono zamanani kunye nenkcazo yazo zonke iindawo. Unako ukuzifunda, uziqonde kwaye uzihlalutye. Kwaye ngokusekwe kuzo, yakha iigrafu zakho kwaye uzongeze kuhlolo lwakho.

Umzekelo wezicelo:
http://bit.do/dataegret_sql
http://bit.do/lesovsky_sql

Lo nguvimba wethu weshishini kunye neyam. Ziqulethe imizekelo yemibuzo. Akukho mibuzo evela kukhetho* kuthotho apho. Sele kukho imibuzo esele yenziwe ngokudibanisa, usebenzisa imisebenzi enomdla ekuvumela ukuba ujike amanani akrwada abe afundeka, amaxabiso afanelekileyo, okt ezi ziibytes, ixesha. Unokuzichola, uzijonge, uzihlalutye, zongeze ekubekeni iliso kwakho, wakhe iliso lakho ngokusekelwe kuzo.

Imibuzo yakho

Umbuzo: Uthe awuzuzibhengeza iibrendi, kodwa ndisafuna ukwazi - luhlobo luni lwedeshbhodi oyisebenzisayo kwiiprojekthi zakho?
Impendulo: Iyahluka. Kwenzeka ukuba sifike kumthengi kwaye sele eneyakhe yokubeka iliso. Kwaye sicebisa umthengi malunga nokuba yintoni ekufuneka yongezwe ekubekeni iliso kwabo. Imeko embi kakhulu nguZabbix. Kuba ayinaso isakhono sokwakha iigrafu zeTopN. Thina ngokwethu sisebenzisa Okmeter, kuba sasibonisana naba bafana esweni. Babeke iliso kwi-PostgreSQL ngokusekelwe kwiinkcukacha zethu zobugcisa. Ndibhala eyam iprojekthi yesilwanyana, eqokelela idatha nge-Prometheus kwaye ndiyinike kuyo IGrafana. Umsebenzi wam kukudala owam umthumeli ngaphandle ePrometheus emva koko ndinike yonke into eGrafana.

Umbuzo: Ngaba kukho naziphi na ii-analogue zeengxelo ze-AWR okanye... ukuhlanganiswa? Ngaba uyayazi into enje?
Impendulo: Ewe, ndiyayazi ukuba yintoni i-AWR, yinto epholileyo. Okwangoku kukho iintlobo ngeentlobo zeebhayisikile eziphumeza malunga nale modeli ilandelayo. Ngexesha elithile lexesha, ezinye iziseko zibhalwa kwi-PostgreSQL efanayo okanye kwindawo yokugcina eyahlukileyo. Unokuzithengisa kwi-Intanethi, zikhona. Omnye wabaphuhlisi bento enjalo uhleli kwiforum ye-sql.ru kwintambo ye-PostgreSQL. Ungambamba apho. Ewe, kukho izinto ezinjalo, zinokusetyenziswa. Plus kuyo pgCenter Ndibhala into ekuvumela ukuba wenze into efanayo.

PS1 Ukuba usebenzisa postgres_exporter, yeyiphi ideshibhodi oyisebenzisayo? Ziliqela zazo. Sele ziphelelwe lixesha. Mhlawumbi uluntu luya kudala itemplate ehlaziyiweyo?

I-PS2 Isusiwe i-pganalyze kuba ilunikezelo lwe-SaaS lobunikazi olugxile ekubekweni kweliso kokusebenza kunye neengcebiso zokulungelelanisa ezizenzekelayo.

Ngabasebenzisi ababhalisiweyo kuphela abanokuthatha inxaxheba kuphando. Ngena, ndiyacela.

Yeyiphi i-postgresql yokuzibamba ngokwakho (ngedeshibhodi) ocinga ukuba yeyona ilungileyo?

• 30,0%I-Zabbix + izongezo ezivela ku-Alexey Lesovsky okanye zabbix 4.4 okanye 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%i-ganalyze yi-SaaS yomnini-andikwazi ukuyicima1

• 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

Bali-10 abasebenzisi abavotileyo. Abasebenzisi abasi-26 abakhange.

umthombo: www.habr.com