To help users achieve better performance with OceanBase Database in various business scenarios, OceanBase has summarized recommended configurations for core parameters and variables based on tuning experience from a large number of real scenarios. OceanBase Cloud Platform (OCP) allows you to select a parameter template in the parameter settings when creating a tenant. Based on the selected template, OCP reads the parameter values from the RPM package of the corresponding kernel version. For information about creating a tenant by using OCP, see Create a primary tenant.
This topic mainly describes the recommended configurations for core parameters and system variables for different business application scenarios after you deploy OceanBase Database by using the CLI.
Parameter configuration templates
After you install the OceanBase Database RPM package, you can view the parameter configuration templates for recommended configurations in different business scenarios under the /home/admin/oceanbase/etc directory.
default_parameter.json: stores the recommended configurations of parameters.default_system_variable.json: stores the recommended configurations of variables.
Notice
The parameter information in this document is based on the configuration template from OceanBase Database V4.4.1. For recommended configurations of other versions, obtain the latest parameter sets from the configuration files in the installation package of the corresponding version.
Template categories in parameter configuration files:
express_oltp: This template is suitable for workloads such as transaction and payment core systems and high-throughput internet applications, with no foreign key constraints, stored procedures, long transactions, large transactions, complex joins, or complex subqueries. For detailed information about the recommended configurations, see express_oltp.complex_oltp: This template is suitable for workloads such as banking and insurance systems, usually involving complex joins, complex correlated subqueries, batch jobs written in PL, and long and large transactions. Short-running queries may use parallel execution. For detailed information about the recommended configurations, see complex_oltp.olap: This template is mainly used for real-time data warehouse analysis applications. For detailed 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 detailed 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 detailed information about the recommended configurations, see kv.
express_oltp
No variable configurations are recommended for this scenario.
Detailed description of recommended parameter values for the express_oltp scenario:
| Parameter | Recommended value | Description | Level |
|---|---|---|---|
| enable_record_trace_log | False | Disables recording of SQL and transaction trace events. | cluster |
| enable_syslog_recycle | True | Enabling syslog automatic recycling prevents log files from filling up disk space. | cluster |
| max_syslog_file_count | 300 | When enable_syslog_recycle is enabled, set this value appropriately. 300 is an empirical value. |
cluster |
| log_transport_compress_all | True | In bandwidth-limited scenarios, RPC compression can save network bandwidth with minimal CPU overhead. | tenant |
| default_load_mode | DISABLED | For simple OLTP workloads, direct load is not used by default. | tenant |
| direct_load_allow_fallback | True | For simple OLTP workloads, allows fallback to regular import when direct load fails. | 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 for simple OLTP workloads. |
tenant |
| default_micro_block_format_version | 1 | Uses the legacy microblock storage format (original Flat format) for simple OLTP workloads. | tenant |
Examples
Execute the following statements in the sys tenant to configure cluster-level parameters.
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 statements in the user tenant to configure tenant-level parameters.
Example for 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'; ALTER SYSTEM SET default_micro_block_format_version = 1;Example for 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'; ALTER SYSTEM SET default_micro_block_format_version = 1;
complex_oltp
No variable configurations are recommended for this scenario.
Detailed description of recommended parameter values for the complex_oltp scenario:
| Parameter | Recommended value | Description | Level |
|---|---|---|---|
| enable_record_trace_log | False | Disables recording of SQL and transaction trace events. | cluster |
| large_query_threshold | 600s | In complex OLTP scenarios, some queries run for a long time. | cluster |
| enable_syslog_recycle | True | Enabling syslog automatic recycling prevents log files from filling up disk space. | cluster |
| max_syslog_file_count | 300 | When enable_syslog_recycle is enabled, set this value appropriately. 300 is an empirical value. |
cluster |
| log_transport_compress_all | True | In bandwidth-limited scenarios, RPC compression can save network bandwidth with minimal CPU overhead. | tenant |
| default_load_mode | DISABLED | For complex OLTP workloads, direct load is not used by default. | tenant |
| direct_load_allow_fallback | True | For complex OLTP workloads, allows fallback to regular import when direct load fails. | 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 for complex OLTP workloads. |
tenant |
| default_micro_block_format_version | 1 | Uses the legacy microblock storage format (original Flat format) for complex OLTP workloads. | tenant |
Examples
Execute the following statements in the sys tenant to configure cluster-level parameters.
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 statements in the user tenant to configure tenant-level parameters.
Example for 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'; ALTER SYSTEM SET default_micro_block_format_version = 1;Example for 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'; ALTER SYSTEM SET default_micro_block_format_version = 1;
olap
Detailed description of recommended parameter values for the olap scenario:
| 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, slow query definition is not required. | cluster |
| large_query_threshold | 0ms | Disables large query detection. | cluster |
| enable_syslog_recycle | True | Enabling syslog automatic recycling prevents log files from filling up disk space. | cluster |
| max_syslog_file_count | 300 | When enable_syslog_recycle is enabled, set this value appropriately. 300 is an empirical value. |
cluster |
| default_table_store_format | column | Specifies pure columnar table format for AP by default. | 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 redundant data in a single I/O read request (redundant data refers to data blocks that must be read in the batch but hit the cache or were filtered by skip indexes). | tenant |
| _io_callback_thread_count | 64 | The number of I/O callback threads. | tenant |
| default_load_mode | DISABLED | Direct load is not enabled by default; use Hint to specify it when needed. | tenant |
| direct_load_allow_fallback | False | For classic OLAP workloads, when direct load fails, report an error directly and do not allow fallback to regular import. | tenant |
| _nested_loop_join_enabled | False | Disables nested loop joins for classic OLAP workloads. | tenant |
| default_table_organization | HEAP | Default heap table; data is stored in write order, independent of primary key order. | tenant |
| default_table_merge_engine | DELETE_INSERT | Uses the delete_insert merge engine for OLAP workloads. |
tenant |
| default_micro_block_format_version | 1 | Uses the legacy microblock storage format (original Flat format) for OLAP workloads. | tenant |
| _force_subquery_unnest | True | Controls whether the optimizer abandons some rewrite opportunities based on prior assumptions when rewriting subqueries as joins. In OLAP mode, set to True so the optimizer ignores all prior pruning rules and rewrites all subqueries that can be rewritten as joins while ensuring correctness. |
tenant |
| max_partition_num | 65536 | Increases the maximum number of partitions per table for AP/HTAP workloads to 65536, supporting large-scale partitioning by time range and hash subpartitioning, which is common in data warehouse and real-time analysis scenarios. | tenant |
Detailed description of recommended variable values for the olap scenario:
| Variable | Recommended value | Description | Level |
|---|---|---|---|
| ob_query_timeout | 604800000000 | Set to 7 days. | tenant |
| ob_trx_timeout | 604800000000 | Set to 7 days. | tenant |
| parallel_min_scan_time_threshold | 10 | Provides optimal parallel execution performance for queries with execution time of 100ms or more. | tenant |
| ob_sql_work_area_percentage | 30 | A larger work area size can reduce the cost of memory leaks. | tenant |
| parallel_degree_policy | AUTO | Enables the Auto DOP strategy. For more information about Auto DOP, see Auto DOP. | tenant |
| collation_server | utf8mb4_bin | Binary collation can improve performance by 20% compared with other collations. | tenant |
| collation_connection | utf8mb4_bin | Binary collation can improve performance by 20% compared with other collations. | tenant |
Examples
Execute the following statements in the sys tenant to configure cluster-level parameters.
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 statements in the user tenant to configure tenant-level parameters.
Example for 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 default_micro_block_format_version = 1; ALTER SYSTEM SET _force_subquery_unnest = True; ALTER SYSTEM SET max_partition_num = 65536;Example for 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 default_micro_block_format_version = 1; ALTER SYSTEM SET "_force_subquery_unnest" = True; ALTER SYSTEM SET max_partition_num = 65536;
Execute the following statements in the user tenant to configure tenant-level variables.
Example for MySQL-compatible tenant:
SET GLOBAL ob_query_timeout = 604800000000; SET GLOBAL ob_trx_timeout = 604800000000; SET GLOBAL parallel_min_scan_time_threshold = 10; SET GLOBAL ob_sql_work_area_percentage = 30; SET GLOBAL parallel_degree_policy = AUTO; SET GLOBAL collation_server = utf8mb4_bin; SET GLOBAL collation_connection = utf8mb4_bin;Example for Oracle-compatible tenant:
SET GLOBAL ob_query_timeout = 604800000000; SET GLOBAL ob_trx_timeout = 604800000000; SET GLOBAL parallel_min_scan_time_threshold = 10; SET GLOBAL ob_sql_work_area_percentage = 30; SET GLOBAL parallel_degree_policy = AUTO; SET GLOBAL collation_server = utf8mb4_bin; SET GLOBAL collation_connection = utf8mb4_bin;
htap
No variable configurations are recommended for this scenario.
Detailed description of recommended parameter values for the htap scenario:
| Parameter | Recommended value | Description | Level |
|---|---|---|---|
| large_query_threshold | 600s | AP queries exist in HTAP workloads; 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 syslog automatic recycling prevents log files from filling up disk space. | cluster |
| max_syslog_file_count | 300 | When enable_syslog_recycle is enabled, set this value appropriately. 300 is an empirical value. |
cluster |
| log_transport_compress_all | True | In bandwidth-limited scenarios, RPC compression can save network bandwidth with minimal CPU overhead. | tenant |
| default_load_mode | DISABLED | For classic HTAP workloads, direct load is not used by default. | tenant |
| direct_load_allow_fallback | True | For classic HTAP workloads, allows fallback to regular import when direct load fails. | tenant |
| default_table_merge_engine | PARTIAL_UPDATE | Uses partial_update table mode by default. |
tenant |
| default_micro_block_format_version | 1 | Uses the legacy microblock storage format (original Flat format) for HTAP workloads. | tenant |
| max_partition_num | 65536 | Increases the maximum number of partitions per table for AP/HTAP workloads to 65536, supporting large-scale partitioning by time range and hash subpartitioning, which is common in data warehouse and real-time analysis scenarios. | tenant |
Examples
Execute the following statements in the sys tenant to configure cluster-level parameters.
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 statements in the user tenant to configure tenant-level parameters.
Example for 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 default_table_merge_engine = 'PARTIAL_UPDATE'; ALTER SYSTEM SET default_micro_block_format_version = 1; ALTER SYSTEM SET max_partition_num = 65536;Example for 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 default_table_merge_engine = 'PARTIAL_UPDATE'; ALTER SYSTEM SET default_micro_block_format_version = 1; ALTER SYSTEM SET max_partition_num = 65536;
kv
No variable configurations are recommended for this scenario.
Detailed description of recommended parameter values for the kv scenario:
| 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 syslog automatic recycling prevents log files from filling up disk space. | cluster |
| max_syslog_file_count | 300 | When enable_syslog_recycle is enabled, set this value appropriately. 300 is an empirical value. |
cluster |
| log_transport_compress_all | True | In bandwidth-limited scenarios, RPC compression can save network bandwidth with minimal CPU overhead. | tenant |
| _enable_filter_reordering | False | Disables filter reordering in KV mode. | tenant |
| default_table_merge_engine | PARTIAL_UPDATE | Uses the partial_update merge engine in KV mode. |
tenant |
| default_micro_block_format_version | 1 | Uses the legacy microblock storage format (original Flat format) for KV mode. | tenant |
| enable_auto_split | True | Enables automatic partition splitting in KV mode. | tenant |
Examples
Execute the following statements in the sys tenant to configure 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 statements in the user tenant to configure tenant-level parameters.
Example for 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 default_micro_block_format_version = 1; ALTER SYSTEM SET enable_auto_split = True;Example for 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 default_micro_block_format_version = 1; ALTER SYSTEM SET enable_auto_split = True;
View parameters and variables
You can use the
SHOW PARAMETERS LIKE 'parameter_name';statement to query parameter information.Example:
SHOW PARAMETERS LIKE 'enable_syslog_recycle';You can use the following statements to query variable information.
MySQL-compatible mode: You can use the
SHOW [GLOBAL] VARIABLES LIKE 'variables_name';statement.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_VARIABLEviews for variable information.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 detailed information about parameters and variables, see Overview of parameters and system variables.
- For information about how to set parameters, see Set parameters.
- For information about how to set variables, see Set variables.