Recommended configurations|V4.3.5| docs|Distributed Database

Recommended configurations

Last Updated：2025-10-14 11:53:38 Updated

OceanBase summarizes the recommended configurations of core parameters and variables for various business scenarios based on its tuning experience from a large number of real scenarios. OceanBase Database can provide high performance in various business scenarios based on the recommended configurations. OceanBase Cloud Platform (OCP) allows you to select a parameter configuration template during tenant creation. OCP reads parameter values from the RPM package of the corresponding kernel version based on the selected configuration template. For more information about how to create a tenant by using OCP, see Create a primary tenant.

This topic provides you with recommended configurations of core parameters and system variables for various business scenarios. You can use these configurations after you deploy OceanBase Database with a command-line interface (CLI) tool.

Configuration templates

You can find the templates of recommended configurations for diversified business scenarios under the /home/admin/oceanbase/etc directory after you install the RPM package of OceanBase Database.

default_parameter.json: stores the recommended configurations of parameters.
default_system_variable.json: stores the recommended configurations of variables.

Notice

The parameter information introduced in this topic is based on the configuration template from OceanBase Database V4.3.5 BP4. For recommended configurations of other versions, refer to the configuration file in the installation package of the corresponding version to obtain the latest parameter set.

The templates in parameter configuration files are classified as follows:

express_oltp: This template is suitable for workloads like transaction, payment core systems, and high-throughput internet applications. It has no foreign key constraints, stored procedures, long transactions, large transactions, complex joins, or complex subqueries. For more information about the recommended configurations, see express_oltp.
complex_oltp: This template is suitable for workloads like banking and insurance systems, usually involving complex joins, complex correlated subqueries, batch jobs written in PL, and long and large transactions. Parallel execution may be used for short-term queries. For more information about the recommended configurations, see complex_oltp.
olap: This template is mainly used for real-time data warehouse analysis applications. For more information about the recommended configurations, see olap.
htap: This template is suitable for mixed OLAP and OLTP workloads, including but not limited to real-time operational data analysis, fraud detection, and personalized recommendations. For more information about the recommended configurations, see htap.
kv: This template is suitable for HBase-compatible and Key-Value table model access modes, with single-partition access and no SQL layer. This scenario typically involves very high data throughput demands and is very latency-sensitive. For more information about the recommended configurations, see kv.

express_oltp

Currently, there are no variable configuration recommendations for this scenario.

Recommended values of the parameters in the express_oltp scenario are described as follows:

Parameter	Recommended value	Description	Level
enable_record_trace_log	False	Specifies not to record SQL and transaction trace events.	cluster
enable_syslog_recycle	True	Enabling automatic recycling of syslog can prevent log files from filling up the disk space.	cluster
max_syslog_file_count	300	When `enable_syslog_recycle` is enabled, set this parameter to an appropriate value. 300 is an example value.	cluster
log_transport_compress_all	True	In scenarios with limited bandwidth, RPC compression can save network bandwidth with slight CPU overhead.	tenant
default_load_mode	DISABLED	For simple OLTP workloads, direct load is not used by default.	tenant
direct_load_allow_fallback	True	When direct load fails for simple OLTP workloads, regular import can be initiated.	tenant
_enable_filter_reordering	False	Disables filter reordering for simple OLTP workloads.	tenant
default_table_merge_engine	PARTIAL_UPDATE	Uses the `partial_update` merge engine to handle simple OLTP workloads.	tenant

express_oltp examples

Execute the following statement in the sys tenant to configure the cluster-level parameter.

ALTER SYSTEM SET enable_record_trace_log = False;
ALTER SYSTEM SET enable_syslog_recycle = True;
ALTER SYSTEM SET max_syslog_file_count = 300;

Execute the following statement in the user tenant to configure the tenant-level parameter.

Here is an example of configuring a MySQL-compatible tenant:

ALTER SYSTEM SET log_transport_compress_all = True;
ALTER SYSTEM SET default_load_mode = 'DISABLED';
ALTER SYSTEM SET direct_load_allow_fallback = True;
ALTER SYSTEM SET _enable_filter_reordering = False;
ALTER SYSTEM SET default_table_merge_engine = 'PARTIAL_UPDATE';

Here is an example of configuring an Oracle-compatible tenant:

ALTER SYSTEM SET log_transport_compress_all = True;
ALTER SYSTEM SET default_load_mode = 'DISABLED';
ALTER SYSTEM SET direct_load_allow_fallback = True;
ALTER SYSTEM SET "_enable_filter_reordering" = False;
ALTER SYSTEM SET default_table_merge_engine = 'PARTIAL_UPDATE';

complex_oltp

Currently, there are no variable configuration recommendations for this scenario.

Recommended values of the parameters in the complex_oltp scenario are described as follows:

Parameter	Recommended value	Description	Level
enable_record_trace_log	False	Specifies not to record SQL and transaction trace events.	cluster
large_query_threshold	600s	In complex OLTP scenarios, some queries will run for a long time.	cluster
enable_syslog_recycle	True	Enabling automatic recycling of syslog can prevent log files from filling up disk space.	cluster
max_syslog_file_count	300	When `enable_syslog_recycle` is enabled, set this parameter to an appropriate value. 300 is an example value.	cluster
log_transport_compress_all	True	In scenarios with limited bandwidth, RPC compression can save network bandwidth with slight CPU overhead.	tenant
default_load_mode	DISABLED	For complex OLTP workloads, direct load is not used by default.	tenant
direct_load_allow_fallback	True	When direct load fails for complex OLTP workloads, regular import can be initiated.	tenant
_enable_filter_reordering	False	Disables filter reordering for complex OLTP workloads.	tenant
default_table_merge_engine	PARTIAL_UPDATE	Uses the `partial_update` merge engine to handle complex OLTP workloads.	tenant

complex_oltp examples

Execute the following statement in the sys tenant to configure the cluster-level parameter.

ALTER SYSTEM SET enable_record_trace_log = False;
ALTER SYSTEM SET large_query_threshold = '600s';
ALTER SYSTEM SET enable_syslog_recycle = True;
ALTER SYSTEM SET max_syslog_file_count = 300;

Execute the following statement in the user tenant to configure the tenant-level parameter.

Here is an example for a MySQL-compatible tenant:

ALTER SYSTEM SET log_transport_compress_all = True;
ALTER SYSTEM SET default_load_mode = 'DISABLED';
ALTER SYSTEM SET direct_load_allow_fallback = True;
ALTER SYSTEM SET _enable_filter_reordering = False;
ALTER SYSTEM SET default_table_merge_engine = 'PARTIAL_UPDATE';

Here is an example for an Oracle-compatible tenant:

ALTER SYSTEM SET log_transport_compress_all = True;
ALTER SYSTEM SET default_load_mode = 'DISABLED';
ALTER SYSTEM SET direct_load_allow_fallback = True;
ALTER SYSTEM SET "_enable_filter_reordering" = False;
ALTER SYSTEM SET default_table_merge_engine = 'PARTIAL_UPDATE';

olap

Recommended values of the parameters in the olap scenario are described as follows:

Parameter	Recommended value	Description	Level
enable_record_trace_log	False	Disabling trace logs can improve AP performance.	cluster
trace_log_slow_query_watermark	7d	In AP scenarios, you do not need to define slow queries.	cluster
large_query_threshold	0ms	Disables the detection of large queries.	cluster
enable_syslog_recycle	True	Enabling automatic recycling of syslog can prevent log files from filling up disk space.	cluster
max_syslog_file_count	300	When `enable_syslog_recycle` is enabled, set this parameter to an appropriate value. 300 is an example value.	cluster
default_table_store_format	column	By default, tables are stored in the columnar storage format in AP scenarios.	tenant
_io_read_batch_size	128K	The maximum batch size of a single I/O read request.	tenant
_io_read_redundant_limit_percentage	50	The maximum percentage (of the I/O batch) of redundant data that can be read. Redundant data is the data block that must be read during an I/O batch but is found in the cache or filtered by skip indexes.	tenant
_io_callback_thread_count	64	Number of I/O callback threads	tenant
default_load_mode	DISABLED	By default, direct load capability is not enabled. If you need to use direct load in specific scenarios, you can specify it using a hint.	tenant
direct_load_allow_fallback	False	When direct load fails for classic OLAP workloads, regular import can be initiated.	tenant
_nested_loop_join_enabled	False	For classic OLAP workloads, nested loop joins are disabled.	tenant
default_table_organization	HEAP	The storage order of data rows in a table. In AP scenarios, data rows in a table are stored without a specific order. Note This parameter was introduced in V4.3.5 BP1.	tenant
default_table_merge_engine	DELETE_INSERT	Uses the `delete_insert` merge engine to handle OLAP workloads.	tenant
_force_subquery_unnest	True	This parameter controls whether the optimizer, when attempting to rewrite subqueries as join forms, abandons some rewrite opportunities based on prior assumptions. In OLAP mode, set this value to `True`, so the optimizer will no longer consider any prior pruning rules; as long as correctness is ensured, all subqueries that can be rewritten as joins will be rewritten.	tenant
max_partition_num	65536	Increase the maximum number of partitions per table for AP/HTAP workloads to 65,536, supporting large-scale partitioning by time range and hash subpartitioning, which is common in data warehouse and real-time analytics scenarios.	tenant

The following table describes the recommended values of the variables in the olap scenario:

Variable	Recommended value	Description	Level
ob_query_timeout	604800000000	7 days.	tenant
ob_trx_timeout	604800000000	7 days.	tenant
parallel_degree_policy	AUTO	Specifies to enable the auto DOP strategy. For more information about auto DOP, see Auto DOP.	tenant
parallel_min_scan_time_threshold	10	Provides optimal parallel execution performance for queries with execution time over 100ms.	tenant
ob_sql_work_area_percentage	30	A large work area size can reduce the costs of memory leaks.	tenant
collation_server	utf8mb4_bin	A binary collation can improve performance by 20% compared with other collations.	tenant
collation_connection	utf8mb4_bin	A binary collation can improve performance by 20% compared with other collations.	tenant

Examples

Execute the following statement in the sys tenant to configure the cluster-level parameter.

ALTER SYSTEM SET enable_record_trace_log = False;
ALTER SYSTEM SET trace_log_slow_query_watermark = '7d';
ALTER SYSTEM SET large_query_threshold = '0ms';
ALTER SYSTEM SET enable_syslog_recycle = True;
ALTER SYSTEM SET max_syslog_file_count = 300;

Execute the following statement in the user tenant to configure the tenant-level parameter.

Here is an example of configuring a MySQL-compatible tenant:

ALTER SYSTEM SET default_table_store_format = 'column';
ALTER SYSTEM SET _io_read_batch_size = '128K';
ALTER SYSTEM SET _io_read_redundant_limit_percentage = 50;
ALTER SYSTEM SET _io_callback_thread_count = 64;
ALTER SYSTEM SET default_load_mode = 'DISABLED';
ALTER SYSTEM SET direct_load_allow_fallback = False;
ALTER SYSTEM SET _nested_loop_join_enabled = False;
ALTER SYSTEM SET default_table_organization = 'HEAP';
ALTER SYSTEM SET default_table_merge_engine = 'DELETE_INSERT';
ALTER SYSTEM SET _force_subquery_unnest = True;
ALTER SYSTEM SET max_partition_num = 65536;

Here is an example of configuring an Oracle-compatible tenant:

ALTER SYSTEM SET default_table_store_format = 'column';
ALTER SYSTEM SET "_io_read_batch_size" = '128K';
ALTER SYSTEM SET "_io_read_redundant_limit_percentage" = 50;
ALTER SYSTEM SET "_io_callback_thread_count" = 64;
ALTER SYSTEM SET default_load_mode = 'DISABLED';
ALTER SYSTEM SET direct_load_allow_fallback = False;
ALTER SYSTEM SET "_nested_loop_join_enabled" = False;
ALTER SYSTEM SET default_table_organization = 'HEAP';
ALTER SYSTEM SET default_table_merge_engine = 'DELETE_INSERT';
ALTER SYSTEM SET "_force_subquery_unnest" = True;
ALTER SYSTEM SET max_partition_num = 65536;

Execute the following statement in the user tenant to configure the tenant-level variable.

Here is an example of configuring a MySQL-compatible tenant:

SET GLOBAL ob_query_timeout = 604800000000;
SET GLOBAL ob_trx_timeout = 604800000000;
SET GLOBAL parallel_degree_policy = AUTO;
SET GLOBAL parallel_min_scan_time_threshold = 10;
SET GLOBAL ob_sql_work_area_percentage = 30;
SET GLOBAL collation_server = utf8mb4_bin;
SET GLOBAL collation_connection = utf8mb4_bin;

Here is an example of configuring an Oracle-compatible tenant:

SET GLOBAL ob_query_timeout = 604800000000;
SET GLOBAL ob_trx_timeout = 604800000000;
SET GLOBAL parallel_degree_policy = AUTO;
SET GLOBAL parallel_min_scan_time_threshold = 10;
SET GLOBAL ob_sql_work_area_percentage = 30;

htap

Currently, there are no variable configuration recommendations for this scenario.

Recommended values of the parameters in the htap scenario are described as follows:

Parameter	Recommended value	Description	Level
large_query_threshold	600s	AP queries exist in HTAP workloads, and we also want them to run quickly.	cluster
enable_record_trace_log	False	Disabling trace logs can improve AP performance.	cluster
enable_syslog_recycle	True	Enabling automatic recycling of syslog can prevent log files from filling up disk space.	cluster
max_syslog_file_count	300	When `enable_syslog_recycle` is enabled, set this parameter to an appropriate value. 300 is an example value.	cluster
log_transport_compress_all	True	In scenarios with limited bandwidth, RPC compression can save network bandwidth with slight CPU overhead.	tenant
default_load_mode	DISABLED	By default, direct load is not used for classic HTAP workloads.	tenant
direct_load_allow_fallback	True	When direct load fails for classic HTAP workloads, regular import can be initiated.	tenant
default_table_merge_engine	PARTIAL_UPDATE	Uses the `partial_update` merge engine to handle HTAP workloads.	tenant
max_partition_num	65536	Increases the maximum number of partitions per table for AP/HTAP workloads to 65,536, supporting large-scale partitioning by time range and hash subpartitioning, which is common in data warehouse and real-time analytics scenarios.	tenant

HTAP scenarios

Execute the following statement in the sys tenant to configure a cluster-level parameter.

ALTER SYSTEM SET large_query_threshold = '600s';
ALTER SYSTEM SET enable_record_trace_log = False;
ALTER SYSTEM SET enable_syslog_recycle = True;
ALTER SYSTEM SET max_syslog_file_count = 300;

Execute the following statement in the user tenant to configure a tenant-level parameter.

Here is an example of configuring a parameter in MySQL-compatible mode:

ALTER SYSTEM SET log_transport_compress_all = True;
ALTER SYSTEM SET default_load_mode = 'DISABLED';
ALTER SYSTEM SET direct_load_allow_fallback = True;
ALTER SYSTEM SET default_table_merge_engine = 'PARTIAL_UPDATE';
ALTER SYSTEM SET max_partition_num = 65536;

Here is an example of configuring a parameter in Oracle-compatible mode:

ALTER SYSTEM SET log_transport_compress_all = True;
ALTER SYSTEM SET default_load_mode = 'DISABLED';
ALTER SYSTEM SET direct_load_allow_fallback = True;
ALTER SYSTEM SET default_table_merge_engine = 'PARTIAL_UPDATE';
ALTER SYSTEM SET max_partition_num = 65536;

kv

Currently, there are no variable configuration recommendations for this scenario.

Recommended values of the parameters in the kv scenario are described as follows:

Parameter	Recommended value	Description	Level
enable_record_trace_log	False	Disabling trace logs can improve AP performance.	cluster
large_query_threshold	0ms	Disables large query detection in KV mode.	cluster
enable_syslog_recycle	True	Enabling automatic recycling of syslog can prevent log files from filling up disk space.	cluster
max_syslog_file_count	300	When `enable_syslog_recycle` is enabled, set this parameter to an appropriate value. 300 is an example value.	cluster
log_transport_compress_all	True	In scenarios with limited bandwidth, RPC compression can save network bandwidth with slight CPU overhead.	tenant
_enable_filter_reordering	False	Disables filter reordering for simple OLTP workloads.	tenant
default_table_merge_engine	PARTIAL_UPDATE	Uses the `partial_update` merge engine in KV mode.	tenant
enable_auto_split	True	Enables automatic partitioning in KV mode.	tenant

Examples

Execute the following statement in the sys tenant to configure the cluster-level parameters.

ALTER SYSTEM SET enable_record_trace_log = False;
ALTER SYSTEM SET large_query_threshold = '0ms';
ALTER SYSTEM SET enable_syslog_recycle = True;
ALTER SYSTEM SET max_syslog_file_count = 300;

Execute the following statement in the user tenant to configure the tenant-level parameters.

Here is an example for a MySQL-compatible tenant:

ALTER SYSTEM SET log_transport_compress_all = True;
ALTER SYSTEM SET _enable_filter_reordering = False;
ALTER SYSTEM SET default_table_merge_engine = 'PARTIAL_UPDATE';
ALTER SYSTEM SET enable_auto_split = True;

Here is an example for an Oracle-compatible tenant:

ALTER SYSTEM SET log_transport_compress_all = True;
ALTER SYSTEM SET "_enable_filter_reordering" = False;
ALTER SYSTEM SET default_table_merge_engine = 'PARTIAL_UPDATE';
ALTER SYSTEM SET enable_auto_split = True;

Query parameter and variable information

You can execute the SHOW PARAMETERS LIKE 'parameter_name'; statement to query the information about a parameter.

Here is an example:
```
SHOW PARAMETERS LIKE 'enable_syslog_recycle';
```
You can execute the following statements to query the information about a variable.
- MySQL-compatible mode: You can execute the SHOW [GLOBAL] VARIABLES LIKE 'variables_name'; statement.
  
  Here is an example:
```
SHOW VARIABLES LIKE 'ob_query_timeout';
```
```
SHOW GLOBAL VARIABLES LIKE 'ob_query_timeout';
```
- Oracle-compatible mode: You can query the SYS.TENANT_VIRTUAL_SESSION_VARIABLE/SYS.TENANT_VIRTUAL_GLOBAL_VARIABLE view for the information about a variable.
  
  Here is an example:
```
SELECT * FROM SYS.TENANT_VIRTUAL_SESSION_VARIABLE WHERE VARIABLE_NAME = 'ob_query_timeout';
```
```
SELECT * FROM SYS.TENANT_VIRTUAL_GLOBAL_VARIABLE WHERE VARIABLE_NAME = 'ob_query_timeout';
```

References

For more information about the parameters and variables, see Overview of parameters and system variables.
For more information about how to set the parameters, see Set parameters.
For more information about how to set the variables, see Set variables.

Recommended configurations

Configuration templates

Notice

express_oltp

express_oltp examples

complex_oltp

complex_oltp examples

olap

Note

Examples

htap

HTAP scenarios

kv

Examples

Query parameter and variable information

References