## 模块 3：基于现有工作负载规划 TiDB 和 TiKV 规模 ### 提示 + 在本次练习中，您将使用 `sysbench` 应用测试工作负载，这是一个具有 18,000 QPS 的读取工作负载。 ### 准备 `sysbench` 环境 + 打开 [终端 3](ssh://monitor1-ssh#3)，连接到 TiUP 控制机。 + 设置 `sysbench` 配置。 ```bash cat << EOF > sysbench_config mysql-host=${HOST_MONITOR1_PRIVATE_IP} mysql-port=6000 mysql-user=root mysql-password= mysql-db=sbtest time=120 threads=14 report-interval=5 EOF cat sysbench_config ``` ```output mysql-host=10.90.1.190 mysql-port=6000 mysql-user=root mysql-password= mysql-db=sbtest time=120 threads=14 report-interval=5 ``` ### 步骤 2. 应用测试工作负载 + 启动 `sysbench` 测试。 ```bash sysbench \ --config-file=sysbench_config oltp_read_only \ --tables=6 \ --table-size=10000000 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: 1203.22 qps: 19280.38 (r/w/o: 16871.13/0.00/2409.25) lat (ms,95%): 14.46 err/s: 0.00 reconn/s: 0.00 ... [ 120s ] thds: 14 tps: 1224.00 qps: 19581.56 (r/w/o: 17133.37/0.00/2448.20) lat (ms,95%): 13.95 err/s: 0.00 reconn/s: 0.00 SQL statistics: queries performed: read: 2048564 write: 0 other: 292652 total: 2341216 transactions: 146326 (1219.27 per sec.) queries: 2341216 (19508.27 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.) General statistics: total time: 120.0102s total number of events: 146326 Latency (ms): min: 9.18 avg: 11.48 max: 29.71 95th percentile: 13.95 sum: 1679913.22 Threads fairness: events (avg/stddev): 10451.8571/163.05 execution time (avg/stddev): 119.9938/0.00 ``` ### 步骤 3. 分析基线 + 收集 QPS、延迟和 TiDB 资源利用率： + QPS 为：19,508.27 每秒 + P95 延迟为：13.95ms + 资源利用率。切换到 Grafana Dashboard，找到 `tidb-demo-Performance-Overview - Performance Overview`，可以找到 `TiDB CPU` & `TiKV CPU/IO MBps`: + tidb CPU(Max): ≈ 6.1vc/8vc ≈ 76% + tikv CPU(Max): ≈ 1.5vc/8vc ≈ 19% + TiDB 容量估计工具预估结果： + 大小：tidb(8vc/16GiB) x 1, tikv(8vc/32GiB) x 3 + QPS: 18,000 + 资源利用率：tidb ≈ 95.24%，tikv ≈ 21.43% ![80%](https://lab-static.pingcap.com/quick-demo/demo-301-2-en.png) + 可以看到 `sysbench` 的结果比工具的估计要好一点。该调整工具是基于 TiDB 6.x 构建的，我们使用 TiDB tidb_version 进行测试。 ### 步骤 4. 扩展 TiDB SQL 层 + 通过向 SQL 层添加一个 TiDB server 来扩展 TiDB SQL 层。验证 QPS 是否增加。 + 查看扩容配置文件。 ```bash cat scale-out-tidb.yaml ``` ```output tidb_servers: - host: 10.90.2.188 port: 4000 status_port: 10080 deploy_dir: /tidb-deploy/tidb-4000 log_dir: /tidb-deploy/tidb-4000/log ``` + 扩展一个新的 tidb-server。 ```bash ~/.tiup/bin/tiup cluster scale-out tidb-demo scale-out-tidb.yaml \ --user ec2-user \ -i ~/.ssh/pe-class-key-${LAB:REGION_NAME}.pem \ -y ``` ```output ... Scaled cluster `tidb-demo` out successfully ``` + 检查 TiDB 集群。验证 TiDB 集群中有两个 tidb-server。 ```bash ~/.tiup/bin/tiup cluster display tidb-demo ``` ```output Cluster type: tidb Cluster name: tidb-demo Cluster version: v8.1.0 Deploy user: ec2-user SSH type: builtin Dashboard URL: http://35.89.229.27:2379/dashboard Grafana URL: http://10.90.4.39:3000 ID Role Host Ports OS/Arch Status Data Dir Deploy Dir -- ---- ---- ----- ------- ------ -------- ---------- 10.90.4.39:9093 alertmanager 10.90.4.39 9093/9094 linux/x86_64 Up /tidb-data/alertmanager-9093 /tidb-deploy/alertmanager-9093 10.90.4.39:3000 grafana 10.90.4.39 3000 linux/x86_64 Up - /tidb-deploy/grafana-3000 10.90.2.182:2379 pd 10.90.2.182 2379/2380 linux/x86_64 Up|L /tidb-data/pd-2379 /tidb-deploy/pd-2379 10.90.4.39:9090 prometheus 10.90.4.39 9090/12020 linux/x86_64 Up /tidb-data/prometheus-9090 /tidb-deploy/prometheus-9090 10.90.2.253:4000 tidb 10.90.2.253 4000/10080 linux/x86_64 Up - /tidb-deploy/tidb-4000 10.90.2.92:4000 tidb 10.90.2.92 4000/10080 linux/x86_64 Up - /tidb-deploy/tidb-4000 10.90.3.122:20160 tikv 10.90.3.122 20160/20180 linux/x86_64 Up /tidb-data/tikv-20160 /tidb-deploy/tikv-20160 10.90.3.153:20160 tikv 10.90.3.153 20160/20180 linux/x86_64 Up /tidb-data/tikv-20160 /tidb-deploy/tikv-20160 10.90.3.5:20160 tikv 10.90.3.5 20160/20180 linux/x86_64 Up /tidb-data/tikv-20160 /tidb-deploy/tikv-20160 10.90.4.39:6000 tiproxy 10.90.4.39 6000/3080 linux/x86_64 Up - /tidb-deploy/tiproxy-6000 Total nodes: 10 ``` + 确认现在有两个 TiDB server 连接到 TiProxy。 登录 Grafana Dashboard，找到 `tidb-demo-TiProxy-Summary - Backend`，在`Ping Backend Duration` 面板中可以看到有两个 tidb-server。 ### 步骤 5. 扩展 sysbench 工作负载 + 将 `sysbench` 线程数增加到 28。 ```bash cat << EOF > sysbench_config mysql-host=${HOST_MONITOR1_PRIVATE_IP} mysql-port=6000 mysql-user=root mysql-password= mysql-db=sbtest time=120 threads=28 report-interval=5 EOF cat sysbench_config ``` ```output mysql-host=10.90.1.190 mysql-port=6000 mysql-user=root mysql-password= mysql-db=sbtest time=120 threads=28 report-interval=5 ``` + 再次启动 `sysbench` 测试。 ```bash sysbench \ --config-file=sysbench_config oltp_read_only \ --tables=6 \ --table-size=10000000 run ``` ```output sysbench 1.0.17 (using bundled LuaJIT 2.1.0-beta2) Running the test with following options: Number of threads: 28 Report intermediate results every 5 second(s) Initializing random number generator from current time Initializing worker threads... Threads started! [ 5s ] thds: 28 tps: 2272.50 qps: 36408.77 (r/w/o: 31858.37/0.00/4550.40) lat (ms,95%): 15.00 err/s: 0.00 reconn/s: 0.00 ... [ 120s ] thds: 28 tps: 2301.17 qps: 36810.07 (r/w/o: 32208.13/0.00/4601.93) lat (ms,95%): 14.73 err/s: 0.00 reconn/s: 0.00 SQL statistics: queries performed: read: 3861368 write: 0 other: 551624 total: 4412992 transactions: 275812 (2298.15 per sec.) queries: 4412992 (36770.39 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.) General statistics: total time: 120.0136s total number of events: 275812 Latency (ms): min: 9.41 avg: 12.18 max: 41.35 95th percentile: 14.73 sum: 3359870.50 Threads fairness: events (avg/stddev): 9850.4286/324.02 execution time (avg/stddev): 119.9954/0.00 ``` ### 步骤 6. 分析扩容结果 + 检查 QPS、延迟和 TiDB 资源利用率： + QPS 为：每秒 36,770.39 次 + P95 延迟为：14.73 毫秒 + 资源利用率。切换到 Grafana Dashboard，找到 `tidb-demo-Performance-Overview - Performance Overview`，可以看到 `TiDB CPU` 和 `TiKV CPU/IO MBps`: + tidb CPU(Max): ≈ 6vc/8vc ≈ 75% + tikv CPU(Max): ≈ 2.8vc/8vc ≈ 35% + TiDB Cluster Sizing Tool 预估结果: + Size: tidb(8vc/16GiB) x 2, tikv(8vc/32GiB) x 3 + QPS: 36,000 + 资源利用率: tidb ≈ 95.24%, tikv ≈ 42.86% ![80%](https://lab-static.pingcap.com/quick-demo/demo-301-3-en.png) + 我们可以看到 `sysbench` 的结果比工具的估计要好一点。请注意，该调整工具是基于 TiDB 6.x 构建的，我们使用 TiDB tidb_version 进行测试。