Check cluster parameters
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: [1, 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 statement 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 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 result is as follows:
+-------+----------+----------------+----------+------------------+-----------+-------+---------------------------------------------------------------------------------------------------------------+----------+---------+---------+-------------------+
| zone | svr_type | svr_ip | svr_port | name | data_type | value | info | section | scope | source | edit_level |
+-------+----------+----------------+----------+------------------+-----------+-------+---------------------------------------------------------------------------------------------------------------+----------+---------+---------+-------------------+
| zone1 | observer | xxx.xxx.xxx.xxx | 2882 | enable_sql_audit | NULL | 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 |
+-------+----------+----------------+----------+------------------+-----------+-------+---------------------------------------------------------------------------------------------------------------+----------+---------+---------+-------------------+
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']
The statements are described as follows:
PARAM_OPTS: the additional qualifiers specified when you modify the parameter, such as specifying a zone or server.ALTER SYSTEM: The statement cannot specify both the zone and the server at the same time. 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
systenant by specifying a user tenant. For example, the statementALTER SYSTEM SET memory_limit='100G' TENANT='test_tenant'will return an error, becausememory_limitis a cluster-level parameter.
The following example shows how to 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.