Izisekelo zokuqapha kwe-PostgreSQL. U-Alexey Lesovsky

Ngiphakamisa ukuthi ufunde okulotshiwe kombiko ka-Alexey Lesovsky ovela ku-Data Egret "Izisekelo Zokuqapha kwe-PostgreSQL"

Kulo mbiko, u-Alexey Lesovsky uzokhuluma ngamaphuzu abalulekile ezibalo ze-post-gress, ukuthi zisho ukuthini, nokuthi kungani kufanele zibe khona ekuqapheni; mayelana nokuthi yimaphi amagrafu okumele abe khona ekuqashweni, ukuthi awengezwe kanjani nokuthi atolikwa kanjani. Umbiko uzoba usizo kubaphathi besizindalwazi, abalawuli besistimu nabathuthukisi abanentshisekelo yokuxazulula inkinga ye-Postgres.

Igama lami ngingu-Alexey Lesovsky, ngimele inkampani ye-Data Egret.

Amagama ambalwa ngami. Ngaqala kudala njengomphathi wesistimu.

Ngaphatha zonke izinhlobo zezinhlelo ze-Linux ezihlukene, ngasebenza ezintweni ezihlukahlukene ezihlobene ne-Linux, okungukuthi i-virtualization, ukuqapha, ukusebenza ngama-proxies, njll. Kodwa ngesikhathi esithile ngaqala ukusebenza kakhulu ngemininingwane yolwazi, i-PostgreSQL. Ngangimthanda kakhulu. Futhi ngesinye isikhathi ngaqala ukusebenza ku-PostgreSQL iningi lesikhathi sami sokusebenza. Futhi ngakho kancane kancane ngaba i-PostgreSQL DBA.

Futhi kuwo wonke umsebenzi wami, bengilokhu nginentshisekelo ezihlokweni zezibalo, ukuqapha, kanye ne-telemetry. Futhi ngenkathi ngingumphathi wesistimu, ngasebenza eduze kakhulu ne-Zabbix. Futhi ngabhala isethi encane yemibhalo efana zabbix-izandiso. Wayedume kakhulu ngesikhathi sakhe. Futhi lapho kwakungenzeka ukuqapha izinto ezibalulekile ezihluke kakhulu, hhayi i-Linux kuphela, kodwa futhi nezingxenye ezihlukahlukene.

Manje ngisebenza ku-PostgreSQL. Sengibhala enye into ekuvumela ukuthi usebenze ngezibalo ze-PostgreSQL. Kubizwa pgCenter (isihloko ku-Habré - Izibalo ze-post-gress ngaphandle kwemizwa nokushuba).

Inothi elincane lesethulo. Yiziphi izimo amakhasimende ethu, amakhasimende ethu anazo? Kukhona uhlobo oluthile lwengozi oluhlobene nesizindalwazi. Futhi lapho ukwaziswa okugciniwe sekubuyisiwe, inhloko yomnyango noma inhloko yezokuthuthukiswa iyafika ithi: “Bangane, kudingeka siqaphe isizindalwazi, ngoba kukhona okubi okwenzekile futhi kudingeka sikuvimbele lokhu kungenzeki esikhathini esizayo.” Futhi lapha kuqala inqubo ethokozisayo yokukhetha isistimu yokuqapha noma ukulungisa isistimu yokuqapha ekhona ukuze ukwazi ukuqapha isizindalwazi sakho - i-PostgreSQL, i-MySQL noma ezinye. Futhi ozakwabo baqala ukusikisela: “Ngizwile ukuthi kukhona isizindalwazi esinjalo nesinjalo. Asisebenzise." Ozakwethu baqala ukuxabana bodwa. Futhi ekugcineni kuvela ukuthi sikhetha uhlobo oluthile lwedathabheyisi, kodwa ukuqapha kwe-PostgreSQL kwethulwa kuyo kunalokho kabi futhi kufanele sengeze okuthile ngaso sonke isikhathi. Thatha amakhosombe athile ku-GitHub, uwahlanganise, uguqule imibhalo, futhi uwenze ngendlela oyifisayo. Futhi ekugcineni kugcina kuwuhlobo oluthile lomsebenzi wezandla.

Ngakho-ke, kule nkulumo ngizozama ukukunikeza ulwazi oluthile mayelana nendlela yokukhetha ukuqapha hhayi kwe-PostgreSQL kuphela, kodwa nesizindalwazi. Futhi ikunikeza ulwazi oluzokuvumela ukuthi uqedele ukuqapha kwakho ukuze uthole inzuzo ethile kukho, ukuze ukwazi ukuqapha isizindalwazi sakho ngokuhlomula, ukuze uvimbele ngokushesha noma yiziphi izimo eziphuthumayo ezizayo ezingase ziphakame.

Futhi imibono ezoba kulo mbiko ingashintshwa ngokuqondile kunoma iyiphi i-database, kungaba i-DBMS noma i-noSQL. Ngakho-ke, akukhona i-PostgreSQL kuphela, kodwa kuzoba nezindlela zokupheka eziningi zokuthi ungakwenza kanjani lokhu ku-PostgreSQL. Kuzoba nezibonelo zemibuzo, izibonelo zezinhlangano i-PostgreSQL enazo zokuqapha. Futhi uma i-DBMS yakho inezinto ezifanayo ezikuvumela ukuthi uzibeke ekuqaphelweni, ungakwazi futhi ukuzivumelanisa nazo, uzingeze futhi kuzoba kuhle.

Ngeke ngibe sembikweni
khuluma ngendlela yokuletha nokugcina amamethrikhi. Ngeke ngisho lutho mayelana nokucutshungulwa kwangemuva kwedatha nokuyethula kumsebenzisi. Futhi ngeke ngisho lutho mayelana nokuxwayisa.
Kodwa njengoba indaba iqhubeka, ngizobonisa izithombe-skrini ezihlukene zokuqapha ezikhona futhi ngandlela-thile ngizigxeke. Kodwa noma kunjalo, ngizozama ukungawasho amagama amabhrendi ukuze ngingadali ukukhangisa noma okuphambene nokukhangisa kwale mikhiqizo. Ngakho-ke, konke ukuqondana kwenzeka ngokungahleliwe futhi kushiywe emcabangweni wakho.

Okokuqala, ake sithole ukuthi kuyini ukuqapha. Ukuqapha kuyinto ebaluleke kakhulu ukuba nayo. Wonke umuntu uyakuqonda lokhu. Kodwa ngesikhathi esifanayo, ukuqapha akuhambisani nomkhiqizo webhizinisi futhi akuthinti ngokuqondile inzuzo yenkampani, ngakho-ke isikhathi sihlala sinikezwa ukuqapha ngokuqhubekayo. Uma sinesikhathi, sizobe sesenza ukuqapha; uma singenaso isikhathi, khona-ke KULUNGILE, sizokubuyisela emuva futhi ngolunye usuku sizobuyela kule misebenzi.

Ngakho-ke, kusukela ekusebenzeni kwethu, lapho sifika kumakhasimende, ukuqapha ngokuvamile akuphelele futhi akunazo izinto ezithakazelisayo ezingasisiza senze umsebenzi ongcono nge-database. Ngakho-ke ukuqapha kudinga ukuqedwa ngaso sonke isikhathi.

Imininingo egciniwe yizinto eziyinkimbinkimbi kakhulu ezidinga futhi ukugadwa, ngoba imininingwane yolwazi iyinqolobane yolwazi. Futhi ulwazi lubaluleke kakhulu enkampanini; alukwazi ukulahleka nganoma iyiphi indlela. Kepha ngasikhathi sinye, imininingwane yolwazi iyizingcezu zesoftware eziyinkimbinkimbi kakhulu. Aqukethe inani elikhulu lezingxenye. Futhi eziningi zalezi zingxenye zidinga ukugadwa.

Uma sikhuluma ngokuqondile nge-PostgreSQL, khona-ke ingamelwa ngendlela yohlelo oluqukethe inani elikhulu lezingxenye. Lezi zingxenye ziyasebenzisana. Futhi ngesikhathi esifanayo, i-PostgreSQL inalokho okubizwa ngokuthi i-Stats Collector subsystem, ekuvumela ukuthi uqoqe izibalo mayelana nokusebenza kwalezi zinhlelo ezincane futhi unikeze uhlobo oluthile lwesixhumi esibonakalayo kumlawuli noma umsebenzisi ukuze akwazi ukubuka lezi zibalo.

Lezi zibalo zethulwa ngendlela yesethi ethile yemisebenzi nokubuka. Angabuye abizwe ngokuthi amatafula. Okusho ukuthi, usebenzisa iklayenti le-psql elivamile, ungakwazi ukuxhuma kusizindalwazi, ukhethe kule misebenzi nokubukwa, futhi uthole izinombolo ezithile mayelana nokusebenza kwezinhlelo ezingaphansi ze-PostgreSQL.

Ungakwazi ukwengeza lezi zinombolo ohlelweni lwakho lokuqapha oluthandayo, udwebe amagrafu, wengeze imisebenzi futhi uthole izibalo ngokuhamba kwesikhathi.

Kodwa kulo mbiko ngeke ngihlanganise yonke le misebenzi ngokuphelele, ngoba kungathatha usuku lonke. Ngizokhuluma ngezinto ezimbili, ezintathu noma ezine futhi ngikutshele ukuthi zisiza kanjani ukwenza ukuqapha kube ngcono.

Futhi uma sikhuluma ngokuqapha kwedathabhesi, yini-ke okudingeka iqashwe? Okokuqala, sidinga ukuqapha ukutholakala, ngoba isizindalwazi siyisevisi enikeza ukufinyelela kudatha kumakhasimende futhi sidinga ukuqapha ukutholakala, futhi sinikeze ezinye zezici zayo zekhwalithi nenani.

Kudingeka futhi siqaphe amaklayenti axhuma kusizindalwazi sethu, ngoba angaba amaklayenti avamile namaklayenti ayingozi angalimaza isizindalwazi. Badinga futhi ukugadwa futhi kulandelelwe imisebenzi yabo.

Lapho amaklayenti exhuma kusizindalwazi, kusobala ukuthi aqala ukusebenza nedatha yethu, ngakho-ke sidinga ukuqapha ukuthi amaklayenti asebenza kanjani ngedatha: ngamathebula amaphi, futhi ngokwezinga elincane, yiziphi izinkomba. Okusho ukuthi, sidinga ukuhlola umthwalo womsebenzi owenziwa amaklayenti ethu.

Kodwa umthwalo womsebenzi nawo uqukethe, vele, izicelo. Izicelo zixhuma ku-database, finyelela idatha zisebenzisa imibuzo, ngakho-ke kubalulekile ukuhlola ukuthi yimiphi imibuzo esinayo kusizindalwazi, siqaphe ukufaneleka kwayo, ukuthi ayibhalwanga ngokungafanele, ukuthi ezinye izinketho zidinga ukubhalwa kabusha futhi zenziwe ukuze zisebenze ngokushesha. nangokusebenza okungcono.

Futhi njengoba sikhuluma nge-database, i-database ihlale iyizinqubo zangemuva. Izinqubo zasemuva zisiza ukugcina ukusebenza kwesizindalwazi ezingeni elihle, ngakho zidinga inani elithile lezinsiza ukuze zisebenze. Futhi ngesikhathi esifanayo, zingadlulela nezinsiza zesicelo seklayenti, ukuze izinqubo zangemuva ezinohaha zingathinta ngokuqondile ukusebenza kwezicelo zeklayenti. Ngakho-ke, nazo zidinga ukugadwa futhi zilandelelwe ukuze kungabikho ukuhlanekezela mayelana nezinqubo zasemuva.

Futhi konke lokhu mayelana nokuqapha isizindalwazi kuhlala kumethrikhi yohlelo. Kodwa uma sicabangela ukuthi ingqalasizinda yethu eminingi iya emafini, amamethrikhi esistimu yomsingathi ngamunye ahlala efiphala ngemuva. Kodwa kusizindalwazi zisabalulekile futhi, kunjalo, kuyadingeka futhi ukuqapha amamethrikhi esistimu.

Yonke into ihamba kahle kakhulu ngamamethrikhi esistimu, zonke izinhlelo zokuqapha zesimanje sezivele ziwasekela lawa mamethrikhi, kodwa ngokuvamile, ezinye izingxenye azikanele futhi ezinye izinto zidinga ukwengezwa. Ngizophinde ngizithinte, kuzoba nama-slides amaningana ngazo.

Iphuzu lokuqala lohlelo ukufinyeleleka. Kuyini ukufinyeleleka? Ukutholakala ekuqondeni kwami ​​ikhono lesisekelo ekuxhumekeni kwesevisi, okungukuthi isisekelo siphakanyisiwe, sona, njengesevisi, samukela ukuxhumana okuvela kumakhasimende. Futhi lokhu kufinyeleleka kungahlolwa ngezici ezithile. Kulula kakhulu ukubonisa lezi zici kumadeshibhodi.

Wonke umuntu uyazi ukuthi ayini amadeshibhodi. Kulapho uke wabheka khona isikrini lapho ulwazi oludingekayo lufingqwa khona. Futhi ungakwazi ngokushesha ukunquma ukuthi kukhona inkinga ku-database noma cha.
Ngokufanelekile, ukutholakala kwesizindalwazi kanye nezinye izici ezibalulekile kufanele zihlale ziboniswa kumadeshibhodi ukuze lolu lwazi luseduze futhi luhlale lutholakala kuwe. Eminye imininingwane eyengeziwe esivele isize ophenyweni lwezehlakalo, lapho kuphenywa ezinye izimo eziphuthumayo, kakade kudingeka ukuthi zibekwe kumadeshibhodi esibili, noma zifihlwe kuzixhumanisi zokudonsela phansi eziholela ezinhlelweni zokuqapha zezinkampani zangaphandle.

Isibonelo sesistimu eyodwa yokuqapha eyaziwayo. Lolu uhlelo lokuqapha olupholile kakhulu. Uqoqa idatha eningi, kodwa ngokombono wami, unomqondo ongajwayelekile wamadeshibhodi. Kukhona isixhumanisi "sokudala ideshibhodi". Kodwa uma udala ideshibhodi, udala uhlu lwamakholomu amabili, uhlu lwamagrafu. Futhi uma udinga ukubheka okuthile, uqala ukuchofoza ngegundane, upheqa, ubheka ishadi olifunayo. Futhi lokhu kuthatha isikhathi, okungukuthi awekho amadeshibhodi anjalo. Kukhona izinhlu kuphela zamashadi.

Yini okufanele uyengeze kulawa madeshibhodi? Ungaqala ngesici esinjengesikhathi sokuphendula. I-PostgreSQL inokubuka kwe-pg_stat_statements. Ikhutshaziwe ngokuzenzakalelayo, kodwa ingenye yezinhlelo ezibalulekile zokubuka okufanele zihlale zinikwe amandla futhi zisetshenziswa. Igcina ulwazi olumayelana nayo yonke imibuzo esebenzayo eyenziwe kusizindalwazi.

Ngakho-ke, singakwazi ukuqala eqinisweni lokuthi singathatha isikhathi sokwenza zonke izicelo futhi sihlukanise ngenani lezicelo sisebenzisa izinkambu ezingenhla. Kodwa leli izinga lokushisa elijwayelekile esibhedlela. Singaqala kwezinye izinkambu - isikhathi esincane sokwenza imibuzo, umkhawulo kanye ne-median. Futhi singakwazi ukwakha amaphesenti; I-PostgreSQL inemisebenzi ehambisanayo yalokhu. Futhi singathola ezinye izinombolo ezibonisa isikhathi sokuphendula sesizindalwazi sethu sezicelo eseziqediwe, okungukuthi, asenzi isicelo esingumgunyathi 'sikhethe 1' futhi sibheke isikhathi sokuphendula, kodwa sihlaziya isikhathi sokuphendula sezicelo eseziqediwe bese sidweba. kungaba umfanekiso ohlukile, noma sakha igrafu ngokusekelwe kuso.

Kubalulekile futhi ukuqapha inani lamaphutha akhiqizwa uhlelo okwamanje. Futhi kulokhu ungasebenzisa ukubuka kwe-pg_stat_database. Sigxila enkambini ye-xact_rollback. Le nkambu ayibonisi kuphela inani lokuhlehla okwenzeka kusizindalwazi, kodwa futhi icabangela inani lamaphutha. Uma sikhuluma ngokuqhathaniswa, singabonisa lesi sibalo kudeshibhodi yethu futhi sibone ukuthi mangaki amaphutha esinawo njengamanje. Uma kunamaphutha amaningi, khona-ke lesi yisizathu esihle sokubheka izingodo futhi ubone ukuthi yiziphi izinhlobo zamaphutha nokuthi kungani zenzeka, bese utshala futhi uzixazulule.

Ungangeza into efana neTachometer. Lezi izinombolo zokwenziwa ngomzuzwana kanye nenani lezicelo ngomzuzwana. Uma sikhuluma ngokuqhathaniswa, ungasebenzisa lezi zinombolo njengokusebenza kwamanje kwesizindalwazi sakho futhi ubheke ukuthi zikhona yini iziqongo ezicelweni, inani eliphakeme lemisebenzi, noma, ngokuphambene, ukuthi ingabe isizindalwazi silayishwe ngaphansi yini ngenxa yokuthi enye i-backend ihlulekile. Kubalulekile ukuthi ngaso sonke isikhathi sibheke lesi sibalo futhi sikhumbule ukuthi kuphrojekthi yethu lolu hlobo lokusebenza luvamile, kodwa amanani angenhla nangaphansi asevele enenkinga ethile futhi engaqondakali, okusho ukuthi sidinga ukubheka ukuthi kungani lezi zinombolo phezulu kakhulu.

Ukuze ulinganisele inani lemisebenzi, singaphinda sibheke pg_stat_database view. Singangeza inani lezinto ezenziwayo kanye nenani lokuhlehla futhi sithole inani lemisebenzi ngomzuzwana.

Ingabe wonke umuntu uyaqonda ukuthi izicelo ezimbalwa zingangena kumsebenzi owodwa? Ngakho-ke i-TPS ne-QPS zihluke kancane.

Inombolo yezicelo ngomzuzwana ingatholwa ku-pg_stat_statements futhi imane ibale isamba sazo zonke izicelo eziqediwe. Kuyacaca ukuthi siqhathanisa inani lamanje nelangaphambilini, silisuse, sithole i-delta, futhi sithole ubuningi.

Ungangeza ama-metrics engeziwe uma uwafisa, asiza futhi ukuhlola ukutholakala kwesizindalwazi sethu futhi uhlole ukuthi kube khona yini izikhathi zokungasebenzi.

Enye yalawa mamethrikhi isikhathi esiphezulu. Kepha isikhathi sokuphumula ku-PostgreSQL siyinkimbinkimbi. Ngizokutshela ukuthi kungani. Lapho i-PostgreSQL isiqalile, isikhathi sokuphumula siqala ukubika. Kodwa uma ngesinye isikhathi, ngokwesibonelo, umsebenzi othile wawusebenza ebusuku, kwafika umbulali we-OOM futhi wayinqamula ngenkani inqubo yengane ye-PostgreSQL, khona-ke kuleli cala i-PostgreSQL inqamula ukuxhumana kwawo wonke amaklayenti, isetha kabusha indawo yenkumbulo eshayiwe futhi iqala ukululama kusuka. indawo yokugcina yokuhlola. Futhi ngenkathi lokhu kubuyiselwa okuvela endaweni yokuhlola kuqhubeka, isizindalwazi asikwamukeli ukuxhumeka, okungukuthi lesi simo singahlolwa njengesikhathi sokuphumula. Kodwa ikhawunta yesikhathi sokuphumula ngeke isethwe kabusha, ngoba icabangela isikhathi sokuqalisa komphathi weposi kusukela ngomzuzu wokuqala. Ngakho-ke, izimo ezinjalo zingeqiwa.

Kufanele futhi uqaphe inani labasebenzi be-vacuum. Ingabe wonke umuntu uyazi ukuthi iyini i-autovacuum ku-PostgreSQL? Lolu uhlelo olungaphansi oluthokozisayo ku-PostgreSQL. Kubhalwe izindatshana eziningi ngaye, sekwenziwe imibiko eminingi. Kunezingxoxo eziningi mayelana ne-vacuum nokuthi kufanele isebenze kanjani. Abaningi bakubheka njengobubi obudingekayo. Kodwa kunjalo. Lolu uhlobo lwe-analogue yomqoqi kadoti ehlanza izinguqulo eziphelelwe yisikhathi zemigqa ezingadingeki nganoma yikuphi ukuthengiselana futhi ikhulule isikhala kumathebula nezinkomba zemigqa emisha.

Kungani udinga ukuyigada? Ngoba ivacuum ngezinye izikhathi ibuhlungu kakhulu. Idla inani elikhulu lezinsiza futhi izicelo zamaklayenti ziqala ukuhlupheka ngenxa yalokho.

Futhi kufanele igadwe ngokubukwa kwe-pg_stat_activity, engizokhuluma ngakho esigabeni esilandelayo. Lokhu kubuka kubonisa umsebenzi wamanje kusizindalwazi. Futhi ngalo msebenzi singakwazi ukulandelela inani lama-vacuum asebenzayo njengamanje. Singakwazi ukulandelela ama-vacuums futhi sibone ukuthi uma seqe umkhawulo, lesi yisizathu sokubheka izilungiselelo ze-PostgreSQL futhi ngandlela thize sandise ukusebenza kwe-vacuum.

Enye into nge-PostgreSQL ukuthi i-PostgreSQL igula kakhulu ngokuthengiselana okude. Ikakhulukazi kusukela ekuthengiseni okuhlala isikhathi eside futhi kungenzi lutho. Lokhu okubizwa nge-stat idle-in-transaction. Umsebenzi onjalo ubamba izingidi futhi uvimbele i-vacuum ekusebenzeni. Futhi ngenxa yalokho, amatafula avuvukala futhi anda ngosayizi. Futhi imibuzo esebenza nalawa mathebula iqala ukusebenza kancane, ngoba udinga ukufoshela zonke izinguqulo ezindala zemigqa ukusuka kumemori kuye kudiski nangemuva. Ngakho-ke, isikhathi, isikhathi sokuthengiselana okude kakhulu, izicelo ze-vacuum ezinde kakhulu nazo zidinga ukugadwa. Futhi uma sibona izinqubo ezithile esezisebenze isikhathi eside kakhulu, sezivele zingaphezu kwemizuzu eyi-10-20-30 yomthwalo we-OLTP, khona-ke sidinga ukuzinaka futhi siziqedele ngamandla, noma sithuthukise uhlelo lokusebenza ukuze azibizwa futhi azilenge isikhathi eside. Ngomthwalo wokuhlaziya umsebenzi, imizuzu eyi-10-20-30 ijwayelekile; kukhona nezinde.

Okulandelayo sinenketho namaklayenti axhunyiwe. Uma sesivele siyidalile ideshibhodi futhi sathumela amamethrikhi angukhiye wokutholakala kuyo, singaphinda sengeze ulwazi olwengeziwe mayelana namaklayenti axhunyiwe lapho.

Ulwazi mayelana namaklayenti axhunyiwe lubalulekile ngoba, ngokombono we-PostgreSQL, amaklayenti ahlukile. Kunamakhasimende amahle futhi kukhona amaklayenti amabi.

Isibonelo esilula. Ngeklayenti ngiyaluqonda uhlelo lokusebenza. Uhlelo lokusebenza luxhumeke ku-database futhi ngokushesha luqala ukuthumela izicelo zalo lapho, izinqubo zedathabhesi futhi lizenze, futhi libuyisela imiphumela kuklayenti. Lawa amaklayenti amahle nalungile.

Kunezimo lapho iklayenti selixhumekile, libamba ukuxhumana, kodwa akwenzi lutho. Isesimweni sokungenzi lutho.

Kodwa kukhona amaklayenti amabi. Isibonelo, iklayenti elifanayo lixhumekile, lavula ukuthengiselana, lenza okuthile ku-database futhi langena kukhodi, isibonelo, ukufinyelela umthombo wangaphandle noma ukucubungula idatha etholiwe lapho. Kodwa akazange akuvale ukuthengiselana. Futhi ukuthengiselana kulenga ku-database futhi kugcinwe kukhiye emgqeni. Lesi isimo esibi. Futhi uma ngokuzumayo isicelo endaweni ethile ngaphakathi ngokwaso sehluleka ngaphandle, khona-ke ukuthengiselana kungahlala kuvuliwe isikhathi eside kakhulu. Futhi lokhu kuthinta ngqo ukusebenza kwe-PostgreSQL. I-PostgreSQL izohamba kancane. Ngakho-ke, kubalulekile ukulandelela amaklayenti anjalo ngesikhathi futhi uqede umsebenzi wawo ngamandla. Futhi udinga ukwandisa uhlelo lwakho lokusebenza ukuze izimo ezinjalo zingenzeki.

Amanye amaklayenti amabi angamakhasimende alindile. Kodwa baba babi ngenxa yezimo. Isibonelo, ukuthengiselana okulula kokungenzi lutho: kungavula ukuthengiselana, ukuthatha izingidi kweminye imigqa, khona-ke endaweni ethile kukhodi kuzohluleka, kushiya ukuthengiselana okulenga. Elinye iklayenti lizofika licele idatha efanayo, kodwa lizohlangana nokhiye, ngoba lokho kuthengiselana sekuvele kubambe izingidi kweminye imigqa edingekayo. Futhi okwenziwayo kwesibili kuzolenga kulinde ukuthi ukuthengiselana kokuqala kuqedwe noma kuvalwe ngenkani ngumlawuli. Ngakho-ke, ukuthengiselana okulindile kungaqongelela futhi kugcwalise umkhawulo wokuxhumana wesizindalwazi. Futhi uma umkhawulo usugcwele, uhlelo lokusebenza ngeke lusakwazi ukusebenza nesizindalwazi. Lesi sekuyisimo esiphuthumayo sephrojekthi. Ngakho-ke, amaklayenti amabi adinga ukulandelelwa nokuphendulwa ngesikhathi.

Esinye isibonelo sokuqapha. Futhi isivele ikhona ideshibhodi ehloniphekile lapha. Kukhona ulwazi mayelana nokuxhumana ngenhla. Ukuxhumeka kwe-DB - izingcezu ezingu-8. Futhi konke. Asinalo ulwazi ngokuthi imaphi amaklayenti asebenzayo, yimaphi amaklayenti angenzi lutho, awenzi lutho. Alukho ulwazi mayelana nokwenziwe okusalindile noxhumo olulindile, okungukuthi lesi yisibalo esibonisa inani labaxhumekile futhi yiso leso. Bese uziqagela.

Ngokufanelekile, ukuze ungeze lolu lwazi ekuqaphelweni, udinga ukufinyelela ukubukwa kwesistimu ye-pg_stat_activity. Uma uchitha isikhathi esiningi ku-PostgreSQL, khona-ke lokhu kuwumbono omuhle kakhulu okufanele ube umngane wakho, ngoba kubonisa umsebenzi wamanje ku-PostgreSQL, okungukuthi okwenzeka kuyo. Enqubweni ngayinye kunomugqa ohlukile obonisa ulwazi mayelana nale nqubo: kumuphi umphathi uxhumano lwenziwa, ngaphansi kwamuphi umsebenzisi, ngaphansi kwaliphi igama, lapho umsebenzi uqalwe, yisiphi isicelo esisebenzayo njengamanje, isiphi isicelo esigcine ukufakwa. Futhi, ngokufanele, singahlola isimo seklayenti sisebenzisa inkambu yezibalo. Uma sikhuluma ngokuqhathaniswa, singakwazi ukuqoqa ngale nkundla futhi sithole lezo zibalo okwamanje ezikusizindalwazi kanye nenani labaxhumi abanalesi sibalo kusizindalwazi. Futhi singathumela izinombolo ezitholiwe kakade ekuqapheni kwethu futhi sidwebe amagrafu ngokusekelwe kuzo.
Kubalulekile futhi ukuhlola ubude besikhathi somsebenzi. Sengike ngasho ukuthi kubalulekile ukuhlola ubude be-vacuum, kodwa ukuthengiselana kuhlolwa ngendlela efanayo. Kukhona izinkambu ze-xact_start kanye ne-query_start. Bona, uma kuqhathaniswa, bakhombisa isikhathi sokuqala kokwenziwayo nesikhathi sokuqala kwesicelo. Sithatha umsebenzi we-now(), obonisa isitembu sesikhathi samanje, bese sikhipha okwenziwayo kanye nesitembu sesikhathi sokucela. Futhi sithola ubude bomsebenzi, isikhathi sesicelo.

Uma sibona okwenziwayo okude, kufanele sikuqedele kakade. Ngomthwalo we-OLTP, ukuthengiselana okude sekungaphezu kwamaminithi angu-1-2-3. Ngomthwalo womsebenzi we-OLAP, ukuthengiselana okude kuvamile, kodwa uma kuthatha amahora angaphezu kwamabili ukuqeda, khona-ke lokhu kuwuphawu lokuthi sine-skew endaweni ethile.

Uma amaklayenti esexhumeke kusizindalwazi, aqala ukusebenza ngedatha yethu. Bafinyelela amathebula, bafinyelela izinkomba ukuze bathole idatha etafuleni. Futhi kubalulekile ukuhlola ukuthi amaklayenti asebenzisana kanjani nale datha.

Lokhu kuyadingeka ukuze sihlole umthwalo wethu womsebenzi futhi siqonde cishe ukuthi yimaphi amathebula “ashisa kakhulu” kithi. Isibonelo, lokhu kuyadingeka ezimweni lapho sifuna ukubeka amatafula "ashisayo" ohlotsheni oluthile lwesitoreji esisheshayo se-SSD. Isibonelo, amanye amathebula engobo yomlando esingawasebenzisi isikhathi eside angathuthelwa ohlotsheni oluthile lwengobo yomlando “ebandayo,” kumadrayivu e-SATA futhi uwavumele ahlale lapho, azofinyelelwa njengoba kudingeka.

Lokhu futhi kuyasiza ekutholeni okudidayo ngemva kwanoma yikuphi ukukhishwa nokusetshenziswa. Ake sithi iphrojekthi ikhiphe isici esisha. Isibonelo, sengeze ukusebenza okusha kokusebenza nesizindalwazi. Futhi uma sihlela amagrafu okusetshenziswa kwethebula, singathola kalula lokhu okudidayo kulawa magrafu. Isibonelo, buyekeza ukuqhuma noma susa ukuqhuma. Kuzobonakala kakhulu.

Ungakwazi futhi ukubona okudidayo kwizibalo "ezintantayo". Kusho ukuthini? I-PostgreSQL inomhleli wemibuzo onamandla kakhulu futhi omuhle kakhulu. Futhi abathuthukisi banikela isikhathi esiningi ekuthuthukisweni kwayo. Usebenza kanjani? Ukuze wenze izinhlelo ezinhle, i-PostgreSQL iqoqa izibalo zokusatshalaliswa kwedatha kumathebula ngesikhathi esithile kanye nangemvamisa ethile. Lawa amanani avame kakhulu: inani lamanani ayingqayizivele, ulwazi mayelana ne-NULL etafuleni, ulwazi oluningi.

Ngokusekelwe kulezi zibalo, umhleli wakha imibuzo embalwa, akhethe ofaneleka kakhulu, futhi asebenzise lolu hlelo lombuzo ukuze asebenzise umbuzo ngokwawo futhi abuyisele idatha.

Futhi kwenzeka ukuthi izibalo "zintanta". Ikhwalithi nedatha yobuningi ngandlela thile ishintshile kuthebula, kodwa izibalo aziqoqwanga. Futhi izinhlelo ezenziwe zingahle zingabi zinhle. Futhi uma izinhlelo zethu ziba nemiphumela engemihle ngokusekelwe ekuqaphelweni okuqoqiwe, ngokusekelwe kumathebula, sizokwazi ukubona lezi ziphazamiso. Isibonelo, endaweni ethile idatha yashintsha ngekhwalithi futhi esikhundleni senkomba, ukudlula okulandelanayo etafuleni kwaqala ukusetshenziswa, i.e. uma umbuzo udinga ukubuyisela imigqa eyi-100 kuphela (kunomkhawulo we-100), ukusesha okuphelele kuzokwenziwa kulo mbuzo. Futhi lokhu kuhlale kunomphumela omubi kakhulu ekusebenzeni.

Futhi lokhu singakubona ekuqapheni. Futhi vele ubheke lo mbuzo, sebenzisa ukuchaza kwawo, qoqa izibalo, wakhe inkomba entsha eyengeziwe. Futhi vele uphendule kule nkinga. Yingakho kubalulekile.

Esinye isibonelo sokuqapha. Ngicabanga ukuthi abantu abaningi bamazi ngoba ethandwa kakhulu. Ubani oyisebenzisayo kumaphrojekthi abo Prometheus? Ubani osebenzisa lo mkhiqizo ngokuhlanganyela ne-Prometheus? Iqiniso liwukuthi endaweni yokugcina evamile yalokhu kuqapha kukhona ideshibhodi yokusebenza ne-PostgreSQL - postgres_exporter I-Prometheus. Kodwa kunemininingwane eyodwa embi.

Kunamagrafu amaningana. Futhi amabhayithi akhonjiswe njengobunye, okungukuthi kukhona amagrafu angu-5. Lena idatha ethi, Faka idatha, Buyekeza idatha, Susa idatha, Landa idatha futhi Buyisela idatha. Isilinganiso seyunithi singamabhayithi. Kodwa into ukuthi izibalo ku-PostgreSQL zibuyisela idatha ku-tuple (imigqa). Futhi, ngokufanelekile, lawa magrafu ayindlela enhle kakhulu yokubukela phansi umthwalo wakho wokusebenza izikhathi eziningana, izikhathi ezingamashumi, ngoba i-tuple ayiyona ibhayithi, i-tuple iyintambo, inamabhayithi amaningi futhi ihlala inobude obuguquguqukayo. Okusho ukuthi, ukubala umthwalo womsebenzi ngamabhayithi usebenzisa ama-tuples kuwumsebenzi ongekho ngokoqobo noma onzima kakhulu. Ngakho-ke, uma usebenzisa ideshibhodi noma ukuqapha okwakhelwe ngaphakathi, kubalulekile ukuqonda ukuthi isebenza ngendlela efanele futhi ikubuyisela idatha ehlolwe kahle.

Uzithola kanjani izibalo kulawa mathebula? Ngale njongo, i-PostgreSQL inomndeni othile wokubuka. Futhi umbono oyinhloko pg_stat_user_tables. User_tables - lokhu kusho amathebula adalelwe umsebenzisi. Ngokuphambene, kukhona ukubukwa kwesistimu okusetshenziswa yi-PostgreSQL ngokwayo. Futhi kukhona ithebula lesifinyezo elithi Alltables, elihlanganisa kokubili isistimu neyomsebenzisi. Ungaqala kunoma iyiphi yazo oyithanda kakhulu.

Ngokusebenzisa izinkambu ezingenhla ungalinganisa inani lokufakwayo, ukuvuselela kanye nokususa. Isibonelo sedeshibhodi engiyisebenzise sisebenzisa lezi zinkambu ukuhlola izici zomthwalo womsebenzi. Ngakho-ke, singakha futhi phezu kwazo. Kodwa kufanelekile ukukhumbula ukuthi lawa ama-tuples, hhayi ama-byte, ngakho-ke asikwazi ukuvele sikwenze ngamabhayithi.

Ngokusekelwe kule datha, singakha lokho okubizwa ngamathebula e-TopN. Ngokwesibonelo, Top-5, Top-10. Futhi ungakwazi ukulandelela lawo mathebula ashisayo agaywa kabusha kakhulu kunamanye. Isibonelo, amatafula angu-5 "ashisayo" ukuze afakwe. Futhi sisebenzisa lawa mathebula e-TopN sihlola umthwalo wethu womsebenzi futhi singahlola ukuqhuma komsebenzi ngemva kwanoma yikuphi ukukhishwa, ukubuyekezwa, kanye nokusetshenziswa.

Kubalulekile futhi ukuhlola usayizi wethebula, ngoba ngezinye izikhathi abathuthukisi bakhipha isici esisha, futhi amatafula ethu aqala ukuvuvukala ngosayizi wawo omkhulu, ngoba banqume ukwengeza inani elingeziwe ledatha, kodwa abazange babikezele ukuthi lokhu kuzokwenzeka kanjani. kuthinte usayizi wesizindalwazi. Amacala anjalo nawo ayasimangalisa.

Futhi manje umbuzo omncane kuwe. Yimuphi umbuzo ophakamayo lapho ubona umthwalo kuseva yakho yesizindalwazi? Imuphi umbuzo olandelayo onawo?

Kodwa eqinisweni umbuzo uphakama kanje. Umthwalo ubangela ziphi izicelo? Okungukuthi, akuthakazelisi ukubuka izinqubo ezibangelwa umthwalo. Kuyacaca ukuthi uma umsingathi enesizinda semininingwane, khona-ke i-database iyasebenza lapho futhi kusobala ukuthi isizindalwazi kuphela esizolahlwa lapho. Uma sivula Okuphezulu, sizobona kukhona uhlu lwezinqubo ku-PostgreSQL ezenza okuthile. Ngeke kucace kwaPhezulu ukuthi benzani.

Ngakho-ke, udinga ukuthola leyo mibuzo ebangela umthwalo ophakeme kakhulu, ngoba imibuzo yokulungisa, njengomthetho, inikeza inzuzo eyengeziwe kunokulungisa i-PostgreSQL noma ukucushwa kwesistimu yokusebenza, noma nokulungisa ihadiwe. Ngokwesilinganiso sami, lokhu cishe kungama-80-85-90%. Futhi lokhu kwenziwa ngokushesha okukhulu. Kuyashesha ukulungisa isicelo kunokulungisa ukucushwa, ukuhlela ukuqalisa kabusha, ikakhulukazi uma isizindalwazi singakwazi ukuqaliswa kabusha, noma ukwengeza ihadiwe. Kulula ukubhala kabusha umbuzo kwenye indawo noma wengeze inkomba ukuze uthole umphumela ongcono kulo mbuzo.

Ngakho-ke, kuyadingeka ukuqapha izicelo kanye nokufaneleka kwazo. Ake sithathe esinye isibonelo sokuqapha. Futhi lapha, futhi, kubonakala kunokuqapha okuhle kakhulu. Kukhona ulwazi lokuphindaphinda, kukhona ulwazi mayelana nokuphuma, ukuvimbela, ukusetshenziswa kwezinsiza. Konke kuhamba kahle, kodwa alukho ulwazi ngezicelo. Akucaci ukuthi yimiphi imibuzo esebenzayo kusizindalwazi sethu, ukuthi isebenza isikhathi esingakanani, mingaki yale mibuzo. Sidinga njalo ukuthi lolu lwazi lube seluqaphelweni lwethu.

Futhi ukuze sithole lolu lwazi singasebenzisa imojuli ye-pg_stat_statements. Ngokusekelwe kukho, ungakha izinhlobonhlobo zamagrafu. Isibonelo, ungathola ulwazi ngemibuzo evame kakhulu, okungukuthi, kuleyo mibuzo evame ukubuzwa. Yebo, ngemuva kokuthunyelwa kuyasiza kakhulu ukukubheka futhi uqonde ukuthi kukhona yini ukwanda kwezicelo.

Ungakwazi ukuqapha imibuzo emide kakhulu, okungukuthi, leyo mibuzo ethatha isikhathi eside ukuyiqeda. Zisebenza ku-processor, zisebenzisa i-I/O. Singakwazi futhi ukuhlola lokhu sisebenzisa izinkambu total_time, mean_time, blk_write_time kanye blk_read_time.

Singahlola futhi siqaphe izicelo ezinzima kakhulu mayelana nokusetshenziswa kwensiza, lezo ezifunda kudiski, ezisebenza ngenkumbulo, noma, ngokuphambene, sidale uhlobo oluthile lomthwalo wokubhala.

Singahlola izicelo zokuphana kakhulu. Lena imibuzo ebuyisela inombolo enkulu yemigqa. Isibonelo, lokhu kungaba isicelo esithile lapho bekhohlwe khona ukubeka umkhawulo. Futhi ivele ibuyisele konke okuqukethwe kwethebula noma umbuzo kuwo wonke amathebula obuziwe.

Futhi ungakwazi ukuqapha imibuzo esebenzisa amafayela esikhashana noma amatafula esikhashana.

Futhi sisenezinqubo zasemuva. Izinqubo zasemuva ziyizindawo zokuhlola ngokuyinhloko noma zibizwa nangokuthi izindawo zokuhlola, lezi ziyi-autovacuum nokuphindaphinda.

Esinye isibonelo sokuqapha. Kunethebhu Yokulungisa kwesokunxele, yiya kuyo futhi uthemba ukubona okuthile okuwusizo. Kodwa nansi kuphela isikhathi sokusebenza kwe-vacuum nokuqoqwa kwezibalo, akukho okunye. Lolu ulwazi olubi kakhulu, ngakho-ke sidinga njalo ukuthi sibe nolwazi mayelana nokuthi izinqubo zangemuva zisebenza kanjani kusizindalwazi sethu kanye nokuthi zikhona yini izinkinga emsebenzini wazo.

Uma sibheka izindawo zokuhlola, kufanele sikhumbule ukuthi izindawo zokuhlola zisusa amakhasi angcolile ukusuka endaweni yenkumbulo ehlukanisiwe ziye kudiski, bese kwakheka indawo yokuhlola. Futhi le ndawo yokuhlola ingasetshenziswa njengendawo yokuthola kabusha uma i-PostgreSQL inqanyulwe kungazelelwe esimweni esiphuthumayo.

Ngakho-ke, ukuze ukhiphe wonke amakhasi "angcolile" kudiski, udinga ukwenza inani elithile lokubhala. Futhi, njengomthetho, kumasistimu anenani elikhulu lenkumbulo, lokhu kuningi. Futhi uma senza ama-checkpoints kaningi esikhathini esifushane, ukusebenza kwediski kuzokwehla kakhulu. Futhi izicelo zamaklayenti zizohlupheka ngenxa yokuntuleka kwezinsiza. Bazoqhudelana ngezinsiza futhi bantule ukukhiqiza.

Ngokufanelekile, nge-pg_stat_bgwriter sisebenzisa izinkambu ezishiwo singaqapha inani lezindawo zokuhlola ezenzekayo. Futhi uma sinezindawo zokuhlola eziningi esikhathini esithile (emaminithini angu-10-15-20, ngesigamu sehora), isibonelo, 3-4-5, khona-ke lokhu kungase kube inkinga kakade. Futhi usuvele udinga ukubheka ku-database, bheka ekucushweni, yini ebangela inala yezindawo zokuhlola. Mhlawumbe kunohlobo oluthile lokuqoshwa okukhulu okuqhubekayo. Sesingavele sihlole umthwalo womsebenzi, ngoba sesivele sengeze amagrafu omthwalo womsebenzi. Sesingavele silungise amapharamitha wephoyinti lokuhlola futhi siqinisekise ukuthi akuthinti kakhulu ukusebenza kombuzo.

Ngibuyela ku-autovacuum futhi ngoba iyinto enjalo, njengoba ngishilo, engahlanganisa kalula kokubili ukusebenza kwediski kanye nombuzo, ngakho-ke kubalulekile ngaso sonke isikhathi ukulinganisa inani le-autovacuum.

Inani labasebenzi be-autovacuum kusizindalwazi lilinganiselwe. Ngokuzenzakalelayo, kunezintathu zazo, ngakho-ke uma sihlala sinezisebenzi ezintathu ezisebenza ku-database, lokhu kusho ukuthi i-autovacuum yethu ayilungiselelwe, sidinga ukuphakamisa imingcele, sibuyekeze izilungiselelo ze-autovacuum futhi singene ekucushweni.
Kubalulekile ukuhlola ukuthi yiziphi izisebenzi ze-vacuum esinazo. Kuphakathi kokuthi yethulwe kumsebenzisi, i-DBA yeza futhi yethula uhlobo oluthile lwe-vacuum, futhi lokhu kwakha umthwalo. Sinenkinga ethile. Noma leli inani lamavacuum akhulula ikhawunta yokwenziwayo. Kwezinye izinguqulo ze-PostgreSQL lezi zingavacuum ezinzima kakhulu. Futhi bangangeza kalula ukusebenza ngoba bafunda ithebula lonke, baskena wonke amabhlogo akulelo thebula.

Futhi, yiqiniso, ubude be-vacuums. Uma sinama-vacuum ahlala isikhathi eside asebenza isikhathi eside kakhulu, lokhu kusho ukuthi sidinga futhi ukunaka ukucushwa kwe-vacuum futhi mhlawumbe siphinde sicabangele izilungiselelo zayo. Ngoba isimo singase siphakame lapho i-vacuum isebenza etafuleni isikhathi eside (amahora angu-3-4), kodwa ngesikhathi sokusebenza kwe-vacuum, inani elikhulu lemigqa efile likwazi ukuqoqa etafuleni futhi. Futhi ngokushesha nje lapho i-vacuum isiqediwe, udinga ukucofa leli thebula futhi. Futhi sifika esimweni - i-vacuum engapheli. Futhi kulokhu, i-vacuum ayihambisani nomsebenzi wayo, futhi amatafula kancane kancane aqala ukuvuvukala ngosayizi, nakuba umthamo wedatha ewusizo kuwo uhlala ufana. Ngakho-ke, ngesikhathi se-vacuums eside, sihlala sibheka ukucushwa futhi sizame ukukulungisa, kodwa ngesikhathi esifanayo ukuze ukusebenza kwezicelo zamaklayenti kungahlupheki.

Kulezi zinsuku cishe akukho ukufakwa kwe-PostgreSQL okungenakho ukuphindaphinda kokusakaza. Ukuphindaphinda kuyinqubo yokuhambisa idatha isuka kokuyinhloko iyiswe efanekisweni.

Ukuphindaphinda ku-PostgreSQL kwenziwa ngelogi yokwenziwayo. Iwizadi ikhiqiza ilogu yokwenziwayo. Ilogi yokwenziwayo ihamba ngoxhumano lwenethiwekhi iye esifanekisweni, bese ikhiqizwa kabusha esifanekisweni. Kulula.

Ngokufanelekile, ukubuka kwe-pg_stat_replication kusetshenziselwa ukuqapha i-replication lag. Kodwa akuyona yonke into elula naye. Kunguqulo ye-10, ukubuka kunezinguquko ezimbalwa. Okokuqala, ezinye izinkambu ziqanjwe kabusha. Futhi ezinye izinkambu zengeziwe. Kunguqulo ye-10, izinkambu zivele ezikuvumela ukuthi ulinganisele ukuphindaphinda ngemizuzwana. Ikhululekile kakhulu. Ngaphambi kwenguqulo 10, bekungenzeka ukulinganisa ukuphindaphindeka kwamabhayithi. Le nketho isala kunguqulo 10, i.e. ungakhetha ukuthi yini ekulungele kakhulu - linganisa i-lag in bytes noma linganisela i-lag ngemizuzwana. Abantu abaningi bakwenza kokubili.

Kodwa noma kunjalo, ukuze uhlole i-lag yokuphindaphinda, udinga ukwazi indawo yelogi ekuthengiseni. Futhi lezi zindawo zelogi yokwenziwe ziku- pg_stat_replication view. Uma sikhuluma ngokuqhathaniswa, singathatha amaphuzu amabili kulogi yokwenziwe sisebenzisa umsebenzi we-pg_xlog_location_diff(). Bala i-delta phakathi kwazo futhi uthole ukuphindaphinda kwamabhayithi. Kulula kakhulu futhi kulula.

Kunguqulo 10, lo msebenzi uqanjwe kabusha ukuze uthi pg_wal_lsn_diff(). Ngokuvamile, kuyo yonke imisebenzi, ukubukwa, nezinsiza lapho igama elithi “xlog” livele khona, lithathelwe indawo inani elithi “wal”. Lokhu kusebenza kukho kokubili ukubuka nemisebenzi. Lokhu ukuqanjwa okusha.

Futhi, enguqulweni ye-10, imigqa yengezwe ekhombisa ngokuqondile i-lag. Lezi yi-write lag, i-flush lag, i-replay lag. Okungukuthi, kubalulekile ukuqapha lezi zinto. Uma sibona ukuthi sine-replication lag, ngakho-ke sidinga ukuphenya ukuthi kungani ivele, ukuthi ivelaphi futhi silungise inkinga.

Cishe yonke into ihlelekile ngamamethrikhi esistimu. Uma noma yikuphi ukuqapha kuqala, kuqala ngamamethrikhi esistimu. Lokhu ukulahlwa kwamaphrosesa, inkumbulo, ukushintshanisa, inethiwekhi kanye nediski. Nokho, amapharamitha amaningi awekho ngokuzenzakalelayo.

Uma konke kuhlelekile ngenqubo yokuvuselela kabusha, khona-ke kunezinkinga ngokuvuselelwa kabusha kwediski. Njengomthetho, abathuthukisi bokuqapha bangeza ulwazi mayelana nokuphuma. Kungaba ngama-iops noma amabhayithi. Kepha bayakhohlwa nge-latency kanye nokusetshenziswa kwamadivayisi wediski. Lawa amapharamitha abaluleke kakhulu asivumela ukuthi sihlole ukuthi alayishwa kanjani amadiski ethu nokuthi ahamba kancane kangakanani. Uma sine-latency ephezulu, lokhu kusho ukuthi kunezinkinga ezithile ngamadiski. Uma sinokusetshenziswa okuphezulu, kusho ukuthi ama-disk awakwazi ukubhekana nakho. Lezi izici ezingcono kune-throughput.

Ngaphezu kwalokho, lezi zibalo zingatholakala ohlelweni lwefayela /proc, njengoba kwenziwa ekugayweni kabusha kwamaphrosesa. Angazi ukuthi kungani lolu lwazi lungangezwa ekuqapheni. Kodwa noma kunjalo, kubalulekile ukuba nalokhu ekuqapheni kwakho.

Okufanayo kuyasebenza nasezinkundleni zokuxhumana. Kukhona ulwazi mayelana nokuphuma kwenethiwekhi emaphaketheni, ngamabhayithi, kodwa nokho alukho ulwazi mayelana nokubambezeleka futhi alukho ulwazi olumayelana nokusetshenziswa, nakuba lokhu futhi kuwulwazi oluwusizo.

Noma yikuphi ukuqapha kunezihibe. Futhi kungakhathaliseki ukuthi uthatha luphi uhlobo lokuqapha, ngeke kuhlangabezane nemibandela ethile. Kodwa noma kunjalo, ziyathuthuka, izici ezintsha nezinto ezintsha ziyengezwa, ngakho-ke khetha okuthile bese uqeda.

Futhi ukuze uqedele, kufanele uhlale unombono wokuthi izibalo ezinikeziwe zisho ukuthini nokuthi ungazisebenzisa kanjani ukuxazulula izinkinga.

Futhi amaphuzu ambalwa abalulekile:

• Kufanele uhlale uqapha ukutholakala futhi ube namadeshibhodi ukuze ukwazi ukuhlola ngokushesha ukuthi yonke into ihlelekile yini ngesizindalwazi.
• Udinga njalo ukuba nombono wokuthi amaklayenti asebenza ini nesizindalwazi sakho ukuze ukhiphe amaklayenti amabi futhi uwadubule.
• Kubalulekile ukuhlola ukuthi la maklayenti asebenza kanjani ngedatha. Udinga ukuba nombono mayelana nomsebenzi wakho.
• Kubalulekile ukuhlola ukuthi lo mthwalo womsebenzi wakheka kanjani, ngosizo lwamiphi imibuzo. Ungakwazi ukuhlola imibuzo, ungakwazi ukuyilungiselela kahle, uyifake kabusha, uyakhele izinkomba. Kubaluleke kakhulu.
• Izinqubo zasemuva zingaba nomthelela omubi ezicelweni zamaklayenti, ngakho-ke kubalulekile ukuqaphela ukuthi awasebenzisi izinsiza eziningi kakhulu.
• Amamethrikhi esistimu akuvumela ukuthi wenze izinhlelo zokukala nokukhulisa umthamo weziphakeli zakho, ngakho-ke kubalulekile ukuwalandela nokuwahlola futhi.

Uma unentshisekelo kulesi sihloko, ungalandela lezi zixhumanisi.
http://bit.do/stats_collector - lena imibhalo esemthethweni evela kumqoqi wezibalo. Kunencazelo yakho konke ukubuka kwezibalo kanye nencazelo yazo zonke izinkambu. Ungafunda, uziqonde futhi uzihlaziye. Futhi ngokusekelwe kuwo, yakha amagrafu akho futhi uwangeze ekuqapheni kwakho.

Izicelo eziyisibonelo:
http://bit.do/dataegret_sql
http://bit.do/lesovsky_sql

Lena inqolobane yethu yebhizinisi futhi eyami. Aqukethe imibuzo eyisibonelo. Ayikho imibuzo evela kokukhethiwe* ochungechungeni lapho. Kukhona imibuzo eseyenziwe kakade ngokujoyina, kusetshenziswa imisebenzi ethokozisayo ekuvumela ukuthi uguqule izinombolo ezingahluziwe zibe amanani afundekayo, alula, i.e. lawa amabhayithi, isikhathi. Ungawathatha, uwabuke, uwahlaziye, uwangeze ekuqaphelweni kwakho, wakhe ukuqapha kwakho ngokusekelwe kukho.

Imibuzo yakho

Umbuzo: Uthe ngeke ukhangise izinhlobo, kodwa ngisafuna ukwazi - hlobo luni lwamadeshibhodi owasebenzisayo kumaphrojekthi akho?
Impendulo: Kuyahlukahluka. Kuyenzeka sifike ekhasimendeni libe selinalo elalo lokuqapha. Futhi seluleka ikhasimende ngalokho okudingeka kwengezwe ekuqaphelweni kwalo. Isimo esibi kakhulu sikuZabbix. Ngoba ayinalo ikhono lokwakha amagrafu we-TopN. Thina ngokwethu sisebenzisa I-Okmeter, ngoba besibonisana nalaba bafana ngokugadwa. Baqaphe i-PostgreSQL ngokusekelwe ekucacisweni kwethu kobuchwepheshe. Ngibhala eyami iphrojekthi yesilwane, eqoqa idatha nge-Prometheus futhi iyisebenzise UGrafana. Umsebenzi wami uwukwakha owami umthumeli ngaphandle e-Prometheus bese nginikeza yonke into e-Grafana.

Umbuzo: Ingabe akhona ama-analogue emibiko ye-AWR noma... ukuhlanganisa? Ingabe uyazi ngento efana nale?
Impendulo: Yebo, ngiyayazi ukuthi iyini i-AWR, yinto emnandi. Okwamanje kunezinhlobonhlobo zamabhayisikili asebenzisa cishe imodeli elandelayo. Ngesinye isikhathi, ezinye izisekelo zibhalwa ku-PostgreSQL efanayo noma endaweni yokugcina ehlukile. Ungazi-google ku-inthanethi, zikhona. Omunye wabathuthukisi bento enjalo uhlezi kuforamu ye-sql.ru kuntambo ye-PostgreSQL. Ungambamba lapho. Yebo, zikhona izinto ezinjalo, zingasetshenziswa. Plus in its pgCenter Ngibhala futhi into ekuvumela ukuthi wenze into efanayo.

PS1 Uma usebenzisa postgres_exporter, iyiphi ideshibhodi oyisebenzisayo? Kuneziningana zazo. Sebevele baphelelwe yisikhathi. Mhlawumbe umphakathi uzodala isifanekiso esibuyekeziwe?

I-PS2 Ikhishiwe i-pganalyze ngoba iwukunikezwa kwe-SaaS yobunikazi egxile ekuqaphelweni kokusebenza kanye neziphakamiso zokushuna ezizenzakalelayo.

Abasebenzisi ababhalisiwe kuphela abangabamba iqhaza kuhlolovo. Ngena ngemvume, wamukelekile.

Ikuphi ukuqapha okuzibambele wena kwe-postgresql (ngedeshibhodi) okubona kungcono kakhulu?

• 30,0%I-Zabbix + izengezo ezivela ku-Alexey Lesovsky noma zabbix 4.4 noma i-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-pganalyze iyi-SaaS yokuphathelene - angikwazi ukuyisusa1

• 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

Bangu-10 abasebenzisi abavotile. Abasebenzisi abangu-26 bagobile.

Source: www.habr.com