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 the SMC or by using a SQL command:
  • Go to Manage > Workloads > Create Profile in the SMC.

    In the SMC, you can create a new blank profile, which contains the system pools only, or you can create a profile as a copy of another profile, which contains copies of all of the resource pools in the base profile (copied pools are named profile: pool in the SMC).

  • Use the SQL command CREATE WLM PROFILE.

When you create any new profile, it contains the system resource pools by default.

To see a list of profiles that are currently in the system, either query the sys.wlm_active_profile view or use the ybsql \dwp command.

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 Changes button in the SMC, which becomes available on the Profiles/Resource Pools screen when any changes are 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.

If you are activating a new or different profile, you can also use the ALTER WLM PROFILE command.

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

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 the Yellowbrick database is installed, it contains a flex profile, a default profile, and a maintenance profile. The default profile remains active until you activate another profile. You cannot modify or remove the default 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.

default Profile

This profile contains four resource pools, which you can monitor when the profile is active. Like the profile itself, these pools cannot be changed. The actual memory allocation varies by system. The following examples show actual memory and temporary space allocation on a 124GB, single-chassis, 15-blade system.

Name of Pool Memory % Slots (max/min) Queue Size
admin 3% 1/1 2000
large (default pool) 80% 4/4 2000
small 7% 8/8 2000
system 9% 3/2 100

flex Profile

This profile contains three resource pools, which you can monitor when the profile is active. Like the profile itself, these pools cannot be changed. This pool is a template that you can use as an example of a flexible profile with a flex pool called mix.

Name of Pool Memory % Slots (max/min) Queue Size
long 20% 1/1 2000
mix (default pool) 71% 24/4 2000
system 9% 3/2 100

maintenance Profile

The maintenance profile contains only two resource pools, which can be monitored when the profile is active. Like the profile itself, these pools cannot be changed.

Name of Pool Memory % Slots (max/min) Queue Size
maintenance (default pool) 91% 10/10 100
system 9% 3/2 100

The following example shows actual memory and temporary space allocation on a dual-chassis, 30-blade system:

WLM Maintenance Mode

Administrators can put the Yellowbrick system in maintenance mode by running the following command:
alter wlm profile "maintenance" activate;
All connected, non-superuser sessions 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;