Managing Workloads

Vertica's resource management scheme allows diverse, concurrent workloads to run efficiently on the database. For basic operations, Vertica pre-configures the built-in GENERAL pool based on RAM and machine cores. You can customize the General pool to handle specific concurrency requirements.

You can also define new resource pools that you configure to limit memory usage, concurrency, and query priority. You can then optionally assign each database user to use a specific resource pool, which controls memory resources used by their requests.

User-defined pools are useful if you have competing resource requirements across different classes of workloads. Example scenarios include:

• A large batch job takes up all server resources, leaving small jobs that update a web page without enough resources. This can degrade user experience.

  In this scenario, create a resource pool to handle web page requests and ensure users get resources they need. Another option is to create a limited resource pool for the batch job, so the job cannot use up all system resources.

• An application has lower priority than other applications and you want to limit the amount of memory and number of concurrent users for the low-priority application.

  In this scenario, create a resource pool with an upper limit on the query's memory and associate the pool with users of the low-priority application.

You can also use resource pools to manage resources assigned to running queries. You can assign a run-time priority to a resource pool, as well as a threshold to assign different priorities to queries with different durations. See Managing Resources At Query Run Time for more information.

For detailed syntax of creating and managing resource pools see the following topics in the SQL Reference Manual:

Statements

• ALTER RESOURCE POOL alters a resource pool.
• ALTER USER associates a user with the RESOURCE POOL and MEMORYCAP parameters.
• CREATE RESOURCE POOL creates a resource pool.
• CREATE USER adds a name to the list of authorized database users and specifies that user's RESOURCE POOL and MEMORYCAP parameters.
• DROP RESOURCE POOL drops a user-created resource pool.
• SET SESSION MEMORYCAP sets the limit on amount of memory that any request issued by the session can consume.
• SET SESSION RESOURCE POOL associates a user session with specified resource pool.

System Tables

• RESOURCE_ACQUISITIONS provides details of resources (memory, open file handles, threads) acquired by each request for each resource pool in the system.
• RESOURCE_POOL_DEFAULTS (systab) lists default values for parameters in each internal and user-defined resource pool.
• RESOURCE_POOL_STATUS provides configuration settings of the various resource pools in the system, including internal pools.
• RESOURCE_POOLS displays information about the parameters the resource pool was configured with.
• RESOURCE_QUEUES provides information about requests pending for various resource pools.
• RESOURCE_REJECTIONS monitors requests for resources that are rejected by the Resource Manager.
• RESOURCE_REJECTION_DETAILS records an entry for each resource request that Vertica denies. This is useful for determining if there are resource space issues, as well as which users/pools encounter problems
• SYSTEM_RESOURCE_USAGE provides history about system resources, such as memory, CPU, network, disk, I/O.