Resource pools

Resource pools

A resource pool comprises a pre-allocated subset of the system resources with an associated queue. Super users can view information about resource pool parameters and make changes to existing parameters through the Resource pools page.

View resource pool details

You can view information about the resource pools available in the system. To view resource pool details:

1. In the Settings page, select Resource pools. The Resource pool page lists the following information:
   • Name - Name of the resource pool.
   • Memory size - Total per-node memory available to the database resource manager that is allocated to this resource pool.
   • Runtime cap (seconds) - Maximum time (in seconds) for which a query in the resource pool can execute. If a query exceeds this setting, it cascades to a secondary pool.
   • Priority - Priority of queries in this resource pool when they compete for resources in the GENERAL pool.
   • Runtime priority - Specifies how the resource manager prioritizes run-time resources (CPU, I/O bandwidth) to queries already running in this resource pool.
   • Queue timeout - The interval that the request waits for resources to become available before being rejected.
   • Max concurrency - Maximum number of concurrent execution slots available to the resource pool across the cluster.
2. In the Actions column, select ⋮ and View details. The Pool details page lists pool-specific information.
3. Select Close.

Create a resource pool

Only super users can create and configure resource pools.

1. In the Settings page, select Resource pools > Create. The Create resource pools page is displayed.

2. In the Pool name field, enter a unique name for the resource pool. If you specify a resource pool name with uppercase letters, OTCAD converts them to lowercase letters.

   The following naming requirements apply:

   • Built-in pool names cannot be used for user-defined pools.
   • All user-defined global resource pools must have unique names.
   • Names of global resource pools cannot be the same.

3. From the Cascade to list, select an existing resource pool that is either a user-defined pool or the GENERAL pool.

4. From the User list, assign existing users to a resource pool. This resource pool is used to process all queries from its assigned users, allocating resources as set by resource pool parameters. The selected user is added to the resource pool by default.

5. In the Minimum memory field, enter the total per-node memory available to the database resource manager that is allocated to this resource pool. The default is 0%. From the list, select the percentage of total memory, or amount of memory in kilobytes, megabytes, gigabytes, or terabytes.

6. In the Maximum memory field, enter the maximum size per node the resource pool can grow by using memory from the GENERAL pool. From the list, select the percentage of total memory, or amount of memory in kilobytes, megabytes, gigabytes, or terabytes.

7. In the Maximum query memory field, enter the maximum amount of memory this resource pool can allocate at runtime to process a query. If the query requires more memory than this setting, OTCAD stops and returns an error. From the list, select the percentage of maximum memory, or amount of memory in kilobytes, megabytes, gigabytes, or terabytes upto the value of maximum memory.

8. The CPU affinity mode specifies whether the resource pool has exclusive or shared use of the CPUs specified in the Affinity set field below. Select one of these values:

   • Shared - Queries that run in this resource pool share its Affinity set CPUs with other resource pools.
   • Exclusive - Dedicates Affinity set CPUs to this resource pool only and excludes other resource pools. If Affinity set is set as a percentage, then that percentage of CPU resources available only are assigned to this resource pool.
   • Any - Queries in this resource pool can run on any CPU.

9. Affinity set - CPUs available to this resource pool. All nodes must have the same number of CPUs. The CPU resources assigned to this set are not available to GENERAL resource pools. From the list, do one of the following:

   • Select Range to dedicate a range of contiguous CPU indexes in the From and To fields to this resource pool.
   • Select Single to dedicate one or more comma-delimited CPUs to this resource pool.
   • Select Percentage to dedicate the percentage of all available CPUs to use for this resource pool. The database rounds this percentage down to include whole CPU units.

10. In the Minimum concurrency field, enter the preferred number of queries to execute concurrently in the resource pool. Select the Auto option to calculate the value automatically at query runtime. The system sets this parameter to the lower of these two calculations, but never less than 4:

    • Number of logical cores
    • Memory divided by 2GB

    Change this parameter only after evaluating performance over a period of time.

    Note

       If the number of logical cores on each node is different, **Auto** is calculated differently for each node. Distributed queries run like the minimal effective planned concurrency. Single node queries run with the planned concurrency of the initiator.
    

11. In the Maximum concurrency field, enter the maximum number of concurrent execution slots available to the resource pool.

12. In the Execution parallelism field, enter the number of threads used to process any single query issued in this resource pool. Select the Auto option to calculate the setting from the number of cores, available memory, and amount of data in the system. Unless memory is limited, or the amount of data is very small, the system sets this parameter to the number of cores on the node.

13. In the Pool priority field, enter the priority of queries in this resource pool when they compete for resources in the GENERAL pool. Enter either negative or positive integer values, where higher numbers denote higher priority.

    • For user-defined resource pools, enter a value in the range -100 to 100.
    • For built-in resource pools such as SYSQUERY, RECOVERY, and TM, enter a value in the range of -110 to 110.

14. From the Runtime priority list, select how the resource manager should prioritize dedication of run-time resources (CPU, I/O bandwidth) to queries already running in this resource pool. Select one of Low, Medium, or High. The default is Medium.

15. In the Runtime priority threshold field, enter the maximum time (in seconds) in which query processing must complete before the resource manager assigns to it the resource pool's RUNTIMEPRIORITY. All queries run with a priority of HIGH. The default value is 2.

16. In the Queue timeout (seconds) field, enter the maximum time (not more than an year) a request can wait (in seconds) for pool resources before it is rejected. There is no maximum wait time. Requests can be queued indefinitely up to one year.

17. In the Runtime cap (seconds) field, enter the maximum run time allowed (not more than an year) to queries in this resource pool. If a query exceeds this setting, it cascades to a secondary pool.

Edit a resource pool

After creating a resource pool, you can edit the details to suit your requirements. Only database administrators can edit resource pools.

1. In the Resource pools page, under the Actions column, choose ⋮ and Edit. All the details about the resource pool are populated. You cannot edit the following fields:

   • Pool name
   • Affinity set
   • Minimum currency
   • Execution parallelism

2. From the Cascade to list, choose the secondary resource pool for executing queries that exceed the RUNTIMECAP setting of their assigned resource pool.

3. From the User list, assign existing users to a resource pool. This resource pool is used to process all queries from its assigned users, allocating resources as set by resource pool parameters. The selected user is added to the resource pool by default.

4. In the Minimum memory field, enter the total per-node memory available to the database resource manager that is allocated to this resource pool. The default is 0%. From the list, select the percentage of total memory, or amount of memory in kilobytes, megabytes, gigabytes, or terabytes.

5. In the Maximum memory field, edit the maximum size per node the resource pool can grow by using memory from the GENERAL pool. From the list, select the percentage of total memory, or amount of memory in kilobytes, megabytes, gigabytes, or terabytes.

6. In the Maximum query memory field, edit the maximum amount of memory this resource pool can allocate at runtime to process a query. If the query requires more memory than this setting, OTCAD stops and returns an error. From the list, select the percentage of maximum memory, or amount of memory in kilobytes, megabytes, gigabytes, or terabytes upto the value of maximum memory.

7. The CPU affinity mode specifies whether the resource pool has exclusive or shared use of the CPUs specified in the Affinity set field below. Select one of these values:

   • Shared - Queries that run in this resource pool share its Affinity set CPUs with other resource pools.
   • Exclusive - Dedicates Affinity set CPUs to this resource pool only and excludes other resource pools. If Affinity set is set as a percentage, then that percentage of CPU resources available only are assigned to this resource pool.

8. Affinity set - Edit the CPUs available to this resource pool. All nodes must have the same number of CPUs. The CPU resources assigned to this set are not available to GENERAL resource pools. From the list, do one of the following:

   • Select Range to dedicate a range of contiguous CPU indexes in the From and To fields to this resource pool.
   • Select Single to dedicate one or more comma-delimited CPUs to this resource pool.
   • Select Percentage to dedicate the percentage of all available CPUs to use for this resource pool. The database rounds this percentage down to include whole CPU units.

9. In the Maximum concurrency field, edit the maximum number of concurrent execution slots available to the resource pool.

10. In the Pool priority field, edit the priority of queries in this resource pool when they compete for resources in the GENERAL pool. Edit either negative or positive integer values, where higher numbers denote higher priority.

    • For user-defined resource pools, enter a value in the range -100 to 100.
    • For built-in resource pools such as SYSQUERY, RECOVERY, and TM, enter a value in the range of -110 to 110.

11. From the Runtime priority list, select how the resource manager should prioritize dedication of run-time resources (CPU, I/O bandwidth) to queries already running in this resource pool. Select one of Low, Medium, or High. The default is Medium.

12. In the Runtime priority threshold field, edit the maximum time (in seconds) in which query processing must complete before the resource manager assigns to it the resource pool's RUNTIMEPRIORITY. All queries run with a priority of HIGH. The default value is 2.

13. In the Queue timeout (seconds) field, edit the maximum time (not more than an year) a request can wait (in seconds) for pool resources before it is rejected. There is no maximum wait time. Requests can be queued indefinitely up to one year.

14. In the Runtime cap (seconds) field, edit the maximum run time allowed (not more than an year) to queries in this resource pool. If a query exceeds this setting, it cascades to a secondary pool.

    For more information about editing a resource pool, see Create a resource pool.

Delete a resource pool

You can delete a resource pool that is no longer in use or required.

1. In the Resource pools page, under the Actions column, choose ⋮ and Delete.
2. In the Confirmation dialog, select Delete. Requests that are queued against the deleted pool are transferred to the GENERAL pool according to the priority of the pool compared to the GENERAL pool. If the pool’s priority is higher than the GENERAL pool, the requests are placed at the head of the queue; otherwise, transferred requests are placed at the end of the queue.