Linux ikukonzekera kukonza magwiridwe antchito a PostgreSQL. Ilya Kosmodemyansky

Zolemba za lipoti la 2015 lolembedwa ndi Ilya Kosmodemyansky "Linux kukonza magwiridwe antchito a PostgreSQL"

Chodzikanira: Ndikuwona kuti lipotili ndi la Novembala 2015 - padutsa zaka 4 ndipo nthawi yayitali yadutsa. Mtundu wa 9.4 womwe wafotokozedwa mu lipotilo sunathandizidwenso. Pazaka 4 zapitazi, zatsopano 5 za PostgreSQL zatulutsidwa, ndipo mitundu 15 ya Linux kernel yatulutsidwa. Ngati mulembanso ndimezi, mudzakhala ndi lipoti losiyana. Koma apa tikuwona kusintha kofunikira kwa Linux kwa PostgreSQL, komwe kuli kofunikira lero.

Dzina langa ndine Ilya Kosmodemyansky. Ndimagwira ntchito ku PostgreSQL-Consulting. Ndipo tsopano ndilankhula pang'ono zomwe ndiyenera kuchita ndi Linux pokhudzana ndi nkhokwe zambiri komanso PostgreSQL makamaka, chifukwa mfundo zake ndizofanana.

Tikambirana chiyani? Ngati mumalankhulana ndi PostgreSQL, ndiye kuti pamlingo wina muyenera kukhala woyang'anira UNIX. Zikutanthauza chiyani? Tikayerekeza Oracle ndi PostgreSQL, ndiye kuti ku Oracle muyenera kukhala 80% DBA database admin ndi 20% Linux admin.

Ndi PostgreSQL ndizovuta kwambiri. Ndi PostgreSQL muyenera kumvetsetsa bwino momwe Linux imagwirira ntchito. Ndipo nthawi yomweyo, thamangani pang'ono pambuyo pa locomotive, chifukwa posachedwapa zonse zasinthidwa bwino. Ndipo maso atsopano amamasulidwa, ndipo magwiridwe antchito amawonekera, magwiridwe antchito amayenda bwino, ndi zina zambiri.

Chifukwa chiyani tikulankhula za Linux? Osati chifukwa tili pa msonkhano wa Linux Peter, koma chifukwa m'mikhalidwe yamakono imodzi mwa machitidwe ovomerezeka kwambiri ogwiritsira ntchito ma database ambiri komanso PostgreSQL makamaka ndi Linux. Chifukwa FreeBSD, mwatsoka, ikukula mwanjira ina yachilendo kwambiri. Ndipo padzakhala mavuto onse ndi magwiridwe antchito ndi zinthu zina zambiri. Kuchita kwa PostgreSQL pa Windows nthawi zambiri kumakhala vuto lalikulu, kutengera mfundo yakuti Windows ilibe kukumbukira komweko komweko monga UNIX, pomwe PostgreSQL yonse imamangiriridwa ku izi, chifukwa ndi machitidwe ambiri.

Ndipo ndikuganiza kuti aliyense alibe chidwi ndi zachilendo ngati Solaris, tiyeni tipite.

Kugawa kwamakono kwa Linux kumakhala ndi zosankha zopitilira 1 za syctl, kutengera momwe mumapangira kernel. Panthawi imodzimodziyo, ngati tiyang'ana mtedza wosiyana, tikhoza kusintha chinachake m'njira zambiri. Pali magawo amafayilo amomwe mungawayikire. Ngati muli ndi mafunso okhudza momwe mungayambitsire: zomwe mungatsegule mu BIOS, momwe mungasinthire zida, ndi zina.

Ili ndi voliyumu yayikulu kwambiri yomwe ingakambidwe kwa masiku angapo, osati mu lipoti limodzi lalifupi, koma tsopano ndiyang'ana pa zinthu zofunika, momwe mungapewere ma rakes omwe amatsimikiziridwa kuti akulepheretseni kugwiritsa ntchito database yanu bwino pa Linux ngati musawakonze . Ndipo panthawi imodzimodziyo, mfundo yofunikira ndi yakuti magawo ambiri osasinthika sakuphatikizidwa m'makonzedwe omwe ali olondola pa database. Ndiko kuti, mwachisawawa zidzagwira ntchito bwino kapena ayi.

Kodi ndi zolinga zotani zokhazikika zomwe zilipo mu Linux? Ndikuganiza kuti popeza nonse mukuchita ndi Linux management, palibe chifukwa chofotokozera zomwe mukufuna.

Mutha kuyimba:

• CPUs.
• Kukumbukira.
• Kusunga.
• Zina. Tikambirana za izi kumapeto kwa zokhwasula-khwasula. Ngakhale, mwachitsanzo, magawo monga ndondomeko zopulumutsa mphamvu zingakhudze ntchito mosadziwika bwino komanso osati njira yosangalatsa kwambiri.

Kodi zenizeni za PostgreSQL ndi database yonse? Vuto ndiloti simungasinthe mtedza uliwonse ndikuwona kuti ntchito yathu yapita patsogolo kwambiri.

Inde, pali zida zotere, koma database ndizovuta. Imalumikizana ndi zinthu zonse zomwe seva ili nazo ndipo imakonda kuyanjana mokwanira. Ngati muyang'ana malingaliro a Oracle amakono amomwe mungagwiritsire ntchito makina a OS, zidzakhala ngati nthabwala za cosmonaut ya ku Mongolia - dyetsa galu osati kukhudza kalikonse. Tiyeni tipatse nkhokwe zonse zothandizira, database yokhayo imakonza zonse.

M'malo mwake, pamlingo wina zinthu ndizofanana ndi PostgreSQL. Kusiyana kwake ndikuti nkhokwe sinathebe kudzitengera zokha zonse, mwachitsanzo, penapake pamlingo wa Linux muyenera kuzikonza nokha.

Lingaliro lalikulu silo kusankha chandamale chimodzi ndikuyamba kuyikonza, mwachitsanzo, kukumbukira, CPU kapena zina zotere, koma kusanthula kuchuluka kwa ntchito ndikuyesera kukonza zomwe zikuyenda momwe mungathere kuti katundu omwe olemba mapulogalamu abwino adazipanga. kwa ife, kuphatikiza ogwiritsa ntchito.

Pano pali chithunzi kufotokoza chomwe chiri. Pali buffer ya Linux OS ndipo pali kukumbukira kogawana ndipo pali PostgreSQL yogawana mabafa. PostgreSQL, mosiyana ndi Oracle, imagwira ntchito mwachindunji kudzera mu buffer ya kernel, mwachitsanzo, kuti tsamba lochokera pa diski lilowe m'makumbukidwe ake omwe amagawana nawo, liyenera kudutsa mu buffer kernel ndi kumbuyo, momwemonso.

Ma disks amakhala pansi pa dongosololi. Ndinajambula izi ngati ma disks. M'malo mwake, pakhoza kukhala wowongolera RAID, ndi zina.

Ndipo zotulutsa izi m'njira zosiyanasiyana zimachitika kudzera munkhaniyi.

PostgreSQL ndi database yachikale. Pali tsamba mkati. Ndipo zolowetsa zonse ndi zotuluka zimachitika pogwiritsa ntchito masamba. Tikukweza midadada mu kukumbukira masamba. Ndipo ngati palibe chomwe chinachitika, tidangowawerenga, ndiye kuti pang'onopang'ono amazimiririka kuchokera ku cache iyi, kuchokera pazosungira zomwe adagawana ndikubwereranso pa disk.

Ngati tisintha china chake penapake, ndiye kuti tsamba lonselo limalembedwa kuti ladetsedwa. Ndinazilemba apa ndi buluu. Ndipo izi zikutanthauza kuti tsamba ili liyenera kulumikizidwa ndi block block. Ndiye kuti, titazidetsa, tidalowa mu WAL. Ndipo panthawi yosangalatsa kwambiri, chodabwitsa chotchedwa checkpoint chinabwera. Ndipo zambiri zidalembedwa mu chipikachi kuti wafika. Ndipo izi zikutanthauza kuti masamba onse akuda omwe anali pano panthawiyo muzosungira zomwe adagawana adalumikizidwa ndi disk yosungirako pogwiritsa ntchito fsync kudzera mu buffer ya kernel.

N’chifukwa chiyani izi zikuchitika? Ngati tataya magetsi, ndiye kuti sitinapeze kuti deta yonse idatayika. Kukumbukira kosalekeza, komwe aliyense anatiuza, kuli pakali pano mu chiphunzitso cha database - ili ndi tsogolo lowala, lomwe, ndithudi, timayesetsa ndipo timalikonda, koma tsopano akukhala zaka zosachepera 20. Ndipo, ndithudi, zonsezi ziyenera kuyang'aniridwa.

Ndipo ntchito yopititsa patsogolo ntchito ndikuwongolera bwino magawo onsewa kuti zonse ziziyenda mmbuyo ndi mtsogolo mwachangu. Memory yogawana kwenikweni ndi cache yamasamba. Mu PostgreSQL tidatumiza funso kapena china chake, idatenganso izi kuchokera pa disk. Iwo anamaliza mu buffers anagawana. Chifukwa chake, kuti izi zigwire bwino ntchito, payenera kukhala zokumbukira zambiri.

Kuti zonsezi zigwire ntchito bwino komanso mwachangu, muyenera kukonza makina ogwiritsira ntchito moyenera pamagawo onse. Ndipo sankhani ma hardware oyenerera, chifukwa ngati muli ndi kusalinganika pamalo ena, ndiye kuti mukhoza kukumbukira zambiri, koma sichidzatumizidwa mofulumira.

Ndipo tiyeni tidutse mfundo zonsezi.

Kuti masambawa aziyenda mmbuyo ndi mtsogolo mwachangu, muyenera kukwaniritsa izi:

• Choyamba, muyenera kugwira ntchito bwino ndi kukumbukira.
• Kachiwiri, kusinthaku pamene masamba okumbukira kupita ku diski ayenera kukhala kothandiza kwambiri.
• Ndipo chachitatu, payenera kukhala ma disks abwino.

Ngati muli ndi 512 GB ya RAM mu seva ndipo zonse zimathera pa SATA hard drive popanda cache, ndiye kuti seva yonse ya database imasanduka osati dzungu, koma dzungu ndi mawonekedwe a SATA. Mudzathamangiramo mwachindunji. Ndipo palibe chimene chingakupulumutseni.

Ponena za mfundo yoyamba ndi kukumbukira, pali zinthu zitatu zomwe zingapangitse moyo kukhala wovuta kwambiri.

Woyamba mwa iwo ndi NUMA. NUMA ndichinthu chomwe chimapangidwa kuti chiwongolere magwiridwe antchito. Kutengera kuchuluka kwa ntchito, zinthu zosiyanasiyana zimatha kukonzedwa. Ndipo m'mawonekedwe ake atsopano, sizabwino kwambiri pamapulogalamu monga ma database omwe amagwiritsa ntchito kwambiri posungira masamba omwe amagawana nawo.

Mwachidule. Kodi mungadziwe bwanji ngati pali vuto ndi NUMA? Muli ndi kugogoda kosasangalatsa, mwadzidzidzi CPU ina yadzaza. Nthawi yomweyo, mumasanthula mafunso mu PostgreSQL ndikuwona kuti palibe chofanana pamenepo. Mafunso awa sayenera kukhala a CPU kwambiri. Mutha kugwira izi kwa nthawi yayitali. Ndizosavuta kugwiritsa ntchito malingaliro olondola kuyambira pachiyambi momwe mungasinthire NUMA ya PostgreSQL.

Kodi kwenikweni chikuchitika ndi chiyani? NUMA imayimira Non-Uniform Memory Access. Mfundo yake ndi yotani? Muli ndi CPU, pafupi ndi izo pali kukumbukira kwanuko. Ndipo kulumikizana kwa kukumbukira uku kumatha kukweza kukumbukira kuchokera ku ma CPU ena.

Ngati muthamanga numactl --hardware, ndiye mudzapeza pepala lalikulu chotero. Mwa zina, padzakhala malo otalikirapo. Padzakhala manambala - 10-20, chinachake chonga icho. Ziwerengerozi sizoposa kuchuluka kwa ma hops kuti atenge kukumbukira kwakutali ndikuzigwiritsa ntchito kwanuko. Kwenikweni, lingaliro labwino. Izi zimafulumizitsa ntchito bwino pansi pa ntchito zosiyanasiyana.

Tsopano tangoganizani kuti muli ndi CPU imodzi poyesa kugwiritsa ntchito kukumbukira kwanuko, kenako ndikuyesa kukweza kukumbukira kwina kudzera pakulumikizana ndi china chake. Ndipo CPU iyi imapeza cache yanu yonse ya tsamba la PostgreSQL - ndizomwe, magigabytes ena. Nthawi zonse mumakhala ndi vuto lalikulu, chifukwa pa CPU nthawi zambiri mumakhala ndi kukumbukira pang'ono mu gawo lomwelo. Ndipo zokumbukira zonse zomwe zimatumizidwa zimadutsa pazolumikizana izi. Zimakhala zodekha komanso zachisoni. Ndipo purosesa yanu yomwe imathandizira node iyi imakhala yodzaza nthawi zonse. Ndipo nthawi yofikira ya kukumbukira uku ndi yoyipa, yochedwa. Izi ndizomwe simukufuna ngati mukugwiritsa ntchito database.

Chifukwa chake, njira yolondola kwambiri yosungiramo zosungirako ndi ya makina ogwiritsira ntchito a Linux kuti asadziwe zomwe zikuchitika kumeneko nkomwe. Kotero kuti imafikira kukumbukira momwe imachitira.

Ndichoncho chifukwa chiyani? Zikuwoneka kuti ziyenera kukhala mwanjira ina mozungulira. Izi zimachitika chifukwa chimodzi chophweka: timafunikira kukumbukira zambiri pa cache ya tsamba - makumi, mazana a gigabytes.

Ndipo ngati titagawa zonsezi ndikusunga deta yathu pamenepo, ndiye kuti phindu logwiritsa ntchito posungira lidzakhala lalikulu kwambiri kuposa phindu lofikira kukumbukira movutikira. Ndipo tidzapindula kwambiri poyerekeza ndi mfundo yakuti tidzatha kukumbukira bwino pogwiritsa ntchito NUMA.

Choncho, pali njira ziwiri pano pakalipano, mpaka tsogolo labwino lafika, ndipo deta yokhayokhayo silingathe kudziwa kuti ndi ma CPU ati omwe akuyendetsa komanso kumene akuyenera kukoka chinachake.

Chifukwa chake, njira yolondola ndikuyimitsa NUMA palimodzi, mwachitsanzo, poyambitsanso. Nthawi zambiri, zopambana zimakhala zazikulu kwambiri kotero kuti funso lomwe lili bwino silimatuluka konse.

Palinso njira ina. Timagwiritsa ntchito nthawi zambiri kuposa yoyamba, chifukwa kasitomala akabwera kwa ife kuti atithandize, kuyambiranso seva ndi chinthu chachikulu kwa iye. Ali ndi bizinesi kumeneko. Ndipo amakumana ndi mavuto chifukwa cha NUMA. Chifukwa chake, timayesa kuyimitsa m'njira zocheperako kuposa kuyambiranso, koma samalani kuti muwone ngati yayimitsidwa. Chifukwa, monga momwe zinachitikira, ndikwabwino kuti tiletse NUMA panjira ya PostgreSQL ya makolo, koma sizofunikira kuti izigwira ntchito. Tiyenera kuyang'ana ndikuwona kuti adazimitsadi.

Pali positi yabwino ndi Robert Haas. Ichi ndi chimodzi mwazochita za PostgreSQL. Mmodzi mwa otsogolera ofunikira a giblets onse otsika. Ndipo ngati mutsatira maulalo a positiyi, akufotokoza nkhani zingapo zokongola za momwe NUMA idavutitsira moyo kwa anthu. Yang'anani, phunzirani mndandanda wa oyang'anira dongosolo pazomwe zikuyenera kukhazikitsidwa pa seva kuti database yathu igwire ntchito bwino. Zokonda izi ziyenera kulembedwa ndikufufuzidwa, chifukwa apo ayi sizikhala zabwino kwambiri.

Chonde dziwani kuti izi zimagwira ntchito pazokonda zonse zomwe ndilankhula. Koma nthawi zambiri nkhokwe zimasonkhanitsidwa munjira ya master-kapolo kuti athe kulekerera zolakwika. Osaiwala kupanga zoikamo izi pa kapoloyo chifukwa tsiku lina udzachita ngozi ndipo udzasinthana ndi kapoloyo ndipo adzakhala bwana.

Muzochitika zadzidzidzi, pamene chirichonse chiri choyipa kwambiri, foni yanu ikulira nthawi zonse ndipo bwana wanu amabwera akuthamanga ndi ndodo yaikulu, simudzakhala ndi nthawi yoganizira za kufufuza. Ndipo zotsatira zake zingakhale zoopsa kwambiri.

Mfundo yotsatira ndi masamba akuluakulu. Masamba akuluakulu ndi ovuta kuyesa padera, ndipo palibe chifukwa chochitira izi, ngakhale pali zizindikiro zomwe zingakhoze kuchita izi. Ndizosavuta kwa Google.

Mfundo yake ndi yotani? Muli ndi seva yotsika mtengo kwambiri yokhala ndi RAM yambiri, mwachitsanzo, kuposa 30 GB. Simugwiritsa ntchito masamba akulu. Izi zikutanthauza kuti muli ndi chiwongolero pakugwiritsa ntchito kukumbukira. Ndipo pamwamba pamutu uwu ndi kutali ndi zosangalatsa kwambiri.

Ndichoncho chifukwa chiyani? Ndiye chikuchitika ndi chiyani? The opaleshoni dongosolo allocates kukumbukira mu tiziduswa tating'ono. Ndizothandiza kwambiri, ndi momwe zidachitikira m'mbiri. Ndipo ngati tipita mwatsatanetsatane, OS iyenera kumasulira maadiresi enieni kukhala akuthupi. Ndipo njirayi si yosavuta, kotero OS imasunga zotsatira za ntchitoyi mu Translation Lookaside Buffer (TLB).

Ndipo popeza TLB ndi cache, mavuto onse omwe ali mu cache amayamba pamenepa. Choyamba, ngati muli ndi RAM yambiri ndipo zonse zimagawidwa muzigawo zing'onozing'ono, ndiye kuti buffer iyi imakhala yaikulu kwambiri. Ndipo ngati cache ndi yayikulu, ndiye kuti kusaka ndikuchedwa. Pamwambapa ndi wathanzi ndipo imatenga malo, mwachitsanzo, RAM ikudyedwa ndi china chake cholakwika. Nthawiyi.

Awiri - pamene cache ikukula kwambiri muzochitika zotere, ndizowonjezereka kuti mudzakhala ndi zophonya za cache. Ndipo mphamvu ya cache iyi imachepa mofulumira pamene kukula kwake kumawonjezeka. Choncho, machitidwe opangira opaleshoni adadza ndi njira yosavuta. Zakhala zikugwiritsidwa ntchito ku Linux kwa nthawi yayitali. Idawonekera mu FreeBSD osati kale kwambiri. Koma tikulankhula za Linux. Awa ndi masamba akulu.

Ndipo apa ziyenera kudziwidwa kuti masamba akulu, monga lingaliro, adakankhidwa poyambilira ndi madera omwe amaphatikizapo Oracle ndi IBM, mwachitsanzo, opanga ma database adaganiza kuti izi zitha kukhala zothandiza pazosungira.

Ndipo izi zitha bwanji kukhala mabwenzi ndi PostgreSQL? Choyamba, masamba akulu ayenera kuyatsidwa mu Linux kernel.

Kachiwiri, ziyenera kufotokozedwa momveka bwino ndi sysctl parameter - ndi angati. Manambala apa akuchokera ku seva ina yakale. Mutha kuwerengera kuchuluka kwa ma buffer omwe muli nawo kuti masamba akulu azitha kukwana pamenepo.

Ndipo ngati seva yanu yonse yaperekedwa ku PostgreSQL, ndiye poyambira bwino ndikugawa 25% ya RAM kumagulu ogawana nawo, kapena 75% ngati mukutsimikiza kuti database yanu idzakwanira mu 75%. Poyambira wani. Ndipo taganizirani, ngati muli ndi 256 GB ya RAM, ndiye kuti, mudzakhala ndi 64 GB ya ma buffers akuluakulu. Werengetsani pafupifupi ndi malire - zomwe chiwerengerochi chiyenera kukhazikitsidwa.

Pambuyo pa mtundu wa 9.2 (ngati sindikulakwitsa, kuyambira mtundu wa 8.2), zinali zotheka kulumikiza PostgreSQL ndi masamba akulu pogwiritsa ntchito laibulale ya chipani chachitatu. Ndipo izi ziyenera kuchitika nthawi zonse. Choyamba, muyenera kernel kuti muthe kugawa masamba akulu molondola. Ndipo, chachiwiri, kuti pulogalamu yomwe imagwira nawo ntchito iwagwiritse ntchito. Sizidzangogwiritsidwa ntchito mwanjira imeneyo. Popeza PostgreSQL idapereka kukumbukira mu kalembedwe ka 5, izi zitha kuchitika pogwiritsa ntchito libhugetlbfs - ili ndi dzina lonse la library.

Mu 9.3, magwiridwe antchito a PostgreSQL adasinthidwa pogwira ntchito ndi kukumbukira ndipo njira yogawa kukumbukira 5 idasiyidwa. Aliyense anali wokondwa kwambiri, chifukwa mwinamwake mumayesa kuyendetsa zochitika ziwiri za PostgreSQL pamakina amodzi, ndipo akunena kuti ndilibe kukumbukira kokwanira. Ndipo akunena kuti sysctl iyenera kukonzedwa. Ndipo pali sysctl kotero kuti mukufunikirabe kuyambiranso, etc. Kawirikawiri, aliyense anali wokondwa. Koma kugawa kwa kukumbukira kwa mmap kudasokoneza kugwiritsa ntchito masamba akulu. Makasitomala athu ambiri amagwiritsa ntchito mabafa akulu omwe amagawana nawo. Ndipo tidalimbikitsa kwambiri kuti tisasinthe ku 9.3, chifukwa chapamwamba pamenepo chinayamba kuwerengedwa bwino.

Koma anthu ammudzi adasamala za vutoli ndipo mu 9.4 adakonzanso chochitikachi bwino kwambiri. Ndipo mu 9.4 chizindikiro chinawonekera mu postgresql.conf momwe mungathetsere kuyesa, kuyatsa kapena kuzimitsa.

Yesani ndiye njira yotetezeka kwambiri. PostgreSQL ikayamba, ikagawa zokumbukira zogawana, imayesa kukumbukira izi pamasamba akulu. Ndipo ngati sichigwira ntchito, ndiye kuti imabwereranso kumasankho abwinobwino. Ndipo ngati muli ndi FreeBSD kapena Solaris, ndiye kuti mutha kuyesa, zimakhala zotetezeka nthawi zonse.

Ngati zichitika, ndiye kuti sizingoyamba ngati sizikanatha kusankha pamasamba akulu. Apa zili kale za ndani ndi zomwe zili zabwino. Koma ngati mwayesa, fufuzani kuti muli ndi zomwe mukufuna kuwunikira, chifukwa pali malo ambiri olakwika. Pakadali pano izi zimangogwira ntchito pa Linux.

Cholemba china chaching'ono tisanapite patsogolo. Masamba akulu owoneka bwino sakukhudzana ndi PostgreSQL pano. Sangagwiritse ntchito moyenera. Ndipo ndi masamba akuluakulu a Transparent a ntchito yotereyi, pakafunika kukumbukira kukumbukira kwakukulu, zopindulitsa zimangobwera ndi mavoliyumu akulu kwambiri. Ngati muli ndi ma terabytes a kukumbukira ndiye kuti izi zitha kuchitika. Ngati tikukamba za ntchito zambiri zatsiku ndi tsiku, mukakhala ndi 32, 64, 128, 256 GB ya kukumbukira pamakina anu, ndiye kuti masamba akulu omwe amakhalapo ndi Ok, ndipo timangoyimitsa Transparent.

Ndipo chinthu chomaliza chokhudza kukumbukira sichikhudzana mwachindunji ndi fruitut, zikhoza kuwononga moyo wanu. Kupititsa patsogolo konse kudzakhudzidwa kwambiri ndi mfundo yakuti seva imasinthasintha nthawi zonse.

Ndipo izi zidzakhala zosasangalatsa kwambiri m'njira zingapo. Ndipo vuto lalikulu ndikuti maso amakono amachita mosiyana ndi ma Linux akale. Ndipo chinthu ichi ndi chosasangalatsa kupondapo, chifukwa tikamalankhula za mtundu wina wa ntchito ndikusinthana, zimatha ndi kubwera kwanthawi yayitali kwa wakupha OOM. Ndipo wakupha wa OOM, yemwe sanafike munthawi yake ndikugwetsa PostgreSQL, sizosangalatsa. Aliyense adziwa za izi, ndiye kuti, mpaka wogwiritsa ntchito womaliza.

Chikuchitikandi chiyani? Muli ndi kuchuluka kwa RAM pamenepo, zonse zimayenda bwino. Koma pazifukwa zina seva imapachikidwa posinthana ndikuchepetsa chifukwa cha izi. Zingawoneke kuti pali kukumbukira zambiri, koma izi zimachitika.

M'mbuyomu, tidalangiza kukhazikitsa vm.swappiness kukhala ziro, mwachitsanzo, kuyimitsa kusinthana. M'mbuyomu, zinkawoneka kuti 32 GB ya RAM ndi ma buffers ofanana nawo anali ochuluka kwambiri. Cholinga chachikulu cha kusinthanitsa ndi kukhala ndi malo oponyera kutumphuka ngati tagwa. Ndipo sunakwaniritsidwenso makamaka. Ndiyeno mutani ndi kutumphuka kumeneku? Iyi ndi ntchito yomwe sizidziwika bwino chifukwa chake kusinthanitsa kumafunika, makamaka kukula kwake.

Koma masiku ano, mwachitsanzo, mitundu yachitatu ya kernel, khalidwe lasintha. Ndipo ngati muyika kusinthana kukhala zero, mwachitsanzo, kuzimitsa, posakhalitsa, ngakhale RAM itatsala, wakupha OOM abwera kwa inu kuti aphe ogula kwambiri. Chifukwa adzalingalira kuti ndi ntchito yotereyi timakhalabe ndi pang'ono ndipo tidzadumpha, mwachitsanzo, kuti tisakhomerere ndondomeko ya dongosolo, koma kukhomerera chinthu chochepa kwambiri. Chofunikira kwambiri ichi chidzakhala chogwiritsa ntchito kwambiri kukumbukira komwe kugawana nawo, ndiye woyang'anira positi. Ndipo pambuyo pake zidzakhala zabwino ngati maziko sakuyenera kubwezeretsedwa.

Chifukwa chake, tsopano zokhazikika, monga ndikukumbukira, magawo ambiri ali kwinakwake mozungulira 6, mwachitsanzo, pa nthawi iti yomwe muyenera kuyamba kugwiritsa ntchito kusinthanitsa malinga ndi kuchuluka kwa kukumbukira komwe kwatsala. Tsopano timalimbikitsa kukhazikitsa vm.swappiness = 1, chifukwa izi zimazimitsa, koma sizipereka zotsatira zofanana ndi za OOM-wakupha zomwe zinafika mosayembekezereka ndikupha chinthu chonsecho.

Chotsatira ndi chiyani? Tikamalankhula za magwiridwe antchito a database ndikusunthira pang'onopang'ono kupita ku ma disks, aliyense amayamba kugwira mitu yawo. Chifukwa chowonadi chakuti diski imachedwa ndipo kukumbukira kumathamanga kumadziwika kwa aliyense kuyambira ali mwana. Ndipo aliyense akudziwa kuti nkhokweyo idzakhala ndi vuto la disk performance.

Vuto lalikulu la magwiridwe antchito a PostgreSQL lolumikizidwa ndi ma spikes a checkpoints silichitika chifukwa disk imachedwa. Izi zimachitika makamaka chifukwa chakuti kukumbukira ndi disk bandwidth sikuli bwino. Komabe, iwo sangakhale olinganizika m’malo osiyanasiyana. PostgreSQL sinakhazikitsidwe, OS sinakonzedwe, zida sizimakonzedwa ndipo zida ndizolakwika. Ndipo vutoli silimachitika pokhapokha ngati zonse zikuchitika momwe ziyenera kukhalira, mwachitsanzo, palibe katundu, kapena zoikidwiratu ndi hardware zasankhidwa bwino.

Ndi chiyani ndipo chikuwoneka bwanji? Nthawi zambiri anthu omwe amagwira ntchito ndi PostgreSQL alowa munkhaniyi kangapo. Ndifotokoza. Monga ndanenera, PostgreSQL nthawi ndi nthawi imapanga ma checkpoints kuti atayire masamba akuda mumakumbukiro omwe amagawana nawo ku disk. Ngati tili ndi zokumbukira zambiri zomwe timagawana, ndiye kuti cheke chimayamba kukhudza kwambiri diski, chifukwa imataya masambawa ndi fsync. Ifika mu buffer ya kernel ndipo imalembedwa ku disks pogwiritsa ntchito fsync. Ndipo ngati kuchuluka kwa bizinesi iyi ndi yayikulu, ndiye kuti titha kuwona zotsatira zosasangalatsa, zomwe ndikugwiritsa ntchito kwambiri ma disks.

Pano ndili ndi zithunzi ziwiri. Tsopano ndifotokoza chomwe chiri. Awa ndi ma graph awiri okhudzana ndi nthawi. Gawo loyamba ndikugwiritsira ntchito disk. Apa ikufika pafupifupi 90% panthawiyi. Ngati muli ndi kulephera kwa database ndi ma disks akuthupi, ndikugwiritsa ntchito RAID controller pa 90%, ndiye kuti iyi ndi nkhani yoyipa. Izi zikutanthauza kuti pang'ono ndipo idzafika 100 ndipo I / O idzasiya.

Ngati muli ndi disk, ndiye kuti ndi nkhani yosiyana pang'ono. Zimatengera momwe zimapangidwira, mtundu wamtundu wanji, ndi zina.

Ndipo mofananira, chithunzi chochokera pamawonekedwe amkati a postgres chimakonzedwa apa, chomwe chimafotokoza momwe cheke chimachitika. Ndipo mtundu wobiriwira apa ukuwonetsa ma buffer angati, masamba akuda awa, panthawiyo adafika pamalo ochezera awa kuti alumikizike. Ndipo ichi ndi chinthu chachikulu chomwe muyenera kudziwa apa. Tikuwona kuti tili ndi masamba ambiri pano ndipo nthawi ina timagunda bolodi, ndiko kuti, tidalemba ndikulemba, apa disk system ikuwoneka yotanganidwa kwambiri. Ndipo cheke chathu chimakhudza kwambiri diski. Moyenera, zinthu ziyenera kuwoneka motere, mwachitsanzo, tinali ndi zojambulira zochepa pano. Ndipo tikhoza kukonza ndi zoikamo kuti zipitirize kukhala chonchi. Ndiko kuti, zobwezeretsanso ndizochepa, koma penapake tikulemba zina pano.

Kodi chofunika n’chiyani kuti tithetse vutoli? Ngati mwayimitsa IO pansi pa database, izi zikutanthauza kuti ogwiritsa ntchito onse omwe adabwera kudzakwaniritsa zopempha zawo adzadikirira.

Ngati muyang'ana kuchokera ku Linux, ngati munatenga zida zabwino, kuzikonza bwino, kukonzanso PostgreSQL nthawi zonse kuti zipangitse kuti ma checkpoints asakhale ochepa, amawafalitsa pakapita nthawi pakati pawo, ndiye kuti mumalowa mu magawo osasintha a Debian. Kwa magawo ambiri a Linux, ichi ndi chithunzi: vm.dirty_ratio=20, vm.dirty_background_ratio=10.

Zikutanthauza chiyani? Chiwanda chimodzi chothamangitsidwa chinawonekera kuchokera ku kernel 2.6. Pdglush, malingana ndi yemwe akugwiritsa ntchito zomwe, zomwe zimagwira kumbuyo kutaya masamba onyansa kuchokera ku kernel buffer ndi kutaya pamene kuli kofunikira kutaya masamba onyansa ziribe kanthu, pamene kutaya kwa backgrouind sikuthandiza.

Kodi maziko amabwera liti? Pamene 10% ya RAM yonse yomwe ilipo pa seva imakhala ndi masamba akuda mu kernel buffer, ntchito yapadera yolembera imatchedwa kumbuyo. N'chifukwa chiyani ili maziko? Monga parameter, zimatengera masamba angati oti alembe. Ndipo, tinene, amalemba masamba a N. Ndipo kwa kanthawi chinthu ichi chimagona. Kenako amabweranso ndikukopera masamba ena.

Iyi ndi nkhani yosavuta kwambiri. Vuto pano lili ngati dziwe losambira, likathira paipi imodzi, limalowera lina. Choyang'ana chathu chinafika ndipo ngati chitumiza masamba onyansa ochepa kuti awatayire, ndiye kuti pang'onopang'ono zonsezo zidzathetsedwa bwino kuchokera ku kernel buffer pgflush.

Ngati masamba odetsedwawa akupitilizabe kudziunjikira, amadziunjikira mpaka 20%, pambuyo pake choyambirira cha OS ndikulemba zonse ku diski, chifukwa mphamvu idzalephera ndipo zonse zikhala zoyipa kwa ife. Titaya deta iyi, mwachitsanzo.

Chinyengo ndi chiyani? Chinyengo ndichakuti magawowa masiku ano ndi 20 ndi 10% ya RAM yonse yomwe ili pamakina, ndizowopsa kwambiri potengera kutulutsa kwa disk iliyonse yomwe muli nayo.

Tangoganizani kuti muli ndi 128 GB ya RAM. 12,8 GB imafika mu disk system yanu. Ndipo ziribe kanthu kuti muli ndi cache yanji kumeneko, ziribe kanthu kuti muli ndi gulu lotani pamenepo, sizikhala nthawi yayitali.

Chifukwa chake, tikupangira kuti musinthe manambalawa nthawi yomweyo kutengera luso la wolamulira wanu wa RAID. Nthawi yomweyo ndidapanga malingaliro apa kwa wowongolera yemwe ali ndi 512 MB ya cache.

Chilichonse chimatengedwa chophweka kwambiri. Mutha kuyika vm.dirty_background mu byte. Ndipo zokonda izi ziletsa ziwiri zam'mbuyo. Chiŵerengero chilichonse chimakhala mwachisawawa, kapena omwe ali ndi ma byte amatsegulidwa, ndiye omwe ali ndi ma byte agwira ntchito. Koma popeza ndine mlangizi wa DBA ndipo ndimagwira ntchito ndi makasitomala osiyanasiyana, ndimayesetsa kujambula udzu ndipo chifukwa chake, ngati ma byte, ndiye ma byte. Palibe amene adatsimikizira kuti woyang'anira wabwino sangawonjezere kukumbukira kwa seva, kuyiyambitsanso, ndipo chiwerengerocho chidzakhalabe chimodzimodzi. Ingowerengerani manambala awa kuti chilichonse chigwirizane ndi chitsimikizo.

Chimachitika ndi chiyani ngati simukukwanira? Ndalemba kuti kuwotcha kulikonse kumayimitsidwa bwino, koma kwenikweni ichi ndi fanizo. Makina ogwiritsira ntchito ali ndi vuto lalikulu - ali ndi masamba ambiri onyansa, kotero IO yomwe makasitomala anu amapanga imayimitsidwa bwino, i.e. pulogalamuyo yabwera kutumiza funso la sql ku database, likudikirira. Kuyika kulikonse / zotuluka kwa izo ndizochepa kwambiri, chifukwa nkhokweyo imakhala ndi poyang'anira. Ndipo pamene iye amaliza izo siziri zomveka. Ndipo mukakwanitsa kuthamangitsidwa kopanda maziko, zikutanthauza kuti IO yanu yonse imakhala nayo. Ndipo mpaka utatha, sudzachita kanthu.

Pali mfundo ziwiri zofunika kwambiri pano zomwe sizikupitirira malire a lipotili. Zokonda izi ziyenera kugwirizana ndi zoikamo mu postgresql.conf, mwachitsanzo zoikamo. Ndipo dongosolo lanu la disk liyenera kukonzedwa mokwanira. Ngati muli ndi cache pa RAID, ndiye kuti iyenera kukhala ndi batri. Anthu amagula RAID ndi cache yabwino popanda batire. Ngati muli ndi ma SSD mu RAID, ndiye kuti ayenera kukhala ma seva, payenera kukhala ma capacitor pamenepo. Nawu mndandanda watsatanetsatane. Ulalo uwu uli ndi lipoti langa lamomwe mungakhazikitsire disk yogwira ntchito mu PostgreSQL. Pali mindandanda yonseyi pamenepo.

Ndi chiyani chinanso chomwe chingapangitse moyo kukhala wovuta? Izi ndi magawo awiri. Iwo ndi atsopano. Mwachikhazikitso, amatha kuphatikizidwa m'mapulogalamu osiyanasiyana. Ndipo akhoza kupangitsa moyo kukhala wovuta ngati atawatsegula molakwika.

Pali zinthu ziwiri zatsopano. Iwo aonekera kale m'magulu achitatu. Izi ndi sched_migration_cost mu nanoseconds ndi sched_autogroup_enabled, yomwe ndi imodzi mwachisawawa.

Ndipo amawononga bwanji moyo wanu? Kodi sched_migration_cost ndi chiyani? Pa Linux, wokonza mapulani amatha kusamutsa njira kuchokera ku CPU kupita ku ina. Ndipo kwa PostgreSQL, yomwe imayankha mafunso, kusamukira ku CPU ina sikudziwika bwino. Kuchokera pamawonekedwe opangira opaleshoni, mukasintha windows pakati pa openoffice ndi terminal, izi zitha kukhala zabwino, koma kwa database izi ndizoyipa kwambiri. Chifukwa chake, mfundo yololera ndikukhazikitsa migration_cost ku mtengo wina waukulu, osachepera masauzande angapo nanoseconds.

Kodi izi zitanthauza chiyani kwa okonza mapulani? Zidzaganiziridwa kuti panthawiyi ndondomekoyi idakali yotentha. Ndiye kuti, ngati muli ndi ntchito yayitali yomwe yakhala ikuchita kwa nthawi yayitali, wokonza mapulani amvetsetsa izi. Adzaganiza kuti mpaka nthawiyi itatha, palibe chifukwa chosunthira njirayi kulikonse. Ngati panthawi imodzimodziyo ndondomekoyo ikuchita chinachake, ndiye kuti sichidzasamutsidwa kulikonse, idzagwira ntchito mwakachetechete pa CPU yomwe inapatsidwa kwa izo. Ndipo zotsatira zake ndi zabwino kwambiri.

Mfundo yachiwiri ndi autogroup. Pali lingaliro labwino lazochulukira zantchito zomwe sizikugwirizana ndi nkhokwe zamakono - uku ndikuyika m'magulu mwa ma terminal omwe amayambirako. Izi ndizoyenera ntchito zina. M'malo mwake, PostgreSQL ndi njira yopangira zinthu zambiri yokhala ndi prefork yomwe imachokera ku terminal imodzi. Muli ndi loko yolembera, poyang'ana, ndipo zopempha zanu zonse zamakasitomala zidzagawidwa m'magulu amodzi, pa CPU iliyonse. Ndipo adzadikirira pamenepo pamodzi kuti akhale mfulu, kuti asokoneze wina ndi mnzake ndi kumukhalitsa wotanganidwa. Iyi ndi nkhani yomwe ili yosafunikira kwenikweni pankhani ya katundu wotero choncho iyenera kuzimitsidwa.

Mnzanga Alexey Lesovsky adayesa ndi pgbench yosavuta, komwe adachulukitsa migration_cost mwa dongosolo la kukula ndikuzimitsa autogroup. Kusiyana pa Hardware zoyipa kunali pafupifupi 10%. Pali zokambirana pamndandanda wamakalata a postgres pomwe anthu amapereka zotsatira zakusintha kofananako pakufulumira kwa mafunso kutengera 50%. Pali nkhani zambiri ngati zimenezi.

Ndipo potsiriza, za ndondomeko yopulumutsa mphamvu. Ubwino wake ndikuti Linux ikhoza kugwiritsidwa ntchito pa laputopu. Ndipo akuti idzagwiritsa ntchito batri bwino. Koma mwadzidzidzi zimakhala kuti izi zikhoza kuchitikanso pa seva.

Kuphatikiza apo, ngati mubwereka ma seva kuchokera kwa hoster, ndiye kuti "abwino" omwe ali nawo samasamala kuti mukuchita bwino. Ntchito yawo ndikuwonetsetsa kuti chitsulo chawo chikugwiritsidwa ntchito moyenera momwe angathere. Chifukwa chake, mwachikhazikitso amatha kuloleza kupulumutsa mphamvu laputopu pamakina ogwiritsira ntchito.

Ngati mugwiritsa ntchito zinthu izi pa seva yokhala ndi database yolemetsa, ndiye kuti kusankha kwanu ndi acpi_cpufreq + permornce. Ngakhale atafuna padzakhala mavuto.

Intel_pstate ndi dalaivala wosiyana pang'ono. Ndipo tsopano zokonda zimaperekedwa kwa uyu, monga momwe zilili pambuyo pake ndipo zimagwira ntchito bwino.

Ndipo, motero, bwanamkubwa ndi ntchito chabe. Odemand, powersave ndi zina zonse sizikukhudza inu.

Zotsatira za kufotokoza kusanthula kwa PostgreSQL zitha kusiyana ndi maulamuliro angapo a kukula ngati muthandizira kupulumutsa mphamvu, chifukwa pafupifupi CPU yomwe ili pansi pa database yanu izikhala ikuyenda mosayembekezereka.

Zinthu izi zitha kuphatikizidwa ndi kusakhazikika. Yang'anani mosamala kuti muwone ngati adayatsa mwachisawawa. Ili likhoza kukhala vuto lalikulu kwambiri.

Ndipo pamapeto pake, ndimafuna kunena zikomo kwa anyamata ochokera ku gulu lathu la PosgreSQL-Consulting DBA, omwe ndi Max Boguk ndi Alexey Lesovsky, omwe akupita patsogolo pankhaniyi tsiku lililonse. Ndipo timayesetsa kuchita zomwe tingathe kwa makasitomala athu kuti zonse ziwathandize. Zili ngati malangizo oyendetsa ndege. Zonse pano zalembedwa m’magazi. Uliwonse wa mtedzawu umapezeka munjira ya vuto linalake. Ndine wokondwa kugawana nanu.

Mafunso:

Zikomo! Ngati, mwachitsanzo, kampani ikufuna kusunga ndalama ndikuyika nkhokwe ndi malingaliro ogwiritsira ntchito pa seva imodzi, kapena ngati kampaniyo ikutsatira mchitidwe wamapangidwe a microservice, momwe PostgreSQL imayendera mu chidebe. Chinyengo ndi chiyani? Sysctl idzakhudza kernel yonse padziko lonse lapansi. Sindinamvepo za ma sysctl omwe amapangidwa mwanjira ina kotero kuti azigwira ntchito padera pachidebe. Pali gulu lokha ndipo pali gawo lokha la ulamuliro pamenepo. Kodi mungakhale bwanji ndi izi? Kapena ngati mukufuna kugwira ntchito, ndiye thamangani PostgreSQL pa seva yosiyana ya hardware ndikuyikonza?

Tayankha funso lanu m'njira zitatu. Ngati sitikulankhula za seva ya hardware yomwe imatha kusinthidwa, ndi zina zotero, ndiye kuti mupumule, chirichonse chidzayenda bwino popanda makonda awa. Ngati muli ndi katundu wotere womwe muyenera kupanga izi, ndiye kuti mudzabwera ku seva yachitsulo kale kuposa makonda awa.

Vuto ndi chiyani? Ngati awa ndi makina enieni, ndiye kuti mudzakhala ndi mavuto ambiri, mwachitsanzo, chifukwa chakuti pamakina ambiri, latency ya disk imakhala yosagwirizana. Ngakhale disk throughput ndi yabwino, ndiye wina analephera I/O transaction zomwe sizimakhudza kwambiri kuchuluka kwapakati komwe kunachitika panthawi yoyang'anira kapena panthawi yolembera WAL, ndiye kuti databaseyo idzavutika kwambiri ndi izi. Ndipo mudzazindikira izi musanakumane ndi mavutowa.

Ngati muli ndi NGINX pa seva yomweyo, mudzakhalanso ndi vuto lomwelo. Adzamenyera kukumbukira nawo. Ndipo simudzafika ku zovuta zomwe zafotokozedwa apa.

Koma kumbali ina, zina mwazigawozi zidzakhala zofunikirabe kwa inu. Mwachitsanzo, ikani dirty_ratio ndi sysctl kuti isakhale yopenga - mulimonse, izi zithandiza. Mwanjira ina, mutha kulumikizana ndi diski. Ndipo zidzakhala molingana ndi dongosolo lolakwika. Izi nthawi zambiri zimakhala zosasinthika pazigawo zomwe ndawonetsa. Ndipo mulimonsemo ndi bwino kusintha iwo.

Koma pakhoza kukhala zovuta ndi NUMA. VmWare, mwachitsanzo, imagwira ntchito bwino ndi NUMA yokhala ndi zosintha zofananira. Ndipo apa muyenera kusankha - seva yachitsulo kapena yopanda chitsulo.

Ndili ndi funso lokhudzana ndi Amazon AWS. Ali ndi zithunzi zokonzedweratu. Mmodzi wa iwo amatchedwa Amazon RDS. Kodi pali makonda aliwonse pamakina awo ogwiritsira ntchito?

Pali zokonda pamenepo, koma ndizosiyana. Apa tikukonzekera machitidwe ogwiritsira ntchito momwe deta idzagwiritsire ntchito chinthu ichi. Ndipo pali magawo omwe amatsimikizira komwe tiyenera kupita tsopano, monga kupanga. Ndiko kuti tikufunika chuma chambiri, tsopano tizidya. Pambuyo pake, Amazon RDS imalimbitsa izi, ndipo magwiridwe antchito amatsika pamenepo. Pali nkhani zapaokha za momwe anthu akuyamba kusokoneza nkhaniyi. Nthawi zina ngakhale bwino ndithu. Koma izi sizikugwirizana ndi zokonda za OS. Zili ngati kuthyola mtambo. Imeneyo ndi nkhani yosiyana.

Chifukwa chiyani masamba akulu a Transparent alibe mphamvu poyerekeza ndi Huge TLB?

Osapereka. Izi zikhoza kufotokozedwa m'njira zambiri. Koma kwenikweni samapereka. Kodi mbiri ya PostgreSQL ndi yotani? Poyambira, imagawa gawo lalikulu la kukumbukira komwe kugawana. Kaya zikuwonekera kapena ayi, zilibe ntchito. Mfundo yakuti iwo amaonekera poyamba ikufotokoza zonse. Ndipo ngati pali zokumbukira zambiri ndipo muyenera kumanganso gawo logawana_memory, ndiye kuti masamba akuluakulu a Transparent adzakhala oyenera. Mu PostgreSQL, zimangogawidwa mu chunk yayikulu pachiyambi ndipo ndizomwezo, ndiyeno palibe chapadera chomwe chimachitika pamenepo. Mukhoza, ndithudi, kuzigwiritsa ntchito, koma pali mwayi wopeza chivundi cha share_memory pamene chigawiranso chinachake. PostgreSQL sakudziwa za izi.

Source: www.habr.com