## Module 1: Prepare The Test Schema And Observe The Baseline ### Step 1. Connect to the TiUP control machine with two terminals + Open two SSH connections to your lab virtual machine, by clicking: + [Terminal 1](ssh://monitor1-ssh#1) + [Terminal 2](ssh://monitor1-ssh#2) + You will now have two active terminal sessions. ### Step 2. Deploy and start a TiDB cluster + In [Terminal 1](ssh://monitor1-ssh#1), review the provisioned cluster topology YAML. ```bash cat nine-nodes.yaml ``` ```output global: user: "ec2-user" ssh_port: 22 deploy_dir: "/tidb-deploy" data_dir: "/tidb-data" arch: "amd64" server_configs: pd: replication.max-replicas: 3 pd_servers: - host: 10.90.2.245 client_port: 2379 peer_port: 2380 advertise_client_addr: "http://13.212.249.174:2379" - host: 10.90.2.19 client_port: 2379 peer_port: 2380 advertise_client_addr: "http://13.212.113.189:2379" - host: 10.90.2.213 client_port: 2379 peer_port: 2380 advertise_client_addr: "http://13.229.88.9:2379" tidb_servers: - host: 10.90.1.92 port: 4000 status_port: 10080 deploy_dir: /tidb-deploy/tidb-4000 log_dir: /tidb-deploy/tidb-4000/log - host: 10.90.1.139 port: 4000 status_port: 10080 deploy_dir: /tidb-deploy/tidb-4000 log_dir: /tidb-deploy/tidb-4000/log tikv_servers: - host: 10.90.3.14 port: 20160 status_port: 20180 - host: 10.90.3.196 port: 20160 status_port: 20180 - host: 10.90.3.96 port: 20160 status_port: 20180 monitoring_servers: - host: 10.90.4.181 grafana_servers: - host: 10.90.4.181 alertmanager_servers: - host: 10.90.4.181 ``` + Fix system configurations by executing `tiup cluster check` with `--apply` option. > [!TIP] > You will notice some system check failures, but don't worry - TiUP will automatically resolve them for you. ```bash tiup mirror set tidb-community-server-${LAB:TIDB_VERSION}-linux-amd64 tiup cluster check nine-nodes.yaml \ --user ec2-user \ -i /home/ec2-user/.ssh/pe-class-key-${LAB:REGION_NAME}.pem \ --apply ``` ```output ... + Try to apply changes to fix failed checks + Try to apply changes to fix failed checks + Try to apply changes to fix failed checks - Applying changes on 10.90.1.139 ... ⠧ Sysctl: host=10.90.1.139 vm.swappiness = 0 - Applying changes on 10.90.4.181 ... ⠧ Shell: host=10.90.4.181, sudo=true, command=`if [ -d /sys/kernel/mm/transparent_hugepage ]; then echo never > /sys/kernel/mm/transparent_hugepage/enabled; fi` + Try to apply changes to fix failed checks + Try to apply changes to fix failed checks - Applying changes on 10.90.1.139 ... Done + Try to apply changes to fix failed checks + Try to apply changes to fix failed checks - Applying changes on 10.90.1.139 ... Done - Applying changes on 10.90.4.181 ... Done - Applying changes on 10.90.2.245 ... Done - Applying changes on 10.90.2.19 ... Done - Applying changes on 10.90.3.14 ... Done - Applying changes on 10.90.3.196 ... Done - Applying changes on 10.90.1.92 ... Done - Applying changes on 10.90.2.213 ... Done - Applying changes on 10.90.3.96 ... Done ``` + Deploy a TiDB cluster. ```bash tiup cluster deploy tidb-demo ${LAB:TIDB_VERSION} ./nine-nodes.yaml \ --user ec2-user \ -i /home/ec2-user/.ssh/pe-class-key-${LAB:REGION_NAME}.pem \ --yes ``` ```output ... Cluster `tidb-demo` deployed successfully, you can start it with command: `tiup cluster start tidb-demo --init` ``` + Start the deployed TiDB cluster. ```bash tiup cluster start tidb-demo ``` ```output ... Started cluster `tidb-demo` successfully ``` + Run the command to get the cluster status. There are 3 PD servers, 3 TiKV servers, and 2 TiDB servers. Your environment and the sample output will differ in the details of the IP addresses, but the subnets are the same. > [!TIP] > If you find any components in a `Down` state, wait a few seconds and run the display command again. ```bash 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://35.92.83.178:2379/dashboard Grafana URL: http://10.90.4.99:3000 ID Role Host Ports OS/Arch Status Data Dir Deploy Dir -- ---- ---- ----- ------- ------ -------- ---------- 10.90.4.99:9093 alertmanager 10.90.4.99 9093/9094 linux/x86_64 Up /tidb-data/alertmanager-9093 /tidb-deploy/alertmanager-9093 10.90.4.99:3000 grafana 10.90.4.99 3000 linux/x86_64 Up - /tidb-deploy/grafana-3000 10.90.3.142:2379 pd 10.90.3.142 2379/2380 linux/x86_64 Up /tidb-data/pd-2379 /tidb-deploy/pd-2379 10.90.3.218:2379 pd 10.90.3.218 2379/2380 linux/x86_64 Up|L /tidb-data/pd-2379 /tidb-deploy/pd-2379 10.90.3.78:2379 pd 10.90.3.78 2379/2380 linux/x86_64 Up /tidb-data/pd-2379 /tidb-deploy/pd-2379 10.90.4.99:9090 prometheus 10.90.4.99 9090/12020 linux/x86_64 Up /tidb-data/prometheus-9090 /tidb-deploy/prometheus-9090 10.90.1.152:4000 tidb 10.90.1.152 4000/10080 linux/x86_64 Up - /tidb-deploy/tidb-4000 10.90.1.192:4000 tidb 10.90.1.192 4000/10080 linux/x86_64 Up - /tidb-deploy/tidb-4000 10.90.2.183:20160 tikv 10.90.2.183 20160/20180 linux/x86_64 Up /tidb-data/tikv-20160 /tidb-deploy/tikv-20160 10.90.2.242:20160 tikv 10.90.2.242 20160/20180 linux/x86_64 Up /tidb-data/tikv-20160 /tidb-deploy/tikv-20160 10.90.2.93:20160 tikv 10.90.2.93 20160/20180 linux/x86_64 Up /tidb-data/tikv-20160 /tidb-deploy/tikv-20160 Total nodes: 11 ``` ### Step 3. Prepare the example workload schema + Review and run the script to prepare the workload schema. Schema setup `DDL` and `tidb-lightning` will take 1 minute to load the sample data. ```bash source ./hosts-env.sh mysql -h ${HOST_DB1_PRIVATE_IP} -uroot -P4000 --verbose << 'EOFX' CREATE USER ltask@'%' IDENTIFIED BY 'q1w2e3R4_'; GRANT ALL PRIVILEGES ON *.* TO ltask@'%'; DROP DATABASE IF EXISTS emp; CREATE DATABASE emp; USE emp; CREATE TABLE `sal` ( `emp_no` INT(11) NOT NULL, `salary` INT(11) NOT NULL, `from_date` DATE NOT NULL, `to_date` DATE NOT NULL, PRIMARY KEY (`emp_no`,`from_date`)); EOFX tiup install tidb-lightning:v${LAB:TIDB_VERSION} tiup tidb-lightning:v${LAB:TIDB_VERSION} -config solution-lightning-csv.toml ``` ```output ... +----+------------------------------------------------------------------------------------------------------------------------------------+-------------+--------+ | # | CHECK ITEM | TYPE | PASSED | +----+------------------------------------------------------------------------------------------------------------------------------------+-------------+--------+ | 1 | Source data files size is proper | performance | true | +----+------------------------------------------------------------------------------------------------------------------------------------+-------------+--------+ | 2 | the checkpoints are valid | critical | true | +----+------------------------------------------------------------------------------------------------------------------------------------+-------------+--------+ | 3 | table schemas are valid | critical | true | +----+------------------------------------------------------------------------------------------------------------------------------------+-------------+--------+ | 4 | all importing tables on the target are empty | critical | true | +----+------------------------------------------------------------------------------------------------------------------------------------+-------------+--------+ | 5 | Cluster version check passed | critical | true | +----+------------------------------------------------------------------------------------------------------------------------------------+-------------+--------+ | 6 | Lightning has the correct storage permission | critical | true | +----+------------------------------------------------------------------------------------------------------------------------------------+-------------+--------+ | 7 | sorted-kv-dir:/tmp/sorted-kv-dir and data-source-dir:/home/ec2-user/stage/TiDB-Administration-exercise-data-v2/exercise-lightning/ | performance | false | | | Importing-CSV-File are in the same disk, may slow down performance | | | +----+------------------------------------------------------------------------------------------------------------------------------------+-------------+--------+ | 8 | local disk resources are rich, estimate sorted data size 6.842MiB, local available is 62.19GiB | critical | true | +----+------------------------------------------------------------------------------------------------------------------------------------+-------------+--------+ | 9 | The storage space is rich, which TiKV/Tiflash is 186.3GiB/0B. The estimated storage space is 20.53MiB/0B. | performance | true | +----+------------------------------------------------------------------------------------------------------------------------------------+-------------+--------+ | 10 | Cluster doesn't have too many empty regions | performance | true | +----+------------------------------------------------------------------------------------------------------------------------------------+-------------+--------+ | 11 | Cluster region distribution is balanced | performance | true | +----+------------------------------------------------------------------------------------------------------------------------------------+-------------+--------+ | 12 | no CDC or PiTR task found | critical | true | +----+------------------------------------------------------------------------------------------------------------------------------------+-------------+--------+ tidb lightning exit successfully ``` + Review and run the script to prepare the `sysbench` schema. Wait for approximately 2 minutes until the setup process is complete. ```bash source ./hosts-env.sh mysql -h ${HOST_DB1_PRIVATE_IP} -uroot -P4000 --verbose << 'EOFX' CREATE DATABASE sbtest; CREATE USER 'userA'@'%' IDENTIFIED BY 'tidb'; CREATE USER 'userB'@'%' IDENTIFIED BY 'tidb'; GRANT ALL PRIVILEGES ON *.* TO 'userA'@'%'; GRANT ALL PRIVILEGES ON *.* TO 'userB'@'%'; EOFX sysbench '/usr/local/share/sysbench/oltp_read_only.lua' \ --mysql-host=${HOST_DB1_PRIVATE_IP} \ --mysql-port=4000 \ --mysql-user=root \ --mysql-db=sbtest \ --percentile=99 \ --threads=10 \ --tables=32 \ --table-size=100000 \ --report-interval=2 \ --db-driver=mysql prepare ``` ```output ... Initializing worker threads... Creating table 'sbtest4'... Creating table 'sbtest8'... ... Creating table 'sbtest5'... Inserting 100000 records into 'sbtest8' Inserting 100000 records into 'sbtest5' ... Inserting 100000 records into 'sbtest2' Creating a secondary index on 'sbtest5'... ... Creating a secondary index on 'sbtest10'... Creating table 'sbtest15'... Inserting 100000 records into 'sbtest15' Creating a secondary index on 'sbtest15'... Creating table 'sbtest19'... Inserting 100000 records into 'sbtest19' ... Creating table 'sbtest31'... Inserting 100000 records into 'sbtest31' Creating a secondary index on 'sbtest31'... ``` ### Step 4. Start the example workload + Review and run the script to start a sample OLTP workload by userA. Keep the script running and proceed to the next step. ```bash cat lab1-workload-userA.sh ``` ```output #!/bin/bash source ./hosts-env.sh sysbench '/usr/local/share/sysbench/oltp_read_only.lua' \ --mysql-host=${HOST_DB1_PRIVATE_IP} \ --mysql-port=4000 \ --mysql-user=userA \ --mysql-db=sbtest \ --percentile=99 \ --mysql-password=tidb \ --time=300 \ --threads=10 \ --tables=32 \ --table-size=100000 \ --report-interval=2 run ``` ```bash ./lab1-workload-userA.sh ``` ```output sysbench 1.0.17 (using bundled LuaJIT 2.1.0-beta2) Running the test with following options: Number of threads: 10 Report intermediate results every 2 second(s) Initializing random number generator from current time Initializing worker threads... Threads started! [ 2s ] thds: 10 tps: 330.67 qps: 5334.20 (r/w/o: 4668.36/0.00/665.84) lat (ms,99%): 77.19 err/s: 0.00 reconn/s: 0.00 [ 4s ] thds: 10 tps: 370.57 qps: 5916.63 (r/w/o: 5175.99/0.00/740.64) lat (ms,99%): 51.94 err/s: 0.00 reconn/s: 0.00 [ 6s ] thds: 10 tps: 370.49 qps: 5924.77 (r/w/o: 5183.80/0.00/740.97) lat (ms,99%): 49.21 err/s: 0.00 reconn/s: 0.00 [ 8s ] thds: 10 tps: 380.46 qps: 6093.80 (r/w/o: 5332.39/0.00/761.41) lat (ms,99%): 49.21 err/s: 0.00 reconn/s: 0.00 ... ``` + In [Terminal 2](ssh://monitor1-ssh#2), run the script to start the same OLTP workload by userB. Keep the script running and proceed to the next step. ```bash cat lab1-workload-userB.sh ``` ```output #!/bin/bash source ./hosts-env.sh sysbench '/usr/local/share/sysbench/oltp_read_only.lua' \ --mysql-host=${HOST_DB2_PRIVATE_IP} \ --mysql-port=4000 \ --mysql-user=userB \ --mysql-db=sbtest \ --percentile=99 \ --mysql-password=tidb \ --time=300 \ --threads=10 \ --tables=32 \ --table-size=100000 \ --report-interval=2 run ``` ```bash ./lab1-workload-userB.sh ``` ```output sysbench 1.0.17 (using bundled LuaJIT 2.1.0-beta2) Running the test with following options: Number of threads: 10 Report intermediate results every 2 second(s) Initializing random number generator from current time Initializing worker threads... Threads started! [ 2s ] thds: 10 tps: 277.17 qps: 4471.71 (r/w/o: 3913.37/0.00/558.34) lat (ms,99%): 116.80 err/s: 0.00 reconn/s: 0.00 [ 4s ] thds: 10 tps: 274.63 qps: 4390.51 (r/w/o: 3840.76/0.00/549.75) lat (ms,99%): 356.70 err/s: 0.00 reconn/s: 0.00 [ 6s ] thds: 10 tps: 232.94 qps: 3733.50 (r/w/o: 3267.63/0.00/465.88) lat (ms,99%): 189.93 err/s: 0.00 reconn/s: 0.00 [ 8s ] thds: 10 tps: 316.08 qps: 5051.22 (r/w/o: 4419.07/0.00/632.15) lat (ms,99%): 125.52 err/s: 0.00 reconn/s: 0.00 ... ``` ### Step 5. Verify the baseline + Verify that the QPS in [Terminal 1](ssh://monitor1-ssh#1) and [Terminal 2](ssh://monitor1-ssh#2) are similar. Because we did not apply any Resource Control measures so far, so `userA` and `userB` are in the same default resource group, and they are equal in their ability to compete for system resources. ![60%](https://lab-static.pingcap.com/quick-demo/demo-006-setup-v2-20250331.png) + Press Ctrl+C to stop both workloads before proceeding to the next module.