作者： weixiaobing
1、集群信息
[tidb@vm116 ~]$ tiup cluster display tidb-prd
tiup is checking updates for component cluster ...
Starting component `cluster`: /home/tidb/.tiup/components/cluster/v1.12.2/tiup-cluster display tidb-prd
Cluster type: tidb
Cluster name: tidb-prd
Cluster version: v7.1.0
Deploy user: tidb
SSH type: builtin
Dashboard URL: http://10.2.103.116:32379/dashboard
Grafana URL: http://10.2.103.116:5000
ID Role Host Ports OS/Arch Status Data Dir Deploy Dir
-- ---- ---- ----- ------- ------ -------- ----------
10.2.103.116:9793 alertmanager 10.2.103.116 9793/9794 linux/x86_64 Up /data1/tidb-data/alertmanager-9793 /data1/tidb-deploy/alertmanager-9793
10.2.103.116:5000 grafana 10.2.103.116 5000 linux/x86_64 Up - /data1/tidb-deploy/grafana-5000
10.2.103.116:32379 pd 10.2.103.116 32379/3380 linux/x86_64 Up|L|UI /data1/tidb-data/pd-32379 /data1/tidb-deploy/pd-32379
10.2.103.116:9390 prometheus 10.2.103.116 9390/32020 linux/x86_64 Up /data1/tidb-data/prometheus-9390 /data1/tidb-deploy/prometheus-9390
10.2.103.116:43000 tidb 10.2.103.116 43000/20080 linux/x86_64 Up - /data1/tidb-deploy/tidb-34000
10.2.103.116:9300 tiflash 10.2.103.116 9300/9123/4930/30170/30292/8234 linux/x86_64 Up /data1/tidb-data/tiflash-9300 /data1/tidb-deploy/tiflash-9300
10.2.103.116:30160 tikv 10.2.103.116 30160/30180 linux/x86_64 Up /data1/tidb-data/tikv-30160 /data1/tidb-deploy/tikv-30160
Total nodes: 7
[tidb@vm116 ~]$
相关参数
mysql> show variables like '%tidb_enable_resource_control%';
+------------------------------+-------+
| Variable_name | Value |
+------------------------------+-------+
| tidb_enable_resource_control | ON |
+------------------------------+-------+
1 row in set (0.00 sec)
mysql> show config where name like '%resource-control%';
+---------+--------------------+------------------------------------------+-------+
| Type | Instance | Name | Value |
+---------+--------------------+------------------------------------------+-------+
| tikv | 10.2.103.116:30160 | resource-control.enabled | true |
| tiflash | 10.2.103.116:4930 | raftstore-proxy.resource-control.enabled | true |
+---------+--------------------+------------------------------------------+-------+
2 rows in set (0.01 sec)
mysql>
预估集群容量
mysql> CALIBRATE RESOURCE;
+-------+
| QUOTA |
+-------+
| 23256 |
+-------+
1 row in set (0.01 sec)
mysql> CALIBRATE RESOURCE WORKLOAD OLTP_WRITE_ONLY;
+-------+
| QUOTA |
+-------+
| 36592 |
+-------+
1 row in set (0.00 sec)
mysql> CALIBRATE RESOURCE WORKLOAD OLTP_READ_ONLY;
+-------+
| QUOTA |
+-------+
| 6985 |
+-------+
1 row in set (0.00 sec)
mysql> CALIBRATE RESOURCE WORKLOAD OLTP_READ_WRITE;
+-------+
| QUOTA |
+-------+
| 14886 |
+-------+
1 row in set (0.00 sec)
mysql>
mysql> CALIBRATE RESOURCE START_TIME '2023-06-07 10:40:00' DURATION '20m';
ERROR 1105 (HY000): There are too few metrics points available in selected time window
mysql>
当负载太小时，可能不能正确的估算出容量
调整负载后，可以正常估算容量
管理资源组
创建资源组
mysql> CREATE RESOURCE GROUP IF NOT EXISTS read_write RU_PER_SEC = 5000 BURSTABLE;
Query OK, 0 rows affected (0.21 sec)
mysql> CREATE RESOURCE GROUP IF NOT EXISTS read_write_no RU_PER_SEC = 5000 ;
Query OK, 0 rows affected (0.14 sec)
mysql> CREATE RESOURCE GROUP IF NOT EXISTS read RU_PER_SEC = 2000 ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your TiDB version for the right syntax to use line 1 column 40 near "read RU_PER_SEC = 2000"
mysql> CREATE RESOURCE GROUP IF NOT EXISTS read_only RU_PER_SEC = 2000;
Query OK, 0 rows affected (0.17 sec)
mysql> CREATE RESOURCE GROUP IF NOT EXISTS write_only RU_PER_SEC = 12000;
Query OK, 0 rows affected (0.18 sec)
mysql> CREATE RESOURCE GROUP IF NOT EXISTS write RU_PER_SEC = 12000;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your TiDB version for the right syntax to use line 1 column 41 near "write RU_PER_SEC = 12000"
mysql>mysql> select * from information_schema.RESOURCE_GROUPS;
+---------------+------------+----------+-----------+
| NAME | RU_PER_SEC | PRIORITY | BURSTABLE |
+---------------+------------+----------+-----------+
| default | UNLIMITED | MEDIUM | YES |
| read_only | 2000 | MEDIUM | NO |
| read_write | 5000 | MEDIUM | YES |
| read_write_no | 5000 | MEDIUM | NO |
| write_only | 12000 | MEDIUM | NO |
+---------------+------------+----------+-----------+
5 rows in set (0.01 sec)
mysql>
绑定资源组
mysql> create user read_only identified by 'tidb';
Query OK, 0 rows affected (0.08 sec)
mysql> create user read_write identified by 'tidb';
Query OK, 0 rows affected (0.03 sec)
mysql> create user read_write_no identified by 'tidb';
Query OK, 0 rows affected (0.04 sec)
mysql> create user write_only identified by 'tidb';
Query OK, 0 rows affected (0.03 sec)
mysql> alter user read_only resource group read_only;
Query OK, 0 rows affected (0.05 sec)
mysql> alter user write_only resource group write_only;
Query OK, 0 rows affected (0.04 sec)
mysql> alter user read_write resource group read_write;
Query OK, 0 rows affected (0.03 sec)
mysql> alter user read_write_no resource group read_write_no;
Query OK, 0 rows affected (0.04 sec)
mysql>
将用户绑定到资源组
mysql> select user,host,User_attributes from mysql.user;
+---------------+------+-------------------------------------+
| user | host | User_attributes |
+---------------+------+-------------------------------------+
| root | % | NULL |
| read_only | % | {"resource_group": "read_only"} |
| read_write | % | {"resource_group": "read_write"} |
| write_only | % | {"resource_group": "write_only"} |
| read_write_no | % | {"resource_group": "read_write_no"} |
+---------------+------+-------------------------------------+
5 rows in set (0.00 sec)
mysql>
性能测试
read_only
压测脚本
[tidb@vm116 ~]$ more sysbench_read_only.config
mysql-host=10.2.103.116
mysql-port=43000
mysql-user=read_only
mysql-password=tidb
mysql-db=sbtest
time=600
threads=16
report-interval=1
db-driver=mysql
[tidb@vm116 ~]$
sysbench --config-file=sysbench_read_only.config oltp_read_only --tables=10 --table-size=10000 run
监控与图表
机器负载
压测结果
SQL statistics:
queries performed:
read: 4125548
write: 0
other: 589364
total: 4714912
transactions: 294682 (491.11 per sec.)
queries: 4714912 (7857.77 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 600.0306s
total number of events: 294682
Latency (ms):
min: 9.70
avg: 32.57
max: 204.13
95th percentile: 38.94
sum: 9599029.86
Threads fairness:
events (avg/stddev): 18417.6250/23.24
execution time (avg/stddev): 599.9394/0.01
read_only_no
压测脚本
[tidb@vm116 ~]$ more sysbench_read_only_no.config
mysql-host=10.2.103.116
mysql-port=43000
mysql-user=read_only_no
mysql-password=tidb
mysql-db=sbtest
time=600
threads=16
report-interval=1
db-driver=mysql
[tidb@vm116 ~]$ sysbench --config-file=sysbench_read_only_no.config oltp_read_only --tables=10 --table-size=10000 run
监控与图表
机器负载
压测结果
SQL statistics:
queries performed:
read: 7025844
write: 0
other: 1003692
total: 8029536
transactions: 501846 (836.38 per sec.)
queries: 8029536 (13382.01 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 600.0231s
total number of events: 501846
Latency (ms):
min: 7.99
avg: 19.13
max: 167.63
95th percentile: 28.67
sum: 9598335.88
Threads fairness:
events (avg/stddev): 31365.3750/58.39
execution time (avg/stddev): 599.8960/0.01
[tidb@vm116 ~]$
没有资源限制用户测试
压测脚本
[tidb@vm116 ~]$ more sysbench.config
mysql-host=10.2.103.116
mysql-port=43000
mysql-user=root
mysql-password=
mysql-db=sbtest
time=600
threads=16
report-interval=1
db-driver=mysql
[tidb@vm116 ~]$
监控与图表
机器负载
压测结果
SQL statistics:
queries performed:
read: 6938876
write: 0
other: 991268
total: 7930144
transactions: 495634 (826.02 per sec.)
queries: 7930144 (13216.36 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 600.0232s
total number of events: 495634
Latency (ms):
min: 7.88
avg: 19.37
max: 239.23
95th percentile: 29.19
sum: 9598338.50
Threads fairness:
events (avg/stddev): 30977.1250/55.63
execution time (avg/stddev): 599.8962/0.01
[tidb@vm116 ~]$
总结
Resource Contro 的确可以限制用户的资源使用，但是当资源组设置BURSTABLE 属性后，基本可以用到机器所有的资源，建议还是需要设置一个最大值，这样可以更好的保护集群。
由于测试资源有限，没有测试更大的集群，在有更多的tidb servre 和tikv 后，希望可以测试一下在资源管控后，是否会资源均衡，希望大家多多测试。
标签：LTS,Control,Resource,read,mysql,write,only,sec,tidb From： https://blog.51cto.com/u_15550868/6887280