[Bug]: concurrency performance of full-text index is poor.

[Ariznawlll]

Is there an existing issue for the same bug?

• I have checked the existing issues.

Branch Name

main

Commit ID

f7093cc

Other Environment Information

- Hardware parameters:
- OS type:
- Others:
machine: 64c 256G(129)

Actual Behavior

结论：1. 以下测试，并发50 qps达到极限；测试期间mem使用率低于50%.

数据集：200
M，4579025行
测试时长：5min

50并发memory: https://shanghai.idc.matrixorigin.cn:30001/d/rYdddlPWk/node-exporter-full?orgId=1&var-datasource=prometheus-standalone&var-job=agents&var-node=10.222.1.129:9100&var-diskdevices=%5Ba-z%5D%2B%7Cnvme%5B0-9%5D%2Bn%5B0-9%5D%2B%7Cmmcblk%5B0-9%5D%2B&from=1736924899000&to=1736925199000

50并发memory: https://shanghai.idc.matrixorigin.cn:30001/d/rYdddlPWk/node-exporter-full?orgId=1&var-datasource=prometheus-standalone&var-job=agents&var-node=10.222.1.129:9100&var-diskdevices=%5Ba-z%5D%2B%7Cnvme%5B0-9%5D%2Bn%5B0-9%5D%2B%7Cmmcblk%5B0-9%5D%2B&from=1736927368000&to=1736927668000

数据集：1.16G，24202046行
测试时长：5min

50并发memory: https://shanghai.idc.matrixorigin.cn:30001/d/rYdddlPWk/node-exporter-full?orgId=1&var-datasource=prometheus-standalone&var-job=agents&var-node=10.222.1.129:9100&var-diskdevices=%5Ba-z%5D%2B%7Cnvme%5B0-9%5D%2Bn%5B0-9%5D%2B%7Cmmcblk%5B0-9%5D%2B&from=1736930233000&to=1736930533000

测试步骤：
steps:
git clone https://github.com/matrixorigin/mo-load.git
cd mo-load
mkdir fulltext
cd fulltext

vi case.yml
name: "fulltext_test"
runMins: 5
runTimes: 0
transaction:
  - name: "fulltext_test"
    terminal: 10
    scripts:
      - sql: "SELECT col3 from test.fulltext_test where match(col3) against ('+{str1}');"

vi replace.yml
replace:
  - name: str1
    type: file
    path: /root/weilu/mo-load/replace/output_test.txt
    dtype: "varchar"

单索引列单关键词检索(200M)：
mysql -h 127.0.0.1 -P 6001 -udump -p111 --local-infile
set experimental_fulltext_index=1;
create database test;
use test;
CREATE TABLE `fulltext_test` (
  `col1` bigint DEFAULT NULL,
  `col2` int NOT NULL,
  `col3` varchar(200) DEFAULT NULL,
  `col4` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`col2`),
 FULLTEXT(`col3`) WITH PARSER ngram
);
load data local infile '/Users/ariznawl/Downloads/zhwiki-20241201-pages-articles-multistream-index.txt' into table test.fulltext_test fields terminated by ':' ESCAPED BY '\t' lines terminated by '\n';


双索引列单关键词检索(200M)：
mysql -h 127.0.0.1 -P 6001 -udump -p111 --local-infile
set experimental_fulltext_index=1;
create database test;
use test;
CREATE TABLE `fulltext_test` (
  `col1` bigint DEFAULT NULL,
  `col2` int NOT NULL,
  `col3` varchar(200) DEFAULT NULL,
  `col4` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`col2`),
 FULLTEXT(`col3`,`col4`) WITH PARSER ngram
);
load data local infile '/Users/ariznawl/Downloads/zhwiki-20241201-pages-articles-multistream-index.txt' into table test.fulltext_test fields terminated by ':' ESCAPED BY '\t' lines terminated by '\n';

单索引列单关键词检索(1.16G)：
mysql -h 127.0.0.1 -P 6001 -udump -p111 --local-infile
set experimental_fulltext_index=1;
create database test;
use test;
CREATE TABLE `fulltext_test` (
  `col1` bigint DEFAULT NULL,
  `col2` int NOT NULL,
  `col3` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`col2`),
 FULLTEXT(`col3`) WITH PARSER ngram
);
load data local infile '/Users/ariznawl/Downloads/enwiki-20241201-pages-articles-multistream-index.txt' into table test.fulltext_test fields terminated by ':' ESCAPED BY '\t' lines terminated by '\n';

cd mo-load
./run.sh -c cases/fulltext -b test -t 10/50/100/500/1000

Expected Behavior

No response

Steps to Reproduce

detailed in actual behavior

注：boolean mode下全文索引并发性能测试，对标数据库ES并发性能测试进行中...

Additional information

No response