SET CLUSTER SETTING

On this page Carat arrow pointing down

The SET CLUSTER SETTING statement modifies a cluster-wide setting.

Warning:
Many cluster settings are intended for tuning CockroachDB internals. Before changing these settings, we strongly encourage you to discuss your goals with CockroachDB; otherwise, you use them at your own risk.

Required privileges

To use the SET CLUSTER SETTING statement, a user must have one of the following attributes:

  • Be a member of the admin role. (By default, the root user belongs to the admin role.)
  • Have the MODIFYCLUSTERSETTING system-level privilege granted. root and admin users have this system-level privilege by default and are capable of granting it to other users and roles using the GRANT statement. For example to grant this system-level privilege to user maxroach:

    GRANT SYSTEM MODIFYCLUSTERSETTING TO maxroach;
    
Note:

New in 22.2.7: The cluster setting sql.auth.modify_cluster_setting_applies_to_all.enabled affects what users with the MODIFYCLUSTERSETTING privilege are able to modify:

  • If set to true (the default), users are able to modify all cluster settings.
  • If set to false, users are allowed to modify only sql.defaults.* cluster settings, not all cluster settings.

Synopsis

SET CLUSTER SETTING var_name = TO var_value
Note:
The SET CLUSTER SETTING statement is unrelated to the other SET TRANSACTION and SET {session variable} statements.

Parameters

Parameter Description
var_name The name of the cluster setting (case-insensitive).
var_value The value for the cluster setting.
DEFAULT Reset the cluster setting to its default value.

The RESET CLUSTER SETTING resets a cluster setting as well.

Examples

Change the default distributed execution parameter

To configure a cluster so that new sessions automatically try to run queries in a distributed fashion:

icon/buttons/copy
> SET CLUSTER SETTING sql.defaults.distsql = 1;

To disable distributed execution for all new sessions:

icon/buttons/copy
> SET CLUSTER SETTING sql.defaults.distsql = 0;
Note:

New in v22.2: Use ALTER ROLE ALL SET {sessionvar} = {val} instead of the sql.defaults.* cluster settings. This allows you to set a default value for all users for any session variable that applies during login, making the sql.defaults.* cluster settings redundant.

Disable automatic diagnostic reporting

To opt out of automatic diagnostic reporting of usage data to Cockroach Labs:

icon/buttons/copy
> SET CLUSTER SETTING diagnostics.reporting.enabled = false;
icon/buttons/copy
> SHOW CLUSTER SETTING diagnostics.reporting.enabled;
  diagnostics.reporting.enabled
---------------------------------
              false
(1 row)

Reset a setting to its default value

Tip:
You can use RESET CLUSTER SETTING to reset a cluster setting as well.
icon/buttons/copy
> SET CLUSTER SETTING sql.metrics.statement_details.enabled = false;
icon/buttons/copy
> SHOW CLUSTER SETTING sql.metrics.statement_details.enabled;
  sql.metrics.statement_details.enabled
-----------------------------------------
                  false
(1 row)
icon/buttons/copy
> SET CLUSTER SETTING sql.metrics.statement_details.enabled = DEFAULT;
icon/buttons/copy
> SHOW CLUSTER SETTING sql.metrics.statement_details.enabled;
  sql.metrics.statement_details.enabled
-----------------------------------------
                  true
(1 row)

See also


Yes No
On this page

Yes No