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. OceanBase Cloud Platform (OCP) allows you to select a parameter configuration template during tenant creation. OCP reads parameter values from the RPM package of the corresponding kernel version based on the selected configuration template. For more information about how to create a tenant by using OCP, see Create a primary tenant.
This topic provides you with recommended configurations of core parameters and system variables for various business scenarios. You can use these configurations after you deploy OceanBase Database with a command-line interface (CLI) tool.
Configuration templates
You can find the templates of recommended configurations for diversified business scenarios under the /home/admin/oceanbase/etc directory after you install the RPM package of OceanBase Database.
default_parameter.json: stores the recommended configurations of parameters.default_system_variable.json: stores the recommended configurations of variables.
For more information about the code of the configuration templates, see the Sample code section in this topic. The configuration templates are created mainly for the following scenarios:
express_oltp: a scenario that applies to workloads related to transaction processing, core payment systems, and high-throughput online applications. In this scenario, the database needs to quickly respond to massive instant queries on a single record or several relevant records, and process basic data operations such as create, read, update, and delete (CRUD). For more information about the recommended configurations for this scenario, see the express_oltp section.complex_oltp: a scenario that applies to workloads related to banking and insurance systems. These workloads often involve complex join operations, complex join subqueries, batch processing jobs compiled in PL, long-running transactions, or large transactions. Short-running queries are sometimes executed in parallel. For more information about the recommended configurations for this scenario, see the complex_oltp section.olap: a scenario that applies to applications supporting online data analysis and data warehousing. For more information about the recommended configurations for this scenario, see the olap section.htap: a scenario that applies to hybrid transaction and analytical processing (HTAP) workloads. In this scenario, users can quickly get insights from active operation data, detect frauds, and obtain scenario-specific recommendations. For more information about the recommended configurations for this scenario, see the htap section.kv: a scenario that applies to HBase-compatible access, key-value table access, single-partition access, and no-SQL layer. In this scenario, the database is demanding on high data throughput and sensitive to latency. For more information about the recommended configurations for this scenario, see the kv section.
express_oltp
No configurations are recommended for variables in this scenario.
The following table describes the parameters and their recommended configurations.
| Parameter | Description | Default value | Recommended value | Level |
No configurations are recommended for variables in this scenario.
| _enable_defensive_check | Specifies whether to perform defensive checks during the execution of a query. | 1 | 0
Disabling defensive checks in the production environment can increase the performance of DML operations by 10%. | cluster |
| enable_syslog_recycle| Specifies whether to enable recycling for system logs. | False | True
This value specifies to enable automatic system log recycling, so as to prevent log files from occupying disk space. | cluster | | max_syslog_file_count | The maximum number of log files that can be retained. | 0 | 300
Set the parameter to an appropriate value when enable_syslog_recycle is set to True. | cluster | | _rowsets_max_rows | The maximum number of rows that can be processed in a vectorized execution by the SQL engine. | 256 | 1
This value can maximize the effectiveness for simple online transaction processing (OLTP) workloads. | tenant | | 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 |
Examples
- Configure cluster-level parameters in the sys tenant.
Examples
Configure cluster-level parameters in the sys tenant.
ALTER SYSTEM SET max_syslog_file_count = 300;- Configure tenant-level parameters in a user tenant.
- Example (MySQL tenant):
ALTER SYSTEM SET _rowsets_max_rows = 1;
ALTER SYSTEM SET log_transport_compress_all = True;
```
* **Example (Oracle tenant):**
```sql
ALTER SYSTEM SET "_rowsets_max_rows" = 1;
ALTER SYSTEM SET log_transport_compress_all = True;
```
## complex_oltp
No configurations are recommended for variables in this scenario.
**The following table describes the parameters and their recommended configurations.**
| **Parameter** | **Description** | **Default value** | **Recommended value** | **Level** |
|------------|----------|------------|----------------------------------|----------|
| [large_query_threshold](../700.reference/800.configuration-items-and-system-variables/100.system-configuration-items/300.cluster-level-configuration-items/11100.large_query_threshold.md) | 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</br>The query execution may last a long time in a scenario that involves complex OLTP workloads. | cluster |
| _enable_defensive_check | Specifies whether to perform defensive checks during the execution of a query. | 1 | 0</br>Disabling defensive checks in the production environment can increase the performance by 3%. | cluster |
| [enable_syslog_recycle](../700.reference/800.configuration-items-and-system-variables/100.system-configuration-items/300.cluster-level-configuration-items/9000.enable_syslog_recycle.md) | Specifies whether to enable recycling for system logs. | False | True</br>This value specifies to enable automatic system log recycling, so as to prevent log files from occupying disk space. | cluster |
| [max_syslog_file_count](../700.reference/800.configuration-items-and-system-variables/100.system-configuration-items/300.cluster-level-configuration-items/13300.max_syslog_file_count.md) | The maximum number of log files that can be retained. | 0 | 300</br>Set the parameter to an appropriate value when `enable_syslog_recycle` is set to `True`. | cluster |
| _rowsets_max_rows | The maximum number of rows that can be processed in a vectorized execution by the SQL engine. | 256 | 4</br>This value can maximize the effectiveness for complex OLTP workloads. | tenant |
### Examples
1. Configure cluster-level parameters in the sys tenant.
```sql
```sql
ALTER SYSTEM SET large_query_threshold = '600s';
ALTER SYSTEM SET _enable_defensive_check = 0;
ALTER SYSTEM SET enable_syslog_recycle = True;
ALTER SYSTEM SET max_syslog_file_count = 300;
- Configure tenant-level parameters in a user tenant.
- Example (MySQL tenant): ```sql ALTER SYSTEM SET _rowsets_max_rows = 4;
ALTER SYSTEM SET log_transport_compress_all = True;
```
* **Example (Oracle tenant):**
```sql
ALTER SYSTEM SET "_rowsets_max_rows" = 4;
ALTER SYSTEM SET log_transport_compress_all = True;
## olap
**The following table describes the parameters for this scenario and their recommended configurations.**
| **Parameter** | **Description** | **Default value** | **Recommended value** | **Level** |
|------------|----------|------------|--------------------------|----------|
| [enable_record_trace_log](../700.reference/800.configuration-items-and-system-variables/100.system-configuration-items/300.cluster-level-configuration-items/7900.enable_record_trace_log.md) | Specifies whether to record trace logs. | True | False</br>This value specifies not to record trace logs, so as to improve AP performance. | cluster |
| [trace_log_slow_query_watermark](../700.reference/800.configuration-items-and-system-variables/100.system-configuration-items/300.cluster-level-configuration-items/23900.trace_log_slow_query_watermark.md) | 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</br>You do not need to define low queries in AP scenarios. | cluster |
| [large_query_threshold](../700.reference/800.configuration-items-and-system-variables/100.system-configuration-items/300.cluster-level-configuration-items/11100.large_query_threshold.md) | 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</br>This value specifies not to detect large queries. | cluster |
| [default_table_store_format](../700.reference/800.configuration-items-and-system-variables/100.system-configuration-items/400.tenant-level-configuration-items/2200.default_table_store_format.md) | 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</br>This value specifies to create a columnstore table by default. | tenant |
| _rowsets_max_rows | The maximum number of rows that can be processed in a vectorized execution by the SQL engine. | 256 | 256</br>This value is sufficient for conventional online analytical processing (OLAP) workloads. | tenant |
**The following table describes the variables for this scenario and their recommended configurations.**
| **Variable** | **Description** | **Default value** | **Recommended value** | **Level** |
|----------|----------|------------|--------------------------|----------|
| [ob_query_timeout](../700.reference/800.configuration-items-and-system-variables/200.system-variable/300.global-system-variable/8600.ob_query_timeout-global.md) | The maximum execution duration allowed for an SQL query. | 10000000, in ms | 604800000000</br>The recommended duration is seven days. | tenant |
| [ob_trx_timeout](../700.reference/800.configuration-items-and-system-variables/200.system-variable/300.global-system-variable/9700.ob_trx_timeout-global.md) | The transaction timeout period. | 86400000000, in ms | 604800000000</br>The recommended period is seven days. | tenant |
| [parallel_degree_policy](../700.reference/800.configuration-items-and-system-variables/200.system-variable/400.session-system-variable/1400.parallel_degree_policy-session.md)| The strategy based on which the optimizer selects the degree of parallelism (DOP). | MANUAL | AUTO</br>This value specifies to enable the Auto DOP strategy. </br>For more information about Auto DOP, see [Auto DOP](../700.reference/1000.performance-tuning-guide/500.sql-optimization/300.distributed-execution-plan/600.auto-dop.md). | tenant |
| [parallel_min_scan_time_threshold](../700.reference/800.configuration-items-and-system-variables/200.system-variable/400.session-system-variable/1500.parallel_min_scan_time_threshold-session.md) | 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 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</br>When this variable is set to `10`, OceanBase Database can achieve optimal parallel execution performance for queries with a execution duration of more than 100 ms. | tenant |
| [ob_sql_work_area_percentage](../700.reference/800.configuration-items-and-system-variables/200.system-variable/300.global-system-variable/9100.ob_sql_work_area_percentage-global.md) | The maximum percentage of tenant memory for SQL execution. | 5 | 30</br>Set this variable to a large value to reduce the costs of memory leaks. | tenant |
| [collation_server](../700.reference/800.configuration-items-and-system-variables/200.system-variable/300.global-system-variable/1600.collation_server-global.md) | The default character set and collation for the server. | utf8mb4_general_ci | utf8mb4_bin</br>This collation can improve the performance by 20% compared with other collations. | tenant |
| [collation_connection](../700.reference/800.configuration-items-and-system-variables/200.system-variable/300.global-system-variable/1400.collation_connection-global.md) | The character set and collation for the connection. | utf8mb4_general_ci | utf8mb4_bin</br>This collation can improve the performance by 20% compared with other collations. | tenant |
### Examples
1. Configure cluster-level parameters in the sys tenant.
```sql
ALTER SYSTEM SET enable_record_trace_log = False;
ALTER SYSTEM SET trace_log_slow_query_watermark = '7d';
ALTER SYSTEM SET large_query_threshold = '0ms';
```
2. Configure tenant-level parameters in a user tenant.
```
2. Configure tenant-level parameters in a user tenant.
* **Example (MySQL tenant):**
```sql
ALTER SYSTEM SET default_table_store_format = 'column';
ALTER SYSTEM SET _rowsets_max_rows = 256;
```
```
* **Example (Oracle tenant):**
```sql
ALTER SYSTEM SET default_table_store_format = 'column';
```
3. Configure tenant-level variables in a user tenant.
```
3. Configure tenant-level variables in a user tenant.
* **Example (MySQL tenant):**
SET GLOBAL ob_trx_timeout = 604800000000;
SET GLOBAL parallel_degree_policy = AUTO;
SET GLOBAL parallel_min_scan_time_threshold = 10;
SET GLOBAL ob_sql_work_area_percentage = 30;
SET GLOBAL collation_server = utf8mb4_bin;
SET GLOBAL collation_connection = utf8mb4_bin;
```
* **Example (Oracle tenant):**
```sql
SET GLOBAL ob_query_timeout = 604800000000;
SET GLOBAL ob_trx_timeout = 604800000000;
SET GLOBAL parallel_degree_policy = AUTO;
SET GLOBAL parallel_min_scan_time_threshold = 10;
SET GLOBAL ob_sql_work_area_percentage = 30;
```
## htap
No configurations are recommended for variables in this scenario.
**The following table describes the parameters and their recommended configurations.**
No configurations are recommended for variables in this scenario.
|------------|----------|------------|--------------------------|----------|
| [large_query_threshold](../700.reference/800.configuration-items-and-system-variables/100.system-configuration-items/300.cluster-level-configuration-items/11100.large_query_threshold.md) | 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</br>Set this variable to a value that can ensure quick execution of AP queries for HTAP workloads. | cluster |
| [enable_syslog_recycle](../700.reference/800.configuration-items-and-system-variables/100.system-configuration-items/300.cluster-level-configuration-items/9000.enable_syslog_recycle.md) | Specifies whether to enable recycling for system logs. | False | True</br>This value specifies to enable automatic system log recycling, so as to prevent log files from occupying disk space. | cluster |
| [max_syslog_file_count](../700.reference/800.configuration-items-and-system-variables/100.system-configuration-items/300.cluster-level-configuration-items/13300.max_syslog_file_count.md) | The maximum number of log files that can be retained. | 0 | 300</br>Set the parameter to an appropriate value when `enable_syslog_recycle` is set to `True`. | cluster |
| _rowsets_max_rows | The maximum number of rows that can be processed in a vectorized execution by the SQL engine. | 256 | 32</br>This value is optimal for classic HTAP workloads. | tenant |
| [log_transport_compress_all](../700.reference/800.configuration-items-and-system-variables/100.system-configuration-items/400.tenant-level-configuration-items/4900.log_transport_compress_all.md) | Specifies whether to compress logs for transmission. | False | True</br>RPC compression can reduce the CPU overhead when the bandwidth resources are limited. | tenant |
### htap
```sql
### htap
1. Configure cluster-level parameters in the sys tenant.
```sql
ALTER SYSTEM SET large_query_threshold = '600s';
ALTER SYSTEM SET enable_syslog_recycle = True;
ALTER SYSTEM SET max_syslog_file_count = 300;
```
2. Configure tenant-level parameters in a user tenant.
* **Example (MySQL tenant):**
```sql
ALTER SYSTEM SET _rowsets_max_rows = 32;
ALTER SYSTEM SET log_transport_compress_all = True;
```
* **Example (Oracle tenant):**
```sql
ALTER SYSTEM SET "_rowsets_max_rows" = 32;
ALTER SYSTEM SET log_transport_compress_all = True;
No configurations are recommended for variables in this scenario.
No configurations are recommended for variables in this scenario.
The following table describes the parameter and its recommended configuration. | Parameter | Description | Default value | Recommended value | Level | |------------|----------|------------|------------------------|----------| | 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 in key-value mode. | cluster |
Example
Configure cluster-level parameters in the sys tenant.
### Example
## View parameters and variables
```sql
ALTER SYSTEM SET large_query_threshold = '600s';
ALTER SYSTEM SET enable_record_trace_log = False;
ALTER SYSTEM SET enable_syslog_recycle = True;
ALTER SYSTEM SET max_syslog_file_count = 300;
2. Configure tenant-level parameters in a user tenant.
* **Example (MySQL tenant):**
```sql
ALTER SYSTEM SET log_transport_compress_all = True;
* **Example (Oracle tenant):**
```sql
ALTER SYSTEM SET log_transport_compress_all = True;
View parameters and variables
You can query a parameter by using the
SHOW PARAMETERS LIKE 'parameter_name’;statement.Here is an example:
SHOW PARAMETERS LIKE 'enable_syslog_recycle';You can query a variable by using the following statements.
MySQL mode: Use the
SHOW [GLOBAL] VARIABLES LIKE 'variables_name';statement for queries.Here are some examples:
SHOW VARIABLES LIKE 'ob_query_timeout';SHOW GLOBAL VARIABLES LIKE 'ob_query_timeout';Oracle mode: Use the
SYS.TENANT_VIRTUAL_SESSION_VARIABLEorSYS.TENANT_VIRTUAL_GLOBAL_VARIABLEview for queries.Here are some examples:
SELECT * FROM SYS.TENANT_VIRTUAL_SESSION_VARIABLE WHERE VARIABLE_NAME = 'ob_query_timeout';SELECT * FROM SYS.TENANT_VIRTUAL_GLOBAL_VARIABLE WHERE VARIABLE_NAME = 'ob_query_timeout';
References
- For more information about parameters and variables, see Overview.
- For more information about how to set parameters, see Set parameters.
- For more information about how to set variables, see Set variables.