TiDB|TiDB 查询热点并打散热点方法 TiDB查询热点并打散热点方法

1、我们有一个 region.py 脚本，

#!/usr/bin/env python
#!coding:utf-8
import argparse
import requests
def main():
args = parse_args()
url = "http://{}:{}/tables/{}/{}/regions".format(args.host, args.port, args.database, args.table)
region_info = requests.get(url).json()
table_region_leader = parse_regions(region_info["record_regions"])
indices_region_leader = []
for index_info in region_info["indices"]:
index_name = index_info["name"]
index_region_leader = parse_regions(index_info["regions"])
indices_region_leader.append({"name": index_name, "leader": index_region_leader})
print("region leader distribution for table \"{}.{}\":".format(args.database, args.table))
print_leader(table_region_leader)
for index_region_info in indices_region_leader:
print("region leader distribution for index \"{}\":".format(index_region_info["name"]))
print_leader(index_region_info["leader"])
def parse_args():
parser = argparse.ArgumentParser(description="Show leader distribution of a TiDB table.")
parser.add_argument("--host", dest="host", help="tidb-server address, default: 127.0.0.1", default="127.0.0.1")
parser.add_argument("--port", dest="port", help="tidb-server status port, default: 10080", default="10080")
parser.add_argument("database", help="database name")
parser.add_argument("table", help="table name")
args = parser.parse_args()
return args
def parse_regions(regions):
info = {}
for region in regions:
if region["leader"]["store_id"] != None:
store_id = region["leader"]["store_id"]
info[store_id] = 1 + info.get(store_id, 0)
return info
def print_leader(info, indent = " "):
total_leaders = 0
for store_id, num_leaders in info.items():
total_leaders += num_leaders
print("{}total leader count: {}".format(indent, total_leaders))
for store_id, num_leaders in info.items():
print("{}store: {}, num_leaders: {}, percentage: {}%".format(indent, store_id, num_leaders, (num_leaders*100.0)/total_leaders))
if __name__ == "__main__":
main()

python region.py --host 10.12.69.21 --port 10080db_ods t_dp_bz_f_shop_sales
先确认表的 region 分布情况

region leader distribution for table "db_ods.t_dp_bz_f_shop_sales":
total leader count: 2
store: 25, num_leaders: 1, percentage: 50.0%
store: 5, num_leaders: 1, percentage: 50.0%
region leader distribution for index "PRIMARY":
total leader count: 1
store: 25, num_leaders: 1, percentage: 100.0%
region leader distribution for index "idx_dtd_shop_sales":
total leader count: 1
store: 25, num_leaders: 1, percentage: 100.0%
region leader distribution for index "idx_shop_id":
total leader count: 1
store: 25, num_leaders: 1, percentage: 100.0%
region leader distribution for index "idx_shopid_dt":
total leader count: 1
store: 25, num_leaders: 1, percentage: 100.0%

2、查看某张表的具体 region 信息
curl http://10.12.69.21:10080/tables/db_ods/t_dp_bz_f_shop_sales/regions
能看到类似下面的结果

{"name":"t_dp_bz_f_shop_sales",
"id":16110,"record_regions":[
{"region_id":485990,"leader":{"id":485993,"store_id":25},"peers":[{"id":485991,"store_id":34},{"id":485992,"store_id":5},{"id":485993,"store_id":25}],"region_epoch":{"conf_ver":865,"version":2181}},
{"region_id":378432,"leader":{"id":440477,"store_id":5},"peers":[{"id":440469,"store_id":34},{"id":440477,"store_id":5},{"id":451570,"store_id":25}],"region_epoch":{"conf_ver":865,"version":2180}}],
"indices":[{"name":"PRIMARY","id":1,"regions":[{"region_id":491389,"leader":{"id":491392,"store_id":25},"peers":[{"id":491390,"store_id":34},{"id":491391,"store_id":5},{"id":491392,"store_id":25}],"region_epoch":{"conf_ver":865,"version":2181}}]},{"name":"idx_dtd_shop_sales","id":2,"regions":[{"region_id":491389,"leader":{"id":491392,"store_id":25},"peers":[{"id":491390,"store_id":34},{"id":491391,"store_id":5},{"id":491392,"store_id":25}],"region_epoch":{"conf_ver":865,"version":2181}}]},{"name":"idx_shop_id","id":3,"regions":[{"region_id":491389,"leader":{"id":491392,"store_id":25},"peers":[{"id":491390,"store_id":34},{"id":491391,"store_id":5},{"id":491392,"store_id":25}],"region_epoch":{"conf_ver":865,"version":2181}}]},{"name":"idx_shopid_dt","id":6,"regions":[{"region_id":485990,"leader":{"id":485993,"store_id":25},"peers":[{"id":485991,"store_id":34},{"id":485992,"store_id":5},{"id":485993,"store_id":25}],"region_epoch":{"conf_ver":865,"version":2181}}]}]}

这里已经基本可以确定是哪个 region 引起的热点了，本次查看监控是 tikv_05 的 CPU 明显高于其他 store，所以对应的 region-id 为378432

3、可以通过 pd-ctl 的命令进一步确认
pd-ctl -uhttp://10.12.69.21:2379
region topread 5
region topwrite 5
或者可以直接通过 tidb.log / tikv.log，grep TIME_COP，一般能够看到慢查询对应的 region-id

4、通过 region 找对应表的信息
curl http://10.12.69.21:10080/regions/378432
{"region_id":378432,"start_key":"dIAAAAAAAD7uX3KAAAAAAAwpjQ==","end_key":"dIAAAAAAAD7w","frames":[{"db_name":"db_ods","table_name":"t_dp_bz_f_shop_sales","table_id":16110,"is_record":true,"record_id":797069}]}

5、手动 split
operator add split-region 378432
之后再用上面的命令查看表的 region 分布情况，理论上经过一段时间调度后，leader 节点会被自动 rebalance
也可以手动迁移 leader
【TiDB|TiDB 查询热点并打散热点方法】operator add transfer-leader 378432 20// 把 Region 1 的 leader 调度到 store 2