Ukuthuthela ku-ClickHouse: eminyakeni emi-3 kamuva

Eminyakeni emithathu edlule uViktor Tarnavsky no-Alexey Milovidov baseYandex esiteji Ukulayisha Okuphezulu++ utshele, i-ClickHouse enhle kangakanani, nokuthi ayinciphisi. Futhi esiteji esilandelayo kwakukhona U-Александр Зайцев с umbiko mayelana nokuthuthela ChofozaHouse kusuka kwenye i-DBMS yokuhlaziya futhi nesiphetho sokuthi ChofozaHouse, yebo, muhle, kodwa akulula kakhulu. Lapho ngo-2016 inkampani LifeStreet, lapho u-Alexander esebenza khona, wayeguqulela uhlelo lokuhlaziya lwe-multi-petabyte ChofozaHouse, “kwakuwumgwaqo wezitini ophuzi” othakazelisayo ogcwele izingozi ezingaziwa - ChofozaHouse emuva ngaleso sikhathi kwakubukeka njengenkundla yemigodi.

Ngemva kweminyaka emithathu ChofozaHouse yaba ngcono kakhulu - ngalesi sikhathi u-Alexander wasungula inkampani i-Altinity, engagcini nje ngokusiza abantu ukuba bathuthele ChofozaHouse inqwaba yamaphrojekthi, kodwa futhi ithuthukisa umkhiqizo ngokwawo kanye nozakwabo bakwaYandex. Manje ChofozaHouse namanje akukona ukuhambahamba ngokunganaki, kodwa akuseyona indawo yamabhomu.

U-Alexander ubesebenza nezinhlelo ezisabalalisiwe kusukela ngo-2003, ethuthukisa amaphrojekthi amakhulu I-MySQL, i-Oracle и I-Vertica. Okokugcina I-HighLoad++ 2019 Alexander, omunye wabasunguli bokusebenzisa ChofozaHouse, utshele ukuthi iyini le DBMS manje. Sizofunda ngezici eziyinhloko ChofozaHouse: ihluke kanjani kwezinye izinhlelo futhi kuziphi izimo lapho iphumelela kakhulu ukuyisebenzisa. Sisebenzisa izibonelo, sizobheka izinqubo zakamuva nezihlolwe iphrojekthi zamasistimu okwakha asekelwe ChofozaHouse.

I-Retrospective: okwenzeka eminyakeni engu-3 edlule

Eminyakeni emithathu edlule sayidlulisela inkampani LifeStreet on ChofozaHouse kusuka kwenye isizindalwazi sokuhlaziya, kanye nokufuduka kwezibalo zenethiwekhi yesikhangiso kubukeke kanje:

• Juni 2016. Ngo I-OpenSource avele ChofozaHouse futhi iphrojekthi yethu yaqala;
• Agasti. Ubufakazi Bemiqondo: inethiwekhi enkulu yokukhangisa, ingqalasizinda kanye nedatha engu-200-300 terabytes;
• Okthoba. Idatha yokuqala yokukhiqiza;
• Disemba. Umthwalo ogcwele womkhiqizo yimicimbi eyizigidigidi eziyi-10-50 ngosuku.
• Juni 2017. Ukuthutha okuyimpumelelo kwabasebenzisi ukuya ChofozaHouse, 2,5 amapetabhayithi edatha kuqoqo lamaseva angu-60.

Phakathi nenqubo yokufuduka, kwaba nokuqonda okukhulayo lokho ChofozaHouse iwuhlelo oluhle okumnandi ukusebenza nalo, kodwa lena iphrojekthi yangaphakathi yeYandex. Ngakho-ke, kukhona ama-nuances: i-Yandex izoqala ukubhekana namakhasimende ayo angaphakathi bese kuphela ngomphakathi kanye nezidingo zabasebenzisi bangaphandle, futhi i-ClickHouse ke ayizange ifinyelele izinga lebhizinisi ezindaweni eziningi zokusebenza. Kungakho sasungula i-Altinity ngoMashi 2017 ukwenza ChofozaHouse ngokushesha futhi kulula kakhulu hhayi ku-Yandex kuphela, kodwa nakwabanye abasebenzisi. Futhi manje thina:

• Siqeqesha futhi sisize ukwakha izixazululo ngokusekelwe ChofozaHouse ukuze amakhasimende angangeni enkingeni, futhi ukuze isisombululo ekugcineni sisebenze;
• Sihlinzeka ngosekelo lwe-24/7 ChofozaHouse- ukufakwa;
• Sithuthukisa amaphrojekthi ethu e-ecosystem;
• Sizibophezela ngokwethu ChofozaHouse, ephendula izicelo zabasebenzisi abafuna ukubona izici ezithile.

Futhi-ke, siyasiza ngokuthuthela ChofozaHouse с MySQL, I-Vertica, Oracle, I-Greenplum, I-Redshift nezinye izinhlelo. Sibambe iqhaza emigudwini eyahlukene, futhi yonke iphumelele.

Kungani uthuthele ku ChofozaHouse

Ayinciphisi! Lesi isizathu esiyinhloko. ChofozaHouse - I-database esheshayo kakhulu yezimo ezahlukene:

Izingcaphuno ezingahleliwe ezivela kubantu asebesebenze nabantu isikhathi eside ChofozaHouse.

I-Scalability. Kwesinye isizindalwazi ungafinyelela ukusebenza okuhle kocezu olulodwa lwehadiwe, kodwa ChofozaHouse awukwazi ukukala hhayi kuphela mpo, kodwa futhi nendawo, ngokungeza amaseva. Konke akusebenzi kahle njengoba besingathanda, kodwa kuyasebenza. Ungakwazi ukwandisa uhlelo njengoba ibhizinisi lakho likhula. Kubalulekile ukuthi singanqunyelwe isisombululo manje futhi kuhlale kukhona okungase kube nentuthuko.

Ukuphatheka. Akukho okunamathiselwe entweni eyodwa. Ngokwesibonelo, nge I-Amazon Redshift Kunzima ukuthuthela kwenye indawo. A ChofozaHouse ungayifaka kukhompyutha yakho ephathekayo, iseva, uyihambise efwini, iya ku Kubernetes - akukho mingcele ekusebenzeni kwengqalasizinda. Lokhu kulungele wonke umuntu, futhi lokhu kuyinzuzo enkulu eminye imininingwane eminingi efanayo engakwazi ukuziqhayisa ngayo.

Ukuvumelana nezimo. ChofozaHouse ayimi entweni eyodwa, ngokwesibonelo, i-Yandex.Metrica, kodwa ithuthukisa futhi isetshenziswe kumaphrojekthi nezimboni eziningi kakhulu. Inganwetshwa ngokungeza amakhono amasha ukuxazulula izinkinga ezintsha. Isibonelo, kukholakala ukuthi ukugcina izingodo ku-database kuwukuziphatha okubi, ngakho baqhamuke Islastiki. Kodwa ngenxa yokuvumelana nezimo ChofozaHouse, ungakwazi futhi ukugcina izingodo kuyo, futhi ngokuvamile lokhu kuba ngcono nakakhulu kunangaphakathi Islastiki - v ChofozaHouse lokhu kudinga insimbi ephindwe izikhathi ezingu-10.

Mahhala Open Source. Awudingi ukukhokhela noma yini. Asikho isidingo sokuxoxisana ngemvume yokufaka uhlelo kukhompuyutha ephathekayo noma kwiseva yakho. Azikho izimali ezifihliwe. Ngaso leso sikhathi, abukho obunye ubuchwepheshe besizindalwazi se-Open Source bungaqhudelana ngesivinini ChofozaHouse. MySQL, MariaDB, Greenplum - zonke zihamba kancane kakhulu.

Umphakathi, shayela futhi fun. At ChofozaHouse umphakathi omuhle kakhulu: ukuhlangana, izingxoxo kanye no-Alexey Milovidov, osikhokhisa sonke ngamandla akhe kanye nethemba.

Iya ku-ClickHouse

Ukuya ku ChofozaHouse ngesizathu esithile, udinga izinto ezintathu kuphela:

• Qonda ukulinganiselwa ChofozaHouse nokuthi yini engafanele.
• Thatha inzuzo ubuchwepheshe kanye namandla abo amakhulu.
• Isivivinyo. Ngisho nokuqonda ukuthi isebenza kanjani ChofozaHouse, akwenzeki ngaso sonke isikhathi ukubikezela ukuthi izoshesha nini, lapho izohamba kancane, ukuthi izoba ngcono nini, futhi izoba kubi nakakhulu nini. Ngakho-ke yizame.

Ukuhambisa inkinga

Kukhona eyodwa kuphela "kodwa": uma uthuthela ChofozaHouse kusuka kwenye into, khona-ke ngokuvamile kukhona okungahambi kahle. Sijwayele ezinye izinqubo nezinto ezisebenza kusizindalwazi sethu esiyintandokazi. Isibonelo, noma ubani osebenza naye SQI-L-database ibheka isethi elandelayo yemisebenzi njengesibopho:

• ukuthengiselana;
• izithiyo;
• ukungaguquguquki;
• indices;
• BUYEKEZA/SUSA;
• NULLs;
• ama-millisecond;
• izinhlobo ezizenzakalelayo;
• ukuhlanganisa okuningi;
• ukwahlukanisa okungahleliwe;
• amathuluzi okuphatha iqoqo.

Ukuqasha kuyimpoqo, kodwa eminyakeni emithathu edlule ChofozaHouse Ayikho kule misebenzi ebitholakala! Manje okungaphansi kwengxenye yalokho okungakaqaliswa kusasele: okwenziwayo, imikhawulo, Ukuvumelana, ama-millisecond kanye nohlobo lokusakaza.

Futhi into esemqoka ukuthi ku ChofozaHouse ezinye izinqubo ezijwayelekile nezindlela azisebenzi noma azisebenzi ngendlela ehlukile kunaleyo esiyijwayele. Konke okuvela ngaphakathi ChofozaHouse, ihambisana nokuthi "Indlela ye-ClickHouse", i. imisebenzi ihlukile kwezinye isizindalwazi. Ngokwesibonelo:

• Izinkomba azikhethiwe, kodwa zeqiwe.
• BUYEKEZA/SUSA hhayi synchronous, kodwa asynchronous.
• Kukhona ukujoyina okuningi, kodwa asikho isihleli semibuzo. Ukuthi zenziwa kanjani ke ngokuvamile akucaci kahle kubantu abavela emhlabeni wesizindalwazi.

I-ClickHouse Scripts

Ngo-1960, isazi sezibalo saseMelika somsuka waseHungary Wigner EP wabhala isihloko "Ukusebenza okungenangqondo kwezibalo kusayensi yemvelo” (“Ukuphumelela Okungaqondakali Kwezibalo Ezifundweni Zemvelo”) ukuthi izwe elisizungezile lichazwe kahle ngesizathu esithile imithetho yezibalo. Izibalo ziyisayensi engabonakali, futhi imithetho yemvelo evezwe ngendlela yezibalo ayiyona into encane, futhi Wigner EP wagcizelela ukuthi lokhu kuyamangaza kakhulu.

Ngokombono wami, ChofozaHouse - ukumangala okufanayo. Ukuchaza kabusha i-Wigner, singasho lokhu: ukusebenza kahle okungaqondakali kuyamangaza ChofozaHouse ezinhlobonhlobo zezicelo zokuhlaziya!

Ngokwesibonelo, ake sithathe I-Real-Time Data Warehouse, lapho idatha ilayishwa khona cishe ngokuqhubekayo. Sifuna ukwamukela izicelo ezivela kuyo ngokulibaziseka kwesibili. Sicela - uyisebenzise ChofozaHouse, ngoba lesi yisimo eyakhelwe sona. ChofozaHouse iyona kanye indlela esetshenziswa ngayo hhayi kuwebhu kuphela, kodwa nasekuhlaziyeni ukumaketha nezezimali, I-AdTech, kanye naku Ukutholwa kokukhwabanisan. IN Real-time Data Warehouse uhlelo oluyinkimbinkimbi oluhlelekile olufana "nenkanyezi" noma "ikhekheba leqhwa" lisetshenziswa, amatafula amaningi ane JOIN (kwesinye isikhathi amaningi), futhi idatha ivamise ukugcinwa futhi ishintshwe kwamanye amasistimu.

Ake sithathe esinye isimo - Uchungechunge Lwesikhathi: ukuqapha amadivayisi, amanethiwekhi, izibalo zokusetshenziswa, i-inthanethi yezinto. Lapha sihlangabezana nemicimbi elula ehlelwe ngesikhathi. ChofozaHouse ayizange yenzelwe lokhu ekuqaleni, kodwa ibonise ukuthi isebenza kahle, yingakho izinkampani ezinkulu zisebenzisa ChofozaHouse njengendawo yokugcina ulwazi lokuqapha. Ukuhlola ukuthi ifanelekile yini ChofozaHouse ochungechungeni lwesikhathi, senze isilinganiso esisuselwe endleleni kanye nemiphumela I-InfluxDB и I-TimescaleDB - ezikhethekile uchungechunge lwesikhathi database. Kwavela, lokho ChofozaHouse, ngisho nangaphandle kokwenza imisebenzi enjalo, iwina endaweni yangaphandle:

В uchungechunge lwesikhathi Ngokuvamile kusetshenziswa itafula elincane - amakholomu amaningana amancane. Idatha eminingi ingavela ekuqaphelweni-izigidi zamarekhodi ngomzuzwana-futhi imvamisa iza ngamaqhubu amancane (isikhathi sangempela ukusakaza). Ngakho-ke, umbhalo ohlukile wokufaka uyadingeka, futhi imibuzo ngokwayo inemininingwane yayo.

Ukuphathwa Kwengena. Ukuqoqa izingodo kusizindalwazi ngokuvamile kubi, kodwa ChofozaHouse lokhu kungenziwa ngokuphawula okuthile njengoba kuchazwe ngenhla. Izinkampani eziningi zisebenzisa ChofozaHouse ngqo ngale njongo. Kulokhu, sisebenzisa ithebula elibanzi eliyisicaba lapho sigcina khona wonke amalogi (isibonelo, efomini I-JSON), noma usike zibe izingcezu. Idatha ivamise ukulayishwa ngamaqoqo amakhulu (amafayela), futhi sisesha ngenkambu ethile.

Kuleyo naleyo misebenzi, isizindalwazi esikhethekile sivamise ukusetshenziswa. ChofozaHouse umuntu angakwenza konke futhi kahle kangangokuthi kuyakudlula. Manje ake sibhekisise uchungechunge lwesikhathi isimo, nendlela “yokupheka” ngendlela efanele ChofozaHouse kulesi simo.

Uchungechunge Lwesikhathi

Njengamanje lesi yisimo esiyinhloko okumele sikwenze ChofozaHouse kucatshangelwe isisombululo esijwayelekile. Uchungechunge lwesikhathi iyiqoqo lezehlakalo ezihlelwe ngesikhathi, ezimele izinguquko kunqubo ethile ngokuhamba kwesikhathi. Isibonelo, lokhu kungaba izinga lokushaya kwenhliziyo ngosuku noma inani lezinqubo ohlelweni. Konke okunikeza isikhathi imikhaza nge izilinganiso ezithile kuyinto uchungechunge lwesikhathi:

Iningi lalezi zinhlobo zemicimbi livela ekuqaphelweni. Lokhu akukwazi ukuqapha iwebhu kuphela, kodwa futhi namadivayisi wangempela: izimoto, izinhlelo zezimboni, IoT, izimboni noma amatekisi angenamuntu, esiqwini lapho i-Yandex isivele ibeka khona ChofozaHouse-seva.

Isibonelo, kunezinkampani eziqoqa idatha emikhunjini. Njalo ngemizuzwana embalwa, izinzwa emkhunjini weziqukathi zithumela amakhulukhulu ezilinganiso ezihlukene. Onjiniyela bayazifunda, bakhe amamodeli futhi bazame ukuqonda ukuthi umkhumbi usetshenziswa kanjani kahle, ngoba umkhumbi wamabhokisi akufanele ungasebenzi ngisho nomzuzwana. Noma yisiphi isikhathi sokuphumula siwukulahlekelwa yimali, ngakho-ke kubalulekile ukubikezela umzila ukuze ukumisa kube kuncane.

Namuhla kukhona ukukhula kolwazi olukhethekile olukalayo uchungechunge lwesikhathi. Esizeni Izinjini ze-DB Izingosi zolwazi ezihlukene zibalwa ngandlela thize, futhi ungazibuka ngohlobo:

Uhlobo olukhula ngokushesha kakhulu uchungechunge lwesikhathis. Imininingwane yegrafu nayo iyakhula, kodwa uchungechunge lwesikhathiibikhula ngokushesha eminyakeni embalwa edlule. Abameleli abajwayelekile balo mndeni wemininingwane yolwazi I-InfluxDB, Prometheus, I-KDB, I-TimescaleDB (yakhiwe phezu I-PostgreSQL), izixazululo ezivela Amazon. ChofozaHouse ingasetshenziswa lapha futhi, futhi iyasetshenziswa. Ake ngikunike izibonelo ezimbalwa zomphakathi.

Omunye wamavulandlela yinkampani I-CloudFlare (CDN-umhlinzeki). Baqaphe okwabo CDN ngokusebenzisa ChofozaHouse (DNS- izicelo, HTTP-imibuzo) enomthwalo omkhulu - imicimbi eyizigidi ezingu-6 ngomzuzwana. Konke kudlula KAFKA, eya ku ChofozaHouse, okunikeza ithuba lokubona amadeshibhodi emicimbi ohlelweni ngesikhathi sangempela.

I-Comcast - omunye wabaholi kwezokuxhumana ngocingo e-USA: I-inthanethi, ithelevishini yedijithali, ucingo. Bakha isistimu yokulawula efanayo CDN ngaphakathi Open Source iphrojekthi I-Apache Traffic Control ukusebenza ngedatha yakho enkulu. ChofozaHouse isetshenziswe njenge-backend ye-analytics.

IPercona eyakhelwe ngaphakathi ChofozaHouse ngaphakathi kwakho PMMukugcina ukuqapha ezihlukahlukene MySQL.

Izidingo Eziqondile

Imininingwane yochungechunge lwesikhathi inezidingo zayo ezithile.

• Ukufakwa okusheshayo okuvela kuma-ejenti amaningi. Kufanele sifake idatha evela ekusakazweni okuningi ngokushesha okukhulu. ChofozaHouse Ikwenza kahle lokhu ngoba konke okufakiwe akuvimbi. Noma yikuphi ukufaka iyifayela elisha kudiski, futhi okufakiwe okuncane kungenziwa kuvikelwe ngendlela eyodwa noma enye. IN ChofozaHouse Kungcono ukufaka idatha ngamaqoqo amakhulu kunomugqa owodwa ngesikhathi.
• Isikimu esivumelana nezimo. I uchungechunge lwesikhathi ngokuvamile asazi ngokuphelele ukwakheka kwedatha. Kungenzeka ukwakha uhlelo lokuqapha lwesicelo esithile, kodwa-ke kunzima ukuyisebenzisela olunye uhlelo lokusebenza. Lokhu kudinga isikimu esivumelana nezimo. ChofozaHouse, ikuvumela ukuthi wenze lokhu, nakuba kuyisizinda esithayiphiwe ngokuqinile.
• Ukugcinwa okusebenzayo nokukhohlwa idatha. Ngokuvamile ku uchungechunge lwesikhathi inani elikhulu ledatha, ngakho-ke kufanele igcinwe ngokuphumelelayo ngangokunokwenzeka. Ngokwesibonelo, at I-InfluxDB ukucindezela okuhle kuyisici sayo esiyinhloko. Kodwa ngaphandle kokugcina, udinga futhi ukwazi "ukukhohlwa" idatha endala futhi wenze uhlobo oluthile isampula - ukubala okuzenzakalelayo kwama-aggregate.
• Imibuzo esheshayo kudatha ehlanganisiwe. Kwesinye isikhathi kuyathakazelisa ukubheka imizuzu engu-5 yokugcina ngokunemba kwama-milliseconds, kodwa ngeminithi ledatha yanyanga zonke noma imbudumbudu yesibili ingase ingadingeki - izibalo ezijwayelekile zanele. Ukwesekwa kwalolu hlobo kuyadingeka, ngaphandle kwalokho isicelo sezinyanga ezi-3 sizothatha isikhathi eside kakhulu ukuqeda ngisho naphakathi ChofozaHouse.
• Izicelo ezifana nokuthi "iphuzu lokugcina, kusukela». Lezi zijwayelekile ze uchungechunge lwesikhathi imibuzo: bheka isikali sokugcina noma isimo sesistimu ngesikhathi esithile t. Lena akuyona imibuzo emnandi kakhulu yesizindalwazi, kodwa udinga futhi ukwazi ukuyenza.
• "Gluing" uchungechunge lwesikhathi. Uchungechunge lwesikhathi uchungechunge lwesikhathi. Uma kunochungechunge lwezikhathi ezimbili, ngokuvamile zidinga ukuxhunywa futhi zihlotshaniswe. Akulungile ukwenza lokhu kuzo zonke izingosi zolwazi, ikakhulukazi ngochungechunge lwesikhathi olungahleliwe: nawa amaphuzu esikhathi, kukhona amanye. Ungacabangela isilinganiso, kodwa ngokuzumayo kusazoba nomgodi lapho, ngakho-ke akucaci.

Ake sibone ukuthi lezi zidingo zihlangatshezwa kanjani ChofozaHouse.

Lolu hlelo

В ChofozaHouse uhlelo lwe uchungechunge lwesikhathi kungenziwa ngezindlela ezahlukene, kuye ngezinga lokujwayelekile kwedatha. Kungenzeka ukwakha isistimu kudatha evamile uma sazi wonke ama-metrics kusengaphambili. Ngokwesibonelo, ngenza lokhu I-CloudFlare ngokuqapha CDN iyisistimu eyenziwe kahle. Ungakha uhlelo olujwayelekile kakhulu oluqapha yonke ingqalasizinda kanye nezinsizakalo ezahlukahlukene. Endabeni yedatha engavamile, asazi kusengaphambili ukuthi yini esiyiqaphayo - futhi lokhu cishe kuyisimo esivame kakhulu.

Idatha evamile. Amakholomu. Uhlelo lulula - amakholomu anezinhlobo ezidingekayo:

CREATE TABLE cpu (
  created_date Date DEFAULT today(),  
  created_at DateTime DEFAULT now(),  
  time String,  
  tags_id UInt32,  /* join to dim_tag */
  usage_user Float64,  
  usage_system Float64,  
  usage_idle Float64,  
  usage_nice Float64,  
  usage_iowait Float64,  
  usage_irq Float64,  
  usage_softirq Float64,  
  usage_steal Float64,  
  usage_guest Float64,  
  usage_guest_nice Float64
) ENGINE = MergeTree(created_date, (tags_id, created_at), 8192);

Leli ithebula elivamile eliqapha uhlobo oluthile lomsebenzi wokulayisha wesistimu (umsebenzisi, uhlelo, Ungenzi lutho, nice). Ilula futhi ifanelekile, kodwa ayiguquki. Uma sifuna isikimu esivumelana nezimo, singasebenzisa amalungu afanayo.

Idatha engajwayelekile. Izinhlu:

CREATE TABLE cpu_alc (
  created_date Date,  
  created_at DateTime,  
  time String,  
  tags_id UInt32,  
  metrics Nested(
    name LowCardinality(String),  
    value Float64
  )
) ENGINE = MergeTree(created_date, (tags_id, created_at), 8192);

SELECT max(metrics.value[indexOf(metrics.name,'usage_user')]) FROM ...

isakhiwo Isidleke kunezigaba ezimbili: metrics.name и inani.metrics. Lapha ungakwazi ukugcina idatha enjalo yokuqapha njengohlu lwamagama kanye nezilinganiso eziningi zomcimbi ngamunye. Ukuze wenze okwengeziwe, esikhundleni sesakhiwo esisodwa esinjalo, ungenza eziningana. Ngokwesibonelo, eyodwa for float-value, enye - for Int-okusho ngoba Int Ngifuna ukugcina kahle kakhulu.

Kodwa isakhiwo esinjalo sinzima kakhulu ukufinyelela. Kuzodingeka usebenzise ukwakhiwa okukhethekile, usebenzisa imisebenzi ekhethekile ukukhipha amanani okuqala wenkomba bese kulandela uhlu:

SELECT max(metrics.value[indexOf(metrics.name,'usage_user')]) FROM ...

Kodwa kusasebenza ngokushesha okukhulu. Enye indlela yokugcina idatha engajwayelekile ngomugqa.

Idatha engajwayelekile. Izintambo. Kule ndlela yendabuko, ngaphandle kwama-arrays, amagama namanani agcinwa kanyekanye. Uma izilinganiso ezingu-5 zivela kudivayisi eyodwa ngesikhathi esisodwa, imigqa engu-000 ikhiqizwa kusizindalwazi:

CREATE TABLE cpu_rlc (
  created_date Date,  
  created_at DateTime,  
  time String,  
  tags_id UInt32,  
  metric_name LowCardinality(String),  
  metric_value Float64
) ENGINE = MergeTree(created_date, (metric_name, tags_id, created_at), 8192);


SELECT 
    maxIf(metric_value, metric_name = 'usage_user'),
    ... 
FROM cpu_r
WHERE metric_name IN ('usage_user', ...)

ChofozaHouse ibhekana nalokhu - inezandiso ezikhethekile ChofozaHouse SQL... Ngokwesibonelo, maxIf — umsebenzi okhethekile obala ubukhulu ngemethrikhi lapho umbandela othile ufinyelelwa. Ungabhala izinkulumo ezinjalo ezimbalwa esicelweni esisodwa bese ubale ngokushesha inani lamamethrikhi ambalwa.

Ake siqhathanise izindlela ezintathu:

imininingwane

Lapha ngingeze "Usayizi Wedatha Yediski" kusethi yedatha yokuhlola. Endabeni yamakholomu, sinosayizi omncane wedatha: ukucindezela okuphezulu, isivinini esikhulu sombuzo, kodwa sikhokha ngokuqopha yonke into ngesikhathi esisodwa.

Endabeni yama-arrays, konke kubi nakakhulu. Idatha isacindezelwe kahle futhi iphethini engajwayelekile ingagcinwa. Kodwa ChofozaHouse - i-database yekholomu, futhi lapho siqala ukugcina yonke into ngohlelo, iphenduka ibe umugqa owodwa, futhi sikhokhela ukuguquguquka ngokusebenza kahle. Kunoma yikuphi ukusebenza, kuzodingeka ufunde lonke uhlu enkumbulweni, bese uthola into oyifunayo kuyo - futhi uma uhlu lukhula, khona-ke isivinini siyehla.

Kwenye yezinkampani ezisebenzisa le ndlela (isibonelo, Uber), ama-arrays anqunywa abe yizicucu zezinto eziyi-128. Idatha evela kumamethrikhi ayizinkulungwane ezimbalwa enevolumu engu-200 TB yedatha/usuku ayigcinwa ohlwini olulodwa, kodwa kumalungu afanayo ayi-10 noma angu-30 anomqondo wesitoreji okhethekile.

Indlela elula ngezintambo. Kodwa idatha ayicindezelwa kahle, usayizi wetafula mkhulu, futhi noma imibuzo isekelwe kumamethrikhi amaningana, i-ClickHouse ayisebenzi kahle.

Isikimu seHybrid

Ake sicabange ukuthi sikhethe isifunda samalungu afanayo. Kodwa uma sazi ukuthi iningi lamadeshibhodi ethu abonisa kuphela amamethrikhi omsebenzisi nawesistimu, singakwazi ngokungeziwe ukwenza lawa mamethrikhi sibe amakholomu ukusuka kumalungu afanayo kuleveli yethebula ngale ndlela:

CREATE TABLE cpu_alc (
  created_date Date,  
  created_at DateTime,  
  time String,  
  tags_id UInt32,  
  metrics Nested(
    name LowCardinality(String),  
    value Float64
  ),
  usage_user Float64 
             MATERIALIZED metrics.value[indexOf(metrics.name,'usage_user')],
  usage_system Float64 
             MATERIALIZED metrics.value[indexOf(metrics.name,'usage_system')]
) ENGINE = MergeTree(created_date, (tags_id, created_at), 8192);

Uma ufaka ChofozaHouse izozibala ngokuzenzakalelayo. Ngale ndlela ungakwazi ukuhlanganisa ibhizinisi nenjabulo: uhlelo luyaguquguquka futhi lujwayelekile, kodwa sikhiphe amakholomu asetshenziswa kakhulu. Qaphela ukuthi lokhu kwakungadingi ukushintsha okufakiwe kanye I-ETLeqhubeka nokufaka amalungu afanayo etafuleni. Sisanda kwenza ALTER ITHEBULA, wengeze izipikha ezimbalwa futhi sathola isikimu esiyingxube nesisheshayo ongaqala ukusisebenzisa ngaso leso sikhathi.

Ama-codecs nokucindezela

Ukuze uchungechunge lwesikhathi Kubalulekile ukuthi uyipakisha kahle kangakanani idatha ngoba inani lolwazi lingaba likhulu kakhulu. IN ChofozaHouse Kukhona isethi yamathuluzi okuzuza umphumela wokucindezela ongu-1:10, 1:20, futhi ngezinye izikhathi nangaphezulu. Lokhu kusho ukuthi i-1 TB yedatha engapakiwe kudiski ithatha i-50-100 GB. Usayizi omncane muhle, idatha ingafundwa futhi icutshungulwe ngokushesha.

Ukuze uthole izinga eliphezulu lokucindezelwa, ChofozaHouse isekela ama-codec alandelayo:

Ithebula eliyisibonelo:

CREATE TABLE benchmark.cpu_codecs_lz4 (
    created_date Date DEFAULT today(), 
    created_at DateTime DEFAULT now() Codec(DoubleDelta, LZ4), 
    tags_id UInt32, 
    usage_user Float64 Codec(Gorilla, LZ4), 
    usage_system Float64 Codec(Gorilla, LZ4), 
    usage_idle Float64 Codec(Gorilla, LZ4), 
    usage_nice Float64 Codec(Gorilla, LZ4), 
    usage_iowait Float64 Codec(Gorilla, LZ4), 
    usage_irq Float64 Codec(Gorilla, LZ4), 
    usage_softirq Float64 Codec(Gorilla, LZ4), 
    usage_steal Float64 Codec(Gorilla, LZ4), 
    usage_guest Float64 Codec(Gorilla, LZ4), 
    usage_guest_nice Float64 Codec(Gorilla, LZ4), 
    additional_tags String DEFAULT ''
)
ENGINE = MergeTree(created_date, (tags_id, created_at), 8192);

Lapha sichaza i-codec I-DoubleDelta kwelinye icala, okwesibili - I-Gorilla, futhi ngokuqinisekile sizokwengeza okwengeziwe I-LZ4 ukucindezela. Ngenxa yalokho, usayizi wedatha kudiski wehliswa kakhulu:

Lokhu kubonisa ukuthi singakanani isikhala esithathwa yidatha efanayo, kodwa kusetshenziswa amakhodekhi ahlukene nokuminyanisa:

• kufayela le-GZIP kudiski;
• ku-ClickHouse ngaphandle kwama-codec, kodwa ngokucindezelwa kwe-ZSTD;
• ku-ClickHouse enama-codec nokucindezelwa kwe-LZ4 ne-ZSTD.

Kuyabonakala ukuthi amatafula anamakhodekhi athatha indawo encane kakhulu.

Izindaba zesikhulu

Akubalulekile kangako ukukhetha uhlobo lwedatha olulungile:

Kuzo zonke izibonelo ezingenhla engizisebenzisile Ukuntanta64. Kodwa uma sikhetha Ukuntanta32, khona-ke lokho kungaba ngcono nakakhulu. Lokhu kuboniswe kahle ngabafana basePerkona esihlokweni esixhunywe ngenhla. Kubalulekile ukusebenzisa uhlobo oluhlangene kakhulu olufanele umsebenzi: ngisho nangaphansi kosayizi wediski kunejubane lombuzo. ChofozaHouse ezwela kakhulu kulokhu.

Uma ungasebenzisa ibe 32 esikhundleni salokho ibe 64, bese ulindela ukukhuphuka cishe okuphindwe kabili ekusebenzeni. Idatha ithatha inkumbulo encane, futhi yonke "i-arithmetic" isebenza ngokushesha kakhulu. ChofozaHouse ngaphakathi iwuhlelo oluthayiphiwe ngokuqinile kakhulu, lwenza ukusetshenziswa okuphezulu kwawo wonke amathuba ahlinzekwa yizinhlelo zesimanje.

Ukuhlanganisa kanye Ukubukwa okwenziwe ngokwenyama

Ukubuka okuhlanganisiwe nokwenziwe kukuvumela ukuthi udale izilinganiso zezikhathi ezahlukahlukene:

Isibonelo, ungase ube nedatha yomthombo engahlanganisiwe, futhi ungakwazi ukunamathisela ukubukwa okuhlukahlukene kuyo ngokuhlanganisa okuzenzakalelayo ngenjini ekhethekile. I-SummingMergeTree (SMT). SMT iwuhlaka olukhethekile lwedatha ehlanganisayo olubala ukuhlanganiswa ngokuzenzakalelayo. Idatha eluhlaza ifakwa kusizindalwazi, ihlanganiswa ngokuzenzakalelayo, futhi amadeshibhodi angasetshenziswa kuyo ngokushesha.

I-TTL - "khohlwa" idatha endala

Indlela "yokukhohlwa" idatha engasadingeki? ChofozaHouse uyakwazi ukwenza lokhu. Lapho udala amatafula, ungacacisa I-TTL izinkulumo: isibonelo, ukuthi sigcina idatha yamaminithi yosuku olulodwa, idatha yansuku zonke izinsuku ezingu-30, futhi singalokothi sithinte idatha yeviki noma yenyanga:

CREATE TABLE aggr_by_minute
…
TTL time + interval 1 day

CREATE TABLE aggr_by_day
…
TTL time + interval 30 day

CREATE TABLE aggr_by_week
…
/* no TTL */

Izigaba eziningi - hlukanisa idatha kuwo wonke amadiski

Ukuqhubekisela phambili lo mbono, idatha ingagcinwa kuyo ChofozaHouse ezindaweni ezahlukene. Ake sithi sifuna ukugcina idatha eshisayo yeviki eledlule endaweni eshesha kakhulu I-SSD, futhi sibeka idatha yomlando eyengeziwe kwenye indawo. IN ChofozaHouse lokhu manje kungenzeka:

Ungamisa inqubomgomo yesitoreji (inqubomgomo yokugcina) Ngakho ChofozaHouse izodlulisela idatha ngokuzenzakalelayo lapho ifinyelela izimo ezithile iye kwesinye isitoreji.

Kodwa akugcini lapho. Ezingeni lethebula elithile, ungachaza imithetho yokuthi idatha ingena nini endaweni ebandayo. Isibonelo, idatha igcinwa kudiski esheshayo izinsuku ezingu-7, futhi yonke into endala idluliselwa kokunensa. Lokhu kuhle ngoba kukuvumela ukuthi ugcine uhlelo lusebenza kakhulu, ngenkathi ulawula izindleko futhi ungachithi imali ngedatha ebandayo:

CREATE TABLE 
... 
TTL date + INTERVAL 7 DAY TO VOLUME 'cold_volume', 
    date + INTERVAL 180 DAY DELETE

Amathuba ayingqayizivele ChofozaHouse

Cishe kukho konke ChofozaHouse Kukhona “okuvelele” okunjalo, kodwa kuxazululwa wukuhluka - into engekho kwezinye izingosi zolwazi. Isibonelo, nazi ezinye zezici eziyingqayizivele ChofozaHouse:

• Izinhlu. I ChofozaHouse ukusekelwa okuhle kakhulu kwama-array, kanye nekhono lokwenza izibalo eziyinkimbinkimbi kuwo.
• Ukuhlanganisa Izakhiwo Zedatha. Lesi ngesinye "sezici zokubulala" ChofozaHouse. Naphezu kweqiniso lokuthi abafana abavela ku-Yandex bathi asifuni ukuhlanganisa idatha, konke kuhlanganiswe ChofozaHouse, ngoba kuyashesha futhi kulula.
• Ukubuka Kwezinto ezibonakalayo. Ngokuhambisana nokuhlanganisa izakhiwo zedatha, ukubukwa okwenyama kukuvumela ukuthi wenze kube lula isikhathi sangempela ukuhlanganisa.
• I-ClickHouse SQL. Lesi isandiso solimi SQL ngezici ezithile ezengeziwe nezikhethekile ezitholakala kuphela ku ChofozaHouse. Ngaphambili, bekufana nokunwetshwa ngakolunye uhlangothi, futhi kubi ngakolunye. Manje cishe zonke ezimbi uma kuqhathaniswa I-SQL 92 siyisusile, manje sekuyisandiso nje.
• Lambda-izinkulumo. Ingabe asekhona kunoma iyiphi i-database?
• ML-sekela. Lokhu kutholakala kuma-database ahlukene, ezinye zingcono, ezinye zimbi kakhulu.
• umthombo ovulekile. Singanweba ChofozaHouse ndawonye. Manje ku ChofozaHouse ababambiqhaza abangaba ngu-500, futhi leli nani likhula njalo.

Imibuzo ekhohlisayo

В ChofozaHouse kunezindlela eziningi ezahlukene zokwenza into efanayo. Isibonelo, ungabuyisela inani lokugcina kusuka kuthebula ngezindlela ezintathu ezahlukene CPU (kukhona futhi okwesine, kodwa kuyinto exotic nakakhulu).

Eyokuqala ibonisa ukuthi kulula kangakanani ukwenza ngaphakathi ChofozaHouse imibuzo uma ufuna ukuhlola lokho isigaxa equkethwe ku-subquery. Lena into engiyiphuthele mina uqobo kwezinye izingosi zolwazi. Uma ngifuna ukuqhathanisa okuthile ne-subquery, khona-ke kwezinye izingosi zolwazi kuphela isikali esingaqhathaniswa naso, kodwa kumakholomu amaningana engidinga ukuwabhala. JOIN. I ChofozaHouse ungasebenzisa i-tuple:

SELECT *
  FROM cpu 
 WHERE (tags_id, created_at) IN 
    (SELECT tags_id, max(created_at)
        FROM cpu 
        GROUP BY tags_id)

Indlela yesibili yenza into efanayo kodwa isebenzisa umsebenzi we-aggregate argMax:

SELECT 
    argMax(usage_user), created_at),
    argMax(usage_system), created_at),
...
 FROM cpu 

В ChofozaHouse kukhona imisebenzi ehlanganisiwe eyishumi nambili, futhi uma usebenzisa izihlanganisi, khona-ke ngokwemithetho yama-combinatorics uzothola cishe inkulungwane yazo. I-ArgMax - omunye wemisebenzi ebala inani eliphezulu: isicelo sibuyisela inani sebenzisa_umsebenzisi, lapho inani eliphezulu lifinyelelwa khona kudalwe_ku:

SELECT now() as created_at,
       cpu.*
  FROM (SELECT DISTINCT tags_id from cpu) base 
  ASOF LEFT JOIN cpu USING (tags_id, created_at)

ASOF JOIN — imigqa “ye-gluing” enezikhathi ezihlukene. Lesi isici esiyingqayizivele sesizindalwazi esitholakala kuphela kuyo kdb+. Uma kukhona uchungechunge lwesikhathi olubili olunezikhathi ezihlukene, ASOF JOIN ikuvumela ukuthi uwasuse futhi uwahlanganise esicelweni esisodwa. Kuvelu ngayinye ochungechungeni lwesikhathi esisodwa, inani eliseduze kolunye liyatholakala, futhi abuyiselwa kulayini ofanayo:

Imisebenzi yokuhlaziya

Esilinganisweni I-SQL-2003 ungabhala kanje:

SELECT origin,
       timestamp,
       timestamp -LAG(timestamp, 1) OVER (PARTITION BY origin ORDER BY timestamp) AS duration,
       timestamp -MIN(timestamp) OVER (PARTITION BY origin ORDER BY timestamp) AS startseq_duration,
       ROW_NUMBER() OVER (PARTITION BY origin ORDER BY timestamp) AS sequence,
       COUNT() OVER (PARTITION BY origin ORDER BY timestamp) AS nb
  FROM mytable
ORDER BY origin, timestamp;

В ChofozaHouse Awukwazi ukwenza lokho - akusekeli izinga I-SQL-2003 futhi cishe ayisoze yakwenza. Kunalokho, ku ChofozaHouse Kuyisiko ukubhala kanje:

Ngathembisa ama-lambda - nawa!

Lena i-analogue yombuzo wokuhlaziya osezingeni I-SQL-2003: ubala umehluko phakathi kwalaba ababili isitembu sesikhathi, ubude besikhathi, inombolo ye-ordinal - yonke into esivame ukuyicabangela imisebenzi yokuhlaziya. IN ChofozaHouse Siwabala ngama-arrays: okokuqala sigoqa idatha kuhlelo, ngemva kwalokho senza konke esikufunayo kumalungu afanayo, bese siyayinweba futhi. Akulula kakhulu, kudinga uthando lokuhlela okusebenzayo okungenani, kodwa kuyavumelana nezimo kakhulu.

Imisebenzi ekhethekile

Ngaphezu kwalokho, ku ChofozaHouse imisebenzi eminingi ekhethekile. Isibonelo, unganquma kanjani ukuthi zingaki izikhathi ezenzeka ngesikhathi esisodwa? Umsebenzi wokuqapha ojwayelekile ukunquma umthwalo omkhulu ngesicelo esisodwa. IN ChofozaHouse Kunomsebenzi okhethekile wale njongo:

Ngokuvamile, i-ClickHouse inemisebenzi ekhethekile ngezinjongo eziningi:

• ukugijima Umehluko, ukugijimaButhelela, umakhelwane;
• i-sumMap(ukhiye, inani);
• i-timeSeriesGroupSum(uid, isitembu sesikhathi, inani);
• i-timeSeriesGroupRateSum(uid, isitembu sesikhathi, inani);
• i-skewPop, i-skewSamp, i-kurtPop, i-kurtSamp;
• NGOKUGCWALISA / NGOKUTHATHA;
• simpleLinearRegression, stochasticLinearRegression.

Lokhu akulona uhlu oluphelele lwemisebenzi, kukhona 500-600 isiyonke. Ukusikisela: yonke imisebenzi ngaphakathi ChofozaHouse isethebula lesistimu (akukona konke okubhaliwe, kodwa konke kuyathakazelisa):

select * from system.functions order by name

ChofozaHouse igcina ulwazi oluningi mayelana nayo, kuhlanganise log amatafula, query_log, ilogi yokulandelela, ilogi yemisebenzi enamabhulokhi edatha (ingxenye_yelogi), ilogi yemethrikhi, kanye nelogi yesistimu, evame ukuyibhala kudiski. Amamethrikhi elogi uchungechunge lwesikhathi в ChofozaHouse Empeleni ChofozaHouse: Isizindalwazi ngokwaso singadlala indima uchungechunge lwesikhathi database, ngaleyo ndlela "idla" ngokwayo.

Lokhu futhi kuyinto eyingqayizivele - njengoba siwenzela umsebenzi omuhle uchungechunge lwesikhathi, kungani singakwazi ukugcina konke esikudingayo ngaphakathi kwethu? Asidingi Prometheus, sigcina yonke into kithi. Ixhunyiwe UGrafana futhi siyaziqapha. Nokho, uma ChofozaHouse ukuwa, ngeke sibone ukuthi kungani, ngakho ngokuvamile abakwenzi lokho.

Iqoqo elikhulu noma amancane amaningi ChofozaHouse

Yini engcono - iqoqo elilodwa elikhulu noma amaClickHouse amaningi amancane? Indlela yendabuko yoku I-DWH iyiqoqo elikhulu lapho amasekhethi abelwe isicelo ngasinye. Size kumphathi wedatha - sinikeze umdwebo, futhi basinika owodwa:

В ChofozaHouse ungakwenza ngokuhlukile. Ungenza uhlelo ngalunye lube ngelakho ChofozaHouse:

Asisayidingi enkulu esabekayo I-DWH kanye nama-admins angakholeki. Singanikeza uhlelo ngalunye olwalo ChofozaHouse, futhi unjiniyela angakwenza ngokwakhe, kusukela ChofozaHouse kulula kakhulu ukuyifaka futhi ayidingi ukuphathwa okuyinkimbinkimbi:

Kodwa uma sinokuningi ChofozaHouse, futhi udinga ukuyifaka kaningi, bese ufuna ukwenza ngokuzenzakalelayo le nqubo. Kulokhu singasebenzisa, isibonelo, ukusebenzisa Kubernetes и clickhouse- opharetha. IN Kubernetes ClickHouse ungayibeka "ngokuchofoza": Ngingakwazi ukuchofoza inkinobho, ngisebenzise i-manifest futhi isizindalwazi sesilungile. Ngingakwazi ukudala umdwebo ngokushesha, ngiqale ukulayisha amamethrikhi lapho, futhi emizuzwini emi-5 senginayo ideshibhodi elungile UGrafana. Kulula kakhulu!

Yini ekugcineni?

Ngakho-ke, ChofozaHouse - Lokhu:

• Ngokushesha. Wonke umuntu uyakwazi lokhu.
• Simply. Ingxabano encane, kodwa ngikholelwa ukuthi kunzima ekuqeqesheni, kulula ukulwa. Uma uqonda ukuthi kanjani ChofozaHouse iyasebenza, khona-ke yonke into ilula kakhulu.
• Emhlabeni wonke. Ilungele izimo ezahlukahlukene: I-DWH, Uchungechunge Lwesikhathi, Isitoreji Sokungena. Kodwa akunjalo I-OLTP database, ngakho-ke ungazami ukufaka amafushane futhi ufunde lapho.
• Kuyathakazelisa. Mhlawumbe osebenza naye ChofozaHouse, wabhekana nezikhathi eziningi ezithakazelisayo ngomqondo omuhle noma omubi. Isibonelo, ukukhishwa okusha kwaphuma, konke kwayeka ukusebenza. Noma lapho uzabalaza ngomsebenzi izinsuku ezimbili, kodwa ngemva kokubuza umbuzo engxoxweni yeTelegram, umsebenzi waxazululwa ngemizuzu emibili. Noma njengasengqungqutheleni embikweni kaLesha Milovidov, isithombe-skrini esivela ChofozaHouse waphula ukusakaza Ukulayisha Okuphezulu++. Lolu hlobo lwento luyenzeka ngaso sonke isikhathi futhi lwenza izimpilo zethu zibe nzima. ChofozaHouse elikhanyayo futhi elithakazelisayo!

Ungabuka isethulo lapha.

Umhlangano okade ulindelwe wabathuthukisi bezinhlelo ezilayisha kakhulu ngo Ukulayisha Okuphezulu++ izoba ngoNovemba 9 no-10 eSkolkovo. Okokugcina, lena kuzoba ingqungquthela engaxhunyiwe ku-inthanethi (nakuba inazo zonke izinyathelo zokuphepha ezikhona), njengoba amandla e-HighLoad++ engakwazi ukupakishwa ku-inthanethi.

Engqungqutheleni, sithola futhi sikubonise amacala mayelana namakhono aphezulu obuchwepheshe: I-HighLoad++ yayiyiyo, iyindawo futhi izoba ukuphela kwendawo lapho ungafunda khona ezinsukwini ezimbili ukuthi i-Facebook, i-Yandex, i-VKontakte, i-Google ne-Amazon isebenza kanjani.

Njengoba sesibambe imihlangano yethu ngaphandle kokuphazamiseka kusukela ngo-2007, kulo nyaka sizohlangana okwe-14. Ngalesi sikhathi, ingqungquthela isikhule izikhathi ezingu-10; ngonyaka odlule, umcimbi obalulekile wezimboni uhlanganise abahlanganyeli abangu-3339, izikhulumi ezingu-165, imibiko kanye nemihlangano, kanye namathrekhi angu-16 agijima ngesikhathi esisodwa.
Ngonyaka odlule bekunamabhasi angu-20, amalitha angu-5280 etiye nekhofi, angu-1650 amalitha eziphuzo zezithelo namabhodlela amanzi angu-10200. Futhi amanye amakhilogremu angu-2640 16 okudla, amapuleti angu-000 25 nezinkomishi ezingu-000 100. Ngendlela, ngemali eqoqwe ephepheni eligaywe kabusha, satshala izithombo ze-oki eziyi-XNUMX :)

Ungawathenga amathikithi lapha, thola izindaba mayelana nengqungquthela - lapha, futhi ukhulume kuwo wonke amanethiwekhi omphakathi: yocingo, Facebook, Vkontakte и Twitter.

Source: www.habr.com