v7.1.0 Resource Control功能测试与体验

集群信息

Cluster type: tidb Cluster name: tidb02 Cluster version: v7.1.0 Deploy user: tidb SSH type: builtin Dashboard URL: http://10.1.6.116:2379/dashboard Grafana URL: http://10.1.6.100:3000 ID Role Host Ports OS/Arch Status Data Dir Deploy Dir 10.1.6.100:9093 alertmanager 10.1.6.100 9093/9094 linux/x86_64 Up /data/tidb-data/alertmanager-9093 /data/tidb-deploy/alertmanager-9093 10.1.6.100:3000 grafana 10.1.6.100 3000 linux/x86_64 Up - /data/tidb-deploy/grafana-3000 10.1.6.116:2379 pd 10.1.6.116 2379/2380 linux/x86_64 Up|UI /data/tidb-data/pd-2379 /data/tidb-deploy/pd-2379 10.1.6.212:2379 pd 10.1.6.212 2379/2380 linux/x86_64 Up /data/tidb-data/pd-2379 /data/tidb-deploy/pd-2379 10.1.6.229:2379 pd 10.1.6.229 2379/2380 linux/x86_64 Up|L /data/tidb-data/pd-2379 /data/tidb-deploy/pd-2379 10.1.6.100:9090 prometheus 10.1.6.100 9090/12020 linux/x86_64 Up /data/tidb-data/prometheus-9090 /data/tidb-deploy/prometheus-9090 10.1.6.116:4000 tidb 10.1.6.116 4000/10080 linux/x86_64 Up - /data/tidb-deploy/tidb-4000 10.1.6.212:4000 tidb 10.1.6.212 4000/10080 linux/x86_64 Up - /data/tidb-deploy/tidb-4000 10.1.6.229:4000 tidb 10.1.6.229 4000/10080 linux/x86_64 Up - /data/tidb-deploy/tidb-4000 10.1.6.158:20160 tikv 10.1.6.158 20160/20180 linux/x86_64 Up /data/tidb-data/tikv-20160 /data/tidb-deploy/tikv-20160 10.1.6.196:20160 tikv 10.1.6.196 20160/20180 linux/x86_64 Up /data/tidb-data/tikv-20160 /data/tidb-deploy/tikv-20160 10.1.6.86:20160 tikv 10.1.6.86 20160/20180 linux/x86_64 Up /data/tidb-data/tikv-20160 /data/tidb-deploy/tikv-20160 Total nodes: 12

环境准备

创建资源组

CREATE RESOURCE GROUP IF NOT EXISTS rg100 RU_PER_SEC = 100; CREATE RESOURCE GROUP IF NOT EXISTS rg500 RU_PER_SEC = 500; CREATE RESOURCE GROUP IF NOT EXISTS rg1000 RU_PER_SEC = 1000; CREATE RESOURCE GROUP IF NOT EXISTS rg2000 RU_PER_SEC = 2000; CREATE RESOURCE GROUP IF NOT EXISTS rg3000 RU_PER_SEC = 3000;

创建用户

CREATE USER user100@% identified by user100; CREATE USER user500@% identified by user500; CREATE USER user1000@% identified by user1000; CREATE USER user2000@% identified by user2000; CREATE USER user3000@% identified by user3000;

绑定用户资源组

ALTER USER user100@% RESOURCE GROUP rg100; ALTER USER user500@% RESOURCE GROUP rg500; ALTER USER user1000@% RESOURCE GROUP rg1000; ALTER USER user2000@% RESOURCE GROUP rg2000; ALTER USER user3000@% RESOURCE GROUP rg3000;

给用户授权

GRANT ALL ON test.* TO user100@%; GRANT ALL ON test.* TO user500@%; GRANT ALL ON test.*TO user1000@%; GRANT ALL ON test.* TO user2000@%; GRANT ALL ON test.* TO user3000@%;

查看资源组

命令行查看

mysql> select * from INFORMATION_SCHEMA.RESOURCE_GROUPS; +---------+------------+----------+-----------+ | NAME | RU_PER_SEC | PRIORITY | BURSTABLE | +---------+------------+----------+-----------+ | default | UNLIMITED | MEDIUM | YES | | rg100 | 100 | MEDIUM | NO | | rg1000 | 1000 | MEDIUM | NO | | rg2000 | 2000 | MEDIUM | NO | | rg3000 | 3000 | MEDIUM | NO | | rg500 | 500 | MEDIUM | NO | +---------+------------+----------+-----------+ 6 rows in set (0.00 sec)

Dashboard查看

基准测试

使用tiup bench tpcc使用不同的用户压测

初始化数据

tiup bench tpcc --warehouses 4 --time 5m prepare -H 10.1.6.229 -P 4000 --user user100 -p user100

以下几种场景

tiup bench tpcc --warehouses 4 --time 5m run -H 10.1.6.229 -P 4000 --user user100 -p user100

tiup bench tpcc --warehouses 4 --time 5m run -H 10.1.6.229 -P 4000 --user user500 -p user500

tiup bench tpcc --warehouses 4 --time 5m run -H 10.1.6.229 -P 4000 --user user1000 -p user1000

tiup bench tpcc --warehouses 4 --time 5m run -H 10.1.6.229 -P 4000 --user user2000 -p user2000

tiup bench tpcc --warehouses 4 --time 5m run -H 10.1.6.229 -P 4000 --user user3000 -p user3000

每次的qps基本和RU_PER_SEC的数量一一对应

再测一下没有资源限制的用户

CREATE USER user@% identified by user; GRANT ALL ON test.* TO user@%;

tiup bench tpcc --warehouses 4 --time 5m run -H 10.1.6.229 -P 4000 --user user -p user

问题与建议

问题1：估算容量过大

从上图可以看到tpcc的估算容量是69768,而我使用无资源的用户最高可以跑到3000RU再乘以3个tidb，也才9000.

问题2：mysql.user User_attributes列的显示问题

新建用户该属性为{}

绑定资源组后为{"resource_group": "rg100"}

解绑资源组后又为{"resource_group": ""}

mysql> select user,host,User_attributes from mysql.user; +--------------------------+-------+------------------------------+ | user | host | User_attributes | +--------------------------+-------+------------------------------+ | root | % | NULL | | user100 | % | {"resource_group": "rg100"} | | user500 | % | {"resource_group": "rg500"} | | user1000 | % | {"resource_group": "rg1000"} | | user2000 | % | {"resource_group": "rg2000"} | | user3000 | % | {"resource_group": "rg3000"} | | user | % | {} | +--------------------------+-------+------------------------------+ 9 rows in set (0.00 sec) mysql> alter user user resource group rg100; Query OK, 0 rows affected (0.02 sec) mysql> select user,host,User_attributes from mysql.user; +--------------------------+-------+------------------------------+ | user | host | User_attributes | +--------------------------+-------+------------------------------+ | root | % | NULL | | user100 | % | {"resource_group": "rg100"} | | user500 | % | {"resource_group": "rg500"} | | user1000 | % | {"resource_group": "rg1000"} | | user2000 | % | {"resource_group": "rg2000"} | | user3000 | % | {"resource_group": "rg3000"} | | user | % | {"resource_group": "rg100"} | +--------------------------+-------+------------------------------+ 9 rows in set (0.01 sec) mysql> alter user user resource group ``; Query OK, 0 rows affected (0.02 sec) mysql> select user,host,User_attributes from mysql.user; +--------------------------+-------+------------------------------+ | user | host | User_attributes | +--------------------------+-------+------------------------------+ | root | % | NULL | | risk_control_center_user | % | {"resource_group": ""} | | user100 | % | {"resource_group": "rg100"} | | user500 | % | {"resource_group": "rg500"} | | user1000 | % | {"resource_group": "rg1000"} | | user2000 | % | {"resource_group": "rg2000"} | | user3000 | % | {"resource_group": "rg3000"} | | user | % | {"resource_group": ""} | +--------------------------+-------+------------------------------+

建议新建用户初始值、解绑资源后再还原设置为{"resource_group": "default"}

问题3：删除资源需先与用户解绑，如与多个用户绑定，只显示一个用户

mysql> alter user user resource group rg100; Query OK, 0 rows affected (0.02 sec) mysql> select user,host,User_attributes from mysql.user; +--------------------------+-------+------------------------------+ | user | host | User_attributes | +--------------------------+-------+------------------------------+ | root | % | NULL | | user100 | % | {"resource_group": "rg100"} | | user500 | % | {"resource_group": "rg500"} | | user1000 | % | {"resource_group": "rg1000"} | | user2000 | % | {"resource_group": "rg2000"} | | user3000 | % | {"resource_group": "rg3000"} | | user | % | {"resource_group": "rg100"} | +--------------------------+-------+------------------------------+ 9 rows in set (0.00 sec) mysql> select * from INFORMATION_SCHEMA.RESOURCE_GROUPS; +---------+------------+----------+-----------+ | NAME | RU_PER_SEC | PRIORITY | BURSTABLE | +---------+------------+----------+-----------+ | default | UNLIMITED | MEDIUM | YES | | rg100 | 100 | MEDIUM | NO | | rg1000 | 1000 | MEDIUM | NO | | rg2000 | 2000 | MEDIUM | NO | | rg3000 | 3000 | MEDIUM | NO | | rg500 | 500 | MEDIUM | NO | +---------+------------+----------+-----------+ 6 rows in set (0.00 sec) mysql> drop resource group rg100; ERROR 1105 (HY000): user [user] depends on the resource group to drop mysql> alter user user resource group ``; Query OK, 0 rows affected (0.02 sec) mysql> drop resource group rg100; ERROR 1105 (HY000): user [user100] depends on the resource group to drop

这里有两个用户使用到了rg100,删除时只显示了一个用户,该用户user解绑后，再删除才显示另一个用户user100。而且这里显示的用是只有用户名没有host的信息。

问题4：无法实时感知资源的变更

tiup bench tpcc --warehouses 4 --time 10m run -H 10.1.6.229 -P 4000 --user user100 -p user100

mysql> select user,host,User_attributes from mysql.user; +--------------------------+-------+------------------------------+ | user | host | User_attributes | +--------------------------+-------+------------------------------+ | root | % | NULL | | user100 | % | {"resource_group": "rg100"} | | user500 | % | {"resource_group": "rg500"} | | user1000 | % | {"resource_group": "rg1000"} | | user2000 | % | {"resource_group": "rg2000"} | | user3000 | % | {"resource_group": "rg3000"} | | user | % | {"resource_group": ""} | +--------------------------+-------+------------------------------+ 9 rows in set (0.00 sec) mysql> alter user user100 resource group rg3000; Query OK, 0 rows affected (0.02 sec) mysql> select user,host,User_attributes from mysql.user; +--------------------------+-------+------------------------------+ | user | host | User_attributes | +--------------------------+-------+------------------------------+ | root | % | NULL | | user100 | % | {"resource_group": "rg3000"} | | user500 | % | {"resource_group": "rg500"} | | user1000 | % | {"resource_group": "rg1000"} | | user2000 | % | {"resource_group": "rg2000"} | | user3000 | % | {"resource_group": "rg3000"} | | user | % | {"resource_group": ""} | +--------------------------+-------+------------------------------+ 9 rows in set (0.00 sec)

qps并未变化。