Greenplum 的资源队列介绍
Greenplum 的资源队列介绍
GP的资源队列，只针对普通用户有效，superuser不受影响
一、创建资源队列语法：
Command: CREATE RESOURCE QUEUE
Description: create a new resource queue for workload management
Syntax:
CREATE RESOURCE QUEUE name WITH (queue_attribute=value [, ... ])
where queue_attribute is:
ACTIVE_STATEMENTS=integer
[ MAX_COST=float [COST_OVERCOMMIT={TRUE|FALSE}] ]
[ MIN_COST=float ]
[ PRIORITY={MIN|LOW|MEDIUM|HIGH|MAX} ]
[ MEMORY_LIMIT='memory_units' ]
| MAX_COST=float [ COST_OVERCOMMIT={TRUE|FALSE} ]
[ ACTIVE_STATEMENTS=integer ]
[ MIN_COST=float ]
[ PRIORITY={MIN|LOW|MEDIUM|HIGH|MAX} ]
[ MEMORY_LIMIT='memory_units' ]
资源队列的设置
1 ACTIVE_STATEMENTS
&emsp#允许同时运行（active状态）的SQL数，-1不限
表示资源队列中，允许同时执行的SQL。
（注意当QUERY的成本低于min_cost时，不需要排队，直接运行。）
2 PRIORITY ={MIN LOW MEDIUM HIGH MAX}
#指当前资源队列的优先级，当资源紧张时，优先将CPU资源分配给高优先级的资源队列。
#（处于高优先级的资源队列中的SQL，可以获得更高优先级的CPU资源）。
#建议将实时性要求高的查询对应的用户分配到高优先级的资源队列中。
3 Memory_limit ‘memory_units kB,MB or GB’
#设置资源队列中所有SQL允许的最大内存使用量。-1不限
#（但是受前面提到的数据库或系统参数限制，触发OOM错误）
4 min_cost float
#（资源超限时，是需要排队的）但是，当QUERY的成本低于min_cost时，
#不需要排队，直接运行。(也就是说小查询，就让他跑吧。
5 max_cost float
#设置浮点或指数（（for example 100.0）,(for example 1e+2)）,-1 不限制
#表示资源组允许同时执行的QUERY加起来的COST上限。
#COST是SQL执行计划中的总成本。
6、COST_OVERCOMMIT boolean，
#当系统空闲时，是否允许(TRUE)超过max_cost的限制。
参数说明
| 参数 | 类型 | 说明 |
|---|---|---|
| name | text | 资源队列的名字。 |
| ACTIVE_STATEMENTS | integer | 带有 ACTIVE_STATEMENTS 阀值的资源队列限制了分配到队列角色所能够执行的查询的数量。它（阀值）控制着活跃查询的数量，活跃查询是在同一时间允许运行的查询数量。ACTIVE_STATEMENTS 的值应该是一个大于0的整数值。 |
| MEMORY_LIMIT | memory_units | 对于所有从该资源队列中提交的语句设置总内存配额。内存单元可以指定为kB, MB或者GB。对于一个资源队列来说最小的内存配额是10MB， 没有最大限值，但是查询执行的上边界由Segment主机的物理内存所限定。默认值时没有限制为（-1） |
| MAX_COST | float | 带有MAX_COST 阀值的资源队列对查询代价设置了一个最大限制。该查询能够被分配到该队列的用户所执行。代价由Greenplum数据库查询优化器（正如查询EXPLAIN 输出显示的）确定的查询的 估计共代价 进行衡量的。因此，管理员必须要熟悉在系统中执行的典型查询，以对队列设置一个合理的阀值。成本以磁盘页提取为单位进行衡量；1.0 等于顺序读取一个磁盘页。MAX_COST 的值可以被指定为浮点数（例如 100.0） 或者可以被指定为（例如 1e+2）。 |
| COST_OVERCOMMIT | boolean | 如果基于 MAX_COST限制资源队列，则管理员可以允许 COST_OVERCOMMIT（默认）。这意味着超过允许的成本阈值的查询将被允许运行，但只有在系统空闲时才能运行。如果指定 COST_OVERCOMMIT=FALSE ，超过成本限制的查询将始终被拒绝，从不允许运行 |
| MIN_COST | float | 该是最小查询的最小查询成本限制。成本低于此限制的查询将不会排队等待立即运行。成本由Greenplum数据库查询优化器（正如查询 EXPLAIN 输出所示）确定的查询的估计总成本所衡量。因此，管理员必须熟悉通常在系统上执行的查询，以便为被认为是小型查询设置适当的成本。成本是以磁盘页提取为单位来衡量的; 1.0等于一个顺序的磁盘页面读取。MIN_COST 的值可以被指定为浮点数（例如 100.0）或也可以被指定为一个指数（例如 1e+2）。 |
| PRIORITY | MIN/LOW/MEDIUM/HIGH/MAX | 设置和资源队列相关查询的优先级。队里中拥有高优先级的查询和语句会在竞争中拥有更大的可用CPU资源份额。队列中拥有低优先级的查询将会被推迟，同时，更高优先级的查询将会被执行。如果没有指定优先级，和队列相关的查询的优先级为 MEDIUM。 |
- 官方建议使用MEMORY_LIMIT 和ACTIVE_STATEMENTS 来替代max_cost
- 如果队列中未设置MEMORY_LIMIT，则每个查询可用的内存值为系统参数statement_mem的值，最大可用内存为statement_mem ACTIVE_STATEMENTS
- 并不是所有语句都受资源队列限制，默认情况下，只有SELECT, SELECT INTO, CREATE TABLE AS SELECT, 和DECLARE CURSOR受限，如果配置参数resource_select_only = off，则INSERT, UPDATE,DELETE语句也会受限
- 如果没有设置max_cost,那么每个语句使用的内存是MEMORY_LIMIT/ACTIVE_STATEMENTS,如果设置了max_cost,内存是MEMORY_LIMIT*(query_cost/max_cost),query_cost为实际SQL的cost
1、创建一个资源策略
create resource queue myqueue with(max_cost=10000.0);
create resource queue q1 with (ACTIVE_STATEMENTS=10,MEMORY_LIMIT='128MB',priority=min);
create resource queue q2 with (ACTIVE_STATEMENTS=10,MEMORY_LIMIT='1GB',PRIORITY=HIGH,COST_OVERCOMMIT=true,MIN_COST=100,MAX_COST=1000000);
2、修改资源队列限制举例：
ALTER RESOURCE QUEUE myqueue WITH (MAX_COST=-1.0, MIN_COST= -1.0);
使用ALTER RESOURCE QUEUE命令来改变资源队列的限制
ALTER RESOURCE QUEUE test_queue WITH (ACTIVE_STATEMENTS=3);
ALTER RESOURCE QUEUE test_queue WITH (MAX_COST=100000.0);
将活动语句数量或者内存限制重置为无限制，可以使用-1值。
ALTER RESOURCE QUEUE test_queue WITH (MAX_COST=-1.0, MEMORY_LIMIT='2GB');
改变查询优先级
ALTER RESOURCE QUEUE test_queue WITH (PRIORITY=MIN);
3、然后把这个资源策略赋权给一个用户
alter role skate resource queue myqueue .
eg:
create resource queue queue_high with (ACTIVE_STATEMENTS=20,MEMORY_LIMIT='55GB',PRIORITY=HIGH,COST_OVERCOMMIT=true,MIN_COST=100,MAX_COST=1000000);
alter role "gkdb-datawarehouse" resource queue queue_high;
恢复到使用默认的资源队列
alter role xiaoxu resource queue none;
4、如果某个资源策略已经授予给了用户，则他不能被删除。
alter role skate resource queue none。解除用户skate的资源策略。
查看用户和用户的资源队列
SELECT
rolname,
rsqname
FROM
pg_roles,
gp_toolkit.gp_resqueue_status
WHERE
pg_roles.rolresqueue = gp_toolkit.gp_resqueue_status.queueid;
rolname : 角色的名字(用户名)
rsqname : 队列的名字
查看资源队列相关参数信息
select * from pg_resqueue_attributes;
查看资源队列相关使用情况
SELECT * FROM gp_toolkit.gp_resqueue_status;
查看资源队列统计信息
SELECT * FROM pg_stat_resqueues;
查询角色分配的资源队列
SELECT * from gp_toolkit.gp_resq_role;
查询资源队列中的等待查询
SELECT * FROM gp_toolkit.gp_locks_on_resqueue WHERE lorwaiting='true';
查询活动语句的优先级
select * from gp_toolkit.gp_resq_priority_statement
清理资源队列中等待的查询
SELECT rolname, rsqname, pid, granted,current_query, datname
FROM pg_roles, gp_toolkit.gp_resqueue_status, pg_locks,pg_stat_activity
WHERE pg_roles.rolresqueue=pg_locks.objid
AND pg_locks.objid=gp_toolkit.gp_resqueue_status.queueid
AND pg_stat_activity.procpid=pg_locks.pid
AND pg_stat_activity.usename=pg_roles.rolname;
删除资源队列
drop resource queue test_queue;
test_queue : 队列的名字
建议的资源队列使用方法：
GPDB默认的资源队列为pg_default，如果不创建队列，那么所有的用户都会被指定给pg_default。这是非常不建议的。
建议的做法是为每个用户创建一个资源队列。（因为通常一个数据库用户对应一个业务。不同的数据库用户可能对应不同的业务或者使用者（例如业务用户、分析师用户、开发者、DBA等）。）
查看资源队列
postgres=# select * from pg_resqueue_attributes;
rsqname | resname | ressetting | restypid
------------+-------------------+------------+----------
pg_default | active_statements | 20 | 1
pg_default | max_cost | -1 | 2
pg_default | min_cost | 0 | 3
pg_default | cost_overcommit | 0 | 4
pg_default | priority | medium | 5
pg_default | memory_limit | -1 | 6
如果每个时间段的资源需求不一样，可以写一个CRONTAB任务，定时的调整资源队列的限制。
例如白天分析师的优先级更高，晚上处理报表的队列优先级更高。
目前Greenplum还不支持按时间段来设置资源限制，所以只能外部部署任务，alter resource queue来实现。
通过gp_toolkit提供的视图，可以观察资源队列的资源使用。
查看资源队列的使用情况
gp_toolkit.gp_resq_activity
gp_toolkit.gp_resq_activity_by_queue
gp_toolkit.gp_resq_priority_backend
gp_toolkit.gp_resq_priority_statement
gp_toolkit.gp_resq_role
gp_toolkit.gp_resqueue_status
二、资源队列相关参数
Greenplum的内存设定，受几个因素影响，首先能够被限制内存使用的，只能是普通用户，不能是supersuer。
注意所有资源队列的内存加起来不要超过gp_vmem_protect_limit的限制。
1、gp_resqueue_memory_policy，资源队列的内存管理策略
postgres=# show gp_resqueue_memory_policy;
gp_resqueue_memory_policy
---------------------------
eager_free
=auto:内存的消耗由 statement_mem 和 资源队列的memory_limit两个参数决定，单条QUERY允许申请的内存将突破资源队列的MEMORY_LIMIT限制。
--示例：
=> SET statement_mem='2GB';
=> SELECT * FROM my_big_table WHERE column='value' ORDER BY id;
=> RESET statement_mem;
注意，还有一个系统参数max_statement_mem，这个可以理解为SEGMENT级别的内存使用安全控制阀，单个QUERY申请的memory不能超过max_statement_mem。
意思是你可以随便改会话级的statement_mem参数，但是不要随便max_statement_mem参数。
建议的max_statement_mem设置：（seghost物理内存/平均并发查询数）
(seghost_physical_memory) / (average_number_concurrent_queries)
=eager_free：表示数据库在评估SQL对内存的申请渴望时，分阶段统计，也就是说一个SQL可能总共需要申请1G内存，但是每个阶段只申请100MB，所以需要的内存实际上是100MB。
使用eager_free策略，可以降低QUERY报内存不足的可能性。
2、gp_resqueue_priority，是否使用资源队列的优先级。
ON使用，OFF不使用。不使用资源队列优先级时，所有队列公平对待。
postgres=# show gp_resqueue_priority;
gp_resqueue_priority
----------------------
on
3、gp_resqueue_priority_cpucores_per_segment，每个SEGMENT可以使用的CPU核数，
例如8核的机器，跑了2个PRIMARY SEGMENT，则配置为4。master 上面如果没有其他节点，配置为8。当发生CPU抢占时，优先级高的资源组中运行的SQL，优先分配CPU资源。
postgres=# show gp_resqueue_priority_cpucores_per_segment;
gp_resqueue_priority_cpucores_per_segment
-------------------------------------------
4
4、gp_resqueue_priority_sweeper_interval，CPU时间片统计间隔，
SQL执行时，计算它的share值（根据优先级以及计算gp_resqueue_priority_cpucores_per_segment出来）。越小越频繁，优先级设置带来的效果越好。但是overhead越大。
postgres=# show gp_resqueue_priority_sweeper_interval;
gp_resqueue_priority_sweeper_interval
---------------------------------------
1000
三、限制了资源队列但是并发执行的QUERY依旧很高，原因分析
当我们创建了资源队列，但是你发现并没有限制住并发时，可能是什么原因呢？
因为在资源队列中有一个开关，如果SQL的COST低于设置的值时，SQL依旧被放行，实际上就是简单的小SQL，不会和大SQL一样排队。
就好像银行的窗口，取钱业务通常可以很快办理，但是开卡的业务需要很久。那么在排队时，我们可以让取钱的业务不排队，直接去窗口办理。
但是，这里也可能存在问题，你怎么就知道他一定是来取钱的呢？万一他也来办卡呢？
在数据库中，有COST来标识，但是记住，他需要依赖准确的统计信息，否则可能COST不准确，例如一张1亿的表，我们没有收集统计信息的话，记录数就是0，那么对这个表的查询操作COST算出来就会很低。这样的话，大SQL也被混入执行中。
所以，此时请关注，你的统计信息是否准确。
《GP的资源队列.pdf》文档下载：https://www.modb.pro/doc/67644
《Oracle 自动收集统计信息机制》：https://www.modb.pro/db/403670
《Oracle_索引重建—优化索引碎片》：https://www.modb.pro/db/399543
《DBA_TAB_MODIFICATIONS表的刷新策略测试》https://www.modb.pro/db/414692
内存利用
-
gp_resqueue_memory_policy - 启用Greenplum数据库的内存管理特性。
-
在Greenplum数据库4.2和其后的版本中，分布算法eager_free会利用并非所有操作符都会同时执行这一事实。查询计划被划分成阶段并且Greenplum数据库会饥渴地在上一阶段执行结束时释放分配给上一阶段的内存，然后将释放出来的内存饥渴地分配给新的阶段。
-
当被设置为none时，内存管理与4.1之前Greenplum数据库发行版相同。当被设置为auto时，查询内存使用由statement_mem和资源队列内存限制所控制。
-
statement_mem和max_statement_mem - 被用来在运行时给一个特定查询分配内存（覆盖资源队列指派的默认分配）。max_statement_mem被数据库超级用户设置以防止常规数据库用户过度分配。
-
gp_vmem_protect_limit - 设置所有查询处理能消耗的上界并且不应超过Segment主机的物理内存量。当一台Segment主机在查询执行时达到这一限制，导致超过限制的查询将被取消。
-
gp_vmem_idle_resource_timeout和gp_vmem_protect_segworker_cache_limit - 被用来释放Segment主机上由闲置数据库进程持有的内存。管理员可能想要在有大量并发的系统上调整这些设置。
-
shared_buffers - 设置Greenplum服务器实例用作共享内存缓冲区的内存量。这个设置必须至少为128千字节并且至少为16千字节乘以max_connections。该值不能超过操作系统共享内存最大分配请求尺寸，该尺寸由Linux上的shmmax控制。推荐的OS内存设置请见Greenplum数据库安装指南。
浙公网安备 33010602011771号