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 can be dynamically effective or effective only 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 a system tenant user or a user tenant user.

   Here is an example of establishing a connection. You must replace the connection information in the example with the actual information of your environment.

   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        |
   +-------+----------+----------------+----------+--------------+-----------+-------+------------------------------------------------------------------------------------------------------------------------------+----------+---------+---------+-------------------+
   | 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 |
   +-------+----------+----------------+----------+--------------+-----------+-------+------------------------------------------------------------------------------------------------------------------------------+----------+---------+---------+-------------------+
   1 row in set
   

   For more information about how to query cluster parameters, see View 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. The default value is BOTH. Where:

     • SPFILE: indicates that the parameter value is modified only in the configuration table and takes effect only after the OBServer node is restarted.

     • BOTH: indicates that the parameter value is modified in both the configuration table and the memory, and the modification takes effect immediately. The modification also takes effect after the OBServer node is restarted.

   • TENANT: specifies the tenant scope for modifying the tenant-level parameters in the system tenant. Valid values: all, all_user, all_meta, and tenant_name.

     • 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 all user tenants scope. We recommend that you use TENANT = all_user when you want the modification to take effect in 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, and ZONE specifies the zone to modify in the specified cluster. You cannot specify both ZONE and SERVER in the same ALTER SYSTEM statement. Moreover, you can specify only one zone when you specify ZONE, and you can specify only one OBServer node when you specify SERVER. If you modify a cluster-level parameter and do not specify a zone or an OBServer node, the modification takes effect in the entire cluster.

   You cannot specify a cluster-level parameter for a user tenant or specify the sys tenant to set a cluster-level parameter for a user tenant.

   For example, the execution of 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 cluster-level or tenant-level based on the value of the scope column in the execution 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 tenant-level parameter.

   The following example shows how to modify a cluster-level parameter in the sys tenant.

   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, you can execute the SHOW PARAMETERS statement to check whether the parameter value is modified successfully, 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        |
   +-------+----------+----------------+----------+--------------+-----------+-------+------------------------------------------------------------------------------------------------------------------------------+----------+---------+---------+-------------------+
   | 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 |
   +-------+----------+----------------+----------+--------------+-----------+-------+------------------------------------------------------------------------------------------------------------------------------+----------+---------+---------+-------------------+
   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.