作者： TiDBer_UutZcd5Y 原文来源：https://tidb.net/blog/24179946
集群信息
Cluster type: tidbCluster name: tidb02Cluster version: v7.1.0Deploy user: tidbSSH type: builtinDashboard URL: http://10.1.6.116:2379/dashboardGrafana URL: http://10.1.6.100:3000ID Role Host Ports OS/Arch Status Data Dir Deploy Dir10.1.6.100:9093 alertmanager 10.1.6.100 9093/9094 linux/x86_64 Up /data/tidb-data/alertmanager-9093 /data/tidb-deploy/alertmanager-909310.1.6.100:3000 grafana 10.1.6.100 3000 linux/x86_64 Up - /data/tidb-deploy/grafana-300010.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-237910.1.6.212:2379 pd 10.1.6.212 2379/2380 linux/x86_64 Up /data/tidb-data/pd-2379 /data/tidb-deploy/pd-237910.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-237910.1.6.100:9090 prometheus 10.1.6.100 9090/12020 linux/x86_64 Up /data/tidb-data/prometheus-9090 /data/tidb-deploy/prometheus-909010.1.6.116:4000 tidb 10.1.6.116 4000/10080 linux/x86_64 Up - /data/tidb-deploy/tidb-400010.1.6.212:4000 tidb 10.1.6.212 4000/10080 linux/x86_64 Up - /data/tidb-deploy/tidb-400010.1.6.229:4000 tidb 10.1.6.229 4000/10080 linux/x86_64 Up - /data/tidb-deploy/tidb-400010.1.6.158:20160 tikv 10.1.6.158 20160/20180 linux/x86_64 Up /data/tidb-data/tikv-20160 /data/tidb-deploy/tikv-2016010.1.6.196:20160 tikv 10.1.6.196 20160/20180 linux/x86_64 Up /data/tidb-data/tikv-20160 /data/tidb-deploy/tikv-2016010.1.6.86:20160 tikv 10.1.6.86 20160/20180 linux/x86_64 Up /data/tidb-data/tikv-20160 /data/tidb-deploy/tikv-20160Total 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 dropmysql> 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 并未变化。
评论