matrixorigin
[Bug]: Non-pk index point select and cluster by point select have significant performance gap between TKE and standalone
Is there an existing issue for the same bug?
- [X] I have checked the existing issues.
Branch Name
main
Commit ID
b9d103d8d
Other Environment Information
- Hardware parameters:
- OS type:
- Others:
Actual Behavior
针对非主键表索引和cluster by排序列点查性能做了一轮测试，测试结果发现两个场景的单机和tke性能都差距比较大
单机测试结果： https://doc.weixin.qq.com/sheet/e3_AYYAgwazACs6Q500d1CQB2CQXsmF6?scode=AJsA6gc3AA8C36swjBAYYAgwazACs&tab=BB08J2 tke测试结果：https://github.com/matrixorigin/mo-nightly-regression/actions/runs/9337845401/job/25700427273
Expected Behavior
No response
Steps to Reproduce
no-pk-index：
sysbench --mysql-host=172.16.122.64 --mysql-port=6001 --mysql-user=dump --mysql-password=111 oltp_point_select.lua --mysql-db=sysbench_db --tables=10 --table_size=100000 --threads=100 --time=30 --report-interval=10 --secondary=on --create_secondary=off --auto_inc=off prepare
sysbench --mysql-host=172.16.122.64 --mysql-port=6001 --mysql-user=dump --mysql-password=111 oltp_point_select.lua --mysql-db=sysbench_db --tables=10 --table_size=100000 --threads=100 --time=300 --report-interval=10 --range_selects=off --point_selects=1 --secondary=on --create_secondary=off --auto_inc=off run
cluster by：
sysbench --mysql-host=172.16.31.60 --mysql-port=6001 --mysql-user=dump --mysql-*** oltp_point_select.lua --mysql-db=sysbench_db --tables=10 --table_size=100000 --threads=100 --time=30 --report-interval=10 --secondary=on --create_secondary=off --auto_inc=off prepare
for i in {1..10};do mysql -h 172.16.31.60 -P6001 -udump -p111 -e "alter table sysbench_db.sbtest$i rename to sysbench_db.bak_sbtest$i;create table sysbench_db.sbtest$i(id int not null,k int default 0,c char(120) default '',pad char(60) default '')cluster by(id);insert into sysbench_db.sbtest$i select * from sysbench_db.bak_sbtest$i;drop table sysbench_db.bak_sbtest$i";done
sysbench --mysql-host=172.16.31.60 --mysql-port=6001 --mysql-user=dump --mysql-*** oltp_point_select.lua --mysql-db=sysbench_db --tables=10 --table_size=100000 --threads=100 --time=300 --report-interval=10 --range_selects=off --point_selects=1 --secondary=on --create_secondary=off --auto_inc=off run
Additional information
No response
schema:
CREATE TABLE `sbtest1` (
`id` INT NOT NULL,
`k` INT NOT NULL DEFAULT 0,
`c` CHAR(120) NOT NULL DEFAULT '',
`pad` CHAR(60) NOT NULL DEFAULT '',
KEY `xid` (`id`)
);
点查 sql
select c from sbtest1 where id = ?
这个的问题在于 no pk, index only 测试结果波动非常大，最高和最低有十倍的差距(几千到几万).
性能波动是因为，点查 expression 推到 reader 会变化，如下面两个:
{"level":"INFO","time":"2024/06/03 14:38:46.101490 +0000","caller":"blockio/read.go:157","msg":
"yyy tableName=sbtest2, len(sels)=0, hasFakePK=true, sortFunc=0x2f29460, unsortFunc=0x2f29400, sorted=false, pkPos=2, expr=
Expr_F(
Func[\"and\"](nargs=2)
Expr_F(
Func[\"in\"](nargs=2)
Expr_Col(sbtest2.__mo_fake_pk_col)
Expr_Unknown(typ:<id:201 > vec:<len:1 data:\"\\000\\034\\000\\000\\000\\010\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\001\\000\\000\\000\\010\\000\\000\\000\\356P\\001\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\" > )
)
Expr_F(
Func[\"=\"](nargs=2)
Expr_Col(sbtest2.id)
Expr_C(i32val:35406 )
)
)"}
{"level":"INFO","time":"2024/06/03 14:37:31.796151 +0000","caller":"blockio/read.go:157","msg":
"yyy tableName=sbtest2, len(sels)=0, hasFakePK=false, sortFunc=<nil>, unsortFunc=<nil>, sorted=false, pkPos=-1, expr=
Expr_F(
Func[\"=\"](nargs=2)
Expr_Col(sbtest2.id)
Expr_C(i32val:50104 )
)"}
同一张表，在同一段时间推到 reader 的 expression 不一样。前者使用了可以在 filter 过滤，只需要读很少的数据，后者会扫描所有的数据。
从 plan 看，是正常的：
mysql> explain analyze select c from sbtest2 where id = 10;
+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN |
+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| Project |
| Analyze: timeConsumed=0ms waitTime=0ms inputRows=1 outputRows=1 InputSize=143bytes OutputSize=143bytes MemorySize=0bytes |
| -> Join |
| Analyze: timeConsumed=0ms waitTime=0ms inputRows=1 outputRows=1 InputSize=8bytes OutputSize=143bytes MemorySize=0bytes |
| Join Type: INDEX |
| Join Cond: (sbtest2.__mo_fake_pk_col = __mo_index_secondary_018fe0e8-2a01-773a-868c-8bb134355964.__mo_index_pri_col) |
| Runtime Filter Build: #[-1,0] |
| -> Table Scan on sysbench_db.sbtest2 [ForceOneCN] |
| Analyze: timeConsumed=0ms waitTime=0ms inputBlocks=1 inputRows=1 outputRows=1 InputSize=155bytes OutputSize=151bytes MemorySize=157bytes |
| Filter Cond: (sbtest2.id = 10) |
| Block Filter Cond: (sbtest2.id = 10) |
| Runtime Filter Probe: sbtest2.__mo_fake_pk_col |
| -> Table Scan on sysbench_db.__mo_index_secondary_018fe0e8-2a01-773a-868c-8bb134355964 [ForceOneCN] |
| Analyze: timeConsumed=0ms waitTime=0ms inputBlocks=1 inputRows=1 outputRows=1 InputSize=32bytes OutputSize=8bytes MemorySize=33bytes |
| Filter Cond: prefix_eq(__mo_index_secondary_018fe0e8-2a01-773a-868c-8bb134355964.__mo_index_idx_col, ':
') |
| Block Filter Cond: prefix_eq(__mo_index_secondary_018fe0e8-2a01-773a-868c-8bb134355964.__mo_index_idx_col, ':
') |
+--------------------------------------------------------------------------------------------------------------------------------------------------------+
16 rows in set (0.00 sec)
@aunjgr 看看是不是运行过程中，分发过程中 plan 是不是被修改了。看起来是有时用了索引，有时没有
https://grafana.ci.matrixorigin.cn/explore?panes=%7B%224XE%22:%7B%22datasource%22:%22loki%22,%22queries%22:%5B%7B%22refId%22:%22A%22,%22expr%22:%22%7Bnamespace%3D%5C%22mo-checkin-regression-9351563024%5C%22,%20pod%3D%5C%22mo-checkin-regression-tp-cn-rd687%5C%22%7D%20%7C~%20%60yyy%20tableName%3Dsbtest%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:%22now-12h%22,%22to%22:%22now%22%7D%7D%7D&schemaVersion=1&orgId=1
