Smaller Clickhouse Setup

Some people would be running a Clickhouse-server in an environment with less than the default of 32 GB system memory for testing purposes or a homelab setup.

it could be wishful to tune Clickhouse to this situation so it does not run out of memory or eat up lots of system resources. This setup example is based on a default installation of Clickhouse on Rocky Linux 8 or 9, with running docker and Coroot on the same system, keeping seven to fourteen days of data with ease.

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 also 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.6</max_server_memory_usage_to_ram_ratio>

Lower the tread pool size

<max_thread_pool_size>5000</max_thread_pool_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.