ClickHouse + Graphite: faʻafefea ona faʻaitiitia le faʻaogaina o avanoa avanoa

Talofa, habr.

Afai e faʻaaogaina e se tasi le faiga graphite-web ma fa'afeiloa'i i se fa'afitauli fa'atinoga o le teuina musumusu (IO, faʻaogaina avanoa vaʻaia), ona maua ai lea o le avanoa na lafoina ai ClickHouse e suitulaga i le tasi. O lenei faʻamatalaga o loʻo faʻaalia ai o se faʻatinoga lona tolu o loʻo faʻaaogaina e avea o se daemon mauaina metrics, mo se faʻataʻitaʻiga tusitala carbon poʻo alu-karona.

ClickHouse foia lelei faʻafitauli faʻamatalaina. Mo se faʻataʻitaʻiga, a maeʻa ona tuʻuina atu le 2TiB o faʻamatalaga mai le musumusu, latou te fetaui i le 300GiB. O le a ou le mafaufau i le faʻatusatusaga i auiliiliga; e tele tala i lenei autu. E le gata i lea, seʻia oʻo mai talu ai nei, e leʻi atoatoa mea uma i le matou ClickHouse teuina.

Fa'afitauli ile fa'aogaina ole avanoa

I le tepa muamua, e tatau ona lelei mea uma. Mulimuli fa'amaumauga, fai se fa'aoga mo le faiga e teu ai fua (fa'aopoopo retention), ona fai lea o se laulau e tusa ai ma le fautuaga a le pito i tua ua filifilia mo graphite-web: carbon-clickhouse+graphite-clickhouse poʻo falekalafi, e fa'atatau i le fa'aputuga e fa'aaogaina. Ma... ua pa le pomu taimi.

Ina ia malamalama po o le fea, e tatau ona e iloa pe faʻafefea ona faʻapipiʻi mea faʻapipiʻi ma le isi ala o le olaga o faʻamaumauga i laulau o afi o le * aigaMergeTree ClickHouse (siata na maua mai презентации Alexey Zatelepin):

• Faaofiina блок fa'amaumauga. I la matou tulaga, o metrics na taunuu mai.
  
• O ia poloka taitasi e fa'avasega e tusa ai ma le ki a'o le'i tusia i le tisiki. ORDER BYfa'amaonia pe a fai le laulau.
• A uma ona fa'avasega, кусок (part) ua tusia fa'amaumauga i le tisiki.
  
• E mataʻituina e le 'auʻaunaga i tua ina ia le tele ni vaega faapena, ma faʻalauiloa le talaaga слияния (merge, sosoo ai ma le tuufaatasia).
  
  
• E tu'u le ta'avale a le server e tu'ufa'atasia na'o ia pe a taofi fa'amaumauga o lo'o tafe malosi i totonu партицию (partition), ae e mafai ona e amataina le faagasologa ma le lima i le poloaiga OPTIMIZE.
• Afai e naʻo le tasi le vaega o totoe i le vaeluaga, o le a le mafai ona e faʻaogaina le tuʻufaʻatasia e faʻaaoga ai le masani masani; e tatau ona e faʻaogaina OPTIMIZE ... FINAL

O lea la, e taunuu mai fua muamua. Ma latou ave sina avanoa. O mea e tutupu mulimuli ane e mafai ona fesuisuiaʻi e faʻatatau i le tele o mea:

• O le ki vaeluaga e mafai ona la'ititi tele (se aso) po'o tele tele (nii masina).
• O le fa'amautu fa'amauina e ono fetaui ma ni fa'aputuga fa'amaumauga taua i totonu o le vaeluaga o lo'o galue (lea o lo'o fa'amauina ai fua), pe leai.
• Afai e tele faʻamatalaga, o uluaʻi pusi, e mafua mai i le tuʻufaʻatasia o talaaga atonu ua tele (afai e te filifilia se ki vaeluaga e le sili ona lelei), o le a le tuʻufaʻatasia i latou lava ma ni pusi laiti fou.

Ma e faai'u lava i taimi uma. Ole avanoa o loʻo nofoia e metrics ile ClickHouse e faʻateleina pe afai:

• aua le talosaga OPTIMIZE ... FINAL lima pe
• aua le fa'aofiina fa'amatalaga i vaega uma i luga o se fa'avae faifaipea, ina ia vave po'o se taimi mulimuli ane o le a amata ai se fa'atasiga i tua

O le auala lona lua e foliga mai e sili ona faigofie e faʻatino ma, o le mea lea, e le saʻo ma sa faʻataʻitaʻiina muamua.
Na ou tusia se tusitusiga python faigofie lava na auina atu fua faʻataʻitaʻi mo aso uma mo le 4 tausaga talu ai ma tamoe cron i itula uma.
Talu ai o le faʻatinoga atoa o le ClickHouse DBMS e faʻavae i luga o le mea moni o lenei faiga o le a vave pe mulimuli ane faia galuega uma i tua, ae e le o iloa poʻo afea, sa le mafai ona ou faʻatali mo le taimi e amata ai ona tuʻufaʻatasia le tele o vaega tuai. laiti fou. Na manino mai e tatau ona matou suʻeina se auala e faʻatometi ai faʻatonuga faʻamalosia.

Fa'amatalaga ile ClickHouse system tables

Sei o tatou tilotilo i le fausaga o le laulau faiga.vaega. O faʻamatalaga auiliili lea e uiga i vaega taʻitasi o laulau uma i luga o le server ClickHouse. E aofia ai, faatasi ai ma isi mea, koluma nei:

• db igoa (database);
• igoa laulau (table);
• igoa ma le vaeluaga ID (partition & partition_id);
• ina ua faia le vaega (modification_time);
• aso laʻititi ma le maualuga i totonu o se fasi pepa (vaeluaga e faia i le aso) (min_date & max_date);

E iai fo'i se laulau system.graphite_retentions, fa'atasi ai ma fanua mata'ina nei:

• db igoa (Tables.database);
• igoa laulau (Tables.table);
• metric tausaga pe a tatau ona faʻaoga le isi faʻaopoopoga (age);

O lea:

1. E iai la matou laulau o pusi ma se laulau o tulafono fa'aopoopo.
2. Matou te tu'ufa'atasia la latou va'aiga ma maua uma laulau *GraphiteMergeTree.
3. O loʻo matou suʻeina uma vaega o loʻo i ai:
   • sili atu ma le tasi fasi
   • pe ua oo mai le taimi e faʻaaoga ai le isi tulafono faʻaopoopo, ma modification_time matua nai lo lenei taimi.

Реализация

O lenei talosaga

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

toe fa'afo'i mai vaega ta'itasi *GraphiteMergeTree laulau o lo'o tu'ufa'atasia e tatau ona fa'asa'oloto ai avanoa tisiki. Pau lava le mea o lo'o totoe o se mea itiiti lava: alu i latou uma ma se talosaga OPTIMIZE ... FINAL. O le faʻatinoga mulimuli e amanaʻia ai foi le mea moni e leai se manaʻoga e paʻi i vaega ma faʻamaumauga malosi.

O le mea tonu lava lea e fai e le poloketi graphite-ch-optimizer. Na faʻataʻitaʻiina e le au faigaluega muamua mai Yandex.Market i le gaosiga, o le taunuuga o le galuega e mafai ona vaʻaia i lalo.

Afai e te faʻatautaia le polokalame i luga o se 'auʻaunaga ma ClickHouse, o le a amata ona galue i le daemon mode. E tasi i le itula o le a faia ai se talosaga, siaki pe ua aliali mai ni vaega fou e sili atu nai lo le tolu aso e mafai ona faʻaleleia.

O a matou fuafuaga vave o le tuʻuina atu o pusa aitalafu, ma afai e mafai, faʻapea foi le rpm.

Nai lo o se faaiuga

I le 9+ masina talu ai sa ou i totonu o laʻu kamupani innogames faʻaalu le tele o le taimi e taʻalo ai i le fetaulaiga o ClickHouse ma graphite-web. O se aafiaga lelei, lea na mafua ai se suiga vave mai le musumusu i le ClickHouse e avea o se metrics storage. Ou te faʻamoemoe o lenei tusiga o se mea o le amataga o se faasologa e uiga i faʻaleleia atili na matou faia i vaega eseese o lenei faaputuga, ma mea o le a faia i le lumanaʻi.

Na fa'aalu le tele o lita o pia ma aso fa'atonu i le atina'eina o le talosaga, fa'atasi ai ma v0 tiapolo, lea ou te fia faailoa atu ai lou agaga faafetai ia te ia. Ma faʻapea foʻi mo le toe iloiloina o lenei tusiga.

Itulau galuega ile github

puna: www.habr.com