DBA bot Joe. Anatoly Stansler (Postgres.ai)

Yuav ua li cas tus tsim tawm backend nkag siab tias cov lus nug SQL yuav ua haujlwm zoo ntawm "prod"? Hauv cov tuam txhab loj lossis loj hlob sai, tsis yog txhua tus tuaj yeem nkag mus rau "cov khoom lag luam". Thiab nrog kev nkag mus, tsis yog txhua qhov kev thov tuaj yeem raug kuaj xyuas tsis tu ncua, thiab tsim cov ntawv luam ntawm cov ntaub ntawv feem ntau siv sijhawm ntau teev. Txhawm rau daws cov teeb meem no, peb tsim DBA dag - Joe. Nws twb tau ua tiav tiav hauv ntau lub tuam txhab thiab pab ntau tshaj li kaum tus neeg tsim khoom.

Yees duab:

Nyob zoo sawv daws! Kuv lub npe yog Anatoly Stansler. Kuv ua haujlwm rau ib lub tuam txhab postgres.ai. Peb tau cog lus tias yuav ua kom cov txheej txheem kev loj hlob sai los ntawm kev tshem tawm cov kev ncua uas cuam tshuam nrog kev ua haujlwm ntawm Postgres los ntawm cov neeg tsim khoom, DBAs thiab QAs.

Peb muaj cov neeg siv khoom zoo thiab niaj hnub no ib feem ntawm daim ntawv tshaj tawm yuav mob siab rau cov xwm txheej uas peb tau ntsib thaum ua haujlwm nrog lawv. Kuv yuav tham txog yuav ua li cas peb pab lawv daws teeb meem loj heev.

Thaum peb tab tom txhim kho thiab ua cov neeg tsiv teb tsaws chaw siab, peb nug peb tus kheej cov lus nug: "Qhov kev tsiv teb tsaws no puas yuav tawm mus?". Peb siv kev tshuaj xyuas, peb siv kev paub txog cov neeg ua haujlwm paub ntau dua, cov kws tshaj lij DBA. Thiab lawv tuaj yeem hais tias nws yuav ya los yog tsis.

Tab sis tej zaum nws yuav zoo dua yog tias peb tuaj yeem sim nws tus kheej ntawm daim ntawv loj loj. Thiab niaj hnub no peb yuav cia li tham txog dab tsi txoj hauv kev rau kev sim tam sim no thiab yuav ua li cas nws tuaj yeem ua tau zoo dua thiab nrog cov cuab yeej dab tsi. Peb tseem yuav tham txog qhov zoo thiab qhov tsis zoo ntawm cov txheej txheem zoo li no, thiab peb tuaj yeem kho qhov no.

Leej twg puas tau ua indexes ncaj qha ntawm cov khoom lossis hloov pauv? Heev me ntsis ntawm. Thiab rau leej twg qhov no ua rau qhov tseeb tias cov ntaub ntawv ploj lossis muaj sijhawm poob? Ces koj paub qhov mob no. Ua Vajtswv tsaug muaj backups.

Thawj txoj hauv kev yog kev sim hauv cov khoom. Los yog, thaum tus tsim tawm zaum ntawm lub tshuab hauv zos, nws muaj cov ntaub ntawv xeem, muaj qee yam kev xaiv txwv. Thiab peb yob tawm mus rau qhov khoom, thiab peb tau txais qhov xwm txheej no.

Nws mob, nws kim. Nws yog qhov zoo tshaj plaws tsis yog.

Thiab qhov twg yog qhov zoo tshaj los ua nws?

Cia peb pib ua haujlwm thiab xaiv qee qhov ntawm cov khoom muaj. Los yog qhov zoo tshaj plaws, cia peb coj cov khoom tiag, tag nrho cov ntaub ntawv. Thiab tom qab peb tau tsim nws hauv zos, peb tseem yuav kuaj xyuas qhov chaw.

Qhov no yuav tso cai rau peb tshem tawm qee qhov yuam kev, piv txwv li tiv thaiv lawv los ntawm kev tsim khoom.

Cov teeb meem yog dab tsi?

• Qhov teeb meem yog tias peb qhia qhov kev ua yeeb yam no nrog cov npoj yaig. Thiab feem ntau nws tshwm sim tias koj ua qee yam kev hloov pauv, bam - thiab tsis muaj cov ntaub ntawv, kev ua haujlwm poob qis. Staging yog multi-terabyte. Thiab koj yuav tsum tau tos ntev heev kom nws sawv dua. Thiab peb txiav txim siab ua kom tiav tag kis. Qhov ntawd yog nws, peb muaj kev txhim kho.
• Thiab, tau kawg, peb muaj ntau tus npoj yaig ua haujlwm nyob ntawd, ntau pab pawg. Thiab nws yuav tsum tau ua manually. Thiab qhov no tsis yooj yim.

Thiab nws tsim nyog hais tias peb tsuas muaj ib qho kev sim, ib qho kev txhaj tshuaj, yog tias peb xav ua qee qhov kev hloov pauv ntawm cov ntaub ntawv, kov cov ntaub ntawv, hloov cov qauv. Thiab yog hais tias ib yam dab tsi mus tsis ncaj ncees lawm, yog hais tias muaj ib qho kev ua yuam kev nyob rau hauv lub tsiv teb tsaws, ces peb yuav tsis sai sai rov qab.

Qhov no zoo dua li yav dhau los mus kom ze, tab sis tseem muaj qhov tshwm sim siab uas qee yam kev ua yuam kev yuav mus rau ntau lawm.

Dab tsi tiv thaiv peb los ntawm kev muab txhua tus tsim tawm ib lub rooj sib tw, ib daim ntawv loj loj? Kuv xav tias nws yog qhov tseeb uas tau txais hauv txoj kev.

Leej twg muaj database loj dua ib terabyte? Ntau tshaj li ib nrab ntawm chav tsev.

Thiab nws yog qhov tseeb tias kev khaws cov tshuab rau txhua tus tsim tawm, thaum muaj ntau ntau lawm, yog kim heev, thiab ntxiv rau, nws yuav siv sij hawm ntev.

Peb muaj cov neeg siv khoom uas tau pom tias nws yog ib qho tseem ceeb heev rau kev sim txhua qhov kev hloov pauv ntawm cov ntawv luam tag nrho, tab sis lawv cov ntaub ntawv tsawg dua li ib terabyte, thiab tsis muaj peev txheej los khaws lub rooj sib tw rau txhua tus tsim tawm. Yog li ntawd, lawv yuav tsum rub tawm cov pob tseg hauv zos rau lawv lub tshuab thiab sim ua li no. Nws yuav siv sij hawm ntau heev.

Txawm hais tias koj ua nws hauv kev tsim kho vaj tse, ces rub tawm ib terabyte ntawm cov ntaub ntawv ib teev twg yog qhov zoo heev. Tab sis lawv siv logical dumps, lawv download tau hauv zos los ntawm huab. Rau lawv, qhov ceev yog li 200 gigabytes ib teev. Thiab nws tseem yuav siv sij hawm tig los ntawm lub logic pov tseg, yob li cov indexes, thiab lwm yam.

Tab sis lawv siv txoj hauv kev no vim nws tso cai rau lawv kom cov khoom ntseeg tau.

Peb tuaj yeem ua dab tsi ntawm no? Cia peb sim cov rooj zaum pheej yig thiab muab txhua tus tsim tawm lawv tus kheej lub rooj zaum.

Thiab qhov no yog ua tau.

Thiab nyob rau hauv txoj kev no, thaum peb ua nyias clones rau txhua tus tsim tawm, peb tuaj yeem faib nws ntawm ib lub tshuab. Piv txwv li, yog tias koj muaj 10TB database thiab xav muab rau 10 developers, koj tsis tas yuav muaj XNUMX x XNUMXTB databases. Koj tsuas yog xav tau ib lub tshuab los ua cov ntawv luam tawm nyias nyias rau txhua tus tsim tawm siv ib lub tshuab. Kuv mam li qhia koj seb nws ua haujlwm li cas tom qab me ntsis.

Piv txwv li:

• DB - 4,5 terabytes.

• Peb tuaj yeem tau txais cov ntawv luam tawm hauv 30 vib nas this.

Koj tsis tas yuav tos qhov chaw sim thiab nyob ntawm seb nws loj npaum li cas. Koj tuaj yeem tau txais nws hauv vib nas this. Nws yuav yog ib puag ncig cais kiag li, tab sis cov ntaub ntawv sib qhia ntawm lawv tus kheej.

Qhov no zoo heev. Ntawm no peb tab tom tham txog kev ua khawv koob thiab lub ntiaj teb sib luag.

Hauv peb qhov xwm txheej, qhov no ua haujlwm siv OpenZFS system.

OpenZFS yog daim ntawv theej-on-sau cov ntaub ntawv uas txhawb nqa snapshots thiab clones tawm ntawm lub thawv. Nws yog txhim khu kev qha thiab scalable. Nws yooj yim heev los tswj. Nws tuaj yeem raug xa mus rau hauv ob pab pawg.

Muaj lwm txoj kev xaiv:

• LWM,

• Cia (piv txwv, Pure Storage).

Lub Database Lab kuv tab tom tham txog yog modular. Yuav siv tau los ntawm cov kev xaiv no. Tab sis tam sim no, peb tau tsom mus rau OpenZFS, vim tias muaj teeb meem nrog LVM tshwj xeeb.

Nws ua haujlwm li cas? Es tsis txhob overwriting cov ntaub ntawv txhua lub sij hawm peb hloov nws, peb txuag nws los ntawm tsuas yog kos tias cov ntaub ntawv tshiab no yog los ntawm ib tug tshiab point nyob rau hauv lub sij hawm, ib tug tshiab snapshot.

Thiab nyob rau hauv lub neej yav tom ntej, thaum peb xav rollback los yog peb xav ua ib tug tshiab clone los ntawm ib co qub version, peb tsuas yog hais tias: "OK, muab peb cov blocks ntawm cov ntaub ntawv uas tau cim zoo li no."

Thiab tus neeg siv no yuav ua haujlwm nrog cov ntaub ntawv zoo li no. Nws yuav maj mam hloov lawv, ua nws tus kheej snapshots.

Thiab peb yuav ceg. Txhua tus tsim tawm hauv peb rooj plaub yuav muaj lub sijhawm los muaj nws tus kheej clone uas nws hloov kho, thiab cov ntaub ntawv uas tau muab faib yuav raug muab faib rau txhua tus.

Txhawm rau siv cov txheej txheem no hauv tsev, koj yuav tsum daws ob qhov teeb meem:

• Thawj yog qhov chaw ntawm cov ntaub ntawv, qhov twg koj yuav coj nws los ntawm. Koj tuaj yeem teeb tsa replication nrog ntau lawm. Koj tuaj yeem siv cov thaub qab uas koj tau teeb tsa, kuv vam tias. WAL-E, WAL-G los yog Barman. Thiab txawm tias koj tab tom siv qee yam kev daws teeb meem huab xws li RDS lossis Cloud SQL, koj tuaj yeem siv cov khoom pov tseg. Tab sis peb tseem qhia koj kom siv cov thaub qab, vim tias nrog rau txoj hauv kev no koj tseem yuav khaws cov qauv ntawm cov ntaub ntawv, uas yuav tso cai rau koj los ze zog rau cov metrics uas koj yuav pom hauv kev tsim khoom txhawm rau txhawm rau ntes cov teeb meem uas muaj nyob.

• Qhov thib ob yog qhov uas koj xav tuav lub Database Lab. Nws tuaj yeem yog Huab, nws tuaj yeem yog On-premise. Nws yog ib qho tseem ceeb hais ntawm no tias ZFS txhawb cov ntaub ntawv compression. Thiab nws ua nws zoo heev.

Xav txog tias rau txhua qhov clone, nyob ntawm seb cov haujlwm uas peb ua nrog lub hauv paus, qee yam dev yuav loj hlob. Rau qhov no, dev kuj xav tau qhov chaw. Tab sis vim qhov tseeb tias peb coj lub hauv paus ntawm 4,5 terabytes, ZFS yuav compress nws mus rau 3,5 terabytes. Qhov no tuaj yeem sib txawv nyob ntawm qhov chaw. Thiab peb tseem muaj chaw rau dev.

Xws li ib qho system tuaj yeem siv rau ntau qhov xwm txheej.

• Cov no yog cov tsim tawm, DBAs rau cov lus nug validation, rau optimization.

• Qhov no tuaj yeem siv rau hauv QA kev xeem los kuaj ib qho kev tsiv teb tsaws chaw ua ntej peb yob nws tawm mus rau qhov khoom. Thiab peb tseem tuaj yeem tsa qhov chaw tshwj xeeb rau QA nrog cov ntaub ntawv tiag tiag, qhov twg lawv tuaj yeem sim ua haujlwm tshiab. Thiab nws yuav siv sij hawm vib nas this es tsis txhob tos cov sij hawm, thiab tej zaum cov hnub nyob rau hauv tej rooj plaub uas nyias tsis siv.

• Thiab lwm rooj plaub. Yog tias lub tuam txhab tsis muaj cov txheej txheem kev tshuaj ntsuam xyuas, ces peb tuaj yeem cais cov txheej txheem nyias nyias ntawm cov khoom lag luam thiab muab rau cov lus nug ntev lossis cov kev ntsuas tshwj xeeb uas tuaj yeem siv rau hauv kev tshuaj xyuas.

Nrog txoj kev no:

1. Tsawg qhov ua yuam kev ntawm "prod", vim tias peb tau sim txhua qhov kev hloov pauv ntawm cov ntaub ntawv loj.

2. Peb muaj kab lis kev cai ntawm kev sim, vim tias tam sim no koj tsis tas yuav tos sijhawm rau koj tus kheej sawv ntsug.

3. Thiab tsis muaj kev thaiv, tsis muaj kev tos ntawm kev sim. Koj tuaj yeem mus thiab tshawb xyuas. Thiab nws yuav zoo dua li no thaum peb ua kom txoj kev loj hlob sai.

• Yuav muaj tsawg refactoring. Tsawg kab yuav xaus rau hauv cov khoom. Peb yuav rov hais dua lawv tsawg dua tom qab.

• Peb tuaj yeem thim qhov kev hloov pauv tsis tau. Qhov no tsis yog tus qauv mus kom ze.

1. Qhov no yog qhov muaj txiaj ntsig vim peb muab cov peev txheej ntawm cov rooj sib tw xeem.

Twb tau zoo, tab sis dab tsi ntxiv tuaj yeem nrawm dua?

Ua tsaug rau cov txheej txheem zoo li no, peb tuaj yeem txo qhov pib nkag mus rau qhov kev sim no zoo heev.

Tam sim no muaj lub voj voog vicious, thaum tus tsim tawm, txhawm rau nkag mus rau cov ntaub ntawv loj tiag tiag, yuav tsum dhau los ua tus kws tshaj lij. Nws yuav tsum tau tso siab rau qhov kev nkag ntawd.

Tab sis yuav ua li cas loj hlob yog tias nws tsis nyob ntawd. Tab sis ua li cas yog tias koj tsuas muaj cov ntaub ntawv xeem me me muaj rau koj? Tom qab ntawd koj yuav tsis tau txais kev paub tiag tiag.

Yuav ua li cas tawm ntawm lub voj voog no? Raws li thawj interface, yooj yim rau cov neeg tsim khoom ntawm txhua qib, peb xaiv Slack bot. Tab sis nws tuaj yeem yog lwm yam interface.

Nws tso cai rau koj ua dab tsi? Koj tuaj yeem nqa cov lus nug tshwj xeeb thiab xa mus rau qhov tshwj xeeb channel rau cov ntaub ntawv. Peb yuav cia li xa tawm nyias clone hauv vib nas this. Cia peb khiav qhov kev thov no. Peb sau cov ntsuas thiab cov lus pom zoo. Cia peb ua ib qho kev pom. Thiab tom qab ntawd cov clone no yuav nyob twj ywm kom cov lus nug no tuaj yeem ua tau zoo, ntxiv qhov ntsuas, thiab lwm yam.

Thiab tseem Slack muab sijhawm rau peb rau kev sib koom tes tawm ntawm lub thawv. Txij li qhov no tsuas yog ib qho channel xwb, koj tuaj yeem pib sib tham txog qhov kev thov no nyob rau hauv xov rau qhov kev thov, ping koj cov npoj yaig, DBAs uas nyob hauv lub tuam txhab.

Tab sis, ntawm chav kawm, muaj teeb meem. Vim tias qhov no yog lub ntiaj teb tiag tiag, thiab peb tab tom siv lub server hosting ntau clones ib zaug, peb yuav tsum tau ntim cov nqi ntawm lub cim xeeb thiab CPU zog muaj rau cov clones.

Tab sis kom cov kev ntsuam xyuas no tuaj yeem ua tau, koj yuav tsum daws qhov teeb meem no.

Nws yog qhov tseeb tias lub ntsiab lus tseem ceeb yog tib cov ntaub ntawv. Tab sis peb twb muaj lawm. Thiab peb xav kom ua tiav tib qho kev teeb tsa. Thiab peb tuaj yeem muab qhov kev teeb tsa yuav luag zoo ib yam.

Nws yuav txias kom muaj cov khoom siv tib yam li hauv kev tsim khoom, tab sis nws yuav txawv.

Cia peb nco ntsoov tias Postgres ua haujlwm nrog lub cim xeeb li cas. Peb muaj ob lub caches. Ib qho ntawm cov ntaub ntawv kaw lus thiab ib haiv neeg Postgres, piv txwv li Kev Sib Koom Tsis Muaj Cache.

Nws yog ib qho tseem ceeb uas yuav tsum nco ntsoov tias Shared Buffer Cache raug faib thaum Postgres pib, nyob ntawm seb qhov loj npaum li cas koj tau teev tseg hauv kev teeb tsa.

Thiab qhov thib ob cache siv txhua qhov chaw muaj.

Thiab thaum peb ua ob peb clones ntawm ib lub tshuab, nws hloov tawm tias peb maj mam sau lub cim xeeb. Thiab nyob rau hauv ib txoj kev zoo, Shared Buffer Cache yog 25% ntawm tag nrho cov nyiaj nco uas muaj nyob rau hauv lub tshuab.

Thiab nws hloov tawm tias yog tias peb tsis hloov qhov kev ntsuas no, ces peb yuav tuaj yeem khiav tsuas yog 4 zaus ntawm ib lub tshuab, piv txwv li 4 ntawm txhua qhov nyias nyias. Thiab qhov no, ntawm chav kawm, yog qhov phem, vim peb xav kom muaj ntau yam ntawm lawv.

Tab sis ntawm qhov tod tes, Buffer Cache yog siv los ua cov lus nug rau indexes, uas yog, txoj kev npaj nyob ntawm seb peb cov caches loj npaum li cas. Thiab yog tias peb tsuas yog coj qhov ntsuas no thiab txo nws, ces peb cov phiaj xwm tuaj yeem hloov pauv ntau.

Piv txwv li, yog tias peb muaj lub cache loj ntawm cov khoom, ces Postgres yuav nyiam siv qhov ntsuas. Thiab yog tias tsis yog, ces yuav muaj SeqScan. Thiab yuav ua li cas yog tias peb cov phiaj xwm tsis sib haum?

Tab sis ntawm no peb tuaj rau qhov xaus tias qhov tseeb txoj kev npaj hauv Postgres tsis nyob ntawm qhov loj me uas tau teev tseg hauv Shared Buffer hauv txoj kev npaj, nws nyob ntawm qhov ua tau zoo_cache_size.

Effective_cache_size yog qhov kwv yees ntawm cov cache uas muaj rau peb, piv txwv li qhov suav ntawm Buffer Cache thiab cov ntaub ntawv kaw lus. Qhov no yog teem los ntawm config. Thiab lub cim xeeb no tsis raug faib.

Thiab vim yog qhov ntsuas no, peb tuaj yeem ua qhov yuam kev Postgres, hais tias peb muaj ntau cov ntaub ntawv muaj, txawm tias peb tsis muaj cov ntaub ntawv no. Thiab yog li, cov phiaj xwm yuav ua tiav nrog kev tsim khoom.

Tab sis qhov no tuaj yeem cuam tshuam rau lub sijhawm. Thiab peb optimize cov lus nug los ntawm lub sijhawm, tab sis nws yog ib qho tseem ceeb uas lub sij hawm nyob ntawm ntau yam:

• Nws nyob ntawm qhov load uas tam sim no ntawm cov khoom.

• Nws nyob ntawm tus yam ntxwv ntawm lub tshuab nws tus kheej.

Thiab qhov no yog qhov tsis ncaj ncees, tab sis qhov tseeb peb tuaj yeem ua kom zoo raws nraim los ntawm cov ntaub ntawv uas cov lus nug no yuav nyeem kom tau txais qhov tshwm sim.

Thiab yog tias koj xav kom lub sijhawm nyob ze rau qhov peb yuav pom hauv cov khoom lag luam, ces peb yuav tsum tau siv cov khoom siv zoo sib xws thiab, tej zaum, ntau dua kom tag nrho cov clones haum. Tab sis qhov no yog kev sib haum xeeb, piv txwv li koj yuav tau txais cov phiaj xwm tib yam, koj yuav pom ntau npaum li cas cov ntaub ntawv ib qho lus nug yuav nyeem thiab koj yuav tuaj yeem txiav txim siab seb cov lus nug no puas zoo (lossis tsiv teb tsaws) lossis phem, nws tseem yuav tsum tau ua kom zoo. .

Cia peb saib seb Joe yog qhov tshwj xeeb zoo li cas.

Cia peb ua ib qho kev thov los ntawm qhov system tiag tiag. Hauv qhov no, lub database yog 1 terabyte. Thiab peb xav suav cov xov xwm tshiab uas muaj ntau dua 10 nyiam.

Peb tab tom sau ib tsab xov xwm rau lub channel, ib tug clone tau deployed rau peb. Thiab peb yuav pom tias qhov kev thov no yuav ua tiav hauv 2,5 feeb. Nov yog thawj qhov uas peb pom.

B Joe yuav qhia koj cov lus pom zoo tsis siv neeg raws li txoj kev npaj thiab kev ntsuas.

Peb yuav pom tias cov lus nug ua cov ntaub ntawv ntau dhau kom tau txais cov kab me me. Thiab qee yam kev ntsuas tshwj xeeb xav tau, vim peb pom tias muaj ntau cov kab lim hauv cov lus nug.

Cia wb mus saib seb qhov twg tshwm sim. Tseeb, peb pom tias peb tau nyeem yuav luag ib thiab ib nrab gigabytes ntawm cov ntaub ntawv los ntawm cov ntaub ntawv cache lossis txawm los ntawm disk. Thiab qhov no tsis zoo, vim peb tau txais 142 kab xwb.

Thiab, nws yuav zoo li, peb muaj qhov ntsuas ntsuas ntawm no thiab yuav tsum tau ua haujlwm sai, tab sis txij li peb tau lim tawm ntau cov kab (peb yuav tsum suav lawv), cov lus nug maj mam ua haujlwm.

Thiab qhov no tshwm sim hauv txoj kev npaj vim qhov tseeb tias cov xwm txheej hauv cov lus nug thiab cov xwm txheej hauv qhov ntsuas ib nrab tsis sib xws.

Cia peb sim ua kom qhov ntsuas tau meej dua thiab pom tias cov lus nug ua tiav li cas hloov tom qab ntawd.

Kev tsim ntawm qhov ntsuas tau siv sijhawm ntev heev, tab sis tam sim no peb tshawb xyuas cov lus nug thiab pom tias lub sijhawm hloov 2,5 feeb tsuas yog 156 milliseconds, uas yog qhov zoo txaus. Thiab peb nyeem tsuas yog 6 megabytes ntawm cov ntaub ntawv.

Thiab tam sim no peb siv index tsuas yog luam theej duab.

Lwm zaj dab neeg tseem ceeb yog tias peb xav nthuav tawm txoj kev npaj ua kom nkag siab ntau dua. Peb tau siv qhov kev pom pom siv Flame Graphs.

Qhov no yog qhov kev thov sib txawv, khaus ntau dua. Thiab peb tsim Flame Graphs raws li ob qhov tsis muaj: qhov no yog tus nqi ntawm cov ntaub ntawv uas ib qho ntawm qhov tshwj xeeb suav nyob rau hauv txoj kev npaj thiab sijhawm, piv txwv li lub sijhawm ua haujlwm ntawm cov node.

Ntawm no peb tuaj yeem sib piv cov nodes rau ib leeg. Thiab nws yuav paub meej tias leej twg ntawm lawv yuav siv sij hawm ntau dua los yog tsawg dua, uas feem ntau nyuaj ua rau lwm txoj hauv kev.

Tau kawg, txhua tus paub piav qhia.depesz.com. Ib qho zoo ntawm qhov kev pom no yog tias peb khaws cov ntawv nyeem thiab tseem muab qee qhov kev txwv yooj yim rau hauv lub rooj kom peb tuaj yeem txheeb xyuas.

Thiab cov neeg tsim khoom uas tseem tsis tau delved rau hauv cov ncauj lus no kuj siv piav qhia.depesz.com, vim nws yooj yim dua rau lawv los txiav txim seb qhov ntsuas twg tseem ceeb thiab qhov twg tsis yog.

Muaj ib txoj hauv kev tshiab rau kev pom - qhov no yog piav qhia.dalibo.com. Lawv ua ib tsob ntoo pom, tab sis nws nyuaj heev los sib piv cov nodes nrog ib leeg. Ntawm no koj tuaj yeem nkag siab cov qauv zoo, txawm li cas los xij, yog tias muaj kev thov loj, ces koj yuav tsum tau scroll rov qab thiab tawm, tab sis kuj yog ib qho kev xaiv.

kev sib koom tes

Thiab, raws li kuv tau hais, Slack muab sijhawm rau peb los koom tes. Piv txwv li, yog tias peb tuaj hla cov lus nug nyuaj uas tsis paub meej yuav ua li cas kom zoo, peb tuaj yeem qhia qhov teeb meem no nrog peb cov npoj yaig hauv xov hauv Slack.

Nws zoo li peb tias nws yog ib qho tseem ceeb rau kev sim ntawm cov ntaub ntawv loj. Txhawm rau ua qhov no, peb tau ua cov cuab yeej hloov tshiab Database Lab, uas muaj nyob rau hauv qhov chaw qhib. Koj tuaj yeem siv Joe bot ib yam nkaus. Koj tuaj yeem nqa nws tam sim no thiab siv nws ntawm koj qhov chaw. Tag nrho cov lus qhia muaj nyob rau ntawd.

Nws tseem ceeb heev uas yuav tsum nco ntsoov tias kev daws nws tus kheej tsis yog kev hloov pauv, vim tias muaj Delphix, tab sis nws yog kev daws teeb meem kev lag luam. Nws yog kiag li kaw, nws yog kim heev. Peb tshwj xeeb hauv Postgres. Cov no yog tag nrho cov khoom lag luam qhib. Koom nrog peb!

Qhov no yog qhov kuv xaus. Ua tsaug!

Lus nug

Nyob zoo! Ua tsaug rau daim ntawv qhia! Txaus siab heev, tshwj xeeb tshaj yog rau kuv, vim kuv tau daws qhov teeb meem tib lub sijhawm dhau los. Thiab yog li kuv muaj ntau cov lus nug. Vam tias kuv yuav tau txais tsawg kawg ib feem ntawm nws.

Kuv xav li cas koj xam qhov chaw rau ib puag ncig no? Lub tshuab txhais tau tias nyob rau hauv qee qhov xwm txheej, koj cov clones tuaj yeem loj hlob mus rau qhov loj tshaj plaws. Hais lus kwv yees, yog tias koj muaj kaum terabyte database thiab 10 clones, ces nws yooj yim rau simulate qhov xwm txheej uas txhua clone hnyav 10 cov ntaub ntawv tshwj xeeb. Yuav ua li cas koj xam qhov chaw no, uas yog, lub delta uas koj tau hais txog, nyob rau hauv uas cov clones yuav nyob?

Zoo lus nug. Nws yog ib qho tseem ceeb kom taug qab cov clones tshwj xeeb ntawm no. Thiab yog tias lub clone muaj qee qhov kev hloov pauv loj heev, nws pib loj hlob, tom qab ntawd peb tuaj yeem ua ntej tshaj tawm lus ceeb toom rau cov neeg siv txog qhov no, lossis tam sim ntawd tso tseg qhov clone no kom peb tsis txhob muaj qhov xwm txheej poob.

Yog, kuv muaj lus nug nested. Ntawd yog, koj ua li cas kom ntseeg tau lub neej voj voog ntawm cov qauv no? Peb muaj qhov teeb meem no thiab tag nrho cov dab neeg sib cais. Qhov no tshwm sim li cas?

Muaj qee qhov ttl rau txhua tus clone. Yeej, peb muaj ttl ruaj.

Yuav ua li cas, yog tias tsis pub leejtwg paub?

1 teev, i.e. tsis ua haujlwm - 1 teev. Yog tias nws tsis siv, ces peb bang nws. Tab sis tsis muaj qhov xav tsis thoob ntawm no, txij li peb tuaj yeem tsa lub clone hauv vib nas this. Thiab yog tias koj xav tau nws dua, thov.

Kuv kuj txaus siab rau kev xaiv cov thev naus laus zis, vim tias, piv txwv li, peb siv ob peb txoj hauv kev ua ke rau ib qho lossis lwm qhov. Vim li cas ZFS? Vim li cas koj ho tsis siv LVM? Koj hais tias muaj teeb meem nrog LVM. Cov teeb meem dab tsi? Hauv kuv lub tswv yim, qhov kev xaiv zoo tshaj plaws yog nrog kev khaws cia, hais txog kev ua haujlwm.

Qhov teeb meem tseem ceeb ntawm ZFS yog dab tsi? Qhov tseeb tias koj yuav tsum khiav ntawm tib tus tswv tsev, piv txwv li txhua qhov xwm txheej yuav nyob hauv tib OS. Thiab nyob rau hauv cov ntaub ntawv ntawm cia, koj tuaj yeem txuas cov khoom sib txawv. Thiab lub bottleneck tsuas yog cov blocks uas nyob rau hauv qhov system cia. Thiab cov lus nug ntawm kev xaiv technologies yog nthuav. Vim li cas ho tsis LVM?

Tshwj xeeb, peb tuaj yeem tham txog LVM ntawm kev sib ntsib. Txog kev khaws cia - nws tsuas yog kim. Peb tuaj yeem siv ZFS system nyob txhua qhov chaw. Koj tuaj yeem xa nws ntawm koj lub tshuab. Koj tuaj yeem yooj yim rub tawm lub chaw cia khoom thiab xa mus. ZFS tau nruab yuav luag txhua qhov chaw yog tias peb tham txog Linux. Ntawd yog, peb tau txais kev hloov pauv tau yooj yim heev. Thiab tawm ntawm lub thawv, ZFS muab ntau heev. Koj tuaj yeem xa cov ntaub ntawv ntau npaum li koj nyiam, txuas ntau tus disks, muaj cov duab thaij duab. Thiab, raws li kuv tau hais, nws yooj yim rau kev tswj hwm. Ntawd yog, nws zoo li ntxim nyiam siv. Nws raug sim, nws muaj ntau xyoo. Nws muaj ib lub zej zog loj heev uas loj hlob tuaj. ZFS yog ib qho kev daws teeb meem zoo heev.

Nikolai Samokhvalov: Kuv puas tuaj yeem hais ntxiv? Kuv lub npe yog Nikolay, peb ua haujlwm nrog Anatoly. Kuv pom zoo tias kev khaws cia zoo heev. Thiab qee qhov ntawm peb cov neeg siv khoom muaj Pure Storage thiab lwm yam.

Anatoly raug sau tseg tias peb tsom mus rau modularity. Thiab nyob rau hauv lub neej yav tom ntej, koj muaj peev xwm siv ib tug interface - nqa ib tug snapshot, ua ib tug clone, rhuav tshem lub clone. Nws yog txhua yam yooj yim. Thiab cia txias, yog tias nws yog.

Tab sis ZFS muaj rau txhua tus. DelPhix twb txaus lawm, lawv muaj 300 tus neeg siv khoom. Ntawm cov no, hmoov zoo 100 muaj 50 tus neeg siv khoom, piv txwv li lawv tau tsom mus rau NASA, thiab lwm yam. Nws yog lub sijhawm rau txhua tus kom tau txais cov thev naus laus zis no. Thiab yog vim li cas peb thiaj muaj qhov qhib Core. Peb muaj ib qho interface uas tsis yog qhib qhov chaw. Nov yog lub platform uas peb yuav qhia. Tab sis peb xav kom nws nkag tau rau txhua tus neeg. Peb xav ua kom muaj kev hloov pauv kom txhua tus neeg sim tsis txhob kwv yees ntawm lub laptops. Peb yuav tsum sau SELECT thiab pom tam sim ntawd nws qeeb. Txhob tos kom DBA qhia koj txog nws. Ntawm no yog lub hom phiaj tseem ceeb. Thiab kuv xav tias peb txhua tus yuav tuaj rau qhov no. Thiab peb ua qhov no rau txhua tus muaj. Yog li ZFS, vim tias nws yuav muaj nyob txhua qhov chaw. Ua tsaug rau cov zej zog daws teeb meem thiab kom muaj daim ntawv tso cai qhib, thiab lwm yam.*

Nyob zoo! Ua tsaug rau daim ntawv qhia! Kuv lub npe yog Maxim. Peb tau daws cov teeb meem tib yam. Lawv txiav txim siab ntawm lawv tus kheej. Koj faib cov peev txheej ntawm cov clones li cas? Txhua tus clone tuaj yeem ua nws tus kheej txhua lub sijhawm: ib qho ntsuas ib yam, lwm tus, ib tus neeg tsim qhov ntsuas, ib tus neeg muaj haujlwm hnyav. Thiab yog tias koj tseem tuaj yeem faib los ntawm CPU, tom qab ntawd los ntawm IO, koj faib li cas? Nov yog thawj lo lus nug.

Thiab lo lus nug thib ob yog hais txog qhov tsis sib xws ntawm qhov sawv. Cia peb hais tias kuv muaj ZFS ntawm no thiab txhua yam yog txias, tab sis tus neeg siv khoom ntawm cov khoom tsis muaj ZFS, tab sis ext4, piv txwv li. Yuav ua li cas hauv qhov no?

Cov lus nug zoo heev. Kuv tau hais txog qhov teeb meem no me ntsis nrog qhov tseeb tias peb faib cov peev txheej. Thiab qhov kev daws yog qhov no. Xav txog tias koj tab tom sim ntawm staging. Koj tuaj yeem muaj qhov xwm txheej zoo li no tib lub sijhawm uas ib tus neeg muab ib lub nra, lwm tus. Thiab yog li ntawd, koj pom cov metrics tsis to taub. Txawm tias tib qhov teeb meem tuaj yeem nrog cov khoom. Thaum koj xav xyuas qee qhov kev thov thiab koj pom tias muaj qee qhov teeb meem nrog nws - nws ua haujlwm maj mam, ces qhov tseeb qhov teeb meem tsis yog nyob rau hauv qhov kev thov, tab sis nyob rau hauv qhov tseeb hais tias muaj ib co hom ntawm parallel load.

Thiab yog li ntawd, nws yog ib qho tseem ceeb ntawm no kom tsom mus rau qhov kev npaj yuav ua li cas, peb yuav ua li cas hauv txoj kev npaj thiab ntau npaum li cas cov ntaub ntawv peb yuav tsa rau qhov no. Qhov tseeb tias peb cov disks, piv txwv li, yuav thauj khoom nrog qee yam, nws yuav cuam tshuam rau lub sijhawm. Tab sis peb tuaj yeem kwv yees li cas loaded qhov kev thov no yog los ntawm tus nqi ntawm cov ntaub ntawv. Nws tsis yog qhov tseem ceeb heev uas tib lub sijhawm yuav muaj qee yam kev tua.

Kuv muaj ob lo lus nug. Qhov no yog qhov zoo tshaj plaws. Puas muaj cov xwm txheej uas cov ntaub ntawv tsim khoom tseem ceeb, xws li credit card naj npawb? Puas muaj ib yam dab tsi npaj txhij los yog nws yog ib txoj haujlwm cais? Thiab lo lus nug thib ob - puas muaj qee yam zoo li no rau MySQL?

Hais txog cov ntaub ntawv. Peb yuav ua obfuscation mus txog thaum peb ua. Tab sis yog tias koj xa Joe raws nraim, yog tias koj tsis muab kev nkag mus rau cov neeg tsim khoom, ces tsis muaj kev nkag mus rau cov ntaub ntawv. Vim li cas? Vim Joe tsis qhia cov ntaub ntawv. Nws tsuas yog qhia cov metrics, cov phiaj xwm thiab qhov ntawd yog nws. Qhov no tau ua tiav ntawm lub hom phiaj, vim tias qhov no yog ib qho ntawm peb cov neeg siv khoom xav tau. Lawv xav kom muaj peev xwm ua kom zoo dua yam tsis pub txhua tus nkag mus.

Hais txog MySQL. Cov kab ke no tuaj yeem siv rau txhua yam uas khaws lub xeev ntawm disk. Thiab txij li thaum peb tab tom ua Postgres, tam sim no peb tab tom ua tag nrho cov automation rau Postgres ua ntej. Peb xav kom automate tau cov ntaub ntawv los ntawm ib tug thaub qab. Peb tab tom teeb tsa Postgres kom raug. Peb paub yuav ua li cas ua kom cov phiaj xwm sib tw, thiab lwm yam.

Tab sis txij li thaum lub kaw lus txuas ntxiv, nws kuj tuaj yeem siv rau MySQL. Thiab muaj cov piv txwv zoo li no. Yandex muaj qhov zoo sib xws, tab sis lawv tsis tshaj tawm nws nyob qhov twg. Lawv siv nws hauv Yandex.Metrica. Thiab tsuas yog ib zaj dab neeg txog MySQL. Tab sis cov thev naus laus zis zoo ib yam, ZFS.

Ua tsaug rau daim ntawv qhia! Kuv kuj muaj ob peb lo lus nug. Koj tau hais tias cloning tuaj yeem siv rau kev txheeb xyuas, piv txwv li los tsim cov indexes ntxiv rau ntawd. Koj puas tuaj yeem qhia me ntsis ntxiv txog nws ua haujlwm li cas?

Thiab kuv mam li nug cov lus nug thib ob tam sim ntawd txog qhov zoo sib xws ntawm qhov sawv ntsug, qhov zoo sib xws ntawm cov phiaj xwm. Txoj kev npaj tseem nyob ntawm cov txheeb cais sau los ntawm Postgres. Koj yuav daws qhov teeb meem no li cas?

Raws li kev txheeb xyuas, tsis muaj qhov tshwj xeeb, vim tias peb tsis tau siv nws, tab sis muaj lub sijhawm zoo li no. Yog tias peb tab tom tham txog kev ntsuas ntsuas, ces xav txog tias cov lus nug tab tom nrhiav ib lub rooj nrog ntau pua lab cov ntaub ntawv thiab ib kem uas feem ntau tsis suav nrog hauv cov khoom. Thiab peb xav suav qee cov ntaub ntawv muaj. Yog tias qhov kev thov no raug xa mus rau prod, ces muaj qhov ua tau tias nws yuav yooj yim ntawm cov khoom, vim tias qhov kev thov yuav raug ua tiav rau ib feeb.

Ok, cia peb ua ib lub clone nyias uas tsis txaus ntshai nres rau ob peb feeb. Thiab txhawm rau ua kom nws yooj yim dua los nyeem cov kev tshuaj ntsuam xyuas, peb yuav ntxiv qhov ntsuas rau cov kab ntawv uas peb nyiam cov ntaub ntawv.

Qhov ntsuas yuav raug tsim txhua lub sijhawm?

Koj tuaj yeem ua nws kom peb kov cov ntaub ntawv, ua snapshots, ces peb yuav rov qab los ntawm qhov snapshot no thiab tsav cov kev thov tshiab. Ntawd yog, koj tuaj yeem ua nws kom koj tuaj yeem tsa cov clones tshiab nrog cov ntawv ntsuas uas twb muaj lawm.

Raws li cov lus nug txog kev txheeb cais, yog tias peb rov qab los ntawm kev thaub qab, yog tias peb ua rov ua dua, ces peb cov txheeb cais yuav zoo ib yam. Vim tias peb muaj tag nrho cov ntaub ntawv lub cev, uas yog, peb yuav nqa cov ntaub ntawv raws li nws yog nrog tag nrho cov kev ntsuas ntsuas ib yam nkaus.

Nov yog lwm qhov teeb meem. Yog tias koj siv cov kev daws teeb meem huab, ces tsuas yog cov ntaub ntawv pov thawj muaj nyob rau ntawd, vim tias Google, Amazon tsis tso cai rau koj nqa daim ntawv theej. Yuav muaj teeb meem.

Ua tsaug rau daim ntawv qhia. Muaj ob lo lus nug zoo ntawm no txog MySQL thiab kev sib koom ua ke. Tab sis, qhov tseeb, nws tag nrho los ntawm qhov tseeb tias qhov no tsis yog lub ntsiab lus ntawm DBMS tshwj xeeb, tab sis ntawm cov ntaub ntawv tag nrho. Thiab, raws li, cov teeb meem ntawm kev sib faib cov peev txheej yuav tsum tau daws los ntawm qhov ntawd, tsis yog qhov kawg uas nws yog Postgres, tab sis hauv cov ntaub ntawv kaw lus, hauv server, piv txwv li.

Kuv lo lus nug txawv me ntsis. Nws yog ze rau cov ntaub ntawv ntau txheej, qhov twg muaj ob peb txheej. Piv txwv li, peb teeb tsa lub kaum-terabyte duab hloov tshiab, peb rov ua dua. Thiab peb tshwj xeeb siv cov tshuaj no rau databases. Replication tab tom ua tiav, cov ntaub ntawv tau hloov kho. Muaj 100 tus neeg ua haujlwm ua haujlwm sib npaug ntawm no, uas pheej tso cov kev txhaj tshuaj sib txawv no. Yuav ua li cas? Yuav ua li cas kom paub tseeb tias tsis muaj kev tsis sib haum xeeb, uas lawv tau tsim ib qho, thiab tom qab ntawd cov ntaub ntawv hloov pauv, thiab cov duab no tag nrho mus?

Lawv yuav tsis mus vim qhov ntawd yog li cas ZFS ua haujlwm. Peb tuaj yeem khaws cia sib cais hauv ib txoj xov ntawm cov ntaub ntawv hloov pauv uas tuaj yeem tshwm sim los ntawm kev rov ua dua tshiab. Thiab khaws cov clones uas cov neeg tsim khoom siv rau ntawm cov ntaub ntawv qub. Thiab nws ua haujlwm rau peb, txhua yam nyob rau hauv kev txiav txim nrog qhov no.

Nws hloov tawm tias qhov hloov tshiab yuav tshwm sim raws li txheej txheej ntxiv, thiab tag nrho cov duab tshiab yuav mus lawm, raws li txheej txheej no, txoj cai?

Los ntawm cov khaubncaws sab nraud povtseg uas yog los ntawm kev rov ua dua yav dhau los.

Cov khaubncaws sab nraud povtseg yuav poob, tab sis lawv yuav xa mus rau txheej qub, thiab lawv puas yuav coj cov duab tshiab los ntawm txheej kawg uas tau txais hauv qhov hloov tshiab?

Feem ntau, yog.

Tom qab ntawd raws li qhov tshwm sim peb yuav muaj txog li ib daim duab ntawm txheej. Thiab dhau sij hawm lawv yuav tsum tau compressed?

Yog txhua yam yog lawm. Muaj ib lub qhov rais. Peb khaws cov duab thaij duab txhua lub lim tiam. Nws nyob ntawm seb koj muaj peev xwm npaum li cas. Yog tias koj muaj peev xwm khaws ntau cov ntaub ntawv, koj tuaj yeem khaws cov snapshots ntev. Lawv yuav tsis ploj mus ntawm lawv tus kheej. Yuav tsis muaj cov ntaub ntawv kev noj nyiaj txiag. Yog hais tias cov snapshots yog outdated, raws li nws zoo nkaus li rau peb, piv txwv li, nws nyob ntawm txoj cai nyob rau hauv lub tuam txhab, ces peb yuav yooj yim rho lawv thiab tso chaw.

Nyob zoo, ua tsaug rau daim ntawv qhia! Lus nug txog Joe. Koj tau hais tias tus neeg siv khoom tsis xav muab txhua tus nkag mus rau cov ntaub ntawv. Hais lus nruj me ntsis, yog tias ib tug neeg muaj qhov tshwm sim ntawm Piav Kev Tshawb Fawb, ces nws tuaj yeem peep cov ntaub ntawv.

Nws zoo li ntawd. Piv txwv li, peb tuaj yeem sau: "Xaiv los ntawm qhov chaw email = rau qhov ntawd". Ntawd yog, peb yuav tsis pom cov ntaub ntawv nws tus kheej, tab sis peb tuaj yeem pom qee qhov qhia tsis ncaj. Qhov no yuav tsum to taub. Tab sis ntawm qhov tod tes, nws tag nrho muaj. Peb muaj kev soj ntsuam ntawm cov cav, peb muaj kev tswj hwm ntawm lwm cov npoj yaig uas tseem pom tias cov neeg tsim khoom ua dab tsi. Thiab yog tias ib tug neeg sim ua qhov no, ces qhov kev pabcuam kev ruaj ntseg yuav tuaj rau lawv thiab ua haujlwm rau qhov teebmeem no.

Nyob zoo tav su Ua tsaug rau daim ntawv qhia! Kuv muaj lus nug luv luv. Yog tias lub tuam txhab tsis siv Slack, puas muaj kev cuam tshuam rau nws tam sim no, lossis puas muaj peev xwm rau cov neeg tsim khoom siv cov xwm txheej txhawm rau txuas cov ntawv xeem rau cov ntaub ntawv?

Tam sim no muaj qhov txuas rau Slack, piv txwv li tsis muaj lwm tus tub txib, tab sis kuv yeej xav ua kev txhawb nqa rau lwm tus tub txib ib yam nkaus. Koj ua tau dab tsi? Koj tuaj yeem xa DB Lab yam tsis muaj Joe, mus nrog kev pab ntawm REST API lossis nrog kev pab ntawm peb lub platform thiab tsim clones thiab txuas nrog PSQL. Tab sis qhov no tuaj yeem ua tiav yog tias koj npaj yuav muab koj cov neeg tsim khoom nkag mus rau cov ntaub ntawv, vim tias yuav tsis muaj ib qho screen ntxiv lawm.

Kuv tsis xav tau txheej no, tab sis kuv xav tau lub sijhawm zoo li no.

Yog lawm, nws tuaj yeem ua tau.

Tau qhov twg los: www.hab.com