救救孩子吧,我这个SQL还有优化的空间不?Slow Query Detail中的一个SQL语句 rpc_time时间格外长是为何?

为提高效率,请提供以下信息,问题描述清晰能够更快得到解决:
【 TiDB 使用环境】
v5.2.1

【概述】 场景 + 问题概述
一、SQL语句如下,具体可以看截图:
SELECT
DISTINCT d.triggerid_down,
d.triggerid_up
FROM
trigger_depends d,
triggers t,
HOSTS h,
items i,
functions f
WHERE
t.triggerid = d.triggerid_down
AND t.flags <> 2
AND h.hostid = i.hostid
AND i.itemid = f.itemid
AND f.triggerid = d.triggerid_down
AND h.status IN (0, 1);
这些SQL语句涉及的表都很小,只有一个大于30M,其他的都小于8M。

二、慢查询的一些信息展示不懂,还望解释下:
time:622.8ms, loops:25, cop_task: {num: 9, max: 213.6ms, min: 16ms, avg: 68.8ms, p95: 213.6ms, max_proc_keys: 3212, p95_proc_keys: 3212, tot_proc: 596ms, tot_wait: 1ms, rpc_num: 9, rpc_time: 619.3ms, copr_cache_hit_ratio: 0.00}
这里的loops、cop_task啥意思啊。
cop_task里的num、tot_proc、totwait、rpc_num、rpc_time啥意思啊

三、这个rpc_time时间长可能是什么导致的?SQL慢查询,显示SQL语句 rpc_time时间长
【背景】 做过哪些操作

【现象】 业务和数据库现象

CREATE TABLE functions (
functionid bigint(20) unsigned NOT NULL,
itemid bigint(20) unsigned NOT NULL,
triggerid bigint(20) unsigned NOT NULL,
name varchar(12) NOT NULL DEFAULT ‘’,
parameter varchar(255) NOT NULL DEFAULT ‘0’,
PRIMARY KEY (functionid) /*T![clustered_index] CLUSTERED */,
KEY functions_1 (triggerid),
KEY functions_2 (itemid,name,parameter),
CONSTRAINT c_functions_1 FOREIGN KEY (itemid) REFERENCES items (itemid) ON DELETE CASCADE,
CONSTRAINT c_functions_2 FOREIGN KEY (triggerid) REFERENCES triggers (triggerid) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE trigger_depends (
triggerdepid bigint(20) unsigned NOT NULL,
triggerid_down bigint(20) unsigned NOT NULL,
triggerid_up bigint(20) unsigned NOT NULL,
PRIMARY KEY (triggerdepid) /*T![clustered_index] CLUSTERED */,
UNIQUE KEY trigger_depends_1 (triggerid_down,triggerid_up),
KEY trigger_depends_2 (triggerid_up),
CONSTRAINT c_trigger_depends_1 FOREIGN KEY (triggerid_down) REFERENCES triggers (triggerid) ON DELETE CASCADE,
CONSTRAINT c_trigger_depends_2 FOREIGN KEY (triggerid_up) REFERENCES triggers (triggerid) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
【业务影响】


3赞

rpc_num , rpc_time :向 TiKV 发送 Cop 类型的 RPC 请求总数量和总时间

5赞

各位大佬们。我这个语句没有可以优化的空间了吗?都是些小表,region_id就2个,一个数据的,一个索引的。

3赞

https://docs.pingcap.com/zh/tidb/stable/tidb-configuration-file#capacity-mb
老哥,试试这个参数,把IndexRangeScan_38这个缓存下。

[tikv-client.copr-cache]
capacity-mb=1000.0
admission-max-ranges=20000 #默认500,最大的缓存range,key的数目超过这个数就不缓存了
admission-max-result-mb=5 # 默认10兆,最大的size,超过这个大小就不缓存了
admission-min-process-ms=0 # 默认是5毫秒

调完后,explain analyze 上面的sql两次 ,然后把两次的执行计划贴上来。一定要2次,第一次未必缓存,如果第二次还没缓存,那就是参数调的不合适,再调。

3赞

修改这个需要重启tidb,重启tidb会导致现有tidb节点的链接断开重连,线上操作要慎重。

2赞

还缺少两个表结构吧,老铁

2赞

不知道这种子查询的方式会不会更快一点

SELECT DISTINCT 
	d.triggerid_down,d.triggerid_up
FROM trigger_depends d
WHERE d.triggerid_down IN (
	SELECT triggerid FROM triggers t 
	WHERE t.flags <> 2
)
AND d.triggerid_down IN (
	SELECT triggerid FROM functions f
	WHERE f.itemid IN (
		SELECT itemid FROM items i
		WHERE i.hostid IN (
			SELECT hostid FROM HOSTS h
			WHERE h.status IN (0, 1)
		)
	)
)
3赞

这几个参数 我试过了,已经调整的很大了。没什么效果。

2赞

因为这个查询是zabbix本身的查询,涉及的表都是些几兆的小表,只有一个region_id,算上索引,也就俩,一个data region_id,一个index region_id。这个SQL一分钟执行一次,根本算不到读热点里去。

试过手动split 将仅有的几MB数据,仅有的那点索引分成了对应tikv实例数个region(参照官方有split的SQL语句 https://docs.pingcap.com/zh/tidb/stable/sql-statement-split-region)能起到加速的效果,但是时间一长pd会自动将你split切分的多个region重新合并成一个。

重新将该SQL涉及的全部小表,都执行一遍ANALYZE,就OK了。
ANALYZE之后就变成这样了:

2赞

感觉好像你这么写,比zabbix原生的好。这是EXPLAIN ANALYZE:

2赞

SQL语句确实按照你写的执行,我测试了很多遍。你的要比zabbix官方的快一倍左右。强!

3赞

:handshake::handshake::handshake:

果然还是要靠提升健康度来优化~

这个调大可不好,这个调成0才会缓存所有的查询。

这个参数也不是越大越好,缓存的条目数的算法是:capacity-mb/admission-max-result-mb*10 所以如果这个调的特别大,会导致总的条目数少,然后不断的evict。这个值要正好存下你所有期望缓存的数据。

这样的话,执行几次,cache-ratio应该能变成1。如果说你的查询条件总是变化,查询的region总是会有新的写入,那缓存的作用不大。如果查询条件不变,并且region也没有总写入新数据,这个缓存还是有效果的。

好的,感谢!
我就只调整了一个capacity-mb=2000.0
其他的我其实没动的。
目前通过analyze也解决了。

大佬。tidb version:v5.2.1没找到下面几个参数啊!
是不是这几个参数被取消了。
admission-max-ranges
admission-max-result-mb
admission-min-process-ms

文档里没有。实际上是有的。你就配置就行了。

server_configs:
tidb:
log.slow-threshold: 300
proxy-protocol.networks: null
tikv-client.copr-cache.admission-max-ranges: 60000
tikv-client.copr-cache.admission-max-result-mb: 5.0
tikv-client.copr-cache.admission-min-process-ms: 0
tikv-client.copr-cache.capacity-mb: 2000.0
token-limit: 4000
嗯嗯,添加了,确实可以配置。然后tiup cluster restart 了集群 。
10s疯狂跑SQL观察并没有明显的变化,因为这些参数没配置的时候,你看ANALYZE后,执行计划,也是缓存了的。
结论:ANALYZE后不缓存100ms左右,缓存之后(缓存时间很短,不到一分钟,不知道是不是SQL涉及的表格有数据变动致缓存失效),70ms左右,改完上面参数重启后跟之前保持一致。

EXPLAIN ANALYZE SELECT
/*+ HASH_JOIN(t) */
DISTINCT d.triggerid_down,
d.triggerid_up
FROM
trigger_depends d,
triggers t,
HOSTS h,
items i,
functions f
WHERE
t.triggerid = d.triggerid_down
AND t.flags <> 2
AND h.hostid = i.hostid
AND i.itemid = f.itemid
AND f.triggerid = d.triggerid_down
AND h.status IN (0, 1);
如上进行优化后,不缓存65ms左右,缓存后(缓存时间很短,不到一分钟就会失效)30ms左右。

各位大佬,这个SQL涉及的小表,ANALYZE前后性能差十倍。但是我看ANALYZE前的执行计划,并没发现什么问题,基本也都走了索引扫描了。
遇到这类问题,怎么才能确认是SQL执行计划的问题呢。或者换个说法从EXPLAIN ANALYZE 哪些输出点可以判断出用别的算子,性能可能会更高些。
各位大佬,就拿我这SQL来讲,SPM优化可以怎么写。给几个示例吧。

你们的表如果频繁写入的话,缓存是很容易失效的。缓存适合表变化小的场景。