黄东旭解析 TiDB 的核心优势
656
2023-11-28
v7.1 LTS Resource Control 试用
1、集群信息
相关参数
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.01read_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 后，希望可以测试一下在资源管控后，是否会资源均衡，希望大家多多测试。
版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。
相关文章
