Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Using ClickHouse to scale an events engine

github.com

Post reply on HN
Print view View original

21–30 of 100 posts

Re: Using ClickHouse to scale an events engine

#21

Post by zX41ZdbW »

joshstrange wrote: post #14

I feel like with all the Clickhouse praise on HN that we /must/ be doing something fundamentally wrong because I hate every interaction I have with Clickhouse. * Timeouts (only 30s???) unless I used the cli client * Cancelling rows - Just kill me, so many bugs and FINAL/PREWHERE are massive foot-guns * Cluster just feels annoying and fragile don't forget "ON CLUSTER" or you'll have a bad time Again, I feel like we mu…

Interesting about "Timeouts (only 30s???)" - most likely, this is a limitation configured explicitly for a user on your server. You can set it up with the `max_execution_time`, and by default, it is unlimited.

For example, I've set it up, along with many more limitations for my public playground https://play.clickhouse.com/, and it allows me to, at least, make it public and not worry much.

It could also be a configuration of a proxy if you connect through a proxy. ClickHouse has built-in HTTP API, so you can query it directly from the browser or put it behind Cloudflare, etc... Where do you host ClickHouse?

Re: Using ClickHouse to scale an events engine

#22

Post by ergonaught »

joshstrange wrote: post #14

I feel like with all the Clickhouse praise on HN that we /must/ be doing something fundamentally wrong because I hate every interaction I have with Clickhouse. * Timeouts (only 30s???) unless I used the cli client * Cancelling rows - Just kill me, so many bugs and FINAL/PREWHERE are massive foot-guns * Cluster just feels annoying and fragile don't forget "ON CLUSTER" or you'll have a bad time Again, I feel like we mu…

Most, though certainly not all, problems I see with ClickHouse usage come from pretending it is another database or that it is intended for other use cases.

Re: Using ClickHouse to scale an events engine

#23

Post by jacobsenscott »

samber wrote: post #6

I'm curious: how many rows Lago store in its CH cluster? Do they collect data for fighting fraud? PG can handle a billion rows easily.

PG can handle billions of rows for certain use cases, but not easily. Generally you can make things work but you definitely start entering "heroic effort" territory.

Re: Using ClickHouse to scale an events engine

#24

Post by mosen »

joshstrange wrote: post #19
nsguy wrote: post #15

Earlier quoted context omitted.

What is your use case? If you're deleting rows that already feels like maybe it's not the intended use case. I think about clickhouse as taking in a firehose of immutable data that you want to aggregate/analyze/report on. Let's say a million records per second. I'll make up an example, the orientation, speed and acceleration of every Tesla vehicle in the world in real time every second.

It's to power all our analytics. We ETL data into it and some data is write-once so we don't have updates/deletes but a number of our tables have summary data ETL'd into them which means cleaning up the old rows. I'm sure CH shines for insert-only workloads but that doesn't cover all our needs.

Have you looked into the ReplacingMergeTree table engine? (Although we still needed to use FINAL with this one)

Re: Using ClickHouse to scale an events engine

#25

Post by shin_lao »

joshstrange wrote: post #20
shin_lao wrote: post #17

Earlier quoted context omitted.

It's meant to store immutable data, and isn't great if you need low-latency updates. Also it's quirky in some ways.

> It's meant to store immutable data I don't disagree, I feel like we might be using it wrong. We were trying to replace ES with it but it just doesn't feel like it fits our needed usecase.

How many rows do you have on average per day?

Re: Using ClickHouse to scale an events engine

#26

Post by joshstrange »

zX41ZdbW wrote: post #21
joshstrange wrote: post #14

I feel like with all the Clickhouse praise on HN that we /must/ be doing something fundamentally wrong because I hate every interaction I have with Clickhouse. * Timeouts (only 30s???) unless I used the cli client * Cancelling rows - Just kill me, so many bugs and FINAL/PREWHERE are massive foot-guns * Cluster just feels annoying and fragile don't forget "ON CLUSTER" or you'll have a bad time Again, I feel like we mu…

Interesting about "Timeouts (only 30s???)" - most likely, this is a limitation configured explicitly for a user on your server. You can set it up with the `max_execution_time`, and by default, it is unlimited. For example, I've set it up, along with many more limitations for my public playground https://play.clickhouse.com/ , and it allows me to, at least, make it public and not worry much. It could also be a configu…

I can believe it's a config issue, I'll have to look into it. I didn't setup the cluster/dbs and when I asked about I was told "use the cli". I'll try to see if I can get that fixed.

Re: Using ClickHouse to scale an events engine

#27

Post by hipadev23 »

joshstrange wrote: post #19
nsguy wrote: post #15

Earlier quoted context omitted.

What is your use case? If you're deleting rows that already feels like maybe it's not the intended use case. I think about clickhouse as taking in a firehose of immutable data that you want to aggregate/analyze/report on. Let's say a million records per second. I'll make up an example, the orientation, speed and acceleration of every Tesla vehicle in the world in real time every second.

It's to power all our analytics. We ETL data into it and some data is write-once so we don't have updates/deletes but a number of our tables have summary data ETL'd into them which means cleaning up the old rows. I'm sure CH shines for insert-only workloads but that doesn't cover all our needs.

CH works just fine for cleaning up rows: Delete with mutations sync=1, or use optimize with deduplicate by, or use aggregate trees and optimize final, or query aggregate tables with final=1.

Numerous ways to achieve removal of old/stale rows.

Re: Using ClickHouse to scale an events engine

#28

Post by joshstrange »

shin_lao wrote: post #25
joshstrange wrote: post #20

Earlier quoted context omitted.

> It's meant to store immutable data I don't disagree, I feel like we might be using it wrong. We were trying to replace ES with it but it just doesn't feel like it fits our needed usecase.

How many rows do you have on average per day?

A couple million (<10M), I don't have a better number available right now. Not all (or even most) of those need cancelling rows thankfully.

Re: Using ClickHouse to scale an events engine

#29

Post by hipadev23 »

joshstrange wrote: post #28
shin_lao wrote: post #25

Earlier quoted context omitted.

How many rows do you have on average per day?

A couple million (<10M), I don't have a better number available right now. Not all (or even most) of those need cancelling rows thankfully.

You don’t need a cluster nor should you be having any issues you mentioned. I run 10x that volume daily on a single gcp box (8 core / 64GB). We migrated off BigQuery and went from $10k/mo to about $250/mo. And it’s faster for both low-latency and big slow queries.

Re: Using ClickHouse to scale an events engine

#30

Post by alecfong »

joshstrange wrote: post #14

I feel like with all the Clickhouse praise on HN that we /must/ be doing something fundamentally wrong because I hate every interaction I have with Clickhouse. * Timeouts (only 30s???) unless I used the cli client * Cancelling rows - Just kill me, so many bugs and FINAL/PREWHERE are massive foot-guns * Cluster just feels annoying and fragile don't forget "ON CLUSTER" or you'll have a bad time Again, I feel like we mu…

What foot guns have you run into with FINAL?
Post reply on HN