黄东旭解析 TiDB 的核心优势
495
2024-04-03
尝试 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 inselected 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:版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。
相关文章
