## 1. 操作系统调优
$ cat > /etc/sysctl.d/tidb.conf << EOF # 网络调优 net.core.somaxconn = 65535 net.ipv4.tcp_max_syn_backlog = 65535 net.ipv4.tcp_fin_timeout = 30 net.ipv4.tcp_keepalive_time = 300 net.ipv4.tcp_keepalive_probes = 5 net.ipv4.tcp_keepalive_intvl = 15 # 文件系统调优 fs.file-max = 655350 # 内存调优 vm.swappiness = 0 vm.overcommit_memory = 1 EOF $ sysctl -p /etc/sysctl.d/tidb.conf ## 2. 关闭透明大页 $ echo never > /sys/kernel/mm/transparent_hugepage/enabled
$ echo never > /sys/kernel/mm/transparent_hugepage/defrag

## 3. 关闭NUMA
$ numactl –interleave=all tiup cluster start fgedu-tidb-cluster

## 4. 配置文件描述符限制
$ cat > /etc/security/limits.d/tidb.conf << EOF * soft nofile 65535 * hard nofile 65535 EOF ## 5. 配置磁盘调度策略 $ echo deadline > /sys/block/sda/queue/scheduler

## 6. 重启系统使配置生效
$ reboot

## 1. TiDB参数调优
$ tiup cluster edit-config fgedu-tidb-cluster
tidb_servers:
– host: 192.168.1.100
config:
performance:
max-procs: 4
tcp-keep-alive: true
prepared-plan-cache:
enabled: true
capacity: 1000
mem-quota-query: 34359738368

## 2. TiKV参数调优
$ tiup cluster edit-config fgedu-tidb-cluster
tikv_servers:
– host: 192.168.1.101
config:
storage:
scheduler-worker-pool-size: 4
readpool:
coprocessor:
high-concurrency: 8
max-tasks-per-worker: 2048
raftstore:
store-pool-size: 2
apply-pool-size: 2

## 3. PD参数调优
$ tiup cluster edit-config fgedu-tidb-cluster
pd_servers:
– host: 192.168.1.100
config:
replication:
max-replicas: 3
schedule:
leader-schedule-limit: 4
region-schedule-limit: 20
peer-schedule-limit: 4

## 4. 重启集群使配置生效
$ tiup cluster reload fgedu-tidb-cluster

## 5. 索引调优
$ mysql -h 192.168.1.100 -P 4000 -u fgedu -p -e “CREATE INDEX idx_fgedu_users_name ON fgedudb.fgedu_users(name);”

## 6. SQL调优
$ mysql -h 192.168.1.100 -P 4000 -u fgedu -p -e “EXPLAIN SELECT * FROM fgedudb.fgedu_users WHERE name = ‘test’;”

## 1. 数据压缩配置
$ tiup cluster edit-config fgedu-tidb-cluster
tikv_servers:
– host: 192.168.1.101
config:
storage:
data-compression:
enabled: true
algorithm: lz4
block-size: 65536

## 2. 数据分片配置
$ mysql -h 192.168.1.100 -P 4000 -u fgedu -p -e “CREATE TABLE fgedudb.fgedu_orders (id INT PRIMARY KEY, user_id INT, amount DECIMAL(10,2), created_at DATETIME) SHARDING BY HASH(id) SHARDS 8;”

## 3. 缓存配置
$ tiup cluster edit-config fgedu-tidb-cluster
tikv_servers:
– host: 192.168.1.101
config:
storage:
block-cache:
capacity: “16GB”
readpool:
storage:
high-concurrency: 8
max-tasks-per-worker: 2048

## 4. 存储介质选择
# 使用NVMe SSD作为TiKV存储

## 5. 磁盘挂载参数
$ cat >> /etc/fstab << EOF /dev/nvme0n1p1 /tidb/fgdata ext4 defaults,noatime,discard 0 2 EOF ## 6. 重启集群使配置生效 $ tiup cluster reload fgedu-tidb-cluster

## 1. 分析历史数据增长
$ mysql -h 192.168.1.100 -P 4000 -u fgedu -p -e “SELECT table_name, data_length, index_length FROM information_schema.tables WHERE table_schema = ‘fgedudb’;”

## 2. 预测未来数据增长
$ cat > data_growth_prediction.py << EOF #!/usr/bin/env python3 # data_growth_prediction.py # from:www.itpux.com.qq113257174.wx:itpux-com # web: http://www.fgedu.net.cn import pandas as pd import numpy as np from sklearn.linear_model import LinearRegression # 历史数据（示例） dates = pd.date_range('2024-01-01', periods=6, freq='M') data_sizes = [100, 120, 150, 180, 210, 250] # GB # 准备数据 X = np.array(range(len(dates))).reshape(-1, 1) y = np.array(data_sizes) # 训练模型 model = LinearRegression() model.fit(X, y) # 预测未来6个月数据量 future_X = np.array(range(len(dates), len(dates) + 6)).reshape(-1, 1) predicted_sizes = model.predict(future_X) print("未来6个月预测数据量：") for i, size in enumerate(predicted_sizes): print(f"{dates[-1] + pd.DateOffset(months=i+1)}: {size:.2f} GB") EOF $ python3 data_growth_prediction.py ## 3. 数据保留策略 $ mysql -h 192.168.1.100 -P 4000 -u fgedu -p -e "CREATE EVENT purge_old_data ON SCHEDULE EVERY 1 DAY DO DELETE FROM fgedudb.fgedu_logs WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY);" ## 4. 数据压缩率评估 $ tiup cluster display fgedu-tidb-cluster | grep -A 10 "TiKV Storage" ## 5. 存储容量计算 # 公式：总存储容量 = 数据量 * 副本数 / 压缩率 * 安全系数 # 示例：数据量=500GB，副本数=3，压缩率=0.5，安全系数=1.5 # 总存储容量 = 500 * 3 / 0.5 * 1.5 = 4500GB

## 1. CPU规划
# 计算公式：CPU核心数 = (并发数 * 平均查询CPU时间) / (目标响应时间 * CPU利用率)
# 示例：并发数=1000，平均查询CPU时间=0.01秒，目标响应时间=0.1秒，CPU利用率=0.7
# CPU核心数 = (1000 * 0.01) / (0.1 * 0.7) ≈ 143

## 2. 内存规划
# 计算公式：内存 = 数据缓存 + 索引缓存 + 连接内存 + 系统内存
# 示例：数据缓存=16GB，索引缓存=8GB，连接内存=4GB，系统内存=4GB
# 内存 = 16 + 8 + 4 + 4 = 32GB

## 3. 存储规划
# 计算公式：存储 = 数据量 * 副本数 / 压缩率 * 安全系数
# 示例：数据量=500GB，副本数=3，压缩率=0.5，安全系数=1.5
# 存储 = 500 * 3 / 0.5 * 1.5 = 4500GB

## 4. 网络规划
# 计算公式：网络带宽 = (峰值并发 * 平均请求大小) / (目标响应时间 * 网络利用率)
# 示例：峰值并发=1000，平均请求大小=1KB，目标响应时间=0.1秒，网络利用率=0.7
# 网络带宽 = (1000 * 1KB) / (0.1秒 * 0.7) ≈ 14.3MB/s ≈ 114.4Mbps

## 5. 资源配置
$ tiup cluster deploy fgedu-tidb-cluster 7.5.0 topology.yaml –user root -p

## 6. 扩展性规划
$ cat > scale_plan.md << EOF # 扩展触发条件 - CPU使用率 > 80% 持续10分钟
– 内存使用率 > 85% 持续10分钟
– 存储使用率 > 80% 持续10分钟
– 响应时间 > 1秒 持续10分钟

# 扩展流程
1. 监控系统触发扩展告警
2. 运维人员确认扩展需求
3. 执行扩展操作
4. 验证扩展效果
5. 更新监控配置
EOF

## 1. 使用TiDB Dashboard监控
# 访问 http://192.168.1.100:10080/dashboard
# 查看性能相关指标

## 2. 使用Prometheus监控
# 访问 http://192.168.1.100:9090
# 查看性能相关指标

## 3. 配置性能告警
$ tiup cluster edit-config fgedu-tidb-cluster
alertmanager_servers:
– host: 192.168.1.100
config:
receivers:
– name: ’email’
email_configs:
– to: ‘admin@fgedu.net.cn’
send_resolved: true
route:
group_by: [‘alertname’]
group_wait: 30s
group_interval: 5m
repeat_interval: 1h
receiver: ’email’
routes:
– match:
severity: critical
receiver: ’email’

## 4. 配置性能指标收集
$ cat > performance_metrics.sh << EOF #!/bin/bash # performance_metrics.sh # from:www.itpux.com.qq113257174.wx:itpux-com # web: http://www.fgedu.net.cn # 收集TiDB性能指标 curl -s http://192.168.1.100:10080/metrics > /tidb/metrics/tidb_metrics.txt

# 收集TiKV性能指标
curl -s http://192.168.1.101:20180/metrics > /tidb/metrics/tikv_metrics.txt

# 收集PD性能指标
curl -s http://192.168.1.100:2379/metrics > /tidb/metrics/pd_metrics.txt
EOF

## 5. 定时收集性能指标
$ crontab -e
*/5 * * * * /tidb/scripts/performance_metrics.sh

## 1. 基准测试
$ tiup bench tpcc prepare –host 192.168.1.100 –port 4000 –user fgedu –password password –db fgedudb –warehouses 100
$ tiup bench tpcc run –host 192.168.1.100 –port 4000 –user fgedu –password password –db fgedudb –warehouses 100 –time 300s

## 2. 性能分析
$ mysql -h 192.168.1.100 -P 4000 -u fgedu -p -e “EXPLAIN ANALYZE SELECT * FROM fgedudb.fgedu_users WHERE name = ‘test’;”

## 3. 性能瓶颈识别
$ tiup cluster display fgedu-tidb-cluster
$ curl -s http://192.168.1.100:10080/dashboard/api/top-sql

## 4. 性能调优验证
$ tiup bench tpcc run –host 192.168.1.100 –port 4000 –user fgedu –password password –db fgedudb –warehouses 100 –time 300s

## 5. 性能报告生成
$ cat > performance_report.sh << EOF #!/bin/bash # performance_report.sh # from:www.itpux.com.qq113257174.wx:itpux-com # web: http://www.fgedu.net.cn date=$(date +%Y-%m-%d) # 收集性能数据 curl -s http://192.168.1.100:9090/api/v1/query?query=tidb_server_qps > /tidb/reports/qps_${date}.json
curl -s http://192.168.1.100:9090/api/v1/query?query=tidb_server_handle_query_duration_seconds_sum > /tidb/reports/latency_${date}.json

# 生成报告
echo “# 性能报告 ${date}” > /tidb/reports/performance_report_${date}.md
echo “## QPS” >> /tidb/reports/performance_report_${date}.md
cat /tidb/reports/qps_${date}.json >> /tidb/reports/performance_report_${date}.md
echo “## 响应时间” >> /tidb/reports/performance_report_${date}.md
cat /tidb/reports/latency_${date}.json >> /tidb/reports/performance_report_${date}.md
EOF

$ ./performance_report.sh