OceanBase summarizes the recommended configurations of core parameters and variables for various business scenarios based on its tuning experience from a large number of real scenarios. OceanBase Database can provide high performance in various business scenarios based on the recommended configurations. This topic describes the recommended configurations of core parameters and variables to ensure query performance in analytical processing (AP) scenarios.
For more information about the recommended parameter configurations for different business scenarios, see the following JSON files in the source code of OceanBase Database:
# system variables
src/share/system_variable/default_system_variable.json
# parameters
src/share/parameter/default_parameter.json
Recommended configurations of parameters
You can use the following parameter configurations in AP scenarios to ensure query performance.
[
{
"scenario": "olap",
"comment": "for real-time data warehouse analytics scenarios.",
"parameters": {
"cluster": [
{
"name": "enable_record_trace_log",
"value": false,
"comment": "disable trace log for better AP performance"
},
{
"name":"trace_log_slow_query_watermark",
"value":"7d",
"comment":"7 days. no 'slow query' concept for AP query"
},
{
"name":"large_query_threshold",
"value":"0ms",
"comment":"disable large query detection for AP query"
}
],
"tenant": [
{
"name":"default_table_store_format",
"value":"column",
"comment":"default to column format for AP"
},
{
"name":"_rowsets_max_rows",
"value": 256,
"comment":"for classic OLAP workloads, rowset 256 is adequate"
}
]
}
}
]
The following table describes the parameters.
| Parameter | Description | Default value | Recommended value for AP scenarios |
|---|---|---|---|
| enable_record_trace_log | Specifies whether to record trace logs. | True | False.This value specifies not to record trace logs, so as to improve AP performance. |
| trace_log_slow_query_watermark | The execution time threshold to identify a slow query. Trace logs of slow queries are written to system logs. | 1s. The default unit is microseconds. |
7d.This value specifies not to define slow queries in AP scenarios. |
| 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. |
| 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 | column.This value specifies to create a columnstore table by default. |
| _rowsets_max_rows | The maximum number of rows processed in a vectorized execution by the SQL engine. | 256 | 256.This value is sufficient for conventional online analytical processing (OLAP) workloads. |
Recommended configurations of system variables
You can use the following variable configurations in AP scenarios to ensure query performance.
{
"scenario": "olap",
"comment": "for real-time data warehouse analytics scenarios.",
"variables": {
"tenant": [
{
"name": "ob_query_timeout",
"value": 604800000000,
"comment":"query timeout for AP is 7 days"
},
{
"name": "ob_trx_timeout",
"value": 604800000000,
"comment":"transaction timeout for AP is 7 days"
},
{
"name": "parallel_degree_policy",
"value" : "AUTO",
"comment" : "auto dop is enabled by default for OLAP"
},
{
"name": "parallel_min_scan_time_threshold",
"value": 10,
"comment":"10ms. enable best parallel performance for query which require 100ms+ only"
},
{
"name": "ob_sql_work_area_percentage",
"value": 30,
"comment":"larger sql work area can save spill cost"
},
{
"name": "collation_server",
"value": "utf8mb4_bin",
"comment": "use binary collation can achieve 20% performance gain compared with other collations"
},
{
"name": "collation_connection",
"value": "utf8mb4_bin",
"comment": "use binary collation can achieve 20% performance gain compared with other collations"
}
]
}
},
The following table describes the variables.
| Variable | Description | Default value | Recommended value for AP scenarios |
|---|---|---|---|
| ob_query_timeout | The maximum execution duration allowed for an SQL query. | 10000000, in microseconds | 604800000000.This value indicates seven days. |
| ob_trx_timeout | The transaction timeout period. | 86400000000, in microseconds | 604800000000.This value indicates seven days. |
| parallel_degree_policy | The strategy based on which the optimizer selects the degree of parallelism (DOP). | MANUAL | AUTO.This value specifies to enable the Auto DOP strategy. For more information about Auto DOP, see Auto DOP. |
| 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 ms | 10.When this variable is set to 10, OceanBase Database can achieve optimal parallel execution performance for queries with execution duration of more than 100 ms. |
| ob_sql_work_area_percentage | The maximum percentage of tenant memory for SQL execution. | 5 | 30.This variable is set to a large value to reduce the costs of memory leaks. |
| collation_server | The default character set and collation for the server. | utf8mb4_general_ci | utf8mb4_bin.This 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.This collation can improve the performance by 20% compared with other collations. |
Example (MySQL tenant)
# Set `collation` to `utf8mb4_bin`. The performance is instantly improved by 15%.
set global collation_connection = utf8mb4_bin;
set global collation_server = utf8mb4_bin;
set global ob_query_timeout= 10000000000;
set global ob_trx_timeout= 100000000000;
set global ob_sql_work_area_percentage=30;
set global max_allowed_packet=67108864;
# We recommend that you set the value of `parallel_servers_target` to 10 times the number of CPU cores.
set global parallel_servers_target=1000;
set global parallel_degree_policy = auto;
set global parallel_min_scan_time_threshold = 10;
# `parallel_degree_limit` specifies the maximum DOP allowed when `parallel_degree_policy` is set to `auto`.
# A large DOP can cause performance issues. We recommend that you set `parallel_degree_limit` to the value of `cpu_count` × 2.
set global parallel_degree_limit = 0;
alter system set compaction_low_thread_score = cpu_count;
alter system set compaction_mid_thread_score = cpu_count;
alter system set default_table_store_format = "column";
Example (Oracle tenant)
set global ob_query_timeout= 10000000000;
set global ob_trx_timeout= 100000000000;
set global ob_sql_work_area_percentage=30;
set global max_allowed_packet=67108864;
# We recommend that you set the value of `parallel_servers_target` to 10 times the number of CPU cores.
set global parallel_servers_target=1000;
set global parallel_degree_policy = auto;
set global parallel_min_scan_time_threshold = 10;
# `parallel_degree_limit` specifies the maximum DOP allowed when `parallel_degree_policy` is set to `auto`.
# A large DOP can cause performance issues. We recommend that you set `parallel_degree_limit` to the value of `cpu_count` × 2.
set global parallel_degree_limit = 0;
alter system set compaction_low_thread_score = cpu_count;
alter system set compaction_mid_thread_score = cpu_count;
alter system set default_table_store_format = "column";
References
- For more information about the parameters and variables, see Overview.
- For more information about how to set the parameters, see Set parameters.
- For more information about how to set the variables, see Set variables.