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

51–60 of 100 posts

Re: Using ClickHouse to scale an events engine

#51

Post by alright2565 »

riku_iki wrote: post #18
nsguy wrote: post #13

Earlier quoted context omitted.

There are various knobs in ClickHouse that allow you to trade memory usage for performance. ( https://clickhouse.com/docs/en/operations/settings/query-com... e.g.) But yes, I've seen similar issues, running out of memory during query processing, it's a price you pay for higher performance. You need to know what's happening under the hood and do more work to make sure your queries will work well. I think postgres can…

> There are various knobs in ClickHouse that allow you to trade memory usage for performance. but what knobs to use and what values to use in each specific case? Query just usually fails with some generic OOM message without much information.

It's not actually so esoteric. The two main knobs are

- max_concurrent_queries, since each query uses a certain amount of memory

- max_memory_usage, which is the max per-query memory usage

Here's my full config for running clickhouse on a 2GiB server without OOMs. Some stuff in here is likely irrelevant, but it's a starting point.

    diff --git a/clickhouse-config.xml b/clickhouse-config.xml
    index f8213b65..7d7459cb 100644
    --- a/clickhouse-config.xml
    +++ b/clickhouse-config.xml
    @@ -197,7 +197,7 @@
     
         4096 -->
     
    -    4096
    +    2000
     
         
         3
    @@ -270,7 +270,7 @@
         -->
     
         
    -    100
    +    4
     
         
    -    5368709120
    +    805306368
     
     
         
     
         
    -    
    -        5
    +        2048
    +        1073741824
    +        0
         
    -    -->
     
         
             
                 
    -            10000000000
    +            536870912
    +
    +            1000
    +            30
    +            4
    +

Re: Using ClickHouse to scale an events engine

#53

Post by alright2565 »

mdaniel wrote: post #44
kapilvt wrote: post #12

Earlier quoted context omitted.

for others curious ParadeDB - AGPL License https://github.com/paradedb/paradedb/blob/dev/LICENSE Hydra - Apache 2.0 https://github.com/hydradatabase/hydra/blob/main/LICENSE also hydra seems derived from citusdata's columnar implementation.

Don't feel bad, lots of people get bitten by not reading all the way down to the bottom of their readme: https://github.com/hydradatabase/hydra/blob/v1.1.2/README.md... While Hydra may very well license their own code Apache 2, they ship the AGPLv3 columnar which to my very best IANAL understanding taints the whole stack and AGPLv3's everything all the way through https://github.com/hydradatabase/hydra/blob/v1.1.2/co…

the only additional requirement that the AGPL introduces is that if you modify the AGPL software, you have to provide people who can access it over the network the code.

If you just use a pre-built package, the AGPL has the exact same requirements as the GPL.

Re: Using ClickHouse to scale an events engine

#54

Post by riku_iki »

alright2565 wrote: post #51
riku_iki wrote: post #18

Earlier quoted context omitted.

> There are various knobs in ClickHouse that allow you to trade memory usage for performance. but what knobs to use and what values to use in each specific case? Query just usually fails with some generic OOM message without much information.

It's not actually so esoteric. The two main knobs are - max_concurrent_queries, since each query uses a certain amount of memory - max_memory_usage, which is the max per-query memory usage Here's my full config for running clickhouse on a 2GiB server without OOMs. Some stuff in here is likely irrelevant, but it's a starting point. diff --git a/clickhouse-config.xml b/clickhouse-config.xml index f8213b65..7d7459cb 100…

> The two main knobs are

my experience is that those are not enough, multiple algorithms will just fail saying you hit max memory limit. There are many other knobs, for example: when to start external aggregation or sorting. For some cases I couldn't figure out setup and query just hits OOM without any ideas how to fix it.

Re: Using ClickHouse to scale an events engine

#55

Post by stephen123 »

unixhero wrote: post #46
stephen123 wrote: post #45

How were they doing millions of events per minute with postgres. I'm struggling with pg write performance ATM and want some tips.

Turn off indexing and other optimizations done on a table level

What do you do to then query the data? I usually need indexes so queries are not slow. Perhaps I could insert into a staging table then bulk copy the data over to an indexed table, but that seems silly.

Re: Using ClickHouse to scale an events engine

#56

Post by andretti1977 »

I have a tangentially related question since I don’t use an Olap db: is deleting data so hard to perform? Is it necessarily an immutable storage?

If so, is it a gdpr compliant storage solution? I am asking it since gdpr compliance may require data deletion (or at least anonimization)

Re: Using ClickHouse to scale an events engine

#57

Post by phantompeace »

stephen123 wrote: post #55
unixhero wrote: post #46

Earlier quoted context omitted.

Turn off indexing and other optimizations done on a table level

What do you do to then query the data? I usually need indexes so queries are not slow. Perhaps I could insert into a staging table then bulk copy the data over to an indexed table, but that seems silly.

Could replicating to a DB with indexing (purely for queries) work?

Re: Using ClickHouse to scale an events engine

#58

Post by unixhero »

stephen123 wrote: post #55
unixhero wrote: post #46

Earlier quoted context omitted.

Turn off indexing and other optimizations done on a table level

What do you do to then query the data? I usually need indexes so queries are not slow. Perhaps I could insert into a staging table then bulk copy the data over to an indexed table, but that seems silly.

You said you struggled with writes... so I mentioned an advice on how to speed up writes... the internet know a lot more about this than me tho

Re: Using ClickHouse to scale an events engine

#59

Post by orf »

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…

From the docs on FINAL:

> However, using FINAL is sometimes necessary in order to produce accurate results

Welp.

Re: Using ClickHouse to scale an events engine

#60

Post by lmz »

stephen123 wrote: post #55
unixhero wrote: post #46

Earlier quoted context omitted.

Turn off indexing and other optimizations done on a table level

What do you do to then query the data? I usually need indexes so queries are not slow. Perhaps I could insert into a staging table then bulk copy the data over to an indexed table, but that seems silly.

Isn't that basically the idea behind the "lambda architecture"? Of course you typically don't use the same product for both the real time and the batch aspects.
Post reply on HN