[Bug]: update_index/delete/write_only/read_write will cause mo out of memory on table with index and 1000w basic data

[aressu1985]

Is there an existing issue for the same bug?

• [X] I have checked the existing issues.

Branch Name

main

Commit ID

32a05ab

Other Environment Information

- Hardware parameters:64C 256G
- OS type:
- Others:

Actual Behavior

on standlone mode, update_index/delete/write_only/read_write will cause mo out of memory on table with index and 1000w basic data

prof and heap: update_index_1000w.tar.gz

Expected Behavior

No response

Steps to Reproduce

1. run a mo standlone mode,and create two databases:sysbench_1000w_no_auto_no_index,sysbench_1000w_no_auto_index
2. git clone https://github.com/matrixorigin/mo-sysbench.git
3. prepare data:
sysbench --mysql-host=127.0.0.1 --mysql-port=6001 --mysql-user=dump --mysql-password='111' oltp_common.lua --mysql-db=sysbench_1000w_no_auto_index  --tables=10 cleanup
sysbench --mysql-host=127.0.0.1 --mysql-port=6001 --mysql-user=dump --mysql-password='111' oltp_common.lua --mysql-db=sysbench_1000w_no_auto_index --auto_inc=off --tables=10 --table_size=1000000 --threads=10  prepare

sysbench --mysql-host=127.0.0.1 --mysql-port=6001 --mysql-user=dump --mysql-password='111' oltp_common.lua --mysql-db=sysbench_1000w_no_auto_no_index --create_secondary=off  --tables=10 cleanup
sysbench --mysql-host=127.0.0.1 --mysql-port=6001 --mysql-user=dump --mysql-password='111' oltp_common.lua --mysql-db=sysbench_1000w_no_auto_no_index --auto_inc=off --create_secondary=off --tables=10 --table_size=1000000 --threads=10  prepare

4.run test
[no index]
sysbench  --mysql-host=127.0.0.1 --mysql-port=6001 --mysql-user=dump --mysql-password=111 --db-ps-mode=disable oltp_update_index.lua --mysql-db=sysbench_1000w_no_auto_no_index --tables=10 --table_size=10000000 --time=60 --report-interval=2 --threads=100  run

sysbench  --mysql-host=127.0.0.1 --mysql-port=6001 --mysql-user=dump --mysql-password=111 --db-ps-mode=disable oltp_write_only.lua --mysql-db=sysbench_1000w_no_auto_no_index --tables=10 --table_size=10000000  --time=60 --report-interval=2 --skip_trx=off --threads=100 run

sysbench  --mysql-host=127.0.0.1 --mysql-port=6001 --mysql-user=dump --mysql-password=111 --db-ps-mode=disable oltp_read_write.lua --mysql-db=sysbench_1000w_no_auto_no_index --tables=10 --table_size=10000000  --time=60 --report-interval=2 --threads=100 run

sysbench  --mysql-host=127.0.0.1 --mysql-port=6001 --mysql-user=dump --mysql-password=111 --db-ps-mode=disable oltp_delete.lua --mysql-db=sysbench_1000w_no_auto_no_index --tables=10 --table_size=10000000  --time=60 --report-interval=2 --threads=100 run

[with index]
sysbench  --mysql-host=127.0.0.1 --mysql-port=6001 --mysql-user=dump --mysql-password=111 --db-ps-mode=disable oltp_update_index.lua --mysql-db=sysbench_1000w_no_auto_index --tables=10 --table_size=10000000 --time=60 --report-interval=2 --threads=100  run 

sysbench  --mysql-host=127.0.0.1 --mysql-port=6001 --mysql-user=dump --mysql-password=111 --db-ps-mode=disable oltp_write_only.lua --mysql-db=sysbench_1000w_no_auto_index --tables=10 --table_size=10000000  --time=60 --report-interval=2 --skip_trx=off --threads=100 run

sysbench  --mysql-host=127.0.0.1 --mysql-port=6001 --mysql-user=dump --mysql-password=111 --db-ps-mode=disable oltp_read_write.lua --mysql-db=sysbench_1000w_no_auto_index --tables=10 --table_size=10000000  --time=60 --report-interval=2 --threads=100 run

sysbench  --mysql-host=127.0.0.1 --mysql-port=6001 --mysql-user=dump --mysql-password=111 --db-ps-mode=disable oltp_delete.lua --mysql-db=sysbench_1000w_no_auto_index --tables=10 --table_size=10000000  --time=60 --report-interval=2 --threads=100 run

Additional information

No response

[aronchanisme]

Seems like a storage issue, could you please kindly take a look? Thanks a lot. @XuPeng-SH

[XuPeng-SH]

Prepare 100w data and explain analyze update on the secondary index column k

> explain analyze UPDATE sbtest2 SET k=k+1 WHERE id=497892;

It will always scan all the table data on the secondary index table.

[XuPeng-SH]

As talked with @aunjgr, we need to optimize with runtime filter

[aunjgr]

在解决中移物联的issue

[aunjgr]

需要手动交换join左右，以及添加runtime filter

[aunjgr]

not working on it today

[aunjgr]

not working on it today

[aunjgr]

已被 #14856 修复，且与 matrixorigin/MO-Cloud#2603 重复

[aressu1985]

testing

[heni02]

confirm,closed commit:47b7ee82f41fa63573e8025bc947e9d7c479129b job:https://github.com/matrixorigin/mo-nightly-regression/actions/runs/8524292804/job/23348752769