Modify cluster parameters

You can modify cluster parameters to ensure that the behaviors of OceanBase Database meet your business requirements. In OceanBase Database, cluster parameters may take effect dynamically or upon a restart. You can set cluster parameters to control the load balancing, major compaction time, major compaction mode, resource allocation, and module switches of an OceanBase cluster.

For more information about parameters, see Parameters and system variables.

Procedure

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

   Note that you must specify the corresponding parameters in the following sample code based on your actual database configurations.

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

   For more information about how to connect to a database, see Overview of connection methods (MySQL-compatible mode) and Overview of connection methods (Oracle-compatible mode).

2. Execute the following statement to view cluster parameter values:

   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 view cluster parameters, see View cluster parameters.

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

   The syntax is:

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

   where:

   • expression specifies the value of the modified parameter.

   • SCOPE specifies the effective scope of the parameter. The default value is BOTH. Valid values are as follows:

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

     • BOTH: specifies to modify the parameter value in both the configuration table and the memory. The modification takes effect immediately and remains effective after an OBServer node is restarted.

   • TENANT is used in the sys tenant to specify the tenants whose tenant-level parameters are to be modified. Valid values are as follows:

     • all or all_user: all user tenants.

       Note

       In OceanBase Database V4.2.1 and later, TENANT = all_user and TENANT = all express the same semantics. If you want an operation to take effect on all user tenants, we recommend that you use TENANT = all_user. TENANT = all will be deprecated.

     • all_meta: all meta tenants.

     • tenant_name: the name of a user tenant.

   • SERVER specifies the server to be modified in the cluster. ZONE specifies the zone to be modified in the cluster. ALTER SYSTEM allows you to specify a zone or a server, but not both. You can specify only one zone or server at a time. If you do not specify a zone or a server when you modify a cluster-level parameter, the modification takes effect in the entire cluster.

   When you modify cluster parameters, you cannot set a cluster-level parameter from a user tenant or by specifying a user tenant in the sys tenant.

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

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

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

   Here are examples of modifying cluster-level parameters 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. Execute the SHOW PARAMETERS statement to verify whether the value of the parameter is successfully modified based on the actual situation (whether the OBServer node is restarted).

   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

• System variables of a tenant

For detailed information about each parameter, see Parameters and system variables.