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
NoteYou can specify the |
Cluster-level parameters and tenant-level parameters
NoteIn the |
| 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
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_namespecifies the name of the parameter to be modified.expressionspecifies 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.SCOPEspecifies the effective scope of the modification. The default value isBOTH. 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.
TENANTis used in thesystenant 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 = allandtenant = all_userhave the same semantics and specify to take effect on all user tenants. Theallsyntax 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.
SERVERspecifies the server for which the parameter is to be modified.ZONEindicates 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
systenant. You cannot set cluster-level parameters in a user tenant or perform this operation in thesystenant by specifying a user tenant. For example, theALTER SYSTEM SET memory_limit='100G' TENANT='test_tenant'statement will cause an error becausememory_limitis a cluster-level parameter. - You can modify tenant-level parameters directly in the current tenant or in the
systenant by specifying theTENANTkeyword. - The
ALTER SYSTEMstatement 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
scopeparameter in the execution results of theSHOW PARAMETERSstatement specifies whether a parameter is a cluster-level or tenant-level parameter. - If the value of
scopeisCLUSTER, the parameter is a cluster-level parameter. - If the value of
scopeisTENANT, 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_namespecifies the name of the parameter to be modified.expressionspecifies 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
systenant by specifying theTENANTkeyword.
Example: Modify the log_disk_utilization_threshold parameter.
obclient> ALTER SYSTEM SET log_disk_utilization_threshold = 20;
Reset parameters
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:
RESETspecifies the keyword used to clear the current value of the parameter and use its default value or start value.SCOPEspecifies the effective scope of the modification. The default value isBOTH. 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.
TENANTspecifies to reset the tenant-level parameters of all tenants or a specified tenant from thesystenant.tenant_namespecifies 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
systenant. You can reset cluster-level parameters only in thesystenant.For example, if you attempt to execute theALTER SYSTEM RESET memory_limit TENANT='test_tenant'statement, an error will be returned becausememory_limitis a cluster-level parameter. - You can modify tenant-level parameters directly in the current tenant or in the
systenant by specifying theTENANTkeyword. - You cannot specify a zone or OBServer node when you execute the
ALTER SYSTEM RESETstatement. - The value of the
scopeparameter in the execution results of theSHOW PARAMETERSstatement specifies whether a parameter is a cluster-level or tenant-level parameter. - If the value of
scopeisCLUSTER, the parameter is a cluster-level parameter. - If the value of
scopeisTENANT, 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:
RESETspecifies the keyword used to clear the current value of the parameter and use its default value or start value.parameter_namespecifies the name of the parameter to be reset.expressionspecifies 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
systenant by specifying theTENANTkeyword.
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
systenant, 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 theTENANTkeyword. - In a user tenant, you can query the tenant-level parameters of the current tenant and cluster-level parameters of the
systenant. - A column attribute specified in the
WHERE exprclause must be a column attribute in the execution results of theSHOW PARAMETERSstatement.
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:
|
| scope | The applicable scope of the parameter. Valid values:
|
| source | The source of the current value. Valid values:
|
| edit_level | The modification behavior of the parameter. Valid values:
|
| default_value | The default value of the parameter. |
| isdefault | Indicates whether the current value is the default value. Valid values:
|
More information
For more information about parameters, see Overview.