Greenplum-Resource queue Management

preface

In Greenplum, each user corresponds to a resource queue. Resource queues are useful in many scenarios. For example, many people share a database and need to develop different queues for different people to control resources. If it is not specified by default, it corresponds to pg_default resource queue.

Not all sql added will be restricted by the resource queue. By default:

select, select into, create table name as select, declare cursor will be restricted in the resource queue, if you set the parameter resource_select_only = off (in the postgresql.conf file), then insert, update, delete will also be restricted in the resource queue

I. Common methods for resource queues 1.1. View resource queues:

testdb=# select from pg_resqueue_attributes;

active_statements: Number of SQL statements that can be executed simultaneously

max_cost: the maximum cost consumed by each sql, that is, the cost that can be seen when explaining

min_cost: minimum cost, if sql requires less cost than this, no matter what the situation will be executed immediately

cost_over_commit: If true, sql exceeding max_cost will be executed when there is no other sql running in the resource queue. If false, an error will be reported as long as max_cost exceeds max_cost.

priority: CPU priority, divided into min| low| medium| high| max

memory_limit： There are two cases: one is that only memory_limit and active_queue are set, then the maximum memory occupied by each sql is: memory_limit / active_queue; the other is that max_cost is set, then the maximum memory occupied by each sql is: memory_limit (sql_cost / max_cost) sql_cost represents the cost required by the current sql

1.2 View the entire system is currently executing sql, you can according to

testdb=# SELECT * FROM pg_stat_activity;

1.3 View the statements waiting in the resource queue:

testdb=# SELECT * FROM gp_toolkit.gp_locks_on_resqueue WHERE lorwaiting='true';

1.4 View the correspondence between users and resource queues

testdb=# SELECT rolname, rsqname FROM pg_roles, gp_toolkit.gp_resqueue_status WHERE pg_roles.rolresqueue=gp_toolkit.gp_resqueue_status.queueid;

1.5 View the relationship between currently executed sql and corresponding resource queue

testdb=# select * from gp_toolkit.gp_resq_priority_statement ;

1.6, New Resource Queue:

testdb=# create resource queue myqueue with (priority=low, memory_limit=1024000, active_statements=10);

1.7 modify resource queue

testdb=# alter resource queue myqueue with (memory_limit=1000000);