Manually trigger a major compaction

You can also manually trigger a major compaction. Manual major compactions include tenant-level, table-level, and partition-level major compactions.

Adaptive major compactions are essentially partition-level major compactions, but they are initiated by the system at the right time for the required partitions. The use cases of adaptive major compactions are the same as those of partition-level major compactions, which mainly address scenarios such as derived tables, frequent DML operations, and low query efficiency. For more information about adaptive major compactions, see Adaptive major compactions. If you have disabled adaptive major compactions for business requirements and are experiencing slow queries, you can manually initiate partition-level major compactions to resolve these issues.

Limitations and considerations

Partitions are associated with tablets. When you perform a partition-level major compaction, consider the following:

• If a tenant-level major compaction is being performed on the corresponding partition, you cannot initiate a partition-level major compaction.

• If an adaptive scheduling major compaction is being performed on the corresponding partition, you cannot initiate a partition-level major compaction.

• If the replicas of the corresponding partition are inconsistent, you cannot initiate a partition-level major compaction.

• If the corresponding partition is being restored or transferred, you cannot initiate a partition-level major compaction.

• If the major compaction task is paused, you cannot initiate a partition-level major compaction.

• A partition-level major compaction is a major compaction task performed on multiple replicas of the same partition. This operation consumes CPU and disk I/O resources. Before initiating a partition-level major compaction, you need to evaluate the current resource usage of the tenant. After the partition-level major compaction command is executed, the CPU and I/O usage will increase.

A table-level major compaction is a major compaction task initiated on all partitions of a table. Table-level major compactions have the following limitations:

• Only user tables whose data is stored in the database support table-level major compactions.

• Like partition-level major compactions, table-level major compactions are asynchronous tasks and are not guaranteed to succeed.

Prerequisites

Before triggering a major compaction, ensure that the global major compaction switch is enabled. The global major compaction switch is controlled by the cluster-level configuration item enable_major_freeze, which defaults to True (enabled).

If the global major compaction switch is not enabled for the current cluster, follow these steps to enable it:

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

2. Check whether the global major compaction switch is enabled.

   obclient> SHOW PARAMETERS LIKE 'enable_major_freeze';
   

3. If the switch is not enabled, execute the following statement to enable it:

   obclient> ALTER SYSTEM SET enable_major_freeze='True';
   

   For more information about the enable_major_freeze configuration item, see enable_major_freeze.

Manually initiate a major compaction for the sys tenant

You can manually initiate a major compaction for the sys tenant, all user tenants, or a specified user tenant.

To manually initiate a major compaction, perform the following steps:

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

2. Execute the following statement to initiate a major compaction.

   • Initiate a major compaction for a tenant

     The SQL statement is as follows:

     ALTER SYSTEM MAJOR FREEZE TENANT [=] all_user | all | all_meta | tenant_name [, tenant_name ...];
     

     In the statement, [=] indicates that the equal sign (=) is optional.

     Here are some examples:

     • Initiate a major compaction for the sys tenant.

       obclient> ALTER SYSTEM MAJOR FREEZE;
       

     • Initiate a major compaction for all user tenants.

       obclient> ALTER SYSTEM MAJOR FREEZE TENANT = all_user;
       

       or

       obclient> ALTER SYSTEM MAJOR FREEZE TENANT = all;
       

       Note

       Starting from OceanBase Database V4.2.1, TENANT = all_user and TENANT = all have the same semantics. When you need to specify all user tenants, we recommend that you use TENANT = all_user. The TENANT = all option will be deprecated in the future.

     • Initiate a major compaction for all meta tenants.

       obclient> ALTER SYSTEM MAJOR FREEZE TENANT = all_meta;
       

     • Initiate a major compaction for a specified user tenant.

       obclient> ALTER SYSTEM MAJOR FREEZE TENANT = tenant1,tenant2;
       

   • Initiate a major compaction for a table

     The SQL statement for initiating a major compaction for a table in a specified user tenant is as follows:

     ALTER SYSTEM MAJOR FREEZE TENANT [=] tenant_name TABLE_ID = table_id;
     

     In the statement, table_id specifies the ID of the table to be compacted. You can query the CDB_OB_TABLE_LOCATIONS view as the sys tenant to obtain the table_id value. For more information about the fields in the CDB_OB_TABLE_LOCATIONS view, see oceanbase.CDB_OB_TABLE_LOCATIONS.

     Here is an example of initiating a major compaction for a table in the tenant1 user tenant:

     1. Obtain the table_id value of the table to be compacted, t1.

        obclient(root@sys)[oceanbase]> SELECT a.tenant_id, b.tenant_name, a.database_name, a.table_id, a.table_name FROM oceanbase.CDB_OB_TABLE_LOCATIONS a, oceanbase.DBA_OB_TENANTS b 
        WHERE a.tenant_id=b.tenant_id 
        AND a.table_name = 't1'
        AND b.tenant_name = 'tenant1';
        

        The query result is as follows:

        +-----------+-------------+---------------+----------+------------+
        | tenant_id | tenant_name | database_name | table_id | table_name |
        +-----------+-------------+---------------+----------+------------+
        |      1002 | tenant1     | test          |   500002 | t1         |
        +-----------+-------------+---------------+----------+------------+
        1 row in set
        

     2. Execute the following statement to initiate a major compaction for the table.

        obclient(root@sys)[oceanbase]> ALTER SYSTEM MAJOR FREEZE TENANT = tenant1 TABLE_ID = 500002;
        

   • Initiate a major compaction for a partition

     The SQL statement for initiating a major compaction for a partition in a specified user tenant is as follows:

     ALTER SYSTEM MAJOR FREEZE TENANT [=] tenant_name TABLET_ID = tablet_id;
     

     In the statement, tablet_id specifies the ID of the partition to be compacted. You can query the CDB_OB_TABLE_LOCATIONS view as the sys tenant to obtain the tablet_id value. For more information about the fields in the CDB_OB_TABLE_LOCATIONS view, see oceanbase.CDB_OB_TABLE_LOCATIONS.

     Here is an example of initiating a major compaction for a partition in the tenant1 user tenant:

     1. Obtain the tablet_id value of the partition to be compacted, t1.

        obclient(root@sys)[oceanbase]> SELECT a.tenant_id, b.tenant_name, a.database_name, a.table_id, a.table_name, a.tablet_id, a.partition_name, a.subpartition_name FROM oceanbase.CDB_OB_TABLE_LOCATIONS a, oceanbase.DBA_OB_TENANTS b 
        WHERE a.tenant_id=b.tenant_id 
        AND a.table_name = 't1'
        AND b.tenant_name = 'tenant1';
        

        The query result is as follows:

        +-----------+-------------+---------------+----------+------------+-----------+----------------+-------------------+
        | tenant_id | tenant_name | database_name | table_id | table_name | tablet_id | partition_name | subpartition_name |
        +-----------+-------------+---------------+----------+------------+-----------+----------------+-------------------+
        |      1002 | tenant1     | test          |   500010 | t1         |    200007 | p0             | NULL              |
        |      1002 | tenant1     | test          |   500010 | t1         |    200008 | p1             | NULL              |
        |      1002 | tenant1     | test          |   500010 | t1         |    200009 | p2             | NULL              |
        +-----------+-------------+---------------+----------+------------+-----------+----------------+-------------------+
        3 rows in set
        

     2. Execute the following statement to initiate a major compaction for the partition.

        obclient(root@sys)[oceanbase]> ALTER SYSTEM MAJOR FREEZE TENANT = tenant1 TABLET_ID = 200007;
        

     Notice

     The major compaction command for a partition is mutually exclusive with the major compaction commands for a tenant and the major compactions initiated by the adaptive scheduling strategy. A successful command execution does not necessarily mean that the major compaction for the partition is initiated. You can query the GV$OB_MERGE_INFO view to check whether the major compaction for the partition is initiated. In the view, look for the ACTION='MEDIUM_MERGE' record. You can also query the GV$OB_TABLET_COMPACTION_HISTORY view to check whether the major compaction for the partition is initiated. In the view, look for the TYPE='MEDIUM_MERGE' record. For more information, see View major compaction information.

Manually initiate a tenant-level or partition-level major compaction

A user tenant can only initiate a tenant-level or partition-level major compaction for its own tenant.

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

2. Initiate a tenant-level major compaction for the user tenant.

   • Initiate a tenant-level major compaction

     obclient> ALTER SYSTEM MAJOR FREEZE;
     

   • Initiate a table-level major compaction

     The SQL statement for initiating a table-level major compaction for the user tenant is as follows:

     ALTER SYSTEM MAJOR FREEZE TABLE_ID = table_id;
     

     In the preceding statement, table_id specifies the ID of the table. You can query the DBA_OB_TABLE_LOCATIONS view to obtain the table_id. For more information about the fields in the DBA_OB_TABLE_LOCATIONS view, see DBA_OB_TABLE_LOCATIONS.

     The following example shows how to initiate a table-level major compaction for the user tenant.

     1. Obtain the table_id of the table t1 to be compacted.

        MySQL-compatible mode

        Oracle-compatible mode

        In MySQL-compatible mode, execute the following statement:

        obclient(root@tenant1)[oceanbase]> SELECT database_name, table_id, table_name FROM oceanbase.DBA_OB_TABLE_LOCATIONS WHERE table_name = 't1';
        

        The query result is as follows:

        +---------------+----------+------------+
        | database_name | table_id | table_name |
        +---------------+----------+------------+
        | test          |   500002 | t1         |
        +---------------+----------+------------+
        1 row in set
        

        In Oracle-compatible mode, execute the following statement:

        obclient(sys@tenant1)[SYS]> SELECT database_name, table_id, table_name FROM SYS.DBA_OB_TABLE_LOCATIONS WHERE table_name = 'T1';
        

        The query result is as follows:

        +---------------+----------+------------+
        | DATABASE_NAME | TABLE_ID | TABLE_NAME |
        +---------------+----------+------------+
        | SYS           |   500002 | T1         |
        +---------------+----------+------------+
        1 row in set
        

     2. Execute the following command to initiate a table-level major compaction.

        obclient> ALTER SYSTEM MAJOR FREEZE TABLE_ID = 500002;
        

   • Initiate a partition-level major compaction

     The statement for initiating a partition-level major compaction for the user tenant is as follows:

     ALTER SYSTEM MAJOR FREEZE TABLET_ID = tablet_id;
     

     In the preceding statement, tablet_id specifies the ID of the partition. You can query the DBA_OB_TABLE_LOCATIONS view to obtain the tablet_id. For more information about the fields in the DBA_OB_TABLE_LOCATIONS view, see DBA_OB_TABLE_LOCATIONS.

     The following example shows how to initiate a partition-level major compaction for the user tenant.

     1. Obtain the tablet_id of the partition of the table t1 to be compacted.

        MySQL-compatible mode

        Oracle-compatible mode

        In MySQL-compatible mode, execute the following statement:

        obclient(root@tenant1)[oceanbase]> SELECT database_name, table_id, table_name, tablet_id, partition_name, subpartition_name FROM oceanbase.DBA_OB_TABLE_LOCATIONS WHERE table_name = 't1';
        

        The query result is as follows:

        +---------------+----------+------------+-----------+----------------+-------------------+
        | database_name | table_id | table_name | tablet_id | partition_name | subpartition_name |
        +---------------+----------+------------+-----------+----------------+-------------------+
        | test          |   500010 | t1         |    200007 | p0             | NULL              |
        | test          |   500010 | t1         |    200008 | p1             | NULL              |
        | test          |   500010 | t1         |    200009 | p2             | NULL              |
        +---------------+----------+------------+-----------+----------------+-------------------+
        3 rows in set
        

        In Oracle-compatible mode, execute the following statement:

        obclient(sys@tenant1)[SYS]> SELECT database_name, table_id, table_name, tablet_id, partition_name, subpartition_name FROM SYS.DBA_OB_TABLE_LOCATIONS WHERE table_name = 'T1';
        

        The query result is as follows:

        +---------------+----------+------------+-----------+----------------+-------------------+
        | DATABASE_NAME | TABLE_ID | TABLE_NAME | TABLET_ID | PARTITION_NAME | SUBPARTITION_NAME |
        +---------------+----------+------------+-----------+----------------+-------------------+
        | SYS           |   500010 | T1         |    200007 | P0             | NULL              |
        | SYS           |   500010 | T1         |    200008 | P1             | NULL              |
        | SYS           |   500010 | T1         |    200009 | P2             | NULL              |
        +---------------+----------+------------+-----------+----------------+-------------------+
        3 rows in set
        

     2. Execute the following command to initiate a partition-level major compaction.

        obclient> ALTER SYSTEM MAJOR FREEZE TABLET_ID = 200007;
        

     Notice

     The partition-level major compaction command is mutually exclusive with the tenant-level major compaction command and the major compaction command initiated by the adaptive scheduling strategy. A successful execution of the partition-level major compaction command does not necessarily mean that the major compaction is initiated. You can query the GV$OB_TABLET_COMPACTION_HISTORY view to check whether a major compaction is initiated for the specified partition. For more information, see View major compaction information.

References

• Automatic major compaction triggering

• Scheduled major compaction triggering

• Adaptive major compaction

• Manually control a major compaction

• View major compaction information