bug(batch): distributed query holds hummock version/snapshot for long time

[zwang28]

Describe the bug

We observer both min version and min snapshot being stuck for days, co-occurrence with distributed_running_query_num change. Is it just very slow query, or something wrong with the execution?

To Reproduce

No response

Expected behavior

No response

Additional context

No response

[zwang28]

Here is another case where the min pinned epoch resumes from "stuck" state at the moment the distributed_running_query_num goes down. However, the min pinned version doesn't resume.

Then the min pinned epoch "stuck" again (for 1+ days before cluster is killed) when distributed_running_query_num goes up.
The frontend didn't restart throughout the period.

[zwang28]

We do observe potential batch task leaked in compute node: the running task num is not zero in compute node, while the query num is zero according to frontend. (But we cannot be sure yet #9841)

[liurenjie1024]

Yes, there is potential task leak, this may cause pinning of hummock version?

[zwang28]

this may cause pinning of hummock version?

Discussed offline. #9848

[zwang28]

#9953

[zwang28]

We encounter another unexpected pinned version by compute node:

1. A long running batch query held hummock version. That's expected.
   

2. We restarted frontend, which subsequently finished batch heartbeat workers in compute nodes. That's expected.

3. However, although cancel_task had been called by heartbeat workers, the compute node still held the hummock version. That's unexpected.

Worker 144004 type COMPUTE_NODE min_pinned_version_id 5592335
Worker 145005 type COMPUTE_NODE min_pinned_version_id 5592660

@liurenjie1024 @ZENOTME Let's dig into this.

[liurenjie1024]

So this means that SeqScanExecutor not dropped? You can add some logs in it to see if it's dropped.

[liurenjie1024]

Did some investigation with no clue. Add #13589 to make the metrics to be more accurate to see if it's really caused by mpp task.

[liurenjie1024]

Some more hints:

1. The metrics shows that there is a long running mpp query, but it can't be detected by show process list (they have 3 fe nodes), I think this maybe possible
2. The query lasted for 5 hours

[zwang28]

No occurrence recently after introducing statement_timeout.