Is there a way to avoid cancelling merges when running ALTER TABLE DROP PARTITION?

[jaumecastell]

I'm using version 24.2 (but most probably happening on all versions), and I saw that when running an ALTER TABLE

DROP PARTITION , it cancels all running merges and they start again, even for the ones that do not correspond to the partition I'm dropping.
Is there any way to avoid cancelling merges for other partitions, when performing this operation?

As an additional information, also an OPTIMIZE was running and it was cancelled:

Code: 236. DB::Exception: Received from localhost:9000. DB::Exception: Cancelled merging parts. (ABORTED)
(query: OPTIMIZE TABLE <table_name> PARTITION 20240425 FINAL)

Partition dropped was 20240423.

[davenger]

Looks like in the ordinary MergeTree there is currently no way to stop merges only in the partition that is being dropped.
In the code I see it is not implemented: https://github.com/ClickHouse/clickhouse/blob/master/src/Storages/StorageMergeTree.cpp#L1655

[jaumecastell]

Hi @davenger, thanks for your response. The main problem I have is that the OPTIMIZE is cancelled too. Do you know some workaround to avoid this?

[davenger]

@jaumecastell OPTIMIZE is essentially a merge.
From what you describe it looks like you have daily partitions, and you manually run OPTIMIZE ... FINAL on them. Would you mind sharing a bit more details on your use case: why you need to run those optimize queries, are you concerned about optimize being cancelled because it take long time to complete?

[jaumecastell]

@davenger yes, I have a table that is partitioned by day, which contains more or less 300GB each partition. I use this table to save backups of my data, so I want to maximize the compression ratio of each partition, that's why I execute OPTIMIZE FINAL before backing up the partition. Also, each day I delete the partitions that have been already backed up, and the OPTIMIZEs (merges involved) are killed. The optimizes, are taking between 12 and 20 hours to finish. For now, I tried to adjust the timings for when the optimizes and the deletes are executed, so the merges are finished before deleting the previous partition, but it would be good for the merges to not be killed if their partition is not involved.

[den-crane]

@jaumecastell you can modify tables parameters
max_bytes_to_merge_at_max_space_in_pool,
min_age_to_force_merge_seconds, min_age_to_force_merge_on_partition_only
then Clickhouse will merge it by itself and retry merge after cancellation.

[alexey-milovidov]

I highly do not recommend modifying any settings.