## 模块 3: 管理多租户环境的系统资源
本模块旨在指导你如何通过使用资源控制有效管理 TiDB 中的资源组。它确保不同的数据库用户获得受控且必要的资源。这样你就可以将不同用户拥有的工作负载分类为预先指定的优先级。TiDB 资源控制利用资源组属性如 `RU_PER_SEC`、`PRIORITY` 和 `BURSTABLE` 来帮助 DBA 实现系统资源控制，在本实验中我们将只使用 `RU_PER_SEC`。
### 步骤 1. 数据库多租户设置
+ 创建两个数据库用户作为两个租户。`t999` 用于访问数据库 `sbtest999`，`t1000` 用于访问数据库 `sbtest1000`。
+ 假设 `t1000` 和 `sbtest1000` 是高级租户配置，而 `t999` 和 `sbtest999` 是免费租户的配置。
```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.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
```
+ 点击这里 - [终端 2](ssh://monitor1-ssh#2) 打开一个新的终端，连接到为你的实践实验分配的虚拟机。
+ 现在，你应该有两个终端连接到远程 Linux。
### 步骤 2. 从两个连接启动示例工作负载并观察基准
+ 在 **终端 1** 和 **终端 2** 中启动示例工作负载。示例工作负载将运行约 1 分钟。
+ **复制** 以下命令到 [终端 1](ssh://monitor1-ssh#1) ，它将以用户 `t999` 的身份在数据库 `sbtest999` 上运行示例工作负载。先不要执行，切换到 [终端 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
```
+ **复制** 以下命令到 [终端 2](ssh://monitor1-ssh#2) ，它将以用户 `t1000` 的身份在数据库 `sbtest1000` 上运行示例工作负载。先不要执行。
```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
```
+ 现在两个终端都准备好执行相同的工作负载，在 [终端 1](ssh://monitor1-ssh#1) 上按 ENTER 键，然后快速切换到 [终端 2](ssh://monitor1-ssh#2) ，再次按 ENTER。
+ 工作负载将运行 1 分钟。比较两个终端的 sysbench 输出。查看 `tps` 和 `qps`，它们是相似的，类似于以下示例输出。
+ 在 [终端 1](ssh://monitor1-ssh#1) 中，事务约为 `79.95 每秒`，查询约为 `1598.91 每秒`。
```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
```
+ 在 [终端 2](ssh://monitor1-ssh#2) 中，事务约为 `80.57 每秒`，查询约为 `1611.43 每秒`。
```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
```
### 步骤 3. 为不同租户设置 RU 配额
+ 现在有一个有趣的事情。假设你的系统正处于压力之下。在决定横向扩展之前，你想给高级用户(如 t1000)更多的系统资源，给免费层租户(如 t999)更少的资源。
+ 在 [终端 1](ssh://monitor1-ssh#1) 中，连接到 TiDB 服务器实例。
```bash
mysql --prompt="mysql [\d] > " -h ${HOST_DB1_PRIVATE_IP} -P 4000 -u root
```
```output
mysql [(none)] >
```
+ 为用户 `t999` 和用户 `t1000` 设置资源组，对[Request Units (RU) per second](https://docs.pingcap.com/tidb/stable/tidb-resource-control-ru-groups/#what-is-request-unit-ru)设置不同的配额，`t999` 获得 `1000`，`t1000` 获得 `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)
```
### 步骤 4. 再次启动示例工作负载并观察结果
+ **复制** 以下命令到 [终端 1](ssh://monitor1-ssh#1) ，它将以用户 `t999` 的身份在数据库 `sbtest999` 上运行示例工作负载。先不要执行，切换到 [终端 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
```
+ **复制** 以下命令到 [终端 2](ssh://monitor1-ssh#2) ，它将以用户 `t1000` 的身份在数据库 `sbtest1000` 上运行示例工作负载。先不要执行。
```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
```
+ 现在两个终端都准备好执行相同的工作负载，在 [终端 1](ssh://monitor1-ssh#1) 上按 ENTER 键，然后快速切换到 [终端 2](ssh://monitor1-ssh#2) ，再次按 ENTER。
+ 比较两个终端的 sysbench 输出。查看 `tps` 和 `qps`，这次它们不会相同。
+ 在 [终端 1](ssh://monitor1-ssh#1) 中，免费层租户，事务约为 `20.41 每秒`，查询约为 `408.21 每秒`。
```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
```
+ 在 [终端 2](ssh://monitor1-ssh#2) 中，高级租户，事务约为 `82.60 每秒`，查询约为 `1652.09 每秒`。
```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
```
+ 以上是模块3 的内容。请记住，[终端 1](ssh://monitor1-ssh#1) 中的工作负载是故意被减慢的。除了 `RU_PER_SEC`，资源控制还为 DBA 提供了其他可配置的属性，如 `PRIORITY` 和 `BURSTABLE`。有关如何利用所有这些属性的更多信息，请访问 [利用资源控制功能管理数据库混合工作负载](/labs/demo_006)。请继续下一个模块。