Modifying TTL causes distributed DDL to block for a long time

[IVitamin-C]

hi 21.8 lts ，I want to modify the ttl of the table,I executed distributed DDL:

ALTER TABLE database.table MODIFY TTL (day_ + toIntervalDay(31)) + toIntervalHour(7), (day_ + toIntervalDay(14)) + toIntervalHour(7) TO VOLUME 'cold'.

I found that the distributed ddl will block 1 to several nodes for more than 10 hours. If you don't kill it manually, it will block for a longer time. But in query_log, this sql fails before execution.

• Which ClickHouse server version to use 21.8.15.1
• Which interface to use, if matters clickhouse-client 21.8.15.1

[genzgd]

I would consider upgrading to a later version. Some mutation locking behavior was improved by these two PRs: https://github.com/ClickHouse/ClickHouse/pull/27931 https://github.com/ClickHouse/ClickHouse/pull/31010

These have no been backported to 21.8

[den-crane]

materialize_ttl_after_modify = 0

--materialize_ttl_after_modify arg                                   Apply TTL for old data, after ALTER MODIFY TTL query

[IVitamin-C]

But I want to delete expired data automatically after modifying TTL. I recommend merging this change into 21.8 lts,one of the reasons for modifying TTL is to reduce the data retention period and delete data.

[tavplubix]

Probably it's because distributed DDL queries are executed one by one to avoid reordering (and issues that may be caused by reordering). It's highly not recommended to run such queries in synchronous mode, because all subsequent queries will wait for the previous one to finish. Consider using the following settings for distributed DDL queries:

SET distributed_ddl_entry_format_version=2;
SET mutations_sync=0;
SET replication_alter_partitions_sync=0;
SET database_atomic_wait_for_drop_and_detach_synchronously=0;

[tavplubix]

This issue is slightly related to #23513