Modify cluster parameters

By modifying cluster parameters, you can make OceanBase Database behave according to your business requirements. Cluster parameters in OceanBase Database are also known as cluster parameters. These parameters are divided into two categories: dynamic and restart-effective. By setting cluster parameters, you can control functions such as load balancing, major compaction time, major compaction method, resource allocation, and module switches for the cluster.

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

Procedure

1. Log in to the cluster as the sys tenant or a user tenant.

   The following is an example connection string. Please use the actual environment settings when connecting to the database.

   obclient -h10.xx.xx.xx -P2883 -utest@mysql#obdemo -p***** -A
   

   For more detailed instructions on connecting to a database, see Overview of database connections (MySQL-compatible mode) and Overview of database connections (Oracle-compatible mode).

2. Run the following command to view the values of cluster parameters.

   Example:

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

   The query 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 | syslog_level | STRING    | WDIAG | specifies the current level of logging. There are DEBUG, TRACE, WDIAG, EDIAG, INFO, WARN, ERROR, seven different log levels. | OBSERVER | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE | WDIAG         |         1 |
   +-------+----------+----------------+----------+--------------+-----------+-------+------------------------------------------------------------------------------------------------------------------------------+----------+---------+---------+-------------------+---------------+-----------+
   1 row in set
   

   For more information about how to view cluster parameters, see View cluster parameters.

3. Run the following command to modify the values of cluster parameters.

   The statement is as follows:

   ALTER SYSTEM [SET] parameter_name = expression 
       [SCOPE = {SPFILE | BOTH}]
       [SERVER [=] 'svr_ip:svr_port' | ZONE [=] 'zone' | TENANT [=] {all | all_user | all_meta | tenant_name}];
   

   The relevant parameters are described as follows:

   • expression specifies the value of the parameter after modification.

   • SCOPE specifies the scope of effect for this parameter modification. The default value is BOTH. Valid values are:

     • SPFILE: Indicates that only the parameter value in the configuration file is modified. The change takes effect after the OBServer node restarts.

     • BOTH: Indicates that both the configuration file and the memory value are modified. The change takes effect immediately and remains valid after the OBServer node restarts.

   • TENANT: Used by the system tenant to modify tenant-level parameters for all user tenants, all Meta tenants, or a specified tenant.

     • all and all_user: All user tenants.

       Note

       Starting from OceanBase Database V4.2.1, TENANT = all_user and TENANT = all have the same semantics. When you need the change to apply to all user tenants, it is recommended to use TENANT = all_user. The latter TENANT = all will be deprecated in future versions.

     • all_meta: All Meta tenants.

     • tenant_name: The name of the specified tenant.

   • SERVER specifies the OBServer node to be modified within the cluster, and ZONE specifies the zone to be modified within the cluster. The ALTER SYSTEM statement does not support specifying both a zone and a server. When specifying a zone, only one zone is supported; when specifying a server, only one server is supported. If you do not specify a zone or a server when modifying cluster-level parameters, the modification takes effect across the entire cluster.

   When modifying cluster parameters, cluster-level parameters cannot be set via a user tenant, nor can they be specified as user tenant settings through the system tenant (i.e., the sys tenant).

   For example, executing the statement ALTER SYSTEM SET memory_limit='100G' TENANT='test_tenant' will result in an error because memory_limit is a cluster-level parameter. To determine whether a parameter is cluster-level or tenant-level, check the value in the scope column of the execution result of the SHOW PARAMETERS LIKE 'parameter_name'; statement:

   • A scope value of CLUSTER indicates a cluster-level parameter.

   • A scope value of TENANT indicates a tenant-level parameter.

   The following examples show how the system tenant modifies cluster-level parameters:

   obclient [(none)]> ALTER SYSTEM SET syslog_level='ERROR' SCOPE = SPFILE;
   

   obclient [(none)]> ALTER SYSTEM SET log_disk_utilization_threshold = 20 TENANT = all_user;
   

   obclient [(none)]> ALTER SYSTEM SET system_memory = '50G' ZONE = 'zone1';
   

   obclient [(none)]> ALTER SYSTEM SET syslog_io_bandwidth_limit = '5M';
   

   obclient [(none)]> ALTER SYSTEM SET memory_limit = '250G' SERVER='xx.xx.xx.238:2882';
   

4. After the modification is successful, you can verify whether the parameter value has been successfully changed by executing the SHOW PARAMETERS statement, depending on your actual situation (whether to restart the OBServer or not).

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

   The query 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 | syslog_level | STRING    | ERROR | specifies the current level of logging. There are DEBUG, TRACE, WDIAG, EDIAG, INFO, WARN, ERROR, seven different log levels. | OBSERVER | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE | WDIAG         |         0 |
   +-------+----------+----------------+----------+--------------+-----------+-------+------------------------------------------------------------------------------------------------------------------------------+----------+---------+---------+-------------------+---------------+-----------+
   1 row in set
   

References

For more information about cluster parameters, see the following topics:

• Cluster parameters

• View cluster parameters

• Reset cluster parameters

• Tenant system variables

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