4 ms·
I had as well excellent results when I experimented with Clickhouse (in my case the table with the most rows that I ever created had 6 columns using a total of
by zepearl 6y ago
I had as well excellent results when I experimented with Clickhouse (in my case the table with the most rows that I ever created had 6 columns using a total of ~55 bytes per row - after having inserted ~35 billion rows the table was 450MiB big). My usecases involving big aggregations and mini-searches (when searching/retrieving just few rows) were excellent (big aggr) respectively good (small searches - not as fast as a normal DB, but for me for sure acceptable).
For whoever is interested to try it out, be aware that you really should understand how "parts" and "merges" work and your write-workload should take that into consideration (for example dynamically lower the workload when "merges" are running, take into consideration the I/O needed to delete pending old "parts", etc...) - if you don't then you're very likely to get bad performance if your workload is write-heavy. The amount of columns and the sort order is as well extremely important.
Last year I decided to start using Clickhouse to host Collecd's metrics ( https://www.segfault.digital/how-to-directly-use-the-clickhouse-db-with-grafana-and-collectd https://www.segfault.digital/how-to-directly-use-the-clickho... ) and so far it's working well. I upgraded twice the version of the CH-software, and killed once by mistake its VM but afterwards I never had problems ("so far", hehe).
- wiredfool 6y agoYeah, DoubleDelta + compression is amazing. I have one column that's an incrementing integer per partition, and for a million rows the total storage for that field is ~20k. That's a savings of something like 10% over the whole dataset.
- zepearl 6y ago> That's a savings of something like 10% over the whole dataset. Sorry, I'm probably misunderstanding you - I'm not sure about that "10%", what does it it refer to? Anyway, I do like a lot in Clickhouse to be able to chain codecs (I personally like to think about "Delta/DoubleDelta/Gorilla/T64 codecs" being "encodings" and the "general purpose codecs LZ4/LZ4HC/ZSTD" being "compression codecs"). I don't have a good math background => about Delta & DoubleDelta I liked this explanation ( https://altinity.com/blog/2019/7/new-encodings-to-improve-clickhouse https://altinity.com/blog/2019/7/new-encodings-to-improve-cl... ) which defines "delta" as tracking "distance" and "doubledelta" tracking "acceleration" between consecutive column values. In the end I tested, using relatively small datasets (which were different between usecases), all combinations of encodings (delta/doubledelta/gorilla/t64) and ZSTD-compression levels (mostly 1/3/9) (I ignored LZ4*). - "Delta"&"DoubleDelta" were often interesting (but in general for my data using "Delta"+ZSTD was already good enough compared to the rest). - "Gorilla" somehow never gave me any benefits if compared to other codecs and/or compression algos. - "T64" is a bit a mistery for me, anyway in some tests it delivered excellent results compared to the other combinations, therefore I'm currently using just T64 for some columns, and for some other columns as T64+ZSTD(9). EDIT: sorry, I think I got it - you probably meant something like "just by doing that on that specific column, the overall storage needs were reduced by 10% for the whole dataset", right?
- wiredfool 6y agoYep. That's it.
- hodgesrm 6y agoThe system tables are incredibly helpful for optimizing ClickHouse performance. The system.columns table gives exact compression ratios for every column and system.parts shows similar stats for table parts. It's possible to tune schema very effectively without a lot of deep knowledge of how codecs and table partitioning work internally.