Purpose
This statement is used to change the I/O throttling parameters (max_iops and max_bandwidth) and key information such as access_id and access_key for the backup or archive path of the current tenant.
Limitations and considerations
For V4.3.5, starting from V4.3.5 BP2, you can modify the
access_idandaccess_keyinformation.Only the user tenant can execute this statement. The sys tenant cannot execute this statement.
Only object storage paths support changing the I/O throttling parameters (
max_iopsandmax_bandwidth) and key information such asaccess_idandaccess_key. If you set I/O throttling parameters (max_iopsandmax_bandwidth) or key information such asaccess_idandaccess_keyfor an NFS path, the system returns an error indicating that the operation is not supported. The error code is1235.If you set the
ACCESS_INFOattribute (access_idandaccess_key) for an object storage path and the parameters are incorrectly formatted (for example, there are extra=signs oraccess_idoraccess_keyis missing), the system returns theOB_OBJECT_STORAGE_PERMISSION_DENIEDerror. The error code is-9116.When an object storage path is accessed using the Assume Role method:
If you set
ACCESS_INFO = 'access_id=new_access_id&access_key=new_access_key', the system returns an error.If you set only the I/O throttling parameters in the
ATTRIBUTEclause without setting theACCESS_INFOattribute, the system may return an error indicating that the operation is not supported or that the settings do not take effect.
You can set the same
ATTRIBUTEandACCESS_INFOattributes for the same path multiple times.
Privilege requirements
The user executing this statement must have the ALTER SYSTEM privilege. For more information about OceanBase Database privileges, see Privilege classification in Oracle mode.
Syntax
ALTER SYSTEM {ALTER|CHANGE|MODIFY} EXTERNAL_STORAGE_DEST
PATH [=] 'path'
SET [ATTRIBUTE = '[max_iops=iops_num][&max_bandwidth=bandwidth_num]']
[ACCESS_INFO = 'access_id=new_access_id&access_key=new_access_key'];
Parameters
| Parameter | Description |
|---|---|
| path | Specifies the backup or archive path for which to change the key information. The path must be a backup or archive path that has been set for the current tenant and must be an object storage path. The path must also contain the host information. For example, in the case of OSS, the path format is as follows: oss://oceanbase-test-bucket/backup/databackup?host=***. |
| max_iops | Specifies the maximum IOPS allowed for the current backup or archive path. The value is a positive integer indicating the number of I/O requests per second. If not explicitly set, the IOPS are not restricted and 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 is 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, the bandwidth is not restricted. Example: |
| access_id=new_access_id&access_key=new_access_key | Specifies the new access_id and access_key information. Both access_id and access_key must be provided. |
Examples
Change the access_id and access_key information for the archive path
OSS
obclient> ALTER SYSTEM MODIFY EXTERNAL_STORAGE_DEST PATH='oss://oceanbase-test-bucket/backup/archive?host=****.aliyun-inc.com' SET ACCESS_INFO = 'access_id=******&access_key=******';S3
obclient> 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 that is compatible with the S3 protocol (OBS/GCS/COS)
obclient> 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> ALTER SYSTEM CHANGE EXTERNAL_STORAGE_DEST PATH='s3://oceanbase-test-bucket/backup/archive?host=https://storage.googleapis.com' SET ACCESS_INFO = 'access_id=******&access_key=******';obclient> ALTER SYSTEM CHANGE 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
OSS
obclient> ALTER SYSTEM CHANGE EXTERNAL_STORAGE_DEST PATH='oss://oceanbase-test-bucket/backup/data?host=****.aliyun-inc.com' SET ACCESS_INFO = 'access_id=******&access_key=******';S3
obclient> ALTER SYSTEM CHANGE EXTERNAL_STORAGE_DEST PATH='s3://oceanbase-test-bucket/backup/data?host=s3.<region>.amazonaws.com' SET ACCESS_INFO = 'access_id=******&access_key=******';Object storage that is compatible with the S3 protocol (OBS/GCS/COS)
obclient> 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> ALTER SYSTEM CHANGE EXTERNAL_STORAGE_DEST PATH='s3://oceanbase-test-bucket/backup/data?host=https://storage.googleapis.com' SET ACCESS_INFO = 'access_id=******&access_key=******';obclient> ALTER SYSTEM CHANGE EXTERNAL_STORAGE_DEST PATH='s3://oceanbase-test/backup/data?host=cos.ap-xxxx.myqcloud.com' SET ACCESS_INFO = 'access_id=******&access_key=******';
Set the I/O throttling parameters (max_iops and max_bandwidth) for the archive path
You can set the max_iops and max_bandwidth parameters at the same time or set one of them separately. Only object storage paths support I/O throttling parameters. NFS paths do not support I/O throttling parameters.
OSS
Set the
max_iopsandmax_bandwidthparameters at the same time. Example:obclient(SYS@oracle001)[SYS]> ALTER SYSTEM MODIFY EXTERNAL_STORAGE_DEST PATH='oss://oceanbase-test-bucket/backup/archive?host=****.aliyun-inc.com' SET ATTRIBUTE = 'max_iops=1000&max_bandwidth=1000mb';Set the
max_iopsparameter separately. Example:obclient(SYS@oracle001)[SYS]> ALTER SYSTEM MODIFY EXTERNAL_STORAGE_DEST PATH='oss://oceanbase-test-bucket/backup/archive?host=****.aliyun-inc.com' SET ATTRIBUTE = 'max_iops=1000';Set the
max_bandwidthparameter separately. Example:obclient(SYS@oracle001)[SYS]> ALTER SYSTEM MODIFY EXTERNAL_STORAGE_DEST PATH='oss://oceanbase-test-bucket/backup/archive?host=****.aliyun-inc.com' SET ATTRIBUTE = 'max_bandwidth=1000mb';S3
Set the
max_iopsandmax_bandwidthparameters at the same time. Example:obclient(SYS@oracle001)[SYS]> 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 that is compatible with the S3 protocol (OBS/GCS/COS)
Set the
max_iopsandmax_bandwidthparameters at the same time. Example:obclient(SYS@oracle001)[SYS]> 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(SYS@oracle001)[SYS]> 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(SYS@oracle001)[SYS]> 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 the I/O throttling parameters (max_iops and max_bandwidth) for the backup path
Note
Recommendations for setting I/O throttling parameters:
- In a highly concurrent and bandwidth-sensitive multi-tenant environment, you can set different
max_iopsormax_bandwidthvalues for different backup paths to isolate the impact of backup on online business bandwidth and IOPS. - We recommend that you start with lower values and gradually adjust them based on your actual network bandwidth and the object storage throttling strategy. For example, you can first set
max_iops=500&max_bandwidth=100mband then fine-tune the values based on the backup duration and business peak hours.
You can set the max_iops and max_bandwidth parameters at the same time or set one of them separately. Only object storage paths support I/O throttling parameters. NFS paths do not support I/O throttling parameters.
OSS
Set the
max_iopsandmax_bandwidthparameters at the same time. Example:obclient(SYS@oracle001)[SYS]> ALTER SYSTEM MODIFY EXTERNAL_STORAGE_DEST PATH='oss://oceanbase-test-bucket/backup/data?host=****.aliyun-inc.com' SET ATTRIBUTE = 'max_iops=1000&max_bandwidth=1000mb';Set the
max_iopsparameter separately. Example:obclient(SYS@oracle001)[SYS]> ALTER SYSTEM MODIFY EXTERNAL_STORAGE_DEST PATH='oss://oceanbase-test-bucket/backup/data?host=****.aliyun-inc.com' SET ATTRIBUTE = 'max_iops=1000';Set the
max_bandwidthparameter separately. Example:obclient(SYS@oracle001)[SYS]> ALTER SYSTEM MODIFY EXTERNAL_STORAGE_DEST PATH='oss://oceanbase-test-bucket/backup/data?host=****.aliyun-inc.com' SET ATTRIBUTE = 'max_bandwidth=1000mb';S3
Set the
max_iopsandmax_bandwidthparameters at the same time. Example:obclient(SYS@oracle001)[SYS]> 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 that is compatible with the S3 protocol (OBS/GCS/COS)
Set the
max_iopsandmax_bandwidthparameters at the same time. Example:obclient(SYS@oracle001)[SYS]> 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(SYS@oracle001)[SYS]> 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(SYS@oracle001)[SYS]> 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';