Product Documentation

Appearance

7.1.2
7.0.2
6.9.3
5.4.17
5.2.31

Creating 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).

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.

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.

flex Profile ​

This profile contains several 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 flexible resource pools.

  • system:large: memory fixed at 6GB, only 1 query slot
  • system:medium: memory fixed at 4.3GB, only 1 query slot
  • system:small: memory fixed at 1.1GB, only 1 query slot
  • long pool: memory fixed at 13.7GB, only 1 query slot
  • mix pool: memory fixed at 38.8GB, minimum concurrency 3, maximum 12

default Profile ​

This profile contains several resource pools, which you can monitor when the profile is active. Like the profile itself, these pools cannot be changed.

  • large: the default resource pool for the profile. About 75% of the available memory is allocated to this pool, with 4 query slots. This pool is also allocated a large amount of temporary space.
  • small: maximum of 8 query slots, 1.1GB per slot
  • admin: smallest amount of memory (about 1%), a small amount of temporary space, maximum of 2 query slots
  • system:large: memory fixed at 6GB, only 1 query slot
  • system:medium: memory fixed at 4.3GB, only 1 query slot
  • system:small: memory fixed at 1.1GB, only 1 query slot

maintenance Profile ​

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

  • maintenance: the default resource pool for the profile. About 85% of the available memory is allocated to this pool.
  • The same set of system pools as the default profile

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;

Parent topic:How WLM Works