Optimal ClickHouse Config Values for a AWS - r5.4xlarge 16 cpu 128gb ram 4tb SSD gp2

[Romit Bhandari]

We are using following:

    <max_connections>4096</max_connections>

    <keep_alive_timeout>60</keep_alive_timeout>

    <!-- Maximum number of concurrent queries. -->

    <max_concurrent_queries>2500</max_concurrent_queries>

    <!-- Set limit on number of open files (default: maximum). This setting makes sense on Mac OS X because getrlimit() fails to retrieve

         correct maximum value. -->

    <!-- <max_open_files>262144</max_open_files> -->

    <uncompressed_cache_size>8589934592</uncompressed_cache_size>

    <!-- Approximate size of mark cache, used in tables of MergeTree family.

         In bytes. Cache is single for server. Memory is allocated only on demand.

         You should not lower this value.

      -->

    <mark_cache_size>5368709120</mark_cache_size>

    <max_session_timeout>3600</max_session_timeout>

    <default_session_timeout>60</default_session_timeout>

We are facing some slow queries, do we need to change something in the configuration above?

[Romit Bhandari]

One more thing to add: The DB size is 3.5 TB and table which has slow queries has 8 bn rows.

[Denis Zhuravlev]

>We are facing some slow queries, do we need to change something in the configuration above?

No, you don't. These parameters are good.

Need to find a root cause. The most probable reason a mis-design of the table / wrong partition by / wrong order by

[Divya Divya]

Here the partition key used in the table has the cardinality 25 

And In the order by we have used column which is used in most of the queries.

Create table db.table 

( 

Domain String,

Category String,

Topic String,

......

......

....

) ENGINE = MergeTree PARTITION BY category

ORDER BY

  (topic, domain) SETTINGS index_granularity = 8192

Couple of questions :

Moving the index key from string to integer would help in fast query time ? 

Should we make any changes in the index granularity ? 

Any other settings which can also improve fast inserts ? 

--
You received this message because you are subscribed to the Google Groups "ClickHouse" group.
To unsubscribe from this group and stop receiving emails from it, send an email to clickhouse+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/clickhouse/0315a7df-4e67-4aba-a947-b0a931fdc2c7%40googlegroups.com.

[Denis Zhuravlev]

>Moving the index key from string to integer would help in fast query time ?

yes, sometimes drastically . Also LowCardinality(String) can mitigate.

>Should we make any changes in the index granularity ?

Can you show an example of slow SQL (text) and a whole line with statistics from a clickhouse-client output (rows processes / GB/s ) (from select ..... from .... format Null;)

>Any other settings which can also improve fast inserts ?

insert only one partition at a time (only one category value in whole insert)!!!!!!!

insert at least 10k rows with insert , better 100k -- check speed upto 1mil rows per insert

To unsubscribe from this group and stop receiving emails from it, send an email to click...@googlegroups.com.