Purpose
This statement changes 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, modifying
access_idandaccess_keyis supported starting from V4.3.5 BP2.Only user tenants can execute this statement. System tenants (sys tenants) cannot execute it.
Only object storage paths support the modification of I/O throttling parameters (
max_iopsandmax_bandwidth) and theaccess_idandaccess_keyinformation. If you set I/O throttling parameters (max_iopsandmax_bandwidth) oraccess_idandaccess_keyinformation for an NFS path, the system returns an unsupported error with the error code1235.When you set the
ACCESS_INFOattribute (access_idandaccess_key) for an object storage path, if the format is incorrect (for example, if there are extra=signs or ifaccess_idoraccess_keyis missing), the system returns the errorOB_OBJECT_STORAGE_PERMISSION_DENIED(error code-9116).If the backup or archive path accesses the object storage path by using Assume Role:
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 unsupported error or the settings may not take effect.
You can set the same
ATTRIBUTE+ACCESS_INFOattributes multiple times for the same path.
Privilege requirements
To execute this statement, the user must have the ALTER SYSTEM privilege. For more information about the privileges in OceanBase Database, see Privilege types in MySQL 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 | The backup or archive path for which you want to change key information. The path must be a backup or archive path already set for the current tenant, must be an object storage path, and must include host information. For OSS, the path format is as follows: oss://oceanbase-test-bucket/backup/databackup?host=***. |
| max_iops | The maximum IOPS allowed for the current backup or archive path. The value is a positive integer that indicates the approximate number of I/O requests per second. If you do not specify this parameter, IOPS is not limited for this path and is determined by the underlying object storage and network capabilities. Example: |
| max_bandwidth | The maximum bandwidth allowed for the current backup or archive path. The value is a positive integer with a unit. The supported units are kb, mb, and gb, which correspond to KB/s, MB/s, and GB/s. If you do not specify this parameter, bandwidth is not limited for this path. Example: |
| access_id=new_access_id&access_key=new_access_key | The new access_id and access_key after the change. You must specify both the access_id and access_key. |
Examples
Change the access_id and access_key of an 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=******';S3-compatible object storage (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 of a 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=******';S3-compatible object storage (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 I/O throttling parameters for an archive path (max_iops and max_bandwidth)
You can set the max_iops and max_bandwidth parameters at the same time, or set only one of the parameters. Only object storage paths support I/O throttling parameters. NFS paths do not support I/O throttling parameters.
OSS
The following example shows how to set the
max_iopsandmax_bandwidthparameters at the same time.obclient(root@mysql001)[(none)]> 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';The following example shows how to set only the
max_iopsparameter.obclient(root@mysql001)[(none)]> ALTER SYSTEM MODIFY EXTERNAL_STORAGE_DEST PATH='oss://oceanbase-test-bucket/backup/archive?host=****.aliyun-inc.com' SET ATTRIBUTE = 'max_iops=1000';The following example shows how to set only the
max_bandwidthparameter.obclient(root@mysql001)[(none)]> ALTER SYSTEM MODIFY EXTERNAL_STORAGE_DEST PATH='oss://oceanbase-test-bucket/backup/archive?host=****.aliyun-inc.com' SET ATTRIBUTE = 'max_bandwidth=1000mb';S3
The following example shows how to set the
max_iopsandmax_bandwidthparameters at the same time.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';S3-compatible object storage (OBS/GCS/COS)
The following example shows how to set the
max_iopsandmax_bandwidthparameters at the same time.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 for a backup path (max_iops and max_bandwidth)
Note
Consider the following recommendations when you set I/O throttling parameters:
- For 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 backups on online business bandwidth and IOPS. - We recommend that you start from a low value and gradually adjust the values based on your actual network bandwidth and 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.
You can set the max_iops and max_bandwidth parameters at the same time, or set only one of the parameters. Only object storage paths support I/O throttling parameters. NFS paths do not support I/O throttling parameters.
OSS
The following example shows how to set the
max_iopsandmax_bandwidthparameters at the same time.obclient(root@mysql001)[(none)]> 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';The following example shows how to set only the
max_iopsparameter.obclient(root@mysql001)[(none)]> ALTER SYSTEM MODIFY EXTERNAL_STORAGE_DEST PATH='oss://oceanbase-test-bucket/backup/data?host=****.aliyun-inc.com' SET ATTRIBUTE = 'max_iops=1000';The following example shows how to set only the
max_bandwidthparameter.obclient(root@mysql001)[(none)]> ALTER SYSTEM MODIFY EXTERNAL_STORAGE_DEST PATH='oss://oceanbase-test-bucket/backup/data?host=****.aliyun-inc.com' SET ATTRIBUTE = 'max_bandwidth=1000mb';S3
The following example shows how to set the
max_iopsandmax_bandwidthparameters at the same time.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';S3-compatible object storage (OBS/GCS/COS)
The following example shows how to set the
max_iopsandmax_bandwidthparameters at the same time.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';