2 ms·

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 t

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.