Tsis ntev los no kuv qhia koj li cas, siv cov zaub mov txawv los ntawm PostgreSQL database. Hnub no peb yuav tham txog yuav ua li cas kev kaw yuav ua tau zoo dua nyob rau hauv cov ntaub ntawv tsis siv ib qho "twists" nyob rau hauv lub config - yooj yim los ntawm kev txhim kho cov ntaub ntawv ntws.
#1. Kev faib tawm
Ib tsab xov xwm hais txog yuav ua li cas thiab yog vim li cas nws thiaj tsim nyog npaj twb tau lawm, ntawm no peb yuav tham txog kev coj ua ntawm kev siv qee txoj hauv kev hauv peb .
"Txhua yam ntawm hnub dhau mus ..."
Thaum xub thawj, zoo li txhua tus MVP, peb txoj haujlwm tau pib nyob rau hauv lub teeb pom kev zoo - kev saib xyuas tsuas yog ua rau lub kaum os ntawm cov servers tseem ceeb tshaj plaws, txhua lub rooj tau sib txuas ... thiab ntau dua, thiab ib zaug ntxiv peb sim ua ib yam dab tsi nrog ib qho rooj 1.5TB loj, peb pom tau tias txawm hais tias nws muaj peev xwm ua neej nyob zoo li no, nws tsis yooj yim heev.
Lub sijhawm yuav luag zoo li lub sijhawm epic, txawv versions ntawm PostgreSQL 9.x muaj feem cuam tshuam, yog li txhua qhov kev faib yuav tsum tau ua "manually" - los ntawm rooj qub txeeg qub teg thiab ua rau routing nrog dynamic EXECUTE.
Cov kev daws teeb meem tau dhau los ua universal txaus uas nws tuaj yeem muab txhais rau txhua lub rooj:
- Ib qhov khoob "header" niam txiv lub rooj tau tshaj tawm, uas tau piav qhia tag nrho tsim nyog indexes thiab triggers.
- Cov ntaub ntawv los ntawm tus neeg siv qhov kev xav tau ua nyob rau hauv "hauv paus" rooj, thiab siv sab hauv routing txhais
BEFORE INSERTcov ntaub ntawv tau "lub cev" tso rau hauv seem uas yuav tsum tau muaj. Yog tias tsis muaj qhov no, peb ntes tau qhov kev zam thiab ... - β¦ los ntawm kev siv tau tsim raws li tus qauv ntawm niam txiv lub rooj ntu nrog kev txwv rau hnub xav tauyog li ntawd thaum cov ntaub ntawv yog retrieved, nyeem ntawv tsuas yog ua nyob rau hauv nws.
PG10: thawj zaug
Tab sis kev faib los ntawm kev qub txeeg qub teg muaj keeb kwm tsis zoo rau kev cuam tshuam nrog kev sau cov kwj dej los yog ntau tus me nyuam partitions. Piv txwv li, koj tuaj yeem nco qab tias cov algorithm rau xaiv cov seem uas yuav tsum tau muaj quadratic complexity, tias nws ua haujlwm nrog 100+ ntu, koj tus kheej nkag siab li cas ...
Hauv PG10 qhov xwm txheej no tau zoo heev los ntawm kev siv kev txhawb nqa . Yog li ntawd, peb tam sim sim siv nws tam sim ntawd tom qab tsiv chaw cia, tab sis ...
Raws li nws tau muab tawm tom qab khawb los ntawm phau ntawv qhia, cov lus hauv ib txwm muab faib hauv qhov version no yog:
- tsis txhawb nqa cov lus piav qhia
- tsis txhawb nqa rau nws
- tsis tuaj yeem yog leej twg " xeeb leej xeeb ntxwv"
- tsis txhawb
INSERT ... ON CONFLICT - tsis tuaj yeem tsim ib ntu txiav
Tau txais kev mob siab rau lub hauv pliaj nrog lub rake, peb pom tau tias nws yuav ua tsis tau yam tsis tau hloov kho daim ntawv thov, thiab ncua kev tshawb fawb ntxiv rau rau lub hlis.
PG10: thib ob lub caij nyoog
Yog li, peb pib daws cov teeb meem uas tshwm sim ib qho los ntawm ib qho:
- Yog vim li cas thiab
ON CONFLICTPeb pom tias peb tseem xav tau lawv ntawm no thiab nyob ntawd, yog li peb tau ua ib theem nruab nrab los ua haujlwm rau lawv npe npe. - Tau tshem ntawm "routing" nyob rau hauv triggers - uas yog, los ntawm
EXECUTE. - Lawv muab nws cais tawm template rooj nrog tag nrho cov indexesyog li ntawd lawv tsis txawm nyob rau hauv lub npe lub rooj.
Thaum kawg, tom qab tag nrho cov no, peb partitioned lub ntsiab rooj natively. Kev tsim ntawm ntu tshiab tseem tshuav rau daim ntawv thov lub siab.
"Sawing" phau ntawv txhais lus
Ib yam li hauv txhua qhov kev tshuaj ntsuam xyuas, peb kuj muaj "facts" thiab "txiav" (dictionaries). Hauv peb qhov xwm txheej, hauv lub peev xwm no lawv tau ua, piv txwv li, zoo sib xws qeeb queries los yog cov ntawv nyeem ntawm cov lus nug nws tus kheej.
"Cov Lus Tseeb" tau muab faib los ntawm ib hnub rau ib lub sijhawm ntev, yog li peb maj mam tshem cov ntu uas tsis tu ncua, thiab lawv tsis thab peb (cov ntaub ntawv!). Tab sis muaj teeb meem nrog dictionaries ...
Tsis yog hais tias muaj ntau ntawm lawv, tab sis kwv yees li 100TB ntawm "qhov tseeb" ua rau 2.5TB phau ntawv txhais lus. Koj tsis tuaj yeem yooj yim rho tawm ib yam dab tsi los ntawm lub rooj zoo li no, koj tsis tuaj yeem nthuav nws hauv lub sijhawm txaus, thiab sau ntawv rau nws maj mam qeeb.
Zoo li phau ntawv txhais lus... nyob rau hauv nws, txhua qhov nkag yuav tsum tau nthuav tawm raws nraim ib zaug... thiab qhov no yog qhov tseeb, tab sis!.. Tsis muaj leej twg txwv tsis pub peb muaj ib phau ntawv txhais lus cais rau txhua hnub! Yog, qhov no coj ib qho kev rov ua dua, tab sis nws tso cai:
- sau/nyeem sai vim qhov me me loj
- noj tsawg nco los ntawm kev ua hauj lwm nrog ntau compact indexes
- khaws cov ntaub ntawv tsawg dua vim muaj peev xwm tshem tawm sai sai
Raws li ib tug tshwm sim ntawm tag nrho cov complex ntawm kev ntsuas CPU load txo los ntawm ~ 30%, disk load los ntawm ~ 50%:
Nyob rau tib lub sij hawm, peb txuas ntxiv sau raws nraim tib yam rau hauv cov ntaub ntawv, tsuas yog nrog tsawg load.
#2. Database evolution thiab refactoring
Yog li ntawd peb txiav txim siab rau qhov peb muaj Txhua hnub nws muaj nws tus kheej ntu nrog cov ntaub ntawv. Tiag tiag, CHECK (dt = '2018-10-12'::date) - thiab muaj qhov tseem ceeb muab faib thiab cov xwm txheej rau cov ntaub ntawv poob rau hauv ib ntu.
Txij li thaum tag nrho cov ntawv ceeb toom hauv peb cov kev pabcuam tau tsim nyob rau hauv cov ntsiab lus ntawm ib hnub tshwj xeeb, cov indexes rau lawv txij li "lub sijhawm tsis sib cais" yog txhua yam. (Server, Hnub tim, Plan Template), (Server, Hnub tim, Plan node), (Hnub tim, Error class, Server)...
Tab sis tam sim no lawv nyob ntawm txhua ntu koj cov ntawv luam txhua qhov ntsuas no... Thiab hauv txhua ntu hnub yog qhov tsis tu ncua... Nws hloov tawm tias tam sim no peb nyob hauv txhua qhov kev ntsuas no tsuas yog nkag mus rau qhov tsis tu ncua raws li ib qho ntawm cov teb, uas ua rau ob qho tib si nws ntim thiab lub sijhawm tshawb nrhiav rau nws, tab sis tsis nqa ib qho txiaj ntsig. Lawv tso lub rake rau lawv tus kheej, oops...
Cov kev taw qhia ntawm optimization yog pom tseeb - yooj yim tshem tawm hnub tim los ntawm tag nrho cov indexes ntawm cov rooj sib faib. Muab peb cov ntim, qhov nce yog hais txog 1TB / lub lis piam!
Tam sim no cia peb nco ntsoov tias qhov terabyte no tseem yuav tsum tau kaw qee yam. Qhov ntawd yog, peb kuj lub disk yuav tsum tam sim no load tsawg dua! Daim duab no qhia meej meej cov txiaj ntsig tau los ntawm kev ntxuav, uas peb tau mob siab rau ib lub lim tiam:
#3. "Spreading" lub ncov load
Ib qho ntawm cov teeb meem loj ntawm cov khoom thauj khoom yog redundant synchronization qee qhov haujlwm uas tsis xav tau. Qee zaum "vim lawv tsis pom", qee zaum "nws yooj yim dua li ntawd", tab sis tsis ntev los sis tom qab koj yuav tsum tau tshem nws.
Cia peb zoom rau hauv daim duab dhau los thiab pom tias peb muaj lub disk "pumps" nyob rau hauv lub load nrog ob amplitude nruab nrab ntawm cov qauv uas nyob ib sab, uas kom meej meej "statistically" yuav tsum tsis txhob tshwm sim nrog xws li ib tug xov tooj ntawm kev khiav hauj lwm:
Qhov no yog qhov yooj yim heev kom ua tiav. Peb twb pib soj ntsuam lawm yuav luag 1000 servers, txhua tus tau ua tiav los ntawm cov xov xwm sib cais, thiab txhua txoj xov rov pib dua cov ntaub ntawv khaws tseg kom xa mus rau cov ntaub ntawv ntawm qee zaus, qee yam zoo li no:
setInterval(sendToDB, interval)Qhov teeb meem ntawm no lies precisely nyob rau hauv lub fact tias tag nrho cov xov pib ntawm kwv yees li tib lub sijhawm, yog li lawv lub sij hawm xa yuav luag ib txwm coincide "mus rau qhov point." Aws #2...
Hmoov zoo, qhov no yog qhov yooj yim heev los kho, ntxiv qhov "random" run-up los ntawm lub sijhawm:
setInterval(sendToDB, interval * (1 + 0.1 * (Math.random() - 0.5)))#4. Peb cache yam peb xav tau
Qhov thib peb tsoos highload teeb meem yog tsis muaj cache nws nyob qhov twg yuav ua.
Piv txwv li, peb tau ua kom nws muaj peev xwm los soj ntsuam ntawm cov phiaj xwm nodes (tag nrho cov no Seq Scan on users), tab sis tam sim ntawd xav tias lawv yog, rau feem ntau, tib yam - lawv tsis nco qab.
Tsis yog, tau kawg, tsis muaj dab tsi sau rau hauv database dua, qhov no txiav tawm qhov ua rau INSERT ... ON CONFLICT DO NOTHING. Tab sis cov ntaub ntawv no tseem mus txog lub database, thiab nws tsis tsim nyog nyeem ntawv los xyuas qhov tsis sib haum xeeb yuav tsum ua. Aws #3...
Qhov sib txawv ntawm cov ntaub ntawv xa mus rau cov ntaub ntawv ua ntej / tom qab caching yog qhov pom tseeb:
Thiab qhov no yog qhov kev poob qis hauv kev khaws cia:
Tag nrho
"Terabyte-ib-hnub" tsuas yog suab txaus ntshai. Yog tias koj ua txhua yam zoo, ces qhov no tsuas yog 2^40 bytes / 86400 vib nas this = ~ 12.5MB / suas txawm tias desktop IDE screws tuav. π
Tab sis qhov tseeb, txawm tias muaj kaum npaug "skew" ntawm lub sijhawm nruab hnub, koj tuaj yeem ua tau raws li cov peev txheej ntawm SSDs niaj hnub no.
Tau qhov twg los: www.hab.com