[Bug]: 'update table' executed about 3 hours in big data test.

Ariznawlll commented 2 months ago

Is there an existing issue for the same bug?

[X] I have checked the existing issues.

Branch Name

main

Commit ID

8327a3c69cec9b234c11b368b8073e24eccc2da2

Other Environment Information

- Hardware parameters:
- OS type:
- Others:

Actual Behavior

表规模：10亿

job url: https://github.com/matrixorigin/mo-nightly-regression/actions/runs/8645071155/job/23701509967 (Quries 10Y -> write 10y test)

log: https://grafana.ci.matrixorigin.cn/explore?panes=%7B%22B_D%22:%7B%22datasource%22:%22loki%22,%22queries%22:%5B%7B%22refId%22:%22A%22,%22expr%22:%22%7Bnamespace%3D%5C%22mo-big-data-20240411%5C%22%7D%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:%221712881214000%22,%22to%22:%221712892014000%22%7D%7D%7D&schemaVersion=1&orgId=1

Expected Behavior

No response

Steps to Reproduce

表schema:
create table if not exists big_data_test.table_with_pk_index_for_write_1B( 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));

load data url s3option {'endpoint'='http://cos.ap-guangzhou.myqcloud.com','access_key_id'='***','secret_access_key'='***','bucket'='mo-load-guangzhou-1308875761', 'filepath'='mo-big-data/1000000000_20_columns_load_data_pk.csv'} into table big_data_test.table_with_pk_index_for_write_1B fields terminated by '|' lines terminated by '\n' ignore 1 lines parallel 'true';

ddl：update big_data_test.table_with_pk_index_for_write_1B set col3 = col3 - 1

Additional information

No response

ouyuanning commented 2 months ago

辛苦锦赛先帮分析下 1、跑个profile。看一下卡在哪里。（如果10亿太多就先跑1亿） 2、如果没有unique key/primary key，看是否会更快 3、如果没有那么多列（比如：只保留： id bigint primary key, col1 tinyint, col2 smallint, col3 int)，是否会更快

jensenojs commented 2 months ago

not working on it

jensenojs commented 2 months ago

处理其他s-1

jensenojs commented 2 months ago

处理其他s-1

jensenojs commented 2 months ago

not working on it