# Lab 14: Using Resource Control with Mixed Workloads
### Overview
In this exercises, you use the recourse control to manage resource usage of two workloads. The following shows the deployment topology diagram of TiDB cluster.
### Notes
+ Click the private key file [pe-class-key-oregon.pem](lab-controller/class_303_get_ec2_key_for_lab.php?region=oregon) to download it. It is required to log into the Control Machine for this lab.
+ Commands and SQL statements presented on this page are clickable for easy copy and paste.
+ The required IP addresses for specific commands will be substituted with the actual ones automatically once you have set up the lab environment after Step 1.
+ Your environment may be different with these exercises, including but not limited to IP, port, username, password and directory. Example output is displayed in a gray background in this guide.
+ The `$` in your terminal window is an OS prompt. The `tidb>` or `mysql>` in your terminal window is SQL command prompt.
### Assumptions
+ You have downloaded and secured the TiUP Control Machine private key (for example the file is saved under `~/.ssh/` folder locally): `chmod 400 [~/.ssh/pe-class-key-oregon.pem](lab-controller/class_303_get_ec2_key_for_lab.php?region=oregon)`
+ Windows users please refer to this [guide](https://github.com/pingcap/tidb-course-201-lab/blob/master/README_303_v65_en_class_setup_guide_for_student.md) for private key preparation.
## Exercise 1
### Subject: Setup and Observe the Baseline
### Overview
In this exercise, you setup the demo my importing the Sysbench data. You also observe the throughput baseline of workload 1 and 2, without resource control.
### Duration
+ This exercise should take you approximately 20 minutes to complete.
### Tasks
1. Setup up the demo, and observe the baseline throughput of userA and userB.
### Solution
1. Open two terminals, login as user ec2-user on monitor1_address. Enter the following command at the terminal prompt and receive the results shown. If you do not possess the credentials to connect, kindly review the Assumptions section under the OVERVIEW tab. The returned message may be slightly different from what is displayed here. If prompted, answer `yes`.
```command
$ ssh -i ~/.ssh/pe-class-key-oregon.pem ec2-user@monitor1_address
```
```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/
```
2. In any terminal, upgrade the existing TiDB from v6.5.1 to v7.5.1.
```command
$ tiup mirror set tidb-community-server-7.5.1-linux-amd64
$ tiup cluster upgrade tidb-test v7.5.1
```
3. In any terminal, extract the data directories archive from `tidb-admin-dataset.zip` to `/home/ec2-user/stage/` on node `monitor1`. You may skip this step, if you have done so eariler.
```command
unzip -u stage/tidb-admin-dataset.zip -d stage/
```
4. In terminal 1, run the script to prepare the sysbench schema:
```command
$ mysql -h ${HOST_DB1_PRIVATE_IP} -uroot -P4000 --verbose
```
```command
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'@'%';
```
```command
exit;
```
```command
$ 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
```
5. In terminal 1, run the script to start a sample workload by userA:
```command
$ 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
```
6. At the almost the same time, in terminal 2, run the script to start the same workload by userB:
```command
$ 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
```
7. Verify the QPS in terminal 1 and 2 are similar. Because we did not apply any Resource Control measures so far, so userA and userB are equal on competing TiDB resources. Note that the performance numbers shown in the figure below are indicative only. The virtual machine types used in the lab may be different from the ones used when making the screen shot.
8. Press CTRL + C to stop both workloads before proceeding to the next section.
## Exercise 2
### Subject: Control the TiDB server run queue by managing the resource group request unit per seconds quota
### Overview
In this exercise, you control the TiDB server run queue by managing the resource group request unit per seconds quota
### Duration
+ This exercise should take you approximately 20 minutes to complete.
### Tasks
+ Control the TiDB server run queue by managing the resource group request unit per seconds quota.
### Solution
1. In terminal 1, run the script to set up resource groups for userA and userB with a different quota on RU per seconds:
```command
$ mysql -h ${HOST_DB1_PRIVATE_IP} -P 4000 -u root --verbose
```
```command
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');
```
```command
exit;
```
2. In terminal 1, run the script to start an OLTP workload by userA, keep the script running:
```command
$ 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
```
3. At almost the same time, in terminal 2, run the script to start an OLTP workload by userB, keep the script running:
```command
$ 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
```
4. Observe that QPS for userA is higher than the QPS for userB because userA had been assigned with more RU_PER_SEC quota. Note that the performance numbers shown in the figure below are indicative only. The virtual machine types used in the lab may be different from the ones used when making the screen shot.
5. Log in to the Grafana Dashboard with admin/admin, find tidb-demo-TiDB-Resource-Control -> Resource Unit -> RU panel and confirm rg1 has higher RU_PER_SEC than rg2. Note that the performance numbers shown in the figure below are indicative only. The virtual machine types used in the lab may be different from the ones used when making the screen shot.
Due to the hardware limitation, we will not be able to demo the following:
(1) Control the TiDB cluster scheduling queue by managing the resource group priorities.
(2) Turn on BURSTABLE to serve both workloads with the best effort, while honoring the PRIORITY settings.
If you are interested in those two demos, you may consider join our Kickstart Workshops. Please talk to the instructor, or your sales.
## End of Exercises