5 ms·

There are various knobs in ClickHouse that allow you to trade memory usage for performance. ( https://clickhouse.com/docs/en/operations/settings/query-complexit

by nsguy 2y ago

There are various knobs in ClickHouse that allow you to trade memory usage for performance. ( https://clickhouse.com/docs/en/operations/settings/query-complexity#settings-max_bytes_before_external_group_by 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 be a thousand or more times slower, and doesn't have the horizontal scalability, so if you need to do complex queries/aggregations over billions of records then "return result" doesn't cut it. If postgres addresses your needs then great- you don't need to use ClickHouse...

riku_iki 2y ago
> 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.
- alright2565 2y ago
  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 @@  - <max_connections>4096</max_connections> + <max_connections>2000</max_connections>  <keep_alive_timeout>3</keep_alive_timeout> @@ -270,7 +270,7 @@ -->  - <max_concurrent_queries>100</max_concurrent_queries> + <max_concurrent_queries>4</max_concurrent_queries>  - <mark_cache_size>5368709120</mark_cache_size> + <mark_cache_size>805306368</mark_cache_size>  -   <default>  - <max_memory_usage>10000000000</max_memory_usage> + <max_memory_usage>536870912</max_memory_usage> + + <queue_max_wait_ms>1000</queue_max_wait_ms> + <max_execution_time>30</max_execution_time> + <background_pool_size>4</background_pool_size> + <!-- How to choose between replicas during distributed query processing. random - choose random replica from set of replicas with minimum number of errors
  - riku_iki 2y ago
    > 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.
    - FridgeSeal 2y ago
      How is your table setup? It’s plausible the on-disk/index layout is not amenable to the kinds of queries you’re trying to do. What kind of queries are you trying to do? Also, what kind of machine are you running on?
      - riku_iki 2y ago
        Trivial example would be to run select count(distinct) from large table with high cardinality values: https://github.com/ClickHouse/ClickHouse/issues/47520 https://github.com/ClickHouse/ClickHouse/issues/47520

by nsguy 2y ago

riku_iki 2y ago

> 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.

alright2565 2y ago
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 @@  - <max_connections>4096</max_connections> + <max_connections>2000</max_connections>  <keep_alive_timeout>3</keep_alive_timeout> @@ -270,7 +270,7 @@ -->  - <max_concurrent_queries>100</max_concurrent_queries> + <max_concurrent_queries>4</max_concurrent_queries>  - <mark_cache_size>5368709120</mark_cache_size> + <mark_cache_size>805306368</mark_cache_size>  -   <default>  - <max_memory_usage>10000000000</max_memory_usage> + <max_memory_usage>536870912</max_memory_usage> + + <queue_max_wait_ms>1000</queue_max_wait_ms> + <max_execution_time>30</max_execution_time> + <background_pool_size>4</background_pool_size> + <!-- How to choose between replicas during distributed query processing. random - choose random replica from set of replicas with minimum number of errors
- riku_iki 2y ago
  > 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.
  - FridgeSeal 2y ago
    How is your table setup? It’s plausible the on-disk/index layout is not amenable to the kinds of queries you’re trying to do. What kind of queries are you trying to do? Also, what kind of machine are you running on?
    - riku_iki 2y ago
      Trivial example would be to run select count(distinct) from large table with high cardinality values: https://github.com/ClickHouse/ClickHouse/issues/47520 https://github.com/ClickHouse/ClickHouse/issues/47520