ClickHouse + Graphite: indlela yokunciphisa kakhulu ukusetyenziswa kwendawo yedisk

Molweni, habr.

Ukuba umntu uxhaphaza inkqubo i-graphite-web kwaye wadibana nomba wokusebenza kogcino sebeza (IO, indawo yedisk edliwe), ke ithuba lokuba iClickHouse iphoswe njengendawo yokutshintsha kufuneka ithambekele kwenye. Le nkcazo ithetha ukuba ukuphunyezwa komntu wesithathu sele kusetyenziswa njengedaemon efumana i-metrics, umzekelo. carbonwriter okanye hamba-carbon.

ClickHouse isombulula iingxaki ezichazwe kakuhle. Ngokomzekelo, emva kokudlulisa i-2TiB yedatha kwi-whisper, ingena kwi-300GiB. Andiyi kuhlala kuthelekiso ngokweenkcukacha; kukho amanqaku amaninzi kwesi sihloko. Ukongeza, kude kube mva nje, ayizizo zonke izinto ezazigqibelele ngokugcinwa kwethu kweClickHouse.

Iingxaki ngendawo esetyenzisiweyo

Ekuqaleni, yonke into kufuneka isebenze kakuhle. Ukulandela amaxwebhu, yenza uqwalaselo lwesikimu sogcino lweemetrics (ukuqhubela phambili retention), emva koko yenza itafile ngokwengcebiso ye-backend ekhethiweyo ye-graphite-web: i-carbon-clickhouse+igraphite-clickhouse okanye igraphouse, kuxhomekeke ekubeni yeyiphi istaki esisetyenzisiweyo. Kwaye ... ixesha ibhombu iyahamba.

Ukuze uqonde ukuba yeyiphi, kufuneka wazi ukuba ufaka njani umsebenzi kunye nendlela yobomi eyongezelelweyo yedatha kwiitafile zeenjini zosapho *MergeTree ClickHouse (iitshati ezithatyathwe kwi iintetho Alexey Zatelepin):

• Ifakiwe блок idatha. Kwimeko yethu, yayiyimilinganiselo efikileyo.
  
• Ibhloko nganye enjalo ihlelwa ngokwesitshixo phambi kokuba ibhalwe kwidisk. ORDER BYicacisiwe xa kuyilwa itheyibhile.
• Emva kokuhlelwa, кусок (part) idatha ibhalwe kwidisk.
  
• Umncedisi ubeka iliso ngasemva ukuze kungabikho maninzi amaqhekeza anjalo, kwaye iqalise ngasemva слияния (merge, ukudibanisa emva koku).
  
  
• Umncedisi uyayeka ukusebenza udibaniso lodwa nje ukuba idatha iyeka ngokusebenzayo ukungena kwi партицию (partition), kodwa ungaqala inkqubo ngesandla ngomyalelo OPTIMIZE.
• Ukuba kukho iqhekeza elinye kuphela eliseleyo kwisahlulelo, ngoko awuyi kukwazi ukuqhuba ukudibanisa usebenzisa umyalelo oqhelekileyo omele uwusebenzise OPTIMIZE ... FINAL

Ke, iimethrikhi zokuqala zifika. Kwaye bathatha indawo ethile. Iziganeko ezilandelayo zinokwahluka noko ngokuxhomekeke kwizinto ezininzi:

• Iqhosha lokwahlula linokuba lincinci kakhulu (ngosuku) okanye libe likhulu kakhulu (iinyanga ezininzi).
• Ubumbeko logcino lusenokungena kwimithamo emininzi ebalulekileyo yodityaniso lwedatha ngaphakathi kwesahlulelo esisebenzayo (apho iimetrics zirekhodwa), okanye mhlawumbi akunjalo.
• Ukuba kukho idatha eninzi, ngoko ke iinqununu zokuqala, ezithi ngenxa yokudibana ngasemva zisenokuba zikhulu (ukuba ukhetha isitshixo sokwahlula esingasisona silungileyo), azizuzidibanisa neziqendwana ezincinci ezintsha.

Kwaye ihlala iphela ngokufanayo. Indawo ehlala iimetrics kwiClickHouse yonyuka kuphela ukuba:

• musa ukufaka isicelo OPTIMIZE ... FINAL ngesandla okanye
• musa ukufaka idatha kuzo zonke izahlulelo ngokuqhubekayo, ukuze kungekudala okanye kamva udibaniso olungasemva luqale

Indlela yesibini ibonakala ilula kakhulu ukuyisebenzisa kwaye, ngoko ke, ayichanekanga kwaye yazanywa kuqala.
Ndibhale iskripthi se-python esilula esithumele iimethrikhi ze-dummy yonke imihla kwiminyaka eyi-4 edlulileyo kwaye iqhube i-cron ngeyure nganye.
Ukusukela ekubeni yonke imisebenzi yeClickHouse DBMS isekwe kwinto yokuba le nkqubo kungekudala okanye kamva yenza wonke umsebenzi ongasemva, kodwa akwaziwa ukuba nini, andikwazanga ukulinda ixesha apho amaqhekeza amakhulu amadala aqala ukudibanisa kunye. amatsha amancinci. Kuye kwacaca ukuba kufuneka sijonge indlela yokwenza usetyenziso olunyanzelweyo.

Ulwazi kwiitafile zeClickHouse inkqubo

Makhe sijonge kwisakhiwo setafile inkqubo.amacandelo. Olu lwazi olubanzi malunga neqhekeza ngalinye lazo zonke iitafile kumncedisi we ClickHouse. Iqulethe, phakathi kwezinye izinto, le miqolo ilandelayo:

• igama le-db (database);
• igama letafile (table);
• igama lesahlulelo kunye nesazisi (partition & partition_id);
• xa iqhekeza ladalwa (modification_time);
• Ubuncinci kunye nowona mhla kwiqhekeza (ukwahlulwa kwenziwa ngemini) (min_date & max_date);

Kukwakho netafile inkqubo.graphite_retentions, enemihlaba elandelayo enomdla:

• igama le-db (Tables.database);
• igama letafile (Tables.table);
• Ubudala bemetric xa udibaniso olulandelayo kufuneka lusetyenziswe (age);

Ngoko:

1. Sinetafile yee-chunks kunye netheyibhile yemithetho yokudibanisa.
2. Sidibanisa ukuhlangana kwabo kwaye sifumane zonke iitafile *GraphiteMergeTree.
3. Sijonge zonke izahlulo apho:
   • ngaphezu kwesinye isiqwenga
   • okanye ixesha lifikile lokusebenzisa umgaqo olandelayo wokudibanisa, kunye modification_time mdala kunalomzuzu.

Ukuphunyezwa

Esi sicelo

SELECT
    concat(p.database, '.', p.table) AS table,
    p.partition_id AS partition_id,
    p.partition AS partition,
    -- Самое "старое" правило, которое может быть применено для
    -- партиции, но не в будущем, см (*)
    max(g.age) AS age,
    -- Количество кусков в партиции
    countDistinct(p.name) AS parts,
    -- За самую старшую метрику в партиции принимается 00:00:00 следующего дня
    toDateTime(max(p.max_date + 1)) AS max_time,
    -- Когда партиция должна быть оптимизированна
    max_time + age AS rollup_time,
    -- Когда самый старый кусок в партиции был обновлён
    min(p.modification_time) AS modified_at
FROM system.parts AS p
INNER JOIN
(
    -- Все правила для всех таблиц *GraphiteMergeTree
    SELECT
        Tables.database AS database,
        Tables.table AS table,
        age
    FROM system.graphite_retentions
    ARRAY JOIN Tables
    GROUP BY
        database,
        table,
        age
) AS g ON
    (p.table = g.table)
    AND (p.database = g.database)
WHERE
    -- Только активные куски
    p.active
    -- (*) И только строки, где правила аггрегации уже должны быть применены
    AND ((toDateTime(p.max_date + 1) + g.age) < now())
GROUP BY
    table,
    partition
HAVING
    -- Только партиции, которые младше момента оптимизации
    (modified_at < rollup_time)
    -- Или с несколькими кусками
    OR (parts > 1)
ORDER BY
    table ASC,
    partition ASC,
    age ASC

ibuyisela ulwahlulo lwetafile nganye *GraphiteMergeTree enodibaniso olunokukhulula isithuba sedisk. Ekuphela kwento esele yenziwe kukuhamba kubo bonke ngesicelo OPTIMIZE ... FINAL. Ukuphunyezwa kokugqibela kwakhona kuthathela ingqalelo into yokuba akukho mfuneko yokubamba izahlulelo ngokurekhoda okusebenzayo.

Yile nto kanye eyenziwa yiprojekthi igraphite-ch-optimizer. Oogxa bangaphambili baseYandex.Market bazama kwimveliso, umphumo womsebenzi unokubonwa ngezantsi.

Ukuba uqhuba inkqubo kumncedisi nge ClickHouse, iya kuqalisa ukusebenza kwimo yedaemon. Kanye ngeyure isicelo siya kwenziwa, kujongwa ukuba ingaba izahlulelo ezitsha ezindala kuneentsuku ezintathu ziye zavela na ezinokwandiswa.

Izicwangciso zethu zangoku kukubonelela ubuncinane iipakethe zedeb, kwaye ukuba kuyenzeka kwakhona rpm.

Endaweni yesiphelo

Kwiinyanga ezidlulileyo ze-9+ bendingaphakathi kwinkampani yam Imidlalo yangaphakathi uchithe ixesha elininzi encwina ekudibaneni kweClickHouse kunye negraphite-web. Yayingamava alungileyo, akhokelela kutshintsho olukhawulezayo ukusuka kwi-whisper ukuya kwiClickHouse njengendawo yokugcina iimetriki. Ndiyathemba ukuba eli nqaku liyinto yokuqala kuthotho malunga nokuba loluphi uphuculo esilwenzile kwiindawo ezahlukeneyo zesitaki, kwaye kuya kwenziwa ntoni kwixesha elizayo.

Iilitha ezininzi zebhiya kunye neentsuku zolawulo zichithwe ekuphuhliseni isicelo, kunye v0 usathana, endifuna ukubonisa umbulelo wam kuye. Kwaye kwakhona ukuphonononga eli nqaku.

Iphepha leprojekthi kwi-github

umthombo: www.habr.com