View parameters related to data backup

After a backup operation is performed, you can view the settings of the parameters related to backup.

View the data backup parameters of a user tenant in the sys tenant

1. Log in to the sys tenant of the cluster as the root user.

2. Execute the following statements to query the parameter settings related to backup.

   • Query the current number of worker threads for high availability tasks with low priority, such as backup and backup cleanup.

     Example:

     obclient(root@sys)[(none)]> SHOW PARAMETERS LIKE '%ha_low_thread_score%'\G
     

     The query result is as follows:

     *************************** 1. row ***************************
              zone: zone1
          svr_type: observer
            svr_ip: 172.xx.xxx.xxx
          svr_port: 2882
              name: ha_low_thread_score
         data_type: INT
             value: 0
              info: the current work thread score of high availability low thread. Range: [0,100] in integer. Especially, 0 means default value
           section: OBSERVER
             scope: TENANT
            source: DEFAULT
        edit_level: DYNAMIC_EFFECTIVE
     default_value: 0
         isdefault: 1
     1 row in set
     

     The tenant-level configuration item ha_low_thread_score specifies the current number of worker threads for high availability tasks with low priority, such as backup and backup cleanup. The default value is 0. If you find that the data backup task is running too slowly, you can appropriately increase the value of the ha_low_thread_score configuration item. We recommend that you double the value each time. For more information about the ha_low_thread_score configuration item, see ha_low_thread_score.

     The following example shows how to set the number of concurrent writes to the file system when performing a backup:

     obclient(root@sys)[(none)]> ALTER SYSTEM SET ha_low_thread_score = 4 TENANT = mysql_tenant;
     

     In this example, the value of the ha_low_thread_score configuration item is set to 4, which specifies the number of worker threads for backup tasks in the tenant (mysql_tenant).

   • Query the backup parameters of a tenant (excluding the source configuration).

     The oceanbase.CDB_OB_BACKUP_PARAMETER view records the backup parameters configured for all tenants in the current cluster.

     obclient(root@sys)[oceanbase]> SELECT * FROM oceanbase.CDB_OB_BACKUP_PARAMETER\G
     

     The query result is as follows:

     *************************** 1. row ***************************
     TENANT_ID: 1004
          NAME: data_backup_dest
         VALUE: oss://oceanbase-test-bucket/backup/data/?host=xxx.aliyun-inc.com&access_id=xxx&access_key=xxx&delete_mode=delete&checksum_type=md5&enable_worm=true
     1 row in set
     

     In this example, the data backup path (data_backup_dest) for the tenant with TENANT_ID of 1004 is oss://oceanbase-test-bucket/backup/data/?host=xxx.aliyun-inc.com&access_id=xxx&access_key=xxx, and its backup file cleanup mode (delete_mode) is delete. The delete_mode parameter can take the following two values:

     • delete mode: the cleanup mode is to directly delete the eligible backup files.

       In this mode, when you clean up backup files, the system directly deletes the files that meet the cleanup criteria.

     • tagging mode: the cleanup mode is to set tags for the eligible backup files. The backup files will still be retained.

       In this mode, when you clean up backup files, the system sets tags for the backup files that meet the cleanup criteria. The tag key is "delete_mode", and the tag value is "tagging", so that you can manage the lifecycle of these files on OSS or AWS S3 by using the tags.

     Additionally, in the parameters of the backup path for the tenant, checksum_type=md5 indicates that the MD5 algorithm is used to verify the integrity of backup files, and enable_worm=true specifies that OceanBase Database performs write and delete operations on the path according to the WORM policy.

   • View information related to the backup path of a tenant (including the source configuration).

     The CDB_OB_BACKUP_STORAGE_INFO view records information about the backup or archive paths for all tenants.

     obclient(root@sys)[oceanbase]> SELECT TENANT_ID, PATH, DEST_TYPE, EXTENSION, MAX_IOPS, MAX_BANDWIDTH, MAX_BANDWIDTH_DISPLAY FROM oceanbase.CDB_OB_BACKUP_STORAGE_INFO WHERE DEST_TYPE='backup_data';
     

     The query result is as follows:

     +-----------+---------------------------------------------+-------------+-----------------------------------------------+----------+---------------+-----------------------+
     | TENANT_ID | PATH                                        | DEST_TYPE   | EXTENSION                                     | MAX_IOPS | MAX_BANDWIDTH | MAX_BANDWIDTH_DISPLAY | 
     +-----------+---------------------------------------------+-------------+-----------------------------------------------+----------+---------------+-----------------------+
     |      1004 | oss://oceanbase-test-bucket/backup/data     | backup_data | checksum_type=md5&enable_worm=true&zone=zone1 |     1000 |             0 | UNLIMITED             |
     +-----------+---------------------------------------------+-------------+-----------------------------------------------+----------+---------------+-----------------------+
     1 row in set
     

     In the query result, for the tenant with ID 1004, the backup path is oss://oceanbase-test-bucket/backup/data. The checksum_type=md5 indicates that the MD5 algorithm is used to verify the integrity of backup files, and enable_worm=true specifies that OceanBase Database performs write and delete operations on the path according to the WORM policy. The source configuration is zone=zone1, which means that only the nodes in zone1 can access the path. In terms of I/O throttling, MAX_IOPS is 1000, which means that the current backup path allows at most about 1,000 I/O requests per second. MAX_BANDWIDTH_DISPLAY is UNLIMITED, which means that the bandwidth is not limited.

View the data backup parameters of the current tenant in a user tenant

1. Log in to the database as the tenant administrator.

   Note

   The administrator of a MySQL tenant is the root user. The administrator of an Oracle tenant is the SYS user.

2. Execute the following statements to query the parameter settings related to backup.

   • Query the current number of worker threads for high availability tasks with low priority, such as backup and backup cleanup.

     Example:

     obclient(root@mysql001)[(none)]> SHOW PARAMETERS LIKE '%ha_low_thread_score%'\G
     

     The query result is as follows:

     *************************** 1. row ***************************
              zone: zone1
          svr_type: observer
            svr_ip: 172.xx.xxx.xxx
          svr_port: 2882
              name: ha_low_thread_score
         data_type: INT
             value: 0
              info: the current work thread score of high availability low thread. Range: [0,100] in integer. Especially, 0 means default value
           section: OBSERVER
             scope: TENANT
            source: DEFAULT
        edit_level: DYNAMIC_EFFECTIVE
     default_value: 0
         isdefault: 1
     1 row in set
     

     The tenant-level configuration item ha_low_thread_score specifies the current number of worker threads for high availability tasks with low priority, such as backup and backup cleanup. The default value is 0. If you find that the data backup task is running too slowly, you can appropriately increase the value of the ha_low_thread_score configuration item. We recommend that you double the value each time. For more information about the ha_low_thread_score configuration item, see ha_low_thread_score.

     The following example shows how a user tenant sets the number of concurrent writes to the file system when performing a backup:

     obclient(root@mysql001)[(none)]> ALTER SYSTEM SET ha_low_thread_score = 4;
     

     In this example, the value of the ha_low_thread_score configuration item is set to 4, which specifies the number of worker threads for backup tasks in the current tenant.

   • Query the backup parameters of the current tenant (excluding the source configuration).

     A user tenant can query the backup parameters configured for the current tenant by using the oceanbase.DBA_OB_BACKUP_PARAMETER view in MySQL-compatible mode or the SYS.DBA_OB_BACKUP_PARAMETER view in Oracle-compatible mode.

     MySQL-compatible mode

     Oracle-compatible mode

     The following example shows how to query the parameters in MySQL-compatible mode:

     obclient(root@mysql001)[oceanbase]> SELECT * FROM oceanbase.DBA_OB_BACKUP_PARAMETER;
     

     The following example shows how to query the parameters in Oracle-compatible mode:

     obclient(SYS@oracle001)[SYS]> SELECT * FROM SYS.DBA_OB_BACKUP_PARAMETER;
     

     The query result is as follows:

     +------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
     | NAME             | VALUE                                                                                                                                                 |
     +------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
     | data_backup_dest | oss://oceanbase-test-bucket/backup/data/?host=xxx.aliyun-inc.com&access_id=xxx&access_key=xxx&delete_mode=delete&checksum_type=md5&enable_worm=true   |
     +------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
     1 row in set
     

     According to the query result, in the parameters of the backup path for the current tenant, checksum_type=md5 indicates that the MD5 algorithm is used to verify the integrity of backup files, and enable_worm=true specifies that OceanBase Database performs write and delete operations on the path according to the WORM policy.

   • View information related to the backup path of the current tenant (including the source configuration).

     A user tenant can query the information about the backup or archive paths of the current tenant by using the oceanbase.DBA_OB_BACKUP_STORAGE_INFO view in MySQL-compatible mode or the SYS.DBA_OB_BACKUP_STORAGE_INFO view in Oracle-compatible mode.

     MySQL-compatible mode

     Oracle-compatible mode

     The following example shows how to query the information in MySQL-compatible mode:

     obclient(root@mysql001)[oceanbase]> SELECT PATH, DEST_TYPE, EXTENSION, MAX_IOPS, MAX_BANDWIDTH, MAX_BANDWIDTH_DISPLAY FROM oceanbase.DBA_OB_BACKUP_STORAGE_INFO WHERE DEST_TYPE='backup_data';
     

     The following example shows how to query the information in Oracle-compatible mode:

     obclient(SYS@oracle001)[SYS]> SELECT PATH, DEST_TYPE, EXTENSION, MAX_IOPS, MAX_BANDWIDTH, MAX_BANDWIDTH_DISPLAY FROM SYS.DBA_OB_BACKUP_STORAGE_INFO WHERE DEST_TYPE='backup_data';
     

     The query result is as follows:

     +---------------------------------------------+-------------+-----------------------------------------------+----------+---------------+-----------------------+
     | PATH                                        | DEST_TYPE   | EXTENSION                                     | MAX_IOPS | MAX_BANDWIDTH | MAX_BANDWIDTH_DISPLAY |
     +---------------------------------------------+-------------+-----------------------------------------------+----------+---------------+-----------------------+
     | oss://oceanbase-test-bucket/backup/data     | backup_data | checksum_type=md5&enable_worm=true&zone=zone1 |     1000 |             0 | UNLIMITED             |
     +---------------------------------------------+-------------+-----------------------------------------------+----------+---------------+-----------------------+
     1 row in set
     

     In the query result, the backup path of the current tenant is oss://oceanbase-test-bucket/backup/data. The checksum_type=md5 indicates that the MD5 algorithm is used to verify the integrity of backup files, and enable_worm=true specifies that OceanBase Database performs write and delete operations on the path according to the WORM policy. The source configuration is zone=zone1, which means that only the nodes in zone1 can access the path. In terms of I/O throttling, MAX_IOPS is 1000, which means that the current backup path allows at most about 1,000 I/O requests per second. MAX_BANDWIDTH_DISPLAY is UNLIMITED, which means that the bandwidth is not limited.