SolvedClickHouse Any plans to support separation hot/cold data at the level of partitions?

With the completion of Q1-Q2 / 2018, clickhouse support store data at multiple disk volumes of a single server at the level of DBs or tables. Is there a plan to support this feature at the partitions?

We want migrate PB-level data to clickhouse hope the partitions can be stored on different performance medias likes hdd, ssd .

We define hot/cold data based on partitions by date, we hope the cold data partitions can be storage on HDD , and hot data partitions can be storage on SSD, In order to achieve good economic benefits.

I think this is a common practice in the field of big data. How do you think about it?

17 Answers

βœ”οΈAccepted Answer

@liuqian1989 it's kind of obvious possible feature, but in practice ClickHouse works fast enough even on 100% HDD given there's enough memory for page cache, so it's priority is not that high.

I'd suggest you to try to put all those petabytes on HDD and see how it goes. Or you have already tried and are not satisfied? In any case, please give us more details.

@blinkov

Really trust the performance of clickhouse on HDD and very agree with your point. :) In my case, HDD is not an issue, support enough memory also not an issue , just wanna to find a way Cost-effective store the huge data.

Let me introduce our case:

  • ~ 10TB of app logs per day.
  • ~ 1TB increase per month.
  • Clickhouse table partition by date YYYYMMDD.
  • Common sharding and replica set architecture.
  • Deployed on 18 servers.
  • Our flow
    • [android/ios client app] => [server api] => [kafka] => [clickhouse] => [grafana]

We have a wealth of HDD resources not many SSD(reason for price). As @filimonov reply above, we actually define the last week as HOT data.

So, in the case of HDD + SSD, we prefer use SSD serve the hot data and we also accept the performance of loading cold data from HDD.

As for why i want to split data on partition level, most logging data is written to a single table by date/timestamp and it is continuously written. The partition function naturally provides a way to split data for large tables, no need to split table, no need split a query into multiple queries.

If support split data on the table partition level, it also keep the cold data and hot data both online.

Other Answers:

@filimonov if I understand you correctly, this would leave us with 2 separate databases and 2 separate tables, each in its own database.

Actually, it can be in one database, enough that table directories will point to different storages.

So the simplest way to archive it currently:

create table mydb.mytable_ssd ( ... ) Engine=MergeTree ...;
create table mydb.mytable_hdd as mydb.mytable_ssd Engine=MergeTree  ...;

Mount (or symlink) /var/lib/clickhouse/data/mydb/mytable_hdd and /var/lib/clickhouse/data/mydb/mytable_ssd to different storages.

Create a table with Engine=Merge above those two tables to make it possible to select both of them as from one logical table:

CREATE TABLE mydb.mytable as mydb.mytable_ssd ENGINE=Merge(mydb, '^mytable_');

What we would like to achieve is to have 1 logical table with many partitions, which are physically written on different media.

Yes, I clearly understand that requirement and hope that it will be implemented exactly as you describe. You were asking about workarounds which can be used right now (before that feature will be implemented), so the answer is - 'yes, there are', and it's doable if you need it right now.

More Issues: