Occasional very slow distributed DDL queries

[njcstreet]

Hi,

We have a two node ClickHouse setup running ClickHouse version 24.10.1.2812. We have a cluster across these two nodes with one partition and two replicas. The servers are large, dedicated 64 core boxes, and we have three separate machines running ClickHouse keeper with the same version number.

Within the cluster we have a table with the ReplicatedMergeTree engine, and this table is partitioned by month using a function toYYYYMM(dateField) - let's call the table reporting_data. There is no distributed table on top, we have a load balancer that distributes queries across the two tables.

In order to load data seamlessly and without impacting users, we create a temporary table with the same schema, let's call it reporting_data_staging. If we need to re-load the last 6 months data for example we:

1. Create a table reporting_data_staging with the same schema under ReplicatedMergeTree engine
2. Insert the data into one of the nodes
3. Call "SYSTEM SYNC REPLICA ON CLUSTER cluster_1S_2R reporting_data_staging" to ensure that the data is completely loaded across the two replicas (since we don't have insert_quorum set).
4. Issue a query to merge the partitions form the staging table into the main table with one statement for each month of the format:  ALTER TABLE reporting_data ON CLUSTER 1S_2R REPLACE PARTITION 'YYYYMM' FROM reporting_data_staging.
5. Finally, we will drop the stating table on the cluster.

The issue we are facing is that normally these DDL statements to replace the partitions on the cluster normally take between 1 and 10 seconds, but sometimes they are taking much longer - up to 10 minutes in some cases. During these cases there seems to be nothing else going on (no other data being loaded or user queries). Would you have any idea why we might be seeing this or anything specific in the logs we could investigate to try and understanding what is happening? It seems to be related to the cluster as we have another single node setup (no cluster) and we never see this issue.

[njcstreet]

Hi, we are wondering if possibly the issue is between (4) and (5), and that we need to call SYSTEM SYNC REPLICA to ensure that all of the REPLACE PARTITION DDL operations have completed before we drop the staging table. Is it possible that the call to ALTER TABLE ... ON CLUSTER REPLACE PARTITION could still be executing even though the query has returned to the Java client? I know it is the case for INSERT statements which is why we call SYSTEM SYNC REPLICA at (3)