Qee lub sij hawm yav tom ntej nyob deb, kev tshem tawm tsis siv neeg ntawm cov ntaub ntawv tsis tsim nyog yuav yog ib txoj haujlwm tseem ceeb ntawm DBMS [1]. Lub sijhawm no, peb tus kheej yuav tsum tau saib xyuas kev rho tawm lossis hloov cov ntaub ntawv tsis tsim nyog rau cov khoom siv qis dua. Wb hais tias koj txiav txim siab rho tawm ob peb lab kab. Ib txoj haujlwm yooj yim, tshwj xeeb tshaj yog tias tus mob paub thiab muaj qhov ntsuas qhov tsim nyog. "DELETE FROM table1 WHERE col1 = :value" - dab tsi yuav yooj yim dua, txoj cai?
Yees duab:
-
Kuv tau nyob rau hauv Pawg Neeg Saib Xyuas Kev Pabcuam Highload txij li thawj xyoo, piv txwv li txij li xyoo 2007.
-
Thiab kuv tau nrog Postgres txij li xyoo 2005. Nws tau siv rau hauv ntau qhov project.
-
Pab pawg nrog RuPostges kuj tseem txij li xyoo 2007.
-
Peb tau loj hlob mus rau 2100+ cov neeg koom nrog Meetup. Nws yog thib ob hauv ntiaj teb tom qab New York, dhau los ntawm San Francisco rau lub sijhawm ntev.
-
Kuv nyob California tau ntau xyoo. Kuv nrog ntau dua nrog cov tuam txhab Asmeskas, suav nrog cov loj. Lawv yog cov neeg siv ntawm Postgres. Thiab muaj txhua yam uas nthuav.
yog kuv lub tuam txhab. Peb yog nyob rau hauv kev lag luam ntawm automating cov hauj lwm uas tshem tawm txoj kev loj hlob qeeb.
Yog tias koj tab tom ua ib yam dab tsi, ces qee zaum muaj qee yam ntsaws ntawm Postgres. Wb hais tias koj yuav tsum tau tos rau tus thawj tswj hwm los teeb tsa qhov chaw sim rau koj, lossis koj yuav tsum tau tos DBA los teb rau koj. Thiab peb pom cov kev tsis zoo li no hauv kev txhim kho, kev sim thiab kev tswj xyuas cov txheej txheem thiab sim tshem tawm lawv nrog kev pab ntawm automation thiab cov txheej txheem tshiab.
Tsis ntev los no kuv nyob VLDB hauv Los Angeles. Qhov no yog lub rooj sib tham loj tshaj plaws ntawm databases. Thiab muaj ib tsab ntawv ceeb toom tias yav tom ntej DBMS yuav tsis tsuas yog khaws cia, tab sis kuj yuav rho tawm cov ntaub ntawv. Nov yog lub ntsiab lus tshiab.
Muaj ntau thiab ntau cov ntaub ntawv nyob rau hauv lub ntiaj teb no ntawm zettabytes - uas yog 1 petabytes. Thiab tam sim no nws twb tau kwv yees tias peb muaj ntau dua 000 zettabytes ntawm cov ntaub ntawv khaws cia hauv ntiaj teb. Thiab muaj ntau thiab ntau ntawm lawv.
Thiab yuav ua li cas nrog nws? Obviously nws yuav tsum tau muab tshem tawm. Nov yog qhov txuas rau daim ntawv qhia nthuav dav no. Tab sis txog tam sim no qhov no tsis tau ua tiav hauv DBMS.
Cov uas suav tau nyiaj xav tau ob yam. Lawv xav kom peb rho tawm, yog li technically peb yuav tsum tau ua.
Qhov kuv yuav qhia ntxiv yog qee qhov xwm txheej tsis txaus ntseeg uas suav nrog ntau qhov xwm txheej tiag tiag, piv txwv li ib hom kev sau ua ke ntawm qhov tshwm sim tiag tiag rau kuv thiab cov ntaub ntawv nyob ib puag ncig ntau zaus, ntau xyoo. Rakes yog nyob txhua qhov chaw thiab txhua tus neeg ua rau lawv txhua lub sijhawm.
Cia peb hais tias peb muaj lub hauv paus lossis ob peb lub hauv paus uas loj hlob tuaj. Thiab qee cov ntaub ntawv tau pom tseeb tias khib nyiab. Piv txwv li, tus neeg siv pib ua ib yam dab tsi nyob rau ntawd, tab sis tsis tiav nws. Thiab tom qab qee lub sij hawm peb paub tias qhov ua tiav no tsis tuaj yeem khaws cia ntxiv lawm. Ntawd yog, peb xav ntxuav qee yam khoom khib nyiab kom txuag chaw, txhim kho kev ua haujlwm, thiab lwm yam.
Feem ntau, txoj haujlwm yog ua kom tshem tawm cov khoom tshwj xeeb, cov kab tshwj xeeb hauv qee lub rooj.
Thiab peb muaj xws li kev thov, uas peb yuav tham txog hnub no, uas yog, hais txog kev tshem tawm cov khib nyiab.
Peb nug ib tus neeg tsim khoom paub los ua nws. Nws tau txais qhov kev thov no, tshuaj xyuas nws tus kheej - txhua yam ua haujlwm. Kuaj nyob rau theem - txhua yam zoo. Rolled tawm - txhua yam ua haujlwm. Ib hnub peb khiav nws - txhua yam zoo.
Lub database loj hlob thiab loj hlob. Txhua hnub DELETE pib ua haujlwm qeeb me ntsis.
Tom qab ntawd peb nkag siab tias tam sim no peb muaj lub tuam txhab kev lag luam thiab kev khiav tsheb yuav loj dua ob peb zaug, yog li peb txiav txim siab ncua ib ntus yam tsis tsim nyog. Thiab tsis nco qab rov qab los.
Ob peb lub hlis tom qab lawv nco qab. Thiab tus tsim tawm ntawd tawm lossis tsis khoom nrog lwm yam, qhia lwm tus kom rov qab los.
Nws tshawb xyuas ntawm dev, ntawm kev ua yeeb yam - txhua yam zoo. Lawm, koj tseem yuav tau ntxuav tej yam uas tau sau tseg. Nws tshawb xyuas txhua yam ua haujlwm.
Yuav ua li cas ntxiv? Ces txhua yam poob rau peb. Nws ntog kom nyob rau qee kis txhua yam ntog. Txhua leej txhua tus nyob rau hauv kev poob siab, tsis muaj leej twg to taub dab tsi tshwm sim. Thiab tom qab ntawd nws hloov tawm tias qhov teeb meem nyob hauv qhov DELETE.
Ib yam dab tsi mus tsis ncaj ncees lawm? Ntawm no yog ib daim ntawv teev dab tsi tuaj yeem ua yuam kev. Qhov twg yog qhov tseem ceeb tshaj plaws?
-
Piv txwv li, tsis muaj kev tshuaj xyuas, piv txwv li tus kws tshaj lij DBA tsis tau saib nws. Nws yuav pom qhov teeb meem tam sim ntawd nrog kev paub txog qhov muag, thiab dhau li ntawd, nws tau nkag mus rau cov khoom, qhov twg ntau lab kab tau sau.
-
Tej zaum lawv tau kuaj xyuas qee yam tsis raug.
-
Tej zaum qhov kho vajtse yog outdated thiab koj yuav tau upgrade lub hauv paus no.
-
Los yog ib yam dab tsi tsis ncaj ncees lawm nrog lub database nws tus kheej, thiab peb yuav tsum tau txav los ntawm Postgres mus rau MySQL.
-
Los yog tej zaum muaj qee yam tsis raug rau kev ua haujlwm.
-
Tej zaum muaj qee qhov yuam kev hauv lub koom haum ntawm kev ua haujlwm thiab koj yuav tsum tua ib tug neeg thiab ntiav cov neeg zoo tshaj?
Tsis muaj DBA check. Yog tias muaj DBA, nws yuav pom ntau lab kab no thiab txawm tias tsis muaj kev sim yuav hais tias: "Lawv tsis ua li ntawd." Piv txwv li yog tias cov cai no nyob hauv GitLab, GitHub thiab yuav muaj cov txheej txheem tshuaj xyuas cov cai thiab tsis muaj ib yam dab tsi uas tsis muaj kev pom zoo los ntawm DBA qhov kev ua haujlwm no yuav tshwm sim ntawm cov khoom, ces pom tseeb tias DBA yuav hais tias: "Qhov no ua tsis tau. β
Thiab nws yuav hais tias koj yuav muaj teeb meem nrog disk IO thiab tag nrho cov txheej txheem yuav vwm, tej zaum yuav muaj cov xauv, thiab koj tseem yuav thaiv autovacuum rau ib pawg ntawm feeb, yog li qhov no tsis zoo.
Qhov yuam kev thib ob - lawv tau kuaj xyuas qhov tsis ncaj ncees lawm. Peb pom tom qab qhov tseeb tias ntau cov ntaub ntawv tsis txaus ntseeg tau sau rau ntawm cov khoom, tab sis tus tsim tawm tsis muaj cov ntaub ntawv khaws tseg hauv cov ntaub ntawv no, thiab tsis muaj leej twg tsim cov khib nyiab no thaum lub sijhawm ua haujlwm. Raws li, muaj 1 kab uas ua haujlwm sai sai.
Peb nkag siab tias peb cov kev xeem tsis muaj zog, piv txwv li cov txheej txheem uas tsim los tsis muaj teeb meem. Ib qho kev sim DB txaus tsis tau ua.
Ib qho kev sim zoo tshaj yog ua tau zoo dua ntawm cov khoom siv tib yam. Nws tsis yog ib txwm ua tau los ua qhov no ntawm tib cov cuab yeej, tab sis nws yog ib qho tseem ceeb heev uas nws yuav tsum yog daim ntawv loj loj ntawm cov ntaub ntawv. Nov yog qhov kuv tau tshaj tawm tau ntau xyoo tam sim no. Thiab ib xyoos dhau los kuv tau tham txog qhov no, koj tuaj yeem saib nws hauv YouTube.
Tej zaum peb cov khoom siv tsis zoo? Yog tias koj saib, ces qhov latency dhia. Peb tau pom tias kev siv yog 100%. Tau kawg, yog tias cov no yog NVMe drives niaj hnub, ces tej zaum nws yuav yooj yim dua rau peb. Thiab tej zaum peb yuav tsis pw ntawm nws.
Yog tias koj muaj huab, ces qhov kev txhim kho tau yooj yim ua tiav rau ntawd. Tsa cov replicas tshiab ntawm qhov kho vajtse tshiab. hloov pauv. Thiab tag nrho yog zoo. Yooj yim heev.
Puas muaj peev xwm kov cov me me disks? Thiab ntawm no, tsuas yog nrog kev pab ntawm DBA, peb dhia mus rau hauv ib lub ntsiab lus hu ua checkpoint tuning. Nws hloov tawm hais tias peb tsis muaj checkpoint tuning.
Checkpoint yog dab tsi? Nws yog nyob rau hauv tej DBMS. Thaum koj muaj cov ntaub ntawv nyob rau hauv lub cim xeeb uas hloov, nws tsis yog tam sim ntawd sau rau disk. Cov ntaub ntawv uas cov ntaub ntawv tau hloov pauv yog thawj zaug sau rau hauv cov ntawv sau ua ntej. Thiab nyob rau qee lub sijhawm, DBMS txiav txim siab tias nws yog lub sijhawm pov tseg cov nplooj ntawv tiag tiag rau disk, yog li yog tias peb tsis ua haujlwm, peb tuaj yeem ua REDO tsawg dua. Nws zoo li khoom ua si. Yog tias peb raug tua, peb yuav pib qhov kev ua si los ntawm qhov chaw kuaj zaum kawg. Thiab tag nrho DBMS siv nws.
Cov kev teeb tsa hauv Postgres tau poob qab. Lawv yog tsim los rau 10-15 xyoo ntim ntawm cov ntaub ntawv thiab kev lag luam. Thiab checkpoint yog tsis muaj kev zam.
Nov yog cov ntaub ntawv los ntawm peb Postgres daim ntawv qhia txog kev kuaj mob, piv txwv li kev kuaj mob tsis siv neeg. Thiab ntawm no yog qee qhov database ntawm ob peb terabytes. Thiab nws tuaj yeem pom zoo tias yuam cov chaw kuaj xyuas yuav luag 90% ntawm cov neeg mob.
Nws txhais li cas? Muaj ob qhov chaw nyob ntawd. Kev kuaj xyuas tuaj yeem tuaj raws sijhawm, piv txwv li, hauv 10 feeb. Los yog nws tuaj yeem tuaj thaum cov ntaub ntawv tau sau ntau heev.
Thiab los ntawm lub neej ntawd max_wal_saze yog teem rau 1 gigabyte. Qhov tseeb, qhov no tshwm sim tiag tiag hauv Postgres tom qab 300-400 megabytes. Koj tau hloov ntau cov ntaub ntawv thiab koj qhov chaw kuaj mob tshwm sim.
Thiab yog tias tsis muaj leej twg tu nws, thiab cov kev pabcuam tau loj hlob, thiab lub tuam txhab khwv tau nyiaj ntau, nws muaj ntau qhov kev hloov pauv, ces qhov chaw kuaj xyuas tuaj ib zaug ib feeb, qee zaum txhua 30 vib nas this, thiab qee zaum txawm tias sib tshooj. Qhov no phem heev.
Thiab peb yuav tsum xyuas kom meej tias nws tuaj tsawg dua. Ntawd yog, peb tuaj yeem tsa max_wal_size. Thiab nws yuav tuaj tsawg zaus.
Tab sis peb tau tsim tag nrho cov txheej txheem rau yuav ua li cas kom raug ntau dua, uas yog, yuav ua li cas txiav txim siab txog kev xaiv chaw, kom meej meej raws li cov ntaub ntawv tshwj xeeb.
Raws li, peb tab tom ua ob qhov kev sim ntawm cov ntaub ntawv.
Thawj koob - peb hloov max_wal_size. Thiab peb tab tom ua haujlwm loj. Ua ntej, peb ua nws ntawm qhov chaw teeb tsa ntawm 1 gigabyte. Thiab peb ua qhov DELETE loj ntawm ntau lab kab.
Koj pom tias nws nyuaj npaum li cas rau peb. Peb pom tias disk IO phem heev. Peb saib seb muaj pes tsawg WALs peb tau tsim, vim qhov no tseem ceeb heev. Cia peb saib pes tsawg zaus lub chaw kuaj mob tshwm sim. Thiab peb pom tias nws tsis zoo.
Tom ntej no peb nce max_wal_size. Peb rov ua dua. Peb nce, peb rov ua dua. Thiab ntau zaus. Hauv txoj cai, 10 cov ntsiab lus yog qhov zoo, qhov twg 1, 2, 4, 8 gigabytes. Thiab peb saib tus cwj pwm ntawm ib qho system. Nws yog tseeb tias ntawm no cov cuab yeej yuav tsum zoo li ntawm cov khoom. Koj yuav tsum muaj tib lub disks, tib lub cim xeeb, thiab tib qhov chaw Postgres.
Thiab nyob rau hauv txoj kev no peb yuav pauv peb lub kaw lus, thiab peb paub tias DBMS yuav coj li cas thaum muaj huab cua phem DELETE, nws yuav kuaj li cas.
Checkpoint nyob rau hauv Russia yog checkpoints.
Piv txwv li: DELETE ob peb lab kab los ntawm qhov ntsuas, kab yog "sib cais" thoob plaws nplooj ntawv.
Ntawm no yog ib qho piv txwv. Nov yog qee qhov hauv paus. Thiab nrog lub neej ntawd teeb tsa ntawm 1 gigabyte rau max_wal_size, nws yog qhov tseeb heev tias peb cov disks mus rau lub txee rau kev kaw. Daim duab no yog ib qho tsos mob ntawm tus neeg mob hnyav heev, uas yog, nws tau hnov ββββmob heev. Thiab muaj ib qho kev ua haujlwm nkaus xwb, tsuas yog DELETE ntawm ntau lab kab.
Yog hais tias xws li ib tug ua hauj lwm raug tso cai nyob rau hauv prod, ces peb yuav cia li pw, vim hais tias nws yog tseeb hais tias ib tug DELETE tua peb nyob rau hauv lub regiment.
Ntxiv mus, qhov twg 16 gigabytes, nws yog qhov tseeb tias cov hniav tau ploj mus lawm. Cov hniav twb zoo lawm, uas yog, peb khob lub qab nthab, tab sis tsis yog li ntawd phem. Muaj qee qhov kev ywj pheej nyob ntawd. Ntawm sab xis yog cov ntaub ntawv. Thiab tus naj npawb ntawm kev ua haujlwm - daim duab thib ob. Thiab nws yog qhov tseeb tias peb twb ua pa yooj yim me ntsis thaum 16 gigabytes.
Thiab qhov twg 64 gigabytes tuaj yeem pom tias nws tau dhau los ua zoo dua. Twb tau cov hniav tau hais, muaj ntau txoj hauv kev kom muaj sia nyob lwm yam haujlwm thiab ua ib yam dab tsi nrog lub disk.
Vim licas?
Kuv yuav dhia mus rau hauv cov ntsiab lus me ntsis, tab sis lub ntsiab lus no, yuav ua li cas los tswj qhov chaw kuaj xyuas, tuaj yeem ua rau tag nrho daim ntawv tshaj tawm, yog li kuv yuav tsis thauj khoom ntau, tab sis kuv yuav piav qhia me ntsis txog qhov teeb meem dab tsi muaj.
Yog tias qhov chaw kuaj xyuas tshwm sim ntau dhau lawm, thiab peb hloov kho peb cov kab tsis sib txuas, tab sis pom los ntawm kev ntsuas, uas yog qhov zoo, vim tias peb tsis tshem tag nrho cov lus, ces nws yuav tshwm sim tias thaum xub thawj peb kov thawj nplooj ntawv, tom qab ntawd tus txhiab, thiab ces rov qab mus rau thawj. Thiab yog hais tias nruab nrab ntawm cov kev mus ntsib thawj nplooj ntawv, checkpoint twb tau khaws cia rau disk, ces nws yuav cawm nws dua, vim hais tias peb tau txais nws qias neeg zaum ob.
Thiab peb yuav yuam cov chaw kuaj xyuas kom txuag tau ntau zaus. Yuav ua li cas yuav muaj redundant kev khiav hauj lwm rau nws.
Tab sis qhov ntawd tsis yog tag nrho. Nplooj ntawv yog 8 kilobytes hauv Postgres thiab 4 kilobytes hauv Linux. Thiab muaj qhov full_page_writes teeb tsa. Nws yog enabled los ntawm lub neej ntawd. Thiab qhov no yog qhov tseeb, vim hais tias yog tias peb tig nws tawm, ces muaj qhov txaus ntshai uas tsuas yog ib nrab ntawm nplooj ntawv yuav raug cawm yog tias nws tsoo.
Tus cwj pwm ntawm kev sau ntawv rau WAL ntawm lub log pem hauv ntej yog xws li thaum peb muaj qhov chaw kuaj xyuas thiab peb hloov nplooj ntawv thawj zaug, tag nrho nplooj ntawv, piv txwv li, tag nrho 8 kilobytes, nkag mus rau hauv lub log pem hauv ntej, txawm hais tias peb tsuas yog hloov cov nplooj ntawv. kab, uas hnyav 100 bytes. Thiab peb yuav tsum sau tag nrho nplooj ntawv.
Hauv kev hloov pauv tom ntej yuav tsuas muaj ib qho tshwj xeeb tuple, tab sis thawj zaug peb sau txhua yam.
Thiab, raws li, yog tias qhov chaw kuaj xyuas tau tshwm sim dua, ces peb yuav tsum pib txhua yam ntawm kos dua thiab thawb tag nrho nplooj ntawv. Nrog cov chaw kuaj xyuas ntau zaus, thaum peb taug kev los ntawm tib nplooj ntawv, full_page_writes = ntawm yuav ntau dua li qhov nws ua tau, piv txwv li peb tsim ntau WAL. Ntau tshaj yog xa mus rau replicas, mus rau lub archive, rau disk.
Thiab, raws li, peb muaj ob redundancy.
Yog tias peb nce max_wal_size, nws hloov tawm tias peb ua kom yooj yim rau ob qho tib si checkpoint thiab wal writer. Thiab qhov ntawd zoo heev.
Cia peb muab tso rau hauv terabyte thiab nyob nrog nws. Dab tsi yog qhov phem? Qhov no yog qhov phem, vim hais tias nyob rau hauv cov ntaub ntawv ntawm ib tug tsis ua hauj lwm, peb yuav nce mus rau lub sij hawm, vim hais tias lub checkpoint tau ntev dhau los thiab ntau twb tau hloov lawm. Thiab peb yuav tsum ua txhua yam no REDO. Thiab yog li peb ua qhov kev sim thib ob.
Peb ua haujlwm thiab pom thaum lub chaw kuaj xyuas yuav ua tiav, peb tua -9 Postgres ntawm lub hom phiaj.
Thiab tom qab ntawd peb pib nws dua, thiab saib ntev npaum li cas nws yuav sawv ntawm cov cuab yeej no, piv txwv li nws yuav REDO ntau npaum li cas hauv qhov xwm txheej phem no.
Ob zaug kuv yuav nco ntsoov tias qhov xwm txheej tsis zoo. Ua ntej, peb tsoo txoj cai ua ntej lub chaw kuaj xyuas dhau lawm, yog li peb muaj ntau yam poob. Thiab qhov thib ob, peb tau ua haujlwm loj heev. Thiab yog tias qhov chaw kuaj xyuas tau nyob rau lub sijhawm, ces, feem ntau yuav, WAL tsawg dua yuav raug tsim tawm txij li qhov chaw kuaj xyuas kawg. Ntawd yog, nws yog ob tus swb.
Peb ntsuas qhov xwm txheej zoo li no rau qhov sib txawv max_wal_size qhov ntau thiab tsawg thiab nkag siab tias yog max_wal_size yog 64 gigabytes, ces nyob rau hauv ob qhov phem tshaj peb yuav nce rau 10 feeb. Thiab peb xav seb nws haum rau peb lossis tsis. Nov yog lus nug ua lag luam. Peb yuav tsum tau qhia daim duab no rau cov neeg saib xyuas kev txiav txim siab ua lag luam thiab nug, βPeb tuaj yeem pw ntev npaum li cas thaum muaj teeb meem? Peb puas tuaj yeem pw hauv qhov xwm txheej phem tshaj li 3-5 feeb? Thiab koj txiav txim siab.
Thiab ntawm no yog qhov nthuav qhia. Peb muaj ob peb tsab ntawv ceeb toom txog Patroni ntawm lub rooj sab laj. Thiab tej zaum koj siv nws. Qhov no yog autofailover rau Postgres. GitLab thiab Data Egret tham txog qhov no.
Thiab yog tias koj muaj autofailover uas tuaj hauv 30 vib nas this, tej zaum peb tuaj yeem pw 10 feeb? Vim tias peb yuav hloov mus rau qhov hloov pauv ntawm qhov taw tes no, thiab txhua yam yuav zoo. Qhov no yog lub ntsiab lus moot. Kuv tsis paub meej cov lus teb. Kuv tsuas xav tias lub ncauj lus no tsis yog nyob ib puag ncig kev sib tsoo xwb.
Yog tias peb muaj kev rov qab los ntev tom qab ua tsis tiav, ces peb yuav tsis xis nyob hauv ntau qhov xwm txheej. Piv txwv li, hauv tib qhov kev sim, thaum peb ua ib yam dab tsi thiab qee zaum yuav tsum tau tos 10 feeb.
Kuv tseem yuav tsis mus deb dhau, txawm tias peb muaj autofailover. Raws li txoj cai, cov nqi xws li 64, 100 gigabytes yog cov txiaj ntsig zoo. Qee zaum nws tseem tsim nyog xaiv tsawg dua. Nyob rau hauv dav dav, qhov no yog ib tug subtle science.
Txhawm rau ua qhov no, piv txwv li, max_wal_size = 1, 8, koj yuav tsum rov ua haujlwm ntau ntau zaus. Koj ua nws. Thiab ntawm tib lub hauv paus koj xav ua nws dua, tab sis koj twb deleted txhua yam. Yuav ua li cas?
Kuv mam li tham tom qab txog peb qhov kev daws teeb meem, peb yuav ua li cas thiaj li rov hais dua hauv cov xwm txheej zoo li no. Thiab qhov no yog txoj hauv kev kom raug.
Tab sis qhov no, peb muaj hmoo. Yog tias, raws li nws hais ntawm no "BEGIN, DELETE, ROLLBACK", ces peb tuaj yeem rov DELETE. Qhov ntawd yog, yog tias peb tso tseg nws tus kheej, ces peb tuaj yeem rov ua dua. Thiab lub cev ntawm koj cov ntaub ntawv yuav pw hauv tib qhov chaw. Koj tseem tsis tau muaj mob. Koj tuaj yeem rov ua dua li DELETEs.
Qhov no DELETE nrog ROLLBACK yog qhov zoo tagnrho rau kev kuaj xyuas qhov ntsuas, txawm tias koj tsis muaj cov chaw kuaj mob uas siv tau zoo.
Peb ua ib lub phaj nrog ib kem "i". Postgres muaj cov khoom siv hluav taws xob. Lawv pom tsis tau tshwj tsis yog hais tshwj xeeb rau. Cov no yog: ctid, xmid, xmax.
Ctid yog qhov chaw nyob ntawm lub cev. Zero page, thawj tuple hauv nplooj ntawv.
Nws tuaj yeem pom tau tias tom qab ROOLBACK lub tuple tseem nyob hauv tib qhov chaw. Qhov ntawd yog, peb tuaj yeem sim dua, nws yuav coj zoo li qub. Qhov no yog qhov tseem ceeb.
Xmax yog lub sijhawm tuag ntawm tuple. Nws tau stamped, tab sis Postgres paub tias qhov kev hloov pauv tau rov qab los, yog li nws tsis muaj teeb meem yog tias nws yog 0 lossis nws yog kev hloov pauv rov qab. Qhov no qhia tau hais tias nws muaj peev xwm rov hais dua DELETE thiab tshawb xyuas cov haujlwm ntau ntawm cov kev coj ua. Koj tuaj yeem ua database lab rau cov neeg pluag.
Qhov no yog hais txog programmers. Txog DBA, ib yam nkaus, lawv ib txwm thuam cov programmer rau qhov no: "Vim li cas koj ua haujlwm ntev thiab nyuaj?". Qhov no yog ib lub ntsiab lus sib txawv kiag li. Ib txwm muaj kev tswj hwm, thiab tam sim no yuav muaj kev txhim kho.
Obviously, peb tsis tau tawg ua tej daim. Nws yog qhov tseeb. Nws yog tsis yooj yim sua kom tsis txhob rhuav xws DELETE rau ib heap ntawm tsheej lab ntawm cov kab rau hauv qhov chaw. Nws yuav ua tiav rau 20 feeb, thiab txhua yam yuav pw. Tab sis, hmoov tsis, txawm tias cov neeg tsim khoom paub dhau los ua yuam kev, txawm tias nyob hauv cov tuam txhab loj heev.
Vim li cas thiaj tseem ceeb rau kev tawg?
-
Yog tias peb pom tias disk nyuaj, ces cia nws qeeb. Thiab yog tias peb tawg, ces peb tuaj yeem ntxiv ncua sijhawm, peb tuaj yeem ua kom qeeb qeeb.
-
Thiab peb yuav tsis thaiv lwm tus rau lub sijhawm ntev. Qee zaum nws tsis muaj teeb meem, yog tias koj tshem tawm cov khib nyiab tiag tiag uas tsis muaj leej twg ua haujlwm nrog, feem ntau koj yuav tsis thaiv leej twg tsuas yog ua haujlwm autovacuum, vim tias nws yuav tos kom ua tiav. Tab sis yog tias koj tshem tawm ib yam dab tsi uas lwm tus tuaj yeem thov, ces lawv yuav raug thaiv, yuav muaj qee yam kev sib txuas lus. Kev lag luam ntev yuav tsum zam ntawm cov vev xaib thiab cov ntawv thov mobile.
Qhov no yog qhov nthuav. Kuv feem ntau pom tias cov neeg tsim khoom nug: "Kuv yuav xaiv lub pob loj li cas?".
Nws yog qhov tseeb tias qhov loj ntawm cov pob khoom loj dua, qhov me dua qhov kev sib pauv nyiaj siv ua haujlwm, piv txwv li, cov nyiaj siv ua haujlwm ntxiv los ntawm kev lag luam. Tab sis tib lub sijhawm, lub sijhawm nce rau qhov kev sib pauv no.
Kuv muaj txoj cai yooj yim heev: siv ntau npaum li koj tuaj yeem ua tau, tab sis tsis txhob mus dhau executables ib ob.
Vim li cas ib pliag? Cov lus piav qhia yog qhov yooj yim heev thiab nkag siab rau txhua tus, txawm tias cov neeg tsis muaj txuj ci. Peb pom ib qho tshuaj tiv thaiv. Cia peb siv 50 milliseconds. Yog hais tias ib yam dab tsi tau hloov, ces peb lub qhov muag yuav hnov. Yog tsawg, ces nyuaj dua. Yog tias ib yam dab tsi teb tom qab 100 milliseconds, piv txwv li, koj nyem tus nas, thiab nws teb koj tom qab 100 milliseconds, koj twb xav tias qhov qeeb me ntsis. Ib tug thib ob twb perceived li brakes.
Raws li, yog tias peb rhuav tshem peb cov haujlwm loj rau hauv 10-thib ob qhov tawg, ces peb muaj kev pheej hmoo tias peb yuav thaiv ib tus neeg. Thiab nws yuav ua haujlwm rau ob peb feeb, thiab tib neeg yuav pom nws lawm. Yog li ntawd, kuv xav tsis txhob ua ntau tshaj ib ob. Tab sis nyob rau tib lub sij hawm, tsis txhob ua txhaum nws heev finely, vim hais tias cov kev pauv nyiaj siv ua haujlwm yuav pom tau. Lub hauv paus yuav nyuaj dua, thiab lwm yam teeb meem sib txawv yuav tshwm sim.
Peb xaiv qhov loj ntawm pob. Hauv txhua kis, peb tuaj yeem ua nws txawv. tuaj yeem ua haujlwm. Thiab peb ntseeg hais tias qhov ua tau zoo ntawm kev ua ntawm ib pob. Ntawd yog, peb ua DELETE ntawm ib pob lossis UPDATE.
Los ntawm txoj kev, txhua yam kuv hais txog tsis yog tsuas yog DELETE. Raws li koj tau twv, cov no yog cov haujlwm ntau ntawm cov ntaub ntawv.
Thiab peb pom tias txoj kev npaj zoo heev. Koj tuaj yeem pom qhov ntsuas ntsuas ntsuas, ntsuas ntsuas ntsuas tsuas yog qhov zoo dua. Thiab peb muaj me me ntawm cov ntaub ntawv koom nrog. Thiab tsawg tshaj li qhov thib ob ua tiav. Super.
Thiab peb tseem yuav tsum tau ua kom paub tseeb tias tsis muaj degradation. Nws tshwm sim tias thawj pob khoom sai sai ua haujlwm tawm, thiab tom qab ntawd nws yuav ua rau mob hnyav, hnyav thiab hnyav dua. Cov txheej txheem yog li ntawd koj yuav tsum tau sim ntau heev. Qhov no yog raws nraim li cas database labs yog rau.
Thiab peb tseem yuav tau npaj ib yam dab tsi kom nws yuav cia peb ua raws li qhov no kom raug hauv kev tsim khoom. Piv txwv li, peb tuaj yeem sau lub sijhawm nyob rau hauv lub cav, peb tuaj yeem sau qhov peb tam sim no thiab leej twg peb tau tam sim no tshem tawm. Thiab qhov no yuav ua rau peb nkag siab tias yuav muaj dab tsi tshwm sim tom qab. Thiab nyob rau hauv cov ntaub ntawv ib yam dab tsi mus tsis ncaj ncees lawm, sai sai nrhiav qhov teeb meem.
Yog tias peb yuav tsum tau kuaj xyuas qhov ua tau zoo ntawm kev thov thiab peb yuav tsum tau rov hais dua ntau zaus, ces muaj xws li cov phooj ywg bot. Nws twb npaj lawm. Nws yog siv los ntawm kaum tawm tus neeg tsim khoom txhua hnub. Thiab nws paub yuav ua li cas muab cov ntaub ntawv loj terabyte ntawm kev thov hauv 30 vib nas this, koj tus kheej daim ntawv. Thiab koj tuaj yeem rho tawm qee yam nyob ntawd thiab hais RESET, thiab rho tawm nws dua. Koj tuaj yeem sim nrog nws li no. Kuv pom lub neej yav tom ntej rau qhov no. Thiab peb twb ua lawm.
Dab tsi yog kev faib cov tswv yim? Kuv pom 3 cov tswv yim sib faib sib txawv uas cov neeg tsim tawm ntawm pob siv.
Thawj tus yog yooj yim heev. Peb muaj tus lej ID. Thiab cia peb rhuav nws mus rau hauv qhov sib txawv thiab ua haujlwm nrog qhov ntawd. Lub downside yog meej. Hauv thawj ntu, peb yuav muaj 100 kab ntawm cov khib nyiab tiag, hauv 5 kab thib ob lossis tsis tag, lossis tag nrho 1 kab yuav dhau los ua khib nyiab. Kev ua haujlwm tsis sib xws heev, tab sis nws yooj yim tawg. Lawv coj tus ID siab tshaj plaws thiab tsoo nws. Qhov no yog txoj kev tsis ncaj ncees.
Qhov thib ob lub tswv yim yog kev sib npaug. Nws yog siv hauv Gitlab. Lawv coj thiab luam lub rooj. Peb pom cov ciam teb ntawm cov pob ID kom txhua pob muaj 10 cov ntaub ntawv. Thiab muab lawv tso rau hauv ib txoj kab. Thiab ces peb txheej txheem. Koj tuaj yeem ua qhov no hauv ntau cov xov.
Hauv thawj lub tswv yim, ib yam nkaus, los ntawm txoj kev, koj tuaj yeem ua qhov no hauv ntau txoj xov. Nws tsis yog qhov nyuaj.
Tab sis muaj qhov txias dua thiab zoo dua mus kom ze. Qhov no yog peb lub tswv yim. Thiab thaum ua tau, nws yog qhov zoo dua los xaiv nws. Peb ua qhov no los ntawm kev ntsuas tshwj xeeb. Nyob rau hauv cov ntaub ntawv no, nws yuav feem ntau yuav yog ib qho Performance index raws li peb cov khib nyiab thiab ID. Peb yuav suav nrog tus ID kom nws yog ib qho index tsuas yog scan kom peb tsis mus rau heap.
Feem ntau, index tsuas yog scan sai dua qhov ntsuas ntsuas.
Thiab peb pom sai sai peb cov IDs uas peb xav tshem tawm. BATCH_SIZE peb xaiv ua ntej. Thiab peb tsis tsuas tau txais lawv, peb tau txais lawv nyob rau hauv ib tug tshwj xeeb txoj kev thiab tam sim ntawd hack lawv. Tab sis peb kaw ces yog lawv twb muab xauv lawm ces peb tsis kaw lawm, tab sis txav mus los coj cov tom ntej. Qhov no yog rau hloov tshiab hla xauv. Qhov no super feature ntawm Postgres tso cai rau peb ua hauj lwm nyob rau hauv ob peb threads yog hais tias peb xav tau. Nws yog ua tau nyob rau hauv ib tug kwj. Thiab ntawm no muaj CTE - qhov no yog ib qho kev thov. Thiab peb muaj kev tshem tawm tiag tiag mus rau hauv pem teb thib ob ntawm CTE no - returning *. Koj tuaj yeem xa rov qab id, tab sis nws zoo dua *yog tias koj tsis muaj cov ntaub ntawv ntau ntawm txhua kab.
Vim li cas peb thiaj xav tau nws? Nov yog qhov peb yuav tsum tau qhia rov qab. Tam sim no peb tau tshem tawm ntau cov kab hauv qhov tseeb. Thiab peb muaj ciam teb los ntawm ID lossis los ntawm created_at zoo li no. Koj tuaj yeem ua min, max. Lwm yam tuaj yeem ua tau. Koj tuaj yeem ntim ntau ntawm no. Thiab nws yooj yim heev rau kev saib xyuas.
Muaj ib daim ntawv ntxiv txog qhov ntsuas. Yog tias peb txiav txim siab tias peb xav tau qhov ntsuas tshwj xeeb rau txoj haujlwm no, ces peb yuav tsum ua kom paub tseeb tias nws tsis lwj heap nkaus xwb tuples tshiab. Ntawd yog, Postgres muaj cov txheeb cais li no. Qhov no tuaj yeem pom hauv pg_stat_user_tables rau koj lub rooj. Koj tuaj yeem pom tias cov kev hloov tshiab kub tau siv los yog tsis.
Muaj cov xwm txheej thaum koj qhov ntsuas tshiab tuaj yeem txiav lawv tawm. Thiab koj muaj tag nrho lwm yam kev hloov tshiab uas twb ua haujlwm lawm, qeeb qeeb. Tsis yog vim tias qhov ntsuas tau tshwm sim (txhua qhov ntsuas qis qis qis me ntsis, tab sis me ntsis), tab sis ntawm no nws tseem ua rau nws puas. Thiab nws yog tsis yooj yim sua kom optimization tshwj xeeb rau lub rooj no. Qhov no tshwm sim qee zaum. Qhov no yog ib tug subtlety uas tsawg tus neeg nco. Thiab qhov rake no yooj yim rau kauj ruam. Qee zaum nws tshwm sim tias koj yuav tsum nrhiav txoj hauv kev los ntawm lwm sab thiab tseem ua yam tsis muaj qhov ntsuas tshiab no, lossis ua lwm qhov ntsuas, lossis lwm txoj hauv kev, piv txwv li, koj tuaj yeem siv txoj kev thib ob.
Tab sis qhov no yog qhov zoo tshaj plaws lub tswv yim, yuav ua li cas faib mus rau hauv batch thiab tua ntawm batches nrog ib tug thov, tshem tawm me ntsis, thiab lwm yam.
Kev lag luam ntev
Blocked autovacuum -
thaiv qhov teeb meem -
Qhov yuam kev #5 yog qhov loj. Nikolai los ntawm Okmeter tham txog Postgres saib xyuas. Qhov zoo tshaj plaws Postgres saib xyuas, hmoov tsis, tsis muaj nyob. Ib txhia nyob ze, ib txhia nyob deb. Okmeter yog qhov zoo nkauj ze rau qhov zoo meej, tab sis ntau qhov ploj lawm thiab yuav tsum tau ntxiv. Koj yuav tsum tau npaj rau qhov no.
Piv txwv li, cov tuples tuag yog qhov zoo tshaj plaws saib xyuas. Yog hais tias koj muaj ntau yam tuag nyob rau hauv lub rooj, ces ib yam dab tsi tsis ncaj ncees lawm. Nws yog qhov zoo dua los teb tam sim no, txwv tsis pub yuav muaj kev degradation, thiab peb tuaj yeem pw. Nws tshwm sim.
Yog tias muaj IO loj, ces nws pom tseeb tias qhov no tsis zoo.
Kev lag luam ntev heev. Kev lag luam ntev yuav tsum tsis pub tso cai ntawm OLTP. Thiab ntawm no yog qhov txuas mus rau ib qho snippet uas tso cai rau koj coj cov kab lus no thiab twb ua qee qhov kev taug qab ntawm kev lag luam ntev.
Vim li cas kev lag luam ntev tsis zoo? Vim tias tag nrho cov xauv yuav raug tso tawm tsuas yog thaum kawg. Thiab peb ntsia txhua tus. Ntxiv rau, peb thaiv autovacuum rau txhua lub rooj. Nws tsis zoo kiag li. Txawm hais tias koj muaj kub standby enabled ntawm lub replica, nws tseem phem. Feem ntau, tsis pom qhov twg zoo dua kom tsis txhob ua lag luam ntev.
Yog tias peb muaj ntau lub rooj uas tsis muaj lub tshuab nqus tsev, ces peb yuav tsum muaj kev ceeb toom. Nov yog qhov xwm txheej no ua tau. Peb tuaj yeem cuam tshuam ncaj qha rau kev ua haujlwm ntawm autovacuum. Qhov no yog ib qho snippet los ntawm Avito, uas kuv tau txhim kho me ntsis. Thiab nws tau dhau los ua ib qho cuab yeej nthuav kom pom tias peb muaj dab tsi nrog autovacuum. Piv txwv li, qee lub rooj tos nyob ntawd thiab yuav tsis tos lawv tig. Koj kuj yuav tsum tau muab tso rau hauv kev saib xyuas thiab muaj kev ceeb toom.
Thiab teeb meem blocks. Hav zoov ntawm cov ntoo thaiv. Kuv nyiam coj ib yam dab tsi los ntawm ib tug neeg thiab txhim kho nws. Ntawm no kuv coj ib tug txias recursive CTE los ntawm Data Egret uas qhia tau hais tias ib tug hav zoov ntawm xauv ntoo. Qhov no yog cov cuab yeej kuaj mob zoo. Thiab ntawm nws lub hauv paus, koj tuaj yeem tsim kev saib xyuas. Tab sis qhov no yuav tsum ua tib zoo ua. Koj yuav tsum ua ib nqe lus me me_timeout rau koj tus kheej. Thiab lock_timeout yog ntshaw.
Qee zaum tag nrho cov kev ua yuam kev no tshwm sim hauv cov lej.
Hauv kuv lub tswv yim, qhov yuam kev tseem ceeb ntawm no yog kev koom tes. Nws yog koom haum, vim hais tias cov txheej txheem tsis rub. Qhov no yog tus naj npawb 2 - lawv tau txheeb xyuas qhov tsis ncaj ncees lawm.
Peb tau txheeb xyuas qhov tsis ncaj ncees lawm, vim tias peb tsis muaj cov khoom siv clone, uas yooj yim rau kev kuaj xyuas. Tus tsim tawm yuav tsis muaj kev nkag mus rau kev tsim khoom txhua.
Thiab peb tau kuaj tsis nyob ntawd. Yog tias peb tau kuaj nyob ntawd, peb yuav pom nws tus kheej. Tus tsim tawm pom nws tag nrho txawm tias tsis muaj DBA yog tias nws tshawb xyuas nws hauv qhov chaw zoo, qhov twg muaj cov ntaub ntawv tib yam thiab qhov chaw zoo ib yam. Nws yuav pom tag nrho cov kev degradation no thiab nws yuav txaj muag.
Xav paub ntau ntxiv txog autovacuum. Tom qab peb tau ua qhov cheb loj ntawm ntau lab kab, peb tseem yuav tau ua REPACK. Qhov no tseem ceeb tshwj xeeb tshaj yog rau cov indexes. Lawv yuav xav tsis zoo tom qab peb ntxuav txhua yam nyob ntawd.
Thiab yog tias koj xav nqa rov qab ua haujlwm tu txhua hnub, ces kuv xav kom ua nws ntau zaus, tab sis me dua. Nws tuaj yeem yog ib zaug ib feeb lossis ntau zaus me ntsis. Thiab koj yuav tsum tau saib xyuas ob yam: tias qhov no tsis muaj qhov yuam kev thiab tias nws tsis poob qab. Qhov ua kom yuam kev uas kuv tau qhia yuav daws qhov no xwb.
Qhov peb ua yog qhib qhov chaw. Nws tau tshaj tawm ntawm GitLab. Thiab peb ua kom cov neeg tuaj yeem tshawb xyuas txawm tias tsis muaj DBA. Peb tab tom ua ib lub chaw kuaj ntaub ntawv, uas yog, peb hu rau lub hauv paus tivthaiv uas Joe tab tom ua haujlwm tam sim no. Thiab koj tuaj yeem rub ib daim qauv ntawm kev tsim khoom. Tam sim no muaj qhov kev siv ntawm Joe rau slack, koj tuaj yeem hais nyob rau ntawd: "piav xws li thiab qhov kev thov" thiab tam sim ntawd tau txais cov txiaj ntsig rau koj daim ntawv luam ntawm cov ntaub ntawv. Koj tuaj yeem DELETE nyob ntawd, thiab tsis muaj leej twg yuav pom nws.
Wb hais tias koj muaj 10 terabytes, peb ua database lab kuj 10 terabytes. Thiab nrog ib txhij 10 terabyte databases, 10 developers tuaj yeem ua haujlwm ib txhij. Txhua tus tuaj yeem ua raws li lawv xav tau. Yuav rho tawm, poob, thiab lwm yam. Qhov ntawd yog qhov kev xav zoo li no. Peb yuav tham txog qhov no tag kis.
Qhov no yog hu ua nyias muab. Qhov no yog kev hloov maj mam. Qhov no yog qee yam kev npau suav uas tshem tawm kev qeeb hauv kev txhim kho, hauv kev sim thiab ua rau lub ntiaj teb zoo dua hauv qhov no. Ntawd yog, nws tsuas yog tso cai rau koj kom tsis txhob muaj teeb meem nrog kev ua haujlwm ntau.
Piv txwv li: 5 terabyte database, tau txais ib daim ntawv luam hauv tsawg dua 30 vib nas this. Thiab nws tsis txawm nyob ntawm qhov loj me, uas yog, nws tsis muaj teeb meem pes tsawg terabytes.
Hnub no koj tuaj yeem mus thiab khawb rau hauv peb cov cuab yeej. Koj tuaj yeem sau npe kom pom dab tsi nyob ntawd. Koj tuaj yeem nruab qhov bot no. Nws yog dawb. Sau.
Lus nug
Feem ntau nyob rau hauv cov xwm txheej tiag tiag nws hloov tawm tias cov ntaub ntawv uas yuav tsum nyob twj ywm hauv lub rooj yog ntau tsawg dua li qhov yuav tsum tau muab tshem tawm. Ntawd yog, nyob rau hauv cov xwm txheej zoo li no, nws feem ntau yooj yim dua rau kev ua raws li txoj hauv kev, thaum nws yooj yim dua los tsim cov khoom tshiab, luam cov ntaub ntawv tsim nyog tsuas yog nyob rau ntawd, thiab rub lub rooj qub. Nws yog qhov tseeb tias qhov programmatic mus kom ze yog xav tau rau lub sijhawm no, thaum koj yuav hloov. Txoj kev no zoo li cas?
Qhov no yog ib txoj hauv kev zoo heev thiab ua haujlwm zoo heev. Nws zoo ib yam li qhov pg_repack ua, nws zoo ib yam li koj yuav tsum ua thaum koj ua IDs 4 bytes. Ntau lub moj khaum tau ua qhov no ob peb xyoos dhau los, thiab tsuas yog cov phiaj xwm loj tuaj, thiab lawv yuav tsum tau hloov mus rau 8 bytes.
Txoj hauj lwm no nyuaj heev. Peb tau ua. Thiab koj yuav tsum ceev faj heev. Muaj cov xauv, thiab lwm yam. Tab sis nws tau ua tiav. Ntawd yog, tus qauv mus kom ze yog mus nrog pg_repack. Koj tshaj tawm daim ntawv lo. Thiab ua ntej koj pib upload cov ntaub ntawv snapshot rau hauv nws, koj kuj tshaj tawm ib lub phaj uas taug qab txhua qhov kev hloov pauv. Muaj qhov ua kom yuam kev uas koj yuav tsis taug qab qee qhov kev hloov pauv. Muaj subtleties. Thiab tom qab ntawd koj hloov los ntawm kev hloov pauv. Yuav muaj kev ncua luv luv thaum peb kaw txhua tus, tab sis feem ntau qhov no tau ua tiav.
Yog tias koj saib pg_repack ntawm GitHub, ces muaj, thaum muaj ib txoj haujlwm los hloov tus ID los ntawm int 4 mus rau int 8, ces muaj ib lub tswv yim siv pg_repack nws tus kheej. Qhov no kuj ua tau, tab sis nws yog me ntsis ntawm hack, tab sis nws yuav ua hauj lwm rau qhov no ib yam nkaus thiab. Koj tuaj yeem cuam tshuam rau qhov ua rau pg_repack siv thiab hais nyob ntawd: "Peb tsis xav tau cov ntaub ntawv no", piv txwv li peb tsuas yog hloov qhov peb xav tau. Thiab ces nws cia li hloov thiab qhov ntawd yog nws.
Nrog rau txoj hauv kev no, peb tseem tau txais daim ntawv theej thib ob ntawm lub rooj, uas cov ntaub ntawv twb tau txheeb xyuas thiab sib npaug sib npaug nrog cov txiaj ntsig zoo nkauj.
Bloat tsis yog tam sim no, nws yog txoj hauv kev zoo. Tab sis kuv paub hais tias muaj kev sim los tsim ib qho automation rau qhov no, piv txwv li los ua ib tug universal tov. Kuv tuaj yeem ua rau koj kov nrog qhov automation no. Nws tau sau hauv Python, uas yog qhov zoo.
Kuv tsuas yog me ntsis ntawm lub ntiaj teb ntawm MySQL, yog li kuv tuaj mloog. Thiab peb siv txoj hauv kev no.
Tab sis nws tsuas yog peb muaj 90%. Yog tias peb muaj 5%, ces nws tsis zoo siv nws.
Ua tsaug rau daim ntawv qhia! Yog tias tsis muaj cov peev txheej los ua kom tiav daim ntawv luam ntawm cov khoom, puas muaj cov algorithm lossis cov qauv los xam cov load lossis loj?
Zoo lus nug. Txog tam sim no, peb muaj peev xwm nrhiav tau ntau-terabyte databases. Txawm hais tias cov khoom siv tsis zoo ib yam, piv txwv li, lub cim xeeb tsawg, processor tsawg thiab disks tsis zoo ib yam, tab sis tseem peb ua. Yog tias tsis muaj qhov twg, ces koj yuav tsum xav. Cia kuv xav txog tag kis, koj tuaj, peb yuav tham, qhov no yog ib lo lus nug zoo.
Ua tsaug rau daim ntawv qhia! Koj thawj zaug pib txog qhov tseeb tias muaj Postgres txias, uas muaj cov kev txwv zoo li no, tab sis nws tab tom txhim kho. Thiab qhov no yog tag nrho ib tug crutch los ntawm thiab loj. Tsis yog qhov no tsis yog txhua qhov kev tsis sib haum xeeb nrog kev txhim kho ntawm Postgres nws tus kheej, uas qee qhov DELETE deferent yuav tshwm sim lossis lwm yam uas yuav tsum khaws cia rau theem qis uas peb tab tom sim ua rau qee qhov kev coj txawv txawv ntawm no?
Yog tias peb tau hais hauv SQL kom tshem tawm lossis hloov kho ntau cov ntaub ntawv hauv ib qho kev sib pauv, ces Postgres yuav faib nws li cas? Peb lub cev muaj kev txwv hauv kev ua haujlwm. Peb tseem yuav ua tau ntev. Thiab peb yuav kaw lub sijhawm no, thiab lwm yam.
Ua tiav nrog indexes.
Kuv tuaj yeem xav tias tib lub checkpoint tuning tuaj yeem siv tau. Ib hnub nws yuav yog. Tab sis tom qab ntawd kuv tsis tshua nkag siab cov lus nug.
Cov lus nug yog, puas muaj xws li ib tug vector ntawm kev loj hlob uas mus ntawm no thiab muaj, thiab ntawm no koj li mus parallel? Cov. Lawv tseem tsis tau xav txog nws?
Kuv tham txog tej ntsiab cai uas siv tau tam sim no. Muaj lwm bot , nrog qhov no koj tuaj yeem ua automated checkpoint tuning. Nws puas yuav muaj hnub nyob hauv Postgres? Kuv tsis paub, nws tseem tsis tau tham txog. Peb tseem nyob deb ntawm qhov ntawd. Tab sis muaj cov kws tshawb fawb uas tsim cov tshuab tshiab. Thiab lawv shove peb mus rau hauv tsis siv neeg indexes. Muaj kev txhim kho. Piv txwv li, koj tuaj yeem saib ntawm qhov pib tuning. Nws xaiv parameters txiav. Tab sis nws yuav tsis ua checkpoint tuning rau koj tsis tau. Ntawd yog, nws yuav tuaj tos rau kev ua haujlwm, plhaub tsis, thiab lwm yam.
Thiab rau kev ntsuas qhov ntsuas, koj tuaj yeem ua qhov no: yog tias koj muaj ntau txhiab pawg thiab cov khoom siv sib txawv, cov tshuab virtual sib txawv hauv huab, koj tuaj yeem siv peb cov bot. ua automation. Thiab max_wal_size yuav raug xaiv raws li koj lub hom phiaj nqis tau txais. Tab sis kom deb li deb qhov no tsis txawm nyob ze rau hauv lub hauv paus, hmoov tsis.
Nyob zoo tav su Koj tau tham txog qhov txaus ntshai ntawm kev ua lag luam ntev. Koj tau hais tias autovacuum raug thaiv thaum tshem tawm. Nws ua phem rau peb li cas? Vim peb tab tom tham ntxiv txog kev tso chaw thiab muaj peev xwm siv tau. Dab tsi ntxiv peb ploj lawm?
Autovacuum tej zaum tsis yog qhov teeb meem loj tshaj plaws ntawm no. Thiab qhov tseeb tias kev lag luam ntev tuaj yeem kaw lwm yam kev lag luam, qhov no muaj peev xwm txaus ntshai dua. Tej zaum nws yuav los sis tsis ntsib. Yog nws ntsib, ces nws yuav phem heev. Thiab nrog autovacuum - qhov no kuj yog ib qho teeb meem. Muaj ob qhov teeb meem nrog kev lag luam ntev hauv OLTP: xauv thiab autovacuum. Thiab yog tias koj muaj kev tawm tswv yim kub standby qhib rau ntawm qhov hloov pauv, ces koj tseem yuav tau txais lub kaw lus autovacuum ntawm tus tswv, nws yuav tuaj txog ntawm qhov hloov pauv. Tab sis tsawg kawg yuav tsis muaj qhov xauv. Thiab yuav muaj loks. Peb tab tom tham txog cov ntaub ntawv hloov pauv, yog li cov xauv xauv yog qhov tseem ceeb ntawm no. Thiab yog hais tias qhov no yog tag nrho rau ntev, ntev, ces ntau thiab ntau muas raug kaw. Lawv tuaj yeem nyiag lwm tus. Thiab lok ntoo tshwm. Kuv muab ib qhov txuas rau qhov snippet. Thiab qhov teeb meem no dhau los ua pom sai dua qhov teeb meem nrog autovacuum, uas tuaj yeem tsuas yog sib sau.
Ua tsaug rau daim ntawv qhia! Koj tau pib koj daim ntawv tshaj tawm los ntawm kev hais tias koj kuaj tsis raug. Peb txuas ntxiv peb lub tswv yim hais tias peb yuav tsum coj tib yam khoom siv, nrog lub hauv paus zoo ib yam. Cia peb hais tias peb muab tus tsim tawm lub hauv paus. Thiab nws tau ua raws li qhov kev thov. Thiab nws zoo nkaus li zoo. Tab sis nws tsis kuaj rau kev ua neej nyob, tab sis rau kev ua neej, piv txwv li, peb muaj 60-70%. Thiab txawm hais tias peb siv qhov kev ntsuas no, nws tsis ua haujlwm zoo heev.
Muaj ib tus kws tshaj lij ntawm pab pawg thiab siv DBA cov kws tshaj lij uas tuaj yeem kwv yees tias yuav muaj dab tsi tshwm sim nrog cov ntaub ntawv keeb kwm yav dhau yog qhov tseem ceeb. Thaum peb tsuas yog tsav peb cov kev hloov huv huv, peb pom daim duab. Tab sis ib tug ntau tshaj mus kom ze, thaum peb ua tib yam dua, tab sis nrog ib tug load simulated nrog ntau lawm. Nws yog txias heev. Txog thaum ntawd, koj yuav tsum loj hlob. Nws zoo li ib tug neeg laus. Peb tsuas yog saib seb peb muaj dab tsi thiab tseem saib seb peb puas muaj peev txheej txaus. Qhov ntawd yog ib lo lus nug zoo.
Thaum peb twb ua ib tug khib nyiab xaiv thiab peb muaj, piv txwv li, ib tug deleted chij
Qhov no yog qhov autovacuum ua tau hauv Postgres.
Auj, nws puas ua nws?
Autovacuum yog cov khoom khib nyiab.
Ua tsaug!
Ua tsaug rau daim ntawv qhia! Puas muaj kev xaiv los tsim cov ntaub ntawv tam sim ntawd nrog kev faib tawm hauv txoj hauv kev uas tag nrho cov khib nyiab tau qias neeg los ntawm lub rooj loj qhov chaw mus rau sab?
Tau kawg muaj.
Puas muaj peev xwm tiv thaiv peb tus kheej yog tias peb tau kaw lub rooj uas yuav tsum tsis txhob siv?
Tau kawg muaj. Tab sis nws zoo li cov lus nug ntawm qaib thiab qe. Yog tias peb txhua tus paub tias yuav muaj dab tsi tshwm sim yav tom ntej, yog li ntawd, peb yuav ua txhua yam txias. Tab sis kev lag luam hloov pauv, muaj cov kab tshiab, kev thov tshiab. Thiab tom qab ntawd - oops, peb xav tshem nws. Tab sis qhov xwm txheej zoo tshaj plaws, hauv lub neej nws tshwm sim, tab sis tsis tas li. Tab sis tag nrho nws yog ib lub tswv yim zoo. Tsuas yog truncate thiab qhov ntawd yog nws.
Tau qhov twg los: www.hab.com