--- title: "SaaS Workshop - TBD (WIP)" version: "v2" status: "PLACEHOLDER" description: "This lab aims to instruct you on effectively managing resource groups within TiDB. It ensures that critical workloads receive the necessary resources while dynamically allocating resources to less critical tasks. The diagram below depicts the general workflow utilized by TiDB to determine the allocation of system resources to database sessions, focusing specifically on the perspective of resource groups. You will gain hands-on experience utilizing resource group attributes such as `RU_PER_SEC`, `PRIORITY`, and `BURSTABLE` throughout this demonstration." --- # SaaS Workshop - TBD (WIP) ## Overview This lab aims to instruct you on effectively managing resource groups within TiDB. It ensures that critical workloads receive the necessary resources while dynamically allocating resources to less critical tasks. The diagram below depicts the general workflow utilized by TiDB to determine the allocation of system resources to database sessions, focusing specifically on the perspective of resource groups. You will gain hands-on experience utilizing resource group attributes such as `RU_PER_SEC`, `PRIORITY`, and `BURSTABLE` throughout this demonstration. ![40%](https://lab-static.pingcap.com/quick-demo/QuickDemo006_en_001_20230512.png) ### Duration + This lab should take you approximately 120 minutes to complete. ### Tasks + Setup up the demo, and observe the baseline throughput of `userA` and `userB`. + Control the TiDB server run queue by managing the resource group request unit per seconds quota (controlled by `RU_PER_SEC` attribute). + Control the TiDB cluster scheduling queue by managing the resource group priorities (controlled by `PRIORITY` attribute). + Turn on `BURSTABLE` to serve both workloads with the best effort, while honoring the `PRIORITY` settings. ### Prerequisites > [!NOTE] > If you plan to use Web SSH in your lab, you can skip this section. Once the lab environment is ready, the Web SSH button will appear. And, click **Next** on the bottom bar to proceed to **Module 1**. + **macOS** users: + Click the private key file [pe-class-key-${LAB:REGION_NAME}.pem](/api/labs/demo_001/vm_key?region_name=${LAB:REGION_NAME}) to download it. This file is required to log in to the remote Linux for this hands-on lab. + Save the private key file from the download folder to `~/.ssh/` folder. + Use the following commands to set the permission and check whether the file is in place. ```bash chmod 400 ~/.ssh/pe-class-key-${LAB:REGION_NAME}.pem ls -lF ~/.ssh/pe-class-key-${LAB:REGION_NAME}.pem ``` ```output ... -r--------@ 1 username staff 1674 Jun 21 2023 /Users/username/.ssh/pe-class-key-${LAB:REGION_NAME}.pem ``` + **Windows 10** users: + Click the private key file [pe-class-key-${LAB:REGION_NAME}.pem](/api/labs/demo_001/vm_key?region_name=${LAB:REGION_NAME}) to download it. This file is required to log in to the remote Linux for this hands-on lab. + Save the private key file from the download folder in `%userprofile%\.ssh` folder. + Use the following command to check if the file is in place. Note that you should use CMD, not PowerShell. ```cmd dir %userprofile%\.ssh\pe-class-key-${LAB:REGION_NAME}.pem ``` ```output ... 2024/11/14 10:22 1,674 pe-class-key-${LAB:REGION_NAME}.pem 1 Files(s) 1,674 bytes ... ``` + **Windows 8** or earlier users: + Click the private key file [pe-class-key-${LAB:REGION_NAME}.ppk](/api/labs/demo_001/vm_key?region_name=${LAB:REGION_NAME}&key_type=ppk) to download it. This file is required to log in to the remote Linux for this hands-on lab. + Download and install [PuTTY](https://www.putty.org/). ## Module 1: Prepare The Test Schema And Observe The Baseline ### Step 1. Connect to the TiUP control machine with two terminals + If you use Web SSH for the lab, click `Web SSH` button or icon 2 times to open 2 terminal sessions, and proceed to **Step 2**. + Open 2 terminals, in each terminal SSH login to ${LAB:HOST_MONITOR1_PUBLIC_IP}. If you do not possess the credentials to connect, kindly review the `Prerequisites` section. + **macOS** users: ```bash ssh -i ~/.ssh/pe-class-key-${LAB:REGION_NAME}.pem ec2-user@${LAB:HOST_MONITOR1_PUBLIC_IP} ``` + **Windows 10** users: ```cmd ssh -i %userprofile%\.ssh\pe-class-key-${LAB:REGION_NAME}.pem ec2-user@${LAB:HOST_MONITOR1_PUBLIC_IP} ``` + **Windows 8** or earlier users: + Run PuTTY, in `Session` -> `Host Name`, type in `ec2-user@${LAB:HOST_MONITOR1_PUBLIC_IP}`. + In `Connection` -> `SSH` -> `Auth` -> `Credentials` -> `Private key file for authentication`, browse to find the location of your key file. + Now, you should be able to connect to the remote Linux. + The banner after you logged in: ```output , #_ ~\_ ####_ Amazon Linux 2 ~~ \_#####\ ~~ \###| ~~ \#/ ___ ~~ V~' '-> ~~~ / A newer version of Amazon Linux is available! ~~._. _/ _/ _/ Amazon Linux 2023, GA and supported until 2028-03-15. _/m/' https://aws.amazon.com/linux/amazon-linux-2023/ ``` ### Step 2. Deploy and start a TiDB cluster + 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 in order to deploy a TiDB cluster. ```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. ```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, 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** and **Terminal 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.png) + Press Ctrl+C to stop both workloads before proceeding to the next module. ## Module 2: Control The TiDB Server Run Queue By Using Request Unit Per Seconds Quota ![40%](https://lab-static.pingcap.com/quick-demo/demo-006-1-en.png) ### Step 1. Open 2 terminals. In each terminal, SSH login to TiUP control machine ```bash ssh -i ~/.ssh/pe-class-key-${LAB:REGION_NAME}.pem ec2-user@${LAB:HOST_MONITOR1_PUBLIC_IP} ``` ### Step 2. Set RU quota for users + Review and run the script to set up resource groups for `userA` and `userB` with a different quota on RU per seconds. ```bash cat lab1-rc-setup.sh ``` ```output #!/bin/bash source ./hosts-env.sh # For cluster export MYSQL_PS1="tidb:db1> " mysql -h ${HOST_DB1_PRIVATE_IP} -P 4000 -u root --verbose << 'EOFX' ALTER USER userA RESOURCE GROUP default; ALTER USER userB RESOURCE GROUP default; DROP RESOURCE GROUP IF EXISTS rg1; DROP RESOURCE GROUP IF EXISTS rg2; CREATE RESOURCE GROUP rg1 RU_PER_SEC = 500; CREATE RESOURCE GROUP rg2 RU_PER_SEC = 250; ALTER USER userA RESOURCE GROUP rg1; ALTER USER userB RESOURCE GROUP rg2; SELECT * FROM information_schema.resource_groups; SELECT Host, user, User_attributes FROM mysql.user WHERE user IN ('userA','userB'); EOFX ``` ```bash ./lab1-rc-setup.sh ``` ```output ... -------------- SELECT * FROM information_schema.resource_groups -------------- NAME RU_PER_SEC PRIORITY BURSTABLE QUERY_LIMIT BACKGROUND default UNLIMITED MEDIUM YES NULL NULL rg1 500 MEDIUM NO NULL NULL rg2 250 MEDIUM NO NULL NULL -------------- SELECT Host, User, User_attributes FROM mysql.user WHERE user IN ('userA','userB') -------------- Host User User_attributes % userA {"resource_group": "rg1"} % userB {"resource_group": "rg2"} ``` ### Step 3. Start the workloads + On **Terminal 1**, run the script to start an OLTP workload by `userA`. Keep the script running and proceed to the next step. ```bash ./lab1-workload-userA.sh ``` + On **Terminal 2**, run the script to start an OLTP workload by `userB`. Keep the script running and proceed to the next step. ```bash ./lab1-workload-userB.sh ``` ### Step 4. Observe the result + Wait for about 1 minute, observe that QPS for `userA` is higher than the QPS for `userB` because `userA` had been assigned with more `RU_PER_SEC` quota. ![60%](https://lab-static.pingcap.com/quick-demo/demo-006-lab-2-1.png) + Log in to the Grafana Dashboard with username and password as `admin/admin`, find `tidb-demo-TiDB-Resource-Control - Resource Unit - RU` panel and confirm `rg1` has higher `RU_PER_SEC` than rg2. ![60%](https://lab-static.pingcap.com/quick-demo/demo-006-lab-2.png) + Press Ctrl+C to stop both workloads from **Terminal 1** and **Terminal 2** before proceeding to the next module. ## Module 3: Control The TiDB Cluster Scheduling Queue By Using Resource Group Priorities ![40%](https://lab-static.pingcap.com/quick-demo/demo-006-2-en.png) ### Step 1. Open 2 terminals, in each terminal SSH login to TiUP control machine ```bash ssh -i ~/.ssh/pe-class-key-${LAB:REGION_NAME}.pem ec2-user@${LAB:HOST_MONITOR1_PUBLIC_IP} ``` ### Step 2. Set resource group priorities for users + On **Terminal 1**, review and run the script to set up resource groups for `userA` as `(priority:HIGH)` and `userB` as `(priority:MEDIUM)`. ```bash cat lab2-rc-setup.sh ``` ```output #!/bin/bash source ./hosts-env.sh # For cluster export MYSQL_PS1="tidb:db1> " mysql -h ${HOST_DB1_PRIVATE_IP} -P 4000 -u root --verbose << 'EOFX' ALTER USER userA RESOURCE GROUP default; ALTER USER userB RESOURCE GROUP default; DROP RESOURCE GROUP IF EXISTS rg1; DROP RESOURCE GROUP IF EXISTS rg2; CREATE RESOURCE GROUP rg1 RU_PER_SEC=50000 PRIORITY=HIGH; CREATE RESOURCE GROUP rg2 RU_PER_SEC=50000 PRIORITY=MEDIUM; ALTER USER userA RESOURCE GROUP rg1; ALTER USER userB RESOURCE GROUP rg2; SELECT * FROM information_schema.resource_groups; SELECT Host,user, User_attributes FROM mysql.user WHERE user IN ('userA','userB'); EOFX ``` ```bash ./lab2-rc-setup.sh ``` ```output ... -------------- SELECT * FROM information_schema.resource_groups -------------- NAME RU_PER_SEC PRIORITY BURSTABLE QUERY_LIMIT BACKGROUND default UNLIMITED MEDIUM YES NULL NULL rg1 50000 HIGH NO NULL NULL rg2 50000 MEDIUM NO NULL NULL -------------- SELECT Host, User, User_attributes FROM mysql.user WHERE user IN ('userA','userB') -------------- Host User User_attributes % userA {"resource_group": "rg1"} % userB {"resource_group": "rg2"} ``` ### Step 3. Start the workloads + On **Terminal 1**, run the script to start a batch workload by `userA` (no output). Keep the script running and proceed to the next step. ```bash ./lab2-workload-userA.sh ``` + On **Terminal 2**, run the script to start the same batch workload by userB (no output). Keep the script running and proceed to the next step. ```bash ./lab2-workload-userB.sh ``` #### Step 4. Observe the result. + Wait for 1 minute, log in to the Grafana Dashboard with the changed password, find `tidb-demo-TiDB-Resource-Control - Resource - KV CPU Time`, confirm that `rg1` is capable of consuming more `CPU Time` than `rg2`. ![60%](https://lab-static.pingcap.com/quick-demo/demo-006-lab-1.jpeg) + Press Ctrl+C to stop both workloads before proceeding to the next module. ## Module 4: Use Burstable Feature ![40%](https://lab-static.pingcap.com/quick-demo/demo-006-3-en.png) ### Step 1. Open 2 terminals, in each terminal SSH login to TiUP control machine ```bash ssh -i ~/.ssh/pe-class-key-${LAB:REGION_NAME}.pem ec2-user@${LAB:HOST_MONITOR1_PUBLIC_IP} ``` ### Step 2. Turn On BURSTABLE To Serve Both Workloads With The Best Effort + On **Terminal 1**, review and run the script to set up resource groups for `userA` and `userB` with low quotas on RU per seconds but burstable. The script gives `userA` a higher priority than `userB`. ```bash cat lab3-rc-setup.sh ``` ```output #!/bin/bash source ./hosts-env.sh # For cluster export MYSQL_PS1="tidb:db1> " mysql -h ${HOST_DB1_PRIVATE_IP} -P 4000 -u root --verbose << 'EOFX' ALTER USER userA RESOURCE GROUP default; ALTER USER userB RESOURCE GROUP default; DROP RESOURCE GROUP IF EXISTS rg1; DROP RESOURCE GROUP IF EXISTS rg2; CREATE RESOURCE GROUP rg1 RU_PER_SEC=500 PRIORITY=HIGH BURSTABLE; CREATE RESOURCE GROUP rg2 RU_PER_SEC=500 PRIORITY=MEDIUM BURSTABLE; ALTER USER userA RESOURCE GROUP rg1; ALTER USER userB RESOURCE GROUP rg2; SELECT * FROM information_schema.resource_groups; SELECT Host,user, User_attributes FROM mysql.user WHERE user IN ('userA','userB'); EOFX ``` ```bash ./lab3-rc-setup.sh ``` ```output ... -------------- SELECT * FROM information_schema.resource_groups -------------- NAME RU_PER_SEC PRIORITY BURSTABLE QUERY_LIMIT BACKGROUND default UNLIMITED MEDIUM YES NULL NULL rg1 500 HIGH YES NULL NULL rg2 500 MEDIUM YES NULL NULL -------------- SELECT Host,user, User_attributes FROM mysql.user WHERE user IN ('userA','userB') -------------- Host user User_attributes % userA {"resource_group": "rg1"} % userB {"resource_group": "rg2"} ``` ### Step 3. Start the workloads + On **Terminal 1**, run the script to start a batch workload by `userA` (no output), keep the script running. ```bash ./lab3-workload-userA.sh ``` + On **Terminal 2**, run the script to start the same batch workload by `userB` (no output), keep the script running. ```bash ./lab3-workload-userB.sh ``` ### Step 4. Observe the result + Wait for 1 minute, log in to the Grafana Dashboard with the changed password, find `tidb-demo-TiDB-Resource-Control - Resource - KV CPU Time`. Because of the `BURSTABLE` setting, the difference in `CPU Time` between them is not as obvious as in Module 3. ![60%](https://lab-static.pingcap.com/quick-demo/demo-006-lab-3.jpeg) + Press Ctrl+C to stop both workloads before proceeding to the next module. ## Module 5: Control The Performance Of Mixed Workloads With Resource Control ![40%](https://lab-static.pingcap.com/quick-demo/demo-006-4-en.png) ### Step 1. Open 2 terminals, in each terminal SSH login to TiUP control machine ```bash ssh -i ~/.ssh/pe-class-key-${LAB:REGION_NAME}.pem ec2-user@${LAB:HOST_MONITOR1_PUBLIC_IP} ``` ### Step 2. Reset the default resource group settings for users + On **Terminal 1**, review and run the script to reset `userA` and `userB` to the default resource group. ```bash cat lab4-rc-default.sh ``` ```output #!/bin/bash source ./hosts-env.sh # For cluster export MYSQL_PS1="tidb:db1> " mysql -h ${HOST_DB1_PRIVATE_IP} -P 4000 -u root --verbose << 'EOFX' ALTER USER userA RESOURCE GROUP default; ALTER USER userB RESOURCE GROUP default; DROP RESOURCE GROUP IF EXISTS rg1; DROP RESOURCE GROUP IF EXISTS rg2; SELECT * FROM information_schema.resource_groups; SELECT Host,user, User_attributes FROM mysql.user WHERE user IN ('userA','userB'); EOFX ``` ```bash ./lab4-rc-default.sh ``` ```output ... -------------- SELECT * FROM information_schema.resource_groups -------------- NAME RU_PER_SEC PRIORITY BURSTABLE QUERY_LIMIT BACKGROUND default UNLIMITED MEDIUM YES NULL NULL -------------- SELECT Host,user, User_attributes FROM mysql.user WHERE user IN ('userA','userB') -------------- Host user User_attributes % userA {"resource_group": "default"} % userB {"resource_group": "default"} ``` ### Step 3. Start the mixed workloads under the default resource group setting + On **Terminal 1**, run the script to start a sample OLTP workload by `userA`. Keep the script running and proceed to the next step. ```bash ./lab4-workload-userA.sh ``` + On **Terminal 2**, run the script to start a sample batch workload by `userB` (no output). Keep the script running and proceed to the next step. ```bash ./lab4-workload-userB.sh ``` ### Step 4. Observe the result under the default resource group setting + On **Terminal 1**, observe the QPS of userA's OLTP workload. Note that the performance is negatively impacted by userB's batch workload. ![60%](https://lab-static.pingcap.com/quick-demo/demo-006-lab-4-1.jpeg) + Press Ctrl+C to stop both workloads before proceeding to the next step. ### Step 5. Use Resource Group Settings to prioritize the selected workload + On **Terminal 1**, run the script to set up resource groups for `userA` as `(RU_PER_SEC=50000 PRIORITY=HIGH BURSTABLE)` and `userB` as `(RU_PER_SEC=500 PRIORITY=MEDIUM)`. ```bash cat lab4-rc-setup.sh ``` ```output #!/bin/bash source ./hosts-env.sh # For cluster export MYSQL_PS1="tidb:db1> " mysql -h ${HOST_DB1_PRIVATE_IP} -P 4000 -u root --verbose << 'EOFX' ALTER USER userA RESOURCE GROUP default; ALTER USER userB RESOURCE GROUP default; DROP RESOURCE GROUP IF EXISTS rg1; DROP RESOURCE GROUP IF EXISTS rg2; CREATE RESOURCE GROUP rg1 RU_PER_SEC=50000 PRIORITY=HIGH BURSTABLE; CREATE RESOURCE GROUP rg2 RU_PER_SEC=500 PRIORITY=MEDIUM; ALTER USER userA RESOURCE GROUP rg1; ALTER USER userB RESOURCE GROUP rg2; SELECT * FROM information_schema.resource_groups; SELECT Host,user, User_attributes FROM mysql.user WHERE user IN ('userA','userB'); EOFX ``` ```bash ./lab4-rc-setup.sh ``` ```output ... -------------- SELECT * FROM information_schema.resource_groups -------------- NAME RU_PER_SEC PRIORITY BURSTABLE QUERY_LIMIT BACKGROUND default UNLIMITED MEDIUM YES NULL NULL rg1 50000 HIGH YES NULL NULL rg2 500 MEDIUM NO NULL NULL -------------- SELECT Host,user, User_attributes FROM mysql.user WHERE user IN ('userA','userB') -------------- Host user User_attributes % userA {"resource_group": "rg1"} % userB {"resource_group": "rg2"} ``` ### Step 6. Start the mixed workloads again + On **Terminal 1**, run the script to start a sample OLTP workload again by `userA`, keep the script running. ```bash ./lab4-workload-userA.sh ``` + On **Terminal 2**, run the script to start a sample batch workload again by `userB` (no output), keep the script running. ```bash ./lab4-workload-userB.sh ``` ### Step 7. Observe the result + On **Terminal 1**, observe the QPS of userA's OLTP workload, after the QPS become stable, which may take up to 1 minute. Note that the performance is much better than the result from Step 4. ![60%](https://lab-static.pingcap.com/quick-demo/demo-006-lab-4-2.jpeg) + Press Ctrl+C on **Terminal 1** and **Terminal 2** to stop both workloads. ## Conclusion TiDB is a distributed relational database management system (RDBMS) capable of handling petabyte-scale data while ensuring strong consistency in transactions. By familiarizing yourself with TiDB's resource control feature through these practices, you can streamline maintenance tasks significantly by consolidating multiple databases from MySQL-compatible sources into a single TiDB cluster. By adhering to the demonstrated best practices, you can effectively manage resource groups within TiDB, ensuring that critical workloads receive necessary resources while allocating resources dynamically to less critical tasks. Through careful balancing of priority levels and quota limits, continuous monitoring of resource usage, and adjusting priorities as required, you can optimize resource allocation and enhance overall system performance. The lab ends here. If you want to learn more about TiDB Resource Control concept, please go to [TiDB Training and Certification](https://tidb.io/education) to learn more.