查询数据响应时间长

[群通知]

【 使用环境 】测试环境
【 OB or 其他组件 】
【 使用版本 】4.3.0.1
【问题描述】
select
pr.ptask_result_id,
pr.ptaskid,
pr.pole_id,
pr.lineid,
pr.station,
pr.segment,
pr.pole_num,
pr.mileage,
pr.cameraid,
pr.imgid,
pr.dfspath,
pr.filename,
pr.path,
pr.width,
pr.height,
pr.ptask_resultcol,
pr.recogstate,
pr.markstate,
pr.smartstate,
pr.dealdate,
pr.seen_state
from
ptask_result_47bb0343bbaa40d8bc0234d68ec1c063 pr
where
1 = 1;
查询响应时间慢于mysql、表内数据总数573350条

下面是表结构
CREATE TABLE ptask_result_47bb0343bbaa40d8bc0234d68ec1c063 (
ptask_result_id int(11) unsigned NOT NULL AUTO_INCREMENT,
ptaskid varchar(32) DEFAULT NULL,
pole_id int(11) DEFAULT NULL,
lineid varchar(32) NOT NULL,
station varchar(100) DEFAULT NULL,
segment varchar(100) DEFAULT NULL,
pole_num varchar(100) DEFAULT NULL,
mileage varchar(20) DEFAULT NULL,
cameraid varchar(32) NOT NULL,
imgid varchar(100) NOT NULL,
dfspath varchar(128) DEFAULT NULL,
filename varchar(45) DEFAULT ‘’,
path varchar(256) DEFAULT ‘’,
width smallint(6) DEFAULT NULL,
height smallint(6) DEFAULT NULL,
historyFlag smallint(6) DEFAULT ‘0’,
ptask_resultcol varchar(45) DEFAULT ‘0’,
recogstate smallint(6) DEFAULT NULL,
markstate smallint(6) DEFAULT ‘0’,
smartstate smallint(6) DEFAULT ‘0’,
_smartstate smallint(6) DEFAULT NULL,
dealdate datetime DEFAULT CURRENT_TIMESTAMP,
historyDate datetime DEFAULT CURRENT_TIMESTAMP,
seen_state smallint(6) DEFAULT ‘0’,
filepre varchar(255) DEFAULT ‘’,
update_by varchar(64) DEFAULT NULL COMMENT ‘更新者’,
update_date datetime DEFAULT NULL COMMENT ‘更新时间’,
PRIMARY KEY (ptask_result_id),
KEY ptaskid_pole_id (ptaskid, pole_id) BLOCK_SIZE 16384 LOCAL
) AUTO_INCREMENT = 1000001 AUTO_INCREMENT_MODE = ‘ORDER’ DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = ‘zstd_1.3.8’ REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0$$

【复现路径】问题出现前后相关操作
【附件及日志】数据在附件

【SOP系列 22 】——故障诊断第一步(自助诊断和诊断信息收集)

[群通知]

obclient [mhsb]> explain select
→ pr.ptask_result_id,
→ pr.ptaskid,
→ pr.pole_id,
→ pr.lineid,
→ pr.station,
→ pr.segment,
→ pr.pole_num,
→ pr.mileage,
→ pr.cameraid,
→ pr.imgid,
pr.dfs → pr.dfspath,
→ pr.filename,
→ pr.path,
→ pr.width,
→ pr.height,
→ pr.ptask_resultcol,
→ pr.recogstate,
→ pr.markstate,
→ pr.smartstate,
→ pr.dealdate,
→ pr.seen_state
→ from
→ ptask_result_47bb0343bbaa40d8bc0234d68ec1c063 pr
→ where
→ 1 = 1;
±----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
±----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| =============================================== |
| |ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)| |
| ----------------------------------------------- |
| |0 |TABLE FULL SCAN|pr |573350 |309658 | |
| =============================================== |

Outputs & filters:
0 - output([pr.ptask_result_id], [pr.ptaskid], [pr.pole_id], [pr.lineid], [pr.station], [pr.segment], [pr.pole_num], [pr.mileage], [pr.cameraid], [pr.imgid],
[pr.dfspath], [pr.filename], [pr.path], [pr.width], [pr.height], [pr.ptask_resultcol], [pr.recogstate], [pr.markstate], [pr.smartstate], [pr.dealdate],
[pr.seen_state]), filter(nil), rowset=256
access([pr.ptask_result_id], [pr.ptaskid], [pr.pole_id], [pr.lineid], [pr.station], [pr.segment], [pr.pole_num], [pr.mileage], [pr.cameraid], [pr.imgid],
[pr.dfspath], [pr.filename], [pr.path], [pr.width], [pr.height], [pr.ptask_resultcol], [pr.recogstate], [pr.markstate], [pr.smartstate], [pr.dealdate],
[pr.seen_state]), partitions(p0)
is_index_back=false, is_global_index=false,
range_key([pr.ptask_result_id]), range(MIN ; MAX)always true

±----------------------------------------------------------------------------------------------------------------------------------------------------------------+
15 rows in set (0.008 sec)

[玉楼]

计划上看去没啥问题，加个并发看看。

select /*+ parallel(16) */
pr.ptask_result_id,
pr.ptaskid,
pr.pole_id,
pr.lineid,
pr.station,
pr.segment,
pr.pole_num,
pr.mileage,
pr.cameraid,
pr.imgid,
pr.dfspath,
pr.filename,
pr.path,
pr.width,
pr.height,
pr.ptask_resultcol,
pr.recogstate,
pr.markstate,
pr.smartstate,
pr.dealdate,
pr.seen_state
from
ptask_result_47bb0343bbaa40d8bc0234d68ec1c063 pr
where
1 = 1;

这里/*+ parallel(16) */表示并发16，这个数字具体是多少要根据你的机器cpu数目进行修改

[秃蛙]

租户资源是什么样的，是新建的业务租户吗。
业务sql是全表扫描的，可以加并发看下性能。
select /*+ PARALLEL(16) */
pr.ptask_result_id,
pr.ptaskid,
pr.pole_id,
pr.lineid,
pr.station,
pr.segment,
pr.pole_num,
pr.mileage,
pr.cameraid,
pr.imgid,
pr.dfspath,
pr.filename,
pr.path,
pr.width,
pr.height,
pr.ptask_resultcol,
pr.recogstate,
pr.markstate,
pr.smartstate,
pr.dealdate,
pr.seen_state
from
ptask_result_47bb0343bbaa40d8bc0234d68ec1c063 pr
where
1 = 1;

[群通知]

根据你的设置了16、执行结束了、573350 rows in set (0.953 sec)

[靖顺]

用obdiag收集一下plan_monitor报告,发出报告来看看，OceanBase分布式数据库-海量数据 笔笔算数

[群通知]

• CPU（核）：12
• 内存（GB）：20
  是的我创建了一个新的租户
  这是执行结果573350 rows in set (0.953 sec)

[群通知]

gather_pack_20240530191821.zip (104.4 KB)

[玉楼]

看你的截图原来是2秒多，提升了100%+了。MySQL是多少呢

[群通知]

mysql在0.3左右

[玉楼]

在业务租户下执行 ALTER SYSTEM MAJOR FREEZE; 然后等待合并完成。完成后在试下执行SQL，可以调整并发度到12或者24看看

[群通知]

使用的租户是sys创建业务租户之后正常