query is very slow when keep order is true

[guo-shaoge]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

tidb/pkg/store/copr/coprocessor.go

Lines 222 to 256 in 0abf3ae

	if it.req.KeepOrder {
	// Don't set high concurrency for the keep order case. It wastes a lot of memory and gains nothing.
	// TL;DR
	// Because for a keep order coprocessor request, the cop tasks are handled one by one, if we set a
	// higher concurrency, the data is just cached and not consumed for a while, this increase the memory usage.
	// Set concurrency to 2 can reduce the memory usage and I've tested that it does not necessarily
	// decrease the performance.
	// For ReqTypeAnalyze, we keep its concurrency to avoid slow analyze(see https://github.com/pingcap/tidb/issues/40162 for details).
	if it.concurrency > 2 && it.req.Tp != kv.ReqTypeAnalyze {
	oldConcurrency := it.concurrency
	partitionNum := req.KeyRanges.PartitionNum()
	if partitionNum > it.concurrency {
	partitionNum = it.concurrency
	}
	it.concurrency = 2
	if it.concurrency < partitionNum {
	it.concurrency = partitionNum
	}
	failpoint.Inject("testRateLimitActionMockConsumeAndAssert", func(val failpoint.Value) {
	if val.(bool) {
	// When the concurrency is too small, test case tests/realtikvtest/sessiontest.TestCoprocessorOOMAction can't trigger OOM condition
	it.concurrency = oldConcurrency
	}
	})
	}
	if it.smallTaskConcurrency > 20 {
	it.smallTaskConcurrency = 20
	}
	it.sendRate = util.NewRateLimit(2 * (it.concurrency + it.smallTaskConcurrency))
	it.respChan = nil
	} else {
	it.respChan = make(chan *copResponse)
	it.sendRate = util.NewRateLimit(it.concurrency + it.smallTaskConcurrency)
	}

This code is intended to make memory usage more efficient. The basic idea is when keep order is true, there is no need to start too many cop workers to receive data, because we want the data to return by order.

However, if selection pushdown occurs, it may require reading many rows to get the desired data. In that case, keeping the concurrency level too low can significantly slow down performance.

2. What did you expect to see? (Required)

3. What did you see instead (Required)

4. What is your TiDB version? (Required)

master

[guo-shaoge]

introduced by #35975

[tiancaiamao]

Maybe we can simply relax the restraction of concurrency when the cop request goes to tiflash.
Because in the tiflash scanerio, the tidb memory usage is not the top concern.

Other changes need more expirements, like here you mentioned:

However, if selection pushdown occurs, it may require reading many rows to get the desired data. In that case, keeping the concurrency level too low can significantly slow down performance.

If the selection pushdown filter a lot data, the cop response packet is small enough, then increase concurrency would not increase the memory usage ... but it's more difficult to predicate how large the return packet would be.

[kennedy8312]

/type regression

[winoros]

More than 2 oncalls are related to this problem.
Just change the severity to ensure we will not forget to bring it to the later minor releases.

[seiya-annie]

/report customer