Product Documentation

Appearance

All platforms

Creating WLM Profiles ​

In terms of how you create it, a profile is simply a container for some resource pools and rules. You can create a profile in Yellowbrick Manager or by using a SQL command:

  • In Yellowbrick Manager, go to Workload Management > +Profile.

You can create a new blank profile, or you can create a profile as a copy of another profile, which duplicates all of the resource pools in the base profile (copied pools are named profile: pool). For example:

To see the profiles that are currently in the system, go to Workload Management in Yellowbrick Manager, query the sys.wlm_active_profile view, or use the ybsql \dwp command, as shown here:

premdb=> \dwp
                 List of WLM profiles
     Name      | Active? |    Default pool    | System? 
----------------+---------+--------------------+---------
 default        | no      | large              | yes
 flex           | no      | mix                | yes
 instance_large | no      | instance_large_mix | yes
 instance_small | yes     | instance_small_mix | yes
 maintenance    | no      | maintenance        | yes
 new            | no      |                    | no
(6 rows)

Profile Activation ​

The active configuration for workload management is a transient state that you set by activating a profile. The resource pools and rules associated with the profile determine the WLM behavior for all of the queries and other operations that run on the system.

You must activate any changes to your WLM configuration before they will take effect. For example, you can add or change a rule at any time, but the new or changed rule will have no effect until you activate the changes. You can use the Activate button in Yellowbrick Manager:

This button becomes available on the Profiles screen when you select a profile or when changes are pending, such as the addition of a WLM rule:

You will also see a green "info" icon next to a profile when it has changes pending:

You can choose to activate the changes immediately or wait a specified period of time for active queries to complete. You can set the profile activation to fail or succeed if queries are still running when the timeout expires.

Note: The target of an Activate operation must be a compute cluster that is running when activation is requested. This operation does not persist between cluster restarts and will fail if the cluster is suspended.

When you activate a profile, the currently active profile is deactivated. There is no specific option or command that deactivates a profile.

You can also use the ALTER WLM PROFILE command to activate profiles.

Download SQL and JSON Options ​

The Actions menu for profiles provides an option to download the definition of a profile as a SQL file or a JSON file. You can save this file for convenient reuse and reference. Select a profile first, then select Download SQL or Download JSON. You may be prompted to select an application suitable for capturing the download.

For example:

This example shows a SQL file downloaded and saved in a text editor:

Dropping Profiles ​

When you drop a profile, its associated resource pools and rules are dropped automatically as well. See DROP WLM PROFILE.

System-Defined Profiles ​

When a Yellowbrick data warehouse is installed, it contains several system-defined profiles, which you cannot remove or modify; however, they may serve as templates for your own profiles. You can use one of these profiles as a base for a new profile that you create.

The default profile remains active until you activate another profile, which guarantees that, in the absence of a user-defined strategy, default WLM behavior is in effect for all queries. If a user-defined profile fails to load and activate at any time, the default profile becomes the active profile.

Note: When any profile is activated for a cluster, it also has the system pool.

default Profile

This profile is defined with the following resource pools, which you can monitor when the profile is active. The actual memory allocation when the profile is activated varies by cluster, depending on the hardware instance type.

flex Profile

This profile contains the following resource pools, which you can monitor when the profile is active. Like the profile itself, these pools cannot be changed. This profile has a flex pool called mix, with a minimum concurrency of 4 and a maximum concurrency of 8.

instance_large and instance_small Profiles

These profiles are targeted for clusters with large and small instance types. They both contain one resource pool. Both pools are flex pools with minimum and maximum concurrency settings (15/30, 2/3).

maintenance Profile

The maintenance profile contains a single resource pool with a fixed concurrency of 10.

Administrators can put the Yellowbrick system in maintenance mode by activating this profile in Yellowbrick Manager or running the following command:

alter wlm profile "maintenance" activate;

All connected sessions for regular users are closed, and their active queries are canceled. While the maintenance profile is active, regular users cannot connect to the database; they will see a System in maintenance mode fatal error message. Any superuser can turn maintenance mode off by switching to another profile. For example:

alter wlm profile "default" activate;