## 模块 3: 扩展 SQL 层 在集群中添加一个TiDB服务器，扩展SQL层。重新执行相同的工作负载，比较`TiDB CPU`消耗，与之前的步骤相比，观察其比例降低。 ### 步骤 1. 检查 TiDB 服务器的扩展设置 ```bash cat scale-out-tidb-2.yaml ``` ```output tidb_servers: - host: 10.90.1.48 port: 4000 status_port: 10080 deploy_dir: /tidb-deploy/tidb-4000 log_dir: /tidb-deploy/tidb-4000/log ``` ### 步骤 2. 添加一个 TiDB 服务器到现有集群 ```bash ~/.tiup/bin/tiup cluster scale-out tidb-demo scale-out-tidb-2.yaml --user ec2-user -i ~/.ssh/pe-class-key-${LAB:REGION_NAME}.pem -y ``` ```output ... Scaled cluster `tidb-demo` out successfully ``` ### 步骤 3. 确认 TiDB 集群状态 + 确保 TiDB 集群中有两个`tidb`服务器。SQL 层的规模从1扩展至2。 ```bash ~/.tiup/bin/tiup cluster display tidb-demo ``` ```output Cluster type: tidb Cluster name: tidb-demo Cluster version: v${LAB:TIDB_VERSION} Deploy user: ec2-user SSH type: builtin Dashboard URL: http://54.148.94.44:2379/dashboard Grafana URL: http://10.90.4.172:3000 ID Role Host Ports OS/Arch Status Data Dir Deploy Dir -- ---- ---- ----- ------- ------ -------- ---------- 10.90.4.172:9093 alertmanager 10.90.4.172 9093/9094 linux/x86_64 Up /tidb-data/alertmanager-9093 /tidb-deploy/alertmanager-9093 10.90.4.172:3000 grafana 10.90.4.172 3000 linux/x86_64 Up - /tidb-deploy/grafana-3000 10.90.3.139:2379 pd 10.90.3.139 2379/2380 linux/x86_64 Up|L /tidb-data/pd-2379 /tidb-deploy/pd-2379 10.90.4.172:9090 prometheus 10.90.4.172 9090/12020 linux/x86_64 Up /tidb-data/prometheus-9090 /tidb-deploy/prometheus-9090 10.90.2.230:4000 tidb 10.90.2.230 4000/10080 linux/x86_64 Up - /tidb-deploy/tidb-4000 10.90.2.75:4000 tidb 10.90.2.75 4000/10080 linux/x86_64 Up - /tidb-deploy/tidb-4000 10.90.3.102:20160 tikv 10.90.3.102 20160/20180 linux/x86_64 Up /tidb-data/tikv-20160 /tidb-deploy/tikv-20160 10.90.3.171:20160 tikv 10.90.3.171 20160/20180 linux/x86_64 Up /tidb-data/tikv-20160 /tidb-deploy/tikv-20160 10.90.3.56:20160 tikv 10.90.3.56 20160/20180 linux/x86_64 Up /tidb-data/tikv-20160 /tidb-deploy/tikv-20160 10.90.4.172:6000 tiproxy 10.90.4.172 6000/3080 linux/x86_64 Up - /tidb-deploy/tiproxy-6000 Total nodes: 10 ``` ### 步骤 4. 重新执行相同的`sysbench`测试工作负载，需2分钟完成 ```bash sysbench --config-file=sysbench_config oltp_read_only --tables=6 --table-size=1000000 run ``` ```output sysbench 1.0.17 (using bundled LuaJIT 2.1.0-beta2) Running the test with following options: Number of threads: 14 Report intermediate results every 5 second(s) Initializing random number generator from current time Initializing worker threads... Threads started! [ 5s ] thds: 14 tps: 1138.22 qps: 18237.17 (r/w/o: 15958.53/0.00/2278.65) lat (ms,95%): 14.21 err/s: 0.00 reconn/s: 0.00 [ 10s ] thds: 14 tps: 1128.66 qps: 18053.90 (r/w/o: 15796.19/0.00/2257.71) lat (ms,95%): 14.21 err/s: 0.00 reconn/s: 0.00 [ 15s ] thds: 14 tps: 1146.20 qps: 18341.64 (r/w/o: 16049.03/0.00/2292.60) lat (ms,95%): 13.95 err/s: 0.00 reconn/s: 0.00 [ 20s ] thds: 14 tps: 1133.40 qps: 18125.81 (r/w/o: 15859.41/0.00/2266.40) lat (ms,95%): 13.95 err/s: 0.00 reconn/s: 0.00 [ 25s ] thds: 14 tps: 1142.40 qps: 18288.81 (r/w/o: 16004.01/0.00/2284.80) lat (ms,95%): 13.95 err/s: 0.00 reconn/s: 0.00 [ 30s ] thds: 14 tps: 1138.60 qps: 18210.79 (r/w/o: 15933.19/0.00/2277.60) lat (ms,95%): 13.95 err/s: 0.00 reconn/s: 0.00 [ 35s ] thds: 14 tps: 1147.39 qps: 18363.10 (r/w/o: 16068.31/0.00/2294.79) lat (ms,95%): 13.95 err/s: 0.00 reconn/s: 0.00 [ 40s ] thds: 14 tps: 1138.80 qps: 18220.87 (r/w/o: 15943.26/0.00/2277.61) lat (ms,95%): 14.21 err/s: 0.00 reconn/s: 0.00 [ 45s ] thds: 14 tps: 1137.80 qps: 18204.20 (r/w/o: 15928.80/0.00/2275.40) lat (ms,95%): 14.21 err/s: 0.00 reconn/s: 0.00 [ 50s ] thds: 14 tps: 1149.21 qps: 18380.90 (r/w/o: 16082.68/0.00/2298.21) lat (ms,95%): 13.95 err/s: 0.00 reconn/s: 0.00 [ 55s ] thds: 14 tps: 1138.80 qps: 18227.03 (r/w/o: 15949.23/0.00/2277.80) lat (ms,95%): 13.95 err/s: 0.00 reconn/s: 0.00 [ 60s ] thds: 14 tps: 1144.20 qps: 18305.17 (r/w/o: 16016.77/0.00/2288.40) lat (ms,95%): 13.95 err/s: 0.00 reconn/s: 0.00 [ 65s ] thds: 14 tps: 1150.61 qps: 18409.75 (r/w/o: 16108.93/0.00/2300.82) lat (ms,95%): 13.95 err/s: 0.00 reconn/s: 0.00 [ 70s ] thds: 14 tps: 1146.38 qps: 18348.26 (r/w/o: 16054.91/0.00/2293.36) lat (ms,95%): 13.95 err/s: 0.00 reconn/s: 0.00 [ 75s ] thds: 14 tps: 1146.61 qps: 18336.94 (r/w/o: 16043.92/0.00/2293.02) lat (ms,95%): 13.70 err/s: 0.00 reconn/s: 0.00 [ 80s ] thds: 14 tps: 1140.80 qps: 18264.44 (r/w/o: 15982.63/0.00/2281.80) lat (ms,95%): 13.95 err/s: 0.00 reconn/s: 0.00 [ 85s ] thds: 14 tps: 1140.60 qps: 18242.54 (r/w/o: 15961.55/0.00/2280.99) lat (ms,95%): 13.95 err/s: 0.00 reconn/s: 0.00 [ 90s ] thds: 14 tps: 1141.40 qps: 18260.07 (r/w/o: 15977.06/0.00/2283.01) lat (ms,95%): 13.95 err/s: 0.00 reconn/s: 0.00 [ 95s ] thds: 14 tps: 1148.80 qps: 18390.78 (r/w/o: 16093.18/0.00/2297.60) lat (ms,95%): 13.95 err/s: 0.00 reconn/s: 0.00 [ 100s ] thds: 14 tps: 1136.80 qps: 18181.97 (r/w/o: 15908.38/0.00/2273.60) lat (ms,95%): 13.95 err/s: 0.00 reconn/s: 0.00 [ 105s ] thds: 14 tps: 1146.60 qps: 18341.07 (r/w/o: 16048.46/0.00/2292.61) lat (ms,95%): 13.95 err/s: 0.00 reconn/s: 0.00 [ 110s ] thds: 14 tps: 1142.60 qps: 18286.77 (r/w/o: 16001.37/0.00/2285.40) lat (ms,95%): 13.95 err/s: 0.00 reconn/s: 0.00 [ 115s ] thds: 14 tps: 1145.40 qps: 18321.19 (r/w/o: 16030.00/0.00/2291.20) lat (ms,95%): 13.70 err/s: 0.00 reconn/s: 0.00 [ 120s ] thds: 14 tps: 1147.79 qps: 18365.31 (r/w/o: 16069.92/0.00/2295.39) lat (ms,95%): 13.95 err/s: 0.00 reconn/s: 0.00 SQL statistics: queries performed: read: 1919484 write: 0 other: 274212 total: 2193696 transactions: 137106 (1142.42 per sec.) queries: 2193696 (18278.68 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.) General statistics: total time: 120.0126s total number of events: 137106 Latency (ms): min: 9.26 avg: 12.25 max: 224.92 95th percentile: 13.95 sum: 1679939.18 Threads fairness: events (avg/stddev): 9793.2857/474.34 execution time (avg/stddev): 119.9957/0.00 ``` ### 步骤 5. 观察改变的指标 + 再次从[Grafana Prometheus Dashboard](http://${LAB:HOST_MONITOR1_PUBLIC_IP}:3000)观察`TiDB CPU`指标，并点击面板中的`avg`角标。因为SQL层现在有两个`tidb`服务器，所以与先前的步骤相比，平均`TiDB CPU`消耗比例相应减少，单个`tidb`服务器的平均CPU消耗减至配置前的约50%。 ![56](https://lab-static.pingcap.com/images/2025/8/4/4684f7d17e7c92cb27e702f96d862cb450684a52.png)