[Bug]: [big data test]delete from a 100million table takes a long time to execute.

Ariznawlll commented 1 week ago

Is there an existing issue for the same bug?

[X] I have checked the existing issues.

Branch Name

main

Commit ID

9f1c2dfa908bc08f58cbf59dcb14b4f3bdd16a1c

Other Environment Information

- Hardware parameters:
- OS type:
- Others:

Actual Behavior

job url: https://qgithub.com/matrixorigin/mo-nightly-regression/actions/runs/9640573308/job/26607815617

执行了大约5h还没执行完，因为设置的时间是5h，所以异常退出了。

log：https://grafana.ci.matrixorigin.cn/explore?panes=%7B%22lDW%22:%7B%22datasource%22:%22loki%22,%22queries%22:%5B%7B%22refId%22:%22A%22,%22expr%22:%22%7Bnamespace%3D%5C%22mo-big-data-20240624%5C%22%7D%20%7C%3D%20%60%60%22,%22queryType%22:%22range%22,%22datasource%22:%7B%22type%22:%22loki%22,%22uid%22:%22loki%22%7D,%22editorMode%22:%22builder%22%7D%5D,%22range%22:%7B%22from%22:%221719210171000%22,%22to%22:%221719293841000%22%7D%7D%7D&schemaVersion=1&orgId=1

对应时间点的profile： https://grafana.ci.matrixorigin.cn/explore?panes=%7B%22cg0%22:%7B%22datasource%22:%22pyroscope%22,%22queries%22:%5B%7B%22groupBy%22:%5B%5D,%22labelSelector%22:%22%7Bnamespace%3D%5C%22mo-big-data-20240624%5C%22%7D%22,%22queryType%22:%22both%22,%22refId%22:%22A%22,%22profileTypeId%22:%22memory:inuse_space:bytes:space:bytes%22,%22datasource%22:%7B%22type%22:%22grafana-pyroscope-datasource%22,%22uid%22:%22pyroscope%22%7D%7D%5D,%22range%22:%7B%22from%22:%221719243802000%22,%22to%22:%221719261279000%22%7D%7D%7D&schemaVersion=1&orgId=1

Expected Behavior

No response

Steps to Reproduce

trigger big-data-test on tke.

测试场景（数据量1亿）：
table schema：

create table if not exists big_data_test.table_with_pk_index_for_write_100M( id bigint primary key, col1 tinyint, col2 smallint, col3 int, col4 bigint, col5 tinyint unsigned, col6 smallint unsigned, col7 int unsigned, col8 bigint unsigned, col9 float, col10 double, col11 varchar(255), col12 Date, col13 DateTime, col14 timestamp, col15 bool, col16 decimal(16,6), col17 text, col18 json, col19 blob, col20 binary(255), col21 varbinary(255), col22 vecf32(3), col23 vecf32(3), col24 vecf64(3), col25 vecf64(3), key(col3), unique key(col4) );

delete from big_data_test.table_with_pk_index_for_write_100M where id <= 1000000