matrixone
matrixone copied to clipboard
matrixorigin
[Bug]: the performance of random ranges is very poor
Is there an existing issue for the same bug?
- [X] I have checked the existing issues.
Branch Name
main
Commit ID
latest
Other Environment Information
- Hardware parameters:
- OS type:
- Others:
Actual Behavior
the qps of 100w random range is 200+ in main branch while the qps is 2000+ in 1.2-dev
Expected Behavior
No response
Steps to Reproduce
daily run sysbench
Additional information
No response
sysbench --mysql-host=127.0.0.1 --mysql-port=6001 --mysql-user=dump --mysql-password=111 select_random_ranges.lua --mysql-db=sysbench_db --tables=10 --table_size=100000 --threads=100 --time=30 --report-interval=10 prepare
sysbench --mysql-host=127.0.0.1 --mysql-port=6001 --mysql-user=dump --mysql-password=111 select_random_ranges.lua --mysql-db=sysbench_db --tables=10 --table_size=100000 --threads=60 --time=300 --report-interval=10 --range_selects=off --point_selects=1 run
expr to ranges is nil
expr to reader is:
Expr_F(
Func["or"](nargs=10)
Expr_F(
Func["and"](nargs=2)
Expr_F(
Func[">="](nargs=2)
Expr_Col(sbtest1.k) Expr_Selectivity(0)
Expr_C(i32val:35020 ) Expr_Selectivity(0)
) Expr_Selectivity(0.1)
Expr_F(
Func["<="](nargs=2)
Expr_Col(sbtest1.k) Expr_Selectivity(0)
Expr_C(i32val:35025 ) Expr_Selectivity(0)
) Expr_Selectivity(0.1)
) Expr_Selectivity(0.07943282347242815)
Expr_F(
Func["and"](nargs=2)
Expr_F(
Func[">="](nargs=2)
Expr_Col(sbtest1.k) Expr_Selectivity(0)
Expr_C(i32val:35030 ) Expr_Selectivity(0)
) Expr_Selectivity(0.1)
Expr_F(
Func["<="](nargs=2)
Expr_Col(sbtest1.k) Expr_Selectivity(0)
Expr_C(i32val:35035 ) Expr_Selectivity(0)
) Expr_Selectivity(0.1)
) Expr_Selectivity(0.07943282347242815)
Expr_F(
Func["and"](nargs=2)
Expr_F(
Func[">="](nargs=2)
Expr_Col(sbtest1.k) Expr_Selectivity(0)
Expr_C(i32val:35027 ) Expr_Selectivity(0)
) Expr_Selectivity(0.1)
Expr_F(
Func["<="](nargs=2)
Expr_Col(sbtest1.k) Expr_Selectivity(0)
Expr_C(i32val:35032 ) Expr_Selectivity(0)
) Expr_Selectivity(0.1)
) Expr_Selectivity(0.07943282347242815)
Expr_F(
Func["and"](nargs=2)
Expr_F(
Func[">="](nargs=2)
Expr_Col(sbtest1.k) Expr_Selectivity(0)
Expr_C(i32val:34987 ) Expr_Selectivity(0)
) Expr_Selectivity(0.1)
Expr_F(
Func["<="](nargs=2)
Expr_Col(sbtest1.k) Expr_Selectivity(0)
Expr_C(i32val:34992 ) Expr_Selectivity(0)
) Expr_Selectivity(0.1)
) Expr_Selectivity(0.07943282347242815)
Expr_F(
Func["and"](nargs=2)
Expr_F(
Func[">="](nargs=2)
Expr_Col(sbtest1.k) Expr_Selectivity(0)
Expr_C(i32val:34997 ) Expr_Selectivity(0)
) Expr_Selectivity(0.1)
Expr_F(
Func["<="](nargs=2)
Expr_Col(sbtest1.k) Expr_Selectivity(0)
Expr_C(i32val:35002 ) Expr_Selectivity(0)
) Expr_Selectivity(0.1)
) Expr_Selectivity(0.07943282347242815)
Expr_F(
Func["and"](nargs=2)
Expr_F(
Func[">="](nargs=2)
Expr_Col(sbtest1.k) Expr_Selectivity(0)
Expr_C(i32val:35033 ) Expr_Selectivity(0)
) Expr_Selectivity(0.1)
Expr_F(
Func["<="](nargs=2)
Expr_Col(sbtest1.k) Expr_Selectivity(0)
Expr_C(i32val:35038 ) Expr_Selectivity(0)
) Expr_Selectivity(0.1)
) Expr_Selectivity(0.07943282347242815)
Expr_F(
Func["and"](nargs=2)
Expr_F(
Func[">="](nargs=2)
Expr_Col(sbtest1.k) Expr_Selectivity(0)
Expr_C(i32val:34994 ) Expr_Selectivity(0)
) Expr_Selectivity(0.1)
Expr_F(
Func["<="](nargs=2)
Expr_Col(sbtest1.k) Expr_Selectivity(0)
Expr_C(i32val:34999 ) Expr_Selectivity(0)
) Expr_Selectivity(0.1)
) Expr_Selectivity(0.07943282347242815)
Expr_F(
Func["and"](nargs=2)
Expr_F(
Func[">="](nargs=2)
Expr_Col(sbtest1.k) Expr_Selectivity(0)
Expr_C(i32val:35036 ) Expr_Selectivity(0)
) Expr_Selectivity(0.1)
Expr_F(
Func["<="](nargs=2)
Expr_Col(sbtest1.k) Expr_Selectivity(0)
Expr_C(i32val:35041 ) Expr_Selectivity(0)
) Expr_Selectivity(0.1)
) Expr_Selectivity(0.07943282347242815)
Expr_F(
Func["and"](nargs=2)
Expr_F(
Func[">="](nargs=2)
Expr_Col(sbtest1.k) Expr_Selectivity(0)
Expr_C(i32val:35416 ) Expr_Selectivity(0)
) Expr_Selectivity(0.1)
Expr_F(
Func["<="](nargs=2)
Expr_Col(sbtest1.k) Expr_Selectivity(0)
Expr_C(i32val:35421 ) Expr_Selectivity(0)
) Expr_Selectivity(0.1)
) Expr_Selectivity(0.07943282347242815)
Expr_F(
Func["and"](nargs=2)
Expr_F(
Func[">="](nargs=2)
Expr_Col(sbtest1.k) Expr_Selectivity(0)
Expr_C(i32val:34982 ) Expr_Selectivity(0)
) Expr_Selectivity(0.1)
Expr_F(
Func["<="](nargs=2)
Expr_Col(sbtest1.k) Expr_Selectivity(0)
Expr_C(i32val:34987 ) Expr_Selectivity(0)
) Expr_Selectivity(0.1)
) Expr_Selectivity(0.07943282347242815)
)Expr_Selectivity(1)
mysql> show create table sbtest1;
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| sbtest1 | CREATE TABLE `sbtest1` (
`id` INT NOT NULL AUTO_INCREMENT,
`k` INT NOT NULL DEFAULT 0,
`c` CHAR(120) NOT NULL DEFAULT '',
`pad` CHAR(60) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `k_1` (`k`)
) |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
100W 场景下，QPS 1.2 是 main 的 10 倍，原因在于 main 中 推到 ranges 的 expr 为空，所以会返回所有的 blk list，这样在 reader 层就会扫描所有的 blk.
1.2 expr:
1.2
random points debug, source: ranges, table: sbtest1, extra: %!s(int=1) expr：
Expr_F(
Func["or"](nargs=10)
Expr_F(
Func["and"](nargs=2)
Expr_F(
Func[">="](nargs=2)
Expr_Col(sbtest1.k) Expr_Selectivity(0)
Expr_F(
Func["cast"](nargs=2)
Expr_P(0) Expr_Selectivity(0)
Expr_T() Expr_Selectivity(0)
) Expr_Selectivity(0)
) Expr_Selectivity(0.1)
Expr_F(
Func["<="](nargs=2)
Expr_Col(sbtest1.k) Expr_Selectivity(0)
Expr_F(
Func["cast"](nargs=2)
Expr_P(1) Expr_Selectivity(0)
Expr_T() Expr_Selectivity(0)
) Expr_Selectivity(0)
) Expr_Selectivity(0.1)
) Expr_Selectivity(0.07943282347242815)
Expr_F(
Func["and"](nargs=2)
Expr_F(
Func[">="](nargs=2)
Expr_Col(sbtest1.k) Expr_Selectivity(0)
Expr_F(
Func["cast"](nargs=2)
Expr_P(2) Expr_Selectivity(0)
Expr_T() Expr_Selectivity(0)
) Expr_Selectivity(0)
) Expr_Selectivity(0.1)
Expr_F(
Func["<="](nargs=2)
Expr_Col(sbtest1.k) Expr_Selectivity(0)
Expr_F(
Func["cast"](nargs=2)
Expr_P(3) Expr_Selectivity(0)
Expr_T() Expr_Selectivity(0)
) Expr_Selectivity(0)
) Expr_Selectivity(0.1)
) Expr_Selectivity(0.07943282347242815)
Expr_F(
Func["and"](nargs=2)
Expr_F(
Func[">="](nargs=2)
Expr_Col(sbtest1.k) Expr_Selectivity(0)
Expr_F(
Func["cast"](nargs=2)
Expr_P(4) Expr_Selectivity(0)
Expr_T() Expr_Selectivity(0)
) Expr_Selectivity(0)
) Expr_Selectivity(0)
Expr_F(
Func["<="](nargs=2)
Expr_Col(sbtest1.k) Expr_Selectivity(0)
Expr_F(
Func["cast"](nargs=2)
Expr_P(5) Expr_Selectivity(0)
Expr_T() Expr_Selectivity(0)
) Expr_Selectivity(0)
) Expr_Selectivity(0)
) Expr_Selectivity(0)
Expr_F(
Func["and"](nargs=2)
Expr_F(
Func[">="](nargs=2)
Expr_Col(sbtest1.k) Expr_Selectivity(0)
Expr_F(
Func["cast"](nargs=2)
Expr_P(6) Expr_Selectivity(0)
Expr_T() Expr_Selectivity(0)
) Expr_Selectivity(0)
) Expr_Selectivity(0)
Expr_F(
Func["<="](nargs=2)
Expr_Col(sbtest1.k) Expr_Selectivity(0)
Expr_F(
Func["cast"](nargs=2)
Expr_P(7) Expr_Selectivity(0)
Expr_T() Expr_Selectivity(0)
) Expr_Selectivity(0)
) Expr_Selectivity(0)
) Expr_Selectivity(0)
Expr_F(
Func["and"](nargs=2)
Expr_F(
Func[">="](nargs=2)
Expr_Col(sbtest1.k) Expr_Selectivity(0)
Expr_F(
Func["cast"](nargs=2)
Expr_P(8) Expr_Selectivity(0)
Expr_T() Expr_Selectivity(0)
) Expr_Selectivity(0)
) Expr_Selectivity(0)
Expr_F(
Func["<="](nargs=2)
Expr_Col(sbtest1.k) Expr_Selectivity(0)
Expr_F(
Func["cast"](nargs=2)
Expr_P(9) Expr_Selectivity(0)
Expr_T() Expr_Selectivity(0)
) Expr_Selectivity(0)
) Expr_Selectivity(0)
) Expr_Selectivity(0)
Expr_F(
Func["and"](nargs=2)
Expr_F(
Func[">="](nargs=2)
Expr_Col(sbtest1.k) Expr_Selectivity(0)
Expr_F(
Func["cast"](nargs=2)
Expr_P(10) Expr_Selectivity(0)
Expr_T() Expr_Selectivity(0)
) Expr_Selectivity(0)
) Expr_Selectivity(0)
Expr_F(
Func["<="](nargs=2)
Expr_Col(sbtest1.k) Expr_Selectivity(0)
Expr_F(
Func["cast"](nargs=2)
Expr_P(11) Expr_Selectivity(0)
Expr_T() Expr_Selectivity(0)
) Expr_Selectivity(0)
) Expr_Selectivity(0)
) Expr_Selectivity(0)
Expr_F(
Func["and"](nargs=2)
Expr_F(
Func[">="](nargs=2)
Expr_Col(sbtest1.k) Expr_Selectivity(0)
Expr_F(
Func["cast"](nargs=2)
Expr_P(12) Expr_Selectivity(0)
Expr_T() Expr_Selectivity(0)
) Expr_Selectivity(0)
) Expr_Selectivity(0)
Expr_F(
Func["<="](nargs=2)
Expr_Col(sbtest1.k) Expr_Selectivity(0)
Expr_F(
Func["cast"](nargs=2)
Expr_P(13) Expr_Selectivity(0)
Expr_T() Expr_Selectivity(0)
) Expr_Selectivity(0)
) Expr_Selectivity(0)
) Expr_Selectivity(0)
Expr_F(
Func["and"](nargs=2)
Expr_F(
Func[">="](nargs=2)
Expr_Col(sbtest1.k) Expr_Selectivity(0)
Expr_F(
Func["cast"](nargs=2)
Expr_P(14) Expr_Selectivity(0)
Expr_T() Expr_Selectivity(0)
) Expr_Selectivity(0)
) Expr_Selectivity(0)
Expr_F(
Func["<="](nargs=2)
Expr_Col(sbtest1.k) Expr_Selectivity(0)
Expr_F(
Func["cast"](nargs=2)
Expr_P(15) Expr_Selectivity(0)
Expr_T() Expr_Selectivity(0)
) Expr_Selectivity(0)
) Expr_Selectivity(0)
) Expr_Selectivity(0)
Expr_F(
Func["and"](nargs=2)
Expr_F(
Func[">="](nargs=2)
Expr_Col(sbtest1.k) Expr_Selectivity(0)
Expr_F(
Func["cast"](nargs=2)
Expr_P(16) Expr_Selectivity(0)
Expr_T() Expr_Selectivity(0)
) Expr_Selectivity(0)
) Expr_Selectivity(0)
Expr_F(
Func["<="](nargs=2)
Expr_Col(sbtest1.k) Expr_Selectivity(0)
Expr_F(
Func["cast"](nargs=2)
Expr_P(17) Expr_Selectivity(0)
Expr_T() Expr_Selectivity(0)
) Expr_Selectivity(0)
) Expr_Selectivity(0)
) Expr_Selectivity(0)
Expr_F(
Func["and"](nargs=2)
Expr_F(
Func[">="](nargs=2)
Expr_Col(sbtest1.k) Expr_Selectivity(0)
Expr_F(
Func["cast"](nargs=2)
Expr_P(18) Expr_Selectivity(0)
Expr_T() Expr_Selectivity(0)
) Expr_Selectivity(0)
) Expr_Selectivity(0)
Expr_F(
Func["<="](nargs=2)
Expr_Col(sbtest1.k) Expr_Selectivity(0)
Expr_F(
Func["cast"](nargs=2)
Expr_P(19) Expr_Selectivity(0)
Expr_T() Expr_Selectivity(0)
) Expr_Selectivity(0)
) Expr_Selectivity(0)
) Expr_Selectivity(0)
)Expr_Selectivity(0.21805782590791353)
main expr:
random points debug: source: ranges, table: sbtest1, extra: 124, expr: nil
@aunjgr @badboynt1
应该是main比1.2分支少了一些条件的下推， 可能有部分pr没有pick到main上，导致Range函数性能下降。
我没有找到具体的commit。 麻烦检查一下这一块。
tke和单机性能都已恢复
https://grafana.ci.matrixorigin.cn/d/tTgeED4Ik01/test-nightly-tke-sysbench-100w?orgId=1
