After you initiate a major compaction, you can query the related views to check the major compaction process.
You can view the compaction process by querying internal tables. Generally, the time required for a major compaction depends on the amount of data changed since the last major compaction. The time required by a major compaction increases when the amount of changed data increases after the last major compaction. The time required by a major compaction is also affected by the number of threads used for the compaction and the load of the cluster during the compaction.
Query the major compaction progress of all tenants from the sys tenant
You can query the oceanbase.CDB_OB_ZONE_MAJOR_COMPACTION and oceanbase.CDB_OB_MAJOR_COMPACTION views for major compaction information about all tenants from the sys tenant.
Log on to the
systenant of the cluster as therootuser.Execute the following statement to view the major compaction progress.
Query information about the major compactions of zones of all tenants.
obclient> SELECT * FROM oceanbase.CDB_OB_ZONE_MAJOR_COMPACTION\G *************************** 1. row *************************** TENANT_ID: 1 ZONE: zone1 BROADCAST_VERSION: 1657130401540694079 LAST_VERSION: 1657130401540694079 LAST_FINISH_TIME: 2022-07-07 02:00:11.646856 START_TIME: 2022-07-07 02:00:01.604192 STATUS: IDLE IS_ERROR: NO INFO: *************************** 2. row *************************** TENANT_ID: 1001 ZONE: zone1 BROADCAST_VERSION: 1657130404994417856 LAST_VERSION: 1657130404994417856 LAST_FINISH_TIME: 2022-07-07 02:06:06.475449 START_TIME: 2022-07-07 02:00:05.008268 STATUS: IDLE IS_ERROR: NO INFO: *************************** 3. row *************************** TENANT_ID: 1002 ZONE: zone1 BROADCAST_VERSION: 1657130404995985329 LAST_VERSION: 1657130404995985329 LAST_FINISH_TIME: 2022-07-07 02:04:38.954977 START_TIME: 2022-07-07 02:00:05.064080 STATUS: IDLE IS_ERROR: NO INFO: *************************** 4. row *************************** TENANT_ID: 1003 ZONE: zone1 BROADCAST_VERSION: 1657130401541700827 LAST_VERSION: 1657130401541700827 LAST_FINISH_TIME: 2022-07-07 02:04:57.750464 START_TIME: 2022-07-07 02:00:01.658430 STATUS: IDLE IS_ERROR: NO INFO: *************************** 5. row *************************** TENANT_ID: 1004 ZONE: zone1 BROADCAST_VERSION: 1657130401542173174 LAST_VERSION: 1657130401542173174 LAST_FINISH_TIME: 2022-07-07 02:05:38.452746 START_TIME: 2022-07-07 02:00:01.658391 STATUS: IDLE IS_ERROR: NO INFO: *************************** 6. row *************************** TENANT_ID: 1007 ZONE: zone1 BROADCAST_VERSION: 1657130402418968296 LAST_VERSION: 1657130402418968296 LAST_FINISH_TIME: 2022-07-07 02:04:24.919463 START_TIME: 2022-07-07 02:00:02.529412 STATUS: IDLE IS_ERROR: NO INFO: *************************** 7. row *************************** TENANT_ID: 1008 ZONE: zone1 BROADCAST_VERSION: 1657130402672739296 LAST_VERSION: 1657130402672739296 LAST_FINISH_TIME: 2022-07-07 02:05:09.385212 START_TIME: 2022-07-07 02:00:02.744969 STATUS: IDLE IS_ERROR: NO INFO: 7 rows in setCheck the value in the
statuscolumn in the execution result. The following table describes the fields in the execution result.Field Description TENANT_ID The ID of the tenant. ZONE The name of the zone. BROADCAST_VERSION The major compaction version that is broadcast. LAST_VERSION The version number of the last major compaction. LAST_FINISH_TIME The time when the last major compaction was completed. START_TIME The time when the major compaction started. STATUS The major compaction status. Valid values: IDLE: No major compaction is in progress.COMPACTING: A major compaction is in progress.VERIFYING: The checksum is being verified.
IS_ERROR YES: An error occurred during the major compaction.NO: No error occurred during the major compaction.
INFO The major compaction information. Query the global major compaction information of all tenants.
obclient> SELECT * FROM oceanbase.CDB_OB_MAJOR_COMPACTION\G *************************** 1. row *************************** TENANT_ID: 1 FROZEN_SNAPSHOT: 1657130401540694079 FROZEN_TIME: 2022-07-07 02:00:01.540694 GLOBAL_BROADCAST_VERSION: 1657130401540694079 LAST_VERSION: 1657130401540694079 LAST_FINISH_TIME: 2022-07-07 02:00:11.651551 START_TIME: 2022-07-07 02:00:01.600871 STATUS: IDLE IS_ERROR: NO IS_SUSPENDED: NO INFO: *************************** 2. row *************************** TENANT_ID: 1001 FROZEN_SNAPSHOT: 1657130404994417856 FROZEN_TIME: 2022-07-07 02:00:04.994418 GLOBAL_BROADCAST_VERSION: 1657130404994417856 LAST_VERSION: 1657130404994417856 LAST_FINISH_TIME: 2022-07-07 02:06:06.480182 START_TIME: 2022-07-07 02:00:05.004782 STATUS: IDLE IS_ERROR: NO IS_SUSPENDED: NO INFO: *************************** 3. row *************************** TENANT_ID: 1002 FROZEN_SNAPSHOT: 1657130404995985329 FROZEN_TIME: 2022-07-07 02:00:04.995985 GLOBAL_BROADCAST_VERSION: 1657130404995985329 LAST_VERSION: 1657130404995985329 LAST_FINISH_TIME: 2022-07-07 02:04:39.318737 START_TIME: 2022-07-07 02:00:05.060462 STATUS: IDLE IS_ERROR: NO IS_SUSPENDED: NO INFO: *************************** 4. row *************************** TENANT_ID: 1003 FROZEN_SNAPSHOT: 1657130401541700827 FROZEN_TIME: 2022-07-07 02:00:01.541701 GLOBAL_BROADCAST_VERSION: 1657130401541700827 LAST_VERSION: 1657130401541700827 LAST_FINISH_TIME: 2022-07-07 02:04:57.764742 START_TIME: 2022-07-07 02:00:01.654864 STATUS: IDLE IS_ERROR: NO IS_SUSPENDED: NO INFO: *************************** 5. row *************************** TENANT_ID: 1004 FROZEN_SNAPSHOT: 1657130401542173174 FROZEN_TIME: 2022-07-07 02:00:01.542173 GLOBAL_BROADCAST_VERSION: 1657130401542173174 LAST_VERSION: 1657130401542173174 LAST_FINISH_TIME: 2022-07-07 02:05:38.456814 START_TIME: 2022-07-07 02:00:01.654895 STATUS: IDLE IS_ERROR: NO IS_SUSPENDED: NO INFO: *************************** 6. row *************************** TENANT_ID: 1007 FROZEN_SNAPSHOT: 1657130402418968296 FROZEN_TIME: 2022-07-07 02:00:02.418968 GLOBAL_BROADCAST_VERSION: 1657130402418968296 LAST_VERSION: 1657130402418968296 LAST_FINISH_TIME: 2022-07-07 02:04:24.928647 START_TIME: 2022-07-07 02:00:02.522797 STATUS: IDLE IS_ERROR: NO IS_SUSPENDED: NO INFO: *************************** 7. row *************************** TENANT_ID: 1008 FROZEN_SNAPSHOT: 1657130402672739296 FROZEN_TIME: 2022-07-07 02:00:02.672739 GLOBAL_BROADCAST_VERSION: 1657130402672739296 LAST_VERSION: 1657130402672739296 LAST_FINISH_TIME: 2022-07-07 02:05:09.389730 START_TIME: 2022-07-07 02:00:02.741418 STATUS: IDLE IS_ERROR: NO IS_SUSPENDED: NO INFO: 7 rows in setCheck the value in the
statuscolumn in the execution result. The following table describes the fields in the execution result.Field Description TENANT_ID The ID of the tenant. FROZEN_SNAPSHOT The latest major compaction snapshot. FROZEN_TIME The value of FROZEN_SCNin the form of readable time.GLOBAL_BROADCAST_VERSION The major compaction version that is globally broadcast. LAST_VERSION The version of the last major compaction. LAST_FINISH_TIME The time when the last major compaction was completed. START_TIME The time when the major compaction started. STATUS The major compaction status. Valid values: IDLE: No major compaction is in progress.COMPACTING: A major compaction is in progress.VERIFYING: The checksum is being verified.
IS_ERROR YES: An error occurred during the major compaction.NO: No error occurred during the major compaction.
IS_SUSPENDED YES: The major compaction is suspended.NO: The major compaction is not suspended.
INFO The major compaction information.
Query the major compaction progress of a user tenant
You can query the DBA_OB_ZONE_MAJOR_COMPACTION or DBA_OB_MAJOR_COMPACTION view for the major compaction progress of the current tenant.
Log on to OceanBase Database as the administrator of the user tenant.
Execute the following statement to view the major compaction progress.
Query the major compaction progress of zones of the current tenant.
MySQL mode:
obclient> SELECT * FROM oceanbase.DBA_OB_ZONE_MAJOR_COMPACTION\G *************************** 1. row *************************** ZONE: zone1 BROADCAST_VERSION: 1 LAST_VERSION: 1 LAST_FINISH_TIME: 1970-01-01 08:00:00.000000 START_TIME: 1970-01-01 08:00:00.000000 STATUS: IDLE IS_ERROR: NO INFO: 1 row in setOracle mode:
obclient> SELECT * FROM sys.DBA_OB_ZONE_MAJOR_COMPACTION\GCheck the value in the
statuscolumn in the execution result. The following table describes the fields in the execution result.Field Description ZONE The name of the zone. BROADCAST_VERSION The major compaction version that is broadcast. LAST_VERSION The version number of the last major compaction. LAST_FINISH_TIME The time when the last major compaction was completed. START_TIME The time when the major compaction started. STATUS The major compaction status. Valid values: IDLE: No major compaction is in progress.COMPACTING: A major compaction is in progress.VERIFYING: The checksum is being verified.
IS_ERROR YES: An error occurred during the major compaction.NO: No error occurred during the major compaction.
INFO The major compaction information. Query the global major compaction information of the current tenant.
MySQL mode:
obclient> SELECT * FROM oceanbase.DBA_OB_MAJOR_COMPACTION\GOracle mode:
obclient> SELECT * FROM sys.DBA_OB_MAJOR_COMPACTION\GCheck the value in the
statuscolumn in the execution result. The following table describes the fields in the execution result.Field Description FROZEN_SNAPSHOT The latest major compaction snapshot. FROZEN_TIME The value of FROZEN_SCNin the form of readable time.GLOBAL_BROADCAST_VERSION The major compaction version that is globally broadcast. LAST_VERSION The version of the last major compaction. LAST_FINISH_TIME The time when the last major compaction was completed. START_TIME The time when the major compaction started. STATUS The major compaction status. Valid values: IDLE: No major compaction is in progress.COMPACTING: A major compaction is in progress.VERIFYING: The checksum is being verified.
IS_ERROR YES: An error occurred during the major compaction.NO: No error occurred during the major compaction.
IS_SUSPENDED YES: The major compaction is suspended.NO: The major compaction is not suspended.
INFO The major compaction information.
