Modify cluster-level parameters

You can modify cluster-level parameters by using SQL statements.

Modify a cluster-level parameter by using an SQL statement

The following example shows the syntax for modifying a cluster-level parameter. If you want to modify multiple parameters at a time, separate them with commas (,).

 ALTER SYSTEM [SET]
       parameter_name = expression [SCOPE = {MEMORY | SPFILE | BOTH}]
          [COMMENT [=] 'text']
            [SERVER [=] 'ip:port' | ZONE [=] 'zone'];

Notes:

• Cluster-level parameters can be modified only in the sys tenant.

• expression: specifies the value of the modified parameter.

• SCOPE: specifies the effective scope of the parameter. Default value: BOTH. Parameters:

  • MEMORY: indicates that only parameters in the memory can be modified, and the modification takes effect immediately. The modification becomes invalid after the server is restarted. However, no parameter supports this modification.

  • SPFILE: indicates that only parameters in the configuration table can be modified. The modification takes effect after the server is restarted.

  • BOTH: indicates that parameters can be modified in both the configuration table and the memory. The modification takes effect immediately and remains effective after the server is restarted.

• 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 either a zone or server, 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.

• You cannot set cluster-level parameters in a user tenant or in the sys tenant by specifying a user tenant. For example, ALTER SYSTEM SET memory_limit='100G' TENANT='test_tenant' will cause an error because memory_limit is a cluster-level parameter.

  The value of the scope parameter in the execution results of the SHOW PARAMETERS LIKE 'parameter_name'; statement specifies whether a parameter is a cluster-level parameter or 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.

Example:

obclient> ALTER SYSTEM SET net_thread_count=1 SCOPE = SPFILE;

obclient> ALTER SYSTEM SET mysql_port=8888;

obclient> ALTER SYSTEM SET mysql_port=8888 ZONE='z1';

obclient> ALTER SYSTEM SET mysql_port=8888 SERVER='xx.xx.xx.xx:2882';

For more information about cluster-level parameters in OceanBase Database, see System parameters.

Modify cluster-level parameters by using OBD

1. After you log on to the server where OBD is installed, run the following command to open the configuration file of the cluster:

   obd cluster edit-config <deploy_name>
   

2. Modify or add parameters in the configuration file.

   For more information about the meaning of each module in the configuration file, see Configuration file.

   If the parameter to be modified does not exist in the configuration file, you can manually add it to the global or node configurations based on the effective scope.

3. Save the configuration file and restart the cluster.

   After you save and close the configuration file, OBD will prompt you to execute the obd cluster reload <deploy_name> or obd cluster restart <deploy_name> --wp command.