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

centosredhat · 2021 年11 月 4 日 05:20

为提高效率，请提供以下信息，问题描述清晰能够更快得到解决：
【 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;
【业务影响】
无

h5n1 · 2021 年11 月 4 日 06:25

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

centosredhat · 2021 年11 月 5 日 09:04

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

TiDBer_jYQINSnf · 2021 年11 月 5 日 11:25

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次，第一次未必缓存，如果第二次还没缓存，那就是参数调的不合适，再调。

TiDBer_jYQINSnf · 2021 年11 月 5 日 11:25

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

长安是只喵 · 2021 年11 月 5 日 12:12

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

长安是只喵 · 2021 年11 月 5 日 12:29

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

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)
		)
	)
)

centosredhat · 2021 年11 月 5 日 15:45

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

centosredhat · 2021 年11 月 5 日 16:00

因为这个查询是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之后就变成这样了：

centosredhat · 2021 年11 月 5 日 16:05

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

centosredhat · 2021 年11 月 5 日 16:17

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

长安是只喵 · 2021 年11 月 8 日 01:14

长安是只喵 · 2021 年11 月 8 日 01:15

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

TiDBer_jYQINSnf · 2021 年11 月 8 日 02:44

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

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

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

centosredhat · 2021 年11 月 8 日 03:22

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

centosredhat · 2021 年11 月 8 日 05:04

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

TiDBer_jYQINSnf · 2021 年11 月 8 日 07:06

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

centosredhat · 2021 年11 月 9 日 02:08

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左右。

centosredhat · 2021 年11 月 9 日 02:09

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

TiDBer_jYQINSnf · 2021 年11 月 9 日 02:39

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