## Module 3: Managing System Resources For A Multi-Tenancy Environment
This module aims to instruct you on effectively managing resource groups within TiDB by using Resource Control. It ensures that different database users receive controlled and necessary resources. So you can classify the workloads owned by different users to pre-specified priorities. TiDB Resource Control utilizes resource group attributes such as `RU_PER_SEC`, `PRIORITY`, and `BURSTABLE` to help DBAs to achieve the system resource controls, in this lab we will use `RU_PER_SEC` only.
### Step 1. Database multi-tenancy setup
+ Create two database users to act as two tenancies at the database level. `t999` for accessing schema `sbtest999`, `t1000` for accessing schema `sbtest1000`.
+ Let's suppose that `t1000` and `sbtest1000` is a premium tenant configuration, while `t999` and `sbtest999` is a configuration for a free tier tenant.
```sql
CREATE USER 't999'@'%' IDENTIFIED BY 'tidb';
CREATE USER 't1000'@'%' IDENTIFIED BY 'tidb';
GRANT ALL PRIVILEGES ON sbtest999.* TO 't999'@'%';
GRANT ALL PRIVILEGES ON sbtest1000.* TO 't1000'@'%';
EXIT;
```
```output
Query OK, 0 rows affected (0.02 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.02 sec)
Query OK, 0 rows affected (0.02 sec)
```
+ Click here - [Terminal 2](ssh://monitor1-ssh#2) to open a new terminal connected to the virtual machine assigned for your hands-on lab.
+ For now on, you should have two Terminals connected to the remove Linux.
### Step 2. Start the sample workload from both connections and observe the baseline
+ In this step, you will start the sample workload in both [Terminal 1](ssh://monitor1-ssh#1) and [Terminal 2](ssh://monitor1-ssh#2). The sample workload will run about 1 minute.
+ **COPY** the following command to [Terminal 1](ssh://monitor1-ssh#1), it will run a sample workload on schema `sbtest999` as user `t999`. Do NOT hit ENTER right now, switch to [Terminal 2](ssh://monitor1-ssh#2).
```bash
cd
clear
cat << EOF > t999_sysbench_config
mysql-host=${HOST_DB1_PRIVATE_IP}
mysql-port=4000
mysql-user=t999
mysql-password=tidb
mysql-db=sbtest999
time=60
threads=1
report-interval=1
EOF
sysbench oltp_read_write run --config-file=t999_sysbench_config --tables=10 --table_size=100
```
+ **COPY** the following command to [Terminal 2](ssh://monitor1-ssh#2), it will run a sample workload on schema `sbtest1000` as user `t1000`. Do NOT hit ENTER right now. Proceed to the next sub step.
```bash
cd
clear
cat << EOF > t1000_sysbench_config
mysql-host=${HOST_DB1_PRIVATE_IP}
mysql-port=4000
mysql-user=t1000
mysql-password=tidb
mysql-db=sbtest1000
time=60
threads=1
report-interval=1
EOF
sysbench oltp_read_write run --config-file=t1000_sysbench_config --tables=10 --table_size=100
```
+ Now that both terminals are ready to execute the same workload, press the ENTER key on [Terminal 1](ssh://monitor1-ssh#1), and quickly switch to [Terminal 2](ssh://monitor1-ssh#2), press ENTER again.
+ The workloads will run for 1 minute. Compare the sysbench output from both terminals. Look at the `tps` and `qps`, they are similar, for example, similar to the following sample output.
+ In [Terminal 1](ssh://monitor1-ssh#1), transactions are around `79.95 per sec`, queries are `1598.91 per sec`.
```output
...
[ 55s ] thds: 1 tps: 83.00 qps: 1655.99 (r/w/o: 1157.99/332.00/166.00) lat (ms,95%): 15.00 err/s: 0.00 reconn/s: 0.00
[ 56s ] thds: 1 tps: 74.00 qps: 1486.01 (r/w/o: 1042.01/296.00/148.00) lat (ms,95%): 16.12 err/s: 0.00 reconn/s: 0.00
[ 57s ] thds: 1 tps: 78.00 qps: 1551.99 (r/w/o: 1083.99/312.00/156.00) lat (ms,95%): 15.83 err/s: 0.00 reconn/s: 0.00
[ 58s ] thds: 1 tps: 77.00 qps: 1555.00 (r/w/o: 1092.00/309.00/154.00) lat (ms,95%): 15.55 err/s: 0.00 reconn/s: 0.00
[ 59s ] thds: 1 tps: 79.00 qps: 1567.00 (r/w/o: 1094.00/315.00/158.00) lat (ms,95%): 15.55 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 1 tps: 78.00 qps: 1557.99 (r/w/o: 1090.00/312.00/156.00) lat (ms,95%): 14.46 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 67172
write: 19192
other: 9596
total: 95960
transactions: 4798 (79.95 per sec.)
queries: 95960 (1598.91 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 60.0146s
total number of events: 4798
Latency (ms):
min: 8.86
avg: 12.51
max: 28.92
95th percentile: 15.83
sum: 60008.24
Threads fairness:
events (avg/stddev): 4798.0000/0.00
execution time (avg/stddev): 60.0082/0.00
```
+ In [Terminal 2](ssh://monitor1-ssh#2), transactions are around `80.57 per sec`, queries are `1611.43 per sec`.
```output
...
[ 55s ] thds: 1 tps: 81.00 qps: 1609.01 (r/w/o: 1124.01/323.00/162.00) lat (ms,95%): 14.73 err/s: 0.00 reconn/s: 0.00
[ 56s ] thds: 1 tps: 76.00 qps: 1534.04 (r/w/o: 1074.03/308.01/152.00) lat (ms,95%): 16.41 err/s: 0.00 reconn/s: 0.00
[ 57s ] thds: 1 tps: 83.00 qps: 1660.01 (r/w/o: 1162.01/332.00/166.00) lat (ms,95%): 14.21 err/s: 0.00 reconn/s: 0.00
[ 58s ] thds: 1 tps: 82.00 qps: 1639.99 (r/w/o: 1147.99/328.00/164.00) lat (ms,95%): 15.27 err/s: 0.00 reconn/s: 0.00
[ 59s ] thds: 1 tps: 87.00 qps: 1739.99 (r/w/o: 1217.99/348.00/174.00) lat (ms,95%): 13.70 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 1 tps: 84.00 qps: 1672.99 (r/w/o: 1172.99/332.00/168.00) lat (ms,95%): 17.01 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 67690
write: 19340
other: 9670
total: 96700
transactions: 4835 (80.57 per sec.)
queries: 96700 (1611.43 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 60.0074s
total number of events: 4835
Latency (ms):
min: 8.53
avg: 12.41
max: 35.83
95th percentile: 15.83
sum: 60000.84
Threads fairness:
events (avg/stddev): 4835.0000/0.00
execution time (avg/stddev): 60.0008/0.00
```
### Step 3. Set RU quota for different tenants
+ Here comes an interesting thing. Suppose that your system is under pressure. Before you decide to scale-out, you want to give more system resources to the premium users (such as t1000) and lesser to the free-tier tenants (such as t999).
+ In [Terminal 1](ssh://monitor1-ssh#1), connect to TiDB server instance.
```bash
mysql --prompt="mysql [\d] > " -h ${HOST_DB1_PRIVATE_IP} -P 4000 -u root
```
```output
mysql [(none)] >
```
+ Set up resource groups for user `t999` and user `t1000` with a different quota on [Request Units (RU) per second](https://docs.pingcap.com/tidb/stable/tidb-resource-control-ru-groups/#what-is-request-unit-ru), `t999` gets `1000` and `t1000` gets `20000`.
```sql
DROP RESOURCE GROUP IF EXISTS free_tier;
DROP RESOURCE GROUP IF EXISTS premium;
CREATE RESOURCE GROUP free_tier RU_PER_SEC = 1000;
CREATE RESOURCE GROUP premium RU_PER_SEC = 20000;
ALTER USER t999 RESOURCE GROUP free_tier;
ALTER USER t1000 RESOURCE GROUP premium;
SELECT * FROM information_schema.resource_groups;
SELECT Host, user, User_attributes FROM mysql.user WHERE user IN ('t999','t1000');
EXIT
```
```output
+-----------+------------+----------+-----------+-------------+------------+
| NAME | RU_PER_SEC | PRIORITY | BURSTABLE | QUERY_LIMIT | BACKGROUND |
+-----------+------------+----------+-----------+-------------+------------+
| default | UNLIMITED | MEDIUM | YES | NULL | NULL |
| free_tier | 1000 | MEDIUM | NO | NULL | NULL |
| premium | 20000 | MEDIUM | NO | NULL | NULL |
+-----------+------------+----------+-----------+-------------+------------+
3 rows in set (0.00 sec)
+------+-------+---------------------------------+
| Host | user | User_attributes |
+------+-------+---------------------------------+
| % | t999 | {"resource_group": "free_tier"} |
| % | t1000 | {"resource_group": "premium"} |
+------+-------+---------------------------------+
2 rows in set (0.00 sec)
```
### Step 4. Start the sample workloads again and observe the results
+ **COPY** the following command to [Terminal 1](ssh://monitor1-ssh#1), it will run a sample workload on schema `sbtest999` as user `t999`. Do NOT execute it, switch to [Terminal 2](ssh://monitor1-ssh#2).
```bash
clear
cat << EOF > t999_sysbench_config
mysql-host=${HOST_DB1_PRIVATE_IP}
mysql-port=4000
mysql-user=t999
mysql-password=tidb
mysql-db=sbtest999
time=60
threads=1
report-interval=1
EOF
sysbench oltp_read_write run --config-file=t999_sysbench_config --tables=10 --table_size=100
```
+ **COPY** the following command to [Terminal 2](ssh://monitor1-ssh#2), it will run a sample workload on schema `sbtest1000` as user `t1000`. Do NOT execute it.
```bash
clear
cat << EOF > t1000_sysbench_config
mysql-host=${HOST_DB1_PRIVATE_IP}
mysql-port=4000
mysql-user=t1000
mysql-password=tidb
mysql-db=sbtest1000
time=60
threads=1
report-interval=1
EOF
sysbench oltp_read_write run --config-file=t1000_sysbench_config --tables=10 --table_size=100
```
+ Now that both terminals are ready to execute the same workload, press the ENTER key on [Terminal 1](ssh://monitor1-ssh#1), and quickly switch to [Terminal 2](ssh://monitor1-ssh#2), press ENTER again.
+ Compare the sysbench output from both terminals. Look at the `tps` and `qps`, this time they will not be the same.
+ In [Terminal 1](ssh://monitor1-ssh#1), the free-tier tenant, the transactions are around `20.41 per sec`, queries are `408.21 per sec`.
```output
...
[ 55s ] thds: 1 tps: 20.00 qps: 403.00 (r/w/o: 282.00/81.00/40.00) lat (ms,95%): 54.83 err/s: 0.00 reconn/s: 0.00
[ 56s ] thds: 1 tps: 20.00 qps: 401.00 (r/w/o: 280.00/81.00/40.00) lat (ms,95%): 54.83 err/s: 0.00 reconn/s: 0.00
[ 57s ] thds: 1 tps: 20.00 qps: 384.00 (r/w/o: 266.00/78.00/40.00) lat (ms,95%): 55.82 err/s: 0.00 reconn/s: 0.00
[ 58s ] thds: 1 tps: 20.00 qps: 414.99 (r/w/o: 294.00/81.00/40.00) lat (ms,95%): 54.83 err/s: 0.00 reconn/s: 0.00
[ 59s ] thds: 1 tps: 19.00 qps: 383.01 (r/w/o: 266.00/79.00/38.00) lat (ms,95%): 55.82 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 1 tps: 20.00 qps: 400.00 (r/w/o: 280.00/80.00/40.00) lat (ms,95%): 55.82 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 17150
write: 4900
other: 2450
total: 24500
transactions: 1225 (20.41 per sec.)
queries: 24500 (408.21 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 60.0175s
total number of events: 1225
Latency (ms):
min: 11.11
avg: 48.99
max: 58.21
95th percentile: 55.82
sum: 60014.80
Threads fairness:
events (avg/stddev): 1225.0000/0.00
execution time (avg/stddev): 60.0148/0.00
```
+ In [Terminal 2](ssh://monitor1-ssh#2), the premium tenant, transactions are around `82.60 per sec`, queries are `1652.09 per sec`.
```output
...
[ 55s ] thds: 1 tps: 81.00 qps: 1617.00 (r/w/o: 1132.00/323.00/162.00) lat (ms,95%): 15.55 err/s: 0.00 reconn/s: 0.00
[ 56s ] thds: 1 tps: 82.00 qps: 1635.00 (r/w/o: 1143.00/328.00/164.00) lat (ms,95%): 14.21 err/s: 0.00 reconn/s: 0.00
[ 57s ] thds: 1 tps: 82.00 qps: 1645.00 (r/w/o: 1153.00/328.00/164.00) lat (ms,95%): 14.21 err/s: 0.00 reconn/s: 0.00
[ 58s ] thds: 1 tps: 89.00 qps: 1774.00 (r/w/o: 1240.00/356.00/178.00) lat (ms,95%): 12.98 err/s: 0.00 reconn/s: 0.00
[ 59s ] thds: 1 tps: 81.00 qps: 1622.00 (r/w/o: 1136.00/324.00/162.00) lat (ms,95%): 15.55 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 1 tps: 84.00 qps: 1685.00 (r/w/o: 1181.00/336.00/168.00) lat (ms,95%): 14.73 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 69398
write: 19828
other: 9914
total: 99140
transactions: 4957 (82.60 per sec.)
queries: 99140 (1652.09 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 60.0076s
total number of events: 4957
Latency (ms):
min: 8.78
avg: 12.10
max: 38.10
95th percentile: 15.27
sum: 60001.22
Threads fairness:
events (avg/stddev): 4957.0000/0.00
execution time (avg/stddev): 60.0012/0.00
```
+ That concludes Module 3. Keep in mind that the workload in [Terminal 1](ssh://monitor1-ssh#1) is intentionally slowed down. In addition to `RU_PER_SEC`, Resource Control offers other configurable attributes for DBAs, such as `PRIORITY` and `BURSTABLE`. For more information about how to leverage all of them please visit [Using Resource Control to Manage Mixed Workloads](/labs/demo_006). Please proceed to the next module.