## 1. 配置Prometheus和Grafana

### 1.1 安装Prometheus
$ wget https://github.com/prometheus/prometheus/releases/download/v2.30.3/prometheus-2.30.3.linux-amd64.tar.gz
$ tar -xzf prometheus-2.30.3.linux-amd64.tar.gz
$ mv prometheus-2.30.3.linux-amd64 /tidb/app/prometheus

### 1.2 配置Prometheus
$ vim /tidb/app/prometheus/prometheus.yml

global:
scrape_interval: 15s
evaluation_interval: 15s

alerting:
alertmanagers:
– static_configs:
– targets:
– localhost:9093

rule_files:
– “rules/*.yml”

scrape_configs:
– job_name: ‘tidb’
static_configs:
– targets: [‘192.168.1.10:10080’]

– job_name: ‘pd’
static_configs:
– targets: [‘192.168.1.10:2379’]

– job_name: ‘tikv’
static_configs:
– targets: [‘192.168.1.20:20180’, ‘192.168.1.21:20180’, ‘192.168.1.22:20180’]

### 1.3 启动Prometheus
$ /tidb/app/prometheus/prometheus –config.file=/tidb/app/prometheus/prometheus.yml

### 1.4 安装Grafana
$ wget https://dl.grafana.com/oss/release/grafana-8.3.3.linux-amd64.tar.gz
$ tar -xzf grafana-8.3.3.linux-amd64.tar.gz
$ mv grafana-8.3.3 /tidb/app/grafana

### 1.5 启动Grafana
$ /tidb/app/grafana/bin/grafana-server –homepath=/tidb/app/grafana

### 1.6 配置Grafana数据源
– 登录Grafana：http://192.168.1.10:3000
– 用户名：admin，密码：admin
– 添加数据源：选择Prometheus，URL填写http://localhost:9090
– 导入TiDB dashboard模板

## 2. 配置TiDB Dashboard

### 2.1 访问TiDB Dashboard
– 访问地址：http://192.168.1.10:2379/dashboard
– 用户名：root，密码：空

### 2.2 查看Region分布
– 进入”集群信息” -> “TiKV Store”
– 查看各Store的Region数量、Leader数量等

### 2.3 查看热点Region
– 进入”集群信息” -> “热点可视化”
– 查看热点Region分布情况

## 1. 检测Region分布
mysql> SELECT peer_store_id, COUNT(*) AS region_count, SUM(region_size) AS total_size
-> FROM information_schema.tikv_region_status
-> GROUP BY peer_store_id
-> ORDER BY region_count DESC;

# 输出示例
+————-+————–+————+
| peer_store_id | region_count | total_size |
+————-+————–+————+
| 1 | 1000 | 10737418240 |
| 2 | 800 | 8589934592 |
| 3 | 700 | 7516192768 |
+————-+————–+————+

## 2. 检测热点Region
mysql> SELECT region_id, db_name, table_name, region_size, leader_store_id
-> FROM information_schema.tikv_region_status
-> ORDER BY region_size DESC
-> LIMIT 10;

# 输出示例
+———–+———+————+————+—————+
| region_id | db_name | table_name | region_size | leader_store_id |
+———–+———+————+————+—————+
| 1 | fgedudb | fgedu_orders | 104857600 | 1 |
| 2 | fgedudb | fgedu_users | 52428800 | 1 |
| 3 | fgedudb | fgedu_products | 26214400 | 2 |
+———–+———+————+————+—————+

## 3. 检测表数据分布
mysql> SELECT table_name, data_length, index_length, table_rows
-> FROM information_schema.tables
-> WHERE table_schema = ‘fgedudb’
-> ORDER BY data_length DESC;

# 输出示例
+————+————-+————–+————+
| table_name | data_length | index_length | table_rows |
+————+————-+————–+————+
| fgedu_orders | 104857600 | 52428800 | 1000000 |
| fgedu_users | 52428800 | 26214400 | 500000 |
| fgedu_products | 26214400 | 13107200 | 250000 |
+————+————-+————–+————+

## 4. 检测分区数据分布
mysql> SELECT partition_name, table_rows
-> FROM information_schema.partitions
-> WHERE table_schema = ‘fgedudb’ AND table_name = ‘fgedu_orders’
-> ORDER BY table_rows DESC;

# 输出示例
+—————-+————+
| partition_name | table_rows |
+—————-+————+
| p202401 | 500000 |
| p202402 | 300000 |
| p202403 | 200000 |
+—————-+————+

## 5. 检测TiKV节点负载
$ top

# 输出示例
top – 10:00:00 up 10 days, 1:00, 1 user, load average: 0.50, 0.40, 0.30
Tasks: 200 total, 1 running, 199 sleeping, 0 stopped, 0 zombie
%Cpu(s): 5.0 us, 0.5 sy, 0.0 ni, 94.0 id, 0.0 wa, 0.0 hi, 0.5 si, 0.0 st
MiB Mem : 16384.0 total, 8192.0 free, 4096.0 used, 4096.0 buff/cache
MiB Swap: 8192.0 total, 8192.0 free, 0.0 used. 12288.0 avail Mem

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
12345 tidb 20 0 8192.0m 2048.0m 512.0m S 20.0 12.5 1:00.00 tikv-server

## 6. 检测网络流量
$ iftop -i eth0

# 输出示例
interface: eth0
IP address is: 192.168.1.20
MAC address is: 00:00:00:00:00:00

# host last 2s last 10s last 40s cumulative
1 192.168.1.10:2379 <- 192.168.1.20:40000 100Kb 100Kb 100Kb 1000Kb 2 192.168.1.10:4000 <- 192.168.1.20:20160 50Kb 50Kb 50Kb 500Kb 3 192.168.1.21:20160 <-> 192.168.1.20:20160 20Kb 20Kb 20Kb 200Kb

## 1. 手动分裂热点Region

### 1.1 查看热点Region
mysql> SELECT region_id, db_name, table_name, region_size
-> FROM information_schema.tikv_region_status
-> WHERE db_name = ‘fgedudb’ AND table_name = ‘fgedu_orders’
-> ORDER BY region_size DESC
-> LIMIT 5;

# 输出示例
+———–+———+————+————+
| region_id | db_name | table_name | region_size |
+———–+———+————+————+
| 1 | fgedudb | fgedu_orders | 104857600 |
+———–+———+————+————+

### 1.2 手动分裂Region
mysql> ADMIN SPLIT REGION 1 BY ‘t_1000_40000000’;

# 输出示例
Query OK, 0 rows affected (0.01 sec)

### 1.3 查看分裂后的Region
mysql> SELECT region_id, db_name, table_name, region_size
-> FROM information_schema.tikv_region_status
-> WHERE db_name = ‘fgedudb’ AND table_name = ‘fgedu_orders’;

# 输出示例
+———–+———+————+————+
| region_id | db_name | table_name | region_size |
+———–+———+————+————+
| 1 | fgedudb | fgedu_orders | 52428800 |
| 2 | fgedudb | fgedu_orders | 52428800 |
+———–+———+————+————+

## 2. 优化主键设计

### 2.1 查看当前主键
mysql> SHOW CREATE TABLE fgedu_orders;

# 输出示例
+————+—————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————+
| Table | Create Table |
+————+—————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————+
| fgedu_orders | CREATE TABLE `fgedu_orders` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`product_id` int(11) NOT NULL,
`amount` decimal(10,2) NOT NULL,
`order_time` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+————+—————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————+

### 2.2 修改主键为UUID
mysql> ALTER TABLE fgedu_orders DROP PRIMARY KEY, ADD PRIMARY KEY (`id`);
mysql> ALTER TABLE fgedu_orders MODIFY COLUMN `id` VARCHAR(36) NOT NULL;
mysql> UPDATE fgedu_orders SET id = UUID();

# 输出示例
Query OK, 1000000 rows affected (10.00 sec)

## 3. 优化业务逻辑

### 3.1 批量插入优化
– 避免单条插入，使用批量插入
– 分散插入数据，避免顺序写入

### 3.2 热点更新优化
– 减少对同一数据的频繁更新
– 使用缓存，减少数据库访问
– 异步更新，降低同步压力

## 1. 分区表优化

### 1.1 创建分区表
mysql> CREATE TABLE fgedu_orders_partitioned (
-> id VARCHAR(36) PRIMARY KEY,
-> user_id INT NOT NULL,
-> product_id INT NOT NULL,
-> amount DECIMAL(10,2) NOT NULL,
-> order_time DATETIME NOT NULL
-> ) PARTITION BY RANGE (YEAR(order_time)) (
-> PARTITION p2023 VALUES LESS THAN (2024),
-> PARTITION p2024 VALUES LESS THAN (2025),
-> PARTITION p2025 VALUES LESS THAN (2026)
-> );

# 输出示例
Query OK, 0 rows affected (0.02 sec)

### 1.2 迁移数据到分区表
mysql> INSERT INTO fgedu_orders_partitioned SELECT * FROM fgedu_orders;

# 输出示例
Query OK, 1000000 rows affected (5.00 sec)

### 1.3 查看分区数据分布
mysql> SELECT partition_name, table_rows
-> FROM information_schema.partitions
-> WHERE table_schema = ‘fgedudb’ AND table_name = ‘fgedu_orders_partitioned’
-> ORDER BY partition_name;

# 输出示例
+—————-+————+
| partition_name | table_rows |
+—————-+————+
| p2023 | 300000 |
| p2024 | 500000 |
| p2025 | 200000 |
+—————-+————+

## 2. 索引优化

### 2.1 查看当前索引
mysql> SHOW INDEX FROM fgedu_orders;

# 输出示例
+————+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+————+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+
| fgedu_orders | 0 | PRIMARY | 1 | id | A | 1000000 | NULL | NULL | | BTREE | |
+————+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+

### 2.2 添加复合索引
mysql> CREATE INDEX idx_user_product ON fgedu_orders (user_id, product_id);

# 输出示例
Query OK, 0 rows affected (1.00 sec)

### 2.3 查看索引使用情况
mysql> EXPLAIN SELECT * FROM fgedu_orders WHERE user_id = 1000 AND product_id = 2000;

# 输出示例
+————————-+———-+———–+————————+——————————–+—————+———+——————————–+——+———-+
| id | estRows | task | access object | operator info | memory | disk | execution info | cost | rows |
+————————-+———-+———–+————————+——————————–+—————+———+——————————–+——+———-+
| IndexLookUp_10 | 10.00 | root | | | 32.77 KB | N/A | time:0.1ms, loops:2, rows:1 | 10.00 | 1 |
| ├─IndexRangeScan_8 | 10.00 | cop[tikv] | table:fgedu_orders, index:idx_user_product(user_id, product_id) | range:[1000,2000,1000,2000], keep order:false | N/A | N/A | time:0.1ms, loops:1, rows:1 | 10.00 | 1 |
| └─TableRowIDScan_9 | 10.00 | cop[tikv] | table:fgedu_orders | | N/A | N/A | time:0.1ms, loops:1, rows:1 | 10.00 | 1 |
+————————-+———-+———–+————————+——————————–+—————+———+——————————–+——+———-+

## 3. PD调度优化

### 3.1 调整PD调度参数
$ vim /tidb/app/pd/conf/pd.toml

[scheduler]
# 调整最大调度数量
max-schedule-count = 32
# 调整热点调度限制
hot-region-schedule-limit = 8
# 调整存储均衡阈值
store-balance-rate = 20

### 3.2 重启PD
$ systemctl restart pd

### 3.3 手动触发均衡
mysql> ADMIN SCHEDULER CONFIGURE balance-region, “{\”enable\”: true}”;

# 输出示例
Query OK, 0 rows affected (0.01 sec)

## 1. 配置Prometheus告警规则

### 1.1 创建告警规则文件
$ mkdir -p /tidb/app/prometheus/rules
$ vim /tidb/app/prometheus/rules/tidb_alerts.yml

groups:
– name: tidb
rules:
– alert: TiKVRegionCountImbalance
expr: |
max(tikv_store_region_count) by (store_id) / min(tikv_store_region_count) by (store_id) > 1.5
for: 5m
labels:
severity: warning
annotations:
summary: “TiKV Region Count Imbalance”
description: “Region count imbalance detected. Max: {{ $value }} times of min.”

– alert: TiKVLeaderCountImbalance
expr: |
max(tikv_store_leader_count) by (store_id) / min(tikv_store_leader_count) by (store_id) > 1.5
for: 5m
labels:
severity: warning
annotations:
summary: “TiKV Leader Count Imbalance”
description: “Leader count imbalance detected. Max: {{ $value }} times of min.”

– alert: TiKVHotRegion
expr: |
sum(tikv_server_requests_total{type=”read”}) by (region_id) > 1000
for: 5m
labels:
severity: warning
annotations:
summary: “TiKV Hot Region”
description: “Hot region detected. Region ID: {{ $labels.region_id }}”

## 2. 配置AlertManager

### 2.1 安装AlertManager
$ wget https://github.com/prometheus/alertmanager/releases/download/v0.23.0/alertmanager-0.23.0.linux-amd64.tar.gz
$ tar -xzf alertmanager-0.23.0.linux-amd64.tar.gz
$ mv alertmanager-0.23.0.linux-amd64 /tidb/app/alertmanager

### 2.2 配置AlertManager
$ vim /tidb/app/alertmanager/alertmanager.yml

global:
resolve_timeout: 5m

route:
group_by: [‘alertname’]
group_wait: 30s
group_interval: 5m
repeat_interval: 1h
receiver: ’email’

receivers:
– name: ’email’
email_configs:
– to: ‘admin@fgedu.net.cn’
from: ‘alertmanager@fgedu.net.cn’
smarthost: ‘smtp.fgedu.net.cn:587’
auth_username: ‘alertmanager’
auth_password: ‘password’

## 3. 启动AlertManager
$ /tidb/app/alertmanager/alertmanager –config.file=/tidb/app/alertmanager/alertmanager.yml

## 4. 验证监控配置
– 访问Prometheus：http://192.168.1.10:9090
– 访问Grafana：http://192.168.1.10:3000
– 访问AlertManager：http://192.168.1.10:9093