Set parameters

2025-04-28 03:35:15  Updated

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

Tenant type Parameters that can be queried Parameters that can be set
sys tenant Cluster-level parameters and tenant-level parameters

Note

You can specify the TENANT keyword in the SHOW PARAMETERS statement to view the parameter settings of a specified tenant.

 Cluster-level parameters and tenant-level parameters

Note

In the sys tenant, you can specify the TENANT keyword to modify the parameters of all tenants or a specific tenant.
User tenant Cluster-level parameters, and tenant-level parameters of the current tenant Tenant-level parameters of the current tenant

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

Data type Description
BOOL The Boolean type. Valid values: true and false.
CAPACITY The unit of capacity. Valid values: b (bytes), k (KB), m (MB), g (GB), t (TB), and p (PB). The unit is case-insensitive. Default value: m.
DOUBLE The Double (double-precision floating-point) type. A value of this data type occupies a storage space of 64 bits, contains 16 valid digits, and is accurate to 15 digits after the decimal point.
INT The Int64 type. A value of this data type can be a positive integer, negative integer, or 0.
MOMENT The type that represents a moment in the hh:mm format, such as 02:00. Special value: disable, which indicates that no time is specified. This data type applies only to the major_freeze_duty_time parameter.
STRING The string type. A value of this type is entered by users.
STRING_LIST The type that represents a list of strings separated with semicolons (;).
TIME The time type. The following time units are supported: us (microseconds), ms (milliseconds), s (seconds), m (minutes), h (hours), and d (days). If no suffix is added to a value of this data type, the unit s is used by default. The unit is case-insensitive.

Modify parameters

MySQL mode
Oracle mode

The SQL syntax for modifying parameters in MySQL mode is as follows:

ALTER SYSTEM [SET]
parameter_name = expression [SCOPE = {SPFILE | BOTH}] [COMMENT [=] 'text']
[ TENANT [=] ALL|all_user|all_meta|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 the following ones:

    • 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 the following ones:

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

Example: Modify the log_disk_utilization_threshold parameter.

obclient> ALTER SYSTEM SET log_disk_utilization_threshold = 20;

Example: Modify the log_disk_utilization_threshold parameter for a specified zone.

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

Example: 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';

Example: 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';

Note

After the statement is executed, the parameter is modified for all the specified tenants.

The SQL syntax for modifying parameters in Oracle mode 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.
    • 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;

Reset parameters

MySQL mode
Oracle mode

The SQL syntax for resetting parameters in MySQL mode 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 specifies to take effect on a specified tenant.

    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.

Example: Reset the log_disk_utilization_threshold parameter.

obclient> ALTER SYSTEM RESET log_disk_utilization_threshold;

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

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

Note

After the statement is executed, the parameter is modified for all the specified tenants.

The SQL syntax for resetting parameters in Oracle mode 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;

View parameters

The SQL syntax for querying a parameter is as follows:

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

Note

  • In the sys tenant, you can query the tenant-level and cluster-level parameters of the current tenant. You can also query parameters of all tenants or a specified tenant by specifying the TENANT keyword.
  • In a user tenant, you can query the tenant-level parameters of the current tenant and cluster-level parameters of the sys tenant.
  • A column attribute specified in the WHERE expr clause must be a column attribute in the execution results of the SHOW PARAMETERS statement.

Here is an example 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 result is as follows:

+-------+----------+----------------+----------+-----------------------+-----------+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------+---------+-------------------+---------------+-----------+
| zone  | svr_type | svr_ip         | svr_port | name                  | data_type | value | info                                                                                                                                                              | section | scope   | source  | edit_level        | default_value | isdefault |
+-------+----------+----------------+----------+-----------------------+-----------+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------+---------+-------------------+---------------+-----------+
| 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 | 5s            |         1 |
+-------+----------+----------------+----------+-----------------------+-----------+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------+---------+-------------------+---------------+-----------+
1 row in set

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

Column name Description
zone The zone where the parameter exists.
svr_type The server type.
svr_ip The IP address of the server.
svr_port The port of the server.
name The name of the parameter.
data_type The data type of the parameter. Valid values: NUMBER, STRING, CAPACITY, and so on.
value The value of the parameter.
Note
You can modify the parameter value for a specified zone or server. Therefore, the value of the parameter may vary with zones and servers.
info The description of the parameter.
section The category of the parameter. Valid values:
  • SSTABLE: an SSTable-related parameter.
  • OBSERVER: an OBServer node-related parameter.
  • ROOT_SERVICE: a RootService-related parameter.
  • TENANT: a tenant-related parameter.
  • TRANS: a transaction-related parameter.
  • LOAD_BALANCE: a load balancing-related parameter.
  • DAILY_MERGE: a major compaction-related parameter.
  • CLOG: a clog-related parameter.
  • LOCATION_CACHE: a location cache-related parameter.
  • CACHE: a cache-related parameter.
  • RPC: an RPC-related parameter.
  • OBPROXY: an ODP-related parameter.
scope The applicable scope of the parameter. Valid values:
  • TENANT: indicates that the parameter is a tenant-level parameter.
  • CLUSTER: indicates that the parameter is a cluster-level parameter.
source The source of the current value. Valid values:
  • TENANT
  • CLUSTER
  • CMDLINE
  • OBADMIN
  • FILE
  • DEFAULT
edit_level The modification behavior of the parameter. Valid values:
  • READONLY: indicates that you cannot modify the parameter.
  • STATIC_EFFECTIVE: indicates that you can modify the parameter but the modification takes effect only after the OBServer node is restarted.
  • DYNAMIC_EFFECTIVE: indicates that you can modify the parameter and the modification takes effect in real time.
default_value The default value of the parameter.
isdefault Indicates whether the current value is the default value. Valid values:
  • 0: The current value is not the default value.
  • 1: The current value is the default value.

More information

For more information about parameters, see Overview of parameters and system variables.

Last
Next

OceanBaseOceanBase

OceanBase, A Highly Scalable Database for Transactional, Analytical, and AI Workloads.

Company

About OceanBaseContact UsPartner

Product

OceanBase CloudOceanBase EnterpriseQuick StartDownloadPricing

Resources

DocsBlogWhite PaperTrust Center

Follow us

LinkedInLinkedInDiscordDiscordTwitterTwitterYoutubeYoutubeForumForumGitHubGitHubStack OverflowStack Overflow

© OceanBase 2024. All rights reserved | Cloud Service Agreement | Privacy Policy | Legal | Security

Contact Us