Purpose
This statement is used to modify the source configuration (such as zone, idc, or region) of the backup or archive path, I/O throttling parameters (max_iops and max_bandwidth), and key information such as access_id and access_key for the current tenant. After modification, the new configuration will overwrite the original one.
Limitations and considerations
Only user tenants can execute this statement; the system tenant (sys tenant) cannot execute it.
Before modifying the
zone,idc, orregionconfiguration of a path, if you need to set the region level or IDC level, you must first use theALTER SYSTEM MODIFY ZONE zone_name SET REGION=region_name;orALTER SYSTEM MODIFY ZONE zone_name SET IDC=idc_name;statement to set the region information or IDC information respectively. It is recommended to set the source-side configuration at the IDC level. This way, backup tasks can use an IDC with more network bandwidth resources, have a wider range of available nodes, and users do not need to worry about changes in zones within the IDCs of the cluster.When you modify the source-side configuration of a backup path, if any backup tasks are in progress, the change may not be detected by those tasks for up to several minutes. During this period, nodes outside the configured scope may still access the backup path, potentially generating cross-IDC or cross-region traffic.
Only the object storage path supports changing the I/O throttling parameters (
max_iopsandmax_bandwidth) and theaccess_idandaccess_keyinformation. If you set I/O throttling parameters (max_iopsandmax_bandwidth) oraccess_id/access_keyfor a backup medium that is an NFS path, the system returns an error code1235, indicating that the operation is not supported.When setting the
ACCESS_INFOattribute (access_idandaccess_key) for an object storage path, if the parameter format is incorrect (for example, contains extra=, or is missingaccess_idoraccess_key), the system returns the errorOB_OBJECT_STORAGE_PERMISSION_DENIED(error code-9116).When using the Assume Role method to access object paths for backup or archive locations:
If you set
ACCESS_INFO = 'access_id=new_access_id&access_key=new_access_key', the system will report an error.If you set only the I/O throttling parameters in
ATTRIBUTE(without setting theACCESS_INFOattribute), the system may indicate that the setting is not supported or will not take effect.
Multiple settings of the same
ATTRIBUTE+ACCESS_INFOpair are allowed for the same path.
Privilege requirements
The user executing this statement must have the ALTER SYSTEM privilege. For more information about OceanBase AI Database privileges, see Permission classification.
Syntax
ALTER SYSTEM {ALTER|CHANGE|MODIFY} EXTERNAL_STORAGE_DEST
PATH [=] 'path'
SET [ATTRIBUTE = '[{zone=zone_name | idc=idc_name | region=region_name}][&max_iops=iops_num][&max_bandwidth=bandwidth_num]']
[ACCESS_INFO = 'access_id=new_access_id&access_key=new_access_key'];
Parameters
Parameter |
Description |
|---|---|
| path | Specify the backup or archive path for the key information to be changed. The path must be a backup or archive path already set for the current tenant, and it must be an object storage path. The path must also contain host Information. Taking S3 as an example, the path format is as follows: s3://oceanbase-test-bucket/backup/databackup?host=***. |
| zone=zone_name \ | idc=idc_name \ | region=region_name | Specifies the source for the current backup or archive path. Valid values: zone, idc, or region. Only one configuration level from the three levels can be selected at a time, or none can be set. If not explicitly specified, the default is no setting.When modifying the source-side configuration of the log archive path:
When modifying the source configuration of the backup path:
|
| max_iops | Specifies the maximum IOPS allowed for the current backup or archive path. The value must be a positive integer, indicating approximately how many I/O requests per second are allowed. If not explicitly set, this path has no IOPS limit, which is determined by the underlying object storage and network capabilities. Example: |
| max_bandwidth | Specifies the maximum bandwidth allowed for the current backup or archive path. The value must be a positive integer with a unit. Supported units are kb, mb, and gb, representing KB/s, MB/s, and GB/s respectively. If not explicitly set, it indicates that the path has no bandwidth limit. Example: |
| access_id=new_access_id&access_key=new_access_key | Specify the new value after the change. access_id and access_key Information. access_id and access_key Both fields are required. |
Examples
Modify the source configuration of the archive path
Assume the original source configuration for the archive path is zone=z1,z2,z3, and now you need to change it to region=region1. After the modification, only nodes in region1 will be able to access this path.
NFS
obclient(root@mysql001)[(none)]> ALTER SYSTEM MODIFY EXTERNAL_STORAGE_DEST PATH='file:///data/nfs/backup/archive' SET ATTRIBUTE = 'region=region1';S3
obclient(root@mysql001)[(none)]> ALTER SYSTEM CHANGE EXTERNAL_STORAGE_DEST PATH='s3://oceanbase-test-bucket/backup/archive?host=s3.<region>.amazonaws.com' SET ATTRIBUTE = 'region=region1';Object storage services compatible with the S3 protocol (OBS, GCS, and COS)
obclient(root@mysql001)[(none)]> ALTER SYSTEM CHANGE EXTERNAL_STORAGE_DEST PATH='s3://oceanbase-test-bucket/backup/archive?host=obs.****.myhuaweicloud.com' SET ATTRIBUTE = 'region=region1';obclient(root@mysql001)[(none)]> ALTER SYSTEM MODIFY EXTERNAL_STORAGE_DEST PATH='s3://oceanbase-test-bucket/backup/archive?host=https://storage.googleapis.com' SET ATTRIBUTE = 'region=region1';obclient(root@mysql001)[(none)]> ALTER SYSTEM ALTER EXTERNAL_STORAGE_DEST PATH='s3://oceanbase-test-bucket/backup/archive?host=cos.ap-xxxx.myqcloud.com' SET ATTRIBUTE = 'region=region1';
Modify the source configuration of the backup path
The original source configuration for the fake device backup path is zone=z1,z2;z3. Now, you need to change the source configuration to idc=idc1;idc2. After the modification, nodes in idc1 and idc2 can access this path, and idc1 has a higher priority than idc2.
NFS
obclient(root@mysql001)[(none)]> ALTER SYSTEM MODIFY EXTERNAL_STORAGE_DEST PATH='file:///data/nfs/backup/data' SET ATTRIBUTE = 'idc=idc1;idc2';S3
obclient(root@mysql001)[(none)]> ALTER SYSTEM ALTER EXTERNAL_STORAGE_DEST PATH='s3://oceanbase-test-bucket/backup/data?host=s3.<region>.amazonaws.com' SET ATTRIBUTE = 'idc=idc1;idc2';Object storage services compatible with the S3 protocol (OBS, GCS, and COS)
obclient(root@mysql001)[(none)]> ALTER SYSTEM CHANGE EXTERNAL_STORAGE_DEST PATH='s3://oceanbase-test-bucket/backup/data?host=obs.****.myhuaweicloud.com' SET ATTRIBUTE = 'idc=idc1;idc2';obclient(root@mysql001)[(none)]> ALTER SYSTEM MODIFY EXTERNAL_STORAGE_DEST PATH='s3://oceanbase-test-bucket/backup/data?host=https://storage.googleapis.com' SET ATTRIBUTE = 'idc=idc1;idc2';obclient(root@mysql001)[(none)]> ALTER SYSTEM ALTER EXTERNAL_STORAGE_DEST PATH='s3://oceanbase-test/backup/data?host=cos.ap-xxxx.myqcloud.com' SET ATTRIBUTE = 'idc=idc1;idc2';
Change the access_id and access_key information for the archive path
S3
obclient(root@mysql001)[(none)]> ALTER SYSTEM CHANGE EXTERNAL_STORAGE_DEST PATH='s3://oceanbase-test-bucket/backup/archive?host=s3.<region>.amazonaws.com' SET ACCESS_INFO = 'access_id=******&access_key=******';Object storage services compatible with the S3 protocol (OBS, GCS, and COS)
obclient(root@mysql001)[(none)]> ALTER SYSTEM CHANGE EXTERNAL_STORAGE_DEST PATH='s3://oceanbase-test-bucket/backup/archive?host=obs.****.myhuaweicloud.com' SET ACCESS_INFO = 'access_id=******&access_key=******';obclient(root@mysql001)[(none)]> ALTER SYSTEM MODIFY EXTERNAL_STORAGE_DEST PATH='s3://oceanbase-test-bucket/backup/archive?host=https://storage.googleapis.com' SET ACCESS_INFO = 'access_id=******&access_key=******';obclient(root@mysql001)[(none)]> ALTER SYSTEM ALTER EXTERNAL_STORAGE_DEST PATH='s3://oceanbase-test-bucket/backup/archive?host=cos.ap-xxxx.myqcloud.com' SET ACCESS_INFO = 'access_id=******&access_key=******';
Change the access_id and access_key information for the backup path
S3
obclient(root@mysql001)[(none)]> ALTER SYSTEM MODIFY EXTERNAL_STORAGE_DEST PATH='s3://oceanbase-test-bucket/backup/data?host=s3.<region>.amazonaws.com' SET ACCESS_INFO = 'access_id=******&access_key=******';Object storage services compatible with the S3 protocol (OBS, GCS, and COS)
obclient(root@mysql001)[(none)]> ALTER SYSTEM CHANGE EXTERNAL_STORAGE_DEST PATH='s3://oceanbase-test-bucket/backup/data?host=obs.****.myhuaweicloud.com' SET ACCESS_INFO = 'access_id=******&access_key=******';obclient(root@mysql001)[(none)]> ALTER SYSTEM MODIFY EXTERNAL_STORAGE_DEST PATH='s3://oceanbase-test-bucket/backup/data?host=https://storage.googleapis.com' SET ACCESS_INFO = 'access_id=******&access_key=******';obclient(root@mysql001)[(none)]> ALTER SYSTEM ALTER EXTERNAL_STORAGE_DEST PATH='s3://oceanbase-test/backup/data?host=cos.ap-xxxx.myqcloud.com' SET ACCESS_INFO = 'access_id=******&access_key=******';
Set I/O throttling parameters (max_iops and max_bandwidth) for the archive path
You can set both the max_iops and max_bandwidth parameters, or set either one of them separately. These parameters are only supported for object storage paths; NFS paths do not support I/O throttling.
S3
Set both the
max_iopsandmax_bandwidthparameters. Example:obclient(root@mysql001)[(none)]> ALTER SYSTEM CHANGE EXTERNAL_STORAGE_DEST PATH='s3://oceanbase-test-bucket/backup/archive?host=s3.<region>.amazonaws.com' SET ATTRIBUTE = 'max_iops=1000&max_bandwidth=1000mb';Object storage services compatible with the S3 protocol (OBS, GCS, and COS)
Set both the
max_iopsandmax_bandwidthparameters at the same time. Example:obclient(root@mysql001)[(none)]> ALTER SYSTEM CHANGE EXTERNAL_STORAGE_DEST PATH='s3://oceanbase-test-bucket/backup/archive?host=obs.****.myhuaweicloud.com' SET ATTRIBUTE = 'max_iops=1000&max_bandwidth=1000mb';obclient(root@mysql001)[(none)]> ALTER SYSTEM MODIFY EXTERNAL_STORAGE_DEST PATH='s3://oceanbase-test-bucket/backup/archive?host=https://storage.googleapis.com' SET ATTRIBUTE = 'max_iops=1000&max_bandwidth=1000mb';obclient(root@mysql001)[(none)]> ALTER SYSTEM ALTER EXTERNAL_STORAGE_DEST PATH='s3://oceanbase-test-bucket/backup/archive?host=cos.ap-xxxx.myqcloud.com' SET ATTRIBUTE = 'max_iops=1000&max_bandwidth=1000mb';
Set I/O throttling parameters (max_iops and max_bandwidth) for the backup path
Note
Suggestions for setting I/O throttling parameters:
- For high-concurrency, bandwidth-sensitive multi-tenant environments, you can set different
max_iopsormax_bandwidthvalues for different backup paths to isolate the impact of backups on online business bandwidth and IOPS. - We recommend that you start with a lower value and gradually adjust it based on your actual network bandwidth and the object storage service's rate-limiting policies. For example, begin by setting it to
max_iops=500&max_bandwidth=100mbto observe the performance, then fine-tune the values based on backup duration and business peak loads.
You can set both the max_iops and max_bandwidth parameters, or set either one of them separately. These parameters are supported only for object storage paths; NFS paths do not support setting I/O throttling parameters.
S3
Set both the
max_iopsandmax_bandwidthparameters. Example:obclient(root@mysql001)[(none)]> ALTER SYSTEM CHANGE EXTERNAL_STORAGE_DEST PATH='s3://oceanbase-test-bucket/backup/data?host=s3.<region>.amazonaws.com' SET ATTRIBUTE = 'max_iops=1000&max_bandwidth=1000mb';Object storage compatible with the S3 protocol (OBS, GCS, and COS)
You can also set both the
max_iopsandmax_bandwidthparameters. Example:obclient(root@mysql001)[(none)]> ALTER SYSTEM CHANGE EXTERNAL_STORAGE_DEST PATH='s3://oceanbase-test-bucket/backup/data?host=obs.****.myhuaweicloud.com' SET ATTRIBUTE = 'max_iops=1000&max_bandwidth=1000mb';obclient(root@mysql001)[(none)]> ALTER SYSTEM ALTER EXTERNAL_STORAGE_DEST PATH='s3://oceanbase-test-bucket/backup/data?host=https://storage.googleapis.com' SET ATTRIBUTE = 'max_iops=1000&max_bandwidth=1000mb';obclient(root@mysql001)[(none)]> ALTER SYSTEM MODIFY EXTERNAL_STORAGE_DEST PATH='s3://oceanbase-test/backup/data?host=cos.ap-xxxx.myqcloud.com' SET ATTRIBUTE = 'max_iops=1000&max_bandwidth=1000mb';