Indlela yoshishino yokulungisa iPostgreSQL: imifuniselo ngogcino-lwazi." UNikolay Samokhvalov

Ndicebisa ukuba ufunde umbhalo wengxelo kaNikolai Samokhvalov "Indlela yoshishino yokulungisa iPostgreSQL: imifuniselo kwiziko ledatha"

I-Shared_buffers = 25% - ingaba ininzi okanye incinci? Okanye kunjalo nje? Wazi njani ukuba le - kunokuba iphelelwe lixesha - ingcebiso ifanelekile kwimeko yakho?

Ixesha lokusondela kumbandela wokukhetha iiparamitha ze-postgresql.conf "njengomntu omdala." Hayi ngoncedo lwe "auto tuners" ezingaboniyo okanye iingcebiso eziphelelwe lixesha ezivela kumanqaku kunye neeblogi, kodwa zisekwe kwi:

1. imifuniselo engqinisiswe ngokungqongqo kugcino-lwazi, olwenziwa ngokuzenzekelayo, ngobuninzi naphantsi kweemeko ezikufutshane kangangoko kunokwenzeka “zokulwa” ezo,
2. ukuqonda okunzulu kweempawu ze-DBMS kunye ne-OS.

Ukusebenzisa uNancy CLI (https://gitlab.com/postgres.ai/nancy), siya kujonga umzekelo othile - i-notorious shared_buffers - kwiimeko ezahlukeneyo, kwiiprojekthi ezahlukeneyo kwaye sizame ukufumanisa indlela yokukhetha ukusetwa kwesiseko sethu, isiseko sedatha kunye nomthwalo.

Siza kuthetha malunga nemifuniselo kunye nogcino-lwazi. Eli libali elithatha ixesha elingaphezu kweenyanga ezintandathu.

Kancinci ngam. Amava ngePostgres ngaphezulu kweminyaka eyi-14. Kuye kwasekwa iqela leenkampani zokuncokola. I-Postgres yayisetyenziswa kuyo yonke indawo.

Kwakhona iqela leRuPostgres kwi-Meetup, indawo ye-2 emhlabeni. Sithe chu sisondela kubantu abangama-2. RuPostgres.org.

Kwaye kwiiPC zeenkomfa ezahlukeneyo, kubandakanya i-Highload, ndinoxanduva lokugcinwa kwedatha, ngakumbi i-Postgres kwasekuqaleni.

Kwaye kule minyaka imbalwa idlulileyo, ndiye ndaphinda ndaphinda ndaphinda ndaqala i-Postgres yam yokuziqhelanisa namaxesha ali-11 ukusuka apha.

Kwaye xa ndisenza oku kwiminyaka embalwa edlulileyo, ndiye ndanekhefu emsebenzini osebenzayo wezandla kunye nePostgres, mhlawumbi ukusukela ngo-2010. Ndothukile indlela encinci yokusebenza ye-DBA etshintshileyo, kwaye ungakanani umsebenzi wezandla osafuna ukusetyenziswa. Kwaye kwangoko ndacinga ukuba kukho into engalunganga apha, kufuneka ndizenzele ngaphezulu kwayo yonke into.

Kwaye ekubeni yonke yayikude, uninzi lwabathengi babesemafini. Kwaye okuninzi sele kuzenzekelayo, ngokucacileyo. Okunye malunga noku kamva. Okokuthi, konke oku kubangele ingcamango yokuba kufuneka kubekho izixhobo ezininzi, oko kukuthi, uhlobo oluthile lweqonga eliya ngokuzenzekelayo phantse zonke izenzo ze-DBA ukwenzela ukuba inani elikhulu leedatha likwazi ukulawulwa.

Le ngxelo ayizobandakanya:

• "Iibhulethi zesilivere" kunye neengxelo ezifana - setha i-8 GB okanye i-25% i-shared_buffers kwaye uya kulunga. Akusayi kubakho nto ingako malunga ne-shared_buffers.
• Hardcore "innards".

Kuya kwenzeka ntoni?

• Kuya kubakho imigaqo yokuphucula esiyisebenzisayo kwaye siyiphuhlise. Kuya kubakho zonke iintlobo zeengcamango ezivela endleleni kunye nezixhobo ezahlukahlukeneyo esizenzayo inxalenye enkulu kwi-Open Source, oko kukuthi senza isiseko kwi-Open Source. Ngapha koko, sinawo amatikiti, lonke unxibelelwano luyiMthombo oVulekileyo. Uyakwazi ukubona into esiyenzayo ngoku, yintoni eya kuba kukukhutshwa okulandelayo, njl.
• Kwakhona kuya kubakho amava ekusebenziseni le migaqo, ezi zixhobo kwiinkampani ezininzi: ukusuka kwiinkampani ezincinci ukuya kwiinkampani ezinkulu.

Kwenzeka njani konke oku?

Okokuqala, owona msebenzi uphambili we-DBA, ukongeza ekuqinisekiseni ukudalwa kweemeko, ukuthunyelwa kwee-backups, njl., kukufumana imiqobo kunye nokwandisa ukusebenza.

Ngoku isekwe ngolu hlobo. Sijonge esweni, sibona into ethile, kodwa siyasilela ezinye iinkcukacha. Siqala ukumba ngokucokisekileyo, ngokuqhelekileyo ngezandla zethu, kwaye siqonde ukuba senze ntoni ngayo ngenye indlela.

Kwaye kukho iindlela ezimbini. Pg_stat_statements sisisombululo esisisiseko sokuchonga imibuzo ecothayo. Kunye nohlalutyo Postgres logs usebenzisa pgBadger.

Indlela nganye yokufundisa ineengxaki ezinzulu. Kwindlela yokuqala, silahle zonke iiparameters. Kwaye ukuba sibona amaqela KHETHA * UKUSUKA kwitafile apho ikholamu ilingana no "?" okanye "$" ukususela Postgres 10. Asazi ukuba le index scan okanye seq scan. Kuxhomekeke kakhulu kwiparameter. Ukuba ubeka endaweni yexabiso elinqabileyo apho, izakuba sisalathisi sokuskena. Ukuba uthatha indawo yexabiso elihlala kwi-90% yetafile apho, i-seq scan iya kubonakala, kuba i-Postgres iyazazi izibalo. Kwaye le yingxaki enkulu ye-pg_stat_statements, nangona umsebenzi othile uyaqhubeka.

Intlupheko enkulu yohlalutyo lwelogi kukuba awukwazi ukufikelela "log_min_duration_statement = 0" njengomthetho. Kwaye siza kuthetha ngale nto kwakhona. Ngokufanelekileyo, awuwuboni wonke umfanekiso. Kwaye omnye umbuzo, okhawuleza kakhulu, unokusebenzisa isixa esikhulu semithombo, kodwa awuyi kuyibona kuba ingaphantsi komda wakho.

Ii-DBA zizisombulula njani iingxaki ezizifumanayo?

Umzekelo, sifumene ingxaki ethile. Kwenziwa ntoni? Ukuba ungumphuhlisi, ngoko uya kuba wenza into ethile kwimeko ethile engalinganiyo. Ukuba uyi-DBA, ngoko uneqonga. Kwaye kunokubakho enye kuphela. Wayesemva kweenyanga ezintandathu. Kwaye ucinga ukuba uya kuya kwimveliso. Kwaye nee-DBA ezinamava emva koko zijonge kwimveliso, kwi-replica. Kwaye kwenzeka ukuba benze isalathisi sexeshana, qiniseka ukuba iyanceda, uyilahle kwaye uyinike abaphuhlisi ukuze bafake kwiifayile zokufuduka. Bububhanxa obu busenzeka ngoku. Kwaye oku kuyingxaki.

• Lungelelanisa ulungelelwaniso.
• Lungiselela iseti yezalathisi.
• Guqula umbuzo weSQL ngokwayo (le yeyona ndlela inzima).
• Yongeza umthamo (eyona ndlela ilula kwiimeko ezininzi).

Ininzi into eyenzekayo ngezi zinto. Zininzi izibambo kwiPostgres. Kuninzi ekufuneka ukwazi. Kukho izalathisi ezininzi kwi-Postgres, enkosi nakubaququzeleli bale nkomfa. Kwaye konke oku kufuneka kwaziwe, kwaye yile nto eyenza ukuba ii-non-DBAs zizive ngathi ii-DBAs zenza umlingo omnyama. Oko kukuthi, kufuneka ufunde iminyaka eli-10 ukuze uqale ukuyiqonda yonke le nto ngokwesiqhelo.

Kwaye ndingumlwi ngokuchasene nalo mlingo umnyama. Ndifuna ukwenza yonke into ukuze kubekho iteknoloji, kwaye akukho intuition kuyo yonke le nto.

Imizekelo yobomi bokwenyani

Oku ndikubonile ubuncinane kwiiprojekthi ezimbini, kuquka neyam. Esinye isithuba sebhlog sisixelela ukuba ixabiso le-1 le-default_statistict_target lilungile. Kulungile, masiyizame kwimveliso.

Kwaye silapha, sisebenzisa isixhobo sethu kwiminyaka emibini kamva, ngoncedo lwemifuniselo kugcino-lwazi esithetha ngalo namhlanje, sinokuthelekisa oko kwayikho kunye nokuba yintoni na.

Kwaye oku kufuneka senze umfuniselo. Inezahlulo ezine.

• Eyokuqala yimekobume. Sifuna isiqwenga sehardware. Kwaye xa ndiza kwinkampani ethile kwaye ndisayine isivumelwano, ndibaxelela ukuba bandinike i-hardware efanayo nanjengemveliso. KwiMasters yakho nganye, ndifuna ubuncinci iqhekeza elinye lehardware efana nale. Nokuba lo ngumzekelo womatshini okhoyo eAmazon okanye kuGoogle, okanye ndifuna isiqwenga esifanayo sehardware. Oko kukuthi, ndifuna ukuyila kwakhona imeko-bume. Kwaye kwimbono yokusingqongileyo sibandakanya inguqulelo enkulu yePostgres.
• Inxalenye yesibini yinjongo yophando lwethu. Le yidatabase. Inokudalwa ngeendlela ezininzi. Ndiza kukubonisa ukuba njani.
• Inxalenye yesithathu ngumthwalo. Lo ngowona mzuzu unzima kakhulu.
• Kwaye inxalenye yesine yinto esiyijongayo, oko kukuthi, siza kuthelekisa nantoni na. Masithi sinokutshintsha iparameters enye okanye ngaphezulu kuqwalaselo, okanye singenza isalathisi, njl.

Siqalisa umfuniselo. Nantsi i-pg_stat_statements. Ekhohlo kwenzeka ntoni. Ekunene - kwenzeka ntoni.

Ngasekhohlo default_statistics_target = 100, ekunene =1. Siyabona ukuba oku kusincedile. Lilonke, yonke into yabangcono nge-000%.

Kodwa ukuba siskrolela ezantsi, kuya kubakho amaqela ezicelo kwi pgBadger okanye kwi pg_stat_statements. Zimbini iinketho. Siza kubona ukuba ezinye izicelo zehle ngama-88%. Kwaye nantsi indlela yobunjineli. Sinokugrumba ngakumbi ngaphakathi kuba siyamangala ukuba kwakutheni ukuze izike. Kufuneka uqonde ukuba kwenzeke ntoni na ngezibalo. Kutheni iibhakethi ezininzi kwizibalo zikhokelela kwiziphumo ezimbi kakhulu.

Okanye asikwazi ukugrumba, kodwa yenza "ALTER TABLE ... ALTER COLUMN" kwaye ubuyisele iibhakethi eziyi-100 emva kwezibalo zale kholamu. Kwaye ke ngolunye uvavanyo sinokuqinisekisa ukuba le patch yanceda. Konke. Le yindlela yobunjineli esinceda ukuba sibone umfanekiso omkhulu kwaye senze izigqibo ezisekelwe kwidatha kune-intuition.

Imizekelo embalwa evela kwezinye iindawo. Kubekho iimvavanyo zeCI ekuvavanyeni iminyaka emininzi. Kwaye akukho projekthi engqondweni yayo elungileyo eya kuhlala ngaphandle kovavanyo oluzenzekelayo.

Kwamanye amashishini: kwinqwelomoya, kwishishini leemoto, xa sivavanya i-aerodynamics, sikwanalo nethuba lokwenza imifuniselo. Asiyi kusungula into ukusuka kumzobo ngqo esithubeni, okanye asiyi kubeka imoto emgceni kwangoko. Ngokomzekelo, kukho itonela yomoya.

Sinokufikelela kwizigqibo ngokuqwalasela kwamanye amashishini.

Okokuqala, sinendawo ekhethekileyo. Isondele kwimveliso, kodwa ayikufutshane. Olona phawu lwayo kukuba kufuneka lungabizi mali ininzi, luphindaphindeke kwaye luzenzekele kangangoko. Kwaye kufuneka kubekho izixhobo ezikhethekileyo zokwenza uhlalutyo oluneenkcukacha.

Kunokwenzeka ukuba, xa siqalisa inqwelo-moya size sibhabhe, sinethuba elincinane lokufunda yonke imilimitha yommandla wephiko kunokuba sinawo kwitonela yomoya. Sinezixhobo ezininzi zokuxilonga. Singakwazi ukuthwala izinto ezinzima ngakumbi esingenakukwazi ukuzifaka kwinqwelomoya emoyeni. Kuyafana nakwiPostgres. Sinokuthi, kwezinye iimeko, sivumele ukuloga kwemibuzo epheleleyo ngexesha lovavanyo. Kwaye asifuni ukwenza oku kwimveliso. Sinokude sicwangcise ukwenza oku sisebenzisa i-auto_explain.

Kwaye njengoko benditshilo, inqanaba eliphezulu lokuzenzekelayo lithetha ukuba sicofa iqhosha kwaye siphinda. Yile ndlela ekumele ukuba kube ngayo, ukuze kubekho ulingo oluninzi, ukuze lube ngumsinga.

UNancy CLI - isiseko "selabhoratri yedatha"

Siyenzile ke le nto. Oko kukuthi, ndithethe ngezi mbono ngoJuni, phantse unyaka odlulileyo. Kwaye sele sinento ebizwa ngokuba yi-Nancy CLI kwi-Open Source. Esi sisiseko sokwakha ilebhu yedathabheyisi.

nancy -IkuMthombo oVulekileyo, kwiGitlab. Ungayithetha, ungayizama. Ndinikeze ikhonkco kwizilayidi. Ungacofa kuyo kwaye iya kuba khona Nceda kuzo zonke iinkalo.

Ewe, kuninzi okusaphuhliswayo. Kukho iingcamango ezininzi apho. Kodwa le yinto esiyisebenzisa phantse yonke imihla. Kwaye xa sinombono-kutheni xa sicima imigca engama-40, yonke into yehla kwi-IO, emva koko sinokuqhuba umfuniselo kwaye sijonge ngakumbi ukuqonda okwenzekayo kwaye sizame ukuyilungisa kwimpukane. Oko kukuthi, senza umfuniselo. Umzekelo, silungisa into kwaye sibone okwenzekayo ekugqibeleni. Kwaye asikwenzi oku kwimveliso. Lo ngundoqo wengcamango.

Inokusebenza phi le nto? Oku kunokusebenza ekuhlaleni, o.k.t. ungayenza naphi na, unokuyiqhuba nakwiMacBook. Sifuna idocker, masihambe. Kuko konke. Ungayiqhuba kwimeko ethile kwisiqwenga sehardware, okanye kumatshini wenyani, naphi na.

Kwaye kukho nethuba lokubaleka ukude eAmazon kwi-EC2 Instance, kwiindawo. Kwaye eli lithuba elihle kakhulu. Umzekelo, izolo senze imifuniselo engaphezulu kwama-500 kumzekelo we-i3, siqala ngoyena mncinane saphela nge-i3-16-xlarge. Kwaye imifuniselo engama-500 ixabisa i-64 yeedola. Nganye yathabatha imizuzu eli-15. Oko kukuthi, ngenxa yokuba amabala asetyenziswa apho, kubiza kakhulu - isaphulelo se-70%, intlawulo ye-Amazon ngomzuzwana. Unokwenza okuninzi. Unokwenza uphando lokwenene.

Kwaye iinguqulelo ezintathu eziphambili zePostgres ziyaxhaswa. Akunzima kangako ukugqiba ezinye ezindala kunye nenguqulelo entsha ye-12.

Into sinokuyichaza ngeendlela ezintathu. Oku:

• Lahla/sql ifayile.
• Indlela engundoqo kukubumba ulawulo lwePGDATA. Njengomthetho, ithathwa kwiseva yokugcina. Ukuba unesiqhelo se-binary backups, unokwenza iiclones ukusuka apho. Ukuba unamafu, ke iofisi yelifu efana neAmazon kunye noGoogle iya kukwenzela oku. Le yeyona ndlela ibalulekileyo yokudibanisa imveliso yokwenyani. Le yindlela esenza ngayo.
• Kwaye indlela yokugqibela ifanelekile kuphando xa ufuna ukuqonda ukuba into isebenza njani kwi-Postgres. Le yi pgbench. Unokwenza usebenzisa i-pgbench. Lukhetho nje olunye lwe "db-pgbench". Umxelele ukuba singakanani isikali. Kwaye yonke into iya kuveliswa efini, njengoko kuchaziwe.

Kwaye layisha:

• Singenza umthwalo kumsonto omnye weSQL. Le yeyona ndlela yakudala.
• Kwaye sinokuxelisa umthwalo. Kwaye sinokuyilinganisa kuqala kuzo zonke ngale ndlela ilandelayo. Kufuneka siqokelele zonke iinkuni. Kwaye kubuhlungu. Ndiza kukubonisa ukuba kutheni. Kwaye usebenzisa i-pgreplay esiyidlalayo, eyakhiwe kuNancy.
• Okanye enye inketho. Into ebizwa ngokuba yi-craft load, esiyenzayo ngomlinganiselo othile womzamo. Ukuhlalutya umthwalo wethu wangoku kwinkqubo yokulwa, sikhupha amaqela aphezulu ezicelo. Kwaye usebenzisa i-pgbench sinokuxelisa lo mthwalo kwilabhoratri.

• Mhlawumbi kufuneka senze uhlobo oluthile lwe-SQL, okt sijonga uhlobo oluthile lokufuduka, senze isalathisi apho, siphumeze i-ANALAZE apho. Kwaye sijonga okwenzekayo ngaphambi kwevacuum nasemva kwevacuum. Ngokubanzi, nayiphi na i-SQL.
• Nokuba sitshintsha iparamitha enye okanye ngaphezulu kuqwalaselo. Sinokusixelela ukuba sijonge, umzekelo, amaxabiso ali-100 eAmazon kwisiseko sedatha yethu yeterabyte. Kwaye kwiiyure ezimbalwa uya kuba nesiphumo. Njengomthetho, kuya kukuthatha iiyure ezininzi ukuhambisa i-database ye-terabyte. Kodwa kukho isiqwenga ekuphuhlisweni, sinothotho olunokwenzeka, o.k.t. ungasoloko usebenzisa ipgdata efanayo kwiseva enye kwaye ujonge. IiPostgres ziya kuqalisa kwakhona kwaye iicache ziya kuphinda zisetwe. Kwaye ungaqhuba umthwalo.

• Uvimba weefayili ufika neqela leefayile ezahlukeneyo, ukuqala kwi pg imifanekiso ekhawulezayoyomthetho***. Kwaye eyona nto inika umdla kukho pg_stat_statements, pg_stat_kcacke. Ezi zizandiso ezibini ezihlalutya izicelo. Kwaye i-pg_stat_bgwriter ayiqulathanga iinkcukacha-manani zombhali kuphela, kodwa nakwindawo yokukhangela kunye nendlela ii-backends ngokwazo ezisusa ngayo ii-buffers ezimdaka. Kwaye konke kunomdla ukukubona. Umzekelo, xa siseta ishared_buffers, inika umdla kakhulu ukubona ukuba wonke umntu utshintshile kangakanani.
• Izigodo zePostgres nazo ziyafika. Iilogi ezimbini - ilog yamalungiselelo kunye nelog yokudlala yomthwalo.
• Into entsha ngokwentelekiso yiFlameGraphs.
• Kwakhona, ukuba usebenzise i-pgreplay okanye iinketho ze-pgbench zokudlala umthwalo, ngoko imveliso yabo iya kuba yemveli. Kwaye uya kubona i-latency kunye ne-TPS. Kuya kwenzeka ukuqonda indlela abayibona ngayo.
• Ulwazi lwenkqubo.
• Iitshekhi ze-CPU ezisisiseko kunye ne-IO. Oku ngakumbi kumzekelo we-EC2 e-Amazon, xa ufuna ukusungula iimeko ezifanayo ezili-100 kumsonto kwaye uqhube imitsi eyohlukeneyo eyi-100 apho, uya kuba nemifuniselo eyi-10. Kwaye kufuneka uqiniseke ukuba awufumani mzekelo unesiphene osele ucinezelwe ngumntu. Abanye bayasebenza kwesi siqwenga sehardware kwaye unesixhobo esincinci esisele. Kungcono ukulahla iziphumo ezinjalo. Kwaye ngoncedo lwe-sysbench evela e-Alexey Kopytov, senza iitshekhi ezininzi ezimfutshane eziza kuza kwaye zinokuthelekiswa nabanye, oko kukuthi uya kuqonda indlela i-CPU eziphatha ngayo kunye nendlela i-IO eziphatha ngayo.

Bubuphi ubunzima bobugcisa obusekelwe kumzekelo weenkampani ezahlukeneyo?

Masithi sifuna ukuphinda umthwalo wokwenyani sisebenzisa iinkuni. Yingcamango enkulu ukuba ibhaliwe kwi-Open Source pgreplay. Siyayisebenzisa. Kodwa ukuze isebenze kakuhle, kufuneka uvule ukuloga kwemibuzo epheleleyo ngeeparamitha kunye nexesha.

Kukho iingxaki ngexesha kunye nesitampu sexesha. Siza kukhupha lonke eli khitshi. Umbuzo ophambili ngowokuba ungawufumana na okanye hayi?

https://gist.github.com/NikolayS/08d9b7b4845371d03e195a8d8df43408

Ingxaki kukuba isenokungafumaneki. Okokuqala, kufuneka uqonde ukuba yeyiphi umsinga oya kubhalwa kwilogi. Ukuba une pg_stat_statements, ungasebenzisa lo mbuzo (ikhonkco liza kufumaneka kwizilayidi) ukuqonda malunga nokuba zingaphi iibytes eziya kubhalwa ngomzuzwana.

Sijonga ubude besicelo. Asiyihoyanga into yokuba akukho zipharamitha, kodwa siyabazi ubude besicelo kwaye siyabazi ukuba mangaphi amaxesha esiphunyeziweyo ngesekhondi. Ngale ndlela sinokuqikelela ukuba zingaphi ii-bytes ngomzuzwana. Sisenokwenza impazamo ngokuphindwe kabini, kodwa ngokuqinisekileyo siya kuwuqonda umyalelo ngale ndlela.

Siyabona ukuba amaxesha angama-802 ngomzuzwana esi sicelo senziwa. Kwaye siyabona ukuba bytes_per sec – 300 kB/s ziya kubhalwa dibanisa okanye thabatha. Kwaye, njengomthetho, sinako ukuhlawula ukuhamba okunjalo.

Kodwa! Inyaniso kukuba kukho iindlela ezahlukeneyo zokugawulwa kwemithi. Kwaye ukungagqibeki kwabantu kudla ngokuba yi "syslog".

Kwaye ukuba une-syslog, ke unokuba nomfanekiso onje. Siza kuthatha i-pgbench, vumela ukugawulwa kwemibuzo kwaye sibone ukuba kwenzeka ntoni.

Ngaphandle kokuloga - lo ngumqolo ongasekhohlo. Sifumene i-161 TPS. Nge-syslog - oku ku-Ubuntu 000 kwi-Amazon, sifumana i-16.04 TPS. Kwaye ukuba sitshintshela kwezinye iindlela ezimbini zokugawulwa kwemithi, ke imeko ingcono kakhulu. Oko kukuthi, besilindele ukuba yehle, kodwa hayi kwinqanaba elifanayo.

Kwaye kwi-CentOS 7, apho ijenali nayo ithatha inxaxheba, iguqula iilogi zibe yifomathi yokubini ukuze ikhangelwe lula, njl.

Kwaye le nto abantu bahlala nayo. Kwaye rhoqo kwiinkampani, ngakumbi ezinkulu, oku kunzima kakhulu ukutshintsha. Ukuba unokubaleka kwi-syslog, ke nceda umke kuyo.

• Vavanya i-IOPS kwaye ubhale ukuhamba.
• Jonga inkqubo yakho yokuloga.
• Ukuba umthwalo oqikelelweyo mkhulu ngokugqithisileyo, cinga ngesampulu.

Sine pg_stat_statements. Njengoko benditshilo, kufanele ukuba ikhona. Kwaye sinokuthatha kwaye sichaze iqela ngalinye lezicelo ngendlela ekhethekileyo kwifayile. Kwaye ke sinokusebenzisa into elula kakhulu kwi-pgbench - oku kukukwazi ukufaka iifayile ezininzi usebenzisa i- "-f" ukhetho.

Iyaqonda kakhulu "-f". Kwaye ungaxela ngoncedo luka "@" ekugqibeleni ukuba ifayile nganye kufuneka ibe nesabelo esingakanani. Oko kukuthi, sinokuthi yenza oku kwi-10% yamatyala, kwaye oku kwi-20%. Kwaye oku kuya kusisondeza kwinto esiyibonayo kwimveliso.

Siya kuyiqonda njani into esinayo kwimveliso? Sisiphi isabelo kwaye njani? Oku kancinci ecaleni. Sinemveliso enye ngaphezulu postgres-ukuhlolwa. Kwakhona isiseko kwi-Open Source. Kwaye ngoku siyiphuhlisa ngenkuthalo.

Wazalelwa ngenxa yezizathu ezahlukeneyo. Ngenxa yezizathu zokuba ukubeka esweni akwanelanga. Oko kukuthi, uze, ujonge isiseko, ujonge iingxaki ezikhoyo. Kwaye, njengomthetho, wenza i-health_check. Ukuba unamava e-DBA, ngoko wenza health_check. Sijonge ukusetyenziswa kwezalathisi, njl. Ukuba une-OKmeter, ke kuhle. Oku esweni cool for Postgres. OKmeter.io - nceda uyifake, yonke into yenziwa kakuhle kakhulu apho. Ihlawulwe.

Ukuba awunayo, ngoko ke ngokuqhelekileyo awunanto ingako. Ekubekeni iliso, kuhlala kukho i-CPU, i-IO, emva koko kunye nogcino, kwaye kuphelele apho. Kwaye sifuna ngaphezulu. Kufuneka sibone indlela i-autovacuum esebenza ngayo, indlela yokukhangela isebenza ngayo, kwi-io kufuneka sahlule indawo yokukhangela kwi-bgwriter nakwi-backends, njl.

Ingxaki kukuba xa unceda inkampani enkulu, ayikwazi ukuphumeza into ngokukhawuleza. Abakwazi ukuthenga ngokukhawuleza i-OKmeter. Mhlawumbi baya kuyithenga kwiinyanga ezintandathu. Abanakho ukuzisa iipakethe ngokukhawuleza.

Kwaye seza nengcamango yokuba sifuna isixhobo esikhethekileyo esingadingi nantoni na ukuba ifakwe, oko kukuthi akudingeki ukuba ufake nantoni na kwimveliso. Yifake kwilaptop yakho, okanye kwiseva yokujonga ukusuka apho uza kuyiqhuba khona. Kwaye iya kuhlalutya izinto ezininzi: inkqubo yokusebenza, inkqubo yefayile, kunye nePostgres ngokwayo, ukwenza imibuzo ekhanyayo enokuthi iqhutywe ngokuthe ngqo kwimveliso kwaye akukho nto iya kusilela.

Sayibiza ngokuba yiPostgres-checkup. Ngokwamayeza, oku kujongwa rhoqo kwimpilo. Ukuba i-auto-themed, ngoko ifana nokugcinwa. Uyenza isondlo kwimoto yakho rhoqo emva kweenyanga ezintandathu okanye ngonyaka, kuxhomekeke kwi-brand. Ngaba uyasenza ulungiso kwisiseko sakho? Oko kukuthi, ngaba wenza uphando olunzulu rhoqo? Kufuneka yenziwe. Ukuba wenza i-backups, emva koko wenze uhlolo, oku akubalulekanga kangako.

Kwaye sinesixhobo esinjalo. Yaqala ukuvela ngenkuthalo kuphela kwiinyanga ezintathu ezidlulileyo. Usemncinci, kodwa kuninzi apho.

Ukuqokelela awona maqela “anamandla” emibuzo-ingxelo ye-K003 kwi-Postgres-checkup

Kwaye kukho iqela leengxelo K. Iingxelo ezintathu ukuza kuthi ga ngoku. Kwaye kukho ingxelo enjalo K003. Kukho umphezulu ovela kwi-pg_stat_statements, ihlelwe nge-total_time.

Xa sihlela amaqela esicelo nge-total_time, phezulu sibona iqela elilayisha kakhulu inkqubo yethu, o.k.t. isebenzisa izixhobo ezingaphezulu. Kutheni ndibiza amaqela emibuzo? Kuba silahle iiparameters. Ezi azisezizo zicelo, kodwa amaqela ezicelo, o.k.t. abstracted.

Kwaye ukuba sikhulisa ukusuka phezulu ukuya ezantsi, siya kuzilula izixhobo zethu kwaye silibazise umzuzu xa kufuneka siphucule. Le yindlela entle kakhulu yokugcina imali.

Mhlawumbi oku akuyona indlela efanelekileyo kakhulu yokunyamekela abasebenzisi, kuba asikwazi ukubona inqabileyo, kodwa iimeko ezicaphukisayo apho umntu elinde imizuzwana ye-15. Zizonke, zinqabile kangangokuba asiziboni, kodwa sijongene nezibonelelo.

Kwenzeke ntoni kule tafile? Sithathe iifoto ezimbini. I-Postgres_checkup iya kukunika i-delta ye-metric nganye: ixesha elipheleleyo, iifowuni, imigqa, i-shared_blks_read, njl. Yiloo nto, i-delta ibalwe. Ingxaki enkulu nge pg_stat_statements kukuba ayikhumbuli xa iphinda isetwa. Ukuba pg_stat_database uyakhumbula, ngoko pg_stat_statements ayikhumbuli. Uyabona ukuba kukho inani le-1, kodwa asazi ukuba sibala phi.

Kwaye apha siyazi, apha sinee-snapshots ezimbini. Siyazi ukuba i-delta kule meko yayiyimizuzwana engama-56. Umsantsa omfutshane kakhulu. Ihlelwe nge total_time. Kwaye ke sinokwahlula, o.k.t. sahlulahlula zonke iimethrikhi ngokobude bexesha. Ukuba sahlula i-metric nganye ngexesha, siya kuba nenani leefowuni ngesekhondi nganye.

Okulandelayo, ixesha_lilonke ngesekhondi yeyona metric endiyithandayo. Ilinganiswa ngemizuzwana, ngesekhondi, o.t. zingaphi imizuzwana ethathelwe inkqubo yethu ukwenza eli qela lezicelo ngesekhondi. Ukuba ubona ngaphezu kwesibini ngesekondi apho, oko kuthetha ukuba kufuneka unike ngaphezu kondoqo omnye. Le metric ilunge kakhulu. Unokuqonda ukuba lo mhlobo, umzekelo, udinga ubuncinane ama-cores amathathu.

Oku kukwazi kwethu, andizange ndiyibone into enje naphi na. Nceda uqaphele - le nto ilula kakhulu - okwesibini ngesibini. Ngamanye amaxesha, xa i-CPU yakho iyi-100%, emva koko isiqingatha seyure ngomzuzwana, oko kukuthi, uchithe isiqingatha seyure usenza ezi zicelo.

Okulandelayo sibona imiqolo ngesekhondi. Siyazi ukuba mingaphi imiqolo ngesekhondi ebuyisiweyo.

Kwaye ke kukho into enomdla. Zingaphi ekwabelwana_buffers esizifunda ngesekondi kwishared_buffers ngokwayo. Ukubetha kwakusele kukho, kwaye sithathe imiqolo kwi-cache yenkqubo yokusebenza okanye kwidiski. Inketho yokuqala ikhawuleza, kwaye okwesibini inokukhawuleza okanye ingabikho ngokukhawuleza, kuxhomekeke kwimeko.

Kwaye indlela yesibini yokwahlula kukwahlula inani lezicelo kweli qela. Kuluhlu lwesibini uyakuhlala unombuzo omnye owahlulwe ngombuzo ngamnye. Kwaye ke inomdla- zingaphi ii-milliseconds ebezikwesi sicelo. Siyazi ukuba lo mbuzo uziphatha njani ngokomndilili. Iimillisecond ezili-101 zazifuneka kwisicelo ngasinye. Le yimetric yemveli ekufuneka siyiqonde.

Mingaphi imiqolo ebuyisiwe umbuzo ngamnye ngokomndilili? Sibona i-8 eli qela libuya. Ngokomyinge, yimalini ethathiweyo kwi-cache kwaye yafundwa. Siyabona ukuba yonke into igcinwe kakuhle. Ukubetha okuqinileyo kwiqela lokuqala.

Kwaye umtya osezantsi wesine kumgca ngamnye yeyiphi ipesenti yetotali. Sineminxeba. Masithi i-1 kwaye sinokuqonda ukuba leliphi igalelo elenziwa leli qela. Siyabona ukuba kule meko iqela lokuqala linikela ngaphantsi kwe-000%. Oko kukuthi, icotha kangangokuba asiyiboni kumfanekiso opheleleyo. Kwaye iqela lesibini li-000% kwiifowuni. Oko kukuthi, i-0,01% yazo zonke iifowuni liqela lesibini.

Ixesha_lilonke likwanika umdla. Sichithe i-14% yexesha lethu lomsebenzi lilonke kwiqela lokuqala lezicelo. Kwaye okwesibini - 11%, njl.

Andiyi kungena kwiinkcukacha, kodwa kukho ubuqili apho. Sibonisa impazamo phezulu, kuba xa sithelekisa, i-snapshots inokudada, oko kukuthi, ezinye izicelo zinokuwa kwaye azikwazi ukubakho kweyesibini, ngelixa ezinye ezintsha zinokuvela. Kwaye apho sibala impazamo. Ukuba ubona u-0, kulungile. Akukho zimpazamo. Ukuba izinga lempazamo lifikelela kwi-20%, kulungile.

Emva koko sibuyela kwisihloko sethu. Kufuneka senze umsebenzi onzima. Sithatha ukusuka phezulu ukuya phezulu kwaye sihambe de sifike kwi-80% okanye i-90%. Ngokuqhelekileyo oku ngamaqela angama-10-20. Kwaye senza iifayile zepgbench. Sisebenzisa i-random apho. Ngamanye amaxesha oku, ngelishwa, akusebenzi. Kwaye kwi-Postgres 12 kuya kubakho amathuba amaninzi okusebenzisa le ndlela.

Kwaye emva koko sizuza i-80-90% ngexesha lonke ngale ndlela. Kufuneka ndibeke ntoni emva ko-"@"? Sijonge kwiifowuni, sijonge ukuba ungakanani umdla okhoyo kwaye siyaqonda ukuba sityala inzala eninzi apha. Ukusuka kwezi pesenti sinokuyiqonda indlela yokulinganisa ifayile nganye. Emva koko sisebenzisa i-pgbench kwaye siye emsebenzini.

Sikwane K001 kunye ne K002.

U-K001 ngumtya omnye omkhulu onemitya enganeno emine. Olu luphawu lomthwalo wethu uphela. Jonga ikholamu yesibini kunye nomqolo wesibini. Siyabona ukuba malunga nesiqingatha esinye nesiqingatha sesekhondi ngesibini, oko kukuthi, ukuba kukho ii-cores ezimbini, kuya kuba kuhle. Kuya kubakho umthamo omalunga nama-75%. Kwaye kuya kusebenza ngolu hlobo. Ukuba sine-10 cores, ngoko siya kuzola ngokubanzi. Ngale ndlela sinokuvavanya izixhobo.

I-K002 yinto endiyibiza ngeeklasi zemibuzo, o.t. KHETHA, FAKA, HLAZIYA, UCIME. Kwaye ngokwahlukeneyo KHETHA UHLAZIYO, kuba sisitshixo.

Kwaye apha sinokugqiba ukuba KHETHA ngabafundi abaqhelekileyo - 82% yazo zonke iifowuni, kodwa kwangaxeshanye - 74% kwitotali_time. Oko kukuthi, zibizwa ngokuba zininzi, kodwa zidla izixhobo ezincinci.

Kwaye sibuyela kumbuzo: "Singazikhetha njani ii-shared_buffers ezifanelekileyo?" Ndibona ukuba uninzi lwebenchmarks lusekwe kumbono-makhe sibone ukuba iyakuba yintoni i-output, o.k.e. iyakuba yintoni i-output. Ngokuqhelekileyo kulinganiswa kwi-TPS okanye i-QPS.

Kwaye sizama ukucudisa iintengiselwano ezininzi ngesekhondi kangangoko sinakho ukusuka kwimoto sisebenzisa iiparamitha zokulungisa. Nantsi kanye 311 ngesekhondi yokukhetha.

Kodwa akukho mntu uqhuba ukuya emsebenzini kwaye abuyele ekhaya ngesantya esiphezulu. Bubudenge obu. Kuyafana noovimba beenkcukacha. Akuyomfuneko ukuba siqhube ngesantya esiphezulu, yaye akukho mntu uqhubayo. Akukho mntu uhlala kwimveliso, ene-100% CPU. Nangona, mhlawumbi umntu uphila, kodwa oku akulungile.

Ingcamango kukuba ngokuqhelekileyo siqhuba kwi-20 yepesenti yomthamo, ngokukhethekileyo kungabi ngaphezu kwe-50%. Kwaye sizama ukwandisa ixesha lokuphendula kubasebenzisi bethu ngaphezu kwayo yonke into. Oko kukuthi, kufuneka sijike amaqhosha ethu ukuze kubekho ubuncinci be-latency kwi-20% yesantya, ngokwemiqathango. Lo ngumbono esikwazama ukuwusebenzisa kwiimvavanyo zethu.

Kwaye ekugqibeleni, iingcebiso:

• Qinisekisa ukwenza iDatabase Lab.
• Ukuba kunokwenzeka, yenze ngokwemfuno ukuze ityhileke okwethutyana - yidlale kwaye uyilahle. Ukuba unamafu, ke oku kuhamba ngaphandle kokutsho, oko kukuthi, unokuma okuninzi.
• Yiba nomdla. Kwaye ukuba kukho into engalunganga, jonga ngemifuniselo ukuba iziphatha njani. UNancy unokusetyenziselwa ukuziqeqesha ukujonga ukuba isiseko sisebenza njani.
• Kwaye ujonge ixesha elincinci lokuphendula.
• Kwaye ungoyiki imithombo ye-Postgres. Xa usebenza ngemithombo yolwazi, kufuneka wazi isiNgesi. Kukho izimvo ezininzi apho, yonke into ichazwe apho.
• Kwaye khangela impilo yesiseko sedatha rhoqo, ubuncinane kanye kwiinyanga ezintathu, ngesandla, okanye i-Postgres-checkup.

Imibuzo yakho

Ndibulela kakhulu! Into enomdla kakhulu.

Iziqwenga ezibini.

Ewe, iziqwenga ezibini. Qha ndandingaqondi ncam. Xa mna noNancy sisebenza, ngaba sinokwenza iparamitha enye kuphela okanye iqela liphela?

Sine-delta config parameter. Ungajika apho kangangoko ufuna ngaxeshanye. Kodwa kufuneka uqonde ukuba xa utshintsha izinto ezininzi, unokwenza izigqibo ezingalunganga.

Ewe. Kutheni ndibuza? Kuba kunzima ukwenza imifuniselo xa une parameter enye kuphela. Uyiqinise, ubone ukuba isebenza njani. Ndamkhupha. Emva koko uqala okulandelayo.

Unokuyiqinisa ngexesha elifanayo, kodwa kuxhomekeke kwimeko, kunjalo. Kodwa kungcono ukuvavanya ingcamango enye. Besinombono izolo. Sasinemeko esondelelene kakhulu. Kwakukho uhlengahlengiso ezimbini. Kwaye asizange siqonde ukuba kutheni kukho umahluko omkhulu. Kwaye kwavela umbono wokuba kufuneka usebenzise i-dichotomy ukuze uqonde ngokuthe rhoqo kwaye ufumane ukuba yintoni umahluko. Unokwenza ngokukhawuleza isiqingatha seeparamitha ezifanayo, ngoko ikota, njl. Yonke into iguquguqukayo.

Kwaye kukho omnye umbuzo. Iprojekthi iselula kwaye iyaphuhla. Amaxwebhu sele elungile, ngaba kukho inkcazo eneenkcukacha?

Ndenze ngokukodwa ikhonkco apho kwinkcazo yeeparamitha. Ikhona. Kodwa izinto ezininzi azikabikho. Ndikhangela abantu abanengqondo efanayo. Kwaye ndizifumana xa ndidlala. Oku kuhle kakhulu. Umntu sele esebenza nam, umntu wanceda kwaye wenza into apho. Kwaye ukuba unomdla kwesi sihloko, nika ingxelo malunga nento engekhoyo.

Nje ukuba sakhe ilabhoratri, mhlawumbi kuya kubakho impendulo. Masibone. Enkosi!

Mholo! Enkosi ngengxelo! Ndabona ukuba kukho inkxaso ye-Amazon. Ngaba kukho naziphi na izicwangciso zokuxhasa i-GSP?

Umbuzo omhle. Saqalisa ukuyenza. Kwaye sayimisa okwangoku kuba sifuna ukonga imali. Oko kukuthi, kukho inkxaso usebenzisa run kwi localhost. Unokwenza umzekelo ngokwakho kwaye usebenze ekuhlaleni. Ngendlela, yile nto siyenzayo. Ndikwenza oku eGetlab, phaya eGSP. Kodwa asiyiboni inqaku lokwenza olo mculo okwangoku, kuba uGoogle akanazo iindawo zitshiphu. Nantsi lapho ??? iimeko, kodwa zinemida. Okokuqala, bahlala benesaphulelo se-70% kwaye awukwazi ukudlala ngexabiso apho. Kumabala, sinyusa ixabiso nge-5-10% ukunciphisa amathuba okuba ukhatywe. Oko kukuthi, ugcina amabala, kodwa anokususwa kuwe nanini na. Ukuba uthe wabhida ngaphezulu kancinci kunabanye, uya kubulawa kamva. UGoogle uneenkcukacha ezahlukeneyo ngokupheleleyo. Kwaye kukho omnye umda ombi kakhulu - bahlala kuphela iiyure ezingama-24. Kwaye ngamanye amaxesha sifuna ukwenza umfuniselo kangangeentsuku ezi-5. Kodwa unokwenza oku kwiindawo; amabala ngamanye amaxesha ahlala iinyanga.

Mholo! Enkosi ngengxelo! Ukhankanye ukuhlolwa. Uzibala njani iimpazamo zestat_statements?

Umbuzo omhle kakhulu. Ndingakubonisa kwaye ndikuxelele ngokweenkcukacha ezinkulu. Ngokufutshane, sijonga indlela isethi yamaqela esicelo edada ngayo: bangaphi abaye bawa kwaye bangaphi abatsha abavele. Kwaye emva koko sijonga i-metrics ezimbini: itotali_ixesha kunye neefowuni, ngoko kukho iimpazamo ezimbini. Kwaye sijonga igalelo lamaqela adadayo. Kukho amacandelwana amabini: abo bahambayo kunye nabafikileyo. Makhe sibone ukuba yintoni igalelo labo kumfanekiso opheleleyo.

Awoyiki ukuba iya kujika kabini okanye kathathu ngexesha phakathi kwesnapshots?

Oko kukuthi, baphinde babhalisa okanye yintoni?

Umzekelo, esi sicelo sele siceliwe kwakanye, emva koko safika saza saphinda salungiselelwa, saphinda sabuya saza saphinda salungiselelwa. Kwaye ubale into apha, kwaye iphi yonke?

Umbuzo olungileyo, kuya kufuneka sijonge.

Ndenze into efanayo. Kwakulula, kunjalo, ndayenza ndedwa. Kodwa kuye kwafuneka ndisete ngokutsha, ndisete ngokutsha i-stat_statements kwaye ndifumanise ngexesha lomfanekiso okhawulezayo ukuba bekukho ngaphantsi kweqhezu elithile, elingekafikeleli kwisilingi sokuba zingakanani i-stat_statements ezinokuqokelelwa apho. Kwaye ukuqonda kwam kukuba, kunokwenzeka, akukho nto yasuswayo.

Ewe Ewe.

Kodwa andiyiqondi indlela enye yokwenza ngokuthembekileyo.

Ngelishwa, andikhumbuli ncam nokuba sisebenzisa isicatshulwa sombuzo apho okanye sibuze nge pg_stat_statements kwaye sigxile kuyo. Ukuba sigxila kwi-queryid, ngoko kwithiyori sithelekisa izinto ezithelekisekayo.

Hayi, unokunyanzelwa ukuba aphume izihlandlo ezininzi phakathi kwe-snapshots kwaye eze kwakhona.

Ngesazisi esifanayo?

Ewe.

Siza kufunda oku. Umbuzo omhle. Kufuneka siyifundisise. Kodwa okwangoku, into esiyibonayo mhlawumbi ibhaliwe 0...

Oku, kunjalo, yinto enqabileyo, kodwa ndothuka xa ndafumanisa ukuba i-stat_statemetns inokufuduka apho.

Zininzi izinto kwi-Pg_stat_statements. Siye sadibana nenyaniso yokuba ukuba une track_utility = on, ke iiseti zakho nazo ziyalandelwa.

Ewe kunjalo.

Kwaye ukuba une-java hibernate, engaqhelekanga, ke itafile ye-hash iqala ukufumaneka apho. Kwaye ngokukhawuleza xa ucima isicelo esilayishwe kakhulu, ugqiba ngamaqela angama-50-100. Kwaye yonke into izinzile ngakumbi okanye ngaphantsi apho. Enye indlela yokulwa noku kukwandisa pg_stat_statements.max.

Ewe, kodwa kufuneka ukwazi ukuba yimalini. Yaye ngandlel’ ithile kufuneka sihlale simjongile. Nantso into endiyenzayo. Oko kukuthi, ndine pg_stat_statements.max. Kwaye ndiyabona ukuba ngexesha le-snapshot andizange ndifike kwi-70%. Kulungile, ngoko asilahlekanga nto. Masisete ngokutsha. Kwaye sigcina kwakhona. Ukuba umfanekiso olandelayo ungaphantsi kwama-70, ngoko ke kusenokwenzeka ukuba awuphindanga uphulukane nantoni na.

Ewe. Ukungagqibeki ngoku ngama-5. Kwaye oku kwanele kubantu abaninzi.

Ngokuqhelekileyo ewe.

Ividiyo:

PS Egameni lam, ndiya kongeza ukuba ukuba iPostgres iqulethe idatha eyimfihlo kwaye ayinakubandakanywa kwindawo yovavanyo, ngoko ungasebenzisa PostgreSQL Anonymizer. Iskimu simalunga noku kulandelayo:

umthombo: www.habr.com