| <!-- |
| $PostgreSQL: pgsql/doc/src/sgml/ref/create_resource_queue.sgml,v 1.4.2.1 2006/10/05 03:06:17 dhutchinson Exp $ |
| PostgreSQL documentation |
| --> |
| |
| <refentry id="SQL-CREATERESOURCEQUEUE"> |
| <refmeta> |
| <refentrytitle id="sql-createresourcequeue-title">CREATE RESOURCE QUEUE</refentrytitle> |
| <refmiscinfo>SQL - Language Statements</refmiscinfo> |
| </refmeta> |
| |
| <refnamediv> |
| <refname>CREATE RESOURCE QUEUE</refname> |
| <refpurpose>create a new resource queue for workload management</refpurpose> |
| </refnamediv> |
| |
| <indexterm zone="sql-createresourcequeue"> |
| <primary>CREATE RESOURCE QUEUE</primary> |
| </indexterm> |
| |
| <refsynopsisdiv> |
| <synopsis> |
| |
| 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' ] |
| |
| </synopsis> |
| </refsynopsisdiv> |
| |
| <refsect1> |
| <title>Description</title> |
| |
| <para> |
| <command>CREATE RESOURCE QUEUE</command> create a new resource queue |
| for Greenplum Database workload management. A resource queue must have |
| either an ACTIVE_STATEMENTS or a MAX_COST value (or it can have both). |
| Only a superuser can create a resource queue. |
| </para> |
| |
| <para> |
| Resource queues with an ACTIVE_STATEMENTS threshold set a maximum limit |
| on the number of queries that can be executed by roles assigned to that |
| queue. It controls the number of active queries that are allowed to run |
| at the same time. The value for ACTIVE_STATEMENTS should be an integer |
| greater than 0. |
| </para> |
| |
| <para> |
| Resource queues with a MAX_COST threshold set a maximum limit on the total |
| cost of queries that can be executed by roles assigned to that queue. Cost |
| is measured in the estimated total cost for the query as determined by the |
| Greenplum Database query planner (as shown in the EXPLAIN output for a query). |
| Therefore, an administrator must be familiar with the queries typically executed |
| on the system in order to set an appropriate cost threshold for a queue. Cost is |
| measured in units of disk page fetches; 1.0 equals one sequential disk page read. |
| The value for MAX_COST is specified as a floating point number (for example 100.0) |
| or can also be specified as an exponent (for example 1e+2). If a resource queue is |
| limited based on a cost threshold, then the administrator can allow |
| COST_OVERCOMMIT=TRUE (the default). This means that a query that exceeds |
| the allowed cost threshold will be allowed to run but only when the system is idle. |
| If COST_OVERCOMMIT=FALSE is specified, queries that exceed the cost limit will |
| always be rejected and never allowed to run. Specifying a value for MIN_COST |
| allows the administrator to define a cost for small queries that will be |
| exempt from resource queueing. |
| </para> |
| |
| <para> |
| If a value is not defined for ACTIVE_STATEMENTS or MAX_COST, it is set to -1 by |
| default (meaning no limit). After defining a resource queue, you must assign roles |
| to the queue using the ALTER ROLE or CREATE ROLE command. |
| </para> |
| |
| <para> |
| You can optionally assign a PRIORITY to a resource queue to control the relative |
| share of available CPU resources used by queries associated with the queue |
| in relation to other resource queues. If a value is not defined for PRIORITY, |
| queries associated with the queue have a default priority of MEDIUM. |
| </para> |
| |
| <para> |
| Resource queues with an optional MEMORY_LIMIT threshold set a maximum limit |
| on the amount of memory that all queries submitted through a resource queue |
| can consume on a segment host. This determines the total amount of memory that |
| all worker processes of a query can consume on a segment host during query execution. |
| </para> |
| |
| <para> |
| Greenplum recommends that MEMORY_LIMIT be used in conjunction with ACTIVE_STATEMENTS |
| rather than with MAX_COST. The default amount of memory allotted per query on |
| statement-based queues is: MEMORY_LIMIT / ACTIVE_STATEMENTS. The default amount |
| of memory allotted per query on cost-based queues is: |
| MEMORY_LIMIT * (query_cost / MAX_COST). |
| </para> |
| |
| <para> |
| The default memory allotment can be overridden on a per-query basis using |
| the statement_mem server configuration parameter, provided that MEMORY_LIMIT |
| or max_statement_mem is not exceeded. For example, to allocate more memory |
| to a particular query: |
| |
| => SET statement_mem='2GB'; |
| |
| => SELECT * FROM my_big_table WHERE column='value' ORDER BY id; |
| |
| => RESET statement_mem; |
| </para> |
| |
| <para> |
| As a general guideline, MEMORY_LIMIT for all of your resource queues should not |
| exceed the amount of physical memory of a segment host.If workloads are staggered |
| over multiple queues, memory allocations can be oversubscribed. However, queries |
| can be cancelled during execution if the segment host memory limit specified |
| in hawq_re_memory_overcommit_max is exceeded. |
| </para> |
| |
| </refsect1> |
| |
| <refsect1> |
| <title>Parameters</title> |
| |
| <variablelist> |
| <varlistentry> |
| <term><replaceable class="parameter">name</replaceable></term> |
| <listitem> |
| <para> |
| The name of the new resource queue. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>ACTIVE_STATEMENTS</literal> <replaceable class="parameter">integer</replaceable></term> |
| <listitem> |
| <para> |
| Resource queues with an <literal>ACTIVE_STATEMENTS</literal> |
| limit the number of queries that can be executed by roles assigned |
| to that queue. It controls the number of active queries that are |
| allowed to run at the same time. The value for <literal>ACTIVE_STATEMENTS</literal> |
| should be an integer greater than 0. If a value is not defined for |
| <literal>ACTIVE_STATEMENTS</literal>, it is set to -1 by default (meaning no limit). |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>MAX_COST</literal> <replaceable class="parameter">float</replaceable></term> |
| <listitem> |
| <para> |
| Resource queues with a <literal>MAX_COST</literal> limit the total cost of |
| queries that can be executed by roles assigned to that queue. Cost |
| is measured in the estimated total cost for the query as determined |
| by the query planner (as shown in the <command>EXPLAIN</command> output for a query). |
| Therefore, an administrator must be familiar with the queries typically |
| executed on the system in order to set an appropriate cost threshold for |
| a queue. Cost is measured in units of disk page fetches; 1.0 equals one |
| sequential disk page read. The value for <literal>MAX_COST</literal> |
| is specified as a floating point number (for example 100.0) or can also be |
| specified as an exponent (for example 1e+2). If a value is not defined |
| for <literal>MAX_COST</literal>, it is set to -1 by default |
| (meaning no limit). |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>MIN_COST</literal> <replaceable class="parameter">float</replaceable></term> |
| <listitem> |
| <para> |
| If a Resource queue has an <literal>MIN_COST</literal> > 0 |
| any query whose cost is < this value is not queued i.e it is ignored |
| for resource scheduling purposes. |
| </para> |
| |
| <para> |
| A value of -1.0 means all queries are ignored. This is the default. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>COST_OVERCOMMIT</literal></term> |
| <listitem> |
| <para> |
| These clauses determine whether the <literal>resource queue</literal> |
| will allow statements whose potential <literal>cost</literal> is |
| larger than the queue's <literal>cost threshold</literal> to execute. |
| </para> |
| <para> |
| In the default <literal>COST_OVERCOMMIT=FALSE</literal> case, such statements |
| produce an error. If <literal>COST_OVERCOMMIT</literal> is set for the |
| queue, then they will be allowed to run when there are no other |
| statements in the queue. |
| </para> |
| </listitem> |
| </varlistentry> |
| </variablelist> |
| </refsect1> |
| |
| <refsect1> |
| <title>Notes</title> |
| <para> |
| To enable the resource scheduling and workload management feature, |
| you must set the <literal>postgresql.conf</literal> parameter |
| <literal>resource_scheduler</literal> to <literal>on</literal>. |
| </para> |
| |
| |
| <para> |
| Use <xref linkend="SQL-ALTERRESOURCEQUEUE" endterm="SQL-ALTERRESOURCEQUEUE-title"> to |
| change the attributes of a resource queue, and <xref linkend="SQL-DROPRESOURCEQUEUE" |
| endterm="SQL-DROPRESOURCEQUEUE-title"> to remove a resource queue. All the attributes |
| specified by <command>CREATE RESOURCE QUEUE</> can be modified by later by the |
| <command>ALTER RESOURCE QUEUE</> command. |
| </para> |
| |
| <para> |
| Use <xref linkend="SQL-ALTERROLE" endterm="SQL-ALTERROLE-title"> |
| to add or remove users from a resource queue. |
| </para> |
| |
| <para> |
| Use the <literal>pg_resqueue</literal> system catalog table to look up |
| information about an existing resource queue. |
| </para> |
| |
| |
| </refsect1> |
| |
| <refsect1> |
| <title>Examples</title> |
| |
| <para> |
| Create a resource queue with an active query limit of 20: |
| <programlisting> |
| CREATE RESOURCE QUEUE myqueue WITH (ACTIVE_STATEMENTS=20); |
| </programlisting> |
| </para> |
| |
| <para> |
| Create a resource queue with a query cost limit of 3000.0: |
| <programlisting> |
| CREATE RESOURCE QUEUE webusers WITH (MAX_COST=3000.0); |
| </programlisting> |
| </para> |
| |
| <para> |
| Create a resource queue with a query cost limit of 3 to the 10th power (or 30000000000.0): |
| |
| <programlisting> |
| CREATE RESOURCE QUEUE myqueue WITH (MAX_COST 3e+10); |
| </programlisting> |
| </para> |
| |
| <para> |
| Create a resource queue with both an active query limit and a query cost limit: |
| <programlisting> |
| CREATE RESOURCE QUEUE myqueue ITH (ACTIVE_STATEMENTS=30, MAX_COST=5000.00); |
| </programlisting> |
| </para> |
| </refsect1> |
| |
| <refsect1> |
| <title>Compatibility</title> |
| |
| <para> |
| There is no <command>CREATE RESOURCE QUEUE</command> in the SQL standard. |
| Resource scheduling is a feature of Bizgres and Greenplum Database. |
| </para> |
| </refsect1> |
| |
| <refsect1> |
| <title>See Also</title> |
| |
| <simplelist type="inline"> |
| <member><xref linkend="sql-createrole" endterm="sql-createrole-title"></member> |
| <member><xref linkend="sql-alterrole" endterm="sql-alterrole-title"></member> |
| <member><xref linkend="sql-alterresourcequeue" endterm="sql-alterresourcequeue-title"></member> |
| <member><xref linkend="sql-dropresourcequeue" endterm="sql-dropresourcequeue-title"></member> |
| </simplelist> |
| </refsect1> |
| </refentry> |
| |
| <!-- Keep this comment at the end of the file |
| Local variables: |
| mode: sgml |
| sgml-omittag:nil |
| sgml-shorttag:t |
| sgml-minimize-attributes:nil |
| sgml-always-quote-attributes:t |
| sgml-indent-step:1 |
| sgml-indent-data:t |
| sgml-parent-document:nil |
| sgml-default-dtd-file:"../reference.ced" |
| sgml-exposed-tags:nil |
| sgml-local-catalogs:"/usr/lib/sgml/catalog" |
| sgml-local-ecat-files:nil |
| End: |
| --> |