To ensure that users can achieve good performance with OceanBase AI Database across various business scenarios, OceanBase has summarized recommended configurations for key parameters and system variables based on extensive optimization experience from real-world scenarios.
OceanBase Cloud Platform (OCP) supports selecting a parameter template in the parameter settings when creating a tenant. Based on the selected template, OCP reads the RPM package content of the corresponding kernel version to obtain the recommended parameter values. For information about creating tenants with OCP, see Create Primary Tenant.
This topic mainly introduces the recommended configurations of key parameters and system variables for different business scenarios.
Parameter configuration templates
After installing the OceanBase AI Database RPM package, you can view the parameter configuration templates for different business scenarios in the /home/admin/oceanbase/etc directory.
default_parameter.json: Stores the recommended parameter configurations.default_system_variable.json: Stores the recommended system variable configurations.
Template categories in the parameter configuration files:
express_oltp: This template is suitable for workloads such as transaction systems, core payment systems, and high-throughput internet applications, with no foreign key constraints, stored procedures, long transactions, large transactions, complex joins, or complex subqueries. It is recommended for AI workloads like vector hybrid search and historical database workloads. For detailed information about the recommended configurations in this scenario, see express_oltp below.olap: This template is primarily used for real-time data warehouse analytics applications. For detailed information about the recommended configurations in this scenario, see olap below.kv: This template is suitable for HBase compatibility and Key-Value table model access modes, single partition access, and no SQL layer. This scenario typically requires extremely high data throughput and is very sensitive to latency. For detailed information about the recommended configurations in this scenario, see KV below.
express_oltp
Currently, there are no recommended variable configurations for this scenario.
Detailed information about the recommended parameter values for the express_oltp scenario is as follows:
Parameter |
Recommended value |
Description |
Level |
|---|---|---|---|
| enable_record_trace_log | False | SQL logging and transaction tracking events are prohibited. | cluster |
| enable_syslog_recycle | True | Enabling automatic syslog recycling can prevent log files from filling up disk space. | cluster |
| max_syslog_file_count | 300 | When enable_syslog_recycle is enabled, this value should be set to an appropriate level. 300 is an empirical value. |
cluster |
| large_query_threshold | 600s | In complex OLTP scenarios, some queries may take a long time to run. | cluster |
| inc_sstable_upload_thread_score | 30 | The number of threads used by a tenant to execute data-sharing tasks in shared storage mode. | tenant |
| enable_rebalance | False | Disable automatic load balancing. | tenant |
Example configuration for the express_oltp scenario
Execute the following statement 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; ALTER SYSTEM SET large_query_threshold = '600s';Execute the following statement in a user tenant to configure tenant-level parameters.
ALTER SYSTEM SET inc_sstable_upload_thread_score = 30; ALTER SYSTEM SET enable_rebalance = False;
olap
Detailed information about the recommended parameter values for the olap scenario is as follows:
Parameter |
Recommended value |
Description |
Level |
|---|---|---|---|
| enable_record_trace_log | False | SQL logging and transaction tracking events are prohibited. | cluster |
| enable_syslog_recycle | True | Enabling automatic syslog recycling can prevent log files from filling up disk space. | cluster |
| max_syslog_file_count | 300 | When enable_syslog_recycle is enabled, this value should be set to an appropriate level. 300 is an empirical value. |
cluster |
| large_query_threshold | 0ms | Disables the detection of large queries. | cluster |
| trace_log_slow_query_watermark | 7d | In AP scenarios, there is no need to define slow queries. | cluster |
| inc_sstable_upload_thread_score | 30 | The number of threads used by a tenant to execute data-sharing tasks in shared storage mode. | tenant |
| ha_high_thread_score | 20 | Sets the current number of worker threads for high-priority threads in high-availability mode. | tenant |
| direct_load_allow_fallback | False | For classic OLAP workloads, if direct load fails, an error is returned directly and fallback to the normal import method is not allowed. | tenant |
| default_table_merge_engine | DELETE_INSERT | Use the delete_insert merge engine to process OLAP workloads. |
tenant |
| default_table_store_format | column | The AP default format is specified as a pure columnar storage table. | tenant |
| default_table_organization | HEAP | By default, data is stored in heap tables in the order it is written, regardless of the primary key sequence. | tenant |
| max_partition_num | 65536 | The maximum number of partitions per table for AP/HTAP workloads has been increased to 65,536, supporting large-scale partitioning by time range and hash subpartitioning, which is common in data warehousing and real-time analytics scenarios. | tenant |
| default_skip_index_level | 1 | Sets the default value of skip_index_level when creating a table in a tenant. | tenant |
| default_delta_format | encoding | Set the default incremental data storage format to encoding when creating a table. | tenant |
| enable_rebalance | False | Disable automatic load balancing. | tenant |
Detailed information about the recommended variable values for the olap scenario is as follows:
Variables |
Recommended value |
Description |
Level |
|---|---|---|---|
| ob_query_timeout | 604800000000 | Set it to 7 days. | tenant |
| ob_trx_timeout | 604800000000 | Set it to 7 days. | tenant |
| parallel_min_scan_time_threshold | 10 | Queries with execution times exceeding 100 ms can achieve optimal parallel execution performance. | tenant |
| ob_sql_work_area_percentage | 30 | Setting a larger workspace memory can help reduce the cost caused by memory leaks. | tenant |
| parallel_degree_policy | AUTO | Indicates that the Auto DOP strategy is enabled. For more information about Auto DOP, see Auto DOP. |
tenant |
| collation_server | utf8mb4_bin | Compared with other collations, using the binary collation can achieve a 20% performance improvement. | tenant |
| collation_connection | utf8mb4_bin | Compared with other collations, using the binary collation can achieve a 20% performance improvement. | tenant |
OLAP scenario configuration example
Execute the following statement 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 statement in a user tenant to configure tenant-level parameters.
ALTER SYSTEM SET inc_sstable_upload_thread_score = 30; ALTER SYSTEM SET ha_high_thread_score = 20; ALTER SYSTEM SET direct_load_allow_fallback = False; ALTER SYSTEM SET default_table_merge_engine = 'DELETE_INSERT'; ALTER SYSTEM SET default_table_store_format = 'column'; ALTER SYSTEM SET default_table_organization = 'HEAP'; ALTER SYSTEM SET max_partition_num = 65536; ALTER SYSTEM SET default_skip_index_level = 1; ALTER SYSTEM SET default_delta_format = 'encoding'; ALTER SYSTEM SET enable_rebalance = False;Execute the following statement in a user tenant to configure tenant-level variables.
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;
KV
No variable configuration is recommended for this scenario.
The recommended values for the KV scenario parameters are described in detail as follows:
Parameter |
Recommended value |
Description |
Level |
|---|---|---|---|
| enable_record_trace_log | False | SQL logging and transaction tracking events are prohibited. | cluster |
| enable_syslog_recycle | True | Enabling automatic syslog recycling can prevent log files from filling up disk space. | cluster |
| max_syslog_file_count | 300 | When enable_syslog_recycle is enabled, this value should be set to an appropriate level. 300 is an empirical value. |
cluster |
| large_query_threshold | 0ms | Disable large query detection in KV mode. | cluster |
| inc_sstable_upload_thread_score | 30 | The number of threads used by a tenant to execute data-sharing tasks in shared storage mode. | tenant |
| enable_rebalance | False | Disable automatic load balancing. | tenant |
KV scenario configuration example
Execute the following statement 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 statement in a user tenant to configure tenant-level parameters.
ALTER SYSTEM SET inc_sstable_upload_thread_score = 30; ALTER SYSTEM SET enable_rebalance = False;
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.
You can use the
SHOW [GLOBAL] VARIABLES LIKE 'variables_name';statement to query.Example:
SHOW VARIABLES LIKE 'ob_query_timeout';SHOW GLOBAL VARIABLES LIKE 'ob_query_timeout';
References
- For more information about parameters and system variables, see Overview of parameters and system variables.
- For more information about how to set parameters, see Set parameters.
- For more information about how to set system variables, see Set variables.