scan性能问题

TiDBer_jYQINSnf · 2024 年6 月 11 日 06:36

主键也是得扫啊，limit是扫描后再limit。并不是随随便便扫出来几条就返回的。优化的话尽量别那么大范围呗。

TiDBer_i3pbMJ65 · 2024 年6 月 11 日 07:10

从trace上看，应该只扫描了两个region, 也只需要扫描两个region。

TiDBer_i3pbMJ65 · 2024 年6 月 11 日 07:16

我倒是怀疑是上面这一步耗时太多。
从执行计划上面看，两种查询在TiKV上的耗时和scan的数据量基本一样，时延差异应该在TiDB上。
我的环境中有18万+个region。
sql查询中指定的key range太大，导致key Range跟所有的region，做范围比较，以确定该region是否是目标 region。
sql查询中指定的key range小，只需要key Range跟少数几个region，做范围比较，以确定该region是否是目标 region。

TiDBer_i3pbMJ65 · 2024 年6 月 12 日 02:35

没有原厂专家分析下吗，感觉是tidb的bug啊！

TiDBer_i3pbMJ65 · 2024 年6 月 12 日 03:04

Decrease loading Region info in RegionCache when using select * from table limit 1 · Issue #39733 · pingcap/tidb · GitHub

类似的问题

有猫万事足 · 2024 年6 月 12 日 05:15

确认是一个产品缺陷，修改一下帖子分类。

TiDBer_i3pbMJ65 · 2024 年6 月 12 日 05:52

后面有修复版本计划，烦请告知一下！

有猫万事足 · 2024 年6 月 12 日 06:04

现在的问题是这样的，原始14320上有一个可能的修复。

但这个pr可能有冲突，没有合并。

github.com/pingcap/tidb

store/copr: parallel build cop tasks

pingcap:master ← you06:copr/parallel-build-task

opened 03:24PM - 21 Feb 23 UTC

you06

+1392 -522

### What problem does this PR solve?  Issue Number: close #14320, close #40441 Problem Summary: In some cases, build cop tasks may takes a non-negligible duration, which directly increases the latency of coprocessor tasks. ### What is changed and how it works? Before this PR, the coprocessor tasks are built in [the preparation stage](https://github.com/pingcap/tidb/blob/a39d7fb09b7b3a7c6694ef8aab7d1d7d8e817f57/store/copr/coprocessor.go#L151-L170) in TiDB, if the build is slow(there may be region misses and need to load from PD), the query latency may increase, this PR makes the build tasks run in [the sender goroutine](https://github.com/pingcap/tidb/blob/a39d7fb09b7b3a7c6694ef8aab7d1d7d8e817f57/store/copr/coprocessor.go#L845), so that some tasks can be started before all tasks are built. Without a pre-built task slice, it's hard to determine the execution concurrency, so this PR also introduces `util/workers`, which creates workers dynamically during the execution, do not spawn useless workers, and also avoids suffering from worker starvation. ![diagram](https://user-images.githubusercontent.com/9587680/223039706-182e907c-3c76-404a-8153-1188c20616e2.png) In a index lookup test(1 index scan with 100 1row-table lookup tasks), the avg duration of the query is decreased from 6.38ms to 5.30ms. ### Check List Tests - [ ] Unit test - [ ] Integration test - [x] Manual test (add detailed scripts or steps below) - [ ] No code Benchmark result |Case|Nightly|This PR Without Parallel Build|Diff| |-|-|-|-| | sysbench read only| 129272 qps|129831 qps|+0.4%| |Case|Nightly|This PR With Parallel Build|Diff| |-|-|-|-| |sysbench read only(range size 100, default)| 129272 qps| 125528 qps|+0.4%| |sysbench read only(range size 1,000)| 25467 qps | 25954 qps|+1.9%| |sysbench read only(range size 10,000)| 9554 qps| 10063 qps|+5.3%| Larger range read cost longer time in building task phase, thus the benefits of parallel building is more significant. Side effects - [ ] Performance regression: Consumes more CPU - [ ] Performance regression: Consumes more Memory - [ ] Breaking backward compatibility Documentation - [ ] Affects user behaviors - [ ] Contains syntax changes - [ ] Contains variable changes - [ ] Contains experimental features - [ ] Changes MySQL compatibility ### Release note Please refer to [Release Notes Language Style Guide](https://pingcap.github.io/tidb-dev-guide/contribute-to-tidb/release-notes-style-guide.html) to write a quality release note. ```release-note store/copr: support parallel building coprocessor tasks, reduce the latency when region cache misses frequently. ```

等这个pr close后，看看对应的日期和版本就明确是在那个版本了。

TiDBer_i3pbMJ65 · 2024 年6 月 13 日 03:16

目前7.5.1版本有办法规避吗（where 条件范围很大）？

Billmay表妹 · 2024 年6 月 17 日 02:33

和相关的老师沟通了之后大概的结果如下：
8.2及以后的版本里面有相关性能提升的规划，不光这个问题，几个相近的问题会集中解决一下。有以下几个相关的 issue：

github.com/pingcap/tidb

Parallel building cop tasks with cop workers in TiKV client. (select * from limit 1 is too slow)

opened 08:22AM - 02 Jan 20 UTC

SunRunAway

type/enhancement help wanted type/performance component/tikv

## Description The current procedure when sending a cop request is 1. build …all cop tasks in memory. 2. send each cop task to `copIteratorWorker` 3. `copIteratorWorker` execute the task If we have thousands of tasks, the building step may take too long(and it may load region info from pd), it will enlarge the time of first meeting row. We need build all cop tasks in a diffrent goroutine, and send tasks which successfully builded immediately to `copIteratorWorker`. 1. For OLTP SQLs, it would be better build first 1 or 2 tasks in the same goroutine, then create a new goroutine to build the rest. 2. We can fix this issue when the request need an asending order firstly. 3. If the request need a desending order response, it may takes extra works on this issue. (We may need implement a desending spliting range method) ## Difficulty - Medium ## Score - 2500 ## Mentor(s) - @SunRunAway - @XuHuaiyu - @qw4990 ## Recommended Skills - Go programming

https://github.com/pingcap/tidb/issues/39733 https://github.com/pingcap/tidb/issues/40441

不过修改会比较多，目前的时间还没有定，计划也可能会有变化。已经有 github issue 建议关注

github.com/pingcap/tidb

Parallel building cop tasks with cop workers in TiKV client. (select * from limit 1 is too slow)

opened 08:22AM - 02 Jan 20 UTC

SunRunAway

type/enhancement help wanted type/performance component/tikv

## Description The current procedure when sending a cop request is 1. build …all cop tasks in memory. 2. send each cop task to `copIteratorWorker` 3. `copIteratorWorker` execute the task If we have thousands of tasks, the building step may take too long(and it may load region info from pd), it will enlarge the time of first meeting row. We need build all cop tasks in a diffrent goroutine, and send tasks which successfully builded immediately to `copIteratorWorker`. 1. For OLTP SQLs, it would be better build first 1 or 2 tasks in the same goroutine, then create a new goroutine to build the rest. 2. We can fix this issue when the request need an asending order firstly. 3. If the request need a desending order response, it may takes extra works on this issue. (We may need implement a desending spliting range method) ## Difficulty - Medium ## Score - 2500 ## Mentor(s) - @SunRunAway - @XuHuaiyu - @qw4990 ## Recommended Skills - Go programming

进展就好哈