Linux tuning los txhim kho PostgreSQL kev ua tau zoo. Ilya Kosmodemyansky

Cov ntawv sau tseg ntawm 2015 tsab ntawv ceeb toom los ntawm Ilya Kosmodemyansky "Linux tuning los txhim kho PostgreSQL kev ua tau zoo"

Kev lees paub: Kuv nco ntsoov tias daim ntawv tshaj tawm no yog hnub tim Kaum Ib Hlis 2015 - ntau dua 4 xyoo tau dhau los thiab ntau lub sijhawm dhau los. Lub version 9.4 tau tham hauv tsab ntawv ceeb toom tsis tau txais kev txhawb nqa lawm. Hauv 4 xyoo dhau los, 5 qhov kev tshaj tawm tshiab ntawm PostgreSQL tau tso tawm, thiab 15 versions ntawm Linux ntsiav tau raug tso tawm. Yog tias koj rov sau cov nqe lus no, koj yuav xaus nrog ib daim ntawv qhia txawv. Tab sis ntawm no peb xav txog qhov tseem ceeb Linux tuning rau PostgreSQL, uas tseem cuam tshuam rau hnub no.

Kuv lub npe yog Ilya Kosmodemyansky. Kuv ua haujlwm ntawm PostgreSQL-Consulting. Thiab tam sim no kuv yuav tham me ntsis txog yuav ua li cas nrog Linux nyob rau hauv kev sib raug zoo rau databases feem ntau thiab PostgreSQL tshwj xeeb, vim hais tias cov ntsiab lus zoo heev.

Peb yuav tham txog dab tsi? Yog tias koj sib txuas lus nrog PostgreSQL, ces qee qhov koj yuav tsum yog UNIX admin. Nws txhais li cas? Yog tias peb piv Oracle thiab PostgreSQL, tom qab ntawd hauv Oracle koj yuav tsum yog 80% DBA database admin thiab 20% Linux admin.

Nrog PostgreSQL nws yog qhov nyuaj me ntsis. Nrog PostgreSQL koj yuav tsum muaj kev nkag siab zoo dua ntawm Linux ua haujlwm li cas. Thiab nyob rau tib lub sijhawm, khiav me ntsis tom qab lub tsheb ciav hlau, vim tsis ntev los no txhua yam tau hloov kho zoo heev. Thiab cov kernels tshiab raug tso tawm, thiab kev ua haujlwm tshiab tshwm sim, kev ua haujlwm txhim kho, thiab lwm yam.

Vim li cas peb tham txog Linux? Tsis yog vim peb nyob ntawm Linux lub rooj sib tham Peter, tab sis vim hais tias nyob rau hauv niaj hnub tej yam kev mob ib tug ntawm cov feem ntau ncaj ncees kev khiav hauj lwm systems rau siv databases nyob rau hauv dav dav thiab PostgreSQL tshwj xeeb yog Linux. Vim tias FreeBSD, hmoov tsis, tab tom txhim kho qee qhov kev coj txawv txawv. Thiab yuav muaj teeb meem ob qho tib si nrog kev ua tau zoo thiab nrog ntau lwm yam. Qhov kev ua tau zoo ntawm PostgreSQL ntawm Windows feem ntau yog qhov teeb meem loj, raws li qhov tseeb tias Windows tsis muaj tib lub cim xeeb sib koom nrog UNIX, thaum PostgreSQL yog tag nrho khi rau qhov no, vim tias nws yog ntau txheej txheem.

Thiab kuv xav tias txhua tus neeg tsis tshua nyiam cov kab txawv xws li Solaris, yog li cia peb mus.

Kev faib tawm Linux niaj hnub muaj ntau dua 1 syctl kev xaiv, nyob ntawm seb koj tsim cov ntsiav. Nyob rau tib lub sijhawm, yog tias peb saib cov txiv ntoo sib txawv, peb tuaj yeem kho qee yam ntawm ntau txoj hauv kev. Muaj cov ntaub ntawv kaw lus tsis ua haujlwm li cas rau mount lawv. Yog tias koj muaj lus nug txog yuav pib nws li cas: yuav ua li cas rau hauv BIOS, yuav ua li cas rau kho vajtse, thiab lwm yam.

Qhov no yog qhov ntim loj heev uas tuaj yeem tham txog ntau hnub, thiab tsis yog hauv ib daim ntawv qhia luv luv, tab sis tam sim no kuv yuav tsom mus rau yam tseem ceeb, yuav ua li cas zam cov rakes uas tau lees paub los tiv thaiv koj los ntawm kev siv koj cov ntaub ntawv zoo ntawm Linux yog tias koj tsis kho lawv. Thiab nyob rau tib lub sijhawm, ib qho tseem ceeb yog tias ntau qhov tsis raug cai tsis suav nrog hauv cov chaw uas raug rau cov ntaub ntawv. Ntawd yog, los ntawm lub neej ntawd nws yuav ua haujlwm tsis zoo lossis tsis ua txhua yam.

Dab tsi yog lub hom phiaj tuning ib txwm muaj nyob hauv Linux? Kuv xav tias txij li thaum koj txhua tus ua haujlwm nrog Linux kev tswj hwm, tsis muaj qhov tshwj xeeb yuav tsum tau piav qhia txog lub hom phiaj yog dab tsi.

Koj tuaj yeem qhib:

• CPUs.
• Nco.
• Cia.
• Lwm yam. Peb mam li tham txog qhov no thaum kawg rau khoom noj txom ncauj. Txawm li cas los xij, piv txwv li, cov kev txwv xws li cov cai txuag hluav taws xob tuaj yeem cuam tshuam kev ua haujlwm hauv qhov tsis tuaj yeem xav txog thiab tsis yog txoj hauv kev zoo siab tshaj plaws.

Dab tsi yog qhov tshwj xeeb ntawm PostgreSQL thiab database feem ntau? Qhov teeb meem yog tias koj tsis tuaj yeem tweak ib tus neeg txiv ntoo thiab pom tias peb qhov kev ua tau zoo dua qub.

Yog, muaj xws li gadgets, tab sis ib tug database yog ib qho nyuaj. Nws cuam tshuam nrog tag nrho cov peev txheej uas tus neeg rau zaub mov muaj thiab nyiam sib cuam tshuam rau tag nrho. Yog tias koj saib Oracle cov lus pom zoo tam sim no txog kev siv tus tswv OS, nws yuav zoo li kev tso dag hais txog Mongolian cosmonaut - pub tus dev thiab tsis kov dab tsi. Cia peb muab lub database tag nrho cov kev pab, lub database nws tus kheej yuav txheeb txhua yam tawm.

Hauv paus ntsiab lus, rau qee qhov xwm txheej zoo ib yam nrog PostgreSQL. Qhov txawv yog tias cov ntaub ntawv tseem tsis tuaj yeem nqa tag nrho cov peev txheej rau nws tus kheej, piv txwv li qhov chaw ntawm Linux qib koj yuav tsum txheeb nws tag nrho koj tus kheej.

Lub tswv yim tseem ceeb tsis yog xaiv ib lub hom phiaj thiab pib kho nws, piv txwv li, nco, CPU lossis ib yam dab tsi zoo li ntawd, tab sis txhawm rau txheeb xyuas qhov ua haujlwm thiab sim txhim kho cov kev xa tawm kom ntau li ntau tau kom cov load uas zoo programmers tsim nws. rau peb, suav nrog peb cov neeg siv.

Ntawm no yog ib daim duab los piav seb nws yog dab tsi. Muaj Linux OS tsis thiab muaj kev sib koom nco thiab muaj PostgreSQL sib koom buffers. PostgreSQL, tsis zoo li Oracle, ua haujlwm ncaj qha tsuas yog los ntawm cov ntsiav tsis, piv txwv li, txhawm rau txhawm rau nplooj ntawv los ntawm disk kom nkag mus rau hauv nws lub cim xeeb sib koom, nws yuav tsum mus dhau ntawm cov ntsiav tsis thiab rov qab, qhov xwm txheej zoo ib yam.

Disks nyob hauv qhov system no. Kuv kos qhov no li disks. Qhov tseeb, tej zaum yuav muaj RAID maub los, thiab lwm yam.

Thiab qhov kev tawm tswv yim-tso tawm ib txoj kev lossis lwm qhov tshwm sim los ntawm qhov teeb meem no.

PostgreSQL yog ib qho database classic. Muaj ib nplooj ntawv sab hauv. Thiab tag nrho cov tswv yim thiab cov zis tshwm sim siv nplooj ntawv. Peb tab tom tsa cov blocks rau hauv kev nco nrog nplooj ntawv. Thiab yog tias tsis muaj dab tsi tshwm sim, peb tsuas yog nyeem lawv, tom qab ntawd maj mam lawv ploj ntawm qhov cache no, los ntawm cov sib koom tsis sib koom thiab xaus rov qab rau hauv disk.

Yog tias peb hloov qee qhov chaw, ces tag nrho nplooj ntawv raug cim tias qias neeg. Kuv cim lawv ntawm no xiav. Thiab qhov no txhais tau tias nplooj ntawv no yuav tsum tau synchronized nrog thaiv cia. Qhov ntawd yog, thaum peb ua kom qias neeg, peb tau nkag hauv WAL. Thiab nyob rau qee lub sijhawm zoo nyob rau lub sijhawm, ib qho tshwm sim hu ua checkpoint tuaj. Thiab cov ntaub ntawv raug kaw rau hauv lub cav no uas nws tau tuaj txog. Thiab qhov no txhais tau hais tias tag nrho cov nplooj ntawv qias neeg uas nyob ntawm no thaum lub sijhawm ntawd hauv cov kev sib koom ua ke no tau synchronized nrog cov ntaub ntawv khaws cia siv fsync los ntawm cov kernel tsis.

Vim li cas qhov no ua tiav? Yog tias peb poob qhov hluav taws xob, ces peb tsis tau txais qhov xwm txheej uas tag nrho cov ntaub ntawv ploj lawm. Kev nco tsis tu ncua, uas txhua tus tau hais rau peb txog, yog tam sim no nyob rau hauv kev tshawb xav database - qhov no yog lub neej yav tom ntej, uas peb, ntawm chav kawm, siv zog rau thiab peb nyiam, tab sis rau tam sim no lawv nyob hauv rho tawm 20 xyoo. Thiab, ntawm chav kawm, tag nrho cov no yuav tsum tau saib xyuas.

Thiab lub luag haujlwm ntawm kev ua kom muaj txiaj ntsig zoo tshaj plaws yog los kho kom zoo ntawm tag nrho cov theem no kom nws txav mus los sai sai. Lub cim xeeb sib koom yog qhov tseem ceeb ntawm nplooj ntawv cache. Hauv PostgreSQL peb xa cov lus nug xaiv lossis qee yam, nws tau muab cov ntaub ntawv no los ntawm disk. Lawv xaus rau hauv kev sib koom buffers. Raws li, rau qhov no kom ua haujlwm zoo dua, yuav tsum muaj ntau lub cim xeeb.

Txhawm rau kom tag nrho cov no ua haujlwm zoo thiab nrawm, koj yuav tsum teeb tsa lub operating system kom raug rau txhua theem. Thiab xaiv cov khoom siv sib npaug, vim tias yog tias koj muaj qhov tsis txaus ntseeg nyob rau hauv qee qhov chaw, koj tuaj yeem ua ntau lub cim xeeb, tab sis nws yuav tsis tau txais kev pabcuam ntawm qhov nrawm txaus.

Thiab cia peb mus dhau txhua qhov ntawm cov ntsiab lus no.

Txhawm rau ua kom cov nplooj ntawv no rov qab mus sai dua, koj yuav tsum ua tiav cov hauv qab no:

• Ua ntej, koj yuav tsum ua haujlwm zoo dua nrog kev nco.
• Qhov thib ob, qhov kev hloov pauv no thaum nplooj ntawv los ntawm kev nco mus rau disk yuav tsum ua haujlwm zoo dua.
• Thiab qhov thib peb, yuav tsum muaj cov disks zoo.

Yog tias koj muaj 512 GB ntawm RAM hauv cov neeg rau zaub mov thiab tag nrho nws xaus rau ntawm SATA hard drive yam tsis muaj cache, tag nrho cov ntaub ntawv server hloov mus rau hauv tsis yog lub taub dag, tab sis taub taub nrog SATA interface. Koj yuav khiav mus rau nws ncaj qha. Thiab tsis muaj dab tsi yuav cawm koj.

Hais txog thawj lub ntsiab lus nrog kev nco, muaj peb yam uas tuaj yeem ua rau lub neej nyuaj heev.

Thawj ntawm lawv yog NUMA. NUMA yog ib yam uas tau tsim los txhim kho kev ua haujlwm. Raws li kev ua haujlwm, ntau yam tuaj yeem ua kom zoo. Thiab nyob rau hauv nws daim ntawv tshiab tam sim no, nws tsis zoo heev rau cov ntawv thov xws li databases uas siv nplooj ntawv cache sib koom buffers.

Nyob rau hauv ib tug nutshell. Koj tuaj yeem qhia tau li cas yog tias muaj qee yam tsis raug nrog NUMA? Koj muaj qee yam tsis kaj siab khob, dheev qee qhov CPU yog overloaded. Tib lub sijhawm, koj txheeb xyuas cov lus nug hauv PostgreSQL thiab pom tias tsis muaj dab tsi zoo sib xws nyob ntawd. Cov lus nug no yuav tsum tsis txhob yog CPU hnyav heev. Koj tuaj yeem ntes tau qhov no ntev. Nws yooj yim dua los siv cov lus pom zoo los ntawm qhov pib ntawm yuav ua li cas teeb tsa NUMA rau PostgreSQL.

Yuav ua li cas tiag? NUMA sawv cev rau Non-Uniform Memory Access. Lub ntsiab lus yog dab tsi? Koj muaj CPU, ib sab ntawm nws muaj nws lub cim xeeb hauv zos. Thiab qhov kev sib txuas ntawm lub cim xeeb no tuaj yeem rub lub cim xeeb los ntawm lwm cov CPUs.

Yog koj khiav numactl --hardware, ces koj yuav tau txais xws li ib daim ntawv loj. Ntawm lwm yam, yuav muaj thaj chaw deb. Yuav muaj cov lej - 10-20, ib yam li ntawd. Cov lej no tsis muaj dab tsi ntau tshaj li tus naj npawb ntawm hops los khaws cov chaw taws teeb nco no thiab siv nws hauv zos. Hauv paus ntsiab lus, lub tswv yim zoo. Qhov no ua rau kev ua haujlwm tau zoo nyob rau hauv ntau yam ntawm kev ua haujlwm.

Tam sim no xav txog tias koj muaj ib lub CPU ua ntej sim siv nws lub cim xeeb hauv zos, tom qab ntawd sim rub lwm lub cim xeeb ntawm kev sib txuas rau qee yam. Thiab qhov CPU no tau txais koj tag nrho PostgreSQL nplooj ntawv cache - qhov ntawd yog nws, qee qhov gigabytes. Koj ib txwm tau txais qhov phem tshaj plaws, vim hais tias ntawm CPU muaj feem ntau me me nco hauv qhov module nws tus kheej. Thiab tag nrho cov cim xeeb uas tau txais kev pabcuam mus los ntawm cov kev sib tshuam no. Nws hloov tawm qeeb thiab tu siab. Thiab koj lub processor uas cov kev pabcuam node tau dhau los tas li. Thiab lub sijhawm nkag mus ntawm lub cim xeeb no tsis zoo, qeeb. Qhov no yog qhov xwm txheej uas koj tsis xav tau yog tias koj siv qhov no rau lub database.

Yog li ntawd, ib qho kev xaiv muaj tseeb dua rau cov ntaub ntawv yog rau Linux operating system tsis paub dab tsi tshwm sim nyob rau ntawd. Kom nws nkag tau lub cim xeeb raws li nws ua.

Yog vim li cas? Nws yuav zoo li tias nws yuav tsum yog lwm txoj hauv kev. Qhov no tshwm sim rau ib qho laj thawj yooj yim: peb xav tau ntau lub cim xeeb rau nplooj ntawv cache - kaum, pua pua gigabytes.

Thiab yog tias peb faib tag nrho cov no thiab cached peb cov ntaub ntawv nyob rau ntawd, ces qhov nce los ntawm kev siv lub cache yuav loj dua qhov nce los ntawm kev nkag mus rau lub cim xeeb. Thiab peb yuav tau txais txiaj ntsig zoo dua piv rau qhov tseeb tias peb yuav nkag mus rau lub cim xeeb zoo dua siv NUMA.

Yog li ntawd, muaj ob txoj hauv kev ntawm no tam sim no, kom txog rau thaum lub neej yav tom ntej tau los txog, thiab cov ntaub ntawv nws tus kheej tsis tuaj yeem txiav txim siab seb CPUs twg nws tab tom khiav thiab qhov twg nws yuav tsum rub ib yam dab tsi los ntawm.

Yog li, txoj hauv kev kom raug yog kom tsis txhob NUMA tag nrho, piv txwv li, thaum rebooting. Feem ntau, cov kev sib tw yeej yog ntawm qhov kev txiav txim siab loj uas cov lus nug uas zoo dua tsis tshwm sim txhua.

Muaj lwm txoj kev xaiv. Peb siv nws ntau zaus dua thawj zaug, vim tias thaum tus neeg siv khoom tuaj rau peb rau kev txhawb nqa, rebooting server yog qhov loj rau nws. Nws muaj kev lag luam nyob ntawd. Thiab lawv ntsib teeb meem vim yog NUMA. Yog li ntawd, peb sim lov tes taw nws nyob rau hauv tsawg invasive txoj kev tshaj li reboot, tab sis yuav tsum tau ceev faj xyuas tias nws yog neeg xiam. Vim tias, raws li kev paub dhau los, nws yog qhov zoo uas peb lov tes taw NUMA ntawm tus niam txiv PostgreSQL txheej txheem, tab sis nws tsis yog qhov tsim nyog uas nws yuav ua haujlwm. Peb yuav tsum tau xyuas thiab pom tias nws yeej hloov tawm.

Muaj ib tsab ntawv zoo los ntawm Robert Haas. Qhov no yog ib qho ntawm PostgreSQL committers. Ib qho ntawm cov neeg tsim khoom tseem ceeb ntawm txhua qib qis giblets. Thiab yog tias koj ua raws cov kev sib txuas los ntawm cov ntawv tshaj tawm no, lawv piav qhia ntau zaj dab neeg muaj yeeb yuj hais txog yuav ua li cas NUMA ua rau lub neej nyuaj rau tib neeg. Saib, kawm cov txheej txheem tswj xyuas cov npe ntawm qhov yuav tsum tau teeb tsa ntawm lub server kom peb cov ntaub ntawv ua haujlwm tau zoo. Cov kev teeb tsa no yuav tsum tau sau thiab tshuaj xyuas, vim tias tsis yog nws yuav tsis zoo heev.

Thov nco ntsoov tias qhov no siv tau rau txhua qhov chaw uas kuv yuav tham txog. Tab sis feem ntau databases tau sau nyob rau hauv master- qhev hom rau kev ua txhaum cai. Tsis txhob hnov ​​​​qab ua cov chaw no rau tus qhev vim muaj ib hnub koj yuav muaj kev sib tsoo thiab koj yuav hloov mus rau tus qhev thiab nws yuav dhau los ua tus tswv.

Thaum muaj xwm txheej ceev, thaum txhua yam phem heev, koj lub xov tooj tau nrov tas li thiab koj tus thawj coj los khiav nrog tus pas loj, koj yuav tsis muaj sijhawm los xav txog kev kuaj xyuas. Thiab cov txiaj ntsig tuaj yeem ua rau muaj kev puas tsuaj loj heev.

Cov ntsiab lus tom ntej yog nplooj ntawv loj. Cov nplooj ntawv loj loj yog qhov nyuaj rau kev sim cais, thiab tsis muaj qhov taw qhia ua li ntawd, txawm tias muaj cov qauv ntsuas uas tuaj yeem ua qhov no. Lawv yooj yim rau Google.

Lub ntsiab lus yog dab tsi? Koj muaj lub server tsis kim heev nrog ntau RAM, piv txwv li, ntau dua 30 GB. Koj tsis siv nplooj ntawv loj. Qhov no txhais tau hais tias koj yeej muaj nyiaj siv ua haujlwm ntawm kev siv nco. Thiab qhov nyiaj siv ua haujlwm no yog deb ntawm qhov zoo siab tshaj plaws.

Yog vim li cas? Yog li dab tsi tshwm sim? Lub operating system faib cov cim xeeb hauv cov khoom me me. Nws yooj yim heev, nws yog li cas nws tshwm sim keeb kwm. Thiab yog tias peb nkag mus rau hauv kev nthuav dav, OS yuav tsum txhais cov chaw nyob virtual rau hauv lub cev. Thiab cov txheej txheem no tsis yog qhov yooj yim tshaj plaws, yog li OS caches qhov tshwm sim ntawm kev ua haujlwm no hauv Kev Txhais Lus Lookaside Buffer (TLB).

Thiab txij li TLB yog ib lub cache, tag nrho cov teeb meem tshwm sim hauv lub cache tshwm sim hauv qhov xwm txheej no. Ua ntej, yog tias koj muaj ntau RAM thiab nws tag nrho faib rau hauv me me chunks, ces qhov no tsis yog loj heev. Thiab yog hais tias lub cache loj, ces nrhiav los ntawm nws yog qeeb qeeb. Nyiaj siv ua haujlwm noj qab haus huv thiab nws tus kheej siv qhov chaw, piv txwv li RAM tau noj los ntawm qee yam tsis raug. Lub sijhawm no.

Ob - ntau lub cache loj hlob hauv qhov xwm txheej zoo li no, qhov ntau dua nws yog tias koj yuav muaj cache nco. Thiab qhov ua tau zoo ntawm qhov cache no sai txo qis thaum nws loj tuaj. Yog li ntawd, kev khiav hauj lwm systems tuaj nrog ib tug yooj yim mus kom ze. Nws tau siv nyob rau hauv Linux tau ntev. Nws tshwm sim hauv FreeBSD tsis ntev dhau los. Tab sis peb tab tom tham txog Linux. Cov no yog nplooj ntawv loj.

Thiab ntawm no nws yuav tsum tau muab sau tseg tias cov nplooj ntawv loj, raws li lub tswv yim, tau pib thawb los ntawm cov zej zog uas suav nrog Oracle thiab IBM, piv txwv li cov tuam txhab database tau xav tias qhov no yuav muaj txiaj ntsig zoo rau cov ntaub ntawv thiab.

Thiab qhov no tuaj yeem ua phooj ywg nrog PostgreSQL li cas? Ua ntej, cov nplooj ntawv loj yuav tsum tau qhib rau hauv Linux ntsiav.

Qhov thib ob, lawv yuav tsum tau qhia meej meej los ntawm sysctl parameter - muaj pes tsawg tus. Cov lej ntawm no yog los ntawm qee tus neeg rau zaub mov qub. Koj tuaj yeem suav pes tsawg tus sib koom buffers koj muaj kom cov nplooj ntawv loj tuaj yeem haum rau ntawd.

Thiab yog tias koj tag nrho cov neeg rau zaub mov tau mob siab rau PostgreSQL, ces qhov pib zoo yog los faib 25% ntawm RAM rau kev sib koom buffers, lossis 75% yog tias koj paub tseeb tias koj cov ntaub ntawv yuav twv yuav raug haum rau 75%. Pib taw tes ib. Thiab xav txog, yog tias koj muaj 256 GB ntawm RAM, ces, raws li, koj yuav muaj 64 GB ntawm buffers loj. Xam kwv yees nrog qee cov npoo - daim duab no yuav tsum tau teeb tsa li cas.

Ua ntej version 9.2 (yog tias kuv tsis yuam kev, txij li version 8.2), nws tuaj yeem txuas PostgreSQL nrog cov nplooj ntawv loj uas siv lub tsev qiv ntawv thib peb. Thiab qhov no yuav tsum tau ua tas li. Ua ntej, koj xav tau cov ntsiav kom muaj peev xwm faib cov nplooj ntawv loj kom raug. Thiab, qhov thib ob, kom cov ntawv thov uas ua haujlwm nrog lawv tuaj yeem siv tau. Nws yuav tsis yog siv li ntawd xwb. Txij li thaum PostgreSQL faib lub cim xeeb hauv qhov system 5 style, qhov no tuaj yeem ua tiav siv libhugetlbfs - qhov no yog lub npe tag nrho ntawm lub tsev qiv ntawv.

Hauv 9.3, PostgreSQL kev ua tau zoo tau txhim kho thaum ua haujlwm nrog lub cim xeeb thiab lub kaw lus 5 nco txoj kev faib tawm raug tso tseg. Txhua leej txhua tus zoo siab heev, vim hais tias txwv tsis pub koj sim khiav ob lub sijhawm PostgreSQL ntawm ib lub tshuab, thiab nws hais tias kuv tsis muaj kev sib koom ua ke txaus. Thiab nws hais tias sysctl yuav tsum tau kho. Thiab muaj xws li ib tug sysctl uas koj tseem yuav tsum tau reboot, thiab lwm yam. Feem ntau, txhua leej txhua tus zoo siab. Tab sis mmap lub cim xeeb faib cuam tshuam kev siv nplooj ntawv loj. Feem ntau ntawm peb cov neeg siv khoom loj sib koom buffers. Thiab peb pom zoo kom tsis txhob hloov mus rau 9.3, vim hais tias cov nyiaj siv ua haujlwm tau pib suav nrog feem pua ​​​​zoo.

Tab sis cov zej zog tau mob siab rau qhov teeb meem no thiab hauv 9.4 lawv tau rov ua dua qhov xwm txheej no zoo heev. Thiab nyob rau hauv 9.4 ib parameter tshwm sim nyob rau hauv postgresql.conf nyob rau hauv uas koj muaj peev xwm pab tau sim, on los yog tawm.

Sim yog qhov kev xaiv nyab xeeb tshaj plaws. Thaum PostgreSQL pib, thaum nws faib cov cim xeeb sib koom, nws sim rub lub cim xeeb no los ntawm cov nplooj ntawv loj. Thiab yog tias nws tsis ua haujlwm, ces nws rov qab mus rau qhov kev xaiv ib txwm. Thiab yog tias koj muaj FreeBSD lossis Solaris, ces koj tuaj yeem sim sim, nws ib txwm muaj kev nyab xeeb.

Yog tias nyob rau, ces nws tsuas tsis pib yog tias nws tsis tuaj yeem xaiv los ntawm nplooj ntawv loj. Ntawm no nws twb hais txog leej twg thiab dab tsi zoo dua. Tab sis yog tias koj tau sim, ces xyuas tias koj yeej muaj qhov koj xav tau tseem ceeb, vim tias muaj ntau chav rau kev ua yuam kev. Tam sim no qhov kev ua haujlwm no tsuas yog ua haujlwm ntawm Linux.

Ib daim ntawv me me ua ntej peb mus ntxiv. Cov nplooj ntawv loj loj pob tshab tsis yog hais txog PostgreSQL tseem. Nws siv tsis tau lawv li ib txwm. Thiab nrog Transparent cov nplooj ntawv loj loj rau kev ua haujlwm zoo li no, thaum xav tau ib qho loj ntawm kev sib koom nco, cov txiaj ntsig tsuas yog tuaj nrog cov ntim loj heev. Yog tias koj muaj terabytes ntawm lub cim xeeb ces qhov no tuaj yeem ua si. Yog tias peb tab tom tham txog ntau daim ntawv thov niaj hnub, thaum koj muaj 32, 64, 128, 256 GB ntawm lub cim xeeb ntawm koj lub tshuab, ces cov nplooj ntawv loj li niaj zaus yog Ok, thiab peb tsuas yog lov tes taw pob tshab.

Thiab qhov kawg ntawm kev nco tsis yog ncaj qha ntsig txog fruitut, nws tuaj yeem ua rau koj lub neej puas. Tag nrho cov throughput yuav cuam tshuam zoo heev los ntawm qhov tseeb hais tias tus neeg rau zaub mov yog tas li swapping.

Thiab qhov no yuav tsis kaj siab heev nyob rau hauv ntau txoj kev. Thiab qhov teeb meem tseem ceeb yog cov kernels niaj hnub coj txawv me ntsis ntawm cov laus Linux kernels. Thiab qhov no yog qhov tsis txaus siab rau kev nqis tes ua, vim tias thaum peb tham txog qee yam kev ua haujlwm nrog kev sib pauv, nws xaus nrog lub sijhawm tuaj txog ntawm OOM-killer. Thiab OOM-killer, uas tsis tuaj txog raws sijhawm thiab poob PostgreSQL, tsis txaus siab. Txhua leej txhua tus yuav paub txog qhov no, uas yog, mus txog rau thaum tus neeg siv kawg.

Yuav ua li cas? Koj muaj ntau tus RAM nyob ntawd, txhua yam ua haujlwm zoo. Tab sis rau qee qhov laj thawj tus neeg rau zaub mov hangs hauv kev sib pauv thiab qeeb vim qhov no. Nws yuav zoo li tias muaj ntau lub cim xeeb, tab sis qhov no tshwm sim.

Yav dhau los, peb tau qhia txog kev teeb tsa vm.swappiness rau xoom, piv txwv li kev cuam tshuam kev sib pauv. Yav dhau los, nws zoo li tias 32 GB ntawm RAM thiab sib koom sib koom ua ke yog qhov loj heev. Lub hom phiaj tseem ceeb ntawm kev sib pauv yog kom muaj qhov chaw pov tseg yog tias peb poob. Thiab nws tsis tau ua tiav tshwj xeeb lawm. Thiab ces koj yuav ua li cas nrog lub crust? Qhov no yog ib txoj haujlwm uas nws tsis paub meej vim li cas kev sib pauv yuav tsum tau, tshwj xeeb tshaj yog qhov loj.

Tab sis nyob rau hauv ntau niaj hnub, piv txwv li, peb versions ntawm lub ntsiav, tus cwj pwm tau hloov. Thiab yog tias koj teem caij sib pauv mus rau xoom, piv txwv li tua nws, tom qab ntawd sai lossis tom qab, txawm tias muaj qee qhov RAM sab laug, tus neeg tua neeg OOM yuav tuaj rau koj los tua cov neeg siv khoom hnyav tshaj plaws. Vim tias nws yuav xav txog tias nrog cov haujlwm zoo li no peb tseem tshuav me ntsis thiab peb yuav dhia tawm, piv txwv li, tsis yog ntsia rau hauv cov txheej txheem txheej txheem, tab sis yuav ntsia ib yam dab tsi tsis tseem ceeb. Qhov tseem ceeb tsawg dua no yuav yog cov neeg siv khoom siv ntau ntawm kev sib koom nco, uas yog tus xa ntawv. Thiab tom qab ntawd nws yuav zoo yog tias lub hauv paus tsis tas yuav rov qab los.

Yog li ntawd, tam sim no lub neej ntawd, raws li kuv nco qab, feem ntau cov kev faib tawm yog qhov chaw nyob ib ncig ntawm 6, piv txwv li thaum twg koj yuav tsum pib siv swap nyob ntawm seb muaj pes tsawg lub cim xeeb. Peb tam sim no pom zoo kom teeb vm.swappiness = 1, vim hais tias qhov no xyaum nws tawm, tab sis tsis muab cov teebmeem tib yam li nrog ib tug OOM-killer uas npaj txhij txog tuaj txog thiab tua tag nrho.

Tom ntej no yog dab tsi? Thaum peb tham txog kev ua tau zoo ntawm cov ntaub ntawv thiab maj mam txav mus rau disks, txhua tus pib tuav lawv lub taub hau. Vim tias qhov tseeb tias disk qeeb thiab lub cim xeeb nrawm yog paub rau txhua tus txij thaum yau. Thiab txhua tus paub tias cov ntaub ntawv yuav muaj teeb meem kev ua haujlwm disk.

Qhov teeb meem tseem ceeb ntawm PostgreSQL kev ua tau zoo cuam tshuam nrog cov chaw kuaj xyuas tsis tshwm sim vim tias lub disk qeeb. Qhov no feem ntau yuav yog vim qhov tseeb tias nco thiab disk bandwidth tsis sib npaug. Txawm li cas los xij, lawv yuav tsis sib npaug hauv ntau qhov chaw. PostgreSQL tsis tau teeb tsa, OS tsis tau teeb tsa, kho vajtse tsis teeb tsa thiab kho vajtse tsis raug. Thiab qhov teeb meem no tsis tshwm sim tsuas yog txhua yam tshwm sim raws li nws yuav tsum, piv txwv li yog tsis muaj kev thauj khoom, lossis cov chaw thiab cov khoom siv tau raug xaiv zoo.

Nws yog dab tsi thiab nws zoo li cas? Feem ntau cov neeg ua haujlwm nrog PostgreSQL tau nkag mus rau qhov teeb meem no ntau dua ib zaug. Kuv mam piav. Raws li kuv tau hais, PostgreSQL ib ntus ua cov chaw kuaj xyuas kom pov tseg nplooj ntawv qias neeg hauv kev sib koom nco rau disk. Yog tias peb muaj ntau lub cim xeeb sib koom, ces qhov chaw kuaj xyuas pib muaj kev cuam tshuam loj heev ntawm disk, vim nws dumps cov nplooj ntawv nrog fsync. Nws tuaj txog hauv kernel tsis thiab sau rau disks siv fsync. Thiab yog tias qhov ntim ntawm qhov kev lag luam no loj, ces peb tuaj yeem soj ntsuam cov txiaj ntsig tsis zoo, uas yog siv cov disks loj heev.

Ntawm no kuv muaj ob daim duab. Tam sim no kuv yuav piav qhia tias nws yog dab tsi. Cov no yog ob lub sijhawm sib cuam tshuam cov duab. Thawj daim duab yog kev siv disk. Ntawm no nws mus txog yuav luag 90% ntawm lub sijhawm no. Yog hais tias koj muaj ib tug database tsis ua hauj lwm nrog lub cev disks, nrog ib tug RAID maub los siv ntawm 90%, ces qhov no yog xov xwm phem. Qhov no txhais tau tias me ntsis ntxiv thiab nws yuav ncav cuag 100 thiab I / O yuav nres.

Yog tias koj muaj disk array, ces nws yog ib zaj dab neeg txawv me ntsis. Nws nyob ntawm seb nws yog configured, hom array nws yog, thiab lwm yam.

Thiab nyob rau hauv parallel, ib daim duab los ntawm lub sab hauv postgres saib yog configured ntawm no, uas qhia yuav ua li cas lub checkpoint tshwm sim. Thiab cov xim ntsuab ntawm no qhia tau hais tias muaj pes tsawg buffers, cov nplooj ntawv qias neeg no, thaum lub sijhawm ntawd tuaj txog ntawm qhov chaw kuaj xyuas no rau synchronization. Thiab qhov no yog qhov tseem ceeb uas koj yuav tsum paub ntawm no. Peb pom tias peb muaj ntau nplooj ntawv ntawm no thiab nyob rau qee lub sijhawm peb ntaus lub rooj tsavxwm, uas yog, peb tau sau thiab sau, ntawm no lub kaw lus disk tau pom meej meej heev. Thiab peb qhov chaw kuaj xyuas muaj kev cuam tshuam loj heev rau lub disk. Qhov zoo tshaj plaws, qhov xwm txheej yuav tsum zoo li qhov no, piv txwv li peb tau kaw tsawg dua ntawm no. Thiab peb tuaj yeem kho nws nrog cov kev teeb tsa kom nws yuav ua tau zoo li no. Ntawd yog, kev rov ua dua tshiab yog me me, tab sis qhov chaw peb tab tom sau qee yam ntawm no.

Yuav tsum ua dab tsi los daws qhov teeb meem no? Yog tias koj tau tso tseg IO nyob rau hauv cov ntaub ntawv, qhov no txhais tau hais tias txhua tus neeg siv uas tuaj yeem ua tiav lawv qhov kev thov yuav tos.

Yog tias koj saib los ntawm qhov pom ntawm Linux, yog tias koj coj cov khoom siv zoo, teeb tsa nws kom raug, teeb tsa PostgreSQL ib txwm ua kom nws ua rau cov chaw kuaj xyuas no tsawg dua, nthuav tawm lawv dhau sijhawm ntawm ib leeg, tom qab ntawd koj nkag mus rau qhov tsis zoo Debian. Rau feem ntau Linux faib, qhov no yog daim duab: vm.dirty_ratio=20, vm.dirty_background_ratio=10.

Nws txhais li cas? Ib tus dab ntxwg nyoog tau tshwm sim los ntawm ntsiav 2.6. Pdglush, nyob ntawm seb leej twg siv qhov twg, uas yog koom nrog hauv keeb kwm yav dhau los pov tseg cov nplooj ntawv qias neeg los ntawm cov kernel tsis thiab muab pov tseg thaum tsim nyog yuav tsum muab cov nplooj ntawv qias neeg tsis muaj teeb meem dab tsi, thaum backgrouind pov tseg tsis pab.

Thaum twg keeb kwm tuaj? Thaum 10% ntawm tag nrho RAM muaj nyob rau ntawm cov neeg rau zaub mov yog nyob los ntawm cov nplooj ntawv qias neeg hauv cov ntsiav tsis, qhov tshwj xeeb sau tawm ua haujlwm hu ua tom qab. Vim li cas nws yog keeb kwm yav dhau? Raws li ib tug parameter, nws yuav siv sij hawm mus rau hauv tus account pes tsawg nplooj ntawv sau tawm. Thiab, cia peb hais, nws sau tawm N nplooj ntawv. Thiab ib pliag qhov no poob pw tsaug zog. Thiab ces nws los dua thiab luam ib co nplooj ntawv ntxiv.

Qhov no yog ib zaj dab neeg yooj yim heev. Qhov teeb meem ntawm no zoo li nrog lub pas dej da dej, thaum nws nchuav rau hauv ib lub yeeb nkab, nws ntws mus rau lwm qhov. Peb qhov chaw kuaj xyuas tuaj txog thiab yog tias nws xa ob peb nplooj ntawv qias neeg rau kev muab pov tseg, tom qab ntawd maj mam tag nrho cov khoom yuav raug kho kom zoo los ntawm cov ntsiav tsis pgflush.

Yog tias cov nplooj ntawv qias neeg no txuas ntxiv mus ntxiv, lawv tau nce mus txog 20%, tom qab ntawd OS qhov tseem ceeb yog sau tag nrho cov khoom mus rau disk, vim tias lub zog yuav poob thiab txhua yam yuav ua rau peb tsis zoo. Peb yuav plam cov ntaub ntawv no, piv txwv li.

Dab tsi yog dag? Qhov ua kom yuam kev yog tias cov tsis muaj nyob hauv lub ntiaj teb niaj hnub no yog 20 thiab 10% ntawm tag nrho RAM uas nyob hauv lub tshuab, lawv yog qhov tsis txaus ntseeg kiag li ntawm kev nkag mus ntawm txhua qhov system disk uas koj muaj.

Xav txog tias koj muaj 128 GB ntawm RAM. 12,8 GB tuaj txog hauv koj qhov system disk. Thiab tsis muaj teeb meem dab tsi cache koj muaj nyob rau ntawd, tsis muaj teeb meem dab tsi koj muaj nyob rau ntawd, lawv yuav tsis kav ntev li ntawd.

Yog li, peb xav kom koj tam sim ntawd kho cov lej no raws li lub peev xwm ntawm koj tus RAID maub los. Kuv tam sim ntawd tau pom zoo ntawm no rau tus maub los uas muaj 512 MB ntawm cache.

Txhua yam yog suav tias yog yooj yim heev. Koj tuaj yeem tso vm.dirty_background hauv bytes. Thiab cov teeb tsa no tso tseg ob qho dhau los. Ib qho piv txwv yog los ntawm lub neej ntawd, lossis cov uas muaj bytes tau qhib, ces cov uas muaj bytes yuav ua haujlwm. Tab sis txij li thaum kuv yog tus kws pab tswv yim DBA thiab ua haujlwm nrog cov neeg siv khoom sib txawv, kuv sim kos cov straws thiab yog li ntawd, yog tias hauv bytes, ces hauv bytes. Tsis muaj leej twg muab kev lees paub tias tus thawj tswj hwm zoo yuav tsis ntxiv lub cim xeeb ntxiv rau lub server, rov pib dua nws, thiab daim duab yuav nyob zoo li qub. Tsuas yog xam cov lej no kom txhua yam haum nrog rau qhov lav.

Yuav ua li cas yog tias koj tsis haum? Kuv tau sau tias txhua qhov dej ntws tawm tau zoo, tab sis qhov tseeb qhov no yog daim duab ntawm kev hais lus. Lub operating system muaj qhov teeb meem loj - nws muaj ntau cov nplooj ntawv qias neeg, yog li IO uas koj cov neeg siv khoom tsim tau raug nres zoo, piv txwv li daim ntawv thov tau tuaj xa cov lus nug sql mus rau cov ntaub ntawv, nws tseem tos. Ib qho kev tawm tswv yim / tawm rau nws yog qhov tseem ceeb tshaj plaws, vim tias cov ntaub ntawv khaws cia los ntawm qhov chaw kuaj xyuas. Thiab thaum twg nws yuav ua tiav nws tsis meej kiag li. Thiab thaum koj tau ua tiav tsis yog tom qab dej ntws tawm, nws txhais tau tias tag nrho koj IO tau nyob ntawm nws. Thiab kom txog thaum nws xaus, koj yuav tsis ua dab tsi.

Muaj ob lub ntsiab lus tseem ceeb ntawm no uas dhau ntawm qhov kev qhia no. Cov kev teeb tsa no yuav tsum phim cov chaw hauv postgresql.conf, piv txwv li cov chaw kuaj xyuas. Thiab koj lub kaw lus disk yuav tsum tau teeb tsa kom txaus. Yog tias koj muaj lub cache ntawm RAID, ces nws yuav tsum muaj lub roj teeb. Cov neeg yuav RAID nrog cache zoo yam tsis muaj roj teeb. Yog tias koj muaj SSDs hauv RAID, ces lawv yuav tsum yog cov servers, yuav tsum muaj cov capacitors nyob ntawd. Ntawm no yog cov ncauj lus kom ntxaws. Qhov txuas no muaj kuv daim ntawv qhia txog yuav ua li cas teeb tsa qhov kev ua haujlwm disk hauv PostgreSQL. Muaj tag nrho cov ntawv txheeb xyuas no.

Dab tsi ntxiv tuaj yeem ua rau lub neej nyuaj? Cov no yog ob qho kev txwv. Lawv yog cov tshiab. Los ntawm lub neej ntawd, lawv tuaj yeem suav nrog hauv cov ntawv thov sib txawv. Thiab lawv tuaj yeem ua rau lub neej nyuaj yog tias lawv qhib tsis raug.

Muaj ob yam tshiab. Lawv twb tshwm sim nyob rau hauv peb cores. Qhov no yog sched_migration_cost hauv nanoseconds thiab sched_autogroup_enabled, uas yog ib qho los ntawm lub neej ntawd.

Thiab lawv ua rau koj lub neej puas? Sched_migration_cost yog dab tsi? Ntawm Linux, lub sijhawm teem sijhawm tuaj yeem hloov pauv cov txheej txheem los ntawm ib qho CPU mus rau lwm qhov. Thiab rau PostgreSQL, uas ua tiav cov lus nug, tsiv mus rau lwm CPU tsis meej kiag li. Los ntawm kev ua haujlwm ntawm qhov pom, thaum koj hloov qhov rais ntawm openoffice thiab davhlau ya nyob twg, qhov no yuav zoo, tab sis rau lub database qhov no yog phem heev. Yog li ntawd, txoj cai tsim nyog yog los teeb tsa migration_cost rau qee tus nqi loj, tsawg kawg ntau txhiab nanoseconds.

Qhov no txhais li cas rau tus teem sijhawm? Nws yuav raug txiav txim siab tias lub sijhawm no cov txheej txheem tseem kub. Ntawd yog, yog tias koj muaj kev lag luam ua haujlwm ntev uas tau ua ib yam dab tsi ntev, tus teem sijhawm yuav nkag siab qhov no. Nws yuav xav tias kom txog thaum lub sijhawm no dhau mus, tsis tas yuav tsiv mus nyob qhov txheej txheem no. Yog hais tias tib lub sij hawm tus txheej txheem ua ib yam dab tsi, ces nws yuav tsis tsiv mus nyob qhov twg, nws yuav ntsiag to ua hauj lwm ntawm CPU uas tau faib rau nws. Thiab qhov tshwm sim yog zoo heev.

Qhov thib ob yog autogroup. Muaj ib lub tswv yim zoo rau cov haujlwm tshwj xeeb uas tsis cuam tshuam nrog cov ntaub ntawv niaj hnub - qhov no yog rau pab pawg txheej txheem los ntawm lub davhlau ya nyob twg virtual uas lawv tau pib. Qhov no yooj yim rau qee yam haujlwm. Hauv kev xyaum, PostgreSQL yog ntau txheej txheem txheej txheem nrog prefork uas khiav los ntawm ib lub davhlau ya nyob twg. Koj muaj tus kws sau ntawv xauv, chaw kuaj xyuas, thiab tag nrho koj cov neeg thov kev thov yuav raug muab tso rau hauv ib lub sijhawm teem sijhawm, ib CPU. Thiab lawv yuav tos muaj nyob rau hauv unison rau nws kom muaj kev ywj pheej, thiaj li yuav cuam tshuam nrog ib leeg thiab kom nws nyob ntev dua. Qhov no yog ib zaj dab neeg uas tsis tsim nyog kiag li nyob rau hauv cov ntaub ntawv ntawm xws li ib tug load thiab yog li ntawd nws yuav tsum tau muab tua.

Kuv tus npoj yaig Alexey Lesovsky tau sim nrog pgbench yooj yim, qhov uas nws tau nce migration_cost los ntawm qhov kev txiav txim ntawm qhov loj thiab tua autogroup. Qhov sib txawv ntawm cov khoom siv tsis zoo yog yuav luag 10%. Muaj kev sib tham ntawm postgres mailing list uas tib neeg muab cov txiaj ntsig ntawm kev hloov pauv zoo sib xws rau cov lus nug ceev cuam ​​tshuam 50%. Muaj ntau ntau zaj dab neeg zoo li no.

Thiab thaum kawg, txog txoj cai txuag hluav taws xob. Qhov zoo yog tias Linux tam sim no tuaj yeem siv rau ntawm lub laptop. Thiab nws yuav supposedly siv lub roj teeb zoo. Tab sis mam li nco dheev nws hloov tawm tias qhov no kuj tuaj yeem tshwm sim ntawm lub server.

Ntxiv mus, yog tias koj xauj servers los ntawm qee tus tswv tsev, ces cov "zoo" hosters tsis xav tias koj muaj kev ua tau zoo dua. Lawv txoj haujlwm yog los xyuas kom meej tias lawv cov hlau siv tau zoo li qhov ua tau. Yog li ntawd, los ntawm lub neej ntawd lawv tuaj yeem pab txhawb lub zog txuag lub laptop ntawm lub operating system.

Yog tias koj siv cov khoom no ntawm lub server nrog cov ntaub ntawv hauv qab hnyav, ces koj xaiv yog acpi_cpufreq + permormance. Txawm nrog ondemand yuav muaj teeb meem.

Intel_pstate yog tus tsav tsheb sib txawv me ntsis. Thiab tam sim no nyiam yog muab rau qhov no, raws li nws yog tom qab thiab ua haujlwm zoo dua.

Thiab, raws li, tus tswv xeev tsuas yog kev ua haujlwm xwb. Ondemand, powersave thiab txhua yam tsis yog hais txog koj.

Cov txiaj ntsig ntawm kev piav qhia PostgreSQL tuaj yeem sib txawv los ntawm ntau qhov kev txiav txim loj yog tias koj ua kom muaj zog txuag, vim tias qhov ua tau zoo CPU hauv koj cov ntaub ntawv yuav khiav hauv txoj kev tsis tuaj yeem ua tiav.

Cov khoom no tuaj yeem suav nrog los ntawm lub neej ntawd. Ua tib zoo saib seb lawv puas muab nws los ntawm lub neej ntawd. Qhov no tuaj yeem yog qhov teeb meem loj heev.

Thiab thaum kawg, kuv xav hais ua tsaug rau cov hais mav los ntawm peb pab pawg PosgreSQL-Consulting DBA, uas yog Max Boguk thiab Alexey Lesovsky, uas tab tom ua haujlwm hauv qhov teeb meem no txhua hnub. Thiab peb sim ua qhov zoo tshaj plaws uas peb tuaj yeem ua tau rau peb cov neeg siv khoom kom nws txhua tus ua haujlwm rau lawv. Nws zoo li nrog cov lus qhia kev nyab xeeb aviation. Txhua yam ntawm no yog sau rau hauv cov ntshav. Txhua ntawm cov txiv ntoo no pom nyob rau hauv cov txheej txheem ntawm qee yam teeb meem. Kuv zoo siab los qhia rau koj.

Cov nqe lus nug:

Ua tsaug! Yog hais tias, piv txwv li, ib lub tuam txhab xav kom txuag nyiaj thiab tso cov ntaub ntawv thiab daim ntawv thov logic ntawm ib tus neeg rau zaub mov, lossis yog tias lub tuam txhab ua raws li cov qauv tsim ntawm microservice architectures, uas PostgreSQL khiav hauv ib lub thawv. Dab tsi yog dag? Sysctl yuav cuam tshuam rau tag nrho cov ntsiav thoob ntiaj teb. Kuv tsis tau hnov ​​​​txog sysctls ua li cas virtualized kom lawv ua haujlwm sib cais ntawm lub thawv. Tsuas muaj ib pab pawg thiab tsuas muaj ib feem ntawm kev tswj nyob ntawd. Koj yuav ua li cas nrog qhov no? Los yog yog tias koj xav tau kev ua tau zoo, ces khiav PostgreSQL ntawm ib qho khoom siv sib cais thiab kho nws?

Peb teb koj cov lus nug txog peb txoj kev. Yog tias peb tsis tham txog cov khoom siv kho vajtse uas tuaj yeem hloov kho, thiab lwm yam, tom qab ntawd so, txhua yam yuav ua haujlwm zoo yam tsis muaj qhov teeb tsa no. Yog hais tias koj muaj xws li ib tug load uas koj yuav tsum tau ua cov chaw, ces koj yuav tuaj rau lub hlau server ua ntej tshaj rau cov chaw.

Qhov teeb meem yog dab tsi? Yog tias qhov no yog lub tshuab virtual, ces feem ntau koj yuav muaj ntau yam teeb meem, piv txwv li, nrog rau qhov tseeb tias ntawm cov tshuab virtual feem ntau lub latency ntawm disk yog qhov tsis sib xws. Txawm hais tias disk throughput yog qhov zoo, ces ib qho kev ua tsis tiav I / O kev lag luam uas tsis cuam tshuam rau qhov nruab nrab ntawm qhov kev xa tawm uas tau tshwm sim thaum lub sijhawm kuaj xyuas lossis thaum lub sijhawm sau ntawv rau WAL, tom qab ntawd cov ntaub ntawv yuav raug kev txom nyem los ntawm qhov no. Thiab koj yuav pom qhov no ua ntej koj khiav mus rau cov teeb meem no.

Yog tias koj muaj NGINX ntawm tib lub server, koj kuj yuav muaj teeb meem tib yam. Nws yuav tawm tsam kom sib koom nco. Thiab koj yuav tsis tau txais cov teeb meem tau piav qhia ntawm no.

Tab sis ntawm qhov tod tes, qee qhov kev txwv no tseem yuav cuam tshuam rau koj. Piv txwv li, teem qias neeg_ratio nrog sysctl kom nws tsis yog vwm - txawm li cas los xij, qhov no yuav pab tau. Ib txoj kev lossis lwm qhov, koj yuav muaj kev cuam tshuam nrog lub disk. Thiab nws yuav ua raws li tus qauv tsis raug. Qhov no feem ntau yog lub neej ntawd rau cov kev txwv uas kuv tau pom. Thiab nyob rau hauv txhua rooj plaub nws yog qhov zoo dua los hloov lawv.

Tab sis tej zaum yuav muaj teeb meem nrog NUMA. VmWare, piv txwv li, ua haujlwm zoo nrog NUMA nrog raws nraim qhov sib txawv. Thiab ntawm no koj yuav tsum xaiv - tus neeg rau zaub mov hlau lossis tsis yog hlau.

Kuv muaj lus nug ntsig txog Amazon AWS. Lawv muaj cov duab pre-configured. Ib tug ntawm lawv yog hu ua Amazon RDS. Puas muaj ib qho kev cai rau lawv qhov kev khiav hauj lwm?

Muaj cov chaw nyob ntawd, tab sis lawv yog cov chaw sib txawv. Ntawm no peb configure lub operating system nyob rau hauv cov nqe lus ntawm yuav ua li cas lub database yuav siv cov khoom no. Thiab muaj cov kev txwv uas txiav txim siab qhov twg peb yuav tsum mus tam sim no, xws li shaping. Qhov ntawd yog, peb xav tau ntau yam kev pab, tam sim no peb yuav noj lawv. Tom qab ntawd, Amazon RDS nruj cov peev txheej no, thiab kev ua haujlwm poob rau ntawd. Muaj cov dab neeg ntawm tus kheej uas tib neeg tab tom pib ntxhov nrog qhov teeb meem no. Qee lub sij hawm txawm zoo heev. Tab sis qhov no tsis muaj dab tsi ua nrog OS nqis. Nws zoo li hacking huab. Qhov ntawd yog ib zaj dab neeg txawv.

Vim li cas cov nplooj ntawv loj loj tsis muaj txiaj ntsig piv rau TLB Loj?

Tsis txhob muab. Qhov no tuaj yeem piav qhia ntau yam. Tab sis qhov tseeb lawv tsuas tsis muab nws. Dab tsi yog keeb kwm ntawm PostgreSQL? Thaum pib, nws faib ib qho loj ntawm kev sib koom nco. Txawm lawv yog pob tshab los yog tsis yog kiag li tsis muaj feem xyuam. Qhov tseeb tias lawv sawv ntawm qhov pib piav qhia txhua yam. Thiab yog tias muaj ntau lub cim xeeb thiab koj yuav tsum rov tsim kho qhov sib koom_memory ntu, ces Pob tshab nplooj ntawv loj yuav cuam tshuam. Hauv PostgreSQL, nws tsuas yog faib rau hauv qhov loj loj thaum pib thiab qhov ntawd yog nws, thiab tom qab ntawd tsis muaj dab tsi tshwj xeeb tshwm sim nyob ntawd. Koj tuaj yeem, ntawm chav kawm, siv nws, tab sis muaj txoj hauv kev tau txais kev noj nyiaj txiag ntawm shared_memory thaum nws rov faib qee yam. PostgreSQL tsis paub txog qhov no.

Tau qhov twg los: www.hab.com