clickhouse background merge cause memory_limit_exceeded error

[ly123-liu]

clickhouse version: 22.3.15.33
ram memory 40G

we have 2 old partition has too many parts , and cannot be merges, since merge those parts cause
MergeFromLogEntryTask MEMORY_LIMIT_EXCEEDED, i change merge settings like max_bytes_to_merge_at_max_space_in_pool seems not help

how can i manully merge this parts or limit one partition merge can slowerly not to merge all this parts in on merge , just do it in an allowable batch? can someone help?

1. clickhouse error.log
   2024.01.31 07:39:16.491564 [ 74 ] {} <Error> MergeFromLogEntryTask: virtual bool DB::ReplicatedMergeMutateTaskBase::executeStep(): Code: 241. DB::Exception: Memory limit (total) exceeded: would use 37.46 GiB (attempt to allocate chunk of 4830532 bytes), maximum: 37.46 GiB. (MEMORY_LIMIT_EXCEEDED), Stack trace (when copying this message, always include the lines below)

2. table system.merges table can see merge options
   ┌─database─┬─table────────┬──────elapsed─┬─progress─┬─num_parts─┬─rows_written─┬─columns_written─┬─memory_usage─┐ │ log │ default_log │ 34.150429372 │ 1 │ 4216 │ 0 │ 0 │ 27731064652 │ │ log │ default_log │ 33.536565067 │ 1 │ 16414 │ 0 │ 0 │ 27363748489 │

3. clickhouse config.xml
   <merge_tree> <merge_max_block_size>1024</merge_max_block_size> <max_bytes_to_merge_at_min_space_in_pool>10485760</max_bytes_to_merge_at_min_space_in_pool> <max_bytes_to_merge_at_max_space_in_pool>10737418240</max_bytes_to_merge_at_max_space_in_pool> <number_of_free_entries_in_pool_to_lower_max_size_of_merge>0</number_of_free_entries_in_pool_to_lower_max_size_of_merge> <max_replicated_merges_in_queue>32</max_replicated_merges_in_queue> </merge_tree>

[seandhaynes]

we have 2 old partition has too many parts

Could you provide a bit more information here, and tell us how many parts you have in these partitions, and how big those parts are?

Maybe something like:

SELECT
    partition,
    count(),
    formatReadableSize(min(bytes_on_disk)),
    formatReadableSize(max(bytes_on_disk))
FROM system.parts
WHERE partition IN ('partition-a', 'partition-b')
GROUP BY partition

Also, what values do you have for the below settings:

background_merges_mutations_concurrency_ratio
background_pool_size

If they are at their default values of 2 and 16 respectively, that means you can have up to 32 merges/mutations running. Given you are running ClickHouse with a relatively small amount of memory, this might be too much if you have a very large amount of merging activity.

I don't have enough information at this point to give reliable advice, but I would say look at overall memory usage on your ClickHouse replicas to see what's consuming it, try reducing the size of the background thread pool or maybe setting background_merges_mutations_concurrency_ratio to 1 temporarily.

I would also recommend running ClickHouse with more memory that 40GB, that's not much more than the minimum of 32GB that's recommended without having to fine tune background operations and things like the number of in-flight queries: https://clickhouse.com/docs/en/operations/tips#ram

[seandhaynes]

clickhouse version: 22.3.15.33

@ly123-liu one last point - I would recommend upgrading to a newer version of ClickHouse,

[ly123-liu]

1）we have two partition with parts cannot merged

┌─partition───────────┬─count()─┬─formatReadableSize(min(bytes_on_disk))─┬─formatReadableSize(max(bytes_on_disk))─┐
│ 2024-01-22 14:00:00 │   16416 │ 2.36 KiB                               │ 3.11 MiB                               │
│ 2024-01-22 16:00:00 │   13956 │ 2.35 KiB                               │ 19.23 MiB                              │

2）background_pool_size settings changed to 32, the others are default ：
background_merges_mutations_concurrency_ratio=2 （default value）
background_pool_size=32

there's another problem: insert is in a batch but merge is not faster , so we changed background_pool_size , but not fix this problem , active parts go to 180000 ,since parts too many，search is slow，zookeeper nodecount/data is growing too big，how can i fix this，thanks？

SELECT count()
FROM system.parts

Query id: 811cd0b5-787b-49a3-a656-04b9463bf6cb

┌─count()─┐
│  180632 │
└─────────┘

[seandhaynes]

That number of parts is crazy high, and can't all be attributed to merges failing because of out of memory issues. 16416 parts in one of those partitions you sent alongside 180632 parts across all partitions suggests your INSERT patterns are not good for ClickHouse at all, and you're creating far too many small parts.

Did you increase parts_to_throw_insert ? As I don't even know how INSERTs could still be processed unless you've increased this setting.

You mentioned you're batching, but it doesn't look like you're batching enough, or you have some other process that is creating too many parts on disk.

I still don't know much about your environment or settings, but I think you should review how you are inserting data into ClickHouse, and give it more resources (especially RAM) that you currently have.

One other suggestion I can make is you can check your replication queue for errors to understand more why merges might not be executing. something like:

SELECT
    last_exception,
    last_exception_time,
    num_postponed,
    postpone_reason,
    merge_type
FROM system.replication_queue
WHERE type = 'MERGE_PARTs'

Have you also checked ClickHouse logs in more detail?

[ly123-liu]

replication_queue

yes, we changed parts_to_throw_insert, normally parts is 10000, a few times can grow to 18w, and back to 1w, we haven't find the reason , is there any idea?

this is one processlist show, seems in one insert

SHOW PROCESSLIST

Query id: 02829dc9-660a-4c5f-b9bc-f1047e434b07

Row 1:
──────
is_initial_query:     1
user:                 default
query_id:             ac18852f-8901-41a0-8bc2-568bf1b89368
address:              ::ffff:172.18.34.67
port:                 54612
initial_user:         default
initial_query_id:     ac18852f-8901-41a0-8bc2-568bf1b89368
initial_address:      ::ffff:172.18.34.67
initial_port:         54612
interface:            2
os_user:              
client_hostname:      
client_name:          
client_revision:      0
client_version_major: 0
client_version_minor: 0
client_version_patch: 0
http_method:          2
http_user_agent:      Vector/0.26.0 (x86_64-unknown-linux-gnu c6b5bc2 2022-12-05)
http_referer:         
forwarded_for:        
quota_key:            
distributed_depth:    0
elapsed:              0.258129671
is_cancelled:         0
read_rows:            0
read_bytes:           0
total_rows_approx:    0
written_rows:         27702
written_bytes:        28162281
memory_usage:         96635207
peak_memory_usage:    114047456
query:                INSERT INTO "log"."default_log2_all" FORMAT JSONEachRow

thread_ids:           [9437]
ProfileEvents:        {'Query':1,'InsertQuery':1,'FileOpen':50,'WriteBufferFromFileDescriptorWrite':53,'WriteBufferFromFileDescriptorWriteBytes':4115766,'IOBufferAllocs':118,'IOBufferAllocBytes':39210730,'FunctionExecute':64,'DiskWriteElapsedMicroseconds':5140,'NetworkReceiveElapsedMicroseconds':1165,'NetworkReceiveBytes':2983342,'InsertedRows':27702,'InsertedBytes':28162281,'ZooKeeperTransactions':5,'ZooKeeperCreate':2,'ZooKeeperMulti':3,'MergeTreeDataWriterRows':6986,'MergeTreeDataWriterUncompressedBytes':7328358,'MergeTreeDataWriterCompressedBytes':1160264,'MergeTreeDataWriterBlocks':3,'ContextLock':29,'RWLockAcquiredReadLocks':4,'CannotWriteToWriteBufferDiscard':45,'QueryProfilerRuns':1}
Settings:             {'max_block_size':'16384','max_threads':'2','load_balancing':'random','max_bytes_before_external_group_by':'5000000000','max_memory_usage':'10000000000','max_memory_usage_for_user':'10000000000','max_partitions_per_insert_block':'300','max_memory_usage_for_all_queries':'10000000000','input_format_skip_unknown_fields':'1','input_format_import_nested_json':'1'}
current_database:     default

┌─database─┬─table────────┬─replica_name────────────────┬─position─┬─node_name────────┬─type────────┬─────────create_time─┬─required_quorum─┬─source_replica──────────────┬─new_part_name──────────────┬─parts_to_merge────────────────────────────────────────────┬─is_detach─┬─is_currently_executing─┬─num_tries─┬─last_exception─┬───last_attempt_time─┬─num_postponed─┬─postpone_reason─┬──last_postpone_time─┬─merge_type─┐
│ log      │ default_log2 │ log-clickhouse-f01-shard-03 │        0 │ queue-0079364300 │ MERGE_PARTS │ 2024-01-31 14:26:41 │               0 │ log-clickhouse-f01-shard-03 │ 1706371200_8951_26443_3084 │ ['1706371200_8951_26442_3083','1706371200_26443_26443_0'] │         0 │                      1 │         1 │                │ 2024-01-31 14:26:41 │             0 │                 │ 1970-01-01 00:00:00 │ REGULAR    │
│ log      │ default_log2 │ log-clickhouse-f01-shard-03 │        1 │ queue-0079364303 │ MERGE_PARTS │ 2024-01-31 14:26:43 │               0 │ log-clickhouse-f01-shard-03 │ 1706544000_5138_30941_3554 │ ['1706544000_5138_30940_3553','1706544000_30941_30941_0'] │         0 │                      1 │         1 │                │ 2024-01-31 14:26:43 │             0 │                 │ 1970-01-01 00:00:00 │ REGULAR    │

another question is, merge is oom ,seems too many parts in one merge , but max_parts_to_merge_at_once default is 100, why some merge is not follow this ?
we changed some merge settings still not work, can someone help?

<merge_tree> 
  <merge_max_block_size>1024</merge_max_block_size> 
  <max_bytes_to_merge_at_min_space_in_pool>10485760</max_bytes_to_merge_at_min_space_in_pool> 
  <max_bytes_to_merge_at_max_space_in_pool>10737418240</max_bytes_to_merge_at_max_space_in_pool> 
 
 <number_of_free_entries_in_pool_to_lower_max_size_of_merge>0</number_of_free_entries_in_pool_to_lower_max_size_of_merge> 
  <max_replicated_merges_in_queue>32</max_replicated_merges_in_queue>
 </merge_tree>

SELECT
    num_parts,
    partition_id,
    total_size_bytes_compressed,
    total_size_marks,
    bytes_read_uncompressed,
    rows_read,
    memory_usage,
    merge_type,
    merge_algorithm
FROM system.merges

Query id: c1b8a1ad-4f4d-4626-a0e1-c85c368b3642

Row 1:
──────
num_parts:                   13952
partition_id:                1705939200
total_size_bytes_compressed: 9104949816
total_size_marks:            28026
bytes_read_uncompressed:     18646230189
rows_read:                   18704646
memory_usage:                31500014639
merge_type:                  REGULAR
merge_algorithm:             Horizontal

SELECT
    last_exception,
    last_attempt_time,
    num_postponed,
    postpone_reason,
    merge_type
FROM system.replication_queue

Row 3:
──────
last_exception:    Code: 241. DB::Exception: Memory limit (total) exceeded: would use 37.46 GiB (attempt to allocate chunk of 4736385 bytes), maximum: 37.46 GiB: (while reading column message): While executing MergeTreeSequentialSource. (MEMORY_LIMIT_EXCEEDED) (version 22.3.15.33 (official build))
last_attempt_time: 2024-02-01 01:34:56
num_postponed:     0
postpone_reason:   
merge_type:        REGULAR

[azat]

@ly123-liu is it still a problem?

If so:

1）we have two partition with parts cannot merged

Please provide exact that was scheduled for a merge

Also please provide complete log for the merge process (it can be distiguished by query_id filed, it contains uuid::result_part_name format for merges)

And note, that you have error for total memory tracker, that can be affected by anything, including queries.