This topic provides parameter configuration recommendations for two business scenarios: hybrid transactional and analytical processing (HTAP) and online analytical processing (OLAP).
To ensure that OceanBase Database delivers optimal performance across various business scenarios, OceanBase has summarized recommended configurations for key parameters and variables based on extensive tuning experience from real-world use cases. These recommended configurations are included in the installation packages for OceanBase Database V4.3.2 and later, and can be viewed after deployment.
For your convenience, parameter templates are supported in both the OceanBase Cloud Platform (OCP) and OceanBase Deployer (obd). When creating a cluster or tenant using OCP or obd, you can directly select the corresponding parameter template for your scenario to complete the configuration.
Parameter configuration templates
After installing the OceanBase Database RPM package, you can find parameter configuration templates with recommended settings for different business scenarios in the /home/admin/oceanbase/etc directory.
default_parameter.json: Contains recommended configurations for parameters.default_system_variable.json: Contains recommended configurations for system variables.
Scenario introduction
HTAP scenario
HTAP refers to database management systems or architectures that support both online transactional processing (OLTP) and online analytical processing (OLAP). HTAP scenarios are common in business environments that require real-time data analysis and queries, such as finance, retail, and logistics. These industries need efficient transaction processing alongside real-time analytics to support business decision-making. The main advantage of the HTAP architecture is its ability to reduce the costs associated with data replication and transformation, deliver real-time analytics, and accelerate the decision-making process.
- For more information about the recommended configurations abd examples for this scenarios, see Recommeded configurations.
Recommended parameter values for HTAP scenarios
| Parameter | Recommended value | Description | Level |
|---|---|---|---|
| large_query_threshold | 600s | AP queries are present 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 syslog auto-recycling prevents log files from filling up disk space. | cluster |
| max_syslog_file_count | 300 | When enable_syslog_recycle is enabled, set this to an appropriate value. 300 is a recommended baseline. |
cluster |
| log_transport_compress_all | true | In bandwidth-constrained scenarios, RPC compression can save network bandwidth with minimal CPU overhead. | tenant |
| default_load_mode | disabled | For typical HTAP workloads, bypass import is disabled by default. | tenant |
| direct_load_allow_fallback | true | For typical HTAP workloads, fallback to regular import is allowed if bypass import fails. | tenant |
OLAP scenario
OLAP is a data processing technology used for multidimensional analysis of large datasets. It allows users to perform complex analyses on data from different perspectives, including dimensions, measures, filters, and sorting. OLAP helps users identify and understand patterns, trends, and relationships within the data, supporting decision-making and business intelligence. OLAP technology is commonly used in data warehouses and business intelligence systems.
- For detailed recommendations and example configurations for this scenario, see Recommended configurations.
Recommended parameter values for OLAP scenarios
| 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, defining slow queries is unnecessary. | cluster |
| large_query_threshold | 0ms | Disables large query detection. | cluster |
| enable_syslog_recycle | true | Enabling syslog auto-recycling prevents log files from filling up disk space. | cluster |
| max_syslog_file_count | 300 | When enable_syslog_recycle is enabled, set this to an appropriate value. 300 is a recommended baseline. |
cluster |
| default_table_store_format | column | The default format for AP workloads is pure columnar storage. | tenant |
| _io_read_batch_size | 128K | The maximum batch size for a single IO read request. | tenant |
| _io_read_redundant_limit_percentage | 50 | The maximum percentage of redundant data in a single IO read request (redundant data refers to blocks that must be read in the batch but are already cached or filtered by skip indexes). | tenant |
| default_load_mode | FULL_DIRECT_WRITE | For classic OLAP workloads, full direct load is used by default. | tenant |
| direct_load_allow_fallback | false | For classic OLAP workloads, fallback to regular import is not allowed if direct load fails; an error is returned instead. | tenant |
| _nested_loop_join_enabled | false | For classic OLAP workloads, nested loop joins are disabled. | tenant |
| default_table_organization | HEAP | The storage order of rows in the table. For AP scenarios, rows are not stored in any specific order. Note: This parameter is available starting from version V4.3.5 BP1. |
tenant |
Recommended values for OLAP scenarios
| Variable | Recommended value | Description | Level |
|---|---|---|---|
| ob_query_timeout | 604800000000 | Sets to 7 days. | tenant |
| ob_trx_timeout | 604800000000 | Sets to 7 days. | tenant |
| parallel_degree_policy | AUTO | Enables the Auto DOP policy. For more details, see Auto DOP. | tenant |
| parallel_min_scan_time_threshold | 10 | Provides optimal parallel execution performance for queries with execution times over 100 ms. | tenant |
| ob_sql_work_area_percentage | 30 | Allocating a larger work area memory can reduce costs caused by memory leaks. | tenant |
| collation_server | utf8mb4_bin | Compared to other collations, using binary collation can improve performance by 20%. | tenant |
| collation_connection | utf8mb4_bin | Compared to other collations, using binary collation can improve performance by 20%. | tenant |