View and modify parameters|V4.3.2| docs|Distributed Database

This topic describes how to view and modify parameters in OceanBase Database Proxy (ODP).

Classification of parameters

You can configure ODP parameters as needed when you use ODP. Currently, ODP parameters are classified into two types: global parameters and multi-level parameters.

Global parameters

Global parameters take effect globally for ODP and have default values. In general usage scenarios, they do not need to be manually set. Currently, they cannot be deleted. For more information about these parameters, see Global parameters.
Multi-level parameters

Multi-level parameters can be set with a specific effective level, which can be LEVEL_VIP (address level), LEVEL_TENANT (tenant level), LEVEL_CLUSTER (cluster level), or LEVEL_GLOBAL (global level), in a priority order from high to low. For more information about these parameters, see Multi-level parameters.

Note

For parameters of the time and capacity types, if their value range is [0,xxx], negative values are supported for these parameters during configuration update. After you set a parameter to a negative value, the value queried from the ob_all_proxy_config table or by using the internal query command show proxyconfig in ODP is the negative value. However, the value that actually takes effect in ODP is still 0. For parameters of the time type, supported units are us, ms, s, m, h, and d (case insensitive), and the default unit is s. For parameters of the capacity type, supported units are b, byte, kb, k, mb, m, gb, g, tb, t, pb, and p (case insensitive), and the default unit is mb.
Parameters usually take effect dynamically or upon a restart. Most parameters take effect dynamically without a restart of ODP.

View parameters

You can perform the following steps to view details of ODP parameters:

Log on to OceanBase Database through ODP as the root@sys user or log on to ODP as the root@proxysys user.

Here is an example of logging on to OceanBase Database through ODP as the root@sys user. For more information about parameters in the following command, see Connect to an OceanBase Database tenant by using OBClient.
```
[admin@obtest ~]$ obclient -uroot@sys#obcluster -h10.10.10.1 -P2883 -p
```
Notice

To use ODP commands in OceanBase Database, the IP address (-h) and the port (-P) for connecting to OceanBase Database must be the IP address and listening port (listen_port) of ODP.

Here is an example of logging on to ODP as the root@proxysys user. The IP address (-h) is the IP address of ODP, and the port (-P) is the listening port (listen_port) of ODP.
```
[admin@obtest ~]$ obclient -uroot@proxysys -h10.10.10.1 -P2883 -p
```
Run the show proxyconfig [like '%var_name%'] command to view the value and minimum effective level of the specified parameters.

Note

This command returns only the parameter value at the global level, and does not return values at other effective levels.
```
show proxyconfig like '%full%'\G
```
The output is as follows, where the config_level value of the first parameter is LEVEL_GLOBAL, indicating that the parameter is a global parameter, and its value takes effect on the entire ODP. The config_level value of the second parameter is LEVEL_VIP, indicating that the parameter is a multi-level parameter and can be configured with different effective levels.
```
*************************** 1. row ***************************
         name: enable_full_username
        value: False
         info: used for non-cloud user, if set true, username must have tenant and cluster
  need_reboot: false
visible_level: SYS
        range:
 config_level: LEVEL_GLOBAL
*************************** 2. row ***************************
         name: enable_cloud_full_username
        value: False
         info: used for cloud user, if set false, treat all login user as username
  need_reboot: false
visible_level: SYS
        range:
 config_level: LEVEL_VIP
```
The fields in the query result are described as follows:
- name: the name of the parameter.
- value: the value of the parameter.
- info: the description of the parameter.
- need_reboot: indicates whether a restart is required for the modifications of the parameter to take effect.
- visible_level: the visibility level of the parameter. The value SYS indicates that the parameter is visible only to the administrator but not to users. The value USER indicates that the parameter is visible to users.
- range: the value range of the parameter, which is displayed only if the parameter is of the time or integer type.
- config_level: the minimum effective level of the parameter. Valid values are LEVEL_GLOBAL and LEVEL_VIP.
  - The value LEVEL_GLOBAL indicates that the parameter takes effect at the global level.
  - The value LEVEL_VIP indicates that the parameter takes effect at multiple levels.
Run the show proxyconfig all [like '%xxx%'] command to view the value and current effective level of the specified parameters.
```
show proxyconfig all like '%root%';
```
The output is as follows, where the value column lists the parameter values, and the config_level column lists the effective levels of the parameters.
```
+------+-----------+-------+---------------+----------------+--------------------------+---------------------+--------------+
| vid  | vip       | vport | tenant_name   | cluster_name   | name                     | value               | config_level |
+------+-----------+-------+---------------+----------------+--------------------------+---------------------+--------------+
| 1    | 127.0.0.3 | 1     | test_tename_1 | test_cluster_1 | rootservice_cluster_name | value_3             | LEVEL_VIP    |
| -1   |           | 0     |               |                | rootservice_cluster_name | undefined           | LEVEL_GLOBAL |
| -1   |           | 0     |               |                | rootservice_list         | 10.10.10.1:2881     | LEVEL_GLOBAL |
+------+-----------+-------+---------------+----------------+--------------------------+---------------------+--------------+
```
The fields in the query result are described as follows:
- vid: the field associated with the virtual IP address. This field is generally controlled by the O&M team. You can ignore it.
- vip: the field associated with the virtual IP address. This field is generally controlled by the O&M team. You can ignore it.
- vport: the field associated with the virtual IP address. This field is generally controlled by the O&M team. You can ignore it.
- tenant_name: the name of the tenant on which the parameter takes effect.
- cluster_name: the name of the cluster on which the parameter takes effect.
- name: the name of the parameter.
- value: the value of the parameter.
- config_level: the effective level of the parameter. Valid values are LEVEL_GLOBAL, LEVEL_CLUSTER, LEVEL_TENANT, and LEVEL_VIP.
  - The value LEVEL_GLOBAL indicates that the parameter takes effect at the global level.
  - The value LEVEL_CLUSTER indicates that the parameter takes effect at the cluster level.
  - The value LEVEL_TENANT indicates that the parameter takes effect at the tenant level.
  - The value LEVEL_VIP indicates that the parameter takes effect at the address level.
Execute the SQL statement select * from proxy_config to view the values of the specified parameters at different effective levels.

Note

You can use this statement only when you log on to ODP as the root@proxysys user. Otherwise, an error is returned.

This statement returns a more detailed result than show proxyconfig all [like '%xxx%'].
```
select * from proxy_config where name='metadb_batch_size'\G
```
The output is as follows:
```
*************************** 1. row ***************************
          vid: -1
          vip:
        vport: 0
  tenant_name:
 cluster_name:
         name: metadb_batch_size
        value: 5
         info:
        range:
  need_reboot:
visible_level:
 config_level: LEVEL_GLOBAL
```
The fields in the query result are described as follows:
- vid: the field associated with the virtual IP address. This field is generally controlled by the O&M team. You can ignore it.
- vip: the field associated with the virtual IP address. This field is generally controlled by the O&M team. You can ignore it.
- vport: the field associated with the virtual IP address. This field is generally controlled by the O&M team. You can ignore it.
- tenant_name: the name of the tenant on which the parameter takes effect.
- cluster_name: the name of the cluster on which the parameter takes effect.
- name: the name of the parameter.
- value: the value of the parameter.
- info: the description of the parameter.
- range: the value range of the parameter, which is displayed only if the parameter is of the time or integer type.
- need_reboot: indicates whether a restart is required for the modifications of the parameter to take effect.
- visible_level: the visibility level of the parameter. The value SYS indicates that the parameter is visible only to the administrator but not to users. The value USER indicates that the parameter is visible to users.
- config_level: the effective level of the parameter. Valid values are LEVEL_GLOBAL, LEVEL_CLUSTER, LEVEL_TENANT, and LEVEL_VIP.
  - The value LEVEL_GLOBAL indicates that the parameter takes effect at the global level.
  - The value LEVEL_CLUSTER indicates that the parameter takes effect at the cluster level.
  - The value LEVEL_TENANT indicates that the parameter takes effect at the tenant level.
  - The value LEVEL_VIP indicates that the parameter takes effect at the address level.
Run the show proxyconfig diff [like '%var_name%'] command to view the globally effective parameters whose current values differ from their default values.
```
show proxyconfig diff like '%enable%'\G
```
The output is as follows:
```
*************************** 1. row ***************************
         name: enable_strict_kernel_release
        value: False
         info: If is true, proxy only support 5u/6u/7u redhat. Otherwise no care kernel release, and proxy maybe unstable
  need_reboot: true
visible_level: SYS
        range:
 config_level: LEVEL_GLOBAL
*************************** 2. row ***************************
         name: enable_cluster_checkout
        value: False
         info: if enable cluster checkout, proxy will send cluster name when login and server will check it
  need_reboot: false
visible_level: USER
        range:
 config_level: LEVEL_GLOBAL
```
The fields in the query result are described as follows:
- name: the name of the parameter.
- value: the value of the parameter.
- info: the description of the parameter.
- need_reboot: indicates whether a restart is required for the modifications of the parameter to take effect.
- visible_level: the visibility level of the parameter. The value SYS indicates that the parameter is visible only to the administrator but not to users. The value USER indicates that the parameter is visible to users.
- range: the value range of the parameter, which is displayed only if the parameter is of the time or integer type.
- config_level: the minimum effective level of the parameter. Valid values are LEVEL_GLOBAL and LEVEL_VIP.
  - The value LEVEL_GLOBAL indicates that the parameter takes effect at the global level.
  - The value LEVEL_VIP indicates that the parameter takes effect at multiple levels.
Run the show proxyconfig diff user [like '%var_name%'] command to view the globally effective parameters whose current values differ from their default values. This command returns only parameters that are visible to users.
```
show proxyconfig diff user\G
```
The output is as follows, where the values of visible_level are all USER.
```
*************************** 1. row ***************************
         name: work_thread_num
        value: 8
         info: proxy work thread num or max work thread num when automatic match, [1, 128]
  need_reboot: true
visible_level: USER
        range: [1,128]
 config_level: LEVEL_GLOBAL
*************************** 2. row ***************************
         name: enable_cluster_checkout
        value: False
         info: if enable cluster checkout, proxy will send cluster name when login and server will check it
  need_reboot: false
visible_level: USER
        range:
 config_level: LEVEL_GLOBAL
*************************** 3. row ***************************
         name: syslog_level
        value: debug
         info: specifies the current level of logging: DEBUG, TRACE, WDIAG, EDIAG, INFO, WARN, USER_ERR, ERROR
  need_reboot: false
visible_level: USER
        range:
 config_level: LEVEL_GLOBAL
```
The fields in the query result are described as follows:
- name: the name of the parameter.
- value: the value of the parameter.
- info: the description of the parameter.
- need_reboot: indicates whether a restart is required for the modifications of the parameter to take effect.
- visible_level: the visibility level of the parameter. The value SYS indicates that the parameter is visible only to the administrator but not to users. The value USER indicates that the parameter is visible to users.
- range: the value range of the parameter, which is displayed only if the parameter is of the time or integer type.
- config_level: the minimum effective level of the parameter. Valid values are LEVEL_GLOBAL and LEVEL_VIP.
  - The value LEVEL_GLOBAL indicates that the parameter takes effect at the global level.
  - The value LEVEL_VIP indicates that the parameter takes effect at multiple levels.

Modify parameters

You can modify ODP parameters in the following ways:

Add the -o option in the startup command to modify a parameter when you start ODP.
```
-o <var_name> = <var_value>
```
If you use this method, the new value takes effect globally by default.
Log on to ODP as the root@proxysys user and run the alter proxyconfig command to modify a parameter.
```
alter proxyconfig set <var_name> = <var_value>
```
You can use this command to change the values of global parameters, but cannot change the values or effective levels of multi-level parameters.
Log on to ODP as the root@proxysys user and run the replace into command to add or modify a parameter.

Note the following requirements when you run the replace into command to modify a parameter:
- When you use replace into to modify a parameter, if the parameter has a value at the specified effective level in ODP, the replace into command modifies the existing value; if the parameter does not have a value at the specified effective level in ODP, replace into adds the parameter with a value at the specified effective level.
- You cannot configure other effective levels for a global parameter.
- Batch modification is supported. A maximum of 200 parameters are allowed in a batch, with the executed SQL statement no larger than 1 MB. In scenarios where a large number of parameters need to be modified, we recommend that you use batch modification for better performance.
- Note the following requirements when you modify a multi-level parameter:
  - If the parameter takes effect at a level other than the address level (LEVEL_VIP), you do not need to configure the vid, vip, or vport field. However, if the fields are involved in batch modification, you need to set vid to -1, vip to an empty string (''), and vport to 0.
  - If the parameter takes effect at the tenant level (LEVEL_TENANT), you must set tenant_name and cluster_name.
  - If the parameter takes effect at the cluster level (LEVEL_CLUSTER), you must set cluster_name and leave tenant_name empty.
  - If the parameter takes effect at the global level (LEVEL_CLOBAL), you must leave both tenant_name and cluster_name empty.

Examples

Change the value of the enable_cluster_checkout parameter to False when you start ODP.

[admin@test ~]$ ./bin/obproxy -p 2883 -r'10.10.10.1:2881;10.10.10.2:2881;10.10.10.3:2881' -o enable_cluster_checkout=False -n test -c mycluster

Run the following command to view the modification result:

show proxyconfig all like 'enable_cluster_checkout';

The output is as follows, where the value of enable_cluster_checkout is False and the effective level is LEVEL_GLOBAL.

+------+------+-------+-------------+--------------+-------------------------+-------+--------------+
| vid  | vip  | vport | tenant_name | cluster_name | name                    | value | config_level |
+------+------+-------+-------------+--------------+-------------------------+-------+--------------+
| -1   |      | 0     |             |              | enable_cluster_checkout | False | LEVEL_GLOBAL |
+------+------+-------+-------------+--------------+-------------------------+-------+--------------+

Configure a value that takes effect at the cluster level for the multi-level parameter read_stale_retry_interval.

obclient [(none)]> replace into proxy_config(cluster_name, name, value, config_level) values ('cluster_1', 'read_stale_retry_interval', '6s', 'LEVEL_CLUSTER');

Run the following command to view the modification result:

show proxyconfig all like 'read_stale_retry_interval';

The output is as follows, showing that the value at the LEVEL_CLUSTER level is added.

+------+------+-------+-------------+--------------+---------------------------+-------+---------------+
| vid  | vip  | vport | tenant_name | cluster_name | name                      | value | config_level  |
+------+------+-------+-------------+--------------+---------------------------+-------+---------------+
| -1   |      | 0     |             |              | read_stale_retry_interval | 5s    | LEVEL_GLOBAL  |
| -1   |      | 0     |             | cluster_1    | read_stale_retry_interval | 6s    | LEVEL_CLUSTER |
+------+------+-------+-------------+--------------+---------------------------+-------+---------------+

Configure values that take effect at different levels for the multi-level parameter rootservice_cluster_name in batches.

obclient [(none)]> replace into proxy_config(vid, vip, vport, tenant_name, cluster_name, name, value, config_level) values
('1', '10.10.10.1', '2881','tenent_1','cluster_1', 'rootservice_cluster_name','value_1','LEVEL_VIP'),
('2', '10.10.10.1', '3881','tenent_1','cluster_1', 'rootservice_cluster_name','value_2','LEVEL_VIP'),
('-1','','0','tenant_2','cluster_2','rootservice_cluster_name','value_3','LEVEL_TENANT'),
('-1','','0','','cluster_3','rootservice_cluster_name','value_4','LEVEL_CLUSTER'),
('-1','','0','','cluster_4','rootservice_cluster_name','value_5','LEVEL_CLOBAL');

Run the following command to view the modification result:

show proxyconfig all like 'rootservice_cluster_name';

The output is as follows, showing that the replace into command has modified the value at the LEVEL_CLOBAL level and added values at the LEVEL_VIP, LEVEL_TENANT, and LEVEL_CLUSTER levels.

+------+------------+-------+-------------+----------------+--------------------------+-----------+---------------+
| vid  | vip        | vport | tenant_name | cluster_name   | name                     | value     | config_level  |
+------+------------+-------+-------------+----------------+--------------------------+-----------+---------------+
| -1   |            | 0     |             | cluster_3      | rootservice_cluster_name | value_4   | LEVEL_CLUSTER |
| -1   |            | 0     | tenant_2    | cluster_2      | rootservice_cluster_name | value_3   | LEVEL_TENANT  |
| 1    | 10.10.10.1 | 2881  | tenent_1    | cluster_1      | rootservice_cluster_name | value_1   | LEVEL_VIP     |
| 2    | 10.10.10.1 | 3881  | tenent_1    | cluster_1      | rootservice_cluster_name | value_2   | LEVEL_VIP     |
| -1   |            | 0     |             |                | rootservice_cluster_name | value_5   | LEVEL_CLOBAL  |
+------+------------+-------+-------------+----------------+--------------------------+-----------+---------------+

Change the value at the LEVEL_CLOBAL level for the multi-level parameter rootservice_cluster_name.

obclient [(none)]> alter proxyconfig set rootservice_cluster_name='obcluster';

Run the following command to view the modification result:

show proxyconfig all like 'rootservice_cluster_name';

The output is as follows, showing that the value at the LEVEL_CLOBAL level is changed from value_5 to obcluster.

+------+------------+-------+-------------+----------------+--------------------------+-------------+---------------+
| vid  | vip        | vport | tenant_name | cluster_name   | name                     | value       | config_level  |
+------+------------+-------+-------------+----------------+--------------------------+-------------+---------------+
| -1   |            | 0     |             | cluster_3      | rootservice_cluster_name | value_4     | LEVEL_CLUSTER |
| -1   |            | 0     | tenant_2    | cluster_2      | rootservice_cluster_name | value_3     | LEVEL_TENANT  |
| 1    | 10.10.10.1 | 2881  | tenent_1    | cluster_1      | rootservice_cluster_name | value_1     | LEVEL_VIP     |
| 2    | 10.10.10.1 | 3881  | tenent_1    | cluster_1      | rootservice_cluster_name | value_2     | LEVEL_VIP     |
| -1   |            | 0     |             |                | rootservice_cluster_name | obcluster   | LEVEL_CLOBAL  |
+------+------------+-------+-------------+----------------+--------------------------+-------------+---------------+

View and modify parameters

Classification of parameters

Note

View parameters

Notice

Note

Note

Modify parameters

Examples