Modify cluster-level parameters

You can modify cluster-level parameters by using SQL statements or in the OceanBase Cloud Platform (OCP) console.

Modify cluster-level parameters 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'];

Note:

• 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.

  • 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.

For 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='192.168.100.1:2882';

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

Modify cluster-level parameters in the OCP console

1. Log on to the OCP console.

   The Clusters page automatically appears.

2. In the Clusters list, find the target cluster and click its name.

3. In the left-side navigation pane of the page that appears, click Parameter Management.

4. (Optional) In the search box of the Parameters page, enter a parameter name to perform a fuzzy search.

5. Find the parameter to be modified and click Change Value in the Actions column.

6. In the dialog box that appears, modify the value and effective scope of the parameter, and then click OK.

   Cluster-level parameters of OceanBase Database have three effective scopes: Cluster, Zone, and OBServer. You can select the scope as needed when you modify the values.

   • If you set the effective scope to Zone, you must specify the zones in which the parameter takes effect.

   • If you set the effective scope to OBServer, you must specify the OBServer nodes on which the parameter takes effect.

   You can apply different parameter values in different scopes. For example, you can set the value of backup_concurrency to 10 in Zone 1, 12 in Zone 2, and 20 on OBServer 1. To do so, you can click Add Value, and select the corresponding effective scope in each row that appears.

   Note

   If multiple rows of values are displayed after you click Add Value, modify the parameter values from the top down. For each successful modification operation, a record is generated on the Change History tab.