Major compactions can also be manually triggered. Manual major compactions include tenant-level, table-level, and partition-level major compactions.
Adaptive major compaction actually schedules partition-level major compactions, except that the system initiates partition-level compaction tasks for the partitions that require them at appropriate times. The use cases of partition-level major compaction are the same as those of adaptive major compaction, mainly including data import, frequent DML operations, and low query efficiency. For more information about adaptive major compaction, see Adaptive compaction. If you have disabled adaptive major compaction for business reasons and encounter issues such as slow queries, you can manually initiate a partition-level major compaction to resolve them.
Limitations and considerations
Partitions and tablets have a one-to-one correspondence. When performing a partition-level major compaction, note the following considerations:
You cannot initiate a partition-level major compaction if the corresponding partition is currently performing a tenant-level major compaction.
You cannot initiate a partition-level major compaction if the corresponding partition is currently performing an adaptive-scheduled major compaction.
You cannot initiate a partition-level major compaction if multiple replicas of the corresponding partition are in an inconsistent state.
You cannot initiate a partition-level major compaction if the corresponding partition is in the recovery or transfer state.
You cannot initiate a partition-level major compaction if the major compaction task is paused.
Partition-level major compaction essentially executes a major compaction task on multiple replicas of the same partition, which consumes CPU and disk I/O. Before performing a partition-level major compaction, evaluate the resource usage of the current tenant. After the partition-level major compaction statement is executed successfully, CPU and I/O usage will increase.
Table-level major compaction refers to initiating a compaction statement for all partitions of a table in a tenant. Table-level major compaction has the following limitations:
Table-level major compaction is supported only for user tables whose data is stored in the database.
Similar to partition-level major compaction, table-level major compaction is an asynchronous task and its success is not guaranteed.
Prerequisites
Before triggering a major compaction, ensure that the global major compaction switch is enabled. This switch is controlled by the cluster-level parameter enable_major_freeze, with a default value of True, indicating the switch is enabled.
If the global major compaction switch is not enabled for the current cluster, perform the following steps to enable it.
Log in to the
systenant of the cluster as therootuser.Check whether the global major compaction switch is enabled.
obclient> SHOW PARAMETERS LIKE 'enable_major_freeze';If it is not enabled, execute the following statement to enable the switch.
obclient> ALTER SYSTEM SET enable_major_freeze='True';For more information about the
enable_major_freezeparameter, see enable_major_freeze.
Manually initiate a major compaction in the sys tenant
The sys tenant can initiate a major compaction for itself, for all tenants, or for specified tenants.
The procedure is as follows:
Log in to the
systenant of the cluster as therootuser.Execute the following statement to initiate a major compaction.
Initiate a tenant-level major compaction
The SQL statement is as follows:
ALTER SYSTEM MAJOR FREEZE TENANT [=] all_user | all | all_meta | tenant_name [, tenant_name ...];Here,
[=]indicates that the equal sign (=) is optional.Examples:
The sys tenant initiates a major compaction for itself.
obclient> ALTER SYSTEM MAJOR FREEZE;The sys tenant initiates a major compaction for all user tenants.
obclient> ALTER SYSTEM MAJOR FREEZE TENANT = all_user;Or
obclient> ALTER SYSTEM MAJOR FREEZE TENANT = all;Note
TENANT = all_userhas the same semantics asTENANT = all. When the scope needs to cover all user tenants, we recommend usingTENANT = all_user.TENANT = allwill be deprecated in the future.The sys tenant initiates a major compaction for all Meta tenants.
obclient> ALTER SYSTEM MAJOR FREEZE TENANT = all_meta;The sys tenant initiates a major compaction for a specified tenant.
obclient> ALTER SYSTEM MAJOR FREEZE TENANT = tenant1,tenant2;
Initiate a table-level major compaction
The SQL statement for the system tenant to initiate a table-level major compaction for a specified tenant is as follows:
ALTER SYSTEM MAJOR FREEZE TENANT [=] tenant_name TABLE_ID = table_id;Here,
table_idis the table ID, which the sys tenant can obtain by querying theCDB_OB_TABLE_LOCATIONSview. For more information about the fields in theCDB_OB_TABLE_LOCATIONSview, see oceanbase.CDB_OB_TABLE_LOCATIONS.The following example shows how the sys tenant initiates a table-level major compaction for tenant
tenant1:Obtain the
table_idof the tablet1to be compacted.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 setExecute the following statement to initiate a table-level major compaction.
obclient(root@sys)[oceanbase]> ALTER SYSTEM MAJOR FREEZE TENANT = tenant1 TABLE_ID = 500002;
Initiate a partition-level major compaction
The SQL statement for the sys tenant to initiate a partition-level major compaction for a specified tenant is as follows:
ALTER SYSTEM MAJOR FREEZE TENANT [=] tenant_name TABLET_ID = tablet_id;Here,
tablet_idcan be obtained by the sys tenant by querying theCDB_OB_TABLE_LOCATIONSview. For more information about the fields in theCDB_OB_TABLE_LOCATIONSview, see oceanbase.CDB_OB_TABLE_LOCATIONS.The following example shows how the sys tenant initiates a partition-level major compaction for tenant
tenant1:Obtain the
tablet_idof the partition of the tablet1to be compacted.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 setExecute the following statement to initiate a partition-level major compaction.
obclient(root@sys)[oceanbase]> ALTER SYSTEM MAJOR FREEZE TENANT = tenant1 TABLET_ID = 200007;
Notice
The partition-level major compaction command is mutually exclusive with tenant-level major compactions and major compactions initiated by adaptive scheduling strategies. Successful command execution does not guarantee that the partition-level major compaction is successfully initiated. You can check whether the major compaction is successfully initiated by viewing the information of
ACTION='MEDIUM_MERGE'in the specified partition from theGV$OB_MERGE_INFOview, or by viewing the information ofTYPE='MEDIUM_MERGE'in the specified partition from theGV$OB_TABLET_COMPACTION_HISTORYview. For detailed operations on viewing major compaction information, see View major compaction information.
Manually initiate a major compaction in a user tenant
A user tenant can initiate only tenant-level and partition-level major compactions for itself.
Log in to the database as the tenant administrator of the user tenant.
Initiate a major compaction for the current tenant.
Initiate a tenant-level major compaction
obclient> ALTER SYSTEM MAJOR FREEZE;Initiate a table-level major compaction
The SQL statement for initiating a major compaction for a specified table in a user tenant is as follows:
ALTER SYSTEM MAJOR FREEZE TABLE_ID = table_id;Here,
table_idrepresents the table ID, which can be obtained by querying theDBA_OB_TABLE_LOCATIONSview. For detailed information about the fields in theDBA_OB_TABLE_LOCATIONSview, see DBA_OB_TABLE_LOCATIONS.An example of initiating a major compaction for a specified table in a user tenant is as follows:
Obtain the
table_idof the table t1 to be compacted.The query statement is as follows:
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 setExecute the following statement to initiate a table-level major compaction.
obclient> ALTER SYSTEM MAJOR FREEZE TABLE_ID = 500002;
Initiate a partition-level major compaction
The SQL statement for initiating a major compaction for a specified partition in a user tenant is as follows:
ALTER SYSTEM MAJOR FREEZE TABLET_ID = tablet_id;Here,
tablet_idcan be obtained by querying theDBA_OB_TABLE_LOCATIONSview. For detailed information about the fields in theDBA_OB_TABLE_LOCATIONSview, see DBA_OB_TABLE_LOCATIONS.An example of initiating a major compaction for a specified partition in a user tenant is as follows:
Obtain the
tablet_idof the partition of the tablet1to be compacted.The query statement is as follows:
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 setExecute the following statement 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 tenant-level major compactions and those initiated by the adaptive scheduling strategy. Successful command execution does not guarantee that the partition major compaction is successfully initiated. You can check whether the major compaction is successfully initiated by querying the
TYPE='MEDIUM_MERGE'information in the specified partition using theGV$OB_TABLET_COMPACTION_HISTORYview. For detailed operations on viewing major compaction information, see View major compaction information.