To help users achieve optimal performance with OceanBase Database across various business scenarios, OceanBase has summarized some recommended configurations for key parameters and variables based on extensive tuning experience from numerous real-world scenarios. This topic introduces the recommended configurations for parameters in hybrid transaction/analytical processing (HTAP) scenarios.
Background information
HTAP is a database management system or architecture that supports both online transaction processing (OLTP) and online analytical processing (OLAP). HTAP applies to business environments in industries such as financial transactions, retailing, and logistics, where data needs to be analyzed and queried in real time and transactions need to efficiently processed to support business decision-making and analysis. The HTAP architecture features lower data replication and conversion costs and real-time data analysis to speed up business decision-making.
Configuration template
Starting from V4.3.2, OceanBase Database provides recommended parameter configuration templates for different business scenarios in the installation package. After installing OceanBase RPM package, you can find these templates in the /home/admin/oceanbase/etc directory, where:
default_parameter.jsonstores recommended parameter configurations.default_system_variable.jsonstores recommended variables configurations.
Applicable version
This content applies to OceanBase Database V4.3.2.
This section takes OceanBase Database V4.3.2 as an example to introduce the recommended configurations for V4.3.x. You can view the recommended configurations for a version later than V4.3.2 in the configuration template for the corresponding version.
Recommended parameter configurations
In this scenario, there are currently no recommended variable configurations.
The table below lists the recommended parameters and their descriptions for this scenario.
| Parameter | Description | Default value | Value range | Recommended value | Applicable scope | Effective upon OBServer node restart? |
|---|---|---|---|---|---|---|
| large_query_threshold | The execution time threshold to identify a large query. A request may be suspended if its execution time exceeds this threshold. A suspended request is considered a large query and is processed by following the large query scheduling strategy. | 5s |
|
600s. Set this parameter to a value that can ensure quick execution of analytical processing (AP) queries for HTAP workloads. |
Cluster | No |
| enable_syslog_recycle | Specifies whether to record the logs generated before the startup of the OBServer node. You can use this parameter with max_syslog_file_count to specify whether to include earlier log files in the recycling logic. |
False |
|
True. This value specifies to enable automatic system log recycling, so as to prevent log files from occupying disk space. |
Cluster | No |
| max_syslog_file_count | The maximum number of system log files that can be retained. | 0 | [0, +∞) | 300. Set the parameter to an appropriate value when enable_syslog_recycle is set to True. |
Cluster | No |
| _rowsets_max_rows | The maximum number of rows processed in a vectorized execution by the SQL engine. | 256 | [1, 65536] | 32. This value is optimal for classic HTAP workloads. |
Tenant | No |
| log_transport_compress_all | Specifies whether to compress logs for transmission. | False |
|
True. Remote procedure call (RPC) compression can reduce the CPU overhead when the bandwidth resources are limited. |
Tenant | No |
Configuration examples
To configure cluster-level parameters in the
systenant, execute the following statements:obclient> ALTER SYSTEM SET large_query_threshold = '600s'; obclient> ALTER SYSTEM SET enable_syslog_recycle = True; obclient> ALTER SYSTEM SET max_syslog_file_count = 300;To configure tenant-level parameters in a user tenant, execute the following statements:
MySQL tenantOracle tenantobclient> ALTER SYSTEM SET _rowsets_max_rows = 32; obclient> ALTER SYSTEM SET log_transport_compress_all = True;obclient> ALTER SYSTEM SET "_rowsets_max_rows" = 32; obclient> ALTER SYSTEM SET log_transport_compress_all = True;