Clickhouse as time series database

[sciffer]

Hi,

I would like to test clickhouse as a time series database, from reading about it I'm not certain about how to optimize it is to handle such cases.

.I'm looking into storing and quering metrics 2.0, which means each metric has a name, labels(which are actual dimentions for that specific metric) and series of values

 Ofcourse keeping the metric name and the same labels all over again per value is very unefficient and will waist a lot of disk space and it should be avoided.

Is there any best practice or recommended schema for such storage?

Again, the queries will have to include time range + metric name while the labels are optional filters on top of it - those are the common queries.

Inserts can be done using different methods depends on what works well for clickhouse(the easiest will be to insert single metric at a time, another option will require client logic for storing block of values per metric in memory and flushing them once every X samples).

The expected ratio and the amount of metrics per second that  needs to be ingested can get to 10's of millions per minute while reads will be much less frequent as they will be used for manual investigations/trends analysis.

I'd appreciate any recommendation or direction about schema/experience with such implementation

Thanks,

sciffer

[man...@gmail.com]

Hi.

About labels.

Lets look at example from http://metrics20.org/:

{
    host: dfs1
    what: diskspace
    mountpoint: srv/node/dfs10
    unit: B
    type: used
    metric_type: gauge
}
meta: {
    agent: diamond,
    processed_by: statsd2
}

Best possible case is when you know set of labels in advance, or rarely add new labels.
Then it is natural to create table where each label is separate column of appropriate type.
No problem if columns will be sparse. No problem if you will have hundreds of columns.

Little worse if you don't know set of labels in advance, but users will configure possible set of labels beforehand, so labels will not be completely dynamic.
Then you create tables of different structure for each user.

Storing dynamic (semi-structured) data is also possible but it is much less optimal.


About metric names.

Counterintuitively, storing metric names in String column (repeated in many many rows) is affordable.
If you have primary key with metric name (in other cases it is also true), then it will be very well compressed.
For example, we store metric names for Graphite (something like "one_min.cenzored_abc_yandex_net.audience-cenzored-cenzored.cenzored-t-36-example36-1_yandex_ru.ConnectionsInUse") in String column. Average length of string is 71 bytes, and (if I have not missed something) it compressed to 0.29 bytes per value. For comparison, Time and Value columns are much larger: https://gist.github.com/alexey-milovidov/5437d0513fafdbb638d6797390c4a55f

Of course it is not optimal for CPU.
Better solution is to hash metric names to 64 bits and store hashes in UInt64 column.
Also you need to store original metric names in another database (MySQL or Redis, for example).

Worth to note, that after hashing, you lose locality by metric name: similar metric names are mapped to distant hashes.
It is important for Graphite, where metric names have concatenated 'labels' inside it, and when you need to query many such metrics.
But if you don't need to query thousands of similar metric names simultaneously, hashes are better.


About data insertion.

For ClickHouse it's required to buffer data in batches. One second batches are Ok.
Performance of 10 million rows per minute is likely to be achieved even on single server.
On our Graphite installation we tested for more than 60 million rows per second per server, but it depends on size of rows,
 and also it depends on how fast your program could write that data.

[Vladimir Tretyakov]

Hello. First of all let me say you thx for such great product, it looks very promissing.

We are going to use CH as timeseries DB and we have worst case as usual -> we don't have predefined list of tags or metrics.

To avoid problems with performance (our current rate is 120000 datapoints per second (each with 3-10 or more tags), and will be in 5-10 times more) we reject schema like:

CREATE TABLE metrics

(

  accountId Int,

  timestamp DateTime,

  measures Nested (

    name String,

    value Float64

  ),

  tags Nested (

    name String,

    value Float64

  )

) ENGINE = MergeTree(timestamp, (accountId, timestamp), 8192)

Our current idea is use wide table with 900 (or 500) columns for measures and 100 (or 50) columns for tags + mapping in dictionary with information

what exactly we store in measures and tags columns. We will have something like:

Measures Table columns:

ShardKey (accountId or better add accountId->shardKeys table one to many, to have ability scale in the future)

Timestamp

M1

M2

...

M900

T1

T2

...

T100

Metainfo table columns:

ShardKey

Name

ColumnName

As you see depending on "ShardKey" we may have different metrics in Mx and Tx columns. For example, for ShardKey=1 M1 may contain count of apples, but for ShardKey=2 M1 may contain count of oranges.

We have couple open questions here, maybe somebody will able make them more clear:

1. Will this approach work in general with CH? Looks like this is good scenario, wide table with sparse data, please confirm.

2. I see enough flexibility (for us at least), does somebody see some underwater stones in this approach?

3. I see/expect some problems with MetaInfo table, will be tricky keep it is actual state, mapping must be unique, but I didn't find some way how to do this in CH. Is there maybe some function like insertOnlyIfNotExist(pk, row) which will return existing row if already exist? Will be very helpful. Or maybe we must use external source here for MetaInfo table? Can somebody suggest something?

4. Very ofter (almost always) we have 2-10 datapoint with the same set of tags. There is 2 ways to insert them:

a: like separate rows, so we will produce 2-10 very sparse rows.

b: group by tags on our side and store as 1 row with 2-10 filled columns.

(we will cache and insert in batches in any case (on our side or use Buffer on CH side, we didn't decide yet), Q is should we group before, will it give good speedup?)

Thx again, looking forward to try CH.

[Aliaksandr Valialkin]

Try the following scheme:

Store all the tags for all the timeseries in the following table:

CREATE TABLE tsdb_id (

    id UInt64,  -- unique id of the time series

    tags String -- sorted 'key=value' tags delimited by spaces that uniquely identify the time series

                       -- For instance, 'key1=value1 key2=value2 key3=value3'

) Engine = Log;

Store actual timeseries values in the following table:

CREATE TABLE tsdb_values (

    date Date,

    id UInt64,  -- id of the time series

    time DateTime,

    value UInt64

) Engine = MergeTree(date, (id, time), 8192);

The 'value' column could be compressed better after https://github.com/yandex/ClickHouse/issues/838 is implemented.

Then the 'insert' process would look like:

1) Obtain timeseries id for the given set of tags from tsdb_id. If the id is missing, then create new time series with the given set of tags under unique id. This step could be sped up by caching ids for the recently inserted time series.

2) Insert the time series value and time into tsdb_values. The step could be sped up by inserting into buffer table on top of tsdb_values.

The 'select' process would look like:

1) Select timeseries ids for the given set of tag filters from tsdb_id.

2) Select data for the given ids for the given time range from tsdb_values.

[man...@gmail.com]

It depends on how sparse/unrelated your metrics are.

If you have related measurements at each time (example: coordinate, velocity, temperature... in each second), then better to use a table with multiple columns (a column per metric).
If you have unrelated measurements at non-uniform intervals, better to have a column with metric id and metric value, as in Aliaksandr scheme.

About tags: you should have different columns for tags only if you have fixed number of tags of different kind for each measurement. It is highly unlikely that you have exactly 100 tags of different kind and your schema with T1..T100 columns looks questionable.
If you have variable number of tags; and tags are of same kind, better to store them in Array type.

[Otis Gospodnetic]

Hi,

I have a question about this part.  You are suggesting "tags String" column to contain sorted 'key=value' tags, such as 'key1=value1 key2=value2 key3=value3'

I assume that you then suggest one queries this tsdb_id table using something like "where tags='key1=value1 key2=value2 key3=value3'", right?

But what if one wants to use a subset of tags, e.g., 'key1=value1 key2=value2'?

This won't find anything because there is no row where tags column has such a value..... unless at insert time we didn't store just 'key1=value1 key2=value2 key3=value3' , but also all different subsets of that, so that one can use any key=value pair(s).  Is that what you are suggesting one does, or is there a better way?

Thanks,
Otis

 

[Goda Stragauskaite - Valencia]

Check this ClickHouse for Time Series article - https://www.altinity.com/blog/clickhouse-for-time-series