Adaptive major compactions

OceanBase Database supports the adaptive major compaction feature since V4.2.0. After this feature is enabled, the system collects statistics on user queries and writes in real time, identifies possible slow query scenarios based on the collected information, and adaptively schedules major compaction tasks to resolve the issue of slow queries in related scenarios, without interrupting user operations. The aforementioned statistics collection and scheduling of major compaction tasks are performed at the partition level.

You can enable adaptive major compaction for the following purposes:

• Avoid some buffer table issues.

• Improve the query performance in data import and export scenarios.

• Identify and avoid some slow query scenarios.

Trigger rules

When the partition leader meets specified conditions, the system adaptively schedules a major compaction task for this partition and the followers. The rules for triggering an adaptive major compaction are as follows:

• The system analyzes the partition statistics collected within a specific period to obtain the number of queries and that of minor compactions for the partition and the proportion of inserted rows in incremental data, to determine whether the current scenario is a data import/export scenario. If yes, the system triggers an adaptive major compaction for the partition.

• The system analyzes the partition statistics collected within a specific period to obtain the number of minor compactions for the partition and the proportion of updated and deleted rows in incremental data, to determine whether the current scenario is a data import/export scenario. If yes, the system triggers an adaptive major compaction for the partition.

• The system calculates the total number of rows in incremental data of the partition to determine whether the current scenario is a continuous write scenario. If yes, the system triggers an adaptive major compaction for the partition.

• The system checks the ratio of the amount of data scanned in queries to the amount of valid data to determine whether queries to this partition are slow queries. If yes, the system triggers an adaptive major compaction for the partition.

Considerations

• Adaptive major compaction can effectively reduce slow queries. If your business requires high query performance and has scenarios supported by adaptive major compaction, we recommend that you do not disable this feature.

• In OceanBase Database, parameters whose names start with an underscore (_) are hidden parameters. They are used by developers only for troubleshooting or emergency O&M. If you want to disable the adaptive major compaction feature, contact OceanBase Technical Support for help.

Step 1: Enable or disable adaptive major compaction

The adaptive major compaction feature is controlled by the tenant-level hidden parameter _enable_adaptive_compaction, and is enabled by default. If you are unsure whether the adaptive major compaction feature is enabled, you can query and enable it by following the steps below.

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

2. Query the value of the tenant-level hidden parameter _enable_adaptive_compaction.

   obclient [oceanbase]> SELECT * FROM oceanbase.GV$OB_PARAMETERS WHERE NAME LIKE '_enable_adaptive_compaction';
   

   The query result is as follows:

   +----------------+----------+-------+--------+-----------+-----------------------------+-----------+-------+---------------------------------------------------------------------------------+---------+-------------------+---------------+-----------+
   | SVR_IP         | SVR_PORT | ZONE  | SCOPE  | TENANT_ID | NAME                        | DATA_TYPE | VALUE | INFO                                                                            | SECTION | EDIT_LEVEL        | DEFAULT_VALUE | ISDEFAULT |
   +----------------+----------+-------+--------+-----------+-----------------------------+-----------+-------+---------------------------------------------------------------------------------+---------+-------------------+---------------+-----------+
   | 172.xx.xxx.xxx |     2882 | zone1 | TENANT |         1 | _enable_adaptive_compaction | NULL      | True  | specifies whether allow adaptive compaction schedule and information collection | TENANT  | DYNAMIC_EFFECTIVE | True          | YES       |
   | 172.xx.xxx.xxx |     2882 | zone1 | TENANT |      1001 | _enable_adaptive_compaction | NULL      | True  | specifies whether allow adaptive compaction schedule and information collection | TENANT  | DYNAMIC_EFFECTIVE | True          | YES       |
   | 172.xx.xxx.xxx |     2882 | zone1 | TENANT |      1002 | _enable_adaptive_compaction | NULL      | True  | specifies whether allow adaptive compaction schedule and information collection | TENANT  | DYNAMIC_EFFECTIVE | True          | YES       |
   | 172.xx.xxx.xxx |     2882 | zone1 | TENANT |      1003 | _enable_adaptive_compaction | NULL      | True  | specifies whether allow adaptive compaction schedule and information collection | TENANT  | DYNAMIC_EFFECTIVE | True          | YES       |
   | 172.xx.xxx.xxx |     2882 | zone1 | TENANT |      1004 | _enable_adaptive_compaction | NULL      | True  | specifies whether allow adaptive compaction schedule and information collection | TENANT  | DYNAMIC_EFFECTIVE | True          | YES       |
   +----------------+----------+-------+--------+-----------+-----------------------------+-----------+-------+---------------------------------------------------------------------------------+---------+-------------------+---------------+-----------+
   5 rows in set
   

   In the query results, the value in the VALUE column is True, indicating that adaptive major compaction is enabled.

3. If adaptive major compaction is not enabled, you can execute the following statement to enable it:

   obclient [oceanbase]> ALTER SYSTEM SET _enable_adaptive_compaction = True TENANT = tenant_name;
   

   Notice

   Since the execution of adaptive major compaction scheduling tasks will occupy some system resources, to reduce the occupation of system resources, you can disable the scheduling of other adaptive major compactions except for extreme mode tables by changing the value of the tenant-level hidden parameter _enable_adaptive_compaction to False.

Step 2: (Optional) Specify the adaptive major compaction strategy using table_mode

After adaptive major compaction is enabled, you can use the table_mode option to specify different adaptive major compaction strategies for different tables. This way, you can flexibly address performance degradation issues caused by buffer tables.

You can specify different adaptive major compaction strategies by using the table option table_mode when you create or modify a table. The table_mode option provides the following five modes, each corresponding to different major compaction strategies.

If you are familiar with the business requirements, you can set a suitable table mode when creating a table. For example, you can create a table in the queuing mode. The following statement shows how to create a table in this mode:

obclient> CREATE TABLE tbl1 (c1 int, c2 double) table_mode = 'queuing';

In the preceding statement, if the table_mode value is not specified, the system creates a table in the normal mode by default.

After creating a table, if you observe serious read amplification and buffer table issues, you can manually modify the table’s table_mode based on your business requirements. This enables the system to trigger major compactions more frequently, accelerating queries. The more frequent major compactions are, the more computing resources are consumed. Therefore, we recommend that you adjust the table_mode value in sequence from low to high based on the major compaction frequency. For example, first adjust table_mode from normal to queuing, and then from queuing to moderate. The following statement shows how to adjust table_mode:

obclient> ALTER TABLE tbl1 SET table_mode = 'moderate';

After executing the statement, the system will take a certain period of time (approximately 2 minutes) for the modification to take effect. Once the modification is effective, the system will schedule adaptive major compactions based on the configured major compaction mode to resolve the buffer table issues.

References

• Automatically trigger a major compaction

• Configure scheduled major compactions

• Manually initiate a major compaction

• Manually control a major compaction