Munguva pfupi yapfuura ndakakuudza kuti sei, uchishandisa mabikirwo akajairwa kubva kuPostgreSQL database. Nhasi tichataura kuti sei kurekodha kunogona kuitwa zvakanyanya mune dhatabhesi pasina kushandisa chero "twist" mugadziriso - nekungorongedza nemazvo kuyerera kwedata.
#1. Sectioning
Chinyorwa pamusoro pekuti sei uye nei zvakakodzera kuronga zvatove, pano tichataura nezve tsika yekushandisa dzimwe nzira mukati medu .
"Zvinhu zvemazuva apfuura ..."
Pakutanga, seMVP chero ipi zvayo, chirongwa chedu chakatanga pasi pemutoro wakareruka - kutarisa kwakaitwa chete kune gumi nemaviri emaseva anonyanya kukosha, matafura ose akanga akakwana ... nezvimwe, uye zvakare takaedza kuita chimwe chinhu neimwe ye matafura 1.5TB muhukuru, takaona kuti kunyange zvazvo zvaiita kuti tirambe tichirarama seizvi, zvakanga zvisingafadzi.
Nguva dzaive dzakaita kunge epic nguva, shanduro dzakasiyana dzePostgreSQL 9.x dzaive dzakakodzera, saka kupatsanura kwese kwaifanira kuitwa "nemaoko" - kuburikidza tafura nhaka uye zvinokonzeresa nzira ine dynamic EXECUTE.
Mhinduro yakaguma yave yepasirese zvakakwana zvekuti inogona kushandurirwa kumatafura ese:
- Iyo isina "musoro" tafura yevabereki yakaziviswa, iyo yakatsanangura zvese inodiwa indexes uye zvinokonzeresa.
- Iyo rekodhi kubva pakuona kwemutengi yakaitwa mu "mudzi" tafura, uye mukati kushandisa routing trigger
BEFORE INSERTrekodhi yaive "pamuviri" yakaiswa muchikamu chaidiwa. Kana pakanga pasati pave nechinhu chakadaro, takabata musiyano uye ... - β¦ nekushandisa yakagadzirwa zvichibva pane template yetafura yevabereki chikamu chine kurambidzwa pazuva raunodakuitira kuti kana data yatorwa, kuverenga kunoitwa mairi chete.
PG10: kuedza kwekutanga
Asi kupatsanura kuburikidza nenhaka kare hakuna kunyatsokodzera kushanda nerukova rwekunyora runoshanda kana nhamba huru yezvikamu zvevazukuru. Semuenzaniso, unogona kuyeuka kuti iyo algorithm yekusarudza chikamu chaidiwa yaive nayo quadratic yakaoma, kuti inoshanda ne100+ zvikamu, iwe pachako unonzwisisa sei ...
MuPG10 mamiriro aya akagadziridzwa zvakanyanya nekushandisa rutsigiro . Naizvozvo, isu takabva tangoedza kuishandisa nekukurumidza mushure mekutamisa chengetedzo, asi ...
Sezvazvakazoitika mushure mekuchera kuburikidza nebhuku rekushandisa, iyo natively partitioned tafura mune iyi vhezheni ndeye:
- haitsigire tsananguro dze index
- haitsigire zvinokonzeresa pairi
- haagoni kuva "muzukuru" wemumwe munhu
- musatsigira
INSERT ... ON CONFLICT - haikwanise kugadzira chikamu otomatiki
Sezvo tagamuchira kurohwa kunorwadza pahuma nereki, takaona kuti hazvigoneke kuita tisina kushandura chikumbiro, uye takaverengera kumwe kutsvagisa kwemwedzi mitanhatu.
PG10: mukana wechipiri
Saka, takatanga kugadzirisa matambudziko akamuka rimwe nerimwe:
- Nekuti zvinokonzeresa uye
ON CONFLICTTakaona tichiri kudzida pano neapo, saka takagadzira intermediate stage yekudzigadzirisa proxy table. - Rasa "routing" mune zvinokonzeresa - kureva, kubva
EXECUTE. - Vakaiburitsa voga template tafura ine zvese indexeszvekuti havatombovepo muproxy table.
Pakupedzisira, mushure mezvose izvi, takagovera tafura huru natively. Kugadzirwa kwechikamu chitsva kuchiri kusiiwa kuhana yechikumbiro.
"Sawing" maduramazwi
Sezviri mune chero analytical system, isu taivawo "chokwadi" uye "kucheka" (maduramazwi). Muchiitiko chedu, mune iyi chinzvimbo ivo vakaita, semuenzaniso, mibvunzo inononoka yakafanana kana mavara emubvunzo wacho pachawo.
"Chokwadi" chakakamurwa nemasikati kwenguva yakareba, saka takadzima takadzikama zvikamu zvekare, uye hazvina kutinetsa (matanda!). Asi paive nedambudziko nemaduramazwi...
Kwete kutaura kuti pakanga paine akawanda, asi anenge 100TB ye "chokwadi" yakakonzera duramazwi re2.5TB. Iwe haugone kudzima chero chinhu kubva patafura yakadaro, haugone kuimanikidza nenguva yakaringana, uye kuinyorera zvishoma nezvishoma.
Seduramazwi... mariri, chinonyorwa chega chega chinofanira kuratidzwa kamwe chete... uye izvi ndizvozvo, asi!.. Hapana ari kutitadzisa kuva nazvo. duramazwi rakasiyana rezuva rega rega! Ehe, izvi zvinounza kumwe kudzikiswa, asi zvinobvumira:
- nyora/verenga nekukurumidza nekuda kwekukura kwechikamu chidiki
- shandisa ndangariro shoma nekushanda nemamwe macompact indexes
- chengetedza data shoma nekuda kwekugona kukurumidza kubvisa zvechinyakare
Somugumisiro wekuoma kwese kwezviyero CPU mutoro wakadzikira ne ~ 30%, disk mutoro ne ~ 50%:
Panguva imwecheteyo, takaenderera mberi nekunyora chinhu chimwe chete mudhatabhesi, nekungotakura zvishoma.
#2. Database evolution uye refactoring
Saka takagara pane zvatinazvo zuva rimwe nerimwe rine chikamu charo nedata. Chokwadi, CHECK (dt = '2018-10-12'::date) - uye kune kiyi yekuparadzanisa uye mamiriro ekuti rekodhi riwire mune chimwe chikamu.
Sezvo mishumo yese iri musevhisi yedu yakavakirwa mukati mezuva chairo, iwo indexes avo kubva "zvisina kuganhurwa nguva" anga ari marudzi ese. (Sevha, Musi, Ronga template), (Sevha, Musi, Plan node), (Musi, Kirasi yezvikanganiso, Sevha)...
Asi iye zvino vanogara pachikamu chimwe nechimwe makopi ako imwe neimwe yakadaro index ... Uye mukati mechikamu chimwe nechimwe date inogara iripo... Zvinoitika kuti ikozvino tiri mune imwe neimwe index yakadaro ingopinda isingachinji seimwe yeminda, iyo inowedzera huwandu hwayo uye nguva yekutsvaga kwayo, asi haina kuunza chero mhedzisiro. Vakasiya raki voga, maiwe...
Iyo nzira ye optimization iri pachena - iri nyore bvisa iyo date field kubva kune ese indexes pamatafura akapatsanurwa. Tichipihwa mavhoriyamu edu, kuwana kuri kuda 1TB/vhiki!
Zvino ngationei kuti iyi terabyte ichiri kufanira kurekodhwa neimwe nzira. Ndiko kuti, nesuwo iyo dhisiki inofanirwa kurodha zvishoma! Mufananidzo uyu unoratidza zvakajeka maitiro akawanikwa kubva mukucheneswa, kwatakazvipira vhiki:
#3. "Kuparadzira" mutoro wepamusoro
Imwe yematambudziko makuru eakarodha masisitimu ndeye redundant synchronization mamwe maoparesheni asingade. Dzimwe nguva "nokuti havana kucherechedza", dzimwe nguva "zvaiva nyore nenzira iyo", asi nokukurumidza kana kuti gare gare unofanira kuibvisa.
Ngatiswededzei pamufananidzo wapfuura uye tione kuti tine dhisiki "mapombi" pasi pemutoro ane kaviri amplitude pakati pemasamples ari padyo, izvo zvakajeka "nhamba" hazvifanirwe kuitika nehuwandu hwekuita:
Izvi zviri nyore kuwana. Takatotanga kuita monitoring anenge 1000 maseva, imwe neimwe inogadziriswa netambo yakasiyana, uye imwe neimwe tambo inogadzirisa zvakare ruzivo rwakaunganidzirwa kuti rutumirwe kune dhatabhesi pane imwe nguva, chimwe chinhu chakadai:
setInterval(sendToDB, interval)Dambudziko riri pano riri pakuti tambo dzese dzinotanga panguva imwe chete, saka nguva dzavo dzokutumira dzinenge nguva dzose dzinopindirana βpachokwadi.β Hoo #2...
Neraki, izvi zviri nyore kugadzirisa, kuwedzera "random" kumhanya-up nenguva:
setInterval(sendToDB, interval * (1 + 0.1 * (Math.random() - 0.5)))#4. Tinochengeta zvatinoda
Yechitatu yechinyakare highload dambudziko iri hapana cache paari aigona kuva.
Semuenzaniso, isu takaita kuti zvikwanise kuongorora maererano nemanodhi ehurongwa (ese aya Seq Scan on users), asi pakarepo funga kuti ivo, kazhinji, vakafanana - vakakanganwa.
Kwete, hongu, hapana chakanyorwa kune dhatabhesi zvakare, izvi zvinodimbura chinokonzeresa nacho INSERT ... ON CONFLICT DO NOTHING. Asi iyi data ichiri kusvika kune database, uye haina basa kuverenga kutarisa kunetsana kuita. Hoo #3...
Musiyano muhuwandu hwemarekodhi akatumirwa kune dhatabhesi isati / mushure mekuita caching yagoneswa iri pachena:
Uye uku ndiko kudonha kunoperekedzwa kwemutoro wekuchengetedza:
Total
"Terabyte-per-day" inongonzwika inotyisa. Kana iwe ukaita zvese nemazvo, saka izvi ndizvo chete 2^40 bytes / 86400 seconds = ~12.5MB/siyo kunyange desktop IDE screws yakabatwa. π
Asi zvakakomba, kunyangwe nekagumi "skew" yemutoro mukati mezuva, unogona kusangana nyore nekugona kweazvino SSD.
Source: www.habr.com