南大通用GBase 8a通过资源管控样例限制集群SQL并行数量

本文介绍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)