Ukulungiswa kweLinux ukuphucula ukusebenza kwePostgreSQL. Ilya Kosmodemyansky

Ushicilelo lwengxelo ka-2015 ngu-Ilya Kosmodemyansky "Ukulungiswa kweLinux ukuphucula ukusebenza kwePostgreSQL"

Inkcazo: Ndiyaqaphela ukuba le ngxelo ibhalwe ngoNovemba 2015 - ngaphezu kweminyaka emi-4 idlulile kwaye ixesha elininzi lidlulile. Inguqulelo 9.4 ekuxoxwe ngayo kwingxelo ayisaxhaswa. Kwisithuba seminyaka emi-4 edlulileyo, ii-5 ezintsha zokukhutshwa kwe-PostgreSQL zikhutshiwe, kwaye iinguqulelo ze-15 ze-Linux kernel zikhutshiwe. Ukuba uphinda ubhale ezi ndinyana, uya kugqiba ngengxelo eyahlukileyo. Kodwa apha sithathela ingqalelo ukulungiswa kweLinux kwiPostgreSQL, esasebenza nanamhlanje.

Igama lam ndingu-Ilya Kosmodemyansky. Ndisebenza ePostgreSQL-Consulting. Kwaye ngoku ndiza kuthetha kancinci malunga nokwenza ngeLinux ngokunxulumene nogcino-lwazi ngokubanzi kunye nePostgreSQL ngokukodwa, kuba imigaqo iyafana.

Siza kuthetha ngantoni? Ukuba unxibelelana nePostgreSQL, ngoko ke kwinqanaba elithile kufuneka ube ngumlawuli weUNIX. Ingaba ithetha ntoni? Ukuba sithelekisa i-Oracle kunye ne-PostgreSQL, ngoko kwi-Oracle kufuneka ube ngu-80% we-DBA yolawulo lwedatha kunye ne-20% ye-Linux admin.

NgePostgreSQL inzima ngakumbi. NgePostgreSQL kufuneka ube nokuqonda okungcono kakhulu kwindlela iLinux esebenza ngayo. Kwaye kwangaxeshanye, baleka kancinci emva kwe-locomotive, kuba mva nje yonke into ihlaziywe kakuhle. Kwaye ii-kernels ezintsha zikhutshwa, kwaye ukusebenza okutsha kubonakala, ukusebenza kuphucula, njl.

Kutheni sithetha ngeLinux? Akunjalo konke konke ngenxa yokuba sikwinkomfa ye-Linux uPetros, kodwa ngenxa yokuba kwiimeko zanamhlanje enye yeendlela zokusebenza ezifanelekileyo zokusebenzisa i-database ngokubanzi kunye ne-PostgreSQL ngokukodwa yi-Linux. Kuba iFreeBSD, ngelishwa, ikhula kwicala elingaqhelekanga kakhulu. Kwaye kuya kubakho iingxaki zombini kunye nokusebenza kunye nezinye izinto ezininzi. Ukusebenza kwePostgreSQL kwiWindows ngokuqhelekileyo ngumba onzima owahlukileyo, osekwe kwinto yokuba iWindows ayinayo inkumbulo ekwabelwana ngayo njenge-UNIX, ngelixa i-PostgreSQL ibotshelelwe koku, kuba yinkqubo yenkqubo ezininzi.

Kwaye ndicinga ukuba wonke umntu akanamdla kangako kwii-exotics ezifana ne-Solaris, masihambe.

Ukuhanjiswa kweLinux yangoku kuneenketho ezingaphezu kwe-1 ze-syctl, kuxhomekeke kwindlela owakha ngayo ikernel. Ngexesha elifanayo, ukuba sijonga amantongomane ahlukeneyo, sinokuhlengahlengisa into ngeendlela ezininzi. Kukho iiparamitha zesixokelelwano sefayile ngendlela yokunyuswa kwazo. Ukuba unemibuzo malunga nendlela yokuyiqala ngayo: yintoni onokuyenza kwi-BIOS, indlela yokuqwalasela i-hardware, njl.

Lo ngumthamo omkhulu kakhulu onokuthi uxoxwe kwiintsuku ezininzi, kwaye kungekhona kwingxelo emfutshane enye, kodwa ngoku ndiza kugxila kwizinto ezibalulekileyo, indlela yokuphepha ezo zixhobo ziqinisekisiwe ukukuthintela ekusebenziseni i-database yakho kakuhle kwi-Linux ukuba sukuwalungisa . Kwaye ngexesha elifanayo, ingongoma ebalulekileyo kukuba ezininzi iiparameters ezingagqibekanga azifakwanga kwizicwangciso ezichanekileyo kwisiseko sedatha. Oko kukuthi, ngokungagqibekanga iya kusebenza kakubi okanye hayi kwaphela.

Zeziphi iithagethi zohlengahlengiso lwemveli ezikhoyo kwiLinux? Ndicinga ukuba njengoko nonke nijongene nolawulo lweLinux, akukho mfuneko ithile yokuchaza ukuba zeziphi iithagethi.

Ungacula:

• ICPU.
• Imemori.
• Ukugcina.
• Okunye. Siza kuthetha ngale nto ekugqibeleni kwi-snack. Nokuba, umzekelo, iiparamitha ezinjengomgaqo-nkqubo wokonga amandla zinokuchaphazela ukusebenza ngendlela engalindelekanga kwaye ingeyona ndlela imnandi.

Ziziphi iinkcukacha zePostgreSQL kunye nedathabheyisi ngokubanzi? Ingxaki kukuba awukwazi ukwenza nayiphi na i-nut kwaye ubone ukuba ukusebenza kwethu kuphuculwe kakhulu.

Ewe, kukho izixhobo ezinjalo, kodwa i-database yinto enzima. Isebenzisana nazo zonke izixhobo ezinomncedisi kwaye zikhetha ukusebenzisana ngokupheleleyo. Ukuba ujonga iingcebiso zangoku ze-Oracle malunga nendlela yokusebenzisa i-OS ye-host host, iya kufana nehlaya malunga ne-cosmonaut yaseMongolia - yondla inja kwaye ungathinti nantoni na. Masinike i-database yonke izibonelelo, i-database ngokwayo iya kulungisa yonke into.

Ngokomgaqo, kwinqanaba elithile imeko iyafana ne-PostgreSQL. Umahluko kukuba i-database ayikakwazi ukuzithathela zonke izixhobo, oko kukuthi, kwindawo ethile kwinqanaba le-Linux kufuneka uzihlele ngokwakho.

Ingcinga ephambili ayikuko ukukhetha ithagethi enye kwaye uqalise ukuyilungisa, umzekelo, inkumbulo, i-CPU okanye into enjalo, kodwa ukuhlalutya umthwalo wokusebenza kwaye uzame ukuphucula ugqithiso kangangoko kunokwenzeka ukuze umthwalo owenziwe ngabadwelisi abalungileyo. kuthi, kuquka nabasebenzisi bethu.

Nanku umfanekiso ukuchaza ukuba yintoni na. Kukho i-Linux OS buffer kwaye kukho inkumbulo ekwabelwana ngayo kwaye kukho i-PostgreSQL ekwabelwana ngayo ibuffers. I-PostgreSQL, ngokungafaniyo ne-Oracle, isebenza ngokuthe ngqo kuphela kwi-kernel buffer, oko kukuthi, ukwenzela ukuba iphepha elivela kwidiski lingene kwimemori ekwabelwana ngayo, kufuneka lidlule kwi-kernel buffer kunye nomva, imeko efanayo.

Iidiski zihlala phantsi kwesi sixokelelwano. Ndizoba oku njengediski. Enyanisweni, kunokubakho umlawuli we-RAID, njl.

Kwaye le igalelo-imveliso ngenye indlela okanye enye kwenzeka ngalo mba.

I-PostgreSQL yisiseko sedatha sakudala. Kukho iphepha ngaphakathi. Kwaye lonke igalelo kunye nemveliso kwenzeka usebenzisa amaphepha. Sinyusa iibhloko kwimemori ngamaphepha. Kwaye ukuba akukho nto yenzekileyo, sizifundile nje, emva koko ngokuthe ngcembe ziyanyamalala kule cache, ukusuka kwiibuffers ekwabelwana ngazo kwaye ziphele zibuyela kwidiski.

Ukuba sibuyisela into kwindawo ethile, ke lonke iphepha liphawulwe njengelimdaka. Ndiziphawule apha ezi blue. Kwaye oku kuthetha ukuba eli phepha kufuneka lihambelane nokugcinwa kwebhloko. Oko kukuthi, xa sisenza ukungcola, senze ukungena kwi-WAL. Kwaye ngexesha elithile elimangalisayo, isiganeko esibizwa ngokuba yindawo yokuhlola yeza. Kwaye kwabhalwa ulwazi kule log ukuba ufikile. Kwaye oku kuthetha ukuba onke amaphepha amdaka awayelapha ngela xesha kwezi zithinteli ekwabelwana ngazo zaye zadityaniswa nediski yokugcina usebenzisa i-fsync ngaphaya kwe-kernel buffer.

Kutheni kusenziwa oku? Ukuba silahlekelwe yi-voltage, ngoko asizange sifumane imeko yokuba yonke idatha ilahlekile. Inkumbulo eqhubekayo, apho wonke umntu esixelele ngayo, ngoku ngoku kwi-theory ye-database - eli likamva eliqaqambileyo, esilizamayo kwaye siyayithanda, kodwa ngoku bahlala kwiminyaka engama-20. Kwaye, ngokuqinisekileyo, konke oku kufuneka kubekwe esweni.

Kwaye umsebenzi wokwandisa i-output kukulungisa kakuhle kuzo zonke ezi zigaba ukuze zonke zibuyele emva naphambili ngokukhawuleza. Inkumbulo ekwabelwana ngayo yicache yephepha. Kwi-PostgreSQL sithumele umbuzo okhethiweyo okanye into, ibuyise le datha kwidiski. Baye baphelela kwizithinteli ekwabelwana ngazo. Ngako oko, ukuze oku kusebenze ngcono, kufuneka kubekho inkumbulo eninzi.

Ukuze konke oku kusebenze kakuhle kwaye ngokukhawuleza, kufuneka ulungiselele ngokuchanekileyo inkqubo yokusebenza kuwo onke amanqanaba. Kwaye ukhethe i-hardware elinganiselayo, kuba ukuba unokungalingani kwindawo ethile, ngoko unokwenza imemori eninzi, kodwa ayiyi kunikwa inkonzo ngesantya esaneleyo.

Kwaye masihambe kuyo nganye kwezi ngongoma.

Ukwenza la maphepha ahambe ngokukhawuleza nangaphezulu, kufuneka ufezekise oku kulandelayo:

• Okokuqala, kufuneka usebenze ngokufanelekileyo ngememori.
• Okwesibini, olu tshintsho xa amaphepha ukusuka kwimemori ukuya kwidiski kufuneka asebenze ngakumbi.
• Kwaye okwesithathu, kufuneka kubekho iidiski ezilungileyo.

Ukuba unayo i-512 GB ye-RAM kwi-server kwaye yonke iphelela kwi-SATA hard drive ngaphandle kwe-cache, ke yonke iseva yedatha yedatha ijika ibe kungekhona nje ithanga, kodwa ithanga kunye ne-SATA interface. Uya kubaleka kuyo ngokuthe ngqo. Kwaye akukho nto iya kukusindisa.

Ngokuphathelele ingongoma yokuqala ngenkumbulo, kukho izinto ezintathu ezinokwenza ubomi bube nzima kakhulu.

Eyokuqala kubo yi-NUMA. I-NUMA yinto eyenzelwe ukuphucula ukusebenza. Ngokuxhomekeke kumthwalo womsebenzi, izinto ezahlukeneyo zinokulungiswa. Kwaye kuhlobo lwayo olutsha lwangoku, ayilunganga kakhulu kwizicelo ezinje ngogcino lwedatha olusebenzisa ngokunzulu i-cache yephepha ekwabelwana ngayo iibuffers.

Amaqokobhe enathi. Unokwazi njani ukuba kukho undonakele kwiNUMA? Unohlobo oluthile lokunkqonkqoza okungathandekiyo, ngequbuliso enye i-CPU igcwele kakhulu. Kwangaxeshanye, uhlalutya imibuzo kwiPostgreSQL kwaye ubone ukuba akukho nto ifanayo apho. Le mibuzo akufuneki ibe yi-CPU enzulu. Ungakubamba oku ixesha elide. Kulula ukusebenzisa iingcebiso ezichanekileyo kwasekuqaleni malunga nendlela yokuqwalasela i-NUMA ye-PostgreSQL.

Kwenzeka ntoni kanye kanye? I-NUMA imele i-Non-Uniform Memory Access. Inceda ngantoni? Une-CPU, ecaleni kwayo kukho inkumbulo yendawo. Kwaye le nkumbulo idibanisa inokutsala imemori kwezinye ii-CPU.

Ukuba uyabaleka numactl --hardware, ngoko uya kufumana iphepha elikhulu kangaka. Phakathi kwezinye izinto, kuya kubakho ibala lokude. Kuya kubakho amanani - 10-20, into enjalo. La manani ayonto ingaphezulu kwenani leehops zokuchola le nkumbulo ikude kwaye uyisebenzise ekuhlaleni. Ngokomgaqo, ingcamango elungileyo. Oku kukhawulezisa ukusebenza kakuhle phantsi koluhlu lwemithwalo yemisebenzi.

Ngoku khawufane ucinge ukuba une-CPU enye kuqala uzama ukusebenzisa imemori yayo yasekhaya, emva koko uzama ukutsala enye imemori ngonxibelelwano lwento ethile. Kwaye le CPU ifumana i-cache yakho yephepha le-PostgreSQL yonke-yiyo, ezinye iigigabytes. Uhlala ufumana eyona meko imbi, kuba kwi-CPU kuhlala kukho inkumbulo encinci kule modyuli ngokwayo. Kwaye yonke inkumbulo enikwa inkonzo idlula kwezi ziqhakamshelwano. Ijika icothe kwaye ilusizi. Kwaye iprosesa yakho enikezela iinkonzo kule node ihlala igcwele kakhulu. Kwaye ixesha lokufikelela kule nkumbulo libi, licotha. Le yimeko ongayifuniyo ukuba usebenzisa le database.

Ngoko ke, ukhetho oluchanekileyo kakhulu lwesiseko sedatha yesistimu yokusebenza ye-Linux yokungazi ukuba kuqhubeka ntoni apho. Ukuze ifikelele kwimemori njengoko isenza.

Kutheni kunjalo? Kuya kubonakala ngathi kufanele kube ngenye indlela. Oku kwenzeka ngesizathu esinye esilula: sidinga imemori eninzi yecache yephepha - amashumi, amakhulu egigabytes.

Kwaye ukuba sinikeze yonke le nto kwaye sagcina idatha yethu apho, ke inzuzo ekusebenziseni i-cache iya kuba nkulu kakhulu kunenzuzo evela kufikelelo olukhohlisayo kwimemori. Kwaye ke siya kuxhamla ngendlela engenakuthelekiswa nanto yokuba siya kufikelela kwimemori ngokufanelekileyo ngakumbi sisebenzisa i-NUMA.

Ngoko ke, kukho iindlela ezimbini apha okwangoku, de kube ikusasa eliqaqambileyo lifikile, kwaye i-database ngokwayo ayikwazi ukufumanisa ukuba yiyiphi i-CPU eqhuba kuyo kwaye kufuneka ikhuphe into ethile.

Ke ngoko, indlela echanekileyo kukuyekisa i-NUMA ngokupheleleyo, umzekelo, xa uqalisa kwakhona. Kwiimeko ezininzi, ii-winnings zezomyalelo wobukhulu kangangokuba umbuzo ongcono awuveli kwaphela.

Kukho enye inketho. Siyisebenzisa rhoqo kuneyokuqala, kuba xa umxhasi esiza kuthi ukuze afumane inkxaso, ukuqalisa kwakhona umncedisi yinto enkulu kuye. Uneshishini apho. Kwaye bafumana iingxaki ngenxa yeNUMA. Ke ngoko, sizama ukuyikhubaza ngeendlela ezingentle kunokuphinda uyiqalise, kodwa qaphela ukuyijonga ukuba ivaliwe. Kuba, njengoko amava ebonisa, kulungile ukuba sivale i-NUMA kwinkqubo yomzali ye-PostgreSQL, kodwa akuyomfuneko kwaphela ukuba isebenze. Kufuneka sijonge kwaye sibone ukuba ucimile ngokwenene na.

Kukho isithuba esihle nguRobert Haas. Le yenye yeekontraki zePostgreSQL. Omnye wabaphuhlisi abaphambili bazo zonke iigiblets ezisezantsi. Kwaye ukuba ulandela amakhonkco kule post, achaza amabali anemibala emininzi malunga nendlela i-NUMA eyenza ngayo ubomi banzima ebantwini. Jonga, funda uluhlu lolawulo lwenkqubo lwento ekufuneka iqwalaselwe kwiseva ukuze idatabase yethu isebenze kakuhle. Ezi zicwangciso kufuneka zibhalwe phantsi kwaye zitshekishwe, kuba ngaphandle koko akuyi kuba kuhle kakhulu.

Nceda uqaphele ukuba oku kusebenza kuzo zonke iisetingi endiza kuthetha ngazo. Kodwa ngokwesiqhelo i-database iqokelelwa kwimowudi ye-master-slave ukunyamezela impazamo. Ungalibali ukwenza ezi setingi kwikhoboka kuba ngenye imini uya kuba nengozi kwaye utshintshele kwikhoboka kwaye liya kuba yinkosi.

Kwimeko engxamisekileyo, xa yonke into imbi kakhulu, ifowuni yakho ihlala ikhala kwaye umphathi wakho uza egijima ngentonga enkulu, awuyi kuba nexesha lokucinga ngokujonga. Yaye imiphumo inokuba yintlekele.

Inqaku elilandelayo linamaphepha amakhulu. Amaphepha amakhulu kunzima ukuvavanya ngokwahlukileyo, kwaye akukho sizathu sokwenza oko, nangona kukho i-benchmarks enokukwenza oku. Zilula kuGoogle.

Inceda ngantoni? Unomncedisi ongabizi kakhulu kunye ne-RAM eninzi, umzekelo, ngaphezu kwe-30 GB. Awusebenzisi amaphepha amakhulu. Oku kuthetha ukuba ngokuqinisekileyo une-overhead malunga nokusetyenziswa kwememori. Kwaye le overhead kude kude kakhulu kumnandi.

Kutheni kunjalo? Kuqhubeka ntoni? Inkqubo yokusebenza yabela imemori ngamaqhekeza amancinci. Kulunge kakhulu, yindlela eyenzeka ngayo ngokwembali. Kwaye ukuba singena kwiinkcukacha, i-OS kufuneka iguqulele iidilesi ezibonakalayo kwizinto eziphathekayo. Kwaye le nkqubo ayisiyiyo eyona ilula, ngoko ke i-OS igcina isiphumo salo msebenzi kwi- Translation Lookaside Buffer (TLB).

Kwaye ekubeni i-TLB iyi-cache, zonke iingxaki ezikhoyo kwi-cache zivela kule meko. Okokuqala, ukuba une-RAM eninzi kwaye yonke yabelwe iziqwenga ezincinci, ke le buffer iba nkulu kakhulu. Kwaye ukuba i-cache inkulu, ukukhangela kuyo kuyacotha. I-Overhead isempilweni kwaye yona ngokwayo ithatha indawo, oko kukuthi i-RAM ityiwa yinto engalunganga. Ngeli xesha.

Ezimbini - ngakumbi i-cache ikhula kwimeko enjalo, kunokwenzeka ngakumbi ukuba uya kuba ne-cache misss. Kwaye ukusebenza kakuhle kwale cache kuncipha ngokukhawuleza njengoko ubukhulu bayo bukhula. Ngoko ke, iinkqubo zokusebenza zeza nendlela elula. Isetyenziswe kwiLinux ixesha elide. Ivele kwiFreeBSD kungekudala. Kodwa sithetha ngeLinux. La ngamaphepha amakhulu.

Kwaye apha kufuneka kuqatshelwe ukuba amaphepha amakhulu, njengombono, ekuqaleni atyhalwa luluntu olubandakanya i-Oracle kunye ne-IBM, o.k.t. abavelisi bedatabase bacinga ngamandla ukuba oku kuya kuba luncedo koovimba beenkcukacha.

Kwaye oku kungenziwa njani ngabahlobo ngePostgreSQL? Okokuqala, amaphepha amakhulu kufuneka avulwe kwi-Linux kernel.

Okwesibini, kufuneka zichazwe ngokucacileyo yiparamitha ye-sysctl - zingaphi na. Amanani apha asuka kwenye iseva endala. Ungabala ukuba zingaphi izithinteli ekwabelwana ngazo onazo ukuze amaphepha amakhulu akwazi ukungena apho.

Kwaye ukuba iseva yakho yonke inikezelwe kwi-PostgreSQL, isiqalo esihle kukwaba nokuba yi-25% ye-RAM kwi-buffers ekwabelwana ngayo, okanye i-75% ukuba uqinisekile ukuba i-database yakho ngokuqinisekileyo iya kungena kule 75%. Inqaku lokuqala. Kwaye qwalasela, ukuba unayo i-256 GB ye-RAM, ngoko, ngokufanelekileyo, uya kuba ne-64 GB ye-buffers enkulu. Bala malunga nomda othile - ukuba lo mfanekiso umiselwe ukuba ube yintoni.

Phambi kwenguqulelo 9.2 (ukuba andiphazamanga, ukusukela kwinguqulelo 8.2), bekunokwenzeka ukudibanisa iPostgreSQL enamaphepha amakhulu usebenzisa ithala leencwadi lesithathu. Kwaye oku kufuneka kuhlale kusenziwa. Okokuqala, udinga i-kernel ukuze ukwazi ukwaba amaphepha amakhulu ngokuchanekileyo. Kwaye, okwesibini, ukwenzela ukuba isicelo esisebenza nabo sinokuzisebenzisa. Ayizukusetyenziswa ngolo hlobo nje. Ukusukela ukuba iPostgreSQL yabelwe inkumbulo kwisitayile sesi-5, oku kunokwenziwa kusetyenziswa i-libhugetlbfs- eli ligama elipheleleyo lethala leencwadi.

Kwi-9.3, ukusebenza kwe-PostgreSQL kwaphuculwa xa usebenza ngememori kwaye inkqubo ye-5 yendlela yokwabiwa kwememori ishiywe. Wonke umntu wayevuya kakhulu, kuba ngaphandle koko uzama ukuqhuba iimeko ezimbini ze-PostgreSQL kumatshini omnye, kwaye uthi andinayo imemori eyaneleyo eyaneleyo. Kwaye uthi i-sysctl ifuna ukulungiswa. Kwaye kukho i-sysctl enje ukuba kusafuneka uqalise kwakhona, njl. Ngokuqhelekileyo, wonke umntu wayevuya. Kodwa ulwabiwo lwenkumbulo yemmap yaphula ukusetyenziswa kwamaphepha amakhulu. Uninzi lwabathengi bethu basebenzisa ii-buffers ezinkulu ekwabelwana ngazo. Kwaye sincoma kakhulu ukuba singatshintsheli kwi-9.3, kuba i-overhead apho yaqala ukubalwa ngeepesenti ezilungileyo.

Kodwa uluntu luye lwayihoya le ngxaki kwaye ngo-9.4 baphinda bawulungisa kakuhle lo msitho. Kwaye ngo-9.4 iparamitha yavela kwi-postgresql.conf apho unokwenza ukuba uzame, ukuvala okanye ukuvala.

Zama yeyona ndlela ikhuselekileyo. Xa iPostgreSQL iqala, xa yabela imemori ekwabelwana ngayo, izama ukubamba le nkumbulo kumaphepha amakhulu. Kwaye ukuba ayisebenzi, iphinda ibuyele kukhetho oluqhelekileyo. Kwaye ukuba unayo i-FreeBSD okanye i-Solaris, ngoko unokuzama, ihlala ikhuselekile.

Ukuba ivuliwe, ayiqalisi ukuba ayikwazanga ukukhetha kumaphepha amakhulu. Nantsi sele imalunga nokuba ngubani kwaye yintoni elunge ngakumbi. Kodwa ukuba uzamile, jonga ke ukuba unayo ngokwenene into oyifunayo, kuba kukho indawo enkulu yempazamo. Okwangoku oku kusebenza kusebenza kuphela kwiLinux.

Elinye inqaku elincinci ngaphambi kokuba siye phambili. Amaphepha amakhulu acacileyo akakho malunga nePostgreSQL okwangoku. Akakwazi ukuzisebenzisa ngokuqhelekileyo. Kwaye kunye namaphepha amakhulu eTransparent kumthwalo onjalo womsebenzi, xa iqhekeza elikhulu lememori ekwabelwana ngayo lifuneka, izibonelelo ziza kuphela kunye nemiqulu emikhulu kakhulu. Ukuba unememori yeeterabytes ke oku kungangena kudlala. Ukuba sithetha malunga nezicelo zemihla ngemihla, xa unama-32, 64, 128, 256 GB yememori kumatshini wakho, ke amaphepha amakhulu aqhelekileyo athi Kulungile, kwaye sikhubaza ngokulula iTransparent.

Kwaye into yokugqibela malunga nenkumbulo ayinxulumananga ngokuthe ngqo ne fruitut, inokonakalisa ngokwenene ubomi bakho. Konke ukuthunyelwa kuya kuchaphazeleka kakhulu yinto yokuba umncedisi uhlala etshintshisa.

Kwaye oku kuya kuba mnandi kakhulu ngeendlela ezininzi. Kwaye eyona ngxaki iphambili kukuba iinkozo zangoku ziziphatha ngokwahlukileyo kwii-Linux kernels ezindala. Kwaye le nto ayimnandanga ukunyathela, kuba xa sithetha ngolunye uhlobo lomsebenzi kunye nokutshintshana, iphetha ngokufika ngokungalindelekanga kwe-OOM-killer. Kwaye i-OOM-killer, engazange ifike ngexesha elifanelekileyo kwaye ilahle i-PostgreSQL, ayithandeki. Wonke umntu uyazi malunga noku, oko kukuthi, de kube ngumsebenzisi wokugqibela.

Kwenzeka ntoni? Unenani elikhulu le-RAM apho, yonke into isebenza kakuhle. Kodwa ngesizathu esithile umncedisi ujinga kutshintshiselwano kwaye uyacotha ngenxa yoku. Kubonakala ngathi kukho inkumbulo eninzi, kodwa oku kuyenzeka.

Ngaphambili, sicebise ukuseta i-vm.swappiness ukuya kwi-zero, o.k. ukukhubaza utshintshiselwano. Ngaphambili, kwakubonakala ngathi i-32 GB ye-RAM kunye ne-buffers ekwabelwana ngayo yayisisixa esikhulu. Injongo ephambili yokutshintshana kukuba nendawo yokuphosa i-crust ukuba siyawa. Yaye ayizange izaliseke ngokukhethekileyo. Kwaye uza kwenza ntoni ngolu qweqwe? Lo ngumsebenzi apho kungacacanga kakuhle ukuba kutheni utshintsho lufuneka, ngakumbi ubungakanani obunjalo.

Kodwa kwimihla yanamhlanje, oko kukuthi, iinguqulelo zesithathu ze-kernel, ukuziphatha kutshintshile. Kwaye ukuba useta ukutshintshela kwi-zero, oko kukuthi, yicime, kungekudala okanye kamva, nokuba kukho i-RAM eseleyo, umbulali we-OOM uya kuza kuwe ukuze abulale abona bathengi banzulu. Ngenxa yokuba uya kuqwalasela ukuba ngomthwalo onjalo womsebenzi sisesencinci kwaye siya kuphuma, oko kukuthi, kungekhona ukubethelela inkqubo yenkqubo, kodwa ukubethelela into engabalulekanga kangako. Lo ungabalulekanga kangako uya kuba ngumthengi onzulu wenkumbulo ekwabelwana ngayo, oko kukuthi umphathi weposi. Kwaye emva koko kuya kuba kuhle ukuba isiseko akufanele sibuyiselwe.

Ke ngoko, ngoku okungagqibekanga, njengoko ndikhumbulayo, uninzi lonikezelo lukwindawo ethile malunga no-6, o.k.t. kufanele uqale ngeliphi inqanaba ukusebenzisa utshintshise ngokuxhomekeke ekubeni ingakanani inkumbulo eseleyo. Ngoku sincoma ukuseta vm.swappiness = 1, kuba oku kuyayicima, kodwa ayiniki iziphumo ezifanayo kunye nombulali we-OOM ofike ngokungalindelekanga wabulala yonke into.

Yintoni elandelayo? Xa sithetha malunga nokusebenza kwedatha kwaye ngokuthe ngcembe siye kwiidiski, wonke umntu uqala ukubamba iintloko zabo. Kuba inyaniso yokuba idiski icotha kwaye inkumbulo ikhawuleza iqhelekile kumntu wonke kwasebuntwaneni. Kwaye wonke umntu uyazi ukuba i-database iya kuba neengxaki zedisk yokusebenza.

Eyona ngxaki iphambili yokusebenza kwePostgreSQL edityaniswe ne-spikes ye-checkpoints ayenzeki kuba idiski iyacotha. Oku kunokwenzeka ngenxa yokuba imemori kunye nedisk bandwidth azilingani. Nangona kunjalo, basenokungalingani kwiindawo ezahlukeneyo. I-PostgreSQL ayilungiswanga, i-OS ayilungiswanga, i-hardware ayilungiswanga kwaye i-hardware ayichanekanga. Kwaye le ngxaki ayenzeki kuphela ukuba yonke into iyenzeka njengoko kufanelekile, oko kukuthi, akukho mthwalo, okanye izicwangciso kunye ne-hardware zikhethwe kakuhle.

Yintoni kwaye ikhangeleka njani? Ngokuqhelekileyo abantu abasebenza ngePostgreSQL bangene kulo mbandela ngaphezu kwesihlandlo esinye. Ndiza kucacisa. Njengoko benditshilo, iPostgreSQL ngamanye amaxesha yenza iindawo zokujonga ukulahla amaphepha amdaka kwimemori ekwabelwana ngayo kwidiski. Ukuba sinomthamo omkhulu wememori ekwabelwana ngayo, ke indawo yokukhangela iqala ukuba nempembelelo enzulu kwidiski, kuba ilahla la maphepha ngefsync. Ifika kwi-kernel buffer kwaye ibhalwe kwiidiski kusetyenziswa i-fsync. Kwaye ukuba umthamo weli shishini mkhulu, ngoko sinokubona umphumo ongathandekiyo, oko kukuthi ukusetyenziswa okukhulu kweediski.

Apha ndinemifanekiso emibini. Ndiza kucacisa ukuba yintoni na. Ezi ziigrafu ezimbini ezihambelana nexesha. Igrafu yokuqala kusetyenziso lwedisk. Apha ifikelela phantse kuma-90% ngeli xesha. Ukuba unokungaphumeleli kwedatha kunye neediski eziphathekayo, kunye nokusetyenziswa komlawuli we-RAID kwi-90%, ke ezi ziindaba ezimbi. Oku kuthetha ukuba kancinci kwaye iya kufikelela kwi-100 kwaye i-I / O iya kuyeka.

Ukuba unoluhlu lwediski, libali elahluke kancinane. Kuxhomekeke kwindlela eqwalaselwe ngayo, luhlobo luni loluhlu, njl.

Kwaye ngokunxuseneyo, igrafu evela kwimboniselo yangaphakathi ye-postgres iqwalaselwe apha, exela ukuba indawo yokukhangela yenzeka njani. Kwaye umbala oluhlaza apha ubonisa ukuba zingaphi ii-buffers, la maphepha amdaka, ngelo xesha afikile kule ndawo yokukhangela ungqamaniso. Kwaye le yeyona nto iphambili ekufuneka uyazi apha. Siyabona ukuba sinamaphepha amaninzi apha kwaye ngexesha elithile sibetha ibhodi, oko kukuthi, sabhala kwaye sabhala, apha inkqubo yediski ngokucacileyo ixakeke kakhulu. Kwaye indawo yethu yokukhangela inempembelelo enamandla kakhulu kwidiski. Ngokufanelekileyo, imeko kufuneka ijonge ngakumbi ngolu hlobo, o.k.t. besinokurekhoda okuncinci apha. Kwaye sinokuyilungisa kunye nezicwangciso ukuze iqhubeke inje. Oko kukuthi, ukurisayikilishwa kuncinci, kodwa kwenye indawo sibhala into apha.

Yintoni ekufuneka yenziwe ukuze koyiswe le ngxaki? Ukuba uye wamisa i-IO phantsi kwesiseko sedatha, oku kuthetha ukuba bonke abasebenzisi abeze ukuzalisekisa izicelo zabo baya kulinda.

Ukuba ujonga ukusuka kwindawo yembono yeLinux, ukuba uthathe ihardware elungileyo, uyiqwalasele ngokuchanekileyo, iqwalaselwe iPostgreSQL ngokwesiqhelo ukuze yenze ezi ndawo zikhangelwe rhoqo, zisasaze ngokuhamba kwexesha phakathi komnye nomnye, emva koko ungene kwi-debian parameters. KwiiLinux ezininzi zonikezelo, lo ngumfanekiso: vm.dirty_ratio=20, vm.dirty_background_ratio=10.

Ingaba ithetha ntoni? Idemon enye egungxulwayo yavela kwi-kernel 2.6. I-Pdglush, kuxhomekeke ekubeni ngubani oyisebenzisayo, ebandakanyeka kwimvelaphi yokulahla amaphepha angcolileyo kwi-kernel buffer kunye nokulahla xa kuyimfuneko ukulahla amaphepha angcolileyo kungakhathaliseki ukuba yintoni na, xa ukulahla i-backgrouind akuncedi.

Ivela nini imvelaphi? Xa i-10% ye-RAM iyonke ekhoyo kumncedisi ihlalwa ngamaphepha amdaka kwi-kernel buffer, umsebenzi okhethekileyo wokubhala ubizwa ngasemva. Kutheni imvelaphi? Njengeparamitha, ithathela ingqalelo ukuba mangaphi amaphepha oza kuwabhala. Kwaye, masithi, ubhala kumaphepha e-N. Kwaye okwethutyana le nto ilala. Kwaye emva koko uyeza kwakhona akhuphele amanye amaphepha.

Eli libali elilula kakhulu. Ingxaki apha ifana nedama lokuqubha, xa ligalela komnye umbhobho, lingene komnye. Indawo yethu yokukhangela ifikile kwaye ukuba ithumele amaphepha amdaka ambalwa ukuba alahlwe, ke ngokuthe ngcembe yonke into iya kusonjululwa kakuhle kwi-kernel buffer pgflush.

Ukuba la maphepha amdaka aqhubeka eqokelela, aqokelela ukuya kuthi ga kwi-20%, emva koko i-OS iphambili kukubhala yonke into kwidiski, kuba amandla aya kusilela kwaye yonke into iya kuba mbi kuthi. Siya kuphulukana nale datha, umzekelo.

Lithini iqhinga? Ubuqhetseba kukuba ezi parameters kwihlabathi lanamhlanje ngama-20 kunye ne-10% ye-RAM iyonke ekumatshini, ziyoyikeka ngokupheleleyo ngokwendlela yokuphuma kuyo nayiphi na inkqubo yediski onayo.

Khawucinge ukuba une-128 GB ye-RAM. I-12,8 GB ifika kwisistim yediski yakho. Kwaye nokuba yeyiphi i-cache onayo apho, nokuba yeyiphi na uluhlu onayo apho, abayi kuhlala ixesha elide.

Ke ngoko, sicebisa ukuba uhlengahlengise kwangoko la manani ngokusekwe kubuchule bomlawuli wakho we-RAID. Ngokukhawuleza ndenze isindululo apha kumlawuli one-512 MB ye-cache.

Yonke into ithathwa njengento elula kakhulu. Ungabeka i-vm.dirty_background kwii-bytes. Kwaye ezi setingi zirhoxisa ezimbini ezidlulileyo. Nokuba umlinganiselo ungowendalo, okanye ezo zineebhayithi ziyasebenza, ngoko ke ezo zineebhayithi ziyakusebenza. Kodwa ekubeni ndingumcebisi we-DBA kwaye ndisebenza kunye nabathengi abahlukeneyo, ndizama ukuzoba ii-straws kwaye ngoko ke, ukuba ngama-bytes, ngoko-bytes. Akukho mntu unike nasiphi na isiqinisekiso sokuba umlawuli olungileyo akayi kongeza imemori kwiseva, uyiqalise kwakhona, kwaye inani liya kuhlala lifana. Bala nje la manani ukuze yonke into ingene apho kunye nesiqinisekiso.

Kwenzeka ntoni xa ungangeni? Ndibhalile ukuba nakuphi na ukugungxulwa kuyekwa ngokufanelekileyo, kodwa eneneni lo ngumzekeliso wentetho. Inkqubo yokusebenza inengxaki enkulu - inamaphepha amaninzi angcolileyo, ngoko ke i-IO eyenziwa ngabaxhasi bakho imisiwe ngokufanelekileyo, oko kukuthi isicelo sifikile ukuthumela umbuzo we-sql kwisiseko sedatha, ilindile. Naliphi na igalelo/imveliso kuyo yeyona iphambili ngokubaluleka, kuba uvimba weenkcukacha uhlala kwindawo yokukhangela. Kwaye xa egqiba ukugqiba akucaci ngokupheleleyo. Kwaye xa ufumene ukugungxulwa okungasemva, oko kuthetha ukuba yonke i-IO yakho ixakwe yiyo. Kwaye ide iphele, awuyi kwenza nto.

Kukho amanqaku amabini abalulekileyo apha angaphaya kwemida yale ngxelo. Ezi zicwangciso kufuneka zihambelane nezicwangciso kwi-postgresql.conf, o.k. iisetingi zeendawo zokutshekisha. Kwaye inkqubo yakho yediski kufuneka iqwalaselwe ngokufanelekileyo. Ukuba une-cache kwi-RAID, ngoko kufuneka ibe nebhetri. Abantu bathenga i-RAID nge-cache elungileyo ngaphandle kwebhetri. Ukuba unayo i-SSD kwi-RAID, ngoko kufuneka ibe yiseva, kufuneka kubekho ii-capacitors apho. Nalu uluhlu lokukhangela oluneenkcukacha. Eli khonkco liqulethe ingxelo yam malunga nendlela yokuqwalasela idiski yokusebenza kwi-PostgreSQL. Kukho zonke ezi zintlu zokutshekisha apho.

Yintoni enye enokwenza ubomi bube nzima? Ezi parameters ezimbini. Zisentsha noko. Ngokungagqibekanga, zinokufakwa kwizicelo ezahlukeneyo. Yaye zinokwenza ubomi bube nzima ngolo hlobo ukuba azivulwanga ngendlela engafanelekanga.

Kukho izinto ezimbini ezintsha. Sele bevele kwi-cores yesithathu. Le yi-sched_migration_cost kwi-nanoseconds kunye ne-sched_autogroup_enabled, enye ngokungagqibekanga.

Yaye babonakalisa njani ubomi bakho? Yintoni i-sched_migration_cost? Kwi-Linux, umcwangcisi unokufudusa inkqubo ukusuka kwi-CPU ukuya kwenye. Kwaye kwiPostgreSQL, eyenza imibuzo, ukufudukela kwenye i-CPU akucaci ngokupheleleyo. Ukusuka kwindawo yokujonga inkqubo, xa utshintsha iifestile phakathi kwe-openoffice kunye ne-terminal, oku kunokuba kuhle, kodwa kwisiseko sedatha kubi kakhulu. Ke ngoko, umgaqo-nkqubo ofanelekileyo kukuseta migration_cost kwixabiso elithile elikhulu, ubuncinci amawaka aliqela nanoseconds.

Ingaba oku kuya kuthetha ntoni kumcwangcisi? Kuya kuqwalaselwa ukuba ngeli xesha inkqubo isatshisa. Oko kukuthi, ukuba unentengiselwano yexesha elide eyenze into ethile ixesha elide, umcwangcisi uya kuqonda oku. Uya kucinga ukuba de kube eli xesha liphelile, akukho mfuneko yokufuduka le nkqubo naphi na. Ukuba ngexesha elifanayo inkqubo yenza into ethile, ngoko ayiyi kufuduka naphi na, iya kusebenza ngokuthula kwi-CPU eyabelwe yona. Kwaye umphumo ubalaseleyo.

Inqaku lesibini li-autogroup. Kukho uluvo olulungileyo lwemithwalo yemisebenzi ethile enganxulumananga nogcino-lwazi lwangoku - oku kukwenza iqela leenkqubo nge-terminal enenyani apho ziqaliswe khona. Oku kulungele eminye imisebenzi. Ngokwesiqhelo, i-PostgreSQL yinkqubo yenkqubo ezininzi ene-prefork esebenza kwi-terminal enye. Unombhali wokutshixa, indawo yokukhangela, kwaye zonke izicelo zakho zabathengi ziya kufakwa zibe ngumcwangcisi omnye, ngokwe-CPU nganye. Kwaye baya kumlinda apho ngazwinye ukuze akhululeke, ukuze baphazamisane kwaye bamgcine exakekile. Eli libali elingadingekile ngokupheleleyo kwimeko yomthwalo onjalo kwaye ngoko kufuneka ucinywe.

Umlingane wam u-Alexey Lesovsky wenza iimvavanyo nge-pgbench elula, apho wenyusa i-migration_cost ngomyalelo wobukhulu kwaye wacima i-autogroup. Umahluko kwi-hardware embi yayiphantse ibe yi-10%. Kukho ingxoxo kuluhlu lokuposa lwe-postgres apho abantu banika iziphumo zotshintsho olufanayo kwisantya sombuzo iphembelele 50%. Maninzi kakhulu amabali anjalo.

Kwaye ekugqibeleni, malunga nenkqubo yokonga amandla. Into entle kukuba iLinux ngoku ingasetyenziswa kwilaptop. Kwaye kuya kusebenzisa ibhetri kakuhle. Kodwa ngequbuliso kuvela ukuba oku kunokwenzeka nakumncedisi.

Ngaphezu koko, ukuba urenta iiseva kumphathi othile, ke ababuki zindwendwe "abalungileyo" abakhathali ukuba usebenza ngcono. Umsebenzi wabo kukuqinisekisa ukuba intsimbi yabo isetyenziswa ngokufanelekileyo kangangoko. Ke ngoko, ngokungagqibekanga banokwenza imowudi yokugcina amandla elaptop kwinkqubo yokusebenza.

Ukuba usebenzisa le nto kwiseva enesiseko sedatha phantsi komthwalo onzima, ngoko ukhetho lwakho luyi acpi_cpufreq + permornce. Nokuba kufunwa imfuno kuya kubakho iingxaki.

I-Intel_pstate ngumqhubi owahluke kancinane. Kwaye ngoku ukhetho lunikezelwa kule, njengoko isemva kwaye isebenza ngcono.

Kwaye, ngokufanelekileyo, irhuluneli yintsebenzo kuphela. Ukufuna, ukonga amandla kunye nayo yonke enye into ayithethi ngawe.

Iziphumo zokuchazwa kocazululo lwePostgreSQL zinokwahluka ngokwemiyalelo emininzi yobukhulu ukuba unika amandla ukonga, kuba ngokwenyani i-CPU ephantsi kwesiseko sedatha yakho iya kusebenza ngendlela engalindelekanga ngokupheleleyo.

Ezi zinto zinokubandakanywa ngokungagqibekanga. Jonga ngononophelo ukubona ukuba bayilayishile na ngokungagqibekanga. Oku kunokuba yingxaki enkulu ngokwenene.

Kwaye ekugqibeleni, bendifuna ukuthi enkosi kubafana beqela lethu le-PosgreSQL-Consulting DBA, elinguMax Boguk no-Alexey Lesovsky, abahamba phambili kulo mbandela yonke imihla. Kwaye sizama ukwenza konke okusemandleni ethu kubathengi bethu ukuze yonke into ibasebenzele. Kufana nemiyalelo yokhuseleko kwinqwelomoya. Yonke into elapha ibhalwe ngegazi. Nganye kula mandongomane ifumaneka kwinkqubo yolunye uhlobo lwengxaki. Ndiyavuya ukwabelana nawe ngazo.

Imibuzo:

Enkosi! Ukuba, umzekelo, inkampani ifuna ukugcina imali kwaye ibeke isiseko sedatha kunye nengqiqo yesicelo kwiseva enye, okanye ukuba inkampani ilandela imfashini ye-microservice architectures, apho i-PostgreSQL isebenza kwi-container. Lithini iqhinga? I-Sysctl iyakuchaphazela yonke i-kernel kwihlabathi jikelele. Andizange ndive malunga ne-sysctls ngandlela ithile yenziwe ukuba isebenze ngokwahlukeneyo kwisikhongozeli. Kukho iqela kuphela kwaye kukho inxalenye yolawulo apho. Ungaphila njani nale nto? Okanye ukuba ufuna ukusebenza, sebenzisa i-PostgreSQL kwiseva eyahlukileyo yehardware kwaye uyilungise?

Siphendule umbuzo wakho malunga neendlela ezintathu. Ukuba singathethi ngomncedisi we-hardware onokulungiswa, njl., emva koko phumla, yonke into iya kusebenza kakuhle ngaphandle kwezi zicwangciso. Ukuba unomthwalo onjalo ofuna ukwenza ezi zicwangciso, ngoko uya kuza kumncedisi wentsimbi ngaphambi kolu cwangciso.

Yintoni ingxaki? Ukuba lo ngumatshini wenyani, ngoko ke unokuba neengxaki ezininzi, umzekelo, ngenyani yokuba koomatshini abaninzi benyani i-latency yediski ayihambelani. Nokuba i-disk throughput ilungile, ke enye ayiphumelelanga intengiselwano ye-I/O engachaphazeli kakhulu umyinge wokutyhutyha okwenzeka ngexesha lokukhangela okanye ngexesha lokubhalwa kwe-WAL, ngoko ke i-database iya kubandezeleka kakhulu kule nto. Kwaye uya kuqaphela oku ngaphambi kokuba ungene kwezi ngxaki.

Ukuba unayo i-NGINX kumncedisi ofanayo, uya kuba nengxaki efanayo. Uya kulwela inkumbulo ekwabelwana ngayo. Kwaye awuyi kufika kwiingxaki ezichazwe apha.

Kodwa kwelinye icala, ezinye zezi parameters ziya kuhlala zibalulekile kuwe. Umzekelo, seta i-dirty_ratio kunye ne-sysctl ukuze ingaphambani kakhulu - kuyo nayiphi na imeko, oku kuya kunceda. Ngenye indlela okanye enye, uya kuba nokusebenzisana nediski. Kwaye kuya kuba ngokwepateni engalunganga. Oku kuqhelekile okumiselweyo kwiparameters endizibonisileyo. Kwaye nakweyiphi na imeko kungcono ukuyitshintsha.

Kodwa kusenokubakho iingxaki nge-NUMA. I-VmWare, umzekelo, isebenza kakuhle ne-NUMA enesetingi ezichasene ngqo. Kwaye apha kufuneka ukhethe - iseva yentsimbi okanye engeyiyo.

Ndinombuzo onxulumene neAmazon AWS. Banemifanekiso elungiselelwe kwangaphambili. Enye yazo ibizwa ngokuba yiAmazon RDS. Ngaba kukho naziphi na iisetingi zesiko kwinkqubo yazo yokusebenza?

Kukho iisetingi apho, kodwa zisetingi ezahlukeneyo. Apha siqwalasela indlela yokusebenza ngokwemigaqo yendlela isiseko sedatha esiza kuyisebenzisa ngayo le nto. Kwaye kukho iiparameters ezichaza ukuba kufuneka siye phi ngoku, njengokubunjwa. Oko kukuthi, sifuna izixhobo ezininzi, ngoku siza kuzitya. Emva koku, iAmazon RDS iqinisa ezi zixhobo, kwaye ukusebenza kuyehla apho. Kukho amabali ngamanye ngendlela abantu abaqala ngayo ukumosha lo mba. Ngamanye amaxesha ngempumelelo. Kodwa oku akunanto yakwenza noseto lwe-OS. Kufana nokuqhekeza ilifu. Libali elahlukileyo elo.

Kutheni amaphepha amakhulu eTransparent engenasiphumo xa kuthelekiswa neHuge TLB?

Musa ukupha. Oku kunokuchazwa ngeendlela ezininzi. Kodwa enyanisweni abanikezeli. Yintoni imbali yePostgreSQL? Ekuqaliseni, yabela iqhekeza elikhulu lememori ekwabelwanayo ngayo. Nokuba ziselubala okanye azibonakali akunamsebenzi kwaphela. Isibakala sokuba bagqame ekuqaleni sichaza yonke into. Kwaye ukuba kukho inkumbulo eninzi kwaye kufuneka uphinde uyakhe i-shared_memory segment, ngoko ke i-Transparent enkulu yamaphepha iya kuba yimfuneko. Kwi-PostgreSQL, yabelwe ngokulula kwi-chunk enkulu ekuqaleni kwaye yiyo, kwaye akukho nto ikhethekileyo eyenzekayo apho. Unako, ngokuqinisekileyo, ukuyisebenzisa, kodwa kukho ithuba lokufumana inkohlakalo ye-share_memory xa iphinda yabela into ethile. I-PostgreSQL ayiyazi ngale nto.

umthombo: www.habr.com