Clickhouse eating up all my server's resources

[DeveloperLewis]

Past Issues Searched

• I have searched open and closed issues to make sure that the bug has not yet been reported

Issue is a Bug Report

• This is a bug report and not a feature request, nor asking for self-hosted support

Using official Plausible Cloud hosting or self-hosting?

Plausible Cloud from plausible.io

Describe the bug

Clickhouse is constantly accessing it's config.xml and just breaking. I'm not entirely sure what's happening but there are multiple instances of it and its bringing down my server. If I turn off my plausable docker contianer, it doesn't happen anymore and I get back 50-80% of all my server resources. So I know it's plausable.

Expected behavior

I don't think it should be using THIS much memory/cpu usage.

Screenshots

Environment

- OS: Ubuntu
- Browser:
- Browser Version:

[ruslandoga]

👋 @DeveloperLewis

ClickHouse isn't accessing config.xml, it's just the command that was used to start the process :)

[DeveloperLewis]

👋 @DeveloperLewis

ClickHouse isn't accessing config.xml, it's just the command that was used to start the process :)

I see! I guess this is more of a clickhouse issue then?

[ruslandoga]

Maybe there are more knobs to tune to try to make ClickHouse consume less in Plausible CE.
Prev discussion: plausible/community-edition#185

[Fakerko]

I have exactly the same problem. CPU is running at 95-100%, RAM is at about 40% - which is fine.

@DeveloperLewis
Have you solved it somehow?

[SS88UK]

I've been doing quite a lot of testing and think I have a solution. I've no idea why ClickHouse uses CPU when nothing is happening in the database however, this was my config for ClickHouse and my CPU usage has been lowered significantly.

<clickhouse>
    <mysql_port remove="1" />
    <postgresql_port remove="1" />  
    <query_thread_log remove="1" />
    <opentelemetry_span_log remove="1" />
    <processors_profile_log remove="1" />  
    <processor_profile_log remove="1" /> 
    <query_log remove="1"/>
    <text_log remove="1"/>
    <trace_log remove="1"/>
    <metric_log remove="1"/>
    <asynchronous_metric_log remove="1"/>
    <session_log remove="1"/>
    <part_log remove="1"/>
    <mlock_executable>false</mlock_executable> 
    <background_pool_size>1</background_pool_size>
    <background_merges_mutations_concurrency_ratio>2</background_merges_mutations_concurrency_ratio>
    <merge_tree>
        <merge_max_block_size>1024</merge_max_block_size>
        <max_bytes_to_merge_at_max_space_in_pool>268435456</max_bytes_to_merge_at_max_space_in_pool>
        <number_of_free_entries_in_pool_to_lower_max_size_of_merge>2</number_of_free_entries_in_pool_to_lower_max_size_of_merge>
        <number_of_free_entries_in_pool_to_execute_mutation>2</number_of_free_entries_in_pool_to_execute_mutation>
        <number_of_free_entries_in_pool_to_execute_optimize_entire_partition>2</number_of_free_entries_in_pool_to_execute_optimize_entire_partition>
  </merge_tree>
</clickhouse>

Right now im still testing it so I don't know the effects on the system but currently with a rough ~25 concurrent connections it seems to be working as expected.

The following config is a mashup of ClickHouse docs + third party (forgot the website where i got it from) which is apparently optimized for RAM usage too. This also works and keeps my CPU low.

<clickhouse>
	<!-- disable some optional components/tables -->
	<mysql_port remove="1" />
	<postgresql_port remove="1" />  
	<query_thread_log remove="1" />
	<opentelemetry_span_log remove="1" />
	<processors_profile_log remove="1" />  
	<processor_profile_log remove="1" /> 

	<!-- Disable some system tables that produce frequent writes -->
	<query_log remove="1"/>
	<text_log remove="1"/>
	<trace_log remove="1"/>
	<metric_log remove="1"/>
	<asynchronous_metric_log remove="1"/>
	<session_log remove="1"/>
	<part_log remove="1"/>

	<!-- disable mlock, allowing binary pages to be unloaded from RAM, relying on Linux defaults -->
	<mlock_executable>false</mlock_executable> 

	<!-- decrease the cache sizes -->
	<mark_cache_size>268435456</mark_cache_size>
	<index_mark_cache_size>67108864</index_mark_cache_size>
	<uncompressed_cache_size>16777216</uncompressed_cache_size>

	<!-- control the concurrency -->
	<max_thread_pool_size>2000</max_thread_pool_size>
	<max_connections>64</max_connections>
	<max_concurrent_queries>32</max_concurrent_queries>
	<max_server_memory_usage_to_ram_ratio>0.50</max_server_memory_usage_to_ram_ratio> <!-- 50% of the RAM, leave more for the system -->
	<max_server_memory_usage>0</max_server_memory_usage> <!-- We leave the overcommiter to manage available ram for queries-->

	<!-- reconfigure the main pool to limit the merges (those can create problems if the insert pressure is high) -->
	<background_pool_size>1</background_pool_size>
	<background_merges_mutations_concurrency_ratio>2</background_merges_mutations_concurrency_ratio>

	<merge_tree>
	<merge_max_block_size>1024</merge_max_block_size>
		<max_bytes_to_merge_at_max_space_in_pool>268435456</max_bytes_to_merge_at_max_space_in_pool>
		<number_of_free_entries_in_pool_to_lower_max_size_of_merge>2</number_of_free_entries_in_pool_to_lower_max_size_of_merge>
		<number_of_free_entries_in_pool_to_execute_mutation>2</number_of_free_entries_in_pool_to_execute_mutation>
		<number_of_free_entries_in_pool_to_execute_optimize_entire_partition>2</number_of_free_entries_in_pool_to_execute_optimize_entire_partition>
	</merge_tree>

	<!-- shrink all pools to minimum-->
	<background_buffer_flush_schedule_pool_size>1</background_buffer_flush_schedule_pool_size>
	<background_merges_mutations_scheduling_policy>round_robin</background_merges_mutations_scheduling_policy>
	<background_move_pool_size>1</background_move_pool_size>
	<background_fetches_pool_size>1</background_fetches_pool_size>
	<background_common_pool_size>2</background_common_pool_size>
	<background_schedule_pool_size>8</background_schedule_pool_size>
	<background_message_broker_schedule_pool_size>1</background_message_broker_schedule_pool_size>
	<background_distributed_schedule_pool_size>1</background_distributed_schedule_pool_size>
	<tables_loader_foreground_pool_size>0</tables_loader_foreground_pool_size>
	<tables_loader_background_pool_size>0</tables_loader_background_pool_size>


	<profiles>
		<default>
			<log_queries>0</log_queries>
			<log_query_threads>0</log_query_threads>
			<max_threads>1</max_threads>
			<queue_max_wait_ms>1000</queue_max_wait_ms>
			<max_execution_time>600</max_execution_time>
			<input_format_parallel_parsing>0</input_format_parallel_parsing>
			<output_format_parallel_formatting>0</output_format_parallel_formatting>
			<max_bytes_before_external_group_by>1073741824</max_bytes_before_external_group_by>
			<max_bytes_before_external_sort>1073741824</max_bytes_before_external_sort>
			<max_block_size>1024</max_block_size>
			<max_download_threads>1</max_download_threads>
			<os_thread_priority>19</os_thread_priority>
		</default>
	</profiles>
</clickhouse>

With this config, take note of max_connections and max_concurrent_queries. I've seen in my logs info/warning/errors about maximum concurrent queries when set too low. One could assume those queries are rejected i.e. missing statistical data. I feel 64/32 is OK.

I'm running ClickHouse clickhouse-server:24.12-alpine and both configs works, so, yeah, give it a go. Add it into the compose:

- ./clickhouse-config.xml:/etc/clickhouse-server/config.d/custom.xml:ro

Thanks,
Sully