Set parameters

OceanBase Database provides cluster-level and tenant-level parameters. You can set these parameters to make sure that the behaviors of OceanBase Database meet your business requirements.

Parameter levels

OceanBase Database provides cluster-level and tenant-level parameters. You can set these parameters to control the load balancing, major compaction time, major compaction mode, resource allocation, and module switches of the entire cluster.

• Cluster-level parameters apply to all OBServer nodes in the cluster.

• Tenant-level parameters apply to OBServer nodes in the current tenant.

Parameters whose names start with an underscore (_), such as _ob_max_thread_num, are hidden parameters. Hidden parameters are used by developers only for troubleshooting or emergency O&M. This topic does not describe hidden parameters, nor does it involve any.

Different types of tenants have different privileges regarding querying and modifying parameters.

The following table describes the data types of parameters in OceanBase Database.

Modify parameters by using an SQL statement

MySQL mode

The SQL syntax for modifying a parameter is as follows:

ALTER SYSTEM [SET]
parameter_name = expression [SCOPE = {MEMORY | SPFILE | BOTH}] [COMMENT [=} 'text']
[ TENANT [=] ALL|all_user|all_meta|tenant_name ] {SERVER [=] 'ip:port' | ZONE [=] 'zone'};

where

• SET specifies the keyword used to set a new value for the parameter.

• parameter_name specifies the name of the parameter to be modified.

• expression specifies the value of the parameter after modification.

• COMMENT 'text' specifies the comment to be added for the modification. This parameter is optional. We recommend that you specify this parameter.

• SCOPE specifies the effective scope of the modification. The default value is BOTH. Valid values include:

  • MEMORY: indicates that the parameter is modified only in the memory. The modification takes effect immediately and becomes invalid after OBServer nodes are restarted. Currently, no parameter supports this effective scope.

  • SPFILE: indicates that only the parameter value in the internal table is modified. The modification takes effect after the OBServer node is restarted. This value is valid only for the parameters that take effect upon a restart.

  • BOTH: indicates that the parameter value is modified in both the internal table and the memory. The modification takes effect immediately and remains effective after the OBServer node is restarted.

• TENANT is used in the sys tenant to specify the tenants whose tenant-level parameters are to be modified. Valid values include:

  • ALL: Starting from OceanBase Database V4.2.1, tenant = all and tenant = all_user have the same semantics and specify to take effect on all user tenants. The all syntax is to be deprecated. We recommend that you do not use this syntax.

  • all_user: specifies to take effect on all user tenants.

  • all_meta: specifies to take effect on all meta tenants.

  • tenant_name: specifies to take effect on a specified tenant.

• SERVER specifies the server for which the parameter is to be modified.

• ZONE indicates that the parameter is modified for the specific server type of the specified cluster. If this parameter is not specified, the parameter is modified for the specific server type of all clusters.

  Note

  • When you modify multiple parameters at a time, separate the parameters with commas (,).
  • You can set cluster-level parameters only in the sys tenant. You cannot set cluster-level parameters in a user tenant or perform this operation in the sys tenant by specifying a user tenant. For example, the ALTER SYSTEM SET memory_limit='100G' TENANT='test_tenant' statement will cause an error because memory_limit is a cluster-level parameter.
  • You can modify tenant-level parameters directly in the current tenant or in the sys tenant by specifying the TENANT keyword.
  • The ALTER SYSTEM statement allows you to specify either a zone or an OBServer node, not both. You can specify only one zone or OBServer node at a time. If you do not specify a zone or an OBServer node, the modification of a cluster-level parameter takes effect on all OBServer nodes in the cluster, and the modification of a tenant-level parameter takes effect on the OBServer node where the current tenant is deployed in the cluster.
  • The value of the scope parameter in the execution results of the SHOW PARAMETERS statement specifies whether a parameter is a cluster-level or tenant-level parameter.
  • If the value of scope is CLUSTER, the parameter is a cluster-level parameter.
  • If the value of scope is TENANT, the parameter is a tenant-level parameter.

Examples

Modify the log_disk_utilization_threshold parameter:

obclient> ALTER SYSTEM SET log_disk_utilization_threshold = 20;

Modify the log_disk_utilization_threshold parameter for a specified zone:

obclient> ALTER SYSTEM SET log_disk_utilization_threshold = 20 ZONE='z1';

Modify the log_disk_utilization_threshold parameter for a specified OBServer node:

obclient> ALTER SYSTEM SET log_disk_utilization_threshold = 20 SERVER='XXX.XXX.XXX.XXX:XXXXX';

Modify a tenant-level parameter for all tenants or a specified tenant in the sys tenant:

obclient> ALTER SYSTEM SET log_disk_utilization_threshold = 20 TENANT='ALL';
obclient> ALTER SYSTEM SET log_disk_utilization_threshold = 20 TENANT='Oracle';

Oracle mode

The SQL syntax for modifying a parameter is as follows:

ALTER SYSTEM [SET] parameter_name = expression

where

• SET specifies the keyword used to set a new value for the parameter.

• parameter_name specifies the name of the parameter to be modified.

• expression specifies the value of the parameter after modification.

  Note

  • When you modify multiple parameters at a time, separate the parameters with commas (,).
  • In Oracle mode, you can set only tenant-level parameters.
  • You can modify tenant-level parameters directly in the current tenant or in the sys tenant by specifying the TENANT keyword.

Examples

Modify the log_disk_utilization_threshold parameter:

obclient> ALTER SYSTEM SET log_disk_utilization_threshold = 20;

Reset a parameter by using an SQL statement

MySQL mode

The SQL syntax for resetting a parameter is as follows:

ALTER SYSTEM [RESET]
parameter_name [SCOPE = {MEMORY | SPFILE | BOTH}] {TENANT [=] 'tenant_name'};

where

• RESET specifies the keyword used to clear the current value of the parameter and use its default value or start value.

• SCOPE specifies the effective scope of the modification. The default value is BOTH. Valid values include the following ones:

  • MEMORY: specifies to reset only the parameter value in the memory. The modification takes effect immediately and becomes invalid after the OBServer node is restarted. At present, no parameter supports this effective scope.

  • SPFILE: specifies to reset only the parameter value in the internal table. The modification takes effect after the OBServer node is restarted. This value is valid only for the parameters that take effect upon a restart.

  • BOTH: specifies to reset the parameter value in both the internal table and the memory. The modification takes effect immediately and remains effective after the OBServer node is restarted.

• TENANT: specifies to reset the tenant-level parameters of all tenants or a specified tenant from the sys tenant.

  • tenant_name: the name of the tenant whose tenant-level parameters are to be reset.

  Note

  • Separate multiple parameters with commas (,).
  • You cannot reset cluster-level parameters in a user tenant or reset cluster-level parameters for a specified user tenant from the sys tenant. You can reset cluster-level parameters only in the sys tenant. For example, if you attempt to execute the ALTER SYSTEM RESET memory_limit TENANT='test_tenant' statement, an error will be returned because memory_limit is a cluster-level parameter.
  • You can modify tenant-level parameters directly in the current tenant, or in the sys tenant by specifying the TENANT keyword.
  • You cannot specify a zone or OBServer node when you execute the ALTER SYSTEM RESET statement.
  • The value of the scope parameter in the execution results of the SHOW PARAMETERS statement specifies whether a parameter is a cluster-level or tenant-level parameter.
  • If the value of scope is CLUSTER, the parameter is a cluster-level parameter.
  • If the value of scope is TENANT, the parameter is a tenant-level parameter.

Examples

Reset the log_disk_utilization_threshold parameter.

obclient> ALTER SYSTEM RESET log_disk_utilization_threshold;

Reset a tenant-level parameter for all tenants or a specified tenant from the sys tenant.

Here are two examples:

obclient> ALTER SYSTEM RESET log_disk_utilization_threshold TENANT='ALL';
obclient> ALTER SYSTEM RESET log_disk_utilization_threshold TENANT='Oracle';

Oracle mode

The SQL syntax for resetting a parameter is as follows:

ALTER SYSTEM [RESET] parameter_name = expression;

where

• RESET specifies the keyword used to clear the current value of the parameter and use its default value or start value.

• parameter_name specifies the name of the parameter to be reset.

• expression specifies the new value of the parameter.

  Note

  • Separate multiple parameters with commas (,).
  • Only tenant-level parameters can be reset in Oracle mode.
  • You can reset tenant-level parameters directly in the current tenant, or in the sys tenant by specifying the TENANT keyword.

Example

Reset the log_disk_utilization_threshold parameter.

obclient> ALTER SYSTEM RESET log_disk_utilization_threshold;

Query parameters by using an SQL statement

The SQL syntax is as follows:

SHOW PARAMETERS [LIKE 'pattern' | WHERE expr] [TENANT = tenant_name]

Here are some examples of querying parameters by using the SHOW PARAMETERS statement:

obclient> SHOW PARAMETERS WHERE scope = 'tenant';

obclient> SHOW PARAMETERS WHERE svr_ip != 'XXX.XXX.XXX.XXX';

obclient> SHOW PARAMETERS WHERE INFO like '%ara%';

obclient> SHOW PARAMETERS LIKE 'large_query_threshold';

The return result is as follows:

+-------+----------+----------------+----------+-----------------------+-----------+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------+---------+-------------------+
| zone  | svr_type | svr_ip         | svr_port | name                  | data_type | value | info                                                                                                                                                              | section | scope   | source  | edit_level        |
+-------+----------+----------------+----------+-----------------------+-----------+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------+---------+-------------------+
| zone1 | observer | 172.xx.xxx.xxx |     2882 | large_query_threshold | TIME      | 5s    | threshold for execution time beyond which a request may be paused and rescheduled as a \'large request\', 0ms means disable \'large request\'. Range: [0ms, +∞)   | TENANT  | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE |
+-------+----------+----------------+----------+-----------------------+-----------+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------+---------+-------------------+
1 row in set

The following table describes the column attributes in the return result.

More information

For more information about parameters, see Overview.