Qhov yuam kev hauv cov ntawv thov uas ua rau tsam plab hauv postgresql. Andrey Salnikov

Kuv xav kom koj nyeem cov ntawv sau tseg ntawm tsab ntawv ceeb toom txij thaum pib ntawm 2016 los ntawm Andrey Salnikov "Cov kev ua yuam kev hauv cov ntawv thov uas ua rau tsam plab hauv postgresql"

Hauv tsab ntawv ceeb toom no, kuv yuav txheeb xyuas qhov yuam kev tseem ceeb hauv cov ntawv thov uas tshwm sim nyob rau theem ntawm kev tsim thiab sau daim ntawv thov code. Thiab kuv yuav coj tsuas yog cov yuam kev uas ua rau tsam plab hauv Postgresql. Raws li txoj cai, qhov no yog qhov pib ntawm qhov kawg ntawm kev ua haujlwm ntawm koj lub cev tag nrho, txawm tias thaum xub thawj tsis muaj qhov yuav tsum tau ua ua ntej rau qhov no tau pom.

Zoo siab txais tos sawv daws! Daim ntawv tshaj tawm no tsis yog kev tshaj lij li yav dhau los los ntawm kuv cov npoj yaig. Daim ntawv tshaj tawm no feem ntau yog tsom rau cov neeg tsim khoom lag luam backend vim tias peb muaj ntau tus neeg siv khoom. Thiab lawv txhua tus ua yuam kev tib yam. Kuv mam qhia koj txog lawv. Kuv yuav piav qhia txog yam uas ua rau neeg tuag taus thiab tej yam phem no ua rau yuam kev.

Vim li cas thiaj ua yuam kev? Lawv tau ua tiav rau ob qho laj thawj: ntawm random, tej zaum nws yuav ua haujlwm tawm thiab vim tsis paub txog qee cov txheej txheem uas tshwm sim ntawm qib ntawm cov ntaub ntawv thiab daim ntawv thov, nrog rau hauv cov ntaub ntawv nws tus kheej.

Kuv mam li muab peb piv txwv rau koj nrog cov duab txaus ntshai ntawm qhov phem npaum li cas. Kuv yuav qhia luv luv rau koj txog cov txheej txheem uas tshwm sim muaj. Thiab yuav ua li cas nrog lawv, thaum lawv tshwm sim, thiab kev tiv thaiv dab tsi los siv los tiv thaiv kev ua yuam kev. Kuv yuav qhia koj txog cov cuab yeej pabcuam thiab muab cov kev sib txuas muaj txiaj ntsig.

Kuv siv cov ntaub ntawv xeem uas kuv muaj ob lub rooj. Ib lub phaj nrog cov neeg siv nyiaj, lwm qhov nrog kev hloov pauv ntawm cov nyiaj no. Thiab nrog qee zaus peb hloov kho cov nyiaj tshuav ntawm cov nyiaj no.

Cov ntaub ntawv pib ntawm lub phaj: nws yog me me, 2 MB. Lub sij hawm teb rau cov ntaub ntawv thiab tshwj xeeb rau kev kos npe kuj zoo heev. Thiab ib qho kev thauj khoom zoo - 2 kev ua haujlwm ib ob raws li lub phaj.

Thiab dhau ntawm daim ntawv tshaj tawm no kuv yuav qhia koj cov duab kom koj thiaj li nkag siab meej txog qhov tshwm sim. Tej zaum yuav muaj 2 slides nrog graphs. Thawj swb yog dab tsi tshwm sim nyob rau hauv dav dav ntawm lub server.

Thiab nyob rau hauv qhov xwm txheej no, peb pom tias peb yeej muaj lub cim me me. Qhov ntsuas me me ntawm 2 MB. Nov yog thawj daim duab ntawm sab laug.

Lub sijhawm teb nruab nrab ntawm lub server tseem ruaj khov thiab luv luv. Nov yog daim duab saum toj kawg nkaus.

Daim duab hauv qab sab laug qhia tau tias qhov kev hloov pauv ntev tshaj plaws. Peb pom tias kev hloov pauv sai sai. Thiab autovacuum tseem tsis tau ua haujlwm ntawm no, vim nws yog qhov pib sim. Nws yuav ua haujlwm ntxiv thiab yuav muaj txiaj ntsig rau peb.

Qhov thib ob swb yuav ib txwm muaj nplooj siab rau lub phaj uas raug kuaj. Hauv qhov xwm txheej no, peb niaj hnub hloov kho tus neeg siv khoom tus account tshuav nyiaj li cas. Thiab peb pom tias lub sijhawm teb nruab nrab rau kev ua haujlwm hloov tshiab yog qhov zoo heev, tsawg dua li millisecond. Peb pom tias cov khoom siv processor (qhov no yog cov duab sab saud) kuj tau noj sib npaug thiab me me.

Daim duab sab xis qhia tau hais tias ntau npaum li cas kev khiav hauj lwm thiab disk nco peb mus los ntawm kev tshawb fawb ntawm peb cov kab uas xav tau ua ntej hloov kho nws. Thiab tus naj npawb ntawm kev ua haujlwm raws li kev kos npe yog 2 ib ob, raws li kuv tau hais thaum pib.

Thiab tam sim no peb muaj ib qho xwm txheej. Rau qee yam yog vim li cas thiaj li tsis nco qab ntev. Cov laj thawj feem ntau yog tag nrho cov banal:

• Ib qho ntawm feem ntau yog tias peb pib nkag mus rau qhov kev pabcuam sab nraud hauv daim ntawv thov code. Thiab qhov kev pabcuam no tsis teb peb. Ntawd yog, peb qhib kev lag luam, hloov pauv hauv cov ntaub ntawv thiab tau mus los ntawm daim ntawv thov nyeem ntawv lossis mus rau lwm qhov kev pabcuam hauv peb cov txheej txheem, thiab rau qee yam nws tsis teb rau peb. Thiab peb qhov kev sib tham tau daig hauv lub xeev uas nws tsis paub thaum twg nws yuav raug daws.
• Qhov xwm txheej thib ob yog thaum muaj kev zam tshwm sim hauv peb cov cai rau qee qhov laj thawj. Thiab nyob rau hauv qhov kev zam peb tsis tau ua qhov kev kaw ntawm qhov kev sib pauv. Thiab peb tau xaus nrog kev sib tham dai nrog kev qhib kev lag luam.
• Thiab qhov kawg kuj yog ib qho teeb meem tshwm sim. Qhov no yog tus lej tsis zoo. Qee lub moj khaum qhib kev lag luam. Nws dai, thiab tej zaum koj yuav tsis paub hauv daim ntawv thov uas koj muaj nws dai.

Tej yam zoo li no coj mus qhov twg?

Txog rau qhov uas peb cov ntxhuav thiab qhov ntsuas tau pib swell heev. Qhov no yog tib yam bloat nyhuv. Rau cov ntaub ntawv, qhov no yuav txhais tau tias lub sijhawm teb cov ntaub ntawv yuav nce siab heev thiab cov khoom thauj ntawm cov neeg rau zaub mov database yuav nce. Thiab yog li ntawd, peb daim ntawv thov yuav raug kev txom nyem. Vim tias yog tias koj siv 10 milliseconds hauv koj cov lej ntawm kev thov rau cov ntaub ntawv, 10 milliseconds ntawm koj cov logic, ces koj txoj haujlwm siv 20 milliseconds kom tiav. Thiab tam sim no koj qhov xwm txheej yuav tu siab heev.

Thiab cia saib seb yuav ua li cas. Daim duab hauv qab sab laug qhia tau hais tias peb muaj kev lag luam ntev ntev. Thiab yog tias peb saib ntawm sab laug sab sauv, peb pom tias qhov loj ntawm peb lub rooj tau dhia ntawm ob megabytes mus rau 300 megabytes. Nyob rau tib lub sijhawm, tus nqi ntawm cov ntaub ntawv hauv lub rooj tsis tau hloov pauv, piv txwv li, muaj cov khib nyiab loj heev.

Qhov xwm txheej dav dav hais txog lub sijhawm nruab nrab ntawm cov neeg rau zaub mov tau hloov pauv los ntawm ntau qhov kev txiav txim ntawm qhov loj. Ntawd yog, txhua qhov kev thov ntawm lub server pib poob tag. Thiab tib lub sijhawm, cov txheej txheem hauv Postgres tau tsim tawm hauv daim ntawv ntawm autovacuum, uas tab tom sim ua qee yam thiab siv cov peev txheej.

Yuav ua li cas rau peb kos npe? Tib yam. Peb lub sij hawm teb nruab nrab raws li qhov kos npe tau dhia ntau qhov kev txiav txim loj. Tshwj xeeb tshaj yog nyob rau hauv cov nqe lus ntawm kev siv cov peev txheej, peb pom tias cov khoom thauj ntawm lub processor tau nce ntau heev. Nov yog daim duab saum toj kawg nkaus. Thiab nws tau nce vim tias lub processor yuav tsum txheeb los ntawm ib pawg ntawm cov kab tsis muaj txiaj ntsig hauv kev tshawb fawb ntawm qhov xav tau. Nov yog daim duab hauv qab no. Thiab yog li ntawd, peb cov xov tooj ntawm kev hu xov tooj ib ob pib poob qis heev, vim tias cov ntaub ntawv tsis muaj sijhawm los ua cov ntawv thov tib yam.

Peb yuav tau rov tau txoj sia. Peb mus online thiab nrhiav pom tias kev lag luam ntev ua rau muaj teeb meem. Peb nrhiav thiab tua qhov kev pauv no. Thiab txhua yam yog qhov qub rau peb. Txhua yam ua haujlwm raws li nws yuav tsum tau ua.

Peb calmed, tab sis tom qab ib pliag peb pib pom tias daim ntawv thov tsis ua hauj lwm ib yam li ua ntej thaum muaj xwm ceev. Kev thov tseem ua tau qeeb qeeb, thiab qeeb qeeb. Ib thiab ib nrab mus rau ob zaug qeeb qeeb tshwj xeeb hauv kuv qhov piv txwv. Lub load ntawm tus neeg rau zaub mov kuj siab dua li nws tau ua ntej qhov kev huam yuaj.

Thiab lo lus nug: "Dab tsi tshwm sim rau lub hauv paus tam sim no?" Thiab cov xwm txheej hauv qab no tshwm sim nrog lub hauv paus. Ntawm daim ntawv qhia kev lag luam koj tuaj yeem pom tias nws tau nres thiab tsis muaj kev lag luam mus sij hawm ntev. Tab sis qhov luaj li cas ntawm cov paib tuag tau nce thaum lub sij hawm sib tsoo. Thiab txij thaum ntawd los lawv tsis tau txo qis. Lub sijhawm nruab nrab ntawm lub hauv paus tau ruaj khov. Thiab cov lus teb zoo li yuav los txaus ntawm qhov ceev uas tau txais rau peb. Lub autovacuum tau ua haujlwm ntau dua thiab pib ua ib yam dab tsi nrog lub cim, vim nws yuav tsum tau sift los ntawm ntau cov ntaub ntawv.

Tshwj xeeb, raws li daim ntawv xeem nrog cov nyiaj, qhov twg peb hloov pauv: lub sij hawm teb rau qhov kev thov zoo li tau rov qab mus rau qhov qub. Tab sis nyob rau hauv kev muaj tiag nws yog ib thiab ib nrab sij hawm siab dua.

Thiab los ntawm kev thauj khoom ntawm lub processor, peb pom tias cov khoom thauj ntawm lub processor tsis tau rov qab mus rau qhov yuav tsum tau muaj ua ntej kev sib tsoo. Thiab yog vim li cas muaj nyob rau hauv cov duab sab xis. Nws tuaj yeem pom tias qee qhov kev nco tau raug tshawb nrhiav nyob ntawd. Ntawd yog, txhawm rau nrhiav cov kab uas yuav tsum tau ua, peb pov tseg cov peev txheej ntawm cov ntaub ntawv server thaum txheeb xyuas cov ntaub ntawv tsis muaj txiaj ntsig. Tus naj npawb ntawm kev hloov pauv ib ob tau ruaj khov.

Zuag qhia tag nrho zoo, tab sis qhov xwm txheej yog qhov phem tshaj qhov nws tau ua. Clear database degradation raws li qhov tshwm sim ntawm peb daim ntawv thov uas ua haujlwm nrog cov ntaub ntawv no.

Thiab kom nkag siab tias muaj dab tsi tshwm sim nyob rau ntawd, yog tias koj tsis nyob ntawm daim ntawv tshaj tawm dhau los, tam sim no cia peb tau txais me ntsis kev xav. Kev xav txog cov txheej txheem sab hauv. Vim li cas lub tshuab nqus tsev vacuum thiab nws ua li cas?

Lus luv luv kom nkag siab. Qee lub sij hawm peb muaj lub rooj. Peb muaj kab hauv lub rooj. Cov kab no tuaj yeem ua haujlwm, muaj sia nyob, thiab qhov peb xav tau tam sim no. Lawv raug cim ntsuab hauv daim duab. Thiab muaj cov kab tuag uas twb tau ua tiav lawm, tau hloov kho, thiab cov ntawv nkag tshiab tau tshwm sim rau lawv. Thiab lawv tau cim tias lawv tsis txaus siab rau lub database. Tab sis lawv nyob hauv lub rooj vim muaj Postgres feature.

Vim li cas koj thiaj xav tau lub tshuab nqus tsev? Qee lub sij hawm, lub autovacuum tuaj, nkag mus rau hauv cov ntaub ntawv thiab nug nws: "Thov muab kuv tus ID ntawm cov kev lag luam qub uas tam sim no qhib rau hauv cov ntaub ntawv." Lub database rov qab no id. Thiab lub autovacuum, tso siab rau nws, xaiv los ntawm cov kab hauv lub rooj. Thiab yog tias nws pom tias qee cov kab tau hloov pauv los ntawm ntau cov kev lag luam qub, ces nws muaj txoj cai los cim lawv li kab uas peb tuaj yeem rov siv dua yav tom ntej los ntawm kev sau cov ntaub ntawv tshiab rau ntawd. Qhov no yog txheej txheem keeb kwm yav dhau.

Lub sijhawm no, peb txuas ntxiv ua haujlwm nrog cov ntaub ntawv thiab txuas ntxiv ua qee qhov hloov pauv rau lub rooj. Thiab ntawm cov kab no, uas peb tuaj yeem rov siv tau, peb sau cov ntaub ntawv tshiab. Thiab yog li peb tau txais lub voj voog, piv txwv li txhua lub sijhawm qee cov kab qub tuag tshwm nyob rau ntawd, tsis yog lawv peb sau cov kab tshiab uas peb xav tau. Thiab qhov no yog lub xeev ib txwm rau PostgreSQL ua haujlwm.

Yuav ua li cas tshwm sim thaum muaj xwm txheej? Cov txheej txheem no tshwm sim li cas nyob rau ntawd?

Peb tau kos npe rau qee yam mob, qee qhov nyob, qee kab tuag. Lub tshuab nqus tsev tau tuaj txog. Nws nug cov ntaub ntawv seb peb qhov kev lag luam qub tshaj plaws yog dab tsi thiab nws tus lej yog dab tsi. Kuv tau txais daim ID no, uas tuaj yeem yog ntau teev dhau los, tej zaum kaum feeb dhau los. Nws nyob ntawm seb qhov hnyav npaum li cas koj muaj ntawm koj cov ntaub ntawv. Thiab nws tau mus nrhiav cov kab uas nws tuaj yeem khij tau rov siv dua. Thiab kuv tsis pom cov kab zoo li no hauv peb lub rooj.

Tab sis lub sij hawm no peb tseem ua hauj lwm nrog lub rooj. Peb ua qee yam hauv nws, hloov kho nws, hloov cov ntaub ntawv. Lub database yuav tsum ua li cas rau lub sijhawm no? Nws tsis muaj kev xaiv tab sis ntxiv cov kab tshiab rau qhov kawg ntawm lub rooj uas twb muaj lawm. Thiab yog li peb lub rooj loj pib o.

Qhov tseeb, peb xav tau cov kab ntsuab ua haujlwm. Tab sis thaum muaj teeb meem zoo li no, nws hloov tawm tias feem pua ​​​​ntawm cov kab ntsuab yog qhov tsawg kawg nkaus thoob plaws lub rooj.

Thiab thaum peb ua tiav cov lus nug, cov ntaub ntawv yuav tsum mus dhau txhua kab: ob qho tib si liab thiab ntsuab, kom pom cov kab uas xav tau. Thiab cov nyhuv ntawm tsam plab lub rooj nrog cov ntaub ntawv tsis muaj txiaj ntsig yog hu ua "bloat", uas tseem noj peb qhov chaw disk. Nco ntsoov, nws yog 2 MB, nws tau los ua 300 MB? Tam sim no hloov megabytes rau gigabytes thiab koj yuav poob tag nrho koj cov peev txheej disk sai sai.

Yuav muaj txiaj ntsig dab tsi rau peb?

• Hauv kuv qhov piv txwv, lub rooj thiab qhov ntsuas tau nce 150 npaug. Qee tus ntawm peb cov neeg siv khoom tau muaj mob hnyav dua thaum lawv tsuas pib khiav tawm ntawm qhov chaw disk.
• Qhov loj ntawm cov ntxhuav nws tus kheej yuav tsis txo qis. Autovacuum hauv qee kis tuaj yeem txiav tawm tus Tsov tus tw ntawm lub rooj yog tias tsuas muaj kab tuag. Tab sis txij li thaum muaj kev sib hloov tas mus li, ib kab ntsuab yuav khov thaum kawg thiab tsis hloov kho, thaum tag nrho lwm tus yuav raug sau cia rau qhov chaw pib ntawm lub phaj. Tab sis qhov no yog qhov tshwm sim tsis zoo uas koj lub rooj nws tus kheej yuav txo qis, yog li koj yuav tsum tsis txhob cia siab rau nws.
• Lub database yuav tsum txheeb los ntawm ib pawg ntawm cov kab tsis muaj txiaj ntsig. Thiab peb pov tseg cov peev txheej disk, peb pov tseg cov peev txheej processor thiab hluav taws xob.
• Thiab qhov no cuam tshuam ncaj qha rau peb daim ntawv thov, vim yog thaum pib peb siv 10 milliseconds ntawm qhov kev thov, 10 milliseconds ntawm peb cov lej, tom qab ntawd thaum lub sijhawm sib tsoo peb pib siv sijhawm thib ob ntawm qhov kev thov thiab 10 milliseconds ntawm tus lej, piv txwv li kev txiav txim ntawm magnitude hauv kev ua haujlwm tau txo qis. Thiab thaum qhov xwm txheej raug daws, peb pib siv 20 milliseconds ntawm kev thov, 10 milliseconds ntawm tus lej. Qhov no txhais tau hais tias peb tseem poob los ntawm ib thiab ib nrab zaug hauv kev tsim khoom. Thiab qhov no yog tag nrho vim yog ib qho kev lag luam uas khov, tej zaum los ntawm peb qhov txhaum.
• Thiab lo lus nug: "Yuav ua li cas peb thiaj li tau txais txhua yam rov qab?" kom txhua yam zoo nrog peb thiab kev thov tuaj sai li sai tau ua ntej kev sib tsoo.

Rau lub hom phiaj no muaj qee lub voj voog ntawm kev ua haujlwm uas tau ua.

Ua ntej peb yuav tsum nrhiav cov rooj teeb meem uas yog bloated. Peb nkag siab tias hauv qee lub rooj cov ntaub ntawv kaw ntau dua, hauv lwm qhov tsis tshua muaj zog. Thiab rau qhov no peb siv qhov txuas ntxiv pgst ua. Los ntawm kev txhim kho qhov txuas ntxiv no, koj tuaj yeem sau cov lus nug uas yuav pab koj nrhiav cov ntxhuav uas muaj qhov khaus heev.

Thaum koj tau pom cov ntxhuav no, koj yuav tsum tau ntim lawv. Tseem muaj cov cuab yeej rau qhov no. Hauv peb lub tuam txhab peb siv peb cov cuab yeej. Thawj yog lub VACUUM FULL built-in. Nws yog tus lim hiam, hnyav thiab tsis muaj kev hlub tshua, tab sis qee zaum nws muaj txiaj ntsig zoo. Pg_repack и pgcompactable - Cov no yog cov khoom siv thib peb rau compressing rooj. Thiab lawv kho cov ntaub ntawv kom zoo dua.

Lawv siv nyob ntawm seb qhov twg yooj yim dua rau koj. Tab sis kuv mam li qhia koj txog qhov no thaum kawg. Qhov tseem ceeb yog tias muaj peb yam cuab yeej. Muaj ntau yam xaiv los ntawm.

Tom qab peb tau kho txhua yam thiab ua kom txhua yam zoo, peb yuav tsum paub yuav ua li cas tiv thaiv qhov xwm txheej no yav tom ntej:

• Nws tuaj yeem tiv thaiv tau yooj yim heev. Koj yuav tsum tau saib xyuas lub sijhawm ntawm kev sib ntsib ntawm Master server. Tshwj xeeb tshaj yog txaus ntshai zaug nyob rau hauv idle nyob rau hauv lub xeev pauv. Cov no yog cov uas nyuam qhuav qhib kev lag luam, ua ib yam dab tsi thiab sab laug, lossis yooj yim dai, tau ploj hauv cov lej.
• Thiab rau koj, raws li cov neeg tsim khoom, nws yog ib qho tseem ceeb uas yuav tau sim koj cov cai thaum cov xwm txheej no tshwm sim. Nws tsis nyuaj ua. Qhov no yuav yog ib qho kev kuaj xyuas muaj txiaj ntsig. Koj yuav zam tau ntau yam "me nyuam yaus" teeb meem cuam tshuam nrog kev lag luam ntev.

Hauv cov duab no, kuv xav qhia koj tias qhov kos npe thiab tus cwj pwm ntawm cov ntaub ntawv hloov pauv li cas tom qab kuv tau mus dhau ntawm lub cim nrog VACUUM FULL hauv qhov no. Qhov no tsis yog kev tsim khoom rau kuv.

Lub rooj loj tam sim ntawd rov qab mus rau nws lub xeev kev ua haujlwm ntawm ob peb megabytes. Qhov no tsis cuam tshuam rau lub sijhawm teb nruab nrab rau lub server.

Tab sis tshwj xeeb tshaj yog rau peb qhov kev sim kos npe, qhov uas peb hloov kho cov nyiaj tshuav, peb pom tias lub sijhawm teb nruab nrab ntawm qhov kev thov hloov kho cov ntaub ntawv hauv kos npe tau txo qis rau theem ua ntej thaum muaj xwm txheej ceev. Cov peev txheej siv los ntawm processor los ua kom tiav qhov kev thov no kuj poob rau qib ua ntej kev sib tsoo. Thiab daim duab sab xis qhia tau hais tias tam sim no peb pom raws nraim txoj kab uas peb xav tau tam sim ntawd, tsis tas yuav hla cov kab ntawm cov kab tuag uas nyob ntawd ua ntej lub rooj tau compressed. Thiab lub sij hawm thov nruab nrab tseem nyob ntawm kwv yees li tib theem. Tab sis ntawm no kuv muaj, theej, qhov yuam kev hauv kuv lub hardware.

Nov yog qhov uas thawj zaj dab neeg xaus. Nws yog qhov feem ntau. Thiab nws tshwm sim rau txhua tus, tsis hais tus neeg siv khoom qhov kev paub dhau los thiab cov programmer tsim nyog npaum li cas. Tsis ntev los sis tom qab qhov no tshwm sim.

Qhov thib ob zaj dab neeg, nyob rau hauv uas peb faib lub load thiab optimize neeg rau zaub mov kev pab

• Peb twb loj hlob lawm thiab ua neeg loj. Thiab peb to taub hais tias peb muaj ib tug replica thiab nws yuav zoo rau peb kom sib npaug ntawm cov load: sau rau tus Xib Hwb, thiab nyeem los ntawm replica. Thiab feem ntau qhov xwm txheej no tshwm sim thaum peb xav npaj qee cov ntawv ceeb toom lossis ETL. Thiab kev lag luam zoo siab heev txog qhov no. Nws yeej xav tau ntau yam kev tshaj tawm nrog ntau qhov kev txheeb xyuas nyuaj.
• Cov ntawv ceeb toom yuav siv sij hawm ntau teev, vim hais tias cov analytics nyuaj tsis tuaj yeem xam hauv milliseconds. Peb, zoo li cov neeg siab tawv, sau cov lej. Hauv daim ntawv thov nkag peb ua cov ntaub ntawv teev tseg ntawm tus Xib Hwb, thiab ua tiav cov lus ceeb toom ntawm qhov hloov pauv.
• Distributing lub load.
• Txhua yam ua haujlwm zoo kawg nkaus. Peb zoo heev.

Thiab qhov xwm txheej no zoo li cas? Tshwj xeeb ntawm cov duab no, kuv kuj tau ntxiv lub sijhawm ntawm kev hloov pauv los ntawm kev hloov pauv rau lub sijhawm hloov pauv. Tag nrho lwm cov duab tsuas yog xa mus rau Master server.

Txog lub sijhawm no, kuv daim ntawv tshaj tawm tau loj hlob. Muaj ntau ntawm lawv. Peb pom tias lub sijhawm nruab nrab ntawm cov neeg rau zaub mov teb tau ruaj khov. Peb pom tias nyob rau hauv replica peb muaj kev khiav hauj lwm ntev uas khiav rau 2 teev. Peb pom kev ua haujlwm ntsiag to ntawm autovacuum, uas ua cov kab tuag. Thiab txhua yam zoo nrog peb.

Tshwj xeeb, raws li cov ntawv xeem, peb txuas ntxiv hloov kho cov nyiaj tshuav nyob ntawd. Thiab peb kuj muaj lub sijhawm teb ruaj khov rau kev thov, kev siv khoom ruaj khov. Txhua yam zoo nrog peb.

Txhua yam zoo kom txog rau thaum lub sijhawm cov ntawv tshaj tawm no pib rov qab vim muaj kev tsis sib haum xeeb nrog kev rov ua dua. Thiab lawv tua hluav taws rov qab rau lub sijhawm tsis tu ncua.

Peb mus online thiab pib nyeem vim li cas qhov no tshwm sim. Thiab peb nrhiav kev daws teeb meem.

Thawj qhov kev daws teeb meem yog txhawm rau ua kom rov ua dua latency. Peb paub tias peb daim ntawv tshaj tawm ua haujlwm rau 3 teev. Peb teem lub replication ncua rau 3 teev. Peb tab tom tshaj tawm txhua yam, tab sis peb tseem muaj teeb meem nrog cov ntawv ceeb toom qee zaum raug muab tso tseg.

Peb xav kom txhua yam zoo tag nrho. Peb nce ntxiv. Thiab peb pom qhov chaw txias hauv Is Taws Nem - hot_standby_feedback. Wb tig rau. Hot_standby_feedback tso cai rau peb tuav rov qab autovacuum ntawm tus Tswv. Yog li ntawd, peb tau tshem ntawm replication tsis sib haum xeeb kiag li. Thiab txhua yam ua haujlwm zoo rau peb nrog cov ntawv ceeb toom.

Thiab dab tsi tshwm sim nrog Master server nyob rau lub sijhawm no? Thiab peb muaj teeb meem tag nrho nrog Master server. Tam sim no peb tab tom pom cov duab kos thaum kuv muaj ob qho kev teeb tsa no tau qhib. Thiab peb pom tias qhov kev sib kho ntawm peb qhov kev hloov pauv tau pib cuam tshuam qhov xwm txheej ntawm Master server. Nws muaj kev cuam tshuam vim nws nres lub autovacuum, uas tshem tawm cov kab tuag. Peb lub rooj loj tau skyrocketed dua. Cov lus nug nruab nrab lub sij hawm ua tiav thoob plaws tag nrho cov ntaub ntawv tseem skyrocketed. Cov autovacuums nruj me ntsis.

Tshwj xeeb, los ntawm peb lub phaj, peb pom tias cov ntaub ntawv hloov tshiab ntawm nws kuj dhia mus rau saum ntuj. CPU noj tau zoo sib xws tau nce ntau heev. Peb tab tom rov qab los ntawm ntau tus neeg tuag, kab tsis muaj txiaj ntsig. Thiab lub sij hawm teb rau qhov kos npe no thiab tus lej ntawm kev lag luam tau poob.

Nws yuav zoo li cas yog tias peb tsis paub qhov kuv tau hais ua ntej?

• Peb pib nrhiav teeb meem. Yog tias peb ntsib teeb meem hauv thawj ntu, peb paub tias qhov no yuav yog vim muaj kev hloov pauv ntev thiab mus rau tus Xib Hwb. Peb muaj teeb meem ntawm tus Xib Hwb. Sausages nws. Nws heats li, nws Load nruab nrab yog hais txog ib puas.
• Kev thov muaj qeeb, tab sis peb tsis pom muaj kev lag luam ntev nyob ntawd. Thiab peb tsis to taub dab tsi yog qhov teeb meem. Peb tsis nkag siab qhov twg los saib.
• Peb xyuas cov khoom siv server. Tej zaum peb qhov kev tsoo tsoo. Tej zaum peb lub cim xeeb tau hlawv. Yog, txhua yam tuaj yeem tshwm sim. Tab sis tsis yog, cov servers tshiab, txhua yam ua haujlwm zoo.
• Txhua tus ua haujlwm: cov thawj coj, cov tsim tawm thiab tus thawj coj. Tsis muaj dab tsi pab.
• Thiab ntawm qee kis txhua yam dheev pib kho nws tus kheej.

Lub sijhawm no, qhov kev thov ntawm peb qhov kev hloov pauv tau ua tiav thiab sab laug. Peb tau txais tsab ntawv ceeb toom. Kev lag luam tseem zoo siab. Raws li koj tau pom, peb lub cim tau loj hlob dua thiab yuav tsis ploj mus. Ntawm daim duab nrog cov kev sib tham, kuv tau tso ib daim ntawm qhov kev hloov pauv ntev no los ntawm kev hloov pauv kom koj tuaj yeem kwv yees nws yuav siv sijhawm ntev npaum li cas kom txog thaum qhov xwm txheej ruaj khov.

Kev sib kho tiav lawm. Thiab tsuas yog tom qab qee lub sijhawm tus neeg rau zaub mov tuaj ntau lossis tsawg dua hauv kev txiav txim. Thiab lub sij hawm teb nruab nrab rau kev thov ntawm Master server rov qab mus rau qhov qub. Vim tias, thaum kawg, autovacuum muaj lub sijhawm los ntxuav tawm thiab kos cov kab tuag no. Thiab nws pib ua nws txoj hauj lwm. Thiab sai npaum li cas nws ua nws, sai sai peb yuav tau txais kev txiav txim.

Raws li cov ntsiav tshuaj kuaj, qhov twg peb hloov kho cov nyiaj tshuav nyiaj li cas, peb pom raws nraim tib daim duab. Lub sijhawm nruab nrab tus lej hloov tshiab kuj tseem maj mam normalizing. Cov peev txheej siv los ntawm processor kuj raug txo. Thiab tus naj npawb ntawm kev hloov pauv ib ob rov qab mus rau qhov qub. Tab sis dua peb rov zoo li qub, tsis zoo li peb tau ua ntej kev sib tsoo.

Txawm li cas los xij, peb tau txais qhov kev poob qis, zoo li hauv thawj kis, los ntawm ib thiab ib nrab mus rau ob zaug, thiab qee zaum ntau dua.

Peb zoo li tau ua txhua yam zoo. Faib lub load. Cov cuab yeej siv tsis ua haujlwm. Peb faib cov kev thov raws li peb lub siab, tab sis tseem txhua yam ua tsis zoo.

• Tsis txhob qhib hot_standby_feedback? Yog, nws tsis pom zoo kom tig nws yam tsis muaj laj thawj tshwj xeeb. Vim tias qhov kev sib tw no cuam tshuam ncaj qha rau Master server thiab ncua kev ua haujlwm ntawm autovacuum nyob ntawd. Los ntawm kev ua kom nws ntawm qee qhov kev hloov pauv thiab tsis nco qab txog nws, koj tuaj yeem tua tus Tswv thiab tau txais teeb meem loj nrog daim ntawv thov.
• Nce max_standby_streaming_delay? Yog, rau cov lus ceeb toom no muaj tseeb. Yog tias koj muaj daim ntawv tshaj tawm peb-teev thiab koj tsis xav kom nws tsoo vim muaj kev sib cav sib ceg, ces tsuas yog ua kom qeeb. Daim ntawv tshaj tawm mus ntev yeej tsis xav tau cov ntaub ntawv uas tau tuaj txog hauv cov ntaub ntawv tam sim no. Yog tias koj muaj nws li peb teev, ces koj tab tom khiav nws rau qee lub sijhawm cov ntaub ntawv qub. Thiab rau koj, txawm tias muaj kev ncua peb-teev lossis ncua rau XNUMX-teev yuav tsis muaj qhov sib txawv, tab sis koj yuav tau txais cov ntawv ceeb toom tsis tu ncua thiab yuav tsis muaj teeb meem nrog lawv poob.
• Lawm, koj yuav tsum tau tswj cov kev sib tham ntev ntawm replicas, tshwj xeeb tshaj yog tias koj txiav txim siab los pab kom hot_standby_feedback ntawm ib tug replica. Vim muaj dab tsi tshwm sim. Peb muab cov qauv no rau tus tsim tawm kom nws tuaj yeem sim cov kev thov. Nws sau ntawv thov vwm. Nws tso nws thiab tawm mus haus tshuaj yej, thiab peb tau txais tus Tswv tsim. Los yog tej zaum peb muab daim ntawv thov tsis raug rau ntawd. Cov xwm txheej no txawv. Kev sib tham ntawm cov ntawv luam yuav tsum tau saib xyuas kom zoo zoo li ntawm tus Xib Hwb.
• Thiab yog tias koj muaj lus nug ceev thiab ntev ntawm cov ntawv luam tawm, ces qhov no nws yog qhov zoo dua los faib lawv kom faib cov khoom thauj. Nov yog qhov txuas rau streaming_delay. Rau cov ceev ceev, muaj ib tug replica nrog ib tug me me replication ncua. Rau qhov kev thov ua haujlwm ntev ntev, muaj cov qauv qub uas tuaj yeem ua lag luam los ntawm 6 teev lossis ib hnub. Qhov no yog qhov xwm txheej ib txwm muaj.

Peb tshem tawm qhov tshwm sim hauv tib txoj kev:

• Peb pom bloated rooj.
• Thiab peb compress nws nrog cov cuab yeej yooj yim tshaj plaws uas haum rau peb.

Zaj thib ob xaus ntawm no. Cia peb mus rau zaj thib peb.

Kuj tseem muaj ntau rau peb uas peb ua migration.

• Txhua yam khoom siv software loj hlob tuaj. Cov kev cai rau nws hloov. Txawm li cas los xij, peb xav txhim kho. Thiab nws tshwm sim tias peb yuav tsum tau hloov kho cov ntaub ntawv hauv lub rooj, uas yog los khiav qhov hloov tshiab ntawm peb txoj kev tsiv teb tsaws rau cov haujlwm tshiab uas peb tab tom qhia ua ib feem ntawm peb txoj kev loj hlob.
• Cov ntaub ntawv qub tsis txaus siab. Cia peb hais tias tam sim no peb tig mus rau lub rooj thib ob, qhov twg kuv muaj kev hloov pauv ntawm cov nyiaj no. Thiab cia peb hais tias lawv nyob rau hauv rubles, thiab peb txiav txim siab los ua kom qhov tseeb thiab ua nws nyob rau hauv kopecks. Thiab rau qhov no peb yuav tsum tau ua ib qho kev hloov tshiab: muab cov teb nrog cov nyiaj pauv los ntawm ib puas.
• Nyob rau hauv lub ntiaj teb no niaj hnub no, peb siv automated database version tswj cov cuab yeej. Cia peb hais Liquibase. Peb sau npe peb txoj kev tsiv teb tsaws chaw nyob ntawd. Peb sim nws ntawm peb lub hauv paus xeem. Txhua yam zoo. Qhov hloov tshiab yuav dhau mus. Nws blocks ua haujlwm ib ntus, tab sis peb tau txais cov ntaub ntawv tshiab. Thiab peb tuaj yeem tso tawm cov haujlwm tshiab ntawm qhov no. Txhua yam raug kuaj thiab kuaj xyuas. Txhua yam tau lees paub.
• Peb tau ua haujlwm tau npaj tseg thiab ua haujlwm tsiv teb tsaws chaw.

Nov yog qhov kev tsiv teb tsaws chaw nrog qhov hloov tshiab tau nthuav tawm rau pem hauv ntej ntawm koj. Txij li cov no yog kuv tus account muas, lub phaj yog 15 GB. Thiab txij li thaum peb hloov kho txhua kab, peb muab ob npaug rau qhov loj ntawm lub rooj nrog qhov hloov tshiab, vim peb rov sau txhua kab.

Thaum lub sijhawm tsiv teb tsaws, peb tsis tuaj yeem ua dab tsi nrog lub phaj no, vim tias txhua qhov kev thov rau nws tau teem caij thiab tos kom txog thaum qhov hloov tshiab no tiav. Tab sis ntawm no kuv xav kos koj cov xim rau cov lej uas nyob rau ntawm txoj kab ntsug. Ntawd yog, peb muaj lub sijhawm thov nruab nrab ua ntej kev tsiv teb tsaws txog li 5 milliseconds thiab lub processor load, tus naj npawb ntawm kev ua haujlwm thaiv rau kev nyeem disk nco yog tsawg dua 7,5.

Peb tau ua qhov kev tsiv teb tsaws chaw thiab tau txais teeb meem dua.

Kev tsiv teb tsaws chaw tau ua tiav, tab sis:

• Cov haujlwm qub tam sim no siv sijhawm ntev dua los ua kom tiav.
• Lub rooj loj dua.
• Lub load ntawm tus neeg rau zaub mov tau dhau los ua ntau dua ua ntej.
• Thiab, ntawm chav kawm, peb tseem tinkering nrog cov kev ua haujlwm uas ua haujlwm zoo, peb tau txhim kho nws me ntsis.

Thiab qhov no yog qhov mob plab, uas rov ua rau peb lub neej.

Ntawm no kuv qhia tau tias lub rooj, zoo li ob rooj plaub dhau los, yuav tsis rov qab mus rau nws qhov ntau thiab tsawg. Qhov nruab nrab server load zoo li txaus.

Thiab yog tias peb tig mus rau lub rooj nrog cov nyiaj, peb yuav pom tias lub sijhawm thov nruab nrab tau nce ob npaug rau lub rooj no. Lub thauj khoom ntawm lub processor thiab cov xov tooj ntawm cov kab txheeb tawm hauv lub cim xeeb dhia saum 7,5, tab sis qis dua. Thiab nws dhia 2 zaug nyob rau hauv cov ntaub ntawv ntawm processors, 1,5 zaug nyob rau hauv cov ntaub ntawv ntawm kev khiav hauj lwm thaiv, piv txwv li peb tau txais ib tug degradation nyob rau hauv neeg rau zaub mov kev ua tau zoo. Thiab yog li ntawd - degradation ntawm kev ua tau zoo ntawm peb daim ntawv thov. Nyob rau tib lub sijhawm, tus naj npawb ntawm kev hu tseem nyob ze ntawm tib theem.

Thiab qhov tseem ceeb ntawm no yog kom nkag siab tias yuav ua li cas thiaj li tau tsiv teb tsaws chaw kom raug. Thiab lawv yuav tsum tau ua. Peb ua cov migrations no zoo ib yam.

• Tej kev tsiv teb tsaws chaw loj zoo li no tsis tshwm sim tau. Lawv yuav tsum tau tswj hwm txhua lub sijhawm.
• Yuav tsum tau saib xyuas los ntawm tus neeg paub. Yog tias koj muaj DBA hauv koj pab neeg, ces cia DBA ua nws. Nws yog nws txoj haujlwm. Yog tias tsis yog, cia tus neeg muaj kev paub dhau los ua nws, leej twg paub ua haujlwm nrog databases.
• Ib tug tshiab database schema, txawm yog hais tias peb hloov tshiab ib kem, peb ib txwm npaj nyob rau hauv theem, piv txwv li ua ntej ua ntej lub tshiab version ntawm daim ntawv thov raug dov tawm:
• Cov teb tshiab tau ntxiv uas peb yuav sau cov ntaub ntawv tshiab.
• Peb hloov cov ntaub ntawv los ntawm daim teb qub mus rau daim teb tshiab hauv qhov me me. Vim li cas peb thiaj ua li no? Ua ntej, peb ib txwm tswj cov txheej txheem ntawm cov txheej txheem no. Peb paub tias peb twb tau pauv ntau ntau ntau lawm thiab peb muaj ntau heev lawm.
• Thiab qhov txiaj ntsig zoo thib ob yog qhov nruab nrab ntawm txhua qhov no peb kaw qhov kev sib pauv, qhib ib qho tshiab, thiab qhov no tso cai rau lub tshuab nqus tsev vacuum ua haujlwm raws li lub phaj, kos cov kab tuag rau rov siv dua.
• Rau cov kab uas yuav tshwm sim thaum daim ntawv thov tab tom khiav (peb tseem muaj daim ntawv thov qub khiav), peb ntxiv ib qho txiaj ntsig uas sau cov txiaj ntsig tshiab rau cov teb tshiab. Hauv peb cov ntaub ntawv, qhov no yog qhov sib npaug ntawm ib puas ntawm cov nqi qub.
• Yog hais tias peb yog tag nrho cov tawv ncauj thiab xav tau tib lub teb, ces thaum ua tiav ntawm tag nrho cov migrations thiab ua ntej dov tawm ib tug tshiab version ntawm daim ntawv thov, peb tsuas rename lub teb. Cov qub tau muab qee lub npe tsim, thiab cov teb tshiab tau hloov npe rau cov qub.
• Thiab tsuas yog tom qab ntawd peb tso tawm ib qho tshiab ntawm daim ntawv thov.

Thiab nyob rau tib lub sij hawm peb yuav tsis tau ib tug bloat thiab yuav tsis raug kev txom nyem nyob rau hauv cov nqe lus ntawm kev ua tau zoo.

Nov yog qhov peb zaj dab neeg xaus.

https://github.com/dataegret/pg-utils/blob/master/sql/table_bloat.sql

https://github.com/dataegret/pg-utils/blob/master/sql/table_bloat_approx.sql

Thiab tam sim no me ntsis ntxiv txog cov cuab yeej uas kuv tau hais hauv thawj zaj dab neeg.

Ua ntej nrhiav bloat, koj yuav tsum nruab qhov txuas ntxiv pgst ua.

Yog li ntawd koj tsis tas yuav tuaj nrog cov lus nug, peb twb tau sau cov lus nug no hauv peb txoj haujlwm. Koj tuaj yeem siv lawv. Muaj ob qhov kev thov ntawm no.

• Thawj tus yuav siv sij hawm ntev heev los ua hauj lwm, tab sis nws yuav qhia koj qhov tseeb bloat qhov tseem ceeb ntawm lub rooj.
• Qhov thib ob ua haujlwm sai dua thiab ua tau zoo heev thaum koj xav tau nrawm ntsuas seb puas muaj qhov khaus lossis tsis raws li lub rooj. Thiab koj yuav tsum tau nkag siab tias tsam plab yog ib txwm nyob hauv lub rooj Postgres. Qhov no yog ib qho ntawm nws cov qauv MVCC.
• Thiab 20% bloat yog qhov qub rau cov rooj hauv feem ntau. Uas yog, koj yuav tsum tsis txhob txhawj thiab compress lub rooj no.

Peb xav paub yuav ua li cas txheeb xyuas cov ntxhuav uas o nrog cov ntaub ntawv tsis muaj txiaj ntsig.

Tam sim no hais txog yuav ua li cas kho bloat:

• Yog tias peb muaj cov ntsiav tshuaj me me thiab cov disks zoo, uas yog, ntawm lub ntsiav tshuaj mus txog ib gigabyte, nws yog qhov ua tau kom siv VACUUM FULL. Nws yuav muab lub xauv tshwj xeeb los ntawm koj ntawm lub rooj rau ob peb feeb thiab tsis ua li cas, tab sis nws yuav ua txhua yam sai thiab hnyav. VACUUM FULL ua dab tsi? Nws yuav siv lub xauv tshwj xeeb rau ntawm lub rooj thiab rov sau cov kab nyob ntawm cov rooj qub rau hauv lub rooj tshiab. Thiab thaum kawg nws hloov lawv. Nws deletes cov ntaub ntawv qub thiab hloov cov qub nrog cov tshiab. Tab sis rau lub sijhawm ntawm nws txoj haujlwm, nws yuav siv lub xauv tshwj xeeb ntawm lub rooj. Qhov no txhais tau hais tias koj ua tsis tau ib yam dab tsi nrog lub rooj no: tsis sau rau nws, los yog nyeem rau hauv nws, los yog hloov nws. Thiab VACUUM FULL xav tau qhov chaw disk ntxiv los sau cov ntaub ntawv.
• Cov cuab yeej tom ntej pg_repack. Hauv nws txoj cai, nws zoo ib yam li VACUUM FULL, vim nws kuj rov sau cov ntaub ntawv los ntawm cov ntaub ntawv qub rau cov tshiab thiab hloov lawv hauv lub rooj. Tab sis tib lub sijhawm, nws tsis siv lub xauv tshwj xeeb ntawm lub rooj thaum pib ntawm nws txoj haujlwm, tab sis siv nws tsuas yog lub sijhawm thaum nws twb tau npaj cov ntaub ntawv txhawm rau hloov cov ntaub ntawv. Nws cov kev xav tau ntawm disk zoo ib yam li cov VACUUM FULL. Koj xav tau qhov chaw disk ntxiv, thiab qee zaum qhov no tseem ceeb heev yog tias koj muaj cov ntxhuav terabyte. Thiab nws yog tus processor-tshaib plab heev vim nws nquag ua haujlwm nrog I / O.
• Qhov thib peb siv yog pgcompactable. Nws tau ceev faj ntau dua nrog cov peev txheej vim nws ua haujlwm raws li cov ntsiab lus sib txawv me ntsis. Lub tswv yim tseem ceeb ntawm pgcompacttable yog tias nws txav tag nrho cov kab nyob mus rau qhov pib ntawm lub rooj siv hloov tshiab hauv lub rooj. Thiab tom qab ntawd nws khiav lub tshuab nqus tsev ntawm lub rooj no, vim tias peb paub tias peb muaj kab nyob hauv qhov pib thiab kab tuag thaum kawg. Thiab lub tshuab nqus tsev nws tus kheej txiav tawm tus Tsov tus tw, piv txwv li nws tsis xav tau ntau qhov chaw disk ntxiv. Thiab nyob rau tib lub sijhawm, nws tseem tuaj yeem raug nyem rau ntawm cov peev txheej.

Txhua yam nrog cov cuab yeej.

Yog tias koj pom cov ncauj lus bloat nthuav dav hauv cov ntsiab lus ntawm delving ntxiv sab hauv, ntawm no yog qee qhov kev sib txuas:

• https://www.slideshare.net/alexius2Mb/where-is-the-space-postgres - Nov yog tsab ntawv ceeb toom los ntawm kuv cov npoj yaig. Nws yog qhov dav dav txog qhov chaw Postgres mus thaum nws ua haujlwm thiab lub neej. Thiab muaj ib qho loj heev thiab cov ncauj lus kom ntxaws cov ntaub ntawv rau cov thawj coj database txog bloat.
• https://github.com/dataegret/pg-utils - qhov no yog qhov txuas mus rau peb lub chaw cia khoom, uas peb khaws ntau cov ntawv sau muaj txiaj ntsig rau kev txheeb xyuas lub xeev ntawm cov ntaub ntawv. Nyob ntawd koj tuaj yeem nrhiav cov ntawv sau los tshawb nrhiav bloat.
• Thib peb и thib plaub txuas rau cov cuab yeej uas yuav pab koj txo qis cov paib.
• http://blog.dataegret.com/2Mb018/03/postgresql-bloatbusters.html - qhov no yog ib tsab ntawv los ntawm kuv cov npoj yaig. Nyob ntawd nws heev tiag tiag thiab technically soj ntsuam bloat hauv kev nthuav dav ntawm ib qib ze rau cov thawj coj.

Kuv sim ntau dua los qhia zaj dab neeg txaus ntshai rau cov neeg tsim tawm, vim tias lawv yog peb cov neeg siv khoom ncaj qha ntawm cov ntaub ntawv thiab yuav tsum nkag siab txog dab tsi thiab dab tsi ua rau. Kuv vam tias kuv ua tiav. Ua tsaug rau koj mloog!

Lus nug

Ua tsaug rau daim ntawv qhia! Koj tau tham txog qhov koj tuaj yeem txheeb xyuas cov teeb meem. Lawv yuav ceeb toom li cas? Ntawd yog, kuv muaj qhov xwm txheej uas thov dai tsis yog vim lawv nkag mus rau qee qhov kev pabcuam sab nraud. Cov no tsuas yog qee qhov kev koom nrog qus. Muaj qee qhov kev thov me me, tsis muaj teeb meem uas tau dai rau ib hnub, thiab tom qab ntawd pib ua qee yam tsis muaj tseeb. Ntawd yog, zoo ib yam li qhov koj piav qhia. Yuav ua li cas taug qab qhov no? Zaum thiab saib tsis tu ncua qhov kev thov twg yog daig? Yuav tiv thaiv qhov no li cas?

Hauv qhov no, qhov no yog txoj haujlwm rau cov thawj coj ntawm koj lub tuam txhab, tsis tas rau DBA.

Kuv yog tus thawj tswj hwm.

PostgreSQL muaj qhov pom hu ua pg_stat_activity uas qhia cov lus nug dangling. Thiab koj tuaj yeem pom ntev npaum li cas nws dai nyob ntawd.

Kuv puas yuav tsum tuaj saib txhua 5 feeb?

Teem cron thiab kos. Yog tias koj muaj lus thov ntev, sau ib tsab ntawv thiab qhov ntawd yog nws. Ntawd yog, koj tsis tas yuav saib nrog koj lub qhov muag, nws tuaj yeem ua haujlwm. Koj yuav tau txais tsab ntawv, koj teb rau nws. Los yog koj tuaj yeem tua tau.

Puas muaj cov laj thawj pom tseeb vim li cas qhov no tshwm sim?

Kuv tau teev ib co. Lwm cov piv txwv nyuaj. Thiab tuaj yeem muaj kev sib tham ntev.

Ua tsaug rau daim ntawv qhia! Kuv xav qhia meej txog cov khoom siv pg_repack. Yog hais tias nws tsis ua ib qho kev xauv tshwj xeeb, ces ...

Nws ua tus xauv tshwj xeeb.

... ces kuv yuav muaj feem poob ntaub ntawv. Kuv daim ntawv thov puas yuav tsum tsis txhob sau dab tsi rau lub sijhawm no?

Tsis yog, nws ua haujlwm zoo nrog lub rooj, piv txwv li pg_repack ua ntej hloov tag nrho cov kab nyob uas muaj nyob. Lawm, qee yam kev nkag mus rau hauv lub rooj tshwm sim muaj. Nws tsuas yog muab tus ponytail no pov tseg xwb.

Qhov ntawd yog, nws yeej ua nws thaum kawg?

Thaum kawg, nws siv lub xauv tshwj xeeb los pauv cov ntaub ntawv no.

Nws puas yuav sai dua VACUUM FULL?

VACUUM FULL, sai li sai tau thaum nws pib, tam sim ntawd muab lub xauv tshwj xeeb. Thiab txog thaum nws ua txhua yam, nws yuav tsis tso nws mus. Thiab pg_repack siv lub xauv tshwj xeeb nkaus xwb thaum lub sijhawm hloov cov ntaub ntawv. Lub sijhawm no koj yuav tsis sau rau ntawd, tab sis cov ntaub ntawv yuav tsis ploj, txhua yam yuav zoo.

Nyob zoo! Koj tau tham txog kev ua haujlwm ntawm lub tshuab nqus tsev. Muaj ib daim duab nrog liab, daj thiab ntsuab kaw cov hlwb. Ntawd yog, daj sawv daws yuav - nws cim lawv li deleted. Thiab yog li ntawd, ib yam dab tsi tshiab tuaj yeem sau rau hauv lawv?

Yog lawm. Postgres tsis rho tawm kab. Nws muaj qhov tshwj xeeb. Yog hais tias peb hloov kho ib kab, peb cim tus qub li deleted. Tus id ntawm kev hloov pauv uas hloov kab no tshwm nyob rau ntawd, thiab peb sau ib kab tshiab. Thiab peb muaj cov rooj sib tham uas tuaj yeem nyeem tau lawv. Qee lub sij hawm lawv dhau los ua neeg laus. Thiab lub ntsiab lus ntawm yuav ua li cas lub autovacuum ua haujlwm yog tias nws mus dhau cov kab no thiab ua rau lawv tsis tsim nyog. Thiab koj tuaj yeem sau cov ntaub ntawv nyob ntawd.

kuv to taub. Tab sis qhov ntawd tsis yog qhov lus nug txog. kuv ua tsis tiav. Cia peb xav tias peb muaj ib lub rooj. Nws muaj thaj tsam ntawm qhov sib txawv. Thiab yog tias kuv sim ntxig ib yam tshiab, nws yuav yooj yim tsis haum rau hauv lub cell qub.

Tsis yog, nyob rau hauv txhua rooj plaub, tag nrho cov kab tau hloov kho nyob rau ntawd. Postgres muaj ob lub qauv khaws ntaub ntawv. Nws xaiv los ntawm hom ntaub ntawv. Muaj cov ntaub ntawv khaws cia ncaj qha rau hauv lub rooj, thiab tseem muaj cov ntaub ntawv tos. Cov no yog cov ntaub ntawv loj: ntawv nyeem, json. Lawv muab khaws cia rau hauv nyias daim hlau. Thiab raws li cov ntsiav tshuaj, tib zaj dab neeg nrog bloat tshwm sim, i.e. txhua yam yog tib yam. Lawv tsuas yog sau npe cais.

Ua tsaug rau daim ntawv qhia! Nws puas tuaj yeem siv cov lus nug ncua sij hawm los txwv lub sijhawm?

Txaus siab heev. Peb siv qhov no txhua qhov chaw. Thiab txij li peb tsis muaj peb tus kheej cov kev pabcuam, peb muab kev pabcuam rau cov chaw taws teeb, peb muaj ntau tus neeg siv khoom. Thiab txhua leej txhua tus txaus siab rau qhov no. Ntawd yog, peb muaj cov haujlwm cron uas kuaj xyuas. Lub sijhawm ntawm kev sib tham tsuas yog pom zoo nrog tus neeg siv khoom, ua ntej peb tsis pom zoo. Nws tuaj yeem yog ib feeb, nws tuaj yeem yog 10 feeb. Nws nyob ntawm qhov load ntawm lub hauv paus thiab nws lub hom phiaj. Tab sis peb txhua tus siv pg_stat_activity.

Ua tsaug rau daim ntawv qhia! Kuv tab tom sim siv koj daim ntawv tshaj tawm rau kuv daim ntawv thov. Thiab nws zoo li peb pib ua lag luam nyob txhua qhov chaw, thiab ua kom tiav nws txhua qhov chaw. Yog tias muaj qee qhov kev zam, ces rollback tseem tshwm sim. Thiab ces kuv pib xav. Tom qab tag nrho, kev hloov pauv yuav tsis pib qhia meej. Tej zaum qhov no yog ib qho qhia rau tus ntxhais. Yog tias kuv nyuam qhuav hloov kho cov ntaub ntawv, puas yuav pib hauv PostgreSQL thiab tsuas yog ua tiav thaum qhov kev sib txuas raug txiav?

Yog tias koj tab tom tham tam sim no txog qib kev thov, ces nws nyob ntawm tus tsav tsheb uas koj siv, ntawm ORM uas tau siv. Muaj ntau qhov chaw nyob ntawd. Yog hais tias koj muaj nws pib cog lus rau enabled, ces ib tug pauv pib muaj thiab kaw tam sim ntawd.

Ntawd yog, nws kaw tam sim tom qab hloov tshiab?

Nws nyob ntawm qhov chaw. Kuv npe ib qhov chaw. Qhov no yog auto commit on. Nws yog qhov ntau heev. Yog hais tias nws yog enabled, ces qhov kev pauv tau qhib thiab kaw. Tshwj tsis yog tias koj tau hais meej meej tias "pib kev lag luam" thiab "kev ua lag luam kawg", tab sis tsuas yog pib thov rau hauv kev sib kho.

Nyob zoo! Ua tsaug rau daim ntawv qhia! Cia peb xav txog tias peb muaj cov ntaub ntawv uas o thiab o thiab tom qab ntawd qhov chaw ntawm lub server khiav tawm. Puas muaj cov cuab yeej los kho qhov xwm txheej no?

Qhov chaw ntawm lub server yuav tsum tau saib xyuas kom zoo.

Piv txwv li, DBA mus rau tshuaj yej, nyob ntawm lub chaw so, thiab lwm yam.

Thaum tsim cov ntaub ntawv kaw lus, tsawg kawg qee qhov chaw thaub qab yog tsim qhov twg cov ntaub ntawv tsis sau.

Yuav ua li cas yog tias tag nrho hauv qab xoom?

Nyob ntawd nws yog hu ua qhov chaw tshwj tseg, piv txwv li nws tuaj yeem tso tawm thiab nyob ntawm seb nws tau tsim loj npaum li cas, koj tau txais qhov chaw dawb. Los ntawm lub neej ntawd kuv tsis paub tias muaj pes tsawg. Thiab nyob rau hauv lwm qhov xwm txheej, xa cov disks kom koj muaj chaw ua haujlwm rov tsim dua. Koj tuaj yeem rho tawm qee lub rooj uas koj tau lees tias tsis xav tau.

Puas muaj lwm yam cuab yeej?

Nws yog ib txwm xuas tes ua. Thiab hauv zos nws paub meej tias qhov twg yog qhov zoo tshaj los ua qhov ntawd, vim qee cov ntaub ntawv tseem ceeb thiab qee qhov tsis tseem ceeb. Thiab rau txhua tus database thiab daim ntawv thov uas ua haujlwm nrog nws, nws nyob ntawm kev lag luam. Nws ib txwm txiav txim siab hauv zos.

Ua tsaug rau daim ntawv qhia! Kuv muaj ob lo lus nug. Ua ntej, koj tau pom cov slides uas pom tau tias thaum kev lag luam daig, ob qho tib si lub rooj loj thiab qhov ntsuas qhov loj hlob. Thiab ntxiv rau ntawm daim ntawv tshaj tawm muaj ntau cov khoom siv hluav taws xob uas ntim cov ntsiav tshuaj. Yuav ua li cas yog lub index?

Lawv ntim lawv thiab.

Tab sis lub tshuab nqus tsev tsis cuam tshuam qhov Performance index?

Qee qhov ua haujlwm nrog qhov ntsuas. Piv txwv li, pg_rapack, pgcompacttable. Lub tshuab nqus tsev rov tsim cov indexes thiab cuam tshuam rau lawv. Nrog VACUUM FULL lub tswv yim yog los sau txhua yam, piv txwv li nws ua haujlwm nrog txhua tus.

Thiab lo lus nug thib ob. Kuv tsis nkag siab tias vim li cas cov ntawv ceeb toom ntawm cov ntawv luam tawm nyob ntawm ntau yam ntawm qhov rov ua dua nws tus kheej. Nws zoo nkaus li kuv tias cov ntawv ceeb toom tau nyeem, thiab rov sau dua.

Dab tsi ua rau muaj kev sib cav sib ceg? Peb muaj tus Xib Hwb uas cov txheej txheem yuav tshwm sim. Peb muaj lub tshuab nqus tsev vacuum mus. Lub autovacuum ua tau dab tsi? Nws tab tom txiav qee cov kab qub. Yog hais tias lub sij hawm no peb muaj ib qho kev thov ntawm cov replica uas nyeem cov qub kab no, thiab nyob rau hauv tus Xib Hwb muaj ib qho teeb meem tshwm sim hais tias lub autovacuum cim cov kab no li ua tau rau overwrite, ces peb overwrote lawv. Thiab peb tau txais ib pob ntawv cov ntaub ntawv, thaum peb yuav tsum tau rov sau cov kab uas qhov kev thov xav tau ntawm lub replica, tus txheej txheem replication yuav tos rau lub sij hawm uas koj configured. Thiab tom qab ntawd PostgreSQL yuav txiav txim siab dab tsi tseem ceeb dua rau nws. Thiab replication tseem ceeb tshaj rau nws tshaj qhov kev thov, thiab nws yuav tua qhov kev thov nyob rau hauv thiaj li yuav ua rau cov kev hloov no ntawm lub replica.

Andrey, Kuv muaj lus nug. Cov duab zoo nkauj no uas koj tau pom thaum lub sijhawm nthuav tawm, cov no puas yog qhov tshwm sim ntawm kev ua haujlwm ntawm qee yam khoom siv ntawm koj li? Cov duab tau ua li cas?

Qhov no yog ib qho kev pabcuam Okmeter.

Qhov no puas yog cov khoom lag luam?

Yog lawm. Qhov no yog cov khoom lag luam.

Tau qhov twg los: www.hab.com