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 is as follows:

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

where

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

  • 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: specifies to take effect on all 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 is as follows:

ALTER SYSTEM SET parameter_name = expression

where

• 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. 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

The SQL syntax is as follows:

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

The following example shows how to query 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 parameters, see Overview of parameters.