Smaller Clickhouse Setup for a homelab

Some people would possibly would like be running a Clickhouse-server in a environment with less then the default of 32 GB system memory for testing purposes or a homelab setup. In my case clickhhouse-server runs on a 16-GB/4-core laptop alongside some docker containers.

it could be usefull to tune Clickhouse to this situation so it does not run out of memory or eat up lots of or all system resources. This setup example is based on a default installation of Clickhouse on Rocky Linux 8 or 9, and running docker and Coroot on the same system, keeping 7 to 21 days of data with ease and so 3GB of data in the clickhouse databases.

Smaller logging setup:

When running a default setup, Clickhouse can take up a lot of data for logging to logfiles that one might not need. You can adjust the logging level and the amount of logging data to keep in: /etc/clickhouse-server/config.xml

Change the log level from debug to information (or even none):

   <levels>
  <ConfigReloader>information</ConfigReloader>
   </levels>

Change the amount of logging data (default 1000M) and number of files tot keep (default 10)

   <size>100M</size>
   <count>7</count>

No database logging setup:

Clickhouse keeps it's logging in the database, and this can be an extensive amount of data (20GB). Logging to the database can be completely disabled.

To do this create a file: /etc/clickhouse-server/config.d/z_log_disable.xml

Put the following in the file:

<?xml version="1.0"?>
<clickhouse>
<asynchronous_metric_log remove="1"/>
<metric_log remove="1"/>
<latency_log remove="1"/>
<query_thread_log remove="1" />
<query_log remove="1" />
<query_views_log remove="1" />
<part_log remove="1"/>
<session_log remove="1"/>
<text_log remove="1" />
<trace_log remove="1"/>
<crash_log remove="1"/>
<opentelemetry_span_log remove="1"/>
<zookeeper_log remove="1"/>
</clickhouse>

Clickhouse-server memory adjustments:

Adjust cache sizes in /etc/clickhouse-server/config.xml:

<mark_cache_size>268435456</mark_cache_size>

<index_mark_cache_size>67108864</index_mark_cache_size>

<uncompressed_cache_size>16777216</uncompressed_cache_size>

Adjust memory usage ratio in /etc/clickhouse-server/config.xml:

<max_server_memory_usage_to_ram_ratio>0.5</max_server_memory_usage_to_ram_ratio>

This means 50% of available RAM can be used by clickhouse.

Lower some connection and query pools

<max_thread_pool_size>2000</max_thread_pool_size>

<max_connections>128</max_connections>

<max_concurrent_queries>64</max_concurrent_queries>

<query_condition_cache_size>53350400</query_condition_cache_size>

After al these changes restart clickhouse-server and your setup will be running smooth in a smaller environment, in my case this is a 4 core 16GB node.