🥇Zijule ezibalweni zangaphakathi ze-PostgreSQL. Alexey Lesovsky

Okulotshiweyo kombiko ka-Alexey Lesovsky ka-2015 "Ukujula ku-PostgreSQL izibalo zangaphakathi"

Umshwana wokuzihlangula ovela kumbhali wombiko: Ngiyaqaphela ukuthi lo mbiko wangomhla kaNovemba 2015 - sekudlule iminyaka engaphezu kwe-4 futhi isikhathi esiningi sesidlulile. Inguqulo 9.4 okukhulunywe ngayo embikweni ayisasekelwa. Eminyakeni engu-4 edlule, kukhishwe okusha okungu-5 lapho kunokuqanjwa okuningi, ukuthuthukiswa kanye nezinguquko eziphathelene nezibalo, futhi okunye okokusebenza kuphelelwe yisikhathi futhi akubalulekile. Njengoba ngibuyekeza, ngizamile ukumaka lezi zindawo ukuze ngingadukisi umfundi. Angizange ngizibhale kabusha lezi zigaba, ziningi futhi umphumela uzoba umbiko ohluke ngokuphelele.

I-PostgreSQL DBMS iwumshini omkhulu, futhi lo mshini uqukethe amasistimu angaphansi amaningi, ukusebenza okudidiyelwe okuthinta ngqo ukusebenza kwe-DBMS. Ngesikhathi sokusebenza, izibalo kanye nolwazi mayelana nokusebenza kwezingxenye kuyaqoqwa, okukuvumela ukuthi uhlole ukusebenza kahle kwe-PostgreSQL futhi uthathe izinyathelo zokuthuthukisa ukusebenza. Nokho, luningi lolu lwazi futhi lwethulwa ngendlela enziwe lula. Ukucubungula lolu lwazi nokulutolika kwesinye isikhathi kuwumsebenzi ongeyona into encane, futhi “izu” yamathuluzi nezinsiza kungadida kalula ngisho ne-DBA ethuthukisiwe.

Sawubona Igama lami ngingu-Aleksey. Njengoba u-Ilya eshilo, ngizokhuluma ngezibalo ze-PostgreSQL.

Izibalo zomsebenzi we-PostgreSQL. I-PostgreSQL inezibalo ezimbili. Izibalo zomsebenzi okuzoxoxwa ngazo. Nezibalo zomhleli mayelana nokusatshalaliswa kwedatha. Ngizokhuluma ngokuqondile ngezibalo zomsebenzi we-PostgreSQL, ezisivumela ukuthi sahlulele ukusebenza futhi ngandlela-thile sikuthuthukise.

Ngizokutshela ukuthi ungasebenzisa kanjani ngempumelelo izibalo ukuxazulula izinkinga ezihlukahlukene onazo noma ongase ube nazo.

Yini engeke ibe embikweni? Embikweni ngeke ngithinte izibalo zomhleli, ngoba... Lesi isihloko esihlukile sombiko ohlukile wokuthi idatha igcinwa kanjani kusizindalwazi kanye nendlela umhleli wemibuzo ewuthola ngayo umbono wezimpawu zekhwalithi nenani zale datha.

Futhi ngeke kube nokubuyekezwa kwamathuluzi, ngeke ngiqhathanise umkhiqizo owodwa nomunye. Ngeke kube khona ukukhangisa. Ake sikubeke eceleni lokho.

Ngifuna ukukukhombisa ukuthi ukusebenzisa izibalo kuyasiza. Kudingekile. Kuphephile ukusebenzisa. Esikudingayo yi-SQL ejwayelekile kanye nolwazi oluyisisekelo lwe-SQL.

Futhi ake sikhulume ngokuthi yiziphi izibalo ongakhetha kuzo ukuxazulula izinkinga.

Uma sibheka i-PostgreSQL futhi sisebenzisa umyalo ohlelweni lokusebenza ukubuka izinqubo, sizobona "ibhokisi elimnyama". Sizobona izinqubo ezithile ezenza okuthile, futhi kusukela egameni singacabanga cishe ukuthi zenzani lapho, ukuthi zenzani. Kodwa, empeleni, yibhokisi elimnyama, asikwazi ukubheka ngaphakathi.

Singabona ukulayishwa kwe-CPU ngaphakathi top, singabheka ukusetshenziswa kwememori kwezinye izinsiza zesistimu, kodwa ngeke sikwazi ukubheka ngaphakathi kwe-PostgreSQL. Kulokhu sidinga amanye amathuluzi.

Futhi ngokuqhubekayo, ngizokutshela ukuthi isikhathi sichithwa kuphi. Uma sicabanga i-PostgreSQL ngendlela yomdwebo onjalo, khona-ke singaphendula lapho isikhathi sichithwa khona. Lezi izinto ezimbili: ukucubungula izicelo zeklayenti ezivela kuzinhlelo zokusebenza kanye nemisebenzi yangemuva eyenziwa yi-PostgreSQL ukuze izigcine isebenza.

Uma siqala ukubuka ekhoneni eliphezulu kwesokunxele, singabona ukuthi izicelo zamaklayenti zicutshungulwa kanjani. Isicelo sivela kuhlelo lokusebenza futhi iseshini yeklayenti ivulwa ukuze kuqhutshekwe nomsebenzi. Isicelo sithunyelwa kusihleli. Isihleli sakha uhlelo lombuzo. Ithumela futhi ukuze isetshenziswe. Kukhona uhlobo oluthile lokufaka/okukhiphayo kwedatha ehlobene namathebula nezinkomba. Idatha edingekayo ifundwa kusuka kumadiski ukuya enkumbulweni endaweni ekhethekile "ama-buffers abiwe". Imiphumela yesicelo, uma iyizibuyekezo, ezisusiwe, zirekhodwa kulogi yokwenziwe ku-WAL. Olunye ulwazi lwezibalo lugcina kulogi noma umqoqi wezibalo. Futhi umphumela wesicelo ubuyiselwa kuklayenti. Ngemva kwalokho iklayenti lingaphinda yonke into futhi ngesicelo esisha.

Kuthiwani ngemisebenzi yangemuva nezinqubo zangemuva? Sinezinqubo ezimbalwa ezigcina isizindalwazi sisebenza ngemodi evamile yokusebenza. Lezi zinqubo zizothintwa futhi embikweni: i-autovacuum, i-checkpointer, izinqubo ezihlobene nokuphindaphinda, umbhali wangemuva. Ngizothinta ngamunye wabo njengoba ngibika.

Yiziphi izinkinga ezikhona ngezibalo?

Kunolwazi oluningi. I-PostgreSQL 9.4 inikeza amamethrikhi angu-109 okubuka idatha yezibalo. Kodwa-ke, uma isizindalwazi sigcina amathebula amaningi, izikimu, isizindalwazi, khona-ke wonke lawa mamethrikhi kuzodingeka aphindwe ngenombolo ehambisanayo yamathebula, izizindalwazi. Okusho ukuthi, kukhona ulwazi olwengeziwe. Futhi kulula kakhulu ukuminza kuwo.
Inkinga elandelayo ukuthi izibalo zimelelwa ngokubala. Uma sibheka lezi zibalo, sizobona amanani akhuphuka njalo. Futhi uma sekudlule isikhathi esiningi kusukela izibalo zisethwe kabusha, sizobona amanani ngezigidigidi. Futhi abasitsheli lutho.
Ayikho indaba. Uma ube nohlobo oluthile lokuhluleka, okuthile kwawa emaminithini angu-15-30 edlule, ngeke ukwazi ukusebenzisa izibalo futhi ubone ukuthi kwenzekeni emaminithini angu-15-30 edlule. Lokhu kuyinkinga.
Ukuntuleka kwethuluzi elakhelwe ku-PostgreSQL kuyinkinga. Abathuthukisi be-kernel abanikezeli nganoma iyiphi insiza. Abanayo into enjalo. Bamane banikeze izibalo kusizindalwazi. Yisebenzise, yenza isicelo kuyo, yenza noma yini oyifunayo.
Njengoba lingekho ithuluzi elakhelwe ku-PostgreSQL, lokhu kubangela enye inkinga. Amathuluzi amaningi ezinkampani zangaphandle. Yonke inkampani enezandla eziqondile noma ezingaphansi izama ukubhala uhlelo lwayo. Futhi ngenxa yalokho, umphakathi unamathuluzi amaningi angasetshenziswa ukusebenza ngezibalo. Futhi amanye amathuluzi anamakhono athile, amanye amathuluzi awanawo amanye amakhono, noma kukhona amakhono amasha. Futhi kuvela isimo sokuthi udinga ukusebenzisa amathuluzi amabili, amathathu noma amane adlulanayo futhi anemisebenzi ehlukene. Lokhu akujabulisi neze.

Yini elandelayo kulokhu? Kubalulekile ukwazi ukuthatha izibalo ngokuqondile, ukuze unganciki ezinhlelweni, noma uthuthukise lezi zinhlelo ngandlela thile ngokwakho: engeza imisebenzi ethile ukuze uthole inzuzo yakho.

Futhi udinga ulwazi oluyisisekelo lwe-SQL. Ukuze uthole idatha ethile kwizibalo, udinga ukudala imibuzo ye-SQL, okungukuthi udinga ukwazi ukuthi ukukhetha nokujoyina kuhlanganiswa kanjani.

Izibalo zisitshela izinto ezimbalwa. Zingahlukaniswa ngezigaba.

Isigaba sokuqala izehlakalo ezenzeka kusizindalwazi. Kulapho umcimbi othile uvela kusizindalwazi: isicelo, ukufinyelela etafuleni, i-autovacuum, ukuzibophezela, bese zonke lezi izehlakalo. Izibali ezihambisana nalezi zenzakalo ziyandiswa. Futhi singakwazi ukulandelela le micimbi.
Isigaba sesibili yizimpawu zezinto ezifana namathebula kanye nesizindalwazi. Banezakhiwo. Lona ubukhulu bamatafula. Singakwazi ukulandelela ukukhula kwamathebula kanye nokukhula kwezinkomba. Singabona izinguquko ku-dynamics.
Futhi isigaba sesithathu yisikhathi esichithwa emcimbini. Isicelo umcimbi. Inesilinganiso sayo esiqondile sobude besikhathi. Iqale lapha, yaphelela lapha. Singakwazi ukuyilandelela. Kuphakathi kokuthi kuthatha isikhathi ukufunda ibhulokhi kudiski noma ukuyibhala. Izinto ezinjalo nazo zigadwa.

Imithombo yezibalo yethulwa kanje:

Kwinkumbulo eyabiwe (izigcinalwazi ezabiwe) kunengxenye yokugcina idatha emile, kukhona nalezo zibali ezihlala zikhuliswa lapho izenzakalo ezithile zenzeka, noma ezinye izikhathi ziphakama ekusebenzeni kwesizindalwazi.
Zonke lezi zibali azifinyeleleki kumsebenzisi futhi azifinyeleleki nakumlawuli. Lezi yizinto ezisezingeni eliphansi. Ukuze ufinyelele kuzo, i-PostgreSQL inikeza isikhombimsebenzisi ngendlela yemisebenzi ye-SQL. Singenza ukukhetha kokuphonswa sisebenzisa le misebenzi futhi sithole uhlobo oluthile lwemethrikhi (noma isethi yamamethrikhi).
Nokho, ukusebenzisa le misebenzi akulula ngaso sonke isikhathi, ngakho-ke imisebenzi iyisisekelo sokubuka (IMIBONO). Lawa amathebula abonakalayo ahlinzeka ngezibalo kusistimu engaphansi ethile, noma kusethi ethile yemicimbi kusizindalwazi.
Lokhu kubuka okushumekiwe (UKUBUKA) kuyisixhumi esibonakalayo esiyinhloko sokusebenza ngezibalo. Zitholakala ngokuzenzakalelayo ngaphandle kwanoma yiziphi izilungiselelo ezengeziwe, ungazisebenzisa ngokushesha, uzibuke, futhi uthathe ulwazi kuzo. Bese kuba khona ama-contribs. Iminikelo isemthethweni. Ungakwazi ukufaka iphakheji ye-postgresql-contrib (isibonelo, i-postgresql94-contrib), ulayishe imojuli edingekayo ekucushweni, ucacise imingcele yayo, uqale kabusha i-PostgreSQL futhi ungayisebenzisa. (Qaphela. Ngokuya ngokusabalalisa, ezinguqulweni zakamuva iphakheji yokunikela iyingxenye yephakheji eyinhloko).
Futhi kukhona iminikelo engekho emthethweni. Azifakiwe ekusabalazweni okujwayelekile kwe-PostgreSQL. Kufanele zihlanganiswe noma zifakwe njengomtapo wolwazi. Izinketho zingahluka kakhulu, kuye ngokuthi umthuthukisi walo mnikelo ongekho emthethweni ufike nakho.

Lesi silayidi sethula yonke leyo BUKA kanye neminye yemisebenzi etholakala ku-PostgreSQL 9.4. Njengoba sibona, maningi. Futhi kulula ukudideka uma uhlangana nakho okokuqala.

Nokho, uma sithatha isithombe esandulele Как тратится время на PostgreSQL futhi iyahambisana nalolu hlu, sithola lesi sithombe. Singasebenzisa ukubuka ngakunye (UKUBUKA) noma umsebenzi ngamunye ngenjongo eyodwa noma enye ukuze sithole izibalo ezihambisanayo lapho i-PostgreSQL isebenza. Futhi singakwazi kakade ukuthola ulwazi mayelana nokusebenza kwe-subsystem.

Into yokuqala esizoyibheka pg_stat_database. Njengoba sibona, lokhu kuwumsebenzi. Kunolwazi oluningi kuyo. Ulwazi oluhlukahlukene kakhulu. Futhi inikeza ulwazi oluwusizo kakhulu lwalokho okwenzeka kusizindalwazi sethu.

Yiziphi izinto eziwusizo esingazithatha lapho? Ake siqale ngezinto ezilula.

select
sum(blks_hit)*100/sum(blks_hit+blks_read) as hit_ratio
from pg_stat_database;

Into yokuqala esingayibheka yiphesenti le-cache hit. Izinga lokushaywa kwenqolobane liyimethrikhi ewusizo. Ikuvumela ukuthi ulinganise ukuthi ingakanani idatha ethathwa kunqolobane yama-buffers okwabelwana ngayo nokuthi ingakanani efundwa kudiski.

Kuyacaca lokho amahithi enqolobane amaningi esinawo, ngcono. Sikala le metric njengephesenti. Futhi, isibonelo, uma iphesenti lethu lalokhu kushaywa kwenqolobane lingaphezu kuka-90%, lokhu kuhle. Uma yehla ngaphansi kuka-90%, kusho ukuthi asinayo inkumbulo eyanele yokubamba inhloko eshisayo yedatha kumemori. Futhi ukuze usebenzise le datha, i-PostgreSQL iyaphoqeleka ukuthi ifinyelele idiski futhi lokhu kuhamba kancane uma ngabe idatha ibifundwa kumemori. Futhi udinga ukucabanga ngokwandisa inkumbulo: noma wandise izigcinalwazi ezabiwe, noma wandise imemori yehadiwe (RAM).

select
datname,
(xact_commit*100)/(xact_commit+xact_rollback) as c_ratio,
deadlocks, conflicts,
temp_file, pg_size_pretty(temp_bytes) as temp_size
from pg_stat_database;

Yini enye ongayithatha kulokhu kusebenza? Ungabona okudidayo okwenzeka kusizindalwazi. Yini eboniswa lapha? Kukhona ukuzibophezela, ukuhlehliswa emuva, ukwakhiwa kwamafayela esikhashana, ubukhulu bawo, ama-deadlocks kanye nokungqubuzana.

Singasisebenzisa lesi sicelo. Le SQL ilula kakhulu. Futhi singabheka le datha lapha.

Futhi nanka amanani omkhawulo. Sibheka isilinganiso sokuzibophezela nokubuyisela emuva. Ukuzibophezela kuwukuqinisekisa okuyimpumelelo komsebenzi. I-Rollbacks iwukubuyisela emuva, okungukuthi umsebenzi wenze umsebenzi othile, wahlunga imininingo egciniwe, wabala okuthile, kwase kuba ukwehluleka futhi imiphumela yokwenziwe ilahlwa. Leyo inani lama-rollbacks elikhuphuka njalo libi. Futhi kufanele ngandlela thize uzigweme, futhi uhlele ikhodi ukuze lokhu kungenzeki.

Izingxabano zihlobene nokuphindaphinda. Futhi kufanele futhi zigwenywe. Uma unemibuzo ethile eyenziwa esifanekisweni futhi kuvela izingxabano, kuzomele uxazulule lezi zingxabano futhi ubone ukuthi kwenzekani. Imininingwane ingatholakala kulogi. Futhi susa izimo zokungqubuzana ukuze izicelo zohlelo lokusebenza zisebenze ngaphandle kwamaphutha.

I-Deadlocks nayo iyisimo esibi. Lapho izicelo zilwela izinsiza, isicelo esisodwa safinyelela insiza eyodwa sathatha isikhiya, isicelo sesibili safinyelela esisetshenziswa sesibili saphinde sathatha isikhiya, kwase kuthi zombili izicelo zafinyelela izinsiza zomunye futhi zavinjwa ngesikhathi sisalinde umakhelwane ukuthi akhulule ilokhi. Lesi futhi isimo esiyinkinga. Zidinga ukubhekwana nazo ezingeni lokubhala kabusha izinhlelo zokusebenza kanye nokufinyelela kwe-serializing kuzinsiza. Futhi uma ubona ukuthi ama-deadlocks akho akhula njalo, udinga ukubheka imininingwane egciniwe, uhlaziye izimo ezivelayo futhi ubone ukuthi inkinga ikuphi.

Amafayela esikhashana (temp_files) nawo mabi. Uma isicelo somsebenzisi singenayo inkumbulo eyanele yokufaka idatha yokusebenza, yesikhashana, kwakha ifayela kudiski. Futhi yonke imisebenzi ebingayenza ku-buffer yesikhashana kumemori iqala ukwenziwa kudiski. Ihamba kancane. Lokhu kwandisa isikhathi sokwenza umbuzo. Futhi iklayenti elithumele isicelo ku-PostgreSQL lizothola impendulo ngemva kwesikhashana. Uma yonke le misebenzi yenziwa ngenkumbulo, i-Postgres izophendula ngokushesha okukhulu futhi iklayenti lizolinda kancane.

Pg_stat_bgwriter - Lokhu kubuka kuchaza ukusebenza kwamasistimu angaphansi amabili angemuva e-PostgreSQL: lokhu checkpointer и background writer.

Okokuqala, ake sibheke amaphuzu okulawula, okuthiwa. checkpoints. Ayini amaphuzu okulawula? Indawo yokuhlola indawo kulogi yokwenziwe ebonisa ukuthi zonke izinguquko zedatha ezirekhodwe kulogi zivunyelaniswe ngempumelelo nedatha ekudiski. Inqubo, kuye ngomthwalo womsebenzi nezilungiselelo, ingaba yinde futhi ikakhulukazi iqukethe ukuvumelanisa amakhasi angcolile kumabhafa okwabelwana ngawo namafayela edatha kudiski. Kwenzelweni? Uma i-PostgreSQL ibihlala ifinyelela idiski futhi ilanda idatha lapho, futhi ibhala idatha ekufinyeleleni ngakunye, bekuzohamba kancane. Ngakho-ke, i-PostgreSQL inengxenye yememori usayizi wayo oncike kuzilungiselelo ekucushweni. I-Postgres igcina idatha ebukhoma kule nkumbulo ukuze icutshungulwe kamuva noma ibuzwe. Esimeni sezicelo zokushintsha idatha, iyashintshwa. Futhi sithola izinguqulo ezimbili zedatha. Enye isenkumbulweni yethu, enye ikudiski. Futhi ngezikhathi ezithile udinga ukuvumelanisa le datha. Sidinga ukuvumelanisa lokho okushintshiwe enkumbulweni kuya kudiski. Ukuze wenze lokhu udinga izindawo zokuhlola.

Indawo yokuhlola idlula kumabhafa okwabelwana ngawo, imaka amakhasi angcolile adingekayo ukuze kuhlolwe. Bese yethula ukudlula kwesibili kumabhafa okwabelwana ngawo. Futhi amakhasi amakelwe indawo yokuhlola, isivele iwavumelanisa. Ngale ndlela idatha ivumelaniswa nediski.

Kunezinhlobo ezimbili zezindawo zokuhlola. Indawo yokuhlola eyodwa isetshenziswa ngokuvala isikhathi. Le ndawo yokuhlola iwusizo futhi inhle - checkpoint_timed. Futhi kunezindawo zokuhlola lapho kudingeka - checkpoint required. Le ndawo yokuhlola yenzeka uma sinerekhodi ledatha elikhulu kakhulu. Sirekhode amalogi amaningi okwenziwe. Futhi i-PostgreSQL ikholelwa ukuthi idinga ukuvumelanisa konke lokhu ngokushesha okukhulu, yenza indawo yokuhlola bese uqhubekela phambili.

Futhi uma ubheka izibalo pg_stat_bgwriter futhi wabona lokho onakho i-checkpoint_req inkulu kakhulu kune-checkpoint_timed, bese lokhu kubi. Kungani kubi? Lokhu kusho ukuthi i-PostgreSQL ingaphansi kwengcindezi eqhubekayo lapho idinga ukubhala idatha kudiski. Indawo yokuhlola ukuphela kwesikhathi ayicindezeli kangako futhi yenziwa ngokuya ngeshejuli yangaphakathi futhi ifana nokusabalalisa ngokuhamba kwesikhathi. I-PostgreSQL inamandla okumisa isikhashana umsebenzi futhi ingacindezeli isistimu engaphansi yediski. Lokhu kuwusizo ku-PostgreSQL. Futhi imibuzo eyenziwa ngesikhathi sokuhlola ngeke ibhekane nengcindezi yokuthi i-subsystem yediski imatasa.

Futhi ukulungisa indawo yokuhlola kunemingcele emithathu:

сheckpoint_segments.
сheckpoint_timeout.
сheckpoint_competion_target.

Bakuvumela ukuthi ulawule ukusebenza kwamaphoyinti okulawula. Kodwa ngeke ngigxile kuzo. Ithonya labo liyisihloko esihlukile.

Isexwayiso: Inguqulo 9.4 okukhulunywe ngayo embikweni ayisasebenzi. Ezinguqulweni zanamuhla ze-PostgreSQL ipharamitha checkpoint_segments kufakwe amapharamitha esikhundleni min_wal_size и max_wal_size.

Isistimu engaphansi elandelayo ingumbhali wangemuva − background writer. Wenzani? Igijima njalo ku-loop engapheli. Iskena amakhasi kumabhafa abiwe futhi ilahle amakhasi angcolile ewathola kudiski. Ngakho-ke, kusiza isikhombi sokuhlola ukuthi senze umsebenzi omncane ngesikhathi sokuhlolwa kwendawo yokuhlola.

Yini enye edingekayo? Ihlinzeka ngesidingo samakhasi angenalutho kumabhafa okwabelwana ngawo uma edingeka ngokuzumayo (ngobuningi futhi ngokushesha) ukuze kufakwe idatha. Ake sithi kuvele isimo lapho amakhasi angenalutho edingeka ukuze kuqedelwe isicelo futhi asevele ekuzigcinalwazi okwabelwana ngazo. I-Postgresive backend avele azithathe azisebenzise, asikho isidingo sokuthi ahlanze lutho yena. Kodwa uma kungazelelwe awekho amakhasi anjalo, i-backend iyamisa umsebenzi futhi iqala ukucinga amakhasi ukuze iwalahle kudiski futhi iwathathele izidingo zayo - okuthinta kabi isikhathi sesicelo esenziwayo njengamanje. Uma ubona ukuthi unepharamitha maxwritten_clean enkulu, lokhu kusho ukuthi umbhali wangemuva akawenzi umsebenzi wawo futhi udinga ukwandisa amapharamitha bgwriter_lru_maxpages, ukuze enze umsebenzi owengeziwe emjikelezweni owodwa, asule amakhasi engeziwe.

Futhi enye inkomba ewusizo kakhulu buffers_backend_fsync. I-backends ayihambisani ngoba ihamba kancane. Badlula i-fsync up the IO stack checkpointer. Isikhombi sokuhlola sinomugqa waso, sisebenza ngezikhathi ezithile i-fsync futhi sivumelanisa amakhasi enkumbulweni namafayela akudiski. Uma ulayini ku-checkpointer mkhulu futhi ugcwele, indawo engemuva iyaphoqeleka ukuthi izenzele i-fsync futhi lokhu kunciphisa umsebenzi we-backend., i.e. iklayenti lizothola impendulo ngemva kwesikhathi kunokuba belingathola. Uma ubona ukuthi inani lakho likhulu kunoziro, lokhu sekuvele kuyinkinga futhi udinga ukunaka izilungiselelo zombhali wangemuva futhi uhlole nokusebenza kwe-subsystem yediski.

Isexwayiso: _Umbhalo olandelayo uchaza imibono yezibalo ehlobene nokuphindaphinda. Iningi lokubuka kanye namagama emisebenzi aqanjwe kabusha ku-Postgres 10. Umongo wokuqamba kabusha kwakuwukuthatha indawo xlog on wal и location on lsn emsebenzini/buka amagama, njll. Isibonelo esikhethekile, umsebenzi pg_xlog_location_diff() yaqanjwa kabusha yaba pg_wal_lsn_diff()._

Sinezinto eziningi lapha nathi. Kodwa sidinga kuphela izinto ezihlobene nendawo.

Uma sibona ukuthi wonke amanani ayalingana, khona-ke lena inketho ekahle futhi isifaniso asisali ngemuva kwenkosi.

Lesi simo se-hexadecimal lapha indawo kulogi yokwenziwe. Ikhula njalo uma kukhona umsebenzi ku-database: ukufakwa, ukususa, njll.

сколько записано xlog в байтах
$ select
pg_xlog_location_diff(pg_current_xlog_location(),'0/00000000');
лаг репликации в байтах
$ select
client_addr,
pg_xlog_location_diff(pg_current_xlog_location(), replay_location)
from pg_stat_replication;
лаг репликации в секундах
$ select
extract(epoch from now() - pg_last_xact_replay_timestamp());

Uma lezi zinto zihlukile, khona-ke kukhona uhlobo oluthile lwe-lag. I-Lag i-lag phakathi kwe-replica ne-master, i.e. idatha iyahluka phakathi kwamaseva.

Kunezizathu ezintathu ze-lag:

Lolu hlelo oluncane lwediski alukwazi ukubhekana nokuvumelanisa ifayela lokurekhoda.
Lawa amaphutha enethiwekhi angenzeka, noma ukugcwala ngokweqile kwenethiwekhi, lapho idatha ingenaso isikhathi sokufinyelela isifaniso futhi ingakwazi ukuyikhiqiza kabusha.
Futhi iprosesa. Iprosesa yicala elingavamile kakhulu. Futhi ngakubona lokhu izikhathi ezimbili noma ezintathu, kodwa lokhu kungenzeka futhi.

Futhi nansi imibuzo emithathu esivumela ukuthi sisebenzise izibalo. Singalinganisela ukuthi sirekhode malini elogu lokwenziwe. Kukhona umsebenzi onjalo pg_xlog_location_diff futhi singakwazi ukulinganisa i-lag yokuphindaphinda ngamabhayithi namasekhondi. Futhi sisebenzisa inani elivela kulokhu kubuka (IMIBONO) kulokhu.

Qaphela: _Esikhundleni sokuthi pg_xlog_locationUmsebenzi we-diff() ungasebenzisa u-opharetha wokukhipha futhi ukhiphe indawo eyodwa kwenye. Ukhululekile.

Kukhona iphuzu elilodwa nge-lag, okuyimizuzwana. Uma kungekho msebenzi ku-master, ukuthengiselana bekukhona cishe emaminithini angu-15 edlule futhi akukho msebenzi, futhi uma sibheka lesi sikhala esifanekiselweni, sizobona i-lag yemizuzu engu-15. Lokhu kufanelekile ukukhumbula. Futhi lokhu kungase kudide uma ubuka lesi siga.

Pg_stat_all_tables okunye ukubuka okuwusizo. Ibonisa izibalo ematafuleni. Uma sinamathebula kusizindalwazi, kunomsebenzi othile ngayo, ezinye izenzo, singathola lolu lwazi kulokhu kubuka.

select
relname,
pg_size_pretty(pg_relation_size(relname::regclass)) as size,
seq_scan, seq_tup_read,
seq_scan / seq_tup_read as seq_tup_avg
from pg_stat_user_tables
where seq_tup_read > 0 order by 3,4 desc limit 5;

Into yokuqala esingayibheka ukuskena okulandelanayo kulo lonke ithebula. Inombolo ngokwayo ngemva kwalawa maphasi ayimbi ngempela futhi ayiyona inkomba yokuthi kudingeka senze okuthile.

Nokho, kukhona imethrikhi yesibili - seq_tup_read. Leli inani lemigqa elibuyiswe kusukela kuskena esilandelanayo. Uma inani elimaphakathi lidlula i-1, 000, 10, 000, khona-ke lokhu sekuvele kuyinkomba yokuthi mhlawumbe udinga ukwakha inkomba endaweni ethile ukuze imibuzo isekelwe kunkomba, noma kungenzeka ukukhulisa imibuzo esebenzisa ukuskena okulandelanayo ukuthi lokhu akwenzeki kwaba.

Isibonelo esilula - ake sithi isicelo esinezindleko ezinkulu ze-OFFSET kanye ne-LIMIT. Isibonelo, imigqa engu-100 kuthebula iyaskeniwa futhi ngemva kwalokho kuthathwa imigqa edingekayo engu-000, futhi imigqa eskeniwe yangaphambilini iyalahlwa. Leli futhi icala elibi. Futhi imibuzo enjalo idinga ukulungiswa. Futhi nawu umbuzo olula we-SQL lapho ungabheka khona lokhu futhi uhlole izinombolo eziwumphumela.

select
relname,
pg_size_pretty(pg_total_relation_size(relname::regclass)) as
full_size,
pg_size_pretty(pg_relation_size(relname::regclass)) as
table_size,
pg_size_pretty(pg_total_relation_size(relname::regclass) -
pg_relation_size(relname::regclass)) as index_size
from pg_stat_user_tables
order by pg_total_relation_size(relname::regclass) desc limit 10;

Osayizi bamathebula bangatholwa kusetshenziswa leli thebula futhi kusetshenziswa imisebenzi eyengeziwe pg_total_relation_size(), pg_relation_size().

Ngokuvamile, kukhona ama-metacommands dt и di, engasetshenziswa ku-PSQL futhi iphinde ibuke osayizi bamathebula nezinkomba.

Kodwa-ke, ukusebenzisa imisebenzi kusisiza ukuthi sibheke osayizi bamathebula, sicabangele ngisho nezinkomba, noma ngaphandle kokucabangela izinkomba, futhi senze ezinye izilinganiso ezisuselwe ekukhuleni kwesizindalwazi, okungukuthi, likhula kanjani, ngaluphi umfutho, futhi finyelela iziphetho ezithile mayelana nokwenza kahle kosayizi.

Umsebenzi wokuqopha. Kuyini ukuqopha? Ake sibheke ukuhlinzwa UPDATE – ukusebenza kwemigqa yokubuyekeza etafuleni. Eqinisweni, ukuvuselela kuyimisebenzi emibili (noma ngisho nangaphezulu). Lokhu kufaka inguqulo entsha yomugqa futhi kumaka inguqulo endala yomugqa njengephelelwe yisikhathi. Ngokulandelayo, i-autovacuum izofika futhi ihlanze lezi zinguqulo eziphelelwe yisikhathi zemigqa, imaka le ndawo njengetholakalela ukusetshenziswa futhi.

Ngaphezu kwalokho, isibuyekezo asikona nje kuphela ukubuyekeza ithebula. Lesi futhi isibuyekezo senkomba. Uma unezinkomba eziningi etafuleni, ngesikhathi sokubuyekeza zonke izinkomba ezifaka izinkambu ezibuyekeziwe embuzweni nazo zizodinga ukubuyekezwa. Lezi zinkomba zizophinda zibe nezinguqulo ezindala zemigqa ezizodinga ukuhlanzwa.

select
s.relname,
pg_size_pretty(pg_relation_size(relid)),
coalesce(n_tup_ins,0) + 2 * coalesce(n_tup_upd,0) -
coalesce(n_tup_hot_upd,0) + coalesce(n_tup_del,0) AS total_writes,
(coalesce(n_tup_hot_upd,0)::float * 100 / (case when n_tup_upd > 0
then n_tup_upd else 1 end)::float)::numeric(10,2) AS hot_rate,
(select v[1] FROM regexp_matches(reloptions::text,E'fillfactor=(\d+)') as
r(v) limit 1) AS fillfactor
from pg_stat_all_tables s
join pg_class c ON c.oid=relid
order by total_writes desc limit 50;

Futhi ngenxa yedizayini yayo entsha, UPDATE kuwumsebenzi osindayo. Kodwa zingenziwa zibe lula. Yidla hot updates. Bavele ku-PostgreSQL version 8.3. Futhi kuyini lokhu? Lesi isibuyekezo esingasindi esingabangeli ukuthi izinkomba zakhiwe kabusha. Okusho ukuthi, sibuyekeze irekhodi, kodwa irekhodi kuphela ekhasini (okungeletafula) elibuyekeziwe, futhi izinkomba zisakhomba irekhodi elifanayo ekhasini. Kunomqondo wokusebenza othokozisayo omncane: uma kufika indawo engenalutho, idala la maketango hot yakha kabusha futhi yonke into iyaqhubeka nokusebenza ngaphandle kokubuyekeza izinkomba, futhi yonke into yenzeka ngokumosha okuncane kwezinsiza.

Futhi nini n_tup_hot_upd enkulu, ke kuhle kakhulu. Lokhu kusho ukuthi izibuyekezo ezingasindi ziningi futhi lokhu kushibhile kithi ngokwezinsiza futhi konke kuhamba kahle.

ALTER TABLE table_name SET (fillfactor = 70);

Indlela yokwandisa ivolumu hot updateov? Singasebenzisa fillfactor. Inquma usayizi wesikhala samahhala esigodliwe lapho ugcwalisa ikhasi kuthebula usebenzisa okuthi INSERT. Lapho okufakiwe kwengezwa etafuleni, kugcwalisa ikhasi ngokuphelele futhi kungashiyi isikhala esingenalutho. Bese kugqanyiswa ikhasi elisha. Idatha igcwaliswa futhi. Futhi lokhu ukuziphatha okuzenzakalelayo, i-fillfactor = 100%.

Singenza i-fillfactor ibe ngu-70%. Okusho ukuthi, ngesikhathi sokufakwa, ikhasi elisha lagqanyiswa, kodwa kwagcwaliswa amaphesenti angama-70 kuphela ekhasi. Futhi sisele no-30% njengobekelwe. Uma udinga ukwenza isibuyekezo, cishe kuzokwenzeka ekhasini elifanayo, futhi inguqulo entsha yomugqa izongena ekhasini elifanayo. Futhi ukubuyekezwa_okushisayo kuzokwenziwa. Lokhu kwenza kube lula ukubhala emathebulani.

select c.relname,
current_setting('autovacuum_vacuum_threshold') as av_base_thresh,
current_setting('autovacuum_vacuum_scale_factor') as av_scale_factor,
(current_setting('autovacuum_vacuum_threshold')::int +
(current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples))
as av_thresh,
s.n_dead_tup
from pg_stat_user_tables s join pg_class c ON s.relname = c.relname
where s.n_dead_tup > (current_setting('autovacuum_vacuum_threshold')::int
+ (current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples));

Ulayini we-autovacuum. I-Autovacuum iyisistimu engaphansi lapho kunezibalo ezimbalwa kakhulu ku-PostgreSQL. Singabona kuphela kumathebula ku-pg_stat_activity ukuthi mangaki ama-vacuum esinawo njengamanje. Nokho, kunzima kakhulu ukuqonda ukuthi mangaki amathebula asemgqeni ngaso leso sikhathi.

Qaphela: _Kusukela nge-Postgres 10, isimo sokulandelelwa kwe-Vatovac sithuthuke kakhulu - umbono we-pg_stat_progress uvelevacuum, okwenza kube lula kakhulu indaba yokuqapha i-vacuum yemoto.

Singasebenzisa lo mbuzo owenziwe lula. Futhi singabona ukuthi ivacuum kuyodingeka yenziwe nini. Kodwa i-vacuum kufanele iqale kanjani futhi nini? Lezi yizinguqulo zefa lemigqa ebengikhuluma ngayo ngaphambili. Kwenzeke isibuyekezo, kwafakwa inguqulo entsha yomugqa. Kuvele inguqulo ephelelwe yisikhathi yeyunithi yezinhlamvu. Etafuleni pg_stat_user_tables kukhona ipharamitha enjalo n_dead_tup. Ibonisa inani lemigqa "efile". Futhi ngokushesha nje lapho inani lemigqa efile liba likhulu kunomkhawulo othile, i-autovacuum izofika etafuleni.

Futhi lo mkhawulo ubalwa kanjani? Leli iphesenti eliqondile lengqikithi yenani lemigqa kuthebula. Kukhona ipharamitha autovacuum_vacuum_scale_factor. Inquma iphesenti. Ake sithi 10% + kukhona umkhawulo oyisisekelo owengeziwe wemigqa engama-50. Futhi kwenzekani? Uma sinemigqa efile eminingi kuno-“10% + 50” wayo yonke imigqa etafuleni, bese sibeka itafula ku-autovacuum.

select c.relname,
current_setting('autovacuum_vacuum_threshold') as av_base_thresh,
current_setting('autovacuum_vacuum_scale_factor') as av_scale_factor,
(current_setting('autovacuum_vacuum_threshold')::int +
(current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples))
as av_thresh,
s.n_dead_tup
from pg_stat_user_tables s join pg_class c ON s.relname = c.relname
where s.n_dead_tup > (current_setting('autovacuum_vacuum_threshold')::int
+ (current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples));

Nokho, kukhona iphuzu elilodwa. Imikhawulo eyisisekelo yamapharamitha av_base_thresh и av_scale_factor zingabelwa ngazodwana. Futhi, ngokufanele, umkhawulo ngeke ube womhlaba wonke, kodwa umuntu ngamunye etafuleni. Ngakho-ke, ukuze ubale, udinga ukusebenzisa amaqhinga namaqhinga. Futhi uma unesithakazelo, ungabheka okuhlangenwe nakho kozakwethu abavela ku-Avito (isixhumanisi ku-slide asivumelekile futhi sibuyekeziwe embhalweni).

Babhalele i-plugin ye-munin, okucabangela lezi zinto. Kukhona indwangu yezinyawo enamashidi amabili lapho. Kodwa ibala ngendlela efanele nangempumelelo isivumela ukuthi sihlole lapho sidinga i-vacuum eningi yamatafula lapho kuncane khona.

Yini esingayenza ngakho? Uma sinomugqa omkhulu futhi i-autovacuum ingakwazi ukumelana, khona-ke singakwazi ukwandisa inani labasebenzi be-vacuum, noma senze i-vacuum ibe nolaka kakhulu., ukuze iqale ngaphambili, icubungula itafula ngezicucu ezincane. Futhi ngaleyo ndlela ulayini uzokwehla. - Into eyinhloko lapha ukuqapha umthwalo kumadiski, ngoba ... i-vacuum akuyona into yamahhala, nakuba ngokufika kwamadivayisi we-SSD/NVMe inkinga ayisabonakali kangako.

I-Pg_stat_all_indexes yizibalo zezinkomba. Akamkhulu. Futhi singayisebenzisa ukuze sithole ulwazi mayelana nokusetshenziswa kwezinkomba. Futhi ngokwesibonelo, singanquma ukuthi yiziphi izinkomba esinazo ezengeziwe.

Njengoba sengishilo, isibuyekezo akusona nje isibuyekezo samathebula, futhi siyisibuyekezo sezinkomba. Ngakho-ke, uma sinezinkomba eziningi etafuleni, lapho-ke sibuyekeza imigqa etafuleni, izinkomba zezinkambu ezikhonjiwe nazo zidinga ukubuyekezwa, futhi uma sinezinkomba ezingasetshenzisiwe ezingenakho ukuskena kwenkomba, khona-ke zilenga njenge-ballast. Futhi kudingeka sizisuse. Kulokhu sidinga insimu idx_scan. Simane sibheke inani lezinkomba zezinkomba. Uma izinkomba zinokuskena okunguziro esikhathini eside uma kuqhathaniswa sokugcina izibalo (okungenani amaviki angu-2-3), khona-ke cishe lezi yizinkomba ezimbi, kudingeka sizisuse.

Qaphela: Lapho usesha izinkomba ezingasetshenzisiwe esimweni sokusakazwa kwamaqoqo okuphindaphinda, udinga ukuhlola wonke ama-cluster node, ngoba izibalo azikho emhlabeni jikelele, futhi uma inkomba ingasetshenzisiwe ku-master, ingasetshenziswa kuma-replicas (uma kukhona umthwalo lapho).

Izixhumanisi ezimbili:

https://github.com/dataegret/pg-utils/blob/master/sql/low_used_indexes.sql

http://www.databasesoup.com/2014/05/new-finding-unused-indexes-query.html

Lezi izibonelo zemibuzo ethuthuke kakhulu yokuthi ungabheka kanjani izinkomba ezingasetshenzisiwe.

Isixhumanisi sesibili siyisicelo esithokozisayo. Kukhona i-logic engeyona encane kakhulu lapho. Ngiyincoma ukuze ngibhekisele kuyo.

Yini enye okufanele ukufingqa usebenzisa indices?

Izinkomba ezingasetshenzisiwe zimbi.
Bathatha indawo.
Nciphisa imisebenzi yokubuyekeza.
Umsebenzi owengeziwe we-vacuum.

Uma sisusa izinkomba ezingasetshenzisiwe, sizokwenza kuphela isizindalwazi sibe ngcono.

Isethulo esilandelayo sithi pg_stat_activity. Lesi yi-analogue yohlelo lokusebenza ps, kuphela ku-PostgreSQL. Uma ps'om ubheka izinqubo ohlelweni lokusebenza, ke pg_stat_activity Izokukhombisa umsebenzi ongaphakathi kwe-PostgreSQL.

Yiziphi izinto eziwusizo esingazithatha lapho?

select
count(*)*100/(select current_setting('max_connections')::int)
from pg_stat_activity;

Singabona umsebenzi ojwayelekile, okwenzekayo kusizindalwazi. Singenza umsebenzi omusha. Konke lapha kuqhume, ukuxhumana okusha akwamukelekile, amaphutha angena kuhlelo lokusebenza.

select
client_addr, usename, datname, count(*)
from pg_stat_activity group by 1,2,3 order by 4 desc;

Singaqalisa umbuzo ofana nalo futhi sibone isamba samaphesenti oxhumo oluhlobene nomkhawulo omkhulu wokuxhumeka futhi sibone ukuthi ubani onokuxhunywa okuningi. Futhi kulesi simo esinikeziwe sibona lowo msebenzisi cron_role uvule izixhumanisi ezingama-508. Futhi kwenzeka okuthile kuye lapho. Kudingeka sibhekane nakho futhi sibheke. Futhi kungenzeka ukuthi lolu uhlobo oluthile lwenombolo engavamile yokuxhumana.

Uma sinomthwalo womsebenzi we-OLTP, imibuzo kufanele isheshe, isheshe kakhulu futhi akufanele kube nemibuzo emide. Kodwa-ke, uma kuphakama imibuzo emide, khona-ke esikhathini esifushane akukho lutho lokukhathazeka ngakho, kodwa Ngokuhamba kwesikhathi, imibuzo emide ilimaza isizindalwazi; yandisa umphumela wokuqunjelwa wamatafula lapho ukuhlukana kwetafula kwenzeka. Udinga ukususa kokubili ukuqunjelwa kanye nemibuzo emide.

select
client_addr, usename, datname,
clock_timestamp() - xact_start as xact_age,
clock_timestamp() - query_start as query_age,
query
from pg_stat_activity order by xact_start, query_start;

Sicela uqaphele: ngalesi sicelo singakwazi ukuhlonza imibuzo emide nemisebenzi. Sisebenzisa umsebenzi clock_timestamp() ukunquma isikhathi sokusebenza. Imibuzo emide esiyitholile, singayikhumbula, siyifeze explain, bheka izinhlelo futhi ngandlela thize wandise. Sethula izicelo ezinde zamanje futhi siqhubeke nempilo yethu.

select * from pg_stat_activity where state in
('idle in transaction', 'idle in transaction (aborted)';

Okwenziwayo okungalungile kuyimisebenzi ekwizimo zokungenzi lutho futhi ezingenzi lutho ezifundeni zomsebenzi (ezinqanyuliwe).

Kusho ukuthini? Okwenziwe kunezifunda eziningi. Futhi esinye salezi zifundazwe singacatshangwa nganoma yisiphi isikhathi. Kunenkambu yokuchaza izifundazwe state kulesi sethulo. Futhi siyisebenzisela ukunquma isimo.

select * from pg_stat_activity where state in
('idle in transaction', 'idle in transaction (aborted)';

Futhi, njengoba ngishilo ngenhla, lezi zimo ezimbili ukungenzi lutho okwenziwayo nokungenzi lutho ekuthengiseni (kuyekiwe) kubi. Yini? Kungaleso sikhathi lapho isicelo savula khona umsebenzi, senza okuthile futhi senza ibhizinisi laso. Okwenziwayo kuhlala kuvuliwe. Iyalenga, akwenzeki lutho kuyo, ithatha ukuxhumana, ikhiye emigqeni eshintshile futhi ikhulise ukuqunjelwa kwamanye amatafula, ngenxa yesakhiwo senjini yokuthengiselana ye-Postrges. Futhi ukuthengiselana okunjalo kufanele futhi kudutshulwe phansi, ngoba ngokuvamile kuyingozi, kunoma yikuphi.

Uma ubona ukuthi unazo ezingaphezu kuka-5-10-20 ku-database yakho, udinga ukukhathazeka futhi uqale ukwenza okuthile ngazo.

Lapha sisebenzisa isikhathi sokubala clock_timestamp(). Sithwebula ukuthengiselana futhi sithuthukise uhlelo lokusebenza.

Njengoba ngishilo ngenhla, ukuvimba yilapho ukuthengiselana okubili noma ngaphezulu kulwela eyodwa noma iqembu lezinsiza. Kulokhu sinensimu waiting ngevelu ye-boolean true noma false.

Iqiniso - lokhu kusho ukuthi inqubo ilindile, kukhona okumele kwenziwe. Uma inqubo ilindile, kusho ukuthi iklayenti eliqale lolu hlelo nalo lilindile. Iklayenti lihlala esipheqululini futhi liphinde lilinde.

Isexwayiso: _Kusuka ku-Postgres version 9.6 field waiting isusiwe futhi esikhundleni salokho kwengezwe izinkambu ezimbili ezifundisayo wait_event_type и wait_event._

Yini okufanele ngiyenze? Uma ubona iqiniso isikhathi eside, kusho ukuthi udinga ukususa izicelo ezinjalo. Simane sinciphise ukuthengiselana okunjalo. Sibhalela onjiniyela ukuthi badinga ukuthuthukisa ngandlela thize ukuze kungabikho umjaho wezinsiza. Bese-ke abathuthukisi belungiselela uhlelo lokusebenza ukuze lokhu kungenzeki.

Futhi icala elidlulele, kodwa elingase lingabulali ukwenzeka kwe-deadlocks. Okwenziwayo okubili kubuyekezwe izinsiza ezimbili, kwase kufinyelelwa kuzo futhi, kulokhu kuzinsiza ezihlukile. Kulokhu, i-PostgreSQL ibulala ukuthengiselana ngokwayo ukuze enye iqhubeke nokusebenza. Lesi yisimo esifile futhi akakwazi ukuzitholela sona ngokwakhe. Ngakho-ke, i-PostgreSQL iphoqeleka ukuthi ithathe izinyathelo ezeqisayo.

https://github.com/lesovsky/uber-scripts/blob/master/postgresql/sql/c4_06_show_locked_queries.sql

https://github.com/lesovsky/uber-scripts/blob/master/postgresql/sql/show_locked_queries_95.sql

https://github.com/lesovsky/uber-scripts/blob/master/postgresql/sql/show_locked_queries_96.sql

http://big-elephants.com/2013-09/exploring-query-locks-in-postgres/

Futhi nansi imibuzo emibili ekuvumela ukuthi ulandelele ukuvinjwa. Sisebenzisa ukubuka pg_locks, okuvumela ukuthi ulandelele izingidi ezinzima.

Futhi isixhumanisi sokuqala umbhalo wesicelo ngokwawo. Kude impela.

Futhi isixhumanisi sesibili siyindatshana emayelana nezingidi. Kuyasiza ukufunda, kuyathakazelisa kakhulu.

Pho sibonani? Sibona izicelo ezimbili. Ukuthenga nge ALTER TABLE kuwumsebenzi ovimbayo. Iqalile, kodwa ayizange iqedele, futhi uhlelo lokusebenza oluqophe lokhu kuthenga lwenza ezinye izinto ndawana thize. Futhi isicelo sesibili esokuvuselela. Ulinda ukuthi i-alter table iphele ngaphambi kokuba aqhubeke nomsebenzi wakhe.

Yile ndlela esingathola ngayo ukuthi ubani okhiye, ubani ophethe, futhi singabhekana nakho ngokuqhubekayo.

Imojula elandelayo ithi pg_stat_statements. Njengoba ngishilo, lena imojula. Ukuze uyisebenzise, udinga ukulayisha ilabhulali yayo ekucushweni, qala kabusha i-PostgreSQL, faka imojuli (ngomyalo owodwa) bese sizoba nombono omusha.

Cреднее время запроса в милисекундах
$ select (sum(total_time) / sum(calls))::numeric(6,3)
from pg_stat_statements;

Самые активно пишущие (в shared_buffers) запросы
$ select query, shared_blks_dirtied
from pg_stat_statements
where shared_blks_dirtied > 0 order by 2 desc;

Yini esingayithatha lapho? Uma sikhuluma ngezinto ezilula, singathatha isikhathi sokwenza imibuzo esimaphakathi. Isikhathi siyakhula, okusho ukuthi i-PostgreSQL iphendula kancane futhi kudingeka senze okuthile.

Singabheka imisebenzi yokubhala esebenza kakhulu kusizindalwazi eshintsha idatha kumabhafa okwabelwana ngawo. Bona ukuthi ubani obuyekeza noma osusa idatha lapho.

Futhi singamane sibheke izibalo ezihlukene zalezi zicelo.

https://github.com/dataegret/pg-utils/blob/master/sql/global_reports/query_stat_total.sql

We pg_stat_statements Siyisebenzisela ukwakha imibiko. Sisetha kabusha izibalo kanye ngosuku. Masiyiqongelele. Ngaphambi kokusetha kabusha izibalo ngokuzayo, asakhe umbiko. Nasi isixhumanisi sombiko. Ungakwazi ukuyibuka.

Senzani? Sibala izibalo ezijwayelekile zazo zonke izicelo. Bese, esicelweni ngasinye, sibala umnikelo waso ngamunye kulezi zibalo zizonke.

Futhi yini esingayibuka? Singabheka isamba sesikhathi sokwenza sazo zonke izicelo zohlobo oluthile ngokumelene nesizinda sazo zonke ezinye izicelo. Singabheka i-CPU kanye nokusetshenziswa kwensiza ye-I/O ngokuhlobene nesithombe sisonke. Futhi vele ulungiselele le mibuzo. Sakha imibuzo ephezulu ngokususelwe kulo mbiko futhi sesivele sithola okucatshangwayo mayelana nokuthi yini okufanele siyilungiselele.

Yini esiyishiye ngemuva? Kuseneziphakamiso ezimbalwa engingazicabangelanga ngoba isikhathi sincane.

Zikhona pgstattuple futhi iyimojula eyengeziwe evela kuphakheji ye-contribs evamile. Ikuvumela ukuthi uhlole bloat amatafula, okuthiwa ukuhlukana kwetafula. Futhi uma kukhona ukuhlukana okuningi, udinga ukukususa futhi usebenzise amathuluzi ahlukene. Futhi umsebenzi pgstattuple isebenza isikhathi eside. Futhi uma kukhona amatafula amaningi, izosebenza isikhathi eside.

Isinyathelo esilandelayo pg_buffercache. Ikuvumela ukuthi uhlole izigcinalwazi ezabiwe: ukuthi amakhasi ebhafa yamathebula asetshenziswa ngokujulile kangakanani nokuthi yimaphi amathebula amakhasi ebhafa asetshenziswa. Futhi ivele ikuvumela ukuthi ubheke kumabhafa okwabelwana ngawo futhi uhlole ukuthi kwenzekani lapho.

Imojula elandelayo ithi pgfincore. Ivumela ukusebenza kwetafula lezinga eliphansi ngekholi yesistimu mincore(), i.e. ikuvumela ukuthi ulayishe ithebula kumabhafa okwabelwana ngawo, noma ulikhiphe. Futhi ivumela, phakathi kwezinye izinto, ukuhlola inqolobane yekhasi lesistimu yokusebenza, okungukuthi, ukuthi itafula lithatha isikhala esingakanani kunqolobane yekhasi, kumabhafa abiwe, futhi limane lisivumele ukuthi sihlole umthwalo womsebenzi wetafula.

Imojula elandelayo - pg_stat_kcache. Iphinde isebenzisa ikholi yesistimu getrusage(). Futhi ikwenza ngaphambi nangemva kokuba isicelo senziwe. Futhi ezibalweni eziwumphumela, kusivumela ukuba silinganisele ukuthi isicelo sethu sichitha malini kudiski I/O, okungukuthi, ukusebenza ngesistimu yefayela futhi sibheka ukusetshenziswa kweprosesa. Kodwa-ke, imojula isencane (ukukhwehlela) futhi ekusebenzeni kwayo idinga i-PostgreSQL 9.4 kanye ne-pg_stat_statements, engishilo ekuqaleni.

Ukwazi ukusebenzisa izibalo kuyasiza. Awudingi izinhlelo zezinkampani zangaphandle. Ungangena, uyabo, wenze okuthize, ufeze okuthize.
Ukusebenzisa izibalo akunzima, yi-SQL evamile nje. Uqoqe isicelo, wasihlanganisa, wasithumela, wasibheka.
Izibalo zisiza ukuphendula imibuzo. Uma unemibuzo, uphendukela ezibalweni - bheka, thola iziphetho, hlaziya imiphumela.
Futhi ukuhlola. Kunezicelo eziningi, idatha eningi. Ungahlala ulungiselela umbuzo okhona. Ungenza inguqulo yakho yesicelo evumelana nawe ngaphezu kokwangempela futhi usisebenzise.