OceanBase Database supports the adaptive major compaction feature starting from 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 compactions to resolve the issue of slow queries in related scenarios, without interrupting user operations. The aforementioned statistics collection and scheduling of major compactions are performed at the partition level.
You can enable the adaptive major compaction feature 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.
Triggering rules
When the partition leader meets the specified conditions, the system adaptively schedules a major compaction 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 compactions can effectively reduce slow queries. If your business requires high query performance and has scenarios supported by adaptive major compactions, we recommend that you do not disable this feature.
In OceanBase Database, parameters whose names start with an underscore (_) are hidden parameters. Hidden parameters 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 the adaptive major compaction feature
The adaptive major compaction feature is controlled by the tenant-level hidden parameter _enable_adaptive_compaction, and is enabled by default.
Log in to the
systenant of the cluster as therootuser.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 | BOOL | 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 | BOOL | 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 | BOOL | 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 | BOOL | 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 | BOOL | True | specifies whether allow adaptive compaction schedule and information collection | TENANT | DYNAMIC_EFFECTIVE | True | YES | +----------------+----------+-------+--------+-----------+-----------------------------+-----------+-------+---------------------------------------------------------------------------------+---------+-------------------+---------------+-----------+ 5 rows in setIn the preceding query result, the value
Truein theVALUEcolumn indicates that the adaptive major compaction feature is enabled.If the feature is not enabled, execute the following statement to enable the feature:
obclient [oceanbase]> ALTER SYSTEM SET _enable_adaptive_compaction = True TENANT = tenant_name;Notice
With this feature enabled, the execution of scheduled major compactions consumes some system resources. To alleviate this, you can disable this feature for the scheduled major compactions of all tables except those in extreme mode by changing the value of the tenant-level hidden parameter
_enable_adaptive_compactiontoFalse.
(Optional) Step 2: Set table_mode to specify the adaptive major compaction strategy for a table
OceanBase Database starts supporting the table option table_mode from V4.2.3. This table option allows you to flexibly solve the performance deterioration issue of buffer tables caused by adaptive major compactions. You can set different table_mode values for tables to specify fast freeze and adaptive major compaction strategies to resolve the buffer table issue.
You can set table_mode when creating or modifying a table, and the valid values are as follows:
| Value | Possibility of triggering a major compaction after a minor compaction |
|---|---|
| normal (default value) | Ultra-low |
| queuing | Low |
| moderate | Medium |
| super | High |
| extreme | Ultra-high |
You can set this option to an appropriate value for each table based on your business needs. Here is an example of create a queuing table:
obclient> CREATE TABLE tbl1 (c1 int, c2 double) table_mode = 'queuing';
In the preceding statement, if you do not change the value of table_mode, the system creates a normal table by default.
If you detect read amplification and buffer table issues with the created table, you can change the value of table_mode for the table to increase the trigger frequency of major compactions. In this case, the query time can be shortened. However, frequent major compactions consume a large amount of computing resources. Therefore, we recommend that you change the value of table_mode based on the frequency level of triggering major compactions. For example, you can change the value from normal to queuing first, and then change it to moderate after a period of observation. The sample code is as follows:
obclient> ALTER TABLE tbl1 SET table_mode = 'moderate';
The setting change takes effect in about 2 minutes after the preceding statement is executed. When the setting is in effect, the system follows the corresponding major compaction strategy to adaptively compact the table and deal with buffer table issues.