5 ms·

I love clickhouse: it's simple yet flexible enough and free software. I'm migrating a lot of data to clickhouse on clusters of servers. One of the unexpected

I love clickhouse: it's simple yet flexible enough and free software. I'm migrating a lot of data to clickhouse on clusters of servers. One of the unexpected gains is how it uses far less disk space, which means the data can be put on a few NVME in RAID10, which provides speed benefits even for data that can't fit in memory. I am now also considering ClickHouse for "cold storage": either by compressing the directories or leaving them as such on a smaller server. Being able to standardize on one thing for both production and storage would be nice.

PeterZaitsev 8y ago
Can you share results what is the space consumption difference for your data ?
- sin7 8y ago
  Take the data in your database, 7zip it. If your data is very redundant, you'll see great compression. If it's not you won't.
  - paulryanrogers 8y ago
    You mean 7z the raw SQL? Just as a preemptive check before moving to Clickhouse?
    - sin7 8y ago
      7z the csv. Get a million rows from your table. Compare the csv to the 7z. That will give you a good idea on what kind of compression you will see. I have very redundant data. I often see 10:1 compression. It's a lot less when I have hashes, addresses and timestamps.
      - dikei 8y ago
        In addition, columnar format like the one used by Click House can often be compressed even better than row format like CSV due to the similarity between value in a column.
        devereaux 8y ago
        And no need to play with columns ordering like with psql: https://blog.2ndquadrant.com/on-rocks-and-sand/ https://blog.2ndquadrant.com/on-rocks-and-sand/
- sethhochberg 8y ago
  Not the poster above, but using Clickhouse for similar purposes (archival/analytics on huge data that started out in MySQL and gets imported to Clickhouse for the long term). Some real-world numbers on our end for the same time period, roughly 2.5 billion rows: InnoDB Barracuda - (logical data + indexes 583GB, physical ZFS LZ4: 386GB, ~1.51x compression radio) Clickhouse MergeTree - (logical 349GB, physical ZFS LZ4: 279GB, ~1.27x compression ratio) The MySQL version of the data has a few different compound indexes which certainly contribute to the size difference, but regardless Clickhouse is dozens of times faster on complex queries against this data. We run both our MySQL and Clickhouse servers on ZFS (Clickhouse accesses its datastore over NFS on a 10gbit link... in practical terms we have not seen a severe performance penalty for doing this, even though we know it isn't encouraged by the Clickhouse maintainers).
  - devereaux 8y ago
    [my numbers match yours] It's very interesting that we made more or less the same choices: NFS on a LAN for cold storage. In practice, I haven't seen performance penalties either. I will certainly standardize on that. If needed, 'reviving' the cold stored data is as simple as copying it to other machines-- which will also offer sharding. My only reproach is that servers have to be identical, as clickhouse doesn't seem to be able to take into account the speed differences. My backup servers are quite different from the production server, but in an emergency I'd like to use them all.

PeterZaitsev 8y ago

Can you share results what is the space consumption difference for your data ?

sin7 8y ago
Take the data in your database, 7zip it. If your data is very redundant, you'll see great compression. If it's not you won't.
- paulryanrogers 8y ago
  You mean 7z the raw SQL? Just as a preemptive check before moving to Clickhouse?
  - sin7 8y ago
    7z the csv. Get a million rows from your table. Compare the csv to the 7z. That will give you a good idea on what kind of compression you will see. I have very redundant data. I often see 10:1 compression. It's a lot less when I have hashes, addresses and timestamps.
    - dikei 8y ago
      In addition, columnar format like the one used by Click House can often be compressed even better than row format like CSV due to the similarity between value in a column.
      - devereaux 8y ago
        And no need to play with columns ordering like with psql: https://blog.2ndquadrant.com/on-rocks-and-sand/ https://blog.2ndquadrant.com/on-rocks-and-sand/
sethhochberg 8y ago
Not the poster above, but using Clickhouse for similar purposes (archival/analytics on huge data that started out in MySQL and gets imported to Clickhouse for the long term). Some real-world numbers on our end for the same time period, roughly 2.5 billion rows: InnoDB Barracuda - (logical data + indexes 583GB, physical ZFS LZ4: 386GB, ~1.51x compression radio) Clickhouse MergeTree - (logical 349GB, physical ZFS LZ4: 279GB, ~1.27x compression ratio) The MySQL version of the data has a few different compound indexes which certainly contribute to the size difference, but regardless Clickhouse is dozens of times faster on complex queries against this data. We run both our MySQL and Clickhouse servers on ZFS (Clickhouse accesses its datastore over NFS on a 10gbit link... in practical terms we have not seen a severe performance penalty for doing this, even though we know it isn't encouraged by the Clickhouse maintainers).
- devereaux 8y ago
  [my numbers match yours] It's very interesting that we made more or less the same choices: NFS on a LAN for cold storage. In practice, I haven't seen performance penalties either. I will certainly standardize on that. If needed, 'reviving' the cold stored data is as simple as copying it to other machines-- which will also offer sharding. My only reproach is that servers have to be identical, as clickhouse doesn't seem to be able to take into account the speed differences. My backup servers are quite different from the production server, but in an emergency I'd like to use them all.