Support data affinity schedule for performance optimization

[lhy1024]

Development Task

Background

As a distributed database, TiDB inherently incurs network overhead that is not present in single-node databases. For many workloads, data from the same table or partition is frequently accessed together. The distribution of this data across different TiKV nodes can lead to significant cross-node communication for query execution, increasing latency and consuming more resources.

In our real-world load testing, we have observed that by ensuring data and indexes for the same table or partition are consolidated within a single Region, we can achieve substantial performance gains. Specifically, this co-location leads to higher QPS, lower query latency, and a notable reduction in CPU and network I/O resource consumption.

This feature will unlock key optimization opportunities, such as:

• More Read and Write Push Down
• More 1PC txn requests

Demo Test Result

Metric	index lookup pushdown	data affinity 1PC	Both
read queries	+7.59%	+7.12%	+18.41%
write queries	+7.59%	+7.12%	+18.41%
avg latency (ms)	-7.05%	-6.64%	-15.54%
95th percentile (ms)	-5.26%	-5.26%	-14.96%

Phase I plan

• TiDB
• Support data affinity schedule for performance optimization pingcap/tidb#64938
• *: update pd client and client go pingcap/tidb#65044
• ddl: support to set and alter the AFFINITY for table pingcap/tidb#64988
• ddl: sync affinity group for pd and support show affinity pingcap/tidb#65049
• TiKV
• raftstore: support split reason tikv#19013
• raftstore: support disable auto split tikv#19014
• raftstore: use SplitReason in SplitCheckerHost tikv#19221
• kvproto
• pd: support disable-auto-split region pingcap/kvproto#1351
• PD
• affinity: add affinity storage, new config, label plan and error code #9993
• affinity: add base models and manager skeleton #9997
• affinity: add txn and key range operations #9998
• affinity: add policy, metrics collection and tests #9999
• schedule: add affinity filter and kvproto update and use affinity mananager #10038
• affinity: refactor and address comments #10050
• schedule: add affinity checker #10040
• api: add affinity groups api and client support #10041
• mcs: add affinity redirect and scheduling watcher  #10042
• pd-ctl: support affinity group commands #10043
• affinity: add scatter filter, add more evict check and avoid statistic miss #10080
• operator: replace opMerge bitwise operations with HasRelatedMergeRegion #10081
• affinity, operator: fix affinity operator count #10091
• affinity: add stale region cache to avoid cascading merges #10103

Release 8.5.5

We don't put MCS in 8.5.5 and disable the affinity schedule by default.

• TiDB
• *: cherry-pick the table affinity to release-8.5 pingcap/tidb#65101
• TiKV
• raftstore: support disable auto split (#19013) (#19014) tikv#19212
• raftstore: use SplitReason in SplitCheckerHost (#19221) tikv#19223
• kvproto
• pd: support disable-auto-split region (#1351) pingcap/kvproto#1386
• PD
• cherrypick data affinity feature to release-8.5 #10061
• affinity: add stale region cache to avoid cascading merges (#10103) #10106

[Tema]

This will work only for table which fit a single region along with the index, right?

[lhy1024]

This will work only for table which fit a single region along with the index, right?
The goal is not only to force a table (or partition) into a single, massive region. Additionally, the goal is to ensure that all regions belonging to a specific table (or partition) are scheduled onto a store.

While the feature prevents auto splitting to keep the regions from being unnecessarily fragmented, it also includes a safeguard to split a "super-huge" region if it grows beyond a safe threshold.