Purpose
This statement updates the I/O throttling parameters (max_iops and max_bandwidth) and the access_id and access_key and other key information of the backup or log archive path in the current tenant.
Limitations and considerations
For V4.3.5, this statement is 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.If the format of the
ACCESS_INFOattribute (access_idandaccess_key) 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 log 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
ATTRIBUTEandACCESS_INFOattributes multiple times for the same path.
Privileges 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 path or log archive path for which you want to change the keys. The path must be an object storage path configured for the current tenant. The path must also contain host information. For example, if you use Alibaba Cloud Object Storage Service (OSS), the path is in the oss://oceanbase-test-bucket/backup/databackup?host=*** format. |
| max_iops | The maximum IOPS allowed for the current backup path or log archive path. You can specify the value as a positive integer. The value indicates the approximate number of I/O requests per second. If you do not specify the value, IOPS for this path is not limited. The actual IOPS is determined by the capabilities of the underlying object storage and network resources. Example: |
| max_bandwidth | The maximum bandwidth allowed for the current backup path or log archive path. You can specify the value as a positive integer with a unit. The supported units are kb, mb, and gb, which correspond to KB/s, MB/s, and GB/s respectively. If you do not specify the value, the bandwidth for this path is not limited. 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=******';Object storage services compatible with the S3 protocol (OBS, GCS, and 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=******';Object storage services compatible with the S3 protocol (OBS, GCS, and 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';Object storage that is compatible with the S3 protocol (OBS, GCS, and 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';Object storage that is compatible with the S3 protocol (OBS, GCS, and 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';
References
Change access_id and access_key of a backup or log archive path