黄东旭解析 TiDB 的核心优势
468
2024-03-30
创建资源组
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
从上图可以看到tpcc的估算容量是69768,而我使用无资源的用户最高可以跑到3000RU再乘以3个tidb,也才9000.
新建用户该属性为{}
绑定资源组后为{"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的信息。
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并未变化。
版权声明:本文内容由网络用户投稿,版权归原作者所有,本站不拥有其著作权,亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容,请联系我们jiasou666@gmail.com 处理,核实后本网站将在24小时内删除侵权内容。