## Module 3: Size The TiDB And TiKV Based On The Existing Workload ### Note + In this practice, you will use `sysbench` to apply a test the workload, which is a read workload with 18,000 QPS. ### Step 1. Prepare the `sysbench` environment + Open [Terminal 3](ssh://monitor1-ssh#3), connect to the TiUP Control Machine. + Set up the `sysbench` configuration. ```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 ``` ### Step 2. Apply the test workload + Start `sysbench` test. ```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 ``` ### Step 3. Analyze the baseline + Gather the QPS, latency and TiDB resource utilization: + QPS is: 19,508.27 per sec + P95 latency is: 13.95ms + Resource utilization. Switch to the Grafana Dashboard, find `tidb-demo-Performance-Overview - Performance Overview`, you can find `TiDB CPU` & `TiKV CPU/IO MBps`: + tidb CPU(Max): ≈ 6.1vc/8vc ≈ 76% + tikv CPU(Max): ≈ 1.5vc/8vc ≈ 19% + TiDB Cluster Sizing Tool Estimation result: + Size: tidb(8vc/16GiB) x 1, tikv(8vc/32GiB) x 3 + QPS: 18,000 + Resource utilization: tidb ≈ 95.24%, tikv ≈ 21.43% ![80%](https://lab-static.pingcap.com/quick-demo/demo-301-2-en.png) + We can see that the `sysbench` result is a little better than the tool's estimation. The sizing tool is built based on TiDB 6.x, we are testing by using TiDB tidb_version. ### Step 4. Scale out the TiDB SQL layer + Scale out the TiDB SQL layer by adding one TiDB server to SQL layer. Verify the QPS is increased. + Review the scale-out configuration file. ```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 ``` + Scale out one new 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 ``` + Check TiDB cluster. Verify there are two tidb-server in the TiDB cluster. ```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 ``` + Confirm that there are two TiDB servers connected to TiProxy now. Log in Grafana Dashboard, find `tidb-demo-TiProxy-Summary - Backend`, you can find there are two tidb-server in the `Ping Backend Duration` panel. ### Step 5. Scale out the sysbench workload + Increase `sysbench` threads to 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 ``` + Start `sysbench` test again. ```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 ``` ### Step 6. Analyze the result of scaling out + Check the QPS, latency and TiDB resource utilization: + QPS is: 36,770.39 per sec + P95 latency is: 14.73ms + Resource utilization. Switch to the Grafana Dashboard, find `tidb-demo-Performance-Overview - Performance Overview`, you can find `TiDB CPU` & `TiKV CPU/IO MBps`: + tidb CPU(Max): ≈ 6vc/8vc ≈ 75% + tikv CPU(Max): ≈ 2.8vc/8vc ≈ 35% + TiDB Cluster Sizing Tool Estimation result: + Size: tidb(8vc/16GiB) x 2, tikv(8vc/32GiB) x 3 + QPS: 36,000 + Resource utilization: tidb ≈ 95.24%, tikv ≈ 42.86% ![80%](https://lab-static.pingcap.com/quick-demo/demo-301-3-en.png) + We can see that the `sysbench` result is a little better than the tool's estimation. Note that the sizing tool is built based on TiDB 6.x, we are testing by using TiDB tidb_version.