how to set maximum memory to be used by clickhouse-server

[badvir]

Hi,

I want to set maximum memory to be used by clickhouse-server under 1GB.
I tried to change several options to make sure the memory usage does not exceed 1GB.

After the server started, the memory seemed to increase and decrease, but after a certain amount of insert command, the memory no longer decreases.

Currently, it is not storing and searching, but memory is not decreasing.
Please, let me know how to set maximum memory to be used by clickhouse-server.

My configuration is as follows.

<max_memory_usage>1000000000</max_memory_usage>
<max_memory_usage_for_user>1000000000</max_memory_usage_for_user>
<max_memory_usage_for_all_queries>1000000000</max_memory_usage_for_all_queries>
<background_pool_size>2</background_pool_size>
<experimental_allow_extended_storage_definition_syntax>1</experimental_allow_extended_storage_definition_syntax>
<use_uncompressed_cache>0</use_uncompressed_cache>

Top info

8648 clickho+ 20 0 2999024 2.204g 12872 S 1.3 57.1 77:08.84 clickhouse-serv

clickhouse info

ClickHouse server version 1.1.54310.

:) select * from asynchronous_metrics

SELECT *
FROM asynchronous_metrics

┌─metric────────────────────────────────────┬──────value─┐
│ tcmalloc.transfer_cache_free_bytes │ 6750208 │
│ tcmalloc.central_cache_free_bytes │ 2151792 │
│ tcmalloc.current_total_thread_cache_bytes │ 28456584 │
│ generic.heap_size │ 2598379520 │
│ generic.current_allocated_bytes │ 1235670024 │
│ ReplicasMaxRelativeDelay │ 0 │
│ tcmalloc.pageheap_unmapped_bytes │ 122200064 │
│ ReplicasMaxAbsoluteDelay │ 0 │
│ ReplicasSumMergesInQueue │ 0 │
│ MarkCacheFiles │ 1658 │
│ tcmalloc.thread_cache_free_bytes │ 28456584 │
│ MaxPartCountForPartition │ 15 │
│ UncompressedCacheBytes │ 0 │
│ ReplicasMaxQueueSize │ 0 │
│ tcmalloc.pageheap_free_bytes │ 1203150848 │
│ UncompressedCacheCells │ 0 │
│ ReplicasMaxInsertsInQueue │ 0 │
│ ReplicasMaxMergesInQueue │ 0 │
│ Uptime │ 89788 │
│ MarkCacheBytes │ 118323888 │
│ ReplicasSumInsertsInQueue │ 0 │
│ ReplicasSumQueueSize │ 0 │
└───────────────────────────────────────────┴────────────┘

:) select * from metrics

SELECT *
FROM metrics

┌─metric───────────────────────────────────┬─value─┐
│ Query │ 1 │
│ Merge │ 0 │
│ ReplicatedFetch │ 0 │
│ ReplicatedSend │ 0 │
│ ReplicatedChecks │ 0 │
│ BackgroundPoolTask │ 0 │
│ DiskSpaceReservedForMerge │ 0 │
│ DistributedSend │ 0 │
│ QueryPreempted │ 0 │
│ TCPConnection │ 1 │
│ HTTPConnection │ 0 │
│ InterserverConnection │ 0 │
│ OpenFileForRead │ 0 │
│ OpenFileForWrite │ 0 │
│ Read │ 1 │
│ Write │ 0 │
│ SendExternalTables │ 0 │
│ QueryThread │ 0 │
│ ReadonlyReplica │ 0 │
│ LeaderReplica │ 0 │
│ MemoryTracking │ 8704 │
│ MemoryTrackingInBackgroundProcessingPool │ 0 │
│ MemoryTrackingForMerges │ 0 │
│ LeaderElection │ 0 │
│ EphemeralNode │ 0 │
│ ZooKeeperWatch │ 0 │
│ DelayedInserts │ 0 │
│ ContextLockWait │ 0 │
│ StorageBufferRows │ 0 │
│ StorageBufferBytes │ 0 │
│ DictCacheRequests │ 0 │
│ Revision │ 54310 │
│ RWLockWaitingReaders │ 0 │
│ RWLockWaitingWriters │ 0 │
│ RWLockActiveReaders │ 1 │
│ RWLockActiveWriters │ 0 │
└──────────────────────────────────────────┴───────┘

:) select * from settings

SELECT *
FROM settings

┌─name───────────────────────────────────────────────────────┬─value──────────────────┬─changed─┐
│ min_compress_block_size │ 65536 │ 0 │
│ max_compress_block_size │ 1048576 │ 0 │
│ max_block_size │ 65536 │ 0 │
│ max_insert_block_size │ 1048576 │ 0 │
│ min_insert_block_size_rows │ 1048576 │ 0 │
│ min_insert_block_size_bytes │ 268435456 │ 0 │
│ max_threads │ 8 │ 0 │
│ max_read_buffer_size │ 1048576 │ 0 │
│ max_distributed_connections │ 1024 │ 0 │
│ max_query_size │ 262144 │ 0 │
│ interactive_delay │ 100000 │ 0 │
│ connect_timeout │ 10 │ 0 │
│ connect_timeout_with_failover_ms │ 50 │ 0 │
│ receive_timeout │ 300 │ 0 │
│ send_timeout │ 300 │ 0 │
│ queue_max_wait_ms │ 5000 │ 0 │
│ poll_interval │ 10 │ 0 │
│ distributed_connections_pool_size │ 1024 │ 0 │
│ connections_with_failover_max_tries │ 3 │ 0 │
│ extremes │ 0 │ 0 │
│ use_uncompressed_cache │ 0 │ 1 │
│ replace_running_query │ 0 │ 0 │
│ background_pool_size │ 2 │ 1 │
│ distributed_directory_monitor_sleep_time_ms │ 100 │ 0 │
│ distributed_directory_monitor_batch_inserts │ 0 │ 0 │
│ optimize_move_to_prewhere │ 1 │ 0 │
│ replication_alter_partitions_sync │ 1 │ 0 │
│ replication_alter_columns_timeout │ 60 │ 0 │
│ load_balancing │ random │ 1 │
│ totals_mode │ after_having_exclusive │ 0 │
│ totals_auto_threshold │ 0.5 │ 0 │
│ compile │ 0 │ 0 │
│ min_count_to_compile │ 3 │ 0 │
│ group_by_two_level_threshold │ 100000 │ 0 │
│ group_by_two_level_threshold_bytes │ 100000000 │ 0 │
│ distributed_aggregation_memory_efficient │ 0 │ 0 │
│ aggregation_memory_efficient_merge_threads │ 0 │ 0 │
│ max_parallel_replicas │ 1 │ 0 │
│ parallel_replicas_count │ 0 │ 0 │
│ parallel_replica_offset │ 0 │ 0 │
│ skip_unavailable_shards │ 0 │ 0 │
│ distributed_group_by_no_merge │ 0 │ 0 │
│ merge_tree_min_rows_for_concurrent_read │ 163840 │ 0 │
│ merge_tree_min_rows_for_seek │ 0 │ 0 │
│ merge_tree_coarse_index_granularity │ 8 │ 0 │
│ merge_tree_max_rows_to_use_cache │ 1048576 │ 0 │
│ merge_tree_uniform_read_distribution │ 1 │ 0 │
│ optimize_min_equality_disjunction_chain_length │ 3 │ 0 │
│ min_bytes_to_use_direct_io │ 0 │ 0 │
│ force_index_by_date │ 0 │ 0 │
│ force_primary_key │ 0 │ 0 │
│ strict_insert_defaults │ 0 │ 0 │
│ mark_cache_min_lifetime │ 10000 │ 0 │
│ max_streams_to_max_threads_ratio │ 1 │ 0 │
│ network_compression_method │ lz4 │ 0 │
│ network_zstd_compression_level │ 1 │ 0 │
│ priority │ 0 │ 0 │
│ log_queries │ 0 │ 0 │
│ log_queries_cut_to_length │ 100000 │ 0 │
│ distributed_product_mode │ deny │ 0 │
│ global_subqueries_method │ push │ 0 │
│ max_concurrent_queries_for_user │ 0 │ 0 │
│ insert_deduplicate │ 1 │ 0 │
│ insert_quorum │ 0 │ 0 │
│ insert_quorum_timeout │ 600000 │ 0 │
│ select_sequential_consistency │ 0 │ 0 │
│ table_function_remote_max_addresses │ 1000 │ 0 │
│ read_backoff_min_latency_ms │ 1000 │ 0 │
│ read_backoff_max_throughput │ 1048576 │ 0 │
│ read_backoff_min_interval_between_events_ms │ 1000 │ 0 │
│ read_backoff_min_events │ 2 │ 0 │
│ memory_tracker_fault_probability │ 0 │ 0 │
│ enable_http_compression │ 0 │ 0 │
│ http_zlib_compression_level │ 3 │ 0 │
│ http_native_compression_disable_checksumming_on_decompress │ 0 │ 0 │
│ resharding_barrier_timeout │ 300 │ 0 │
│ count_distinct_implementation │ uniqExact │ 0 │
│ output_format_write_statistics │ 1 │ 0 │
│ add_http_cors_header │ 0 │ 0 │
│ input_format_skip_unknown_fields │ 0 │ 0 │
│ input_format_values_interpret_expressions │ 1 │ 0 │
│ output_format_json_quote_64bit_integers │ 1 │ 0 │
│ output_format_json_quote_denormals │ 0 │ 0 │
│ output_format_pretty_max_rows │ 10000 │ 0 │
│ use_client_time_zone │ 0 │ 0 │
│ send_progress_in_http_headers │ 0 │ 0 │
│ http_headers_progress_interval_ms │ 100 │ 0 │
│ fsync_metadata │ 1 │ 0 │
│ input_format_allow_errors_num │ 0 │ 0 │
│ input_format_allow_errors_ratio │ 0 │ 0 │
│ join_use_nulls │ 0 │ 0 │
│ preferred_block_size_bytes │ 1000000 │ 0 │
│ max_replica_delay_for_distributed_queries │ 300 │ 0 │
│ fallback_to_stale_replicas_for_distributed_queries │ 1 │ 0 │
│ distributed_ddl_allow_replicated_alter │ 0 │ 0 │
│ preferred_max_column_in_block_size_bytes │ 0 │ 0 │
│ insert_distributed_sync │ 0 │ 0 │
│ insert_distributed_timeout │ 0 │ 0 │
│ distributed_ddl_task_timeout │ 120 │ 0 │
│ experimental_allow_extended_storage_definition_syntax │ 1 │ 1 │
│ stream_flush_interval_ms │ 7500 │ 0 │
│ format_schema │ │ 0 │
│ max_rows_to_read │ 0 │ 0 │
│ max_bytes_to_read │ 0 │ 0 │
│ read_overflow_mode │ throw │ 0 │
│ max_rows_to_group_by │ 0 │ 0 │
│ group_by_overflow_mode │ throw │ 0 │
│ max_bytes_before_external_group_by │ 0 │ 0 │
│ max_rows_to_sort │ 0 │ 0 │
│ max_bytes_to_sort │ 0 │ 0 │
│ sort_overflow_mode │ throw │ 0 │
│ max_bytes_before_external_sort │ 0 │ 0 │
│ max_result_rows │ 0 │ 0 │
│ max_result_bytes │ 0 │ 0 │
│ result_overflow_mode │ throw │ 0 │
│ max_execution_time │ 0 │ 0 │
│ timeout_overflow_mode │ throw │ 0 │
│ min_execution_speed │ 0 │ 0 │
│ timeout_before_checking_execution_speed │ 0 │ 0 │
│ max_columns_to_read │ 0 │ 0 │
│ max_temporary_columns │ 0 │ 0 │
│ max_temporary_non_const_columns │ 0 │ 0 │
│ max_subquery_depth │ 100 │ 0 │
│ max_pipeline_depth │ 1000 │ 0 │
│ max_ast_depth │ 1000 │ 0 │
│ max_ast_elements │ 50000 │ 0 │
│ readonly │ 0 │ 0 │
│ max_rows_in_set │ 0 │ 0 │
│ max_bytes_in_set │ 0 │ 0 │
│ set_overflow_mode │ throw │ 0 │
│ max_rows_in_join │ 0 │ 0 │
│ max_bytes_in_join │ 0 │ 0 │
│ join_overflow_mode │ throw │ 0 │
│ max_rows_to_transfer │ 0 │ 0 │
│ max_bytes_to_transfer │ 0 │ 0 │
│ transfer_overflow_mode │ throw │ 0 │
│ max_rows_in_distinct │ 0 │ 0 │
│ max_bytes_in_distinct │ 0 │ 0 │
│ distinct_overflow_mode │ throw │ 0 │
│ max_memory_usage │ 1000000000 │ 1 │
│ max_memory_usage_for_user │ 1000000000 │ 1 │
│ max_memory_usage_for_all_queries │ 1000000000 │ 1 │
│ max_network_bandwidth │ 0 │ 0 │
│ max_network_bytes │ 0 │ 0 │
│ max_network_bandwidth_for_user │ 0 │ 0 │

:) show processlist

SHOW PROCESSLIST

Ok.

0 rows in set. Elapsed: 0.003 sec.

[vavrusa]

What you're limiting is memory allowance for queries. Best thing you can probably do is to use low granularity primary key, disable uncompressed page cache, and rebuild with libc malloc and use MALLOC_TRIM_THRESHOLD_ to force heap shrinking, but it's a bit silly.

[blinkov]

@badvir do you have any further questions?

[badvir]

@blinkov No, I don't have any, thank you.