GBase8a数据库性能优化——优先级（二）

1. 显示优先级状态

1) 语法

Show priorities [where conditions]

2) 显示

node_name：集群节点名称。

Group：资源组编号。

Priority：优先级编号。

priority_weight：优先级配重。

Status：优先级开启状态 ON/OFF。

Description：优先级控制参数描述。

3) 权限要求

有show权限用户。

4) 示例

• 需要配置cgconfig.conf文件。在配置文件中为资源组0、1进行设置。

• 启动cgconfig服务。

• 重启gcware:

$gcluster_services gcware restart

完成以上配置操作后，再执行下面示例的命令，0、1两个控制组的优先级将为开启状态。

示例1：查看集群全部节点优先级状态。

gbase> show priorities;

| node_name | group | priority | priority-weight | status | description|

| node1 | 0| 0 | 20 | ON |...... |

| node1 | 0| 1 | 40 | ON |...... |

| node1 | 0| 2 | 60 | ON |...... |

| node1 | 0| 3 | 80 | ON |...... |

| node1 | 1| 0 | 20 | ON |...... |

| node1 | 1| 1 | 40 | ON |...... |

| node1 | 1| 2 | 60 | ON |...... |

| node1 | 1| 3 | 80 | ON |...... |

| node1 | 2| 0 | 20 | OFF |...... |

| node1 | 2| 1 | 40 | OFF |...... |

| node1 | 2| 2 | 60 | OFF |...... |

| node1 | 2| 3 | 80 | OFF |...... |

......

| node2 | 15| 0 | 20 | OFF |...... |

| node2 | 15| 1 | 40 | OFF |...... |

| node2 | 15| 2 | 60 | OFF |...... |

| node2 | 15| 3 | 80 | OFF |...... |

128 rows in set

示例2：查看node1节点的优先级状态信息。

gbase> show priorities where node_name = 'node1';

| node_name | group | priority | priority-weight | status | description|

| node1 | 0| 0 | 20 | ON |...... |

| node1 | 0| 1 | 40 | ON |...... |

| node1 | 0| 2 | 60 | ON |...... |

| node1 | 0| 3 | 80 | ON |...... |

| node1 | 1| 0 | 20 | ON |...... |

| node1 | 1| 1 | 40 | ON |...... |

| node1 | 1| 2 | 60 | ON |...... |

| node1 | 1| 3 | 80 | ON |...... |

| node1 | 2| 0 | 20 | OFF |...... |

| node1 | 2| 1 | 40 | OFF |...... |

| node1 | 2| 2 | 60 | OFF |...... |

| node1 | 2| 3 | 80 | OFF |...... |

......

| node1 | 15| 0 | 20 | OFF |...... |

| node1 | 15| 1 | 40 | OFF |...... |

| node1 | 15| 2 | 60 | OFF |...... |

| node1 | 15| 3 | 80 | OFF |...... |

64 rows in set

示例3：查看状态为ON的优先级信息。

gbase> show priorities where status ='ON';

| node_name | group | priority | priority-weight | status | description|

| node1 | 0| 0 | 20 | ON |...... |

| node1 | 0| 1 | 40 | ON |...... |

| node1 | 0| 2 | 60 | ON |...... |

| node1 | 0| 3 | 80 | ON |...... |

| node1 | 1| 0 | 20 | ON |...... |

| node1 | 1| 1 | 40 | ON |...... |

| node1 | 1| 2 | 60 | ON |...... |

| node1 | 1| 3 | 80 | ON |...... |

8 rows in set

示例4：关闭node1节点cgroup配置服务（service cgconfig stop）。

service cgconfig stop

Stopping cgconfig service: [ OK ]

su - gbase

$ gcluster_services all restart

Stopping GCMonit success!

Signaling GCRECOVER (gcrecover) to terminate: [ OK ]

Waiting for gcrecover services to unload:...[ OK ]

Signaling GCSYNC (gc_sync_server) to terminate: [ OK ]

[ OK ]for gc_sync_server services to unload:[ OK ]

Signaling GCLUSTERD to terminate: [ OK ]

.[ OK ]or gclusterd services to unload:...[ OK ]

Signaling GBASED to terminate: [ OK ]

.[ OK ]or gbased services to unload:[ OK ]

Signaling GCWARE (gcware) to terminate: [ OK ]

Waiting for gcware services to unload:.[ OK ]

Starting GCWARE (gcwexec): [ OK ]

Starting GCMonit success!

Starting GBASED : [ OK ]

Starting GCLUSTERD : [ OK ]

Starting GCSYNC : [ OK ]

Starting GCRECOVER : [ OK ]

$ gccli -uroot

GBase client 9.5.3.17.117651. Copyright (c) 2004-2019, GBase. All Rights Reserved.

gbase> show priorities where node_name = 'node1';

| node_name | group | priority | priority-weight | status | description|

| node1 | 0| 0 | 20 | OFF |...... |

| node1 | 0| 1 | 40 | OFF |...... |

| node1 | 0| 2 | 60 | OFF |...... |

| node1 | 0| 3 | 80 | OFF |...... |

| node1 | 1| 0 | 20 | OFF |...... |

| node1 | 1| 1 | 40 | OFF |...... |

| node1 | 1| 2 | 60 | OFF |...... |

| node1 | 1| 3 | 80 | OFF |...... |

| node1 | 2| 0 | 20 | OFF |...... |

| node1 | 2| 1 | 40 | OFF |...... |

| node1 | 2| 2 | 60 | OFF |...... |

| node1 | 2| 3 | 80 | OFF |...... |

......

| node1 | 15| 0 | 20 | OFF |...... |

| node1 | 15| 1 | 40 | OFF |...... |

| node1 | 15| 2 | 60 | OFF |...... |

| node1 | 15| 3 | 80 | OFF |...... |

64 rows in set

l 示例5：重新开启node1 的cgroup配置服务（service cgconfig start）。

service cgconfig start

Starting cgconfig service: [ OK ]

su - gbase

gcluster_services all restart

Stopping GCMonit success!

Signaling GCRECOVER (gcrecover) to terminate: [ OK ]

Waiting for gcrecover services to unload:...[ OK ]

Signaling GCSYNC (gc_sync_server) to terminate: [ OK ]

[ OK ]for gc_sync_server services to unload:[ OK ]

Signaling GCLUSTERD to terminate: [ OK ]

.[ OK ]or gclusterd services to unload:...[ OK ]

Signaling GBASED to terminate: [ OK ]

.[ OK ]or gbased services to unload:[ OK ]

Signaling GCWARE (gcware) to terminate: [ OK ]

Waiting for gcware services to unload:.[ OK ]

Starting GCWARE (gcwexec): [ OK ]

Starting GCMonit success!

Starting GBASED : [ OK ]

Starting GCLUSTERD : [ OK ]

Starting GCSYNC : [ OK ]

Starting GCRECOVER : [ OK ]

$ gccli -uroot

GBase client 9.5.3.17.117651. Copyright (c) 2004-2019, GBase. All Rights Reserved.

gbase> show priorities where node_name = 'node1';

| node_name | group | priority | priority-weight | status | description|

| node1 | 0| 0 | 20 | ON |...... |

| node1 | 0| 1 | 40 | ON |...... |

| node1 | 0| 2 | 60 | ON |...... |

| node1 | 0| 3 | 80 | ON |...... |

| node1 | 1| 0 | 20 | ON |...... |

| node1 | 1| 1 | 40 | ON |...... |

| node1 | 1| 2 | 60 | ON |...... |

| node1 | 1| 3 | 80 | ON |...... |

| node1 | 2| 0 | 20 | OFF |...... |

| node1 | 2| 1 | 40 | OFF |...... |

| node1 | 2| 2 | 60 | OFF |...... |

| node1 | 2| 3 | 80 | OFF |...... |

......

| node1 | 15| 0 | 20 | OFF |...... |

| node1 | 15| 1 | 40 | OFF |...... |

| node1 | 15| 2 | 60 | OFF |...... |

| node1 | 15| 3 | 80 | OFF |...... |

64 rows in set

2. 配置优先级队列相关参数

通过在gcluster($GCLUSTER_BASE/config/gbase_8a_gcluster.cnf)与gnode($GBASE_BASE/config/gbase_8a_gbase.cnf)的配置文件中修改下面几个参数值来完成优先级队列的配置：

• gbase_use_priority_queue：

参数设置为0，表示关闭优先级队列；

设置为1，表示开启优先级队列。

• _gbase_priority_total_tasks：参数表示最大并行运行查询任务数目，包括DML的查询部分,本参数最大值不能超过128，缺省为本地CPU核数2倍；

• _gbase_priority_tasks：参数表示每个优先级队列可容纳最大任务数目（即可参加调度的数目），未能进入队列任务将阻塞等待，本参数最大值不能超过64，缺省为本地CPU核数；

• gbase_use_res_ctrl_group：本参数决定是不是启用资源控制组挂接。参数设置为0，表示不开启资源控制组挂接，缺省设置为不开启状态；设置其他值为开启资源控制组挂接。

3. 指定查询SQL优先级

资源组用户Session可根据具体情况，通过使用hint (格式为/+PRIORITY(‘priority_value’)/), 决定该SQL运行级别（即对应的优先级），本命令只限于查询SQL。

l 语法：

Select /+PRIORITY(‘priority_value’)/ …

权限要求

有create，insert，drop，select权限的用户。

l 备注：

优先级设定只能小于或等于该用户优先级别，设置错误会恢复到用户优先级，并报出警告“can not upgrade to priority X”（X是执行用户的优先级）。

l 示例：

gbase> create table t1(a int);

Query OK, 0 rows affected

gbase> insert into t1 values (1),(1),(2),(3),(5);

Query OK, 5 rows affected

Records: 5 Duplicates: 0 Warnings: 0

gbase> select /+PRIORITY(‘0’)/ * from t1 group by a;

| a |

| 1 |

| 2 |

| 3 |

| 5 |

4 rows in set