---
title: "Using Resource Control with Mixed Workloads"
version: "v2"
status: "GA"
description: "In this hands-on lab, you will learn how to use resource control with mixed workloads. Specifically, we will cover how to control the TiDB server run queue by managing the resource group request unit per seconds quota."
---
# Lab 5: Using Resource Control with Mixed Workloads
## Overview
>[!NOTE]
> This hands-on lab is also included in the [TiDB Certification Training: TiDB Administration Using TiUP](https://www.pingcap.com/education/course-tidb-database-administration-for-self-hosted-deployments/) course.
In this lab, you use the recourse control to manage resource usage of two workloads. The following shows the deployment topology diagram of TiDB cluster.
> [!TIP]
> Click **Start** to launch the lab environment.
> Once the environment is ready, select **Next** on the bottom toolbar to advance to **Module 1**.
> And, follow the step-by-step lab guide to connect to the hands-on environment and begin your tasks.
## Module 1: Setup and Observe the Baseline
### Step 1. Log into the remote Linux
+ 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 twp active terminal sessions.
+ The banner displayed after logging in appears as follows.
```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. Prepare the data
+ In any terminal, extract the data directories archive from `tidb-admin-dataset.zip` to `/home/ec2-user/stage/` on node `monitor1`.
```bash
unzip -u stage/tidb-admin-dataset-v2.zip -d stage/
```
### Step 3. In terminal 1, prepare `sysbench` schema
+ In [Terminal 1](ssh://monitor1-ssh#1), run the script to prepare the sysbench schema:
```bash
mysql -h ${HOST_DB1_PRIVATE_IP} -uroot -P4000 --verbose
```
```sql
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'@'%';
```
```sql
exit;
```
```bash
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
```
### Step 4. In terminal 1, start a sample workload by userA
+ Please run step 4 and step 5 at almost the same time.
```bash
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
```
### Step 5. In terminal 2, start the same workload by userB
```bash
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
```
### Step 6. In both terminals, check the QPS
+ Verify the QPS in [Terminal 1](ssh://monitor1-ssh#1) and [Terminal 2](ssh://monitor1-ssh#2) are similar. Since we did not apply any Resource Control measures so far, 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.
### Step 7. In both terminals, terminate the workload
+ Press CTRL + C to stop both workloads before proceeding to the next section.
## Module 2: Control the TiDB server run queue
### Step 1. In terminal 1, set up the resource group
+ In [Terminal 1](ssh://monitor1-ssh#1), run the script to set up resource groups for userA and userB with a different quota on RU per seconds:
```bash
mysql -h ${HOST_DB1_PRIVATE_IP} -P 4000 -u root --verbose
```
```sql
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');
```
```sql
exit;
```
### Step 2. In terminal 1, start a sample workload by userA
+ Please run step 2 and step 3 at almost the same time.
```bash
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
```
### Step 3. In terminal 2, start the same workload by userB
```bash
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
```
### Step 4. In both terminals, check the QPS
+ 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.
### Step 5. Check resource usage on Grafana Dashboard
+ Log in to the [Grafana Prometheus Dashboard](http://${LAB:HOST_MONITOR1_PUBLIC_IP}:3000) 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.
### Step 6. In any terminal, delete the resource groups and users
```bash
mysql -h ${HOST_DB1_PRIVATE_IP} -P 4000 -u root --verbose
```
```sql
DROP USER userA;
DROP USER userB;
DROP RESOURCE GROUP IF EXISTS rg1;
DROP RESOURCE GROUP IF EXISTS rg2;
```
## End of Exercises