Check cluster parameters|V4.3.1| docs|Distributed Database

Check cluster parameters

Last Updated：2024-06-28 05:30:29 Updated

This topic describes how to view and modify cluster parameters by using SQL statements.

View parameters

Cluster parameters

Parameter	Description	Recommended value
memory_limit_percentage	The percentage of the total available memory to the total memory of the system. Value range: [10, 90].	80
server_permanent_offline_time	The timeout period for a server. If no heartbeat message is sent from a server within the period, the server is considered permanently offline. Data replicas on a permanently offline server need to be automatically supplemented. Value range: [20s, +∞).	7200s
clog_sync_time_warn_threshold	The warning threshold of the transaction log synchronization duration. If the synchronization duration exceeds this threshold, a WARN log is generated. Value range: [1 ms, 10000 ms].	1s
large_query_threshold	The threshold of the query execution duration. A request may be suspended if its execution time exceeds this threshold. A suspended request is considered a large query and is processed by following the large query scheduling strategy. Value range: [1 ms, +∞).	5s
trace_log_slow_query_watermark	The threshold of the query execution duration. A query whose execution duration exceeds this threshold is considered a slow query. Trace logs of slow queries are written to system logs. Value range: [1 ms, +∞).	1s
autoinc_cache_refresh_interval	The refresh interval for the auto-increment column cache. Value range: [100 ms, + ∞).	4200s
default_compress_func	The default algorithm for compressing table data. You can also specify another compression algorithm when you create a table. Valid values: none, lz4-1.0, snappy-1.0, zlib-1.0, zstd-1.0, and zstd-1.3.8.	zstd_1.0
syslog_io_bandwidth_limit	The maximum I/O bandwidth available for system logs. If this value is reached, the remaining system logs are discarded.	5 MB
memstore_limit_percentage	The percentage of the memory occupied by the MemStore to the total available memory of a tenant. Value range: [1, 99].	50
system_memory	The memory size reserved by the system for the `sys500` tenant. Value range: [0 MB, +∞).	50 GB
resource_hard_limit	Specifies how resource units are allocated. During the allocation of resources such as CPU cores and memory, the total resource volume is the actual volume multiplied by the specified value in percentage. The proportion of the final allocated server resource volume cannot exceed the value of `resource_hard_limit`. Value range: [100, 10000].	100
syslog_level	The level of system logs. Valid values: DEBUG, TRACE, INFO, WARN, USER-ERR, and ERROR.	INFO
data_disk_usage_limit_percentage	The maximum usage of the data disk. When the usage exceeds this threshold, data can no longer be migrated into the data disk. Value range: [50, 100].	95
enable_perf_event	Specifies whether to enable the information collection feature for performance events. Valid values: True and False.	True
enable_sql_audit	Specifies whether to enable SQL audit. Valid values: True and False.	True

Query the values of parameters by using views

You can query the values of cluster parameters by using the GV$OB_PARAMETERS view under the sys tenant. The SQL syntax is as follows:

SELECT SVR_IP,ZONE,NAME,VALUE FROM OCEANBASE.GV$OB_PARAMETERS WHERE NAME IN ("memory_limit_percentage", "server_permanent_offline_time", "clog_sync_time_warn_threshold", "large_query_threshold", "trace_log_slow_query_watermark", "autoinc_cache_refresh_interval", "default_compress_func", "syslog_io_bandwidth_limit", "memstore_limit_percentage", "system_memory", "resource_hard_limit", "syslog_level", "data_disk_usage_limit_percentage", "enable_perf_event", "enable_sql_audit");

For more information about GV$OB_PARAMETERS, see GV$OB_PARAMETERS.

Here is an example:

obclient [(none)]> SELECT SVR_IP,ZONE,NAME,VALUE FROM OCEANBASE.GV$OB_PARAMETERS WHERE NAME IN ("memory_limit_percentage", "server_permanent_offline_time", "clog_sync_time_warn_threshold", "large_query_threshold", "trace_log_slow_query_watermark", "autoinc_cache_refresh_interval", "default_compress_func", "syslog_io_bandwidth_limit", "memstore_limit_percentage", "system_memory", "resource_hard_limit", "syslog_level", "data_disk_usage_limit_percentage", "enable_perf_event", "enable_sql_audit");

The return result is as follows:

+-----------------+-------+----------------------------------+------------+
| SVR_IP          | ZONE  | NAME                             | VALUE      |
+-----------------+-------+----------------------------------+------------+
| xxx.xxx.xxx.xxx | zone1 | data_disk_usage_limit_percentage | 90         |
| xxx.xxx.xxx.xxx | zone1 | clog_sync_time_warn_threshold    | 100ms      |
| xxx.xxx.xxx.xxx | zone1 | resource_hard_limit              | 100        |
| xxx.xxx.xxx.xxx | zone1 | server_permanent_offline_time    | 3600s      |
| xxx.xxx.xxx.xxx | zone1 | memstore_limit_percentage        | 50         |
| xxx.xxx.xxx.xxx | zone1 | large_query_threshold            | 5s         |
| xxx.xxx.xxx.xxx | zone1 | autoinc_cache_refresh_interval   | 3600s      |
| xxx.xxx.xxx.xxx | zone1 | memory_limit_percentage          | 80         |
| xxx.xxx.xxx.xxx | zone1 | syslog_io_bandwidth_limit        | 30MB       |
| xxx.xxx.xxx.xxx | zone1 | syslog_level                     | WDIAG      |
| xxx.xxx.xxx.xxx | zone1 | default_compress_func            | zstd_1.3.8 |
| xxx.xxx.xxx.xxx | zone1 | enable_perf_event                | True       |
| xxx.xxx.xxx.xxx | zone1 | enable_sql_audit                 | True       |
| xxx.xxx.xxx.xxx | zone1 | trace_log_slow_query_watermark   | 1s         |
| xxx.xxx.xxx.xxx | zone1 | system_memory                    | 30G        |
+-----------------+-------+----------------------------------+------------+
15 rows in set

Query the values of parameters by using the SHOW statement

You can execute the following SQL statement under the sys tenant to view the values of cluster parameters:

SHOW PARAMETERS LIKE '$parameters_name';

Here, $parameters_name is the name of the cluster parameter.

Here is an example:

obclient [(none)]> SHOW PARAMETERS LIKE 'enable_sql_audit';

The return 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 | enable_sql_audit | BOOL      | True  | specifies whether SQL audit is turned on. The default value is TRUE. Value: TRUE: turned on FALSE: turned off | OBSERVER | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE | true          |         1 |
+-------+----------+----------------+----------+------------------+-----------+-------+---------------------------------------------------------------------------------------------------------------+----------+---------+---------+-------------------+---------------+-----------+
1 row in set

Modify cluster parameters

You can use the following SQL statement to modify the values of cluster parameters.

The following example shows the syntax for modifying a parameter. If you want to modify two or more parameters at the same time, separate them with commas (,).

ALTER SYSTEM SET param_name = expr
  [COMMENT 'text']
  [PARAM_OPTS]
  [TENANT = 'tenantname'];    

PARAM_OPTS:
  [ZONE='zone' | SERVER='server_ip:rpc_port']

Take note of the following points when you use this statement:

You can use PARAM_OPTS to specify additional qualifiers, such as the zone or server, when you modify the parameters.
You cannot specify both the zone and server in the ALTER SYSTEM statement. When you specify a zone, only one zone can be specified. When you specify a server, only one server can be specified.
You cannot set cluster-level parameters in a user tenant or in the sys tenant by specifying a user tenant. For example, the statement ALTER SYSTEM SET memory_limit='100G' TENANT='test_tenant' will return an error, because memory_limit is a cluster-level parameter.

Here is an example:

Change the value of server_permanent_offline_time to 7200s:

obclient [(none)]> ALTER SYSTEM SET server_permanent_offline_time = '7200s';

References

If you are using OceanBase Cloud Platform (OCP) to manage your OceanBase cluster, you can also perform unified parameter management through OCP, including viewing parameters, modifying parameters, and viewing parameter modification history. For more information, see View parameters.
For more information about cluster parameters, see Parameter summary.