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 online analytical processing (OLAP) scenarios.
Background information
OLAP is a data processing technology for multi-dimensional analysis of large-scale datasets. It enables users to perform complex analyses from various perspectives, including dimensions, measurements, limitations, and sorting. OLAP helps users identify and understand data patterns, trends, and correlations, thereby supporting decision-making and business intelligence. This technology is commonly applied in data warehouses and business intelligence systems.
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
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? |
|---|---|---|---|---|---|---|
| enable_record_trace_log | Specifies whether to record trace logs. | True |
|
False. This value specifies not to record trace logs, so as to improve analytical processing (AP) performance. |
Cluster | No |
| trace_log_slow_query_watermark | The threshold of the query execution duration. A query whose execution duration exceeds this threshold is considered a slow query. Trace logs of slow queries are written to system logs. | 1s. The default unit is microseconds. | [1ms, +∞) | 7d. This value specifies not to define slow queries in AP scenarios. |
Cluster | No |
| 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 |
|
0ms. This value specifies not to detect large queries. |
Cluster | No |
| default_table_store_format | The default format for a table created in a user tenant, which can be a rowstore table, columnstore table, or hybrid rowstore-columnstore table. | row | ("row", "column", "compound").
Note
|
column. This value specifies to create a pure columnstore table by default. |
Tenant | No |
| _rowsets_max_rows | The maximum number of rows processed in a vectorized execution by the SQL engine. | 256 | [1, 65536] | 256. This value is sufficient for conventional OLAP workloads. |
Tenant | No |
Configuration examples
To configure cluster-level parameters in the
systenant, execute the following statements:obclient> ALTER SYSTEM SET enable_record_trace_log = False; obclient> ALTER SYSTEM SET trace_log_slow_query_watermark = '7d'; obclient> ALTER SYSTEM SET large_query_threshold = '0ms';To configure tenant-level parameters in a user tenant, execute the following statements:
MySQL tenantOracle tenantobclient> ALTER SYSTEM SET default_table_store_format = 'column'; obclient> ALTER SYSTEM SET _rowsets_max_rows = 256;obclient> ALTER SYSTEM SET default_table_store_format = 'column'; obclient> ALTER SYSTEM SET "_rowsets_max_rows" = 256;
Recommended variable configurations
You also need to specify variables while specifying parameters. The following table lists the recommended variable configurations.
| Variable | Description | Default value | Recommended value | Applicable scope |
|---|---|---|---|---|
| ob_query_timeout | The maximum execution duration allowed for an SQL query. | 10000000, in microseconds | 604800000000, which indicates 7 days |
|
| ob_trx_timeout | The transaction timeout period. | 86400000000, in microseconds | 604800000000, which indicates 7 days |
|
| parallel_degree_policy | The strategy based on which the optimizer selects the degree of parallelism (DOP). | MANUAL | AUTO, which specifies to enable the Auto DOP strategy. In other words, the optimizer automatically determine the DOP during plan generation. |
|
| parallel_min_scan_time_threshold | The minimum estimated execution duration of the base table scan. This variable is used in the Auto DOP strategy to calculate the DOP. When the estimated execution duration of the base table scan exceeds the specified value, parallel scan is enabled, and an appropriate DOP value is calculated based on the specified value. | 1000, in milliseconds | 10.
NoteWhen this variable is set to |
|
| ob_sql_work_area_percentage | The maximum percentage of tenant memory for SQL execution. | 5 | 30.
NoteThis variable is set to a large value to reduce the costs of memory leaks. |
Global |
| collation_server | The default character set and collation for the server. | utf8mb4_general_ci | utf8mb4_bin.
NoteThis collation can improve the performance by 20% compared with other collations. |
|
| collation_connection | The character set and collation for the connection. | utf8mb4_general_ci | utf8mb4_bin.
NoteThis collation can improve the performance by 20% compared with other collations. |
|
Configuration examples
obclient> SET GLOBAL ob_query_timeout = 604800000000;
obclient> SET GLOBAL ob_trx_timeout = 604800000000;
obclient> SET GLOBAL parallel_degree_policy = AUTO;
obclient> SET GLOBAL parallel_min_scan_time_threshold = 10;
obclient> SET GLOBAL ob_sql_work_area_percentage = 30;
obclient> SET GLOBAL collation_server = utf8mb4_bin;
obclient> SET GLOBAL collation_connection = utf8mb4_bin;
obclient> SET GLOBAL ob_query_timeout = 604800000000;
obclient> SET GLOBAL ob_trx_timeout = 604800000000;
obclient> SET GLOBAL parallel_degree_policy = AUTO;
obclient> SET GLOBAL parallel_min_scan_time_threshold = 10;
obclient> SET GLOBAL ob_sql_work_area_percentage = 30;