<!--
$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:
-->