Modify cluster parameters

You can modify cluster parameters to ensure that the behavior of OceanBase Database meets your business requirements. Cluster parameters of OceanBase Database are also called cluster parameters. These parameters are divided into two categories: take effect immediately and take effect after a restart. You can set cluster parameters to control the load balancing, major compaction time, major compaction mode, resource allocation, and module switches of a cluster.

For more information about parameters, see Overview.

Procedure

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

   Here is an example of how to connect to a database. Modify the connection string based on your actual database settings.

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

   For more information about how to connect to a database, see Overview (MySQL mode) and Overview (Oracle mode).

2. Execute the following statement to query the value of a cluster parameter.

   Here is an 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 query cluster parameters, see Query cluster parameters.

3. Execute the following statement to modify the value of a cluster parameter.

   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 parameters in the statement are described as follows:

   • expression specifies the new value of the parameter.

   • SCOPE specifies the scope in which the parameter modification takes effect. Default value: BOTH. Where:

     • SPFILE: specifies to modify only the parameter values in the configuration table. The modification takes effect only after the OBServer node is restarted.

     • BOTH: specifies to modify the parameter values in the configuration table and in memory. The modification takes effect immediately and the parameter values still take effect after the OBServer node is restarted.

   • TENANT: specifies the tenant-level parameters to be modified for the sys tenant or a user tenant. Valid values:

     • all and all_user: all user tenants.

       Note

       Starting from V4.2.1, OceanBase Database allows you to use TENANT = all_user and TENANT = all interchangeably to specify the scope of effectiveness for all user tenants. We recommend that you use TENANT = all_user when you want the modification to take effect for all user tenants. TENANT = all will be deprecated.

     • all_meta: all meta tenants.

     • tenant_name: the name of the specified tenant.

   • SERVER specifies the OBServer node to modify in the specified cluster. ZONE specifies the zone to modify in the specified cluster. You cannot specify both ZONE and SERVER in an ALTER SYSTEM statement. When you specify ZONE, you can specify only one zone. When you specify SERVER, you can specify only one server. If you modify a cluster-level parameter without specifying a zone or server, the modification takes effect in the entire cluster.

   You cannot modify a cluster-level parameter as a user tenant parameter or a user tenant parameter as a cluster-level parameter.

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

   • If the value of the scope column is CLUSTER, the parameter is a cluster-level parameter.

   • If the value of the scope column is TENANT, the parameter is a user tenant-level parameter.

   The following examples show how to modify cluster-level parameters as the sys tenant user:

   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 you modify a cluster parameter, you can execute the SHOW PARAMETERS statement to check whether the modification takes effect, depending on whether you want to restart the OBServer node.

   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.