8a CPU资源管理

注意点：
1）动态池cpu资源有共享的功能，但静态池能把资源限制住(建议使用静态池区分两种对比测试)。

2）确保cgroup有没有正常启动，正常起作用。（cgroup只针对于数据库node节点）

3）资源管理，只能管理受控SQL的资源使用情况， DDL、操作系统其他应用的cpu使用情况管不了，在测试方面应尽量使用cpu设置百分比低的进行测试。效果更加明显。

4）cpu使用率，只是针对资源使用平均的，并不是仅使用十分之一个cpu，而是使用所有cpu的资源的平均十分之一。

1.操作步骤
0) 准备工作，创建用户和组
create consumer group group1 comment = 'test group1';
create consumer group group2 comment = 'test group2';

create user test1 identified by 'test1';
create user test2 identified by 'test2';

grant all on test.*  to test1;
grant all on test.*  to test2;
grant all on tpch.*  to test1;
grant all on tpch.*  to test2;
grant file on *.*  to test1;
grant file on *.*  to test2;

alter consumer group group1 add user test1; 
alter consumer group group2 add user test2;

1)    创建静态资源池，分别限制CPU占用比为20%和80%，其他资源设置较大
create resource pool static_pool1(cpu_percent=20,max_memory=1000000000,max_disk_space=10000000000,max_temp_diskspace=20000000000,max_disk_writeio=200000000,max_disk_readio=200000000) type static;
create resource pool static_pool2(cpu_percent=40,max_memory=1000000000,max_disk_space=10000000000,max_temp_diskspace=20000000000,max_disk_writeio=200000000,max_disk_readio=200000000) type static;

2)    创建2个动态资源池，分别隶属于上述两个静态池
create resource pool dynamic_pool1(priority=1, cpu_percent=100,max_memory=1000000000,max_disk_space=10000000000,max_temp_diskspace=20000000000,max_disk_writeio=200000000,max_disk_readio=200000000) type dynamic base on static_pool1;
create resource pool dynamic_pool2(priority=1, cpu_percent=100,max_memory=1000000000,max_disk_space=10000000000,max_temp_diskspace=20000000000,max_disk_writeio=200000000,max_disk_readio=200000000) type dynamic base on static_pool2;

3)    创建计划，并激活
create resource plan plan1 comment = 'test plan1';
create resource directive directive1(plan_name = 'plan1',pool_name = 'dynamic_pool1', group_name = 'group1',comment = 'test directive1');
create resource directive directive2(plan_name = 'plan1',pool_name = 'dynamic_pool2', group_name = 'group2',comment = 'test directive2');
create resource directive directive4(plan_name = 'plan1',pool_name = 'dynamic_pool2', group_name = 'default_consumer_group',comment = 'others');

active resource plan plan1 on vc vcname000001;

4)    查看资源池
select resource_pool_name,max_memory,max_tmp_table_space,max_disk_space,cpu_percent from gbase.resource_pool;

5) 查询关系语句(确保配置正确)
查询资源组和资源组用户的对应关系
select a.consumer_group_name, b.user_name   from gbase.consumer_group a ,gbase.consumer_group_user b where a.consumer_group_id = b.consumer_group_id;

查询资源编排
select c.resource_plan_directive_name,a.consumer_group_name, b.resource_pool_name,d.user_name from gbase.consumer_group a,
gbase.resource_pool b, gbase.resource_plan_directive c,gbase.consumer_group_user d 
where c.resource_pool_id = b.resource_pool_id and c.consumer_group_id=a.consumer_group_id 
and c.consumer_group_id = d.consumer_group_id;

6) 样例SQL
见下方

7) 清理资源池 及用户
deactive resource plan on vc vcname000001;
drop resource directive directive1;
drop resource directive directive2;
drop resource directive directive4;
drop resource plan plan1 ;
drop resource pool dynamic_pool1;
drop resource pool dynamic_pool2;
drop resource pool static_pool1;
drop resource pool static_pool2;

alter consumer group group1 remove user  test1;
alter consumer group group2 remove user  test2;
drop consumer group group1;
drop consumer group group2;
drop user test1;
drop user test2;

测试步骤：
1)    使用test1执行SQL，比如tpch模型的sql1，观察CPU资源并截图，CPU资源应消耗10%，或者执行select * from information_schema.GNODES_RESOURCE_POOL_USAGE查看CPU消耗情况；
gccli -utest1 -ptest1 -Dtpch
select
       l_returnflag,
       l_linestatus,
       sum(l_quantity) as sum_qty,
       sum(l_extendedprice) as sum_base_price,
       sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
       sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
       avg(l_quantity) as avg_qty,
       avg(l_extendedprice) as avg_price,
       avg(l_discount) as avg_disc,
       count(*) as count_order
from
       lineitem
where
       l_shipdate <= date '1998-12-01' - interval '106' day
group by
       l_returnflag,
       l_linestatus
order by
       l_returnflag,
       l_linestatus;

2)    使用test2执行相同的SQL，观察CPU资源并截图，CPU资源应消耗90%，消耗的时间较test1用户约提升9倍左右。
gccli -utest2 -ptest2 -Dtpch
select
       l_returnflag,
       l_linestatus,
       sum(l_quantity) as sum_qty,
       sum(l_extendedprice) as sum_base_price,
       sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
       sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
       avg(l_quantity) as avg_qty,
       avg(l_extendedprice) as avg_price,
       avg(l_discount) as avg_disc,
       count(*) as count_order
from
       lineitem
where
       l_shipdate <= date '1998-12-01' - interval '106' day
group by
       l_returnflag,
       l_linestatus
order by
       l_returnflag,
       l_linestatus;