Modify cluster parameters

You can modify cluster parameters to ensure that the behaviors of OceanBase Database meet the service 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 on 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 Connection methods (MySQL mode) or Connection methods (Oracle mode).

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

   Here is an example:

   obclient [(none)]> SHOW PARAMETERS LIKE '%syslog_level%';
   +-------+----------+----------------+----------+--------------+-----------+-------+------------------------------------------------------------------------------------------------------------------------+----------+---------+---------+-------------------+
   | zone  | svr_type | svr_ip         | svr_port | name         | data_type | value | info                                                                                                                   | section  | scope   | source  | edit_level        |
   +-------+----------+----------------+----------+--------------+-----------+-------+------------------------------------------------------------------------------------------------------------------------+----------+---------+---------+-------------------+
   | zone2 | observer | 172.xx.xx.xx   |     2882 | syslog_level | NULL      | PERF  | specifies the current level of logging. There are DEBUG, TRACE, INFO, WARN, USER_ERR, ERROR, six different log levels. | OBSERVER | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE |
   | zone3 | observer | 172.xx.xx.xx   |     2882 | syslog_level | NULL      | PERF  | specifies the current level of logging. There are DEBUG, TRACE, INFO, WARN, USER_ERR, ERROR, six different log levels. | OBSERVER | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE |
   | zone1 | observer | 172.xx.xx.xx   |     2882 | syslog_level | NULL      | PERF  | specifies the current level of logging. There are DEBUG, TRACE, INFO, WARN, USER_ERR, ERROR, six different log levels. | OBSERVER | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE |
   +-------+----------+----------------+----------+--------------+-----------+-------+------------------------------------------------------------------------------------------------------------------------+----------+---------+---------+-------------------+
   3 rows in set
   

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

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

   The syntax is as follows:

   obclient [(none)]> ALTER SYSTEM [SET]
       parameter_name = expression [SCOPE = {SPFILE | BOTH}]
          [COMMENT [=] 'text']
            [ TENANT [=] all | all_user | all_meta | tenant_name ]
            [SERVER [=] 'svr_ip:svr_port' | ZONE [=] 'zone'];
   

   where

   • expression specifies the value of the modified parameter.

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

     • SPFILE: specifies only to modify the parameter value in the configuration table. The modification takes effect after the 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 the 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: all 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 is an example:

   obclient [(none)]> ALTER SYSTEM SET syslog_level='ERROR' SCOPE = SPFILE;
   
   obclient [(none)]> ALTER SYSTEM SET log_disk_utilization_threshold = 20 TENANT = all;
   
   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 'system_memory';
   +-------+----------+----------------+----------+---------------+-----------+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+---------+---------+-------------------+
   | zone  | svr_type | svr_ip         | svr_port | name          | data_type | value | info                                                                                                                                                                    | section  | scope   | source  | edit_level        |
   +-------+----------+----------------+----------+---------------+-----------+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+---------+---------+-------------------+
   | zone1 | observer | xx.xx.xx.237   |     2882 | system_memory | NULL      | 50G   | the memory reserved for internal use which cannot be allocated to any outer-tenant, and should be determined to guarantee every server functions normally. Range: [0M,) | OBSERVER | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE |
   | zone3 | observer | xx.xx.xx.218   |     2882 | system_memory | NULL      | 0M    | the memory reserved for internal use which cannot be allocated to any outer-tenant, and should be determined to guarantee every server functions normally. Range: [0M,) | OBSERVER | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE |
   | zone2 | observer | xx.xx.xx.238   |     2882 | system_memory | NULL      | 0M    | the memory reserved for internal use which cannot be allocated to any outer-tenant, and should be determined to guarantee every server functions normally. Range: [0M,) | OBSERVER | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE |
   +-------+----------+----------------+----------+---------------+-----------+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+---------+---------+-------------------+
   3 rows in set
   

References

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

• Cluster parameters

• View cluster parameters

• System variables of a tenant

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