删除语句执行时间过长

🪐 TiDB 技术问题
, , , ,
1

【 TiDB 使用环境】生产环境
【 TiDB 版本】6.1.0
【问题现象及影响】只有一张表的删除语句过慢，不定时出现，大概每天出现几次
【复现路径】explain
【资源配置】TIKV：3台(8P32G)，Servers跟pd：3台(8P16G)
前提：TIDB限制语句执行时候不能超过10秒
只有一张表的删除语句过慢，查看慢日志详细，没发现什么
这张表的数据才：187937（18万条数据）

image
image1605×922 66.8 KB

2

发下sql和执行计划。

3

DELETE FROM stat_day_longteng WHERE event_date = “20210212”

执行计划？

4

看看后面两个sheet，cop 读取 和 事务

5

explain DELETE FROM stat_day_longteng WHERE event_date = “20210212”

6

不是必现，是每天会出现个几次

7

image
image1005×186 6.6 KB

8

我现在执行explain是正常的，但是他时不时就会出现超时

9

全表扫描了，肯定慢了，连索引都没用上，先优化下条件吧

slow query 开了没？ 开了，应该可以找到详细的信息的

不行就开启资源定位，帮你收集这些信息
https://docs.pingcap.com/zh/tidb/stable/identify-expensive-queries

1 个赞
10

慢日志开了，截图在上面， 全表不超过20万条数据，就算是全表扫描也不应该会超过10秒的吧

11

查看了tidb.log，发现了这个
[2022/12/05 14:47:17.787 +08:00] [INFO] [conn.go:1149] [“command dispatched failed”] [conn=367744858060751563] [connInfo=“id:367744858060751563, addr:172.28.3.8:61211 status:11, collation:utf8_general_ci, user:tx_data”] [command=Execute] [status=“inTxn:1, autocommit:1”] [sql=“DELETE FROM stat_day_longteng WHERE event_date = ? [arguments: "20210212"]”] [txn_mode=PESSIMISTIC] [timestamp=437838892945375274] [err=“context canceled\ngithub.com/tikv/client-go/v2/internal/locate.(*RegionRequestSender).sendReqToRegion\n\t/go/pkg/mod/github.com/tikv/client-go/v2@v2.0.1-0.20220531092439-efebaeb9fe53/internal/locate/region_request.go:1220\ngithub.com/tikv/client-go/v2/internal/locate.(*RegionRequestSender).SendReqCtx\n\t/go/pkg/mod/github.com/tikv/client-go/v2@v2.0.1-0.20220531092439-efebaeb9fe53/internal/locate/region_request.go:990\ngithub.com/tikv/client-go/v2/internal/locate.(*RegionRequestSender).SendReq\n\t/go/pkg/mod/github.com/tikv/client-go/v2@v2.0.1-0.20220531092439-efebaeb9fe53/internal/locate/region_request.go:231\ngithub.com/tikv/client-go/v2/tikv.(*KVStore).SendReq\n\t/go/pkg/mod/github.com/tikv/client-go/v2@v2.0.1-0.20220531092439-efebaeb9fe53/tikv/kv.go:394\ngithub.com/tikv/client-go/v2/txnkv/transaction.actionPessimisticLock.handleSingleBatch\n\t/go/pkg/mod/github.com/tikv/client-go/v2@v2.0.1-0.20220531092439-efebaeb9fe53/txnkv/transaction/pessimistic.go:143\ngithub.com/tikv/client-go/v2/txnkv/transaction.(*batchExecutor).startWorker.func1\n\t/go/pkg/mod/github.com/tikv/client-go/v2@v2.0.1-0.20220531092439-efebaeb9fe53/txnkv/transaction/2pc.go:1993\nruntime.goexit\n\t/usr/local/go/src/runtime/asm_amd64.s:1571\ngithub.com/pingcap/errors.AddStack\n\t/go/pkg/mod/github.com/pingcap/errors@v0.11.5-0.20211224045212-9687c2b0f87c/errors.go:174\ngithub.com/pingcap/errors.Trace\n\t/go/pkg/mod/github.com/pingcap/errors@v0.11.5-0.20211224045212-9687c2b0f87c/juju_adaptor.go:15\ngithub.com/pingcap/tidb/store/driver/error.ToTiDBErr\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/store/driver/error/error.go:169\ngithub.com/pingcap/tidb/store/driver/txn.extractKeyErr\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/store/driver/txn/error.go:154\ngithub.com/pingcap/tidb/store/driver/txn.(*tikvTxn).extractKeyErr\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/store/driver/txn/txn_driver.go:275\ngithub.com/pingcap/tidb/store/driver/txn.(*tikvTxn).LockKeys\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/store/driver/txn/txn_driver.go:75\ngithub.com/pingcap/tidb/session.(*LazyTxn).LockKeys\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/session/txn.go:392\ngithub.com/pingcap/tidb/executor.(*ExecStmt).handlePessimisticDML\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/executor/adapter.go:714\ngithub.com/pingcap/tidb/executor.(*ExecStmt).handleNoDelay\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/executor/adapter.go:514\ngithub.com/pingcap/tidb/executor.(*ExecStmt).Exec\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/executor/adapter.go:465\ngithub.com/pingcap/tidb/session.runStmt\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/session/session.go:2069\ngithub.com/pingcap/tidb/session.(*session).preparedStmtExec\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/session/session.go:2223\ngithub.com/pingcap/tidb/session.(*session).ExecutePreparedStmt\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/session/session.go:2419\ngithub.com/pingcap/tidb/server.(*TiDBStatement).Execute\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/server/driver_tidb.go:75\ngithub.com/pingcap/tidb/server.(*clientConn).executePreparedStmtAndWriteResult\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/server/conn_stmt.go:234\ngithub.com/pingcap/tidb/server.(*clientConn).handleStmtExecute\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/server/conn_stmt.go:201\ngithub.com/pingcap/tidb/server.(*clientConn).dispatch\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/server/conn.go:1393\ngithub.com/pingcap/tidb/server.(*clientConn).Run\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/server/conn.go:1121\ngithub.com/pingcap/tidb/server.(*Server).onConn\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/server/server.go:559\nruntime.goexit\n\t/usr/local/go/src/runtime/asm_amd64.s:1571\nDELETE FROM stat_day_longteng WHERE event_date = ? [arguments: "20210212"]”]

12

啥配置？ 机器配置，集群配置

你按照这个来补充下信息把

描述问题时候，请按照：
【 TiDB 使用环境】生产环境 /测试/ Poc
【 TiDB 版本】
【问题现象及影响】
【复现路径】做过哪些操作出现的问题
【资源配置】

描述，尽可能提供更多有效的背景信息，很多问题在不同的场景、业务下，大家可能提供的建议是不一样的，没有讲清楚只会让大家想帮忙都无从下手~

13

【 TiDB 使用环境】生产环境
【 TiDB 版本】6.1.0
【问题现象及影响】只有一张表的删除语句过慢，不定时出现，大概每天出现几次
【复现路径】explain
【资源配置】TIKV：3台(8P32G)，Servers跟pd：3台(8P16G)
已更新

14

tidb 节点几个？
tikv 和 PD 什么类型的磁盘？

日志描述是命令分发失败…

command dispatched failed

[2022/12/05 14:47:17.787 +08:00] [INFO] [conn.go:1149] [“command dispatched failed”] [conn=367744858060751563] [connInfo=“id:367744858060751563, addr:172.28.3.8:61211 status:11, collation:utf8_general_ci, user:tx_data”] [command=Execute] [status=“inTxn:1, autocommit:1”] [sql=“DELETE FROM `stat_day_longteng` WHERE `event_date` = ? [arguments: "20210212"]”] [txn_mode=PESSIMISTIC] [timestamp=437838892945375274] 
[err=“context canceled]

这个好像是手动取消了，可以忽略 :see_no_evil:

15

TIDB整个集群共有6个节点，KV独占3个节点，tidbServers跟tipd占三个节点
都是SSD硬盘

16

:sob:呜呜

17

建议还是通过索引来命中数据，以此目标来优化

其他的建议，对你来说应该没啥用

数据量太少了 :see_no_evil: :see_no_evil:

18

好的，我这边已经加了索引，明后天会看看是否还会出现这个问题，如果没有出现这问题，我也会回复的。麻烦大佬这几天关注下问题，十分感谢

19

大佬，还是会，我加了索引 :sob:

image
image818×170 6.2 KB

20

加了索引后，执行多久呢？ 还是出现slow query 么？