doc/src/sgml/ref/create_resource_queue.sgml - hawq - Git at Google

 <!--
 $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> &gt; 0
         any query whose cost is &lt; 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:
 -->
	<!--
	$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:
	-->