本文介绍GBase 8a的资源管理的限制集群的SQL并发数量的功能。
目录导航
资管管控介绍
这部分详情看资源管理部分。
限制
目前该并发限制，只能限制单个管理节点的，对于多个管理节点，建议在业务端进行分配和控制。
比如业务A分配用户user_A，全部连接节点1，备用节点2 ，最多10个并发。
业务B分配用户use_B,连接节点3，备用节点4，最多5并发。
目标
限制最高2个并发SQL执行。
环境
2节点集群，版本为 8.6.2.43-R28
[root@rh6-1 ~]# gcadmin
CLUSTER STATE: ACTIVE
CLUSTER MODE: NORMAL
=================================================================
| GBASE COORDINATOR CLUSTER INFORMATION |
=================================================================
| NodeName | IpAddress |gcware |gcluster |DataState |
-----------------------------------------------------------------
| coordinator1 | 10.0.2.201 | OPEN | OPEN | 0 |
-----------------------------------------------------------------
=============================================================
| GBASE DATA CLUSTER INFORMATION |
=============================================================
|NodeName | IpAddress |gnode |syncserver |DataState |
-------------------------------------------------------------
| node1 | 10.0.2.201 | OPEN | OPEN | 0 |
-------------------------------------------------------------
| node2 | 10.0.2.202 | OPEN | OPEN | 0 |
-------------------------------------------------------------
创建用户
我们创建1个abc用户，授权abc数据库全部权限。
gbase> create user abc identified by 'abc';
Query OK, 0 rows affected (Elapsed: 00:00:00.01)
gbase> grant all on abc.* to abc;
Query OK, 0 rows affected (Elapsed: 00:00:00.01)
创建消费者组
我们的测试用户，属于这个测试的消费者组，我们设置最多10个并发SQL执行。
gbase> create consumer group cg_max10 comment 'max 10 parrel sql';
Query OK, 0 rows affected (Elapsed: 00:00:00.13)
将用户加入消费者组
gbase> alter consumer group cg_max10 add user abc;
Query OK, 0 rows affected (Elapsed: 00:00:00.02)
创建静态资源池
关注里面的max_activetask=10，最大活跃任务数是10。
gbase> create resource pool static_max10(cpu_percent=100,max_memory=4096,max_disk_readio=100,max_disk_writeio=100,max_temp_diskspace=1024,max_disk_space=102400,max_activetask=10)type static;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)
创建动态资源池
注意我们在动态池里，创建的最大任务数是2，以便我们测试。因为一个静态池可以有多个动态池。
gbase> create resource pool dy_max10(priority=1, cpu_percent=100, max_memory=1000, max_temp_diskspace=200, max_disk_space=2000, max_disk_writeio=100, max_disk_readio=100, max_activetask=2)type dynamic base on static_max10;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)
创建资源任务计划
gbase> create resource plan rp_max10;
Query OK, 0 rows affected (Elapsed: 00:00:00.01)
创建资源指令(装配)
这一步就是将资源计划，消费者组，资源池装配到一起。
gbase> create resource directive rd_max10(plan_name='rp_max10',group_name='cg_max10',pool_name='dy_max10');
Query OK, 0 rows affected (Elapsed: 00:00:00.01)创建一个默认的
gbase> create resource directive rd_default(plan_name='rp_max10',group_name='default_consumer_group',pool_name='dy_max10');
Query OK, 0 rows affected (Elapsed: 00:00:00.01)激活资源计划
gbase> set global active_resource_plan='rp_max10';
Query OK, 0 rows affected (Elapsed: 00:00:00.03)发起3个并发SQL
通过abc用户，发起3个sql，然后查看运行情况如下。
gbase> show processlist;
+----+-----------------+------------------+------+---------+-------+-----------------------------+------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+------------------+------+---------+-------+-----------------------------+------------------------------------+
| 1 | event_scheduler | localhost | NULL | Daemon | 16420 | Waiting for next activation | NULL |
| 4 | root | localhost | abc | Query | 0 | NULL | show processlist |
| 5 | root | 10.0.2.201:39333 | abc | Sleep | 236 | | NULL |
| 42 | abc | localhost | abc | Query | 17 | Sending task to gnodes | select count(*),sleep(100) from t1 |
| 43 | abc | localhost | abc | Query | 13 | Sending task to gnodes | select count(*),sleep(100) from t1 |
| 44 | abc | localhost | abc | Query | 7 | waiting in res pool | select count(*),sleep(100) from t1 |
+----+-----------------+------------------+------+---------+-------+-----------------------------+------------------------------------+
6 rows in set (Elapsed: 00:00:00.00)
gbase> show processlist;
+----+-----------------+------------------+------+---------+-------+-----------------------------+------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+------------------+------+---------+-------+-----------------------------+------------------------------------+
| 1 | event_scheduler | localhost | NULL | Daemon | 16421 | Waiting for next activation | NULL |
| 4 | root | localhost | abc | Query | 0 | NULL | show processlist |
| 5 | root | 10.0.2.201:39333 | abc | Sleep | 237 | | NULL |
| 42 | abc | localhost | abc | Query | 18 | Sending task to gnodes | select count(*),sleep(100) from t1 |
| 43 | abc | localhost | abc | Query | 14 | Sending task to gnodes | select count(*),sleep(100) from t1 |
| 44 | abc | localhost | abc | Query | 8 | waiting in res pool | select count(*),sleep(100) from t1 |
+----+-----------------+------------------+------+---------+-------+-----------------------------+------------------------------------+
6 rows in set (Elapsed: 00:00:00.00)
可以看到，有2个SQL是执行中，第3个是waiting in res pool等待状态。
执行最终结果
因为3个SQL不是同时启动的，所以第3个的waiting时间取决于1和3开始执行时间差。只有连接1执行完毕了，才开始执行3.
连接1，100秒后完成
gbase> select count(*),sleep(100) from t1;
+----------+------------+
| count(*) | sleep(100) |
+----------+------------+
| 0 | 0 |
+----------+------------+
1 row in set (Elapsed: 00:01:41.06)
连接2，100秒后完成
gbase> select count(*),sleep(100) from t1;
+----------+------------+
| count(*) | sleep(100) |
+----------+------------+
| 0 | 0 |
+----------+------------+
1 row in set (Elapsed: 00:01:40.26)
连接3，192秒后完成。
gbase> select count(*),sleep(100) from t1;
+----------+------------+
| count(*) | sleep(100) |
+----------+------------+
| 0 | 0 |
+----------+------------+
1 row in set (Elapsed: 00:03:12.06)