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.