Linux tuning yekuvandudza mashandiro ePostgreSQL. Ilya Kosmodemyansky

Chinyorwa cheiyo 2015 mushumo naIlya Kosmodemyansky "Linux tuning yekuvandudza PostgreSQL kuita"

Disclaimer: Ndinocherechedza kuti chirevo ichi chakaitwa munaNovember 2015 - anopfuura makore mana apfuura uye nguva yakawanda yapfuura. Iyo vhezheni 4 yakakurukurwa mumushumo haichatsigirwi. Kwemakore mana apfuura, mashanu matsva ePostgreSQL akaburitswa, uye gumi neshanu shanduro dzeLinux kernel dzakaburitswa. Kana ukanyora patsva ndima idzi, unozopedzisira wava nemushumo wakasiyana. Asi pano isu tinofunga yakakosha Linux tuning yePostgreSQL, iyo ichiri kushanda nanhasi.

Ini ndinonzi Ilya Kosmodemyansky. Ini ndinoshanda kuPostgreSQL-Consulting. Uye ikozvino ini ndichataura zvishoma nezve zvekuita neLinux maererano nedhatabhesi muhuwandu uye PostgreSQL kunyanya, nekuti iyo misimboti yakafanana.

Tichataura nezvei? Kana iwe ukataurirana nePostgreSQL, saka kusvika pamwero wakati unofanirwa kuve UNIX admin. Zvinorevei? Kana tikaenzanisa Oracle nePostgreSQL, saka muOracle unofanirwa kuve 80% DBA dhatabhesi admin uye 20% Linux admin.

NePostgreSQL zvakatonyanya kuomarara. NePostgreSQL unofanirwa kuve nekunzwisisa kuri nani kwemashandiro anoita Linux. Uye panguva imwecheteyo, mhanya zvishoma mushure meiyo locomotive, nekuti nguva pfupi yadarika zvese zvakagadziridzwa zvakanaka. Uye kernels itsva dzinoburitswa, uye mashandiro matsva anooneka, kuita kunonatsiridza, nezvimwe.

Sei tiri kutaura nezve Linux? Kwete nekuti tiri kumusangano weLinux Peter, asi nekuti mumamiriro ezvinhu emazuva ano imwe yeanonyanya kurongeka masisitimu ekushandisa dhatabhesi mune zvese uye PostgreSQL kunyanya iLinux. Nekuti FreeBSD, zvinosiririsa, iri kusimukira mune imwe nzira isinganzwisisike. Uye pachave nematambudziko ese nekuita uye nezvimwe zvinhu zvakawanda. Kuita kwePostgreSQL paWindows kunowanzo inyaya yakakomba yakaparadzana, zvichibva pakuti Windows haina yakagovaniswa ndangariro seUNIX, nepo PostgreSQL yese yakasungirirwa kune izvi, nekuti iyo yakawanda-maitiro system.

Uye ndinofunga munhu wese haafarire exotics seSolaris, saka ngatiendei.

Iyo yemazuva ano Linux kugovera kune pamusoro pe1 syctl sarudzo, zvichienderana nekuti unovaka sei kernel. Panguva imwecheteyo, kana tikatarisa nuts dzakasiyana, tinogona kugadzirisa chimwe chinhu nenzira dzakawanda. Kune faira system parameters ekuti ungaisa sei. Kana uine mibvunzo pamusoro pekuti ungatanga sei: chii chekugonesa muBIOS, maitiro ekugadzirisa hardware, nezvimwe.

Iri ivhoriyamu yakakura kwazvo inogona kukurukurwa pamusoro pemazuva akati wandei, uye kwete mumushumo mupfupi, asi ikozvino ini ndichatarisa pazvinhu zvakakosha, nzira yekudzivirira iyo rakes inovimbiswa kukudzivirira kubva kushandisa yako dhatabhesi zvakanaka paLinux kana iwe. usavaruramisa . Uye panguva imwecheteyo, chinhu chakakosha ndechekuti akawanda default parameters haana kuisirwa muzvirongwa izvo zvakaringana kune database. Ndiko kuti, nekusarudzika ichashanda zvisina kunaka kana kwete zvachose.

Ndezvipi zvechinyakare tuning zvinangwa zviripo muLinux? Ini ndinofunga kuti sezvo mese muri kubata neLinux manejimendi, hapana chikonzero chekutsanangura kuti ndezvipi zvinangwa.

Unogona kutamba:

• CPUs.
• Ndangariro.
• Kuchengetedza.
• Zvimwe. Tichazotaura pamusoro peizvi kumagumo kune chekudya. Kunyangwe, semuenzaniso, ma paramita akadai sesimba rekuchengetedza simba anogona kukanganisa mashandiro nenzira isingatarisirwi uye kwete iyo inonyanya kunakidza nzira.

Ndezvipi zvakatsanangurwa zvePostgreSQL uye dhatabhesi zvakazara? Dambudziko nderekuti haugone kugadzirisa chero nzungu uye kuona kuti kuita kwedu kwakawedzera zvakanyanya.

Hongu, kune zvigadzirwa zvakadaro, asi dhatabhesi chinhu chakaoma. Iyo inopindirana nezvose zviwanikwa izvo sevha ine uye inofarira kupindirana kusvika pakuzara. Kana iwe ukatarisa kurudziro yazvino yaOracle pamashandisiro ekushandisa OS inotambira, ichaita sejee nezve iyo Mongolia cosmonaut - dyisa imbwa uye usabata chero chinhu. Ngatipei dhatabhesi zviwanikwa zvese, dhatabhesi pachayo ichagadzirisa zvese kunze.

Muchidimbu, kusvika pamwero wakati mamiriro acho akafanana nePostgreSQL. Musiyano ndewekuti dhatabhesi haisati yakwanisa kutora zvese zviwanikwa zvayo, i.e. kumwe kunhu paLinux level iwe unofanirwa kuzvigadzirisa zvese iwe pachako.

Pfungwa huru haisi yekusarudza chinangwa chimwe chete uye kutanga kuchigadzirisa, semuenzaniso, chiyeuchidzo, CPU kana chimwe chinhu chakadaro, asi kuongorora basa rekushanda uye kuedza kuvandudza mabudiro zvakanyanya sezvinobvira kuitira kuti mutoro wakagadzirwa nevagadziri vakanaka. kwatiri, kusanganisira vashandisi vedu.

Heino mufananidzo kutsanangura kuti chii. Kune Linux OS buffer uye kune yakagovaniswa ndangariro uye kune PostgreSQL yakagovaniswa mabuffer. PostgreSQL, kusiyana neOracle, inoshanda zvakananga chete kuburikidza nekernel buffer, kureva kuti, kuitira kuti peji kubva kudhisiki ipinde mundangariro yayo yakagovaniswa, inofanirwa kupfuura nepakati kernel buffer uye kumashure, iwo chaiwo mamiriro akafanana.

Madhisiki anogara pasi pechirongwa ichi. Ndakadhirowa izvi semadisk. Muchokwadi, panogona kunge paine RAID controller, nezvimwe.

Uye iyi yekupinza-inobuda neimwe nzira kana imwe inoitika kuburikidza nenyaya iyi.

PostgreSQL ndeye classic database. Pane peji mukati. Uye zvese zvinopinza uye zvinobuda zvinoitika uchishandisa mapeji. Tiri kusimudza matombo mundangariro nemapeji. Uye kana pasina chakaitika, isu takangozviverenga, zvino zvishoma nezvishoma ivo vanonyangarika kubva kune iyi cache, kubva kune yakagovaniswa mabhafa uye vanopedzisira vadzokera pa diski.

Kana tikatsiva chimwe chinhu pane imwe nzvimbo, ipapo peji rese rinoiswa chiratidzo sechakasviba. Ndakamaka apa neblue. Uye izvi zvinoreva kuti peji rino rinofanirwa kuwiriraniswa nekuchengetedza block. Ndiko kuti, patakaisvibisa, takapinda muWAL. Uye pane imwe nguva inoshamisa munguva, chiitiko chinonzi chekuongorora chakauya. Uye mashoko akanyorwa mubhuku iri kuti akanga asvika. Uye izvi zvinoreva kuti mapeji ese akasviba aive pano panguva iyoyo mune aya akagovaniswa mabhafa akawiriraniswa nedhisiki rekuchengetedza uchishandisa fsync kuburikidza nekernel buffer.

Sei izvi zvichiitwa? Kana isu takarasa voltage, saka isu hatina kuwana mamiriro ekuti data rese rakarasika. Chiyeuchidzo chinoramba chiripo, icho munhu wese akatiudza nezvazvo, chiri kure mune dhatabhesi dzidziso - iri ramangwana rakajeka, iro isu, chokwadi, tinovavarira uye tinorida, asi ikozvino vanorarama mumakore makumi maviri. Uye, chokwadi, izvi zvose zvinoda kuongororwa.

Uye basa rekuwedzera throughput nderekunyatso-tune pamatanho ese aya kuitira kuti zvese zvifambe nekudzoka nekukasira. Memory yakagovaniswa inongova peji cache. MuPostgreSQL takatumira mubvunzo wakasarudzika kana chimwe chinhu, yakatora iyi data kubva kudhisiki. Vakapedzisira vave mumabhafa akagovana. Nokudaro, kuti izvi zvishande zviri nani, panofanira kuva nendangariro dzakawanda.

Kuti zvese izvi zvishande nemazvo uye nekukurumidza, unofanirwa kunyatso gadzirisa iyo inoshanda sisitimu pamatanho ese. Uye sarudza yakaenzana hardware, nekuti kana iwe uine kusaenzana mune imwe nzvimbo, saka iwe unogona kuita yakawanda yekurangarira, asi haizoshandiswe nekumhanya kwakakwana.

Uye ngatiende neimwe yeiyi pfungwa.

Kuita kuti mapeji aya afambe nekudzoka nekukurumidza, unofanirwa kuita zvinotevera:

• Chekutanga, unofanirwa kushanda zvakanyanya nendangariro.
• Chechipiri, shanduko iyi kana mapeji kubva mundangariro achienda kune dhisiki inofanira kunyatsoita.
• Uye chechitatu, panofanira kunge kune madhisiki akanaka.

Kana iwe uine 512 GB ye RAM muvhavha uye yose inopera paSATA hard drive pasina chero cache, ipapo yese server yedatabase inoshanduka kuva kwete nhanga chete, asi nhanga ine SATA interface. Iwe uchamhanyira mairi zvakananga. Uye hapana chichakuponesa.

Pamusoro pepfundo rokutanga nendangariro, pane zvinhu zvitatu zvinogona kuita kuti upenyu huve hwakaoma zvikuru.

Chekutanga chadzo ndiNUMA. NUMA chinhu chinogadzirwa kusimudzira mashandiro. Zvichienderana nebasa rekuita, zvinhu zvakasiyana zvinogona kugadziriswa. Uye muchimiro chayo chitsva chazvino, haina kunaka kune maapplication akadai sedhatabhesi anoshandisa zvakanyanya peji cache yakagovaniswa buffers.

Muchidimbu. Ungaziva sei kana pane chakaipa paNUMA? Iwe une imwe mhando yekugogodza kusingafadzi, kamwe kamwe imwe CPU inoremerwa. Panguva imwecheteyo, iwe unoongorora mibvunzo muPostgreSQL uye ona kuti hapana chakafanana ipapo. Iyi mibvunzo haifanire kunge yakanyanya CPU yakanyanya. Unogona kubata izvi kwenguva yakareba. Zviri nyore kushandisa kurudziro chaiyo kubva pakutanga pakugadzirisa NUMA yePostgreSQL.

Chii chaizvo chiri kuitika? NUMA inomirira Non-Uniform Memory Access. Zvine basa rei? Iwe une CPU, padivi payo pane ndangariro yenzvimbo. Uye iyi ndangariro inobatana inogona kudhonza ndangariro kubva kune mamwe maCPU.

Kana uchimhanya numactl --hardware, ipapo iwe uchawana pepa rakakura kudaro. Pakati pezvimwe zvinhu, pachava nenzvimbo yemadaro. Pachava nenhamba - 10-20, chimwe chinhu chakadaro. Nhamba idzi hadzisi chimwe chinhu kunze kwenhamba yehops yekutora iyi ndangariro iri kure uye kuishandisa munharaunda. In musimboti, pfungwa yakanaka. Izvi zvinomhanyisa kuita zvakanaka pasi pehuwandu hwehuwandu hwemabasa.

Zvino fungidzira kuti une CPU imwe kutanga kuyedza kushandisa ndangariro yenzvimbo, wozoedza kukwevera imwe ndangariro kuburikidza nekubatanidza kune chimwe chinhu. Uye iyi CPU inowana yako yese PostgreSQL peji cache - ndizvozvo, mamwe magigabytes. Iwe unogara uchiwana iyo yakaipisisa kesi, nekuti paCPU kazhinji pane ndangariro shoma mune iyo module pachayo. Uye ndangariro dzese dzinoitirwa masevhisi dzinoenda kuburikidza nekubatanidza uku. Zvinobuda zvishoma nezvishoma uye zvinosuruvarisa. Uye processor yako inosevhisi iyi node inogara yakawandisa. Uye nguva yekuwana yendangariro iyi yakaipa, inononoka. Aya ndiwo mamiriro ausingade kana uri kushandisa izvi kune dhatabhesi.

Naizvozvo, imwe nzira chaiyo yedatabase ndeye Linux inoshanda sisitimu yekusaziva zviri kuitika ipapo zvachose. Kuti iwane ndangariro sezvainoita.

Nei zvakadaro? Zvingaita sekuti zvinofanira kuva neimwe nzira. Izvi zvinoitika nechikonzero chimwe chakareruka: isu tinoda yakawanda yekuyeuka kune peji cache - makumi, mazana emagigabytes.

Uye kana isu tikagovera zvese izvi uye tikavharira data redu ipapo, ipapo kuwana kubva mukushandisa cache kuchave kwakakura zvakanyanya kupfuura kuwana kubva kune inonyengera kuwana mundangariro. Uye isu tichabatsirwa zvisingaenzaniswi kana tichienzaniswa nekuti tichawana ndangariro zvakanyanya tichishandisa NUMA.

Naizvozvo, pane nzira mbiri pano panguva ino, kusvikira ramangwana rakajeka rasvika, uye dhatabhesi pachayo haigone kuona kuti ndeapi maCPU arikumhanya uye kwaanofanira kudhonza chimwe chinhu kubva.

Naizvozvo, nzira chaiyo ndeye kudzima NUMA zvachose, semuenzaniso, paunenge uchitangazve. Muzviitiko zvakawanda, kuhwina ndekwemirairo yehukuru zvekuti mubvunzo wekuti ndeupi uri nani haumuke zvachose.

Pane imwe sarudzo. Isu tinoishandisa kakawanda kupfuura yekutanga, nekuti kana mutengi achiuya kwatiri kuzotsigirwa, kutangazve sevha chinhu chikuru kwaari. Ane bhizinesi ikoko. Uye vanosangana nematambudziko nekuda kweNUMA. Naizvozvo, tinoedza kuidzima nenzira shoma dzinopinda pane kutangazve, asi chenjera kutarisa kuti yakaremara. Nekuti, sezvinoratidzwa nechiitiko, zvakanaka kuti isu tidzivise NUMA pane mubereki PostgreSQL maitiro, asi hazvina hazvo basa kuti ishande. Tinofanirwa kutarisa toona kuti adzima zvechokwadi here.

Pane yakanaka positi naRobert Haas. Uyu ndeumwe wePostgreSQL vabati. Imwe yevakakosha vagadziri veese akaderera-level giblets. Uye kana iwe ukatevera zvinongedzo kubva pane ino positi, vanotsanangura akati wandei ane mavara nyaya dzekuti NUMA yakaita sei kuti hupenyu huome kuvanhu. Tarisa, dzidza iyo system administrator cheki yezvinoda kugadzirwa pane server kuitira kuti dhatabhesi redu rishande nemazvo. Aya marongero anofanirwa kunyorwa pasi uye kuongororwa, nekuti kana zvisina kudaro hazvizonyanya kunaka.

Ndokumbira utarise kuti izvi zvinoshanda kune ese marongero andichataura nezvayo. Asi kazhinji dhatabhesi inounganidzwa mune tenzi-muranda modhi yekutadza kushivirira. Usakanganwe kugadzira masetting aya pahapwa nekuti rimwe zuva uchaita tsaona ukachinja uchienda kunhapwa zvobva zvaita tenzi.

Mumamiriro ezvinhu ekukurumidzira, kana zvese zvanyanya kuipa, foni yako inogara ichirira uye mukuru wako anouya achimhanya netsvimbo hombe, hauzove nenguva yekufunga nezvekutarisa. Uye miuyo yacho inogona kuva ine ngwavaira zvikuru.

Pfungwa inotevera mapeji makuru. Mapeji makuru akaoma kuyedza akaparadzana, uye hapana chikonzero mukuita kudaro, kunyangwe paine mabhenji anogona kuita izvi. Izvo zviri nyore kuGoogle.

Zvine basa rei? Iwe une sevha isingadhure zvakanyanya ine RAM yakawanda, semuenzaniso, kupfuura 30 GB. Iwe haushandise mapeji makuru. Izvi zvinoreva kuti iwe zvechokwadi une overhead maererano nekushandiswa kwendangariro. Uye iyi yekumusoro iri kure nekunyanya kunakidza.

Nei zvakadaro? Saka chii chiri kuitika? Iyo inoshanda sisitimu inogovera ndangariro muzvidimbu zvidiki. Zviri nyore, ndizvo zvakaitika kare. Uye kana tikapinda mune zvakadzama, iyo OS inofanirwa kushandura chaiwo kero mune dzenyama. Uye maitiro aya haasi akareruka, saka OS inochengetedza mhedzisiro yekuvhiya uku muDudziro Lookaside Buffer (TLB).

Uye sezvo iyo TLB iri cache, matambudziko ese arimo mune cache anomuka mumamiriro ezvinhu aya. Chekutanga, kana uine RAM yakawanda uye yese yakagoverwa mumachunks madiki, saka iyi buffer inova yakakura kwazvo. Uye kana cache yakakura, saka kutsvaga kuburikidza nayo kunononoka. Kumusoro kune hutano uye iyo pachayo inotora nzvimbo, kureva kuti RAM iri kudyiwa nechimwe chinhu chisina kunaka. Nguva ino.

Vaviri - iyo yakawanda cache inokura mumamiriro ezvinhu akadaro, zvakanyanya kuitika kuti iwe uchave uine cache misses. Uye kushanda kweiyi cache kunokurumidza kuderera sezvo kukura kwayo kunowedzera. Naizvozvo, masisitimu ekushandisa akauya nenzira iri nyore. Yakashandiswa muLinux kwenguva yakareba. Yakaonekwa muFreeBSD kwete kare kare. Asi isu tiri kutaura nezve Linux. Aya mapeji makuru.

Uye pano zvinofanirwa kucherechedzwa kuti mapeji akakura, sepfungwa, akatanga kusundidzirwa nenharaunda dzaisanganisira Oracle neIBM, kureva kuti vanogadzira dhatabhesi vakafunga zvakasimba kuti izvi zvingave zvinobatsira kune dhatabhesi zvakare.

Uye izvi zvingaitwa sei shamwari nePostgreSQL? Chekutanga, mapeji mahombe anofanira kugoneswa muLinux kernel.

Chechipiri, ivo vanofanirwa kutsanangurwa zvakajeka ne sysctl parameter - vangani varipo. Nhamba dziri pano dzakabva kune imwe server yekare. Iwe unogona kuverenga kuti mangani akagovaniswa mabuffers aunawo kuitira kuti mapeji akakura akwane ipapo.

Uye kana sevha yako yese yakatsaurirwa kuPostgreSQL, saka yakanaka yekutanga kugovera ingave 25% ye RAM kune yakagovaniswa mabuffers, kana 75% kana uine chokwadi chekuti dhatabhesi yako ichanyatso kukwana mune iyi 75%. Nzvimbo yekutanga. Uye funga, kana iwe uine 256 GB ye RAM, saka, maererano, iwe uchave uine 64 GB yemabhuru makuru. Verenga zvishoma neimwe margin - kuti nhamba iyi inofanira kusetwa pairi.

Pamberi pevhezheni 9.2 (kana ndisiri kukanganisa, kubvira vhezheni 8.2), zvaigoneka kubatanidza PostgreSQL nemapeji makuru uchishandisa raibhurari yebato rechitatu. Uye izvi zvinofanira kuitwa nguva dzose. Kutanga, iwe unoda kernel kuti ukwanise kugovera mapeji akakura nemazvo. Uye, chechipiri, kuitira kuti iyo application inoshanda navo inogona kuvashandisa. Hazvizongo shandiswa nenzira iyoyo. Sezvo PostgreSQL yakapihwa ndangariro mune system 5 maitiro, izvi zvinogona kuitwa uchishandisa libhugetlbfs - iri ndiro zita rakazara reraibhurari.

Muna 9.3, kuita kwePostgreSQL kwakavandudzwa pakushanda nendangariro uye iyo system 5 memory yekugovera nzira yakasiiwa. Wese munhu aifara kwazvo, nekuti zvikasadaro unoedza kumhanya maviri ePostgreSQL kesi pamushini mumwe, uye anoti ini handina ndangariro dzakagovaniswa. Uye anoti sysctl inoda kugadziriswa. Uye kune sysctl yakadaro kuti iwe uchiri kufanira kutangazve, nezvimwewo. Kazhinji, munhu wose aifara. Asi mmap memory allocation yakatyora kushandiswa kwemapeji akakura. Vazhinji vevatengi vedu vanoshandisa hombe dzakagovaniswa buffers. Uye isu takakurudzira zvakasimba kuti tisachinjira ku9.3, nekuti kumusoro uko kwakatanga kuverengerwa muzvikamu zvakanaka.

Asi nharaunda yakateerera dambudziko iri uye muna 9.4 vakagadzirisa chiitiko ichi zvakanaka kwazvo. Uye mu9.4 parameter yakaonekwa mu postgresql.conf yaunokwanisa kugonesa kuedza, kubatidza kana kudzima.

Edza ndiyo yakachengeteka sarudzo. Kana PostgreSQL yatanga, kana yagovera ndangariro yakagovaniswa, inoedza kubata iyi ndangariro kubva kumapeji makuru. Uye kana ikasashanda, zvino inotenderera ichidzokera kune yakajairwa sarudzo. Uye kana iwe uine FreeBSD kana Solaris, saka iwe unogona kuedza, inogara yakachengeteka.

Kana iripo, saka haingotange kana ikatadza kusarudza kubva pamapeji makuru. Apa zvatove nezve kuti ndiani uye chii chakanaka. Asi kana iwe waedza, wobva watarisa kuti une chaizvo zvaunoda zvakasimbiswa, nekuti pane yakawanda nzvimbo yekukanganisa. Parizvino basa iri rinongoshanda paLinux.

Imwezve katsamba kadiki tisati taenda mberi. Mapeji mahombe akajeka haasi ePostgreSQL parizvino. Haakwanisi kuzvishandisa nguva dzose. Uye neTransparent yakakura mapeji ebasa rakadaro, kana chikamu chikuru chendangariro chakagovaniswa chichidikanwa, mabhenefiti anongouya nemavhoriyamu akakura kwazvo. Kana iwe uine terabytes yendangariro saka izvi zvinogona kupinda mukutamba. Kana isu tiri kutaura nezve mamwe mazuva ese maapplication, kana iwe uine 32, 64, 128, 256 GB yendangariro pamushini wako, saka akajairwa mapeji mahombe ari Ok, uye isu tinongodzima Transparent.

Uye chinhu chekupedzisira pamusoro pekuyeuka hachina hukama zvakananga kune fruitut, inogona kukanganisa hupenyu hwako. Kubuda kwese kuchakanganiswa zvakanyanya nenyaya yekuti sevha inogara ichichinja.

Uye izvi zvichange zvisingafadzi munzira dzakawanda. Uye dambudziko guru nderekuti kernels dzemazuva ano dzinoita zvakasiyana kubva kune yekare Linux kernels. Uye chinhu ichi hachina kufadza kutsika, nekuti kana tichitaura nezve imwe mhando yebasa nekuchinjana, inopera nekusasvika kwekukurumidza kweOOM-mhondi. Uye iyo OOM-mhondi, iyo isina kusvika nenguva uye yakadonhedza PostgreSQL, haina kufadza. Munhu wose achaziva pamusoro peizvi, kureva, kusvikira mushandisi wekupedzisira.

Chii chiri kuitika? Iwe une huwandu hukuru hwe RAM ipapo, zvese zvinoshanda nemazvo. Asi nekuda kwechimwe chikonzero sevha inorembera mukutsinhana uye inononoka nekuda kweizvi. Zvingaita sekuti pane zvakawanda zvekuyeuka, asi izvi zvinoitika.

Kare, takakurudzira kuseta vm.swappiness kusvika zero, kureva kudzima shanduko. Pakutanga, zvaiita senge 32 GB ye RAM uye inowirirana yakagovaniswa mabuffer yaive yakawanda. Chinangwa chikuru chekuchinjana ndechekuti tive nenzvimbo yekukanda goko kana tikadonha. Uye rakanga risingachanyanyi kuzadzika. Uye zvino uchaitei neiyi crust? Iri ibasa risinganyatso kujeka kuti nei kuchinja kuchidiwa, kunyanya ukuru hwakadai.

Asi mune mamwe mazuva ano, kureva, shanduro yechitatu yekernel, maitiro akachinja. Uye kana iwe ukaisa swap kusvika zero, i.e. dzima, ipapo nekukurumidza kana gare gare, kunyangwe paine RAM yasara, mhondi yeOOM ichauya kwauri kuzouraya vatengi vakanyanya. Nokuti iye achafunga kuti nebasa rakadaro isu tichine zvishoma zvishoma uye isu tichasvetuka kunze, kureva, kwete kuroverera pasi maitiro ehurongwa, asi kuroverera pasi chimwe chinhu chisinganyanyi kukosha. Iyi isinganyanyi kukosha ichave mutengi ane simba wekugovana ndangariro, kureva postmaster. Uye mushure mezvo zvichava zvakanaka kana chigadziko chisingafaniri kudzorerwa.

Naizvozvo, ikozvino yakasarudzika, sekurangarira kwandinorangarira, kugovera kwakawanda kune imwe nzvimbo yakatenderedza 6, i.e. papi paunofanira kutanga kushandisa shanduko zvichienderana nekuti yakawanda ndangariro yasara. Isu zvino tinokurudzira kuisa vm.swappiness = 1, nekuti izvi zvinodzima, asi hazvipi mhedzisiro yakafanana neyeOOM-mhondi yakasvika zvisingatarisirwi ikauraya chinhu chose.

Chii chinotevera? Kana isu tichitaura nezve mashandiro edhatabhesi uye zvishoma nezvishoma tichienda kune disks, munhu wese anotanga kubata misoro yavo. Nekuti chokwadi chekuti dhisiki inononoka uye ndangariro inokurumidza inozivikanwa kune wese munhu kubva paudiki. Uye munhu wese anoziva kuti dhatabhesi ichava nematambudziko ekuita disk.

Dambudziko guru rePostgreSQL rekuita rakabatana nemacheckpoints spikes haaitike nekuti diski inononoka. Izvi zvinonyanya kuitika nekuda kwekuti ndangariro uye disk bandwidth hazvina kuenzana. Zvisinei, vanogona kunge vasina kuenzana munzvimbo dzakasiyana. PostgreSQL haina kugadzirwa, iyo OS haina kugadzirwa, iyo hardware haina kugadzirwa uye hardware haina kururama. Uye dambudziko iri harisi kuitika chete kana zvinhu zvose zvikaitika sezvazvinofanira, i.e. kana pasina mutoro, kana zvirongwa uye hardware zvakasarudzwa zvakanaka.

Chii uye chinoratidzika sei? Kazhinji vanhu vanoshanda nePostgreSQL vakapinda munyaya iyi kanopfuura kamwe chete. Ndichatsanangura. Sezvandakataura, PostgreSQL nguva nenguva inogadzira macheki ekurasa mapeji akasviba mundangariro yakagovaniswa kune disk. Kana isu tiine huwandu hukuru hwekugovana ndangariro, ipapo cheki chinotanga kuve nechakanyanya kukanganisa padhisiki, nekuti inorasa mapeji aya nefsync. Iyo inosvika mu kernel buffer uye inonyorerwa kune disks uchishandisa fsync. Uye kana vhoriyamu yebhizinesi iri yakakura, saka isu tinogona kuona isingafadzi mhedzisiro, iyo yakakura kwazvo kushandiswa kwema disks.

Pano ndine mifananidzo miviri. Ndichatsanangura zvino kuti chii. Aya magirafu maviri anoenderana nenguva. Iyo yekutanga girafu ndeye dhisiki kushandiswa. Pano inosvika inenge 90% panguva ino nenguva. Kana iwe uine dhatabhesi kukundikana nemadhisiki emuviri, ine RAID controller kushandiswa pa90%, zvino iyi inhau dzakaipa. Izvi zvinoreva kuti zvishoma uye ichasvika zana uye iyo I / O ichamira.

Kana iwe uine dhisiki array, saka inyaya yakati siyanei. Izvo zvinoenderana nekuti yakagadziriswa sei, imhando ipi yehurongwa, nezvimwe.

Uye zvakafanana, girafu kubva mukati memukati postgres maonero inogadziriswa pano, iyo inotaurira kuti chekicho chinoitika sei. Uye ruvara rwegirinhi apa runoratidza kuti mabhafa mangani, aya mapeji ane tsvina, panguva iyoyo akasvika panzvimbo ino yekutarisa kuti iwirirane. Uye ichi ndicho chinhu chikuru chaunofanira kuziva pano. Tinoona kuti tine mapeji akawanda pano uye pane imwe nguva tinorova bhodhi, kureva kuti, takanyora uye takanyora, pano disk system yakanyatsoshanda. Uye yedu yekutarisa ine simba rakasimba pane dhisiki. Zvakanaka, mamiriro acho anofanira kutaridzika seizvi, i.e. takanga tisina kurekodha pano. Uye isu tinogona kuzvigadzirisa nemaseting kuitira kuti zvirambe zvakadaro. Ndiko kuti, kudzokorodza kudiki, asi pane imwe nzvimbo isu tiri kunyora chimwe chinhu pano.

Chii chinoda kuitwa kukunda dambudziko iri? Kana iwe wakamisa IO pasi pe database, izvi zvinoreva kuti vese vashandisi vakauya kuzozadzisa zvikumbiro zvavo vachamirira.

Kana iwe ukatarisa kubva pamaonero eLinux, kana iwe wakatora yakanaka hardware, yakagadziridza nemazvo, yakamisikidzwa PostgreSQL kazhinji kuitira kuti iite kuti idzi dzekutarisa dziite shoma kazhinji, dzinodziparadzira nekufamba kwenguva pakati peumwe neumwe, wobva wapinda mune default Debian paramita. Pakugoverwa kweLinux kwakawanda, uyu ndiwo mufananidzo: vm.dirty_ratio=20, vm.dirty_background_ratio=10.

Zvinorevei? Rimwe dhimoni rinoputika rakaonekwa kubva kukernel 2.6. Pdglush, zvichienderana nekuti ndiani ari kushandisa iyo, iyo inobatanidzwa kumashure kurasa mapeji ane tsvina kubva kune kernel buffer uye kurasa kana zvakakosha kurasa mapeji ane tsvina pasinei nokuti chii, kana backgrouind kurasa hazvibatsiri.

Kumashure kunouya riini? Kana 10% yeiyo yakazara RAM inowanikwa pane sevha inogarwa nemapeji akasviba mukernel buffer, yakakosha kunyora-basa basa rinodaidzwa kumashure. Nei zviri kumashure? Separameter, zvinotora mundangariro mangani mapeji ekunyora. Uye, ngatitii, anonyora mapeji eN. Uye kwechinguva chinhu ichi chinorara. Uye anobva auya zvakare okopa mamwe mapeji.

Iyi inyaya iri nyore kwazvo. Dambudziko riri pano rakaita sedziva rekutuhwina, parinodira mune imwe pombi, rinoyerera richipinda mune imwe. Yedu yekutarisa yakasvika uye kana yakatumira mashoma akasviba mapeji ekurasa, ipapo zvishoma nezvishoma chinhu chose chichagadziriswa zvakanaka kubva kune kernel buffer pgflush.

Kana mapeji aya akasviba achiramba achiunganidza, anounganidza kusvika ku20%, mushure meiyo OS inonyanya kukosha ndeyekunyora chinhu chose ku diski, nokuti simba richakundikana uye zvose zvichava zvakaipa kwatiri. Ticharasikirwa iyi data, semuenzaniso.

Chii chinonyengera? Icho chinonyengera ndechekuti aya maparamendi munyika yanhasi i20 uye 10% yeiyo yakazara RAM iri pamushini, iwo anotyisa kwazvo maererano nekubuda kwechero disk system yauinayo.

Fungidzira iwe une 128 GB ye RAM. 12,8 GB inosvika mune yako disk system. Uye zvisinei kuti ndeipi cache yaunayo ipapo, zvisinei kuti ndeapi array yaunayo ipapo, haigare kwenguva yakareba kudaro.

Naizvozvo, isu tinokurudzira kuti iwe nekukasika ugadzirise nhamba idzi zvichienderana nekugona kweRAID controller yako. Ndakabva ndaita kurudziro pano kune controller ine 512 MB yecache.

Zvose zvinonzi zviri nyore. Unogona kuisa vm.dirty_background mumabhayiti. Uye izvi zvigadziriso zvinodzima maviri apfuura. Chero reshiyo ndeye default, kana avo vane mabhayiti vanobatidzwa, ipapo vane mabhayiti vachashanda. Asi sezvo ini ndiri chipangamazano weDBA uye ndichishanda nevatengi vakasiyana, ndinoedza kudhirowa mashanga uye saka, kana mumabhayiti, ipapo mumabhayiti. Hapana akapa chero vimbiso yekuti admin akanaka aisazowedzera imwe ndangariro kune sevha, tangazve, uye nhamba yaizoramba yakafanana. Ingoverenga nhamba idzi kuitira kuti zvose zvienderane imomo nevimbiso.

Chii chinoitika kana ukasakwana? Ndakanyora kuti chero kugezesa kunomira zvakanaka, asi chokwadi ichi chirevo chekutaura. Iyo inoshanda sisitimu ine dambudziko hombe - ine akawanda mapeji akasviba, saka iyo IO inogadzirwa nevatengi vako inomira zvinobudirira, i.e. application yauya kutumira sql query kune database, yakamirira. Chero kupinza / kuburitsa kwairi ndekwekunyanya kukosha, nekuti dhatabhesi inogarwa nenzvimbo yekutarisa. Uye paachapedza hazvinyatso jeka. Uye kana iwe wawana kusiri-background flushing, zvinoreva kuti yako yese IO inogarwa nayo. Uye kusvikira yapera, hapana chauchaita.

Pane zvimwe zviviri zvakakosha pano zviri kunze kwechikamu chemushumo uyu. Aya marongero anofanirwa kuenderana nezvirongwa mu postgresql.conf, i.e. checkpoints marongero. Uye dhisiki system yako inofanirwa kugadzirwa zvakakwana. Kana iwe uine cache pane RAID, saka inofanira kunge iine bhatiri. Vanhu vanotenga RAID ine cache yakanaka isina bhatiri. Kana iwe uine maSSD muRAID, saka anofanirwa kunge ari iwo evhavha, panofanira kunge paine capacitors ipapo. Heino rondedzero yakazara. Ichi chinongedzo chine mushumo wangu wekugadzirisa dhisiki rekuita muPostgreSQL. Pane ese aya machekilist ipapo.

Chii chimwe chingaita kuti upenyu huome zvikuru? Aya maparameter maviri. Zvingori zvitsva. Nekusagadzikana, ivo vanogona kuverengerwa mune akasiyana maapplication. Uye vanogona kuita kuti upenyu huome saizvozvo kana vakabatidzwa zvisiri izvo.

Pane zvinhu zviviri zvitsva. Vakatoonekwa mumakori echitatu. Izvi zvinonzi sched_migration_cost mu nanoseconds uye sched_autogroup_enabled, inova imwe nekusarudzika.

Uye vanokanganisa sei hupenyu hwako? Chii chinonzi sched_migration_cost? PaLinux, mugadziri anogona kutamisa maitiro kubva kune imwe CPU kuenda kune imwe. Uye yePostgreSQL, iyo inoita mibvunzo, kutamira kune imwe CPU haina kujeka zvachose. Kubva pane yekushandisa system yekuona, kana iwe uchichinja windows pakati peopenoffice uye terminal, izvi zvingave zvakanaka, asi kune dhatabhesi izvi zvakaipa kwazvo. Naizvozvo, mutemo unonzwisisika ndewekuseta migration_cost kune imwe kukosha kukuru, ingangoita zviuru zvakati wandei nanoseconds.

Izvi zvinorevei kune anoronga? Zvichaonekwa kuti panguva ino nzira ichiri kupisa. Ndiko kuti, kana uine kutengeserana kwenguva refu kwave kuita chimwe chinhu kwenguva yakareba, mugadziri achanzwisisa izvi. Anozofunga kuti kusvika nguva iyi yapera, hapana chikonzero chekutamisa nzira iyi chero kupi. Kana panguva imwe chete iyo nzira ichiita chimwe chinhu, saka haizofambiswi chero kupi zvako, ichashanda chinyararire paCPU iyo yakagoverwa kwairi. Uye mhedzisiro yacho yakanaka kwazvo.

Yechipiri poindi ndeye autogroup. Pane zano rakanaka remamwe mabasa asina hukama neazvino dhatabhesi - iyi ndeyekuita mapoka maitiro neiyo chaiyo terminal kwavanotangwa. Izvi zvakanakira mamwe mabasa. Mukuita, PostgreSQL ndeye-multi-process system ine prefork inomhanya kubva kune imwechete terminal. Iwe une kiyi munyori, cheki, uye zvese zvikumbiro zvemutengi wako zvichaiswa muchikamu chimwe chete, paCPU. Uye vachamirira ikoko pamwe chete kuti asununguke, kuti vavhiringidze mumwe nomumwe uye varambe vachibatikana. Iyi inyaya isingadikanwi zvachose munyaya yemutoro wakadaro uye naizvozvo inoda kudzima.

Wandaishanda naye Alexey Lesovsky akaita bvunzo nepgbench iri nyore, kwaakawedzera migration_cost nekuraira kwehukuru uye akadzima autogroup. Musiyano pane zvakaipa Hardware waive ungangoita 10%. Pane nhaurirano pane postgres tsamba yetsamba apo vanhu vanopa mhedzisiro yekuchinja kwakafanana kune kukurumidza kubvunza kukanganiswa 50%. Kune nyaya dzakadai dzakawanda.

Uye pakupedzisira, pamusoro pekuchengetedza simba mutemo. Chinhu chakanaka ndechekuti Linux ikozvino inogona kushandiswa palaptop. Uye inofungidzirwa kushandisa bhatiri zvakanaka. Asi kamwe kamwe zvinoitika kuti izvi zvinogonawo kuitika pane server.

Uyezve, kana iwe ukahaya maseva kubva kune imwe hoster, saka ivo "vakanaka" vanogamuchira havana basa kuti une basa riri nani. Basa ravo nderekuona kuti iron yavo yashandiswa nemazvo sezvinobvira. Naizvozvo, nekukasira vanogona kugonesa laptop simba rekuchengetedza modhi pane inoshanda sisitimu.

Kana iwe ukashandisa zvinhu izvi pane sevha ine dhatabhesi pasi pemutoro unorema, saka sarudzo yako ndeye acpi_cpufreq + permornce. Kunyangwe neinoda pachava nematambudziko.

Intel_pstate mutyairi akati siyanei. Uye zvino sarudzo inopiwa kune iyi, sezvazviri gare gare uye inoshanda zviri nani.

Uye, maererano, gavhuna ibasa chete. Ondemand, powersave uye zvimwe zvese hazvisi pamusoro pako.

Mhedzisiro yekutsanangura ongorora PostgreSQL inogona kusiyana nemirairo yakati wandei yehukuru kana iwe ukagonesa kuchengetedza simba, nekuti ingangoita iyo CPU iri pasi pe database yako ichange ichimhanya nenzira isingafungidzike zvachose.

Zvinhu izvi zvinogona kusanganisirwa nekusarudzika. Tarisa uone kana vakabatidza nekukasira. Iri rinogona kuva dambudziko guru chairo.

Uye pakupedzisira, ndaida kuti mazvita kune vakomana kubva kuPosgreSQL-Consulting DBA timu yedu, inova Max Boguk naAlexey Lesovsky, vari kufambisa nyaya iyi mazuva ese. Uye isu tinoedza kuita zvese zvatinogona kune vatengi vedu kuti zvese zvivaitire. Zvakafanana nemirairo yekuchengetedza ndege. Zvose pano zvakanyorwa muropa. Imwe neimwe yenzungu idzi inowanikwa mukuita kweimwe mhando yedambudziko. Ndinofara kugoverana newe.

Mibvunzo:

Ndatenda! Kana, semuenzaniso, kambani ichida kuchengetedza mari uye nekuisa dhatabhesi uye mashandisiro ekushandisa pane imwe sevha, kana iyo kambani ichitevera fashoni maitiro e microservice architecture, umo PostgreSQL inomhanya mumudziyo. Chii chinonyengera? Sysctl ichabata kernel pasi rose. Ini handisati ndanzwa nezve sysclts iri kugadzirwa neimwe nzira kuti ishande zvakasiyana pamudziyo. Pane chete boka uye pane chikamu chekutonga ipapo. Ungararama sei nazvo? Kana kana iwe uchida kuita, wobva wamhanya PostgreSQL pane yakaparadzana Hardware server uye uiite?

Takapindura mubvunzo wako nenzira dzinenge nhatu. Kana isu tisiri kutaura nezve sevha yehardware inogona kugadziriswa, nezvimwewo, wobva wazorora, zvese zvichashanda zvakanaka pasina zvigadziriso izvi. Kana iwe uine mutoro wakadaro waunoda kugadzira aya marongero, ipapo iwe uchauya kune iron server pamberi pezvirongwa izvi.

Dambudziko nderei? Kana iyi iri muchina chaiwo, saka kazhinji iwe uchave nematambudziko mazhinji, semuenzaniso, nenyaya yekuti pamichina yakawanda iyo latency yedhisiki haina kunyatsoenderana. Kunyange kana disk throughput yakanaka, ipapo imwe yakakundikana I / O kutengeserana iyo isinganyanyi kukanganisa avhareji yepakati yakaitika panguva yekutarisa kana panguva yekunyora kuWAL, ipapo dhatabhesi ichatambura zvakanyanya kubva pane izvi. Uye iwe unozoona izvi usati wasangana nematambudziko aya.

Kana iwe uine NGINX pane imwechete sevha, iwe zvakare uchava nedambudziko rakafanana. Acharwira ndangariro dzakagoverana. Uye iwe hausi kuzosvika kumatambudziko anotsanangurwa pano.

Asi nerumwe rutivi, mamwe eaya ma parameter achange achiri akakosha kwauri. Semuyenzaniso, isa dirty_ratio ne sysctl kuitira kuti isapenga - chero zvakadaro, izvi zvichabatsira. Imwe nzira kana imwe, iwe uchave nekudyidzana ne diski. Uye zvichava maererano nemaitiro asina kunaka. Izvi zvinowanzoita default kune ma parameter andakaratidza. Uye mune chero mamiriro ezvinhu zviri nani kuvashandura.

Asi panogona kunge paine matambudziko neNUMA. VmWare, semuenzaniso, inoshanda nemazvo neNUMA ine chaizvo zvakapesana. Uye pano iwe unofanirwa kusarudza - sevha yesimbi kana isiri yesimbi.

Ndine mubvunzo une chekuita neAmazon AWS. Vane mifananidzo pre-yakagadzirirwa. Mumwe wavo anonzi Amazon RDS. Pane chero zvigadziriso zvetsika yavo yekushandisa system?

Kune marongero ipapo, asi iwo akasiyana marongero. Pano isu tinogadzirisa sisitimu yekushandisa maererano nemashandisiro anoita dhatabhesi chinhu ichi. Uye kune maparameter anosarudza kwatinofanira kuenda izvozvi, sekuumba. Kureva kuti tinoda zviwanikwa zvakawanda, tichazvidya. Mushure meizvi, Amazon RDS inosimbisa zviwanikwa izvi, uye kuita kunodonha ipapo. Pane nyaya dzega dzega dzekuti vanhu vari kutanga kuvhiringa nyaya iyi sei. Dzimwe nguva kunyange zvakabudirira. Asi izvi hazvinei nechekuita neOS marongero. Zvakafanana nekubira gore. Ndiyo nyaya yakasiyana.

Sei Transparent yakakura mapeji isina mhedzisiro ichienzaniswa neHuge TLB?

Usapa. Izvi zvinogona kutsanangurwa nenzira dzakawanda. Asi chaizvoizvo havazvipi. Ndeipi nhoroondo yePostgreSQL? Pakutanga, inogovera chikamu chikuru chendangariro yakagovaniswa. Kuti zviri pachena here kana kuti kwete hazvina basa zvachose. Idi rokuti ivo vakatanhamara pakutanga rinotsanangura zvose. Uye kana paine ndangariro dzakawanda uye iwe uchifanira kuvaka zvakare yakagovaniswa_memory chikamu, ipapo Transparent hombe mapeji anozove akakodzera. MuPostgreSQL, inongopihwa muhombe chunk pakutanga uye ndizvozvo, uyezve hapana chakakosha chinoitika ipapo. Iwe unogona, hongu, kuishandisa, asi pane mukana wekuwana huwori hwekugovana_memory kana ichigoverazve chimwe chinhu. PostgreSQL haizive nezve izvi.

Source: www.habr.com