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.

The following table describes privileges of different tenants to view and modify parameters.

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

Modify parameters by using an SQL statement

MySQL mode

SQL syntax:

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

Parameters:

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

• expression: the value of the parameter after modification.

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

• SCOPE: the effective scope of the modification. Default value: BOTH. Valid values:

  • 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: used in the sys tenant to specify the tenants whose tenant-level parameters are to be modified. Valid values:

  • ALL: all tenants.

  • tenant_name: a specified tenant.

• SERVER: 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 a 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

SQL syntax:

ALTER SYSTEM SET parameter_name = expression

Parameters:

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

• expression: 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. For more information about the tenant-level parameters, see System parameters in System Reference.
  • You can modify tenant-level parameters directly in the current tenant or in the sys tenant by specifying the TENANT keyword.

Example

Modify the log_disk_utilization_threshold parameter:

obclient> ALTER SYSTEM SET log_disk_utilization_threshold = 20;

Query parameters by using an SQL statement

SQL syntax:

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

Here is an example for 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';
+-------+----------+-----------------+----------+-----------------------+-----------+-------+------------------------------------------------------------------------------------------------------------------------------+---------+---------+---------+-------------------+
| zone  | svr_type | svr_ip          | svr_port | name                  | data_type | value | info                                                                                                                         | section | scope   | source  | edit_level        |
+-------+----------+-----------------+----------+-----------------------+-----------+-------+------------------------------------------------------------------------------------------------------------------------------+---------+---------+---------+-------------------+
| zone1 | observer | XXX.XXX.XXX.XXX |     2882 | large_query_threshold | NULL      | 5s    | threshold for execution time beyond which a request may be paused and rescheduled as a \'large request\'. Range: [1ms, +∞)   | TENANT  | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE |
+-------+----------+-----------------+----------+-----------------------+-----------+-------+------------------------------------------------------------------------------------------------------------------------------+---------+---------+---------+-------------------+
1 row in set (0.01 sec)

The following table describes the column attributes in the execution results.

More information

For more information about the parameters, see System parameters.