Ukushuna kwe-Linux ukuthuthukisa ukusebenza kwe-PostgreSQL. Ilya Kosmodemyansky

Okulotshiweyo kombiko ka-2015 ka-Ilya Kosmodemyansky "Ukulungiswa kweLinux ukuthuthukisa ukusebenza kwe-PostgreSQL"

Umshwana wokuzihlangula: Ngiyaqaphela ukuthi lo mbiko ungowomhla kaNovemba 2015 - sekudlule iminyaka engaphezu kwengu-4 futhi isikhathi esiningi sesidlulile. Inguqulo 9.4 okukhulunywe ngayo embikweni ayisasekelwa. Eminyakeni emi-4 edlule, kukhishwe okusha okungu-5 kwe-PostgreSQL, futhi kukhishwe izinguqulo eziyi-15 ze-Linux kernel. Uma ubhala kabusha lezi ziqephu, uzogcina usunombiko ohlukile. Kepha lapha sibheka ukulungiswa kweLinux okuyisisekelo kwe-PostgreSQL, okusasebenza nanamuhla.

Igama lami ngingu-Ilya Kosmodemyansky. Ngisebenza kwa-PostgreSQL-Consulting. Futhi manje ngizokhuluma kancane mayelana nokuthi yini okufanele yenziwe nge-Linux maqondana nemininingwane yolwazi ngokujwayelekile kanye ne-PostgreSQL ikakhulukazi, ngoba izimiso ziyefana.

Sizokhuluma ngani? Uma uxhumana ne-PostgreSQL, ngokwezinga elithile udinga ukuba umlawuli we-UNIX. Kusho ukuthini? Uma siqhathanisa i-Oracle ne-PostgreSQL, khona-ke ku-Oracle udinga ukuba ngu-80% wesizindalwazi sedatha ye-DBA kanye no-20% we-Linux admin.

Nge-PostgreSQL iyinkimbinkimbi kakhulu. Nge-PostgreSQL udinga ukuqonda kangcono kakhulu ukuthi i-Linux isebenza kanjani. Futhi ngesikhathi esifanayo, gijima kancane ngemva kwesitimela, ngoba muva nje yonke into ibuyekezwe kahle kakhulu. Futhi ama-kernels amasha akhululwa, futhi ukusebenza okusha kubonakala, ukusebenza kuyathuthuka, njll.

Kungani sikhuluma ngeLinux? Hhayi nakancane ngoba sisengqungqutheleni ye-Linux uPeter, kodwa ngenxa yokuthi ezimeni zesimanje enye yezinhlelo zokusebenza ezivumeleke kakhulu zokusebenzisa imininingwane yolwazi ngokujwayelekile kanye ne-PostgreSQL ikakhulukazi iLinux. Ngoba iFreeBSD, ngeshwa, ithuthuka ngendlela eyinqaba kakhulu. Futhi kuzoba nezinkinga zombili ngokusebenza kanye nezinye izinto eziningi. Ukusebenza kwe-PostgreSQL ku-Windows ngokuvamile kuwudaba olubalulekile oluhlukile, olusekelwe eqinisweni lokuthi i-Windows ayinayo inkumbulo eyabiwe njenge-UNIX, kuyilapho i-PostgreSQL iboshelwe kulokhu, ngoba iwuhlelo lwezinqubo eziningi.

Futhi ngicabanga ukuthi wonke umuntu akanantshisekelo kuma-exotics afana ne-Solaris, ngakho-ke asihambe.

Ukusatshalaliswa kweLinux yesimanje kunezinketho ezingaphezu kwe-1 ze-syctl, kuya ngokuthi uyakha kanjani i-kernel. Ngesikhathi esifanayo, uma sibheka amantongomane ahlukene, singalungisa okuthile ngezindlela eziningi. Kunezinhlaka zesistimu yefayela zokuthi zikhwezwa kanjani. Uma unemibuzo mayelana nokuthi ungayiqala kanjani: yini ongayinika amandla ku-BIOS, indlela yokumisa i-hardware, njll.

Lona ivolumu enkulu kakhulu engaxoxwa phakathi kwezinsuku ezimbalwa, futhi hhayi embikweni owodwa omfushane, kodwa manje ngizogxila ezintweni ezibalulekile, indlela yokugwema lawo ma-rakes aqinisekisiwe ukukuvimbela ekusebenziseni kahle i-database yakho ku-Linux uma ungawalungisi . Futhi ngesikhathi esifanayo, iphuzu elibalulekile ukuthi imingcele eminingi ezenzakalelayo ayifakiwe kuzilungiselelo ezilungile ku-database. Okusho ukuthi, ngokuzenzakalelayo izosebenza kabi noma ingasebenzi nhlobo.

Yiziphi izinhloso zokushuna zendabuko ezikhona ku-Linux? Ngicabanga ukuthi njengoba nonke nisebenzisana nokuphathwa kwe-Linux, asikho isidingo sokuchaza ukuthi yiziphi izinhloso.

Ungakwazi ukushuna:

• Ama-CPU.
• Inkumbulo.
• Isitoreji.
• Okunye. Sizokhuluma ngalokhu ekugcineni ukuze uthole ukudla okulula. Ngisho, isibonelo, amapharamitha afana nenqubomgomo yokonga amandla angathinta ukusebenza ngendlela engalindelekile kakhulu futhi engeyona ejabulisa kakhulu.

Yimiphi imininingwane ye-PostgreSQL kanye nesizindalwazi ngokujwayelekile? Inkinga ukuthi awukwazi ukushintsha noma yiliphi i-nati ngayinye futhi ubone ukuthi ukusebenza kwethu kuthuthuke kakhulu.

Yebo, akhona amagajethi anjalo, kodwa i-database iyinto eyinkimbinkimbi. Isebenzisana nazo zonke izinsiza iseva enazo futhi ikhetha ukusebenzelana ngokugcwele. Uma ubheka izincomo zamanje ze-Oracle mayelana nendlela yokusebenzisa i-OS yokusingatha, kuzofana nehlaya mayelana naleyo cosmonaut yaseMongolia - yondla inja futhi ungathinti lutho. Ake sinikeze database zonke izinsiza, i-database ngokwayo izolungisa yonke into.

Empeleni, ngokwezinga elithile isimo sifana ncamashi ne-PostgreSQL. Umehluko ukuthi i-database ayikakakwazi ukuzithathela zonke izinsiza, okungukuthi endaweni ethile ezingeni le-Linux udinga ukuzixazulula ngokwakho.

Umqondo oyinhloko awukona ukukhetha ithagethi eyodwa bese uqala ukuyilungisa, isibonelo, inkumbulo, i-CPU noma into enjalo, kodwa ukuhlaziya umthwalo womsebenzi bese uzama ukuthuthukisa ukuphuma ngangokunokwenzeka ukuze umthwalo abaklami abahle bawudale. kithi, kuhlanganisa nabasebenzisi bethu.

Nasi isithombe ukuchaza ukuthi kuyini. Kukhona i-Linux OS buffer futhi kunememori eyabiwe futhi kukhona amabhafa okwabelwana ngawo we-PostgreSQL. I-PostgreSQL, ngokungafani ne-Oracle, isebenza ngokuqondile kuphela nge-kernel buffer, okungukuthi, ukuze ikhasi elisuka kudiski lingene kumemori yalo eyabelwe, kufanele lidlule ku-kernel buffer bese libuyela emuva, isimo esifanayo ncamashi.

Amadiski aphila ngaphansi kwalesi simiso. Ngidwebe lokhu njengamadiski. Eqinisweni, kungase kube nesilawuli se-RAID, njll.

Futhi lokhu okufakwayo-okuphumayo ngandlela thize kwenzeka ngalolu daba.

I-PostgreSQL iyisizindalwazi sakudala. Kukhona ikhasi ngaphakathi. Futhi konke okokufaka nokuphumayo kwenzeka kusetshenziswa amakhasi. Sikhulisa amabhlogo kumemori ngamakhasi. Futhi uma kungenzeki lutho, sivele sazifunda, bese kancane kancane ziyanyamalala kule cache, zisuka kumabhafa okwabelwana ngazo bese zigcina sezibuyela kudiski.

Uma sishintsha okuthile ndawana thize, lonke ikhasi liphawulwa njengelingcolile. Ngiwamaka lapha ngombala oluhlaza okwesibhakabhaka. Futhi lokhu kusho ukuthi leli khasi kufanele livunyelaniswe ne-block storage. Okusho ukuthi, lapho sikwenza kwaba ngcolile, senze ukungena ku-WAL. Futhi ngesikhathi esithile esimangalisayo, kwafika into ebizwa ngokuthi indawo yokuhlola. Nemininingwane yabhalwa kulolugodo ukuthi usefikile. Futhi lokhu kusho ukuthi wonke amakhasi angcolile ayelapha ngaleso sikhathi kulezi zigcinalwazi ezabiwe avumelaniswa nediski yokugcina kusetshenziswa i-fsync ngokusebenzisa ibhafa ye-kernel.

Kwenziwani lokhu? Uma silahlekelwe yi-voltage, khona-ke asizange sithole isimo sokuthi yonke idatha ilahlekile. Inkumbulo eqhubekayo, wonke umuntu asitshele ngayo, kuze kube manje ku-database theory - leli yikusasa eliqhakazile, okuyinto thina, yebo, esilwela futhi siyayithanda, kodwa okwamanje bahlala eminyakeni engama-20. Futhi-ke, konke lokhu kudinga ukugadwa.

Futhi umsebenzi wokukhulisa umphumela uwukushuna kahle kuzo zonke lezi zigaba ukuze zonke zihambe ziye emuva naphambili ngokushesha. Inkumbulo eyabiwe ngokuyisisekelo iyinqolobane yekhasi. Ku-PostgreSQL sithumele umbuzo okhethiwe noma okuthile, ibuyise le datha kudiski. Bagcine sebengene kumabhafa okwabelwana ngawo. Ngakho-ke, ukuze lokhu kusebenze kangcono, kufanele kube nenkumbulo eningi.

Ukuze konke lokhu kusebenze kahle futhi ngokushesha, udinga ukulungisa kahle uhlelo lokusebenza kuzo zonke izigaba. Futhi khetha i-hardware elinganiselayo, ngoba uma unokungalingani endaweni ethile, khona-ke ungenza inkumbulo eningi, kodwa ngeke isetshenziswe ngesivinini esanele.

Ake sidlule kulelo nalelo phuzu.

Ukuze wenze lawa makhasi ahambe aye emuva naphambili ngokushesha, udinga ukufeza okulandelayo:

• Okokuqala, udinga ukusebenza ngokuphumelelayo ngenkumbulo.
• Okwesibili, lolu shintsho lapho amakhasi asuka kumemori eya kudiski kufanele asebenze kahle.
• Futhi okwesithathu, kufanele kube namadiski amahle.

Uma une-RAM engu-512 GB kuseva futhi konke kugcina ku-hard drive ye-SATA ngaphandle kwanoma iyiphi inqolobane, khona-ke yonke iseva yedatha egciniwe iphenduka ingabi nje ithanga, kodwa ithanga eline-interface ye-SATA. Uzogijima kuyona ngqo. Futhi akukho okuzokusindisa.

Mayelana nephuzu lokuqala elinenkumbulo, kunezinto ezintathu ezingenza ukuphila kube nzima kakhulu.

Owokuqala wabo yi-NUMA. I-NUMA yinto eyenziwe ukuthuthukisa ukusebenza. Ngokuya ngomthwalo womsebenzi, izinto ezahlukene zingenziwa zibe ngcono. Futhi ngendlela yayo entsha yamanje, ayizilungele kakhulu izinhlelo zokusebenza ezifana nesizindalwazi esisebenzisa kakhulu inqolobane yekhasi okwabelwana ngayo izigcinalwazi.

Ngamafuphi. Ungabona kanjani ukuthi kukhona okungahambi kahle nge-NUMA? Unohlobo oluthile lokungqongqoza okungajabulisi, kungazelelwe enye i-CPU igcwele ngokweqile. Ngasikhathi sinye, uhlaziya imibuzo ku-PostgreSQL futhi ubone ukuthi akukho okufanayo lapho. Le mibuzo akufanele ibe yi-CPU ejulile. Ungakubamba lokhu isikhathi eside. Kulula ukusebenzisa isincomo esifanele kusukela ekuqaleni kokuthi ungayilungisa kanjani i-NUMA ye-PostgreSQL.

Kwenzekani ngempela? I-NUMA imele i-Non-Uniform Memory Access. Liyini iphuzu? Une-CPU, eduze kwayo kunenkumbulo yakhona yendawo. Futhi lokhu kuxhumana kwenkumbulo kungakhuphula inkumbulo kusuka kwamanye ama-CPU.

Uma ugijima numactl --hardware, khona-ke uzothola ishidi elikhulu kangaka. Phakathi kwezinye izinto, kuzoba nenkundla yebanga. Kuzoba nezinombolo - 10-20, into enjalo. Lezi zinombolo aziyona into engaphezu kwenani lama-hops ukuze athathe le memori ekude futhi ayisebenzise endaweni. Empeleni, umqondo omuhle. Lokhu kusheshisa ukusebenza kahle ngaphansi kwenqwaba yemithwalo yemisebenzi.

Manje ake ucabange ukuthi une-CPU eyodwa kuqala uzama ukusebenzisa inkumbulo yendawo, bese uzama ukudonsa enye inkumbulo ngoxhumano lwento ethize. Futhi le-CPU ithola yonke inqolobane yekhasi lakho le-PostgreSQL - yilokho, amanye amagigabhayithi. Uhlala uthola isimo esibi kakhulu, ngoba ku-CPU ngokuvamile kunenkumbulo encane kuleyo module ngokwayo. Futhi yonke inkumbulo esetshenziswayo idlula kulezi zixhumanisi. Kuvele kuhamba kancane futhi kudabuke. Futhi iphrosesa yakho eyenza le nodi ihlale igcwele ngokweqile. Futhi isikhathi sokufinyelela sale nkumbulo sibi, sihamba kancane. Lesi yisimo ongasifuni uma usebenzisa lokhu kusizindalwazi.

Ngakho-ke, inketho elungile kakhulu yesizindalwazi iwuhlelo lokusebenza lwe-Linux ukuthi lungazi ukuthi kwenzekani lapho nhlobo. Ukuze ifinyelele inkumbulo njengoba yenza.

Kungani kunjalo? Kubonakala sengathi kufanele kube ngenye indlela. Lokhu kwenzeka ngesizathu esisodwa esilula: sidinga inkumbulo eningi yenqolobane yekhasi - amashumi, amakhulu amagigabhayithi.

Futhi uma sinikeze konke lokhu futhi sagcina idatha yethu lapho, khona-ke inzuzo ngokusebenzisa inqolobane izoba nkulu kakhulu kunenzuzo yokufinyelela kumemori okukhohlisayo. Futhi ngaleyo ndlela sizozuza ngokungenakuqhathaniswa uma kuqhathaniswa neqiniso lokuthi sizofinyelela inkumbulo ngokuphumelelayo sisebenzisa i-NUMA.

Ngakho-ke, kunezindlela ezimbili lapha okwamanje, kuze kube yilapho ikusasa eliqhakazile selifikile, futhi i-database ngokwayo ayikwazi ukuthola ukuthi yimaphi ama-CPU asebenza kuwo nokuthi idinga ukudonsa okuthile kuwo.

Ngakho-ke, indlela efanele ukukhubaza i-NUMA ngokuphelele, isibonelo, uma iqalisa kabusha. Ezimweni eziningi, ukuwina kungokwemiyalo yobukhulu kangangokuthi umbuzo wokuthi yikuphi okungcono awuveli nhlobo.

Kukhona enye inketho. Siyisebenzisa kaningi kuneyokuqala, ngoba uma iklayenti liza kithi ukuze lithole ukwesekwa, ukuqalisa kabusha iseva kuyinto enkulu kuye. Unebhizinisi lapho. Futhi bahlangabezana nezinkinga ngenxa ye-NUMA. Ngakho-ke, sizama ukuyikhubaza ngezindlela ezimbalwa ezihlaselayo kunokuyiqalisa kabusha, kodwa qaphela ukubheka ukuthi ivaliwe yini. Ngoba, njengoba ulwazi lubonisa, kuhle ukuthi sikhubaze i-NUMA enqubweni yomzali ye-PostgreSQL, kodwa akudingekile nakancane ukuthi izosebenza. Kudingeka sibheke futhi sibone ukuthi ucishiwe ngempela.

Kukhona okuthunyelwe okuhle kukaRobert Haas. Lesi ngesinye sezibophezelo ze-PostgreSQL. Omunye wonjiniyela ababalulekile bawo wonke ama-giblets asezingeni eliphansi. Futhi uma ulandela izixhumanisi zalokhu okuthunyelwe, zichaza izindaba ezimbalwa ezimibalabala mayelana nendlela i-NUMA eyenza ngayo impilo ibe nzima kubantu. Bheka, funda uhlu lokuhlola lomphathi wesistimu lwalokho okudingeka kumiswe kuseva ukuze isizindalwazi sethu sisebenze kahle. Lezi zilungiselelo zidinga ukubhalwa phansi futhi zihlolwe, ngoba ngaphandle kwalokho ngeke kube kuhle kakhulu.

Sicela uqaphele ukuthi lokhu kusebenza kuzo zonke izilungiselelo engizokhuluma ngazo. Kepha imvamisa imininingwane yolwazi iqoqwa kumodi yesigqila sokubekezelela amaphutha. Ungakhohlwa ukwenza amasethingi esigqilini ngoba ngelinye ilanga uyoba nengozi ushintshele esigqilini iyoba inkosi.

Esimeni esiphuthumayo, lapho konke kukubi kakhulu, ifoni yakho ihlale ikhala futhi umphathi wakho eza egijima nenduku enkulu, ngeke ube naso isikhathi sokucabanga ngokuhlola. Futhi imiphumela ingaba yinhlekelele ngempela.

Iphuzu elilandelayo lingamakhasi amakhulu. Amakhasi amakhulu kunzima ukuwahlola ngokwehlukana, futhi akukho phuzu ekwenzeni kanjalo, nakuba kukhona ama-benchmarks angenza lokhu. Kulula ku-Google.

Liyini iphuzu? Uneseva engabizi kakhulu ene-RAM eningi, isibonelo, ngaphezu kuka-30 GB. Awusebenzisi amakhasi amakhulu. Lokhu kusho ukuthi une-overhead ngokwemibandela yokusebenzisa inkumbulo. Futhi lokhu okungaphezulu akukude nokumnandi kakhulu.

Kungani kunjalo? Pho kwenzakalani? Uhlelo lokusebenza lunikeza inkumbulo ngezingcezu ezincane. Kulula kakhulu, yindlela okwenzeka ngayo ngokomlando. Futhi uma singena emininingwaneni, i-OS kufanele ihumushe amakheli abonakalayo abe angokoqobo. Futhi le nqubo ayiyona elula kakhulu, ngakho-ke i-OS igcina umphumela walokhu kusebenza ku- Translation Lookaside Buffer (TLB).

Futhi njengoba i-TLB iyinqolobane, zonke izinkinga ezitholakala kunqolobane ziphakama kulesi simo. Okokuqala, uma une-RAM eningi futhi yonke yabelwe ngezingcezu ezincane, khona-ke le buffer iba nkulu kakhulu. Futhi uma inqolobane inkulu, ukucinga kuyo kuhamba kancane. I-Overhead inempilo futhi yona ngokwayo ithatha isikhala, okungukuthi i-RAM idliwa okuthile okungalungile. Ngalesi sikhathi.

Okubili - lapho i-cache ikhula esimweni esinjalo, maningi amathuba okuthi uzoba nokuphuthelwa kwe-cache. Futhi ukusebenza kahle kwale cache kuncipha ngokushesha njengoba usayizi wayo ukhula. Ngakho-ke, amasistimu wokusebenza aqhamuke nendlela elula. Isetshenziswe ku-Linux isikhathi eside. Ivele kuFreeBSD kungekudala. Kepha sikhuluma ngeLinux. Lawa ngamakhasi amakhulu.

Futhi lapha kufanele kuqashelwe ukuthi amakhasi amakhulu, njengombono, ekuqaleni aphushwa imiphakathi ehlanganisa i-Oracle ne-IBM, okusho ukuthi abakhiqizi besizindalwazi bacabanga ngokuqinile ukuthi lokhu kuzoba usizo nakumininingwane yolwazi.

Futhi lokhu kungenziwa kanjani abangani nge-PostgreSQL? Okokuqala, amakhasi amakhulu kufanele anikwe amandla ku-Linux kernel.

Okwesibili, kufanele zicaciswe ngokucacile ipharamitha ye-sysctl - ukuthi zingaki. Izinombolo lapha zisuka kwenye iseva endala. Ungakwazi ukubala ukuthi mangaki amabhafa okwabelwana ngawo ukuze amakhasi amakhulu angene lapho.

Futhi uma iseva yakho yonke inikezelwe ku-PostgreSQL, khona-ke isiqalo esihle ukwaba noma u-25% we-RAM kumabhafa okwabelwana ngawo, noma u-75% uma uqinisekile ukuthi isizindalwazi sakho sizongena nakanjani kule 75%. Iphuzu lokuqala. Futhi cabanga, uma une-256 GB ye-RAM, ngakho-ke, ngokufanele, uzoba ne-64 GB yamabhafa amakhulu. Bala cishe ngemajini ethile - ukuthi lesi sibalo kufanele sisethelwe kuphi.

Ngaphambi kwenguqulo 9.2 (uma ngenza iphutha, kusukela kunguqulo 8.2), bekungenzeka ukuxhuma i-PostgreSQL namakhasi amakhulu usebenzisa umtapo wolwazi wenkampani yangaphandle. Futhi lokhu kufanele kwenziwe njalo. Okokuqala, udinga i-kernel ukuze ukwazi ukwaba amakhasi amakhulu ngendlela efanele. Futhi, okwesibili, ukuze isicelo esisebenza nabo sikwazi ukuzisebenzisa. Ngeke nje isetshenziswe ngaleyo ndlela. Njengoba i-PostgreSQL yabele inkumbulo ngesitayela sesistimu 5, lokhu kungenziwa kusetshenziswa ama-libhugetlbfs - leli igama eligcwele lomtapo wolwazi.

Ngo-9.3, ukusebenza kwe-PostgreSQL kwathuthukiswa lapho kusebenza ngenkumbulo futhi indlela yokwabiwa kwememori yesistimu 5 iyekwa. Wonke umuntu wayejabule kakhulu, ngoba ngaphandle kwalokho uzama ukusebenzisa izimo ezimbili ze-PostgreSQL emshinini owodwa, futhi uthi anginayo inkumbulo eyanele eyabiwe. Futhi uthi i-sysctl idinga ukulungiswa. Futhi kukhona i-sysctl enjalo ukuthi usadinga ukuqala kabusha, njll. Ngokuvamile, wonke umuntu wayejabule. Kodwa ukwabiwa kwenkumbulo ye-mmap kwephule ukusetshenziswa kwamakhasi amakhulu. Iningi lamakhasimende ethu asebenzisa amabhafa amakhulu okwabelwana ngawo. Futhi sincome kakhulu ukuthi singashintsheli ku-9.3, ngoba phezulu lapho kwaqala ukubalwa ngamaphesenti amahle.

Kodwa umphakathi wayinaka le nkinga kwathi ngo-9.4 bawulungisa kahle lo mcimbi. Futhi ngo-9.4 ipharamitha yavela ku-postgresql.conf lapho ungakwazi ukunika amandla ukuzama, ukuvala noma ukuvala.

Zama inketho ephephe kunazo zonke. Lapho i-PostgreSQL iqala, lapho yabela inkumbulo eyabiwe, izama ukubamba le nkumbulo emakhasini amakhulu. Futhi uma kungasebenzi, bese ibuyela emuva ekukhethweni okujwayelekile. Futhi uma une-FreeBSD noma i-Solaris, ungazama, kuhlale kuphephile.

Uma ivuliwe, ivele ingaqali uma ingakwazi ukukhetha emakhasini amakhulu. Lapha sekuvele kumayelana nokuthi ubani futhi yini enhle kakhudlwana. Kodwa uma uzame, bese uhlola ukuthi unakho ngempela okudingayo okugqanyisiwe, ngoba maningi indawo yephutha. Okwamanje lokhu kusebenza kusebenza kuphela ku-Linux.

Enye inothi elincane ngaphambi kokuthi siqhubekele phambili. Amakhasi amakhulu asobala awekho mayelana ne-PostgreSQL okwamanje. Akakwazi ukuzisebenzisa ngokujwayelekile. Futhi ngamakhasi amakhulu a-Transparent omthwalo onjalo womsebenzi, lapho ucezu olukhulu lwenkumbulo eyabiwe ludingeka, izinzuzo ziza kuphela namavolumu amakhulu kakhulu. Uma unememori yama-terabytes, lokhu kungase kusebenze. Uma sikhuluma ngezinhlelo zokusebenza eziningi zansuku zonke, uma unememori engu-32, 64, 128, 256 GB emshinini wakho, khona-ke amakhasi amakhulu avamile athi Kulungile, futhi sivele sikhubaze i-Transparent.

Futhi into yokugcina mayelana nenkumbulo ayihlobene ngokuqondile ne-fruitut, ingalimaza impilo yakho ngempela. Konke okuphumayo kuzothinteka kakhulu yiqiniso lokuthi iseva ishintshashintsha njalo.

Futhi lokhu kuzoba okungajabulisi kakhulu ngezindlela eziningi. Futhi inkinga enkulu ukuthi izikhwebu zesimanje ziziphatha ngendlela ehlukile kuma-Linux kernels amadala. Futhi le nto ayijabulisi neze ukuqhubekela phambili, ngoba uma sikhuluma ngohlobo oluthile lomsebenzi ngokushintshana, kugcina ngokufika okungakafiki kombulali we-OOM. Futhi umbulali we-OOM, ongafikanga ngesikhathi futhi wehlisa i-PostgreSQL, akajabulisi. Wonke umuntu uzokwazi ngalokhu, okungukuthi, kuze kube umsebenzisi wokugcina.

Kwenzakalani? Unenani elikhulu le-RAM lapho, yonke into isebenza kahle. Kodwa ngesizathu esithile iseva ilenga ekushintshanisweni futhi iyancipha ngenxa yalokhu. Kubukeka sengathi kunenkumbulo eningi, kepha lokhu kuyenzeka.

Ngaphambilini, sicebise ukusetha i-vm.swappiness ibe yiziro, okungukuthi ukukhubaza ukushintshwa. Ngaphambilini, bekubonakala sengathi i-32 GB ye-RAM kanye nama-buffers abiwe ahambisanayo kwakuyinani elikhulu. Inhloso enkulu yokushintshana wukuba nendawo yokuphonsa uqweqwe uma siwa. Futhi yayingasagcwaliseki ngokukhethekile. Futhi-ke uzokwenzani ngalolu qweqwe? Lona umsebenzi lapho kungacaci kahle ukuthi kungani kudingeka ukushintshanisa, ikakhulukazi usayizi onjalo.

Kodwa esikhathini samanje, okungukuthi, izinguqulo zesithathu ze-kernel, ukuziphatha kushintshile. Futhi uma usetha ukushintshela ku-zero, okungukuthi ukuyivala, khona-ke maduze noma kamuva, noma ngabe sekusele i-RAM ethile, umbulali we-OOM uzoza kuwe ukuze abulale abathengi abanamandla kakhulu. Ngoba uzocabangela ukuthi ngomthwalo onjalo womsebenzi sisasele kancane futhi sizophuma, okungukuthi, hhayi ukubethelela inqubo yesistimu, kodwa ukubethela into engabalulekile kangako. Lokhu okungabalulekile kakhulu kuzoba umthengi ojulile wenkumbulo eyabiwe, okungukuthi umphathi weposi. Futhi ngemva kwalokho kuyoba kuhle uma isisekelo kungadingeki sibuyiselwe.

Ngakho-ke, manje okuzenzakalelayo, njengoba ngikhumbula, ukusabalalisa okuningi kukhona endaweni engaba ngu-6, okungukuthi kufanele uqale nini ukusebenzisa ukushintsha ngokuya ngokuthi ingakanani inkumbulo esele. Manje sincoma ukusetha i-vm.swappiness = 1, ngoba lokhu kuvamile ukuyivala, kodwa ayinikezi imiphumela efana ne-OOM-killer efike ngokungalindelekile yabulala yonke into.

Yini elandelayo? Uma sikhuluma ngokusebenza kolwazi futhi kancane kancane siqhubekela kumadiski, wonke umuntu uqala ukubamba amakhanda abo. Ngoba iqiniso lokuthi i-disk ihamba kancane futhi inkumbulo iyashesha ijwayele wonke umuntu kusukela ebuntwaneni. Futhi wonke umuntu uyazi ukuthi i-database izoba nezinkinga zokusebenza kwediski.

Inkinga enkulu yokusebenza kwe-PostgreSQL ehlotshaniswa nama-checkpoints spikes ayenzeki ngoba idiski ayisheshi. Lokhu kungenzeka kakhulu ngenxa yokuthi inkumbulo kanye ne-disk bandwidth ayilingani. Nokho, kungenzeka angalingani ezindaweni ezahlukene. I-PostgreSQL ayimisiwe, i-OS ayimisiwe, i-hardware ayilungiselelwe futhi ihadiwe ayilungile. Futhi le nkinga ayenzeki kuphela uma konke kwenzeka njengoba kufanele, okungukuthi noma akukho mthwalo, noma izilungiselelo ne-hardware kukhethwe kahle.

Kuyini futhi kubukeka kanjani? Ngokuvamile abantu abasebenza nge-PostgreSQL bangene kulolu daba izikhathi ezingaphezu kwesisodwa. Ngizochaza. Njengoba ngishilo, i-PostgreSQL ngezikhathi ezithile yenza izindawo zokuhlola ukuze ilahle amakhasi angcolile kumemori eyabelwe kudiski. Uma sinenani elikhulu lememori eyabiwe, indawo yokuhlola iqala ukuba nomthelela omkhulu kudiski, ngoba ilahla lawa makhasi nge-fsync. Ifika ku-kernel buffer futhi ibhalwe kumadiski kusetshenziswa i-fsync. Futhi uma umthamo waleli bhizinisi mkhulu, khona-ke singakwazi ukubona umphumela ongemuhle, okungukuthi ukusetshenziswa okukhulu kwamadiski.

Lapha nginezithombe ezimbili. Manje ngizochaza ukuthi kuyini. Lawa amagrafu amabili ahlobene nesikhathi. Igrafu yokuqala ukusetshenziswa kwediski. Lapha ifinyelela cishe ku-90% ngalesi sikhathi. Uma unokuhluleka kwedatha ngamadiski angokwenyama, ngokusetshenziswa kwesilawuli se-RAID ku-90%, khona-ke lezi yizindaba ezimbi. Lokhu kusho ukuthi kancane futhi izofinyelela ku-100 futhi i-I/O izoma.

Uma une-disk array, khona-ke indaba ehluke kancane. Kuya ngokuthi icushwa kanjani, iluhlobo luni lohlu, njll.

Futhi ngokuhambisanayo, igrafu evela ekubukweni kwe-postgres yangaphakathi iyalungiswa lapha, etshela ukuthi indawo yokuhlola yenzeka kanjani. Futhi umbala oluhlaza lapha ubonisa ukuthi mangaki amabhafa, lawa makhasi angcolile, ngaleso sikhathi afika kule ndawo yokuhlola ukuze avumelaniswe. Futhi lokhu kuyinto esemqoka odinga ukuyazi lapha. Siyabona ukuthi sinamakhasi amaningi lapha futhi ngesikhathi esithile sishaya ibhodi, okungukuthi, sabhala futhi sabhala, lapha uhlelo lwe-disk ngokucacile lumatasa kakhulu. Futhi indawo yethu yokuhlola inomthelela onamandla kakhulu kudiski. Ngokufanelekile, isimo kufanele sibukeke kakhulu ngale ndlela, okungukuthi sibe nokuqoshwa okuncane lapha. Futhi singayilungisa ngezilungiselelo ukuze izoqhubeka ibe kanje. Okusho ukuthi, ukugaywa kabusha kuncane, kodwa endaweni ethile sibhala okuthile lapha.

Yini okudingeka yenziwe ukuze kunqotshwe le nkinga? Uma uyeke i-IO ngaphansi kwesizindalwazi, lokhu kusho ukuthi bonke abasebenzisi abeze ukuzogcwalisa izicelo zabo bazolinda.

Uma ubheka ngokombono we-Linux, uma uthathe i-hardware enhle, uyilungiselele kahle, ulungiselele i-PostgreSQL ngokujwayelekile ukuze yenze lezi zindawo zokuhlola zingabi kaningi, zisakaze ngokuhamba kwesikhathi phakathi komunye nomunye, bese ungena kumingcele ye-Debian ezenzakalelayo. Ngokusabalalisa okuningi kwe-Linux, lesi isithombe: vm.dirty_ratio=20, vm.dirty_background_ratio=10.

Kusho ukuthini? Idemoni elilodwa elishaywayo livele ku-kernel 2.6. I-Pdglush, kuye ngokuthi ubani osebenzisa lokho, okubandakanya ngemuva kokulahla amakhasi angcolile asuka ku-kernel buffer futhi alahle lapho kudingekile ukulahla amakhasi angcolile kungakhathaliseki ukuthi kwenzekani, lapho ukulahla kwe-backgrouind akusizi.

Ingemuva lifika nini? Lapho u-10% wesamba se-RAM etholakala kuseva kusetshenziswa amakhasi angcolile ku-kernel buffer, umsebenzi okhethekile wokucisha ubizwa ngemuva. Kungani ingemuva? Njengepharamitha, ibheka ukuthi mangaki amakhasi okufanele uwabhale. Futhi, ake sithi, ubhala amakhasi angu-N. Futhi isikhashana le nto ilala. Bese ebuya futhi akopishe amanye amakhasi.

Lena indaba elula kakhulu. Inkinga lapha ifana ne-swimming pool, uma ithela kwelinye ipayipi, igeleza kwelinye. Indawo yethu yokuhlola yafika futhi uma ithumele amakhasi ambalwa angcolile ukuze ilahlwe, kancane kancane yonke into izoxazululwa kahle kusuka ku-kernel buffer pgflush.

Uma la makhasi angcolile eqhubeka nokuqongelela, aqongelela ku-20%, emva kwalokho okubalulekile kwe-OS ukubhala yonke into kudiski, ngoba amandla azohluleka futhi konke kuzoba kubi kithi. Sizolahlekelwa yile datha, isibonelo.

Liyini iqhinga? Iqhinga liwukuthi le mingcele emhlabeni wanamuhla ingama-20 kanye ne-10% yenani le-RAM esemshinini, imbi kakhulu ngokwendlela yokuphuma kwanoma yiluphi uhlelo lwediski onalo.

Cabanga ukuthi uno-128 GB we-RAM. I-12,8 GB ifika ohlelweni lwakho lwediski. Futhi kungakhathaliseki ukuthi iyiphi inqolobane onayo lapho, noma ngabe unaluphi uhlu lapho, ngeke luhlale isikhathi eside kangako.

Ngakho-ke, sincoma ukuthi ulungise ngokushesha lezi zinombolo ngokusekelwe emandleni esilawuli sakho se-RAID. Ngokushesha ngenze isincomo lapha ngesilawuli esinenqolobane engu-512 MB.

Konke kubhekwa njengento elula kakhulu. Ungafaka i-vm.dirty_background ngamabhayithi. Futhi lezi zilungiselelo zikhansela ezimbili ezedlule. Noma isiphi isilinganiso senziwa ngokuzenzakalelayo, noma lawo anamabhayithi ayasebenza, bese lawo anamabhayithi azosebenza. Kodwa njengoba ngingumxhumanisi we-DBA futhi ngisebenza namakhasimende ahlukene, ngizama ukudweba utshani ngakho-ke, uma ngamabhayithi, bese kuba ngamabhayithi. Akekho onikeze isiqinisekiso sokuthi umlawuli omuhle ngeke engeze inkumbulo eyengeziwe kuseva, ayiqalise kabusha, futhi isibalo sizohlala sifana. Vele ubale lezi zinombolo ukuze yonke into ihambisane nesiqinisekiso.

Kwenzekani uma ungangeni? Ngibhale ukuthi noma yikuphi ukushaywa kwamanzi kumiswe ngempumelelo, kodwa empeleni lesi isifenqo. Isistimu yokusebenza inenkinga enkulu - inamakhasi amaningi angcolile, ngakho-ke i-IO ekhiqizwa amaklayenti akho imiswe ngempumelelo, okungukuthi uhlelo lokusebenza luze ukuthumela umbuzo we-sql kusizindalwazi, ilindile. Noma yikuphi okokufaka/okuphumayo kuyo kubaluleke kakhulu, ngoba isizindalwazi sigcwele indawo yokuhlola. Futhi ukuthi uzoqeda nini akucaci kahle. Futhi uma usuzuze ukugungiswa okungeyona ingemuva, kusho ukuthi yonke i-IO yakho igcwele yikho. Futhi kuze kuphele, ngeke wenze lutho.

Kunamaphuzu amabili abalulekile lapha angaphezu komthamo walo mbiko. Lezi zilungiselelo kufanele zifane nezilungiselelo ku-postgresql.conf, okungukuthi izilungiselelo zezindawo zokuhlola. Futhi isistimu yediski yakho kufanele imiswe ngokwanele. Uma unenqolobane ku-RAID, kufanele ibe nebhethri. Abantu bathenga i-RAID ngenqolobane enhle ngaphandle kwebhethri. Uma unama-SSD ku-RAID, kufanele kube yiwo amaseva, kufanele kube nama-capacitor lapho. Nalu uhlu lokuhlola olunemininingwane. Lesi sixhumanisi siqukethe umbiko wami wokuthi ungayilungisa kanjani idiski yokusebenza ku-PostgreSQL. Kukhona zonke lezi zinhla zokuhlola lapho.

Yini enye engenza ukuphila kube nzima kakhulu? Lawa amapharamitha amabili. Zintsha uma kuqhathaniswa. Ngokuzenzakalelayo, zingafakwa ezinhlelweni ezahlukene. Futhi bangenza ukuphila kube nzima uma bengavulwa ngendlela engafanele.

Kunezinto ezimbili ezintsha uma kuqhathaniswa. Sebevele bavele kuma-cores wesithathu. Lokhu kuyi-sched_migration_cost kuma-nanosecond kanye ne-sched_autogroup_enabled, okukodwa ngokuzenzakalelayo.

Futhi ziyonakalisa kanjani impilo yakho? Iyini i-sched_migration_cost? Ku-Linux, isihleli singathutha inqubo isuka kwenye i-CPU iye kwenye. Futhi nge-PostgreSQL, eyenza imibuzo, ukuthuthela kwenye i-CPU akucacile ngokuphelele. Ngokombono wesistimu yokusebenza, lapho ushintsha amafasitela phakathi kwe-openoffice ne-terminal, lokhu kungase kube kuhle, kodwa kusizindalwazi lokhu kubi kakhulu. Ngakho-ke, inqubomgomo ephusile iwukusetha migration_cost kunani elikhulu, okungenani izinkulungwane ezimbalwa zama-nanosecond.

Lokhu kuzosho ukuthini kumhleli? Kuzobhekwa ukuthi ngalesi sikhathi uhlelo lusashisa. Okusho ukuthi, uma unomsebenzi omude owenze okuthile isikhathi eside, umhleli uzokuqonda lokhu. Uzothatha ngokuthi kuze kudlule lesi sikhathi sokuvala, asikho isidingo sokuthuthela le nqubo noma yikuphi. Uma ngesikhathi esifanayo inqubo yenza okuthile, ngakho-ke ngeke ithuthelwe noma kuphi, izosebenza buthule ku-CPU eyabelwe yona. Futhi umphumela muhle kakhulu.

Iphuzu lesibili i-autogroup. Kunombono omuhle wemithwalo yemisebenzi ethile engahlobene nesizindalwazi sesimanje - lokhu okokuhlanganisa izinqubo ngetheminali ebonakalayo lapho yethulwa khona. Lokhu kulungele eminye imisebenzi. Empeleni, i-PostgreSQL iwuhlelo lwezinqubo eziningi olune-prefork egijima isuka kutheminali eyodwa. Unombhali wokukhiya, indawo yokuhlola, futhi zonke izicelo zeklayenti lakho zizofakwa kuhlelo olulodwa, nge-CPU ngayinye. Futhi bayolinda lapho ngazwi linye ukuze akhululeke, ukuze aphazamisane futhi amgcine ematasa isikhathi eside. Lena indaba engadingekile ngokuphelele endabeni yomthwalo onjalo ngakho-ke idinga ukuvalwa.

Uzakwethu u-Alexey Lesovsky wenza izivivinyo nge-pgbench elula, lapho akhulisa khona i-migration_cost ngomyalelo wobukhulu futhi wavala i-autogroup. Umehluko kuhardware embi wawucishe ube ngu-10%. Kukhona ingxoxo ohlwini lwama-postgres lapho abantu benikeza imiphumela yezinguquko ezifanayo esivinini sokubuza kuthonywe u-50%. Ziningi impela izindaba ezinjalo.

Futhi ekugcineni, mayelana nenqubomgomo yokonga amandla. Okuhle ukuthi i-Linux manje isingasetshenziswa kwi-laptop. Futhi kuthiwa izosebenzisa ibhethri kahle. Kodwa kungazelelwe kuvela ukuthi lokhu kungenzeka nakuseva.

Ngaphezu kwalokho, uma uqasha amaseva kumphathi othile, abasingathi “abahle” abanandaba nokuthi usebenza kangcono. Umsebenzi wabo ukuqinisekisa ukuthi i-iron yabo isetshenziswa ngendlela efanele ngangokunokwenzeka. Ngakho-ke, ngokuzenzakalelayo bangavumela imodi yokonga amandla e-laptop ohlelweni lokusebenza.

Uma usebenzisa lezi zinto kuseva enesizindalwazi ngaphansi komthwalo osindayo, ukukhetha kwakho yi-acpi_cpufreq + permormance. Ngisho noma kunesidingo kuzoba nezinkinga.

I-Intel_pstate ingumshayeli ohluke kancane. Futhi manje okuthandwayo kunikezwa kulena, njengoba kwenzeka kamuva futhi isebenza kangcono.

Futhi, ngokufanele, umbusi uwukusebenza kuphela. I-Odemand, ukonga amandla nakho konke okunye akumayelana nawe.

Imiphumela yencazelo yokuhlaziya i-PostgreSQL ingase yehluke ngama-oda amaningana wobukhulu uma unika amandla ukonga amandla, ngoba cishe i-CPU engaphansi kwesizindalwazi sakho izosebenza ngendlela engalindelekile ngokuphelele.

Lezi zinto zingase zifakwe ngokuzenzakalela. Bheka ngokucophelela ukuze ubone ukuthi bayivule ngokuzenzakalela. Lokhu kungaba inkinga enkulu ngempela.

Futhi ekugcineni, bengifuna ukubonga kubafana beqembu lethu le-PosgreSQL-Consulting DBA, okungu-Max Boguk no-Alexey Lesovsky, abahamba phambili kulolu daba nsuku zonke. Futhi sizama ukwenzela amakhasimende ethu konke okusemandleni ethu ukuze konke kusebenzele wona. Kufana nemiyalelo yokuphepha kwendiza. Konke lapha kubhalwe ngegazi. Ngayinye yalezi nati itholakala ohlelweni oluthile lwenkinga. Ngiyajabula ukwabelana nawe ngazo.

Imibuzo:

Ngiyabonga! Uma, ngokwesibonelo, inkampani ifuna ukonga imali futhi ibeke isizindalwazi kanye ne-logic yesicelo kuseva eyodwa, noma uma inkampani ilandela inkambiso yemfashini yezakhiwo ze-microservice, lapho i-PostgreSQL isebenza esitsheni. Liyini iqhinga? I-Sysctl izothinta yonke i-kernel emhlabeni jikelele. Angikaze ngizwe ngama-sysctl enziwa ngandlela thize ukuze asebenze ngokuhlukana esitsheni. Kukhona iqoqo kuphela futhi kukhona ingxenye yokulawula lapho. Ungaphila kanjani nalokhu? Noma uma ufuna ukusebenza, bese usebenzisa i-PostgreSQL kuseva ehlukile yehadiwe bese uyishuna?

Siphendule umbuzo wakho cishe ngezindlela ezintathu. Uma singakhulumi ngeseva yehadiwe engacushwa, njll., bese uphumule, konke kuzosebenza kahle ngaphandle kwalezi zilungiselelo. Uma unomthwalo onjalo ukuthi udinga ukwenza lezi zilungiselelo, uzofika kuseva yensimbi ngaphambi kwalezi zilungiselelo.

Yini inkinga? Uma lokhu kungumshini we-virtual, khona-ke cishe uzoba nezinkinga eziningi, isibonelo, neqiniso lokuthi emishinini eminingi ebonakalayo i-latency yediski ayihambisani neze. Ngisho noma i-disk throughput inhle, khona-ke ukuthengiselana okukodwa kwe-I/O okuhlulekile okungaphazamisi kakhulu ukusebenza okujwayelekile okwenzeka ngesikhathi sokuhlola noma ngesikhathi sokubhala ku-WAL, khona-ke i-database izohlupheka kakhulu kulokhu. Futhi uzoqaphela lokhu ngaphambi kokuba ungene kulezi zinkinga.

Uma une-NGINX kuseva efanayo, uzoba nenkinga efanayo. Uzolwela inkumbulo ehlanganyelwe. Futhi ngeke ufinyelele ezinkingeni ezichazwe lapha.

Kodwa ngakolunye uhlangothi, amanye alawa mapharamitha asazosebenza kuwe. Isibonelo, setha i-dirty_ratio nge-sysctl ukuze ingahlanyi kangako - kunoma yikuphi, lokhu kuzosiza. Ngandlela thize, uzoba nokuxhumana nediski. Futhi kuzoba ngokwephethini engalungile. Lokhu ngokuvamile okuzenzakalelayo kwamapharamitha engiwabonisile. Futhi kunoma yikuphi kungcono ukuwashintsha.

Kodwa kungase kube nezinkinga nge-NUMA. I-VmWare, isibonelo, isebenza kahle ne-NUMA enezilungiselelo eziphambene ncamashi. Futhi lapha kufanele ukhethe - iseva yensimbi noma engeyona eyensimbi.

Nginombuzo ohlobene ne-Amazon AWS. Zinezithombe ezilungiselelwe kusengaphambili. Enye yazo ibizwa nge-Amazon RDS. Ingabe zikhona izilungiselelo ngokwezifiso zesistimu yazo yokusebenza?

Kukhona izilungiselelo lapho, kodwa ziyizilungiselelo ezihlukile. Lapha silungisa uhlelo lokusebenza ngokwendlela i-database ezoyisebenzisa ngayo le nto. Futhi kukhona amapharamitha anquma ukuthi kufanele siye kuphi manje, njengokubumba. Okusho ukuthi sidinga izinsiza eziningi, sizozidla manje. Ngemuva kwalokhu, i-Amazon RDS iqinisa lezi zinsiza, futhi ukusebenza kwehla lapho. Kunezindaba zomuntu ngamunye zokuthi abantu sebeqala ukuxova kanjani lolu daba. Ngezinye izikhathi ngisho ngempumelelo impela. Kodwa lokhu akuhlangene nezilungiselelo ze-OS. Kufana nokugenca ifu. Leyo yindaba ehlukile.

Kungani amakhasi amakhulu e-Transparent engenawo umthelela uma kuqhathaniswa ne-Huge TLB?

Unganikeli. Lokhu kungachazwa ngezindlela eziningi. Kodwa empeleni abakunikezi. Uyini umlando we-PostgreSQL? Ekuqaleni, yabela ingxenye enkulu yenkumbulo eyabiwe. Ukuthi asobala noma cha akunamsebenzi nhlobo. Iqiniso lokuthi bagqama ekuqaleni lichaza yonke into. Futhi uma kunenkumbulo eningi futhi udinga ukwakha kabusha ingxenye ye-shared_memory, khona-ke amakhasi amakhulu e-Transparent azohambisana. Ku-PostgreSQL, imane yabelwa ku-chunk enkulu ekuqaleni futhi yilokho, bese akukho okukhethekile okwenzeka lapho. Ungayisebenzisa, kodwa kunethuba lokuthola inkohlakalo ye-share_memory lapho iphinde yabela okuthile. I-PostgreSQL ayazi ngalokhu.

Source: www.habr.com