ClickHouse aggregation VS graphite—clickhouse aggregation

Enable ClickHouse aggregation

You should enable it in the config, it's disabled by default

[clickhouse]
# Use metrics aggregation on ClickHouse site
internal-aggregation = true
# maximum number of points per metric. Default is 4096 for ClickHouse older than 20.8
# https://github.com/ClickHouse/ClickHouse/commit/d7871f3976e4f066c6efb419db22725f476fd9fa
max-data-points = 4096

The only known frontend supporting passing maxDataPoints from requests is carbonapi. The used protocol should be carbonapi_v3_pb, see config->backendv2->backends->protocol.

But even without the mentioned adjustments, internal-aggregation improves the whole picture by implementing whisper-like archives, see below.

Compatible ClickHouse versions

The feature uses ClickHouse aggregation combinators -OrNull and -Resample. They are pretty old, but -OrNull and -OrDefault have had a bug until ClickHouse/ClickHouse#10741. This fix is included in the following ClickHouse versions:

• v20.5.2.7-stable and newer
• v20.4.5.36-stable
• v20.3.10.75-lts
• v20.1.13.105-stable

Schemes and changes overview

Classic whisper scheme

header:
xFilesFactor: [0, 1]
aggregation: {avg,sum,min,max,...}
retention: 1d:1m,1w:5m,1y:1h
data:
archive1: 1440 points
archive2: 2016 points
archive3: 8760 points

• Each archive filled up simultaneously
• Aggregation on the fly during writing
• xFilesFactor controls if points from archive(N) should be aggregated into archive(N+1)
• Points are selected only from one archive, with the most precision:
  • from <= now-1d -> archive1
  • from <= now-7d -> archive2
  • else -> archive3

Historical graphite-clickhouse way

Storing: GraphiteMergeTree table engine in ClickHouse (CH)

Completely another principle of data storing.

• Retention scheme looks slightly different:
  retention: 0:60,1d:5m,1w:1h,1y:1d
• Retention and aggregation policies are applied only when point becomes older than X (1d,1w,1y)
• There is no such thing as archive, each point is stored only once
• No xFilesFactor entity: each point will be aggregated

Fetching data: before September 2019

SELECT Path, Time, Value, Timestamp
FROM data WHERE ...

• Select all points
• Aggregate them in graphite-clickhouse to the proper archive step
• Pass further to graphite-web/carbonapi

Problems:

• A big overhead for Path (the heaviest part)
• Overkill by network traffic, especially when CH cluster is used
  • The CH node query-initiator must collect the whole data (in memory or on the disk), and only then the date will be passed further

Fetching data: after September 2019 (#61, #62, #65)

SELECT Path,
  groupArray(Time),
  groupArray(Value),
  groupArray(Timestamp)
FROM data WHERE ... GROUP BY Path

• Network consumption was decreased up to 6 times
• But still selects all points and aggregates in graphite-clickhouse

Fetching data: September 2020 (#88)

SELECT Path,
  arrayFilter(x->isNotNull(x),
    anyOrNullResample($from, $until, $step)
      (toUInt32(intDiv(Time, $step)*$step), Time)
  ),
  arrayFilter(x->isNotNull(x),
    ${func}OrNullResample($from, $until, $step)
      (Value, Time)
  )
FROM data WHERE ... GROUP BY Path

• This solution implements archive analog on CH site
• The most of data is aggregated on CH shards and doesn't leave them, so query-initiator consumes much less memory
• Together with carbonapi the /render?maxDatePoints=x parameter as well processed on CH side

Fetching data: concepts' difference

For small requests, the difference is not so big, but for the heavy one the amount of data was decreased up to 100 times:

target=${986_metrics_60s_precision}
from=-7d
maxDataPoints=100

method	rows	points	data (binary)	time (s)
row/point	9887027	9887027	556378258 (530M)	16.486
groupArray	986	9887027	158180388 (150M)	35.498
-Resample	986	98553	1421418 (1M)	13.181

note: it's localhost, so network data transfer overhead is not taken into account.

The maxDataPoints processing

The classical pipeline:

• Fetch the data in graphite-web/carbonapi
• Apply all functions from target
• Compare the result with maxDataPoints URI parameter and adjust them

Current:

• Get data, aggregated with the proper function directly from CH
• Apply all functions to pre-aggregated data