🥇ClickHouse + Graphite: yuav ua li cas txo qhov chaw siv disk

Nyob zoo, habr.

Yog hais tias ib tug neeg exploits lub system graphite-web thiab ntsib teeb meem kev ua haujlwm cia ntxhi (IO, disk qhov chaw noj), ces lub caij nyoog uas ClickHouse raug cam khwb cia li hloov yuav tsum zoo ib yam. Cov lus no txhais tau hais tias qhov kev siv thib peb twb tau siv los ua tus daemon txais kev ntsuas, piv txwv li carbonwriter los yog mus-carbon.

ClickHouse daws cov teeb meem piav qhia tau zoo. Piv txwv li, tom qab hloov 2TiB ntawm cov ntaub ntawv los ntawm ntxhi, lawv haum rau 300GiB. Kuv yuav tsis nyob ntawm qhov kev sib piv hauv kev nthuav dav; muaj ntau cov lus ntawm lub ncauj lus no. Tsis tas li ntawd, txog thaum tsis ntev los no, tsis yog txhua yam zoo tag nrho nrog peb ClickHouse cia.

Teeb meem nrog qhov chaw siv

Thaum xub thawj siab ib muag, txhua yam yuav tsum ua haujlwm zoo. Ua raws cov ntaub ntawv, tsim config rau metrics cia scheme (ntxiv retention), ces tsim ib lub rooj raws li qhov kev pom zoo ntawm cov xaiv backend rau graphite-web: carbon-clickhouse+graphite-clickhouse los yog nrag, nyob ntawm seb pawg twg siv. Thiab ... lub sij hawm foob pob tawm mus.

Yuav kom nkag siab qhov twg, koj yuav tsum paub yuav ua li cas inserts ua hauj lwm thiab lub neej ntxiv txoj kev ntawm cov ntaub ntawv nyob rau hauv cov ntxhuav ntawm cav ntawm * tsev neegMergeTree ClickHouse (cov duab kos los ntawm kev nthuav qhia Alexey Zatelpin):

Ntxig блок cov ntaub ntawv. Hauv peb cov ntaub ntawv, nws yog cov metrics uas tuaj txog.
Txhua qhov thaiv no raug txheeb raws li tus yuam sij ua ntej sau rau disk. ORDER BYteev thaum tsim lub rooj.
Tom qab sorting, кусок (part) cov ntaub ntawv yog sau rau disk.
Cov neeg rau zaub mov saib xyuas hauv keeb kwm yav dhau kom tsis muaj ntau daim ntawv, thiab tso tawm keeb kwm yav dhau los слияния (merge, ntawm no tom qab sib koom ua ke).
Tus neeg rau zaub mov nres khiav sib koom ua ke ntawm nws tus kheej sai li sai tau thaum cov ntaub ntawv nres nquag ntws mus rau hauv партицию (partition), tab sis koj tuaj yeem pib txheej txheem manually nrog cov lus txib OPTIMIZE.
Yog tias tsuas muaj ib qho seem hauv qhov muab faib, ces koj yuav tsis tuaj yeem khiav qhov kev sib koom ua ke siv cov lus txib ib txwm; koj yuav tsum siv OPTIMIZE ... FINAL

Yog li, thawj metrics tuaj txog. Thiab lawv siv qee qhov chaw. Cov xwm txheej tom ntej yuav txawv me ntsis nyob ntawm ntau yam:

Tus yuam sij muab faib tuaj yeem yog me me (ib hnub) lossis loj heev (ntau lub hlis).
Cov kev tuav pov hwm yuav haum rau ntau qhov tseem ceeb ntawm cov ntaub ntawv sib sau ua ke hauv qhov kev faib ua haujlwm (qhov ntsuas ntsuas tau kaw), lossis tej zaum tsis yog.
Yog hais tias muaj ntau cov ntaub ntawv, ces qhov ntxov tshaj plaws chunks, uas yog vim lub keeb kwm yav dhau merging tej zaum yuav loj heev (yog hais tias koj xaiv ib tug uas tsis yog-zoo partitioning yuam sij), yuav tsis ua ke lawv tus kheej nrog tshiab chunks tshiab.

Thiab nws yeej ib txwm xaus tib yam. Qhov chaw nyob los ntawm kev ntsuas hauv ClickHouse tsuas yog nce yog tias:

tsis thov OPTIMIZE ... FINAL manually los yog
tsis txhob ntxig cov ntaub ntawv rau hauv txhua qhov kev faib ua ntu zus, kom sai lossis tom qab kev sib koom ua ke tom qab yuav pib

Txoj kev thib ob zoo li yog qhov yooj yim tshaj plaws los siv thiab, yog li ntawd, nws tsis raug thiab raug sim ua ntej.
Kuv tau sau ib tsab ntawv python yooj yim uas xa cov kev ntsuas dummy rau txhua hnub rau 4 xyoo dhau los thiab khiav cron txhua teev.
Txij li tag nrho cov haujlwm ntawm ClickHouse DBMS yog raws li qhov tseeb tias qhov system no yuav sai dua lossis tom qab ua tag nrho cov haujlwm keeb kwm yav dhau los, tab sis nws tsis paub tias thaum twg, kuv tsis tuaj yeem tos lub sijhawm thaum cov ntawv loj loj qub tau pib ua ke nrog. tshiab me me. Nws tau pom tseeb tias peb yuav tsum nrhiav txoj hauv kev los ua kom muaj kev ua kom zoo dua qub.

Cov ntaub ntawv hauv ClickHouse system tables

Cia peb saib ntawm lub rooj qauv system.parts. Qhov no yog cov ntaub ntawv qhia txog txhua daim ntawm txhua lub rooj ntawm ClickHouse server. Muaj, ntawm lwm yam, cov kab hauv qab no:

db npe (database);
lub npe rooj (table);
muab faib lub npe thiab ID (partition & partition_id);
thaum cov khoom raug tsim (modification_time);
yam tsawg kawg thiab siab kawg hnub nyob rau hauv ib daim (partitioning yog ua los ntawm hnub) (min_date & max_date);

Kuj muaj ib lub rooj system.graphite_retentions, nrog cov nram qab no nthuav teb:

db npe (Tables.database);
lub npe rooj (Tables.table);
metric hnub nyoog thaum qhov kev sib sau tom ntej yuav tsum tau siv (age);

Yog li ntawd,

Peb muaj ib lub rooj ntawm chunks thiab ib lub rooj ntawm aggregation txoj cai.
Peb muab lawv cov kev sib tshuam thiab tau txais tag nrho cov rooj * GraphiteMergeTree.
Peb tab tom nrhiav rau tag nrho cov partitions uas:
- ntau tshaj ib thooj
- los yog lub sijhawm tau los siv txoj cai sib sau ua ke tom ntej, thiab modification_time laus dua lub caij no.

Kev siv

Qhov kev thov no

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

xa rov qab txhua qhov ntawm * GraphiteMergeTree cov lus muab faib uas nws qhov kev sib koom ua ke yuav tsum pub dawb qhov chaw disk. Qhov tsuas tshuav ua yog mus hla lawv tag nrho nrog kev thov OPTIMIZE ... FINAL. Qhov kawg kev siv kuj yuav siv sij hawm mus rau hauv tus account lub fact tias tsis muaj yuav tsum tau kov partitions nrog active kaw.

Qhov no yog qhov project ua graphite-ch-optimizer. Yav dhau los cov npoj yaig los ntawm Yandex.Market sim nws hauv kev tsim khoom, cov txiaj ntsig ntawm kev ua haujlwm tuaj yeem pom hauv qab no.

Yog tias koj khiav qhov kev pab cuam ntawm lub server nrog ClickHouse, nws tsuas yog pib ua haujlwm hauv daemon hom. Ib zaug ib teev ib daim ntawv thov yuav raug muab tua, xyuas seb cov partitions tshiab tshaj peb hnub tau tshwm sim uas tuaj yeem ua kom zoo dua.

Peb cov phiaj xwm tam sim no yog muab tsawg kawg deb pob, thiab yog tias ua tau thiab rpm.

Es tsis txhob ib tug xaus

Tshaj li 9+ lub hlis dhau los kuv tau nyob hauv kuv lub tuam txhab innogames siv sijhawm ntau tinkering ntawm kev sib tshuam ntawm ClickHouse thiab graphite-web. Nws yog ib qho kev paub zoo, uas ua rau muaj kev hloov pauv sai ntawm ntxhi rau ClickHouse raws li kev ntsuas ntsuas. Kuv vam tias tsab xov xwm no yog ib yam ntawm qhov pib ntawm cov koob hais txog qhov kev txhim kho peb tau ua rau ntau qhov chaw ntawm pawg no, thiab yuav ua li cas rau yav tom ntej.

Ob peb litres ntawm npias thiab admin hnub tau siv los tsim qhov kev thov, ua ke nrog v0 dab, uas kuv xav qhia kuv txoj kev ris txiaj rau nws. Thiab kuj rau kev tshuaj xyuas cov kab lus no.

Project page ntawm github

Tau qhov twg los: www.hab.com

ClickHouse + Graphite: yuav ua li cas txo qhov chaw siv disk

Teeb meem nrog qhov chaw siv

Cov ntaub ntawv hauv ClickHouse system tables

Kev siv

Es tsis txhob ib tug xaus

Ntxiv ib saib Отменить ответ