GBase 8a Implementation Guide: Parameter Optimization (2)
Published: 2024-07-01 02:38:08

## 1. SQL Execution Parameters

### 1.1 Insert Value Data Distribution Parameter

**`gcluster_random_insert`**

This parameter controls how data is distributed across nodes when executing `insert value` on a randomly distributed table. The default value is 0, and the recommended configuration is 1.

- **0**: All `insert value` data is inserted into a single node (if the executing node is a composite node, data is inserted there; otherwise, it is inserted into a random node).
- **1**: `insert value` data is evenly distributed across all nodes randomly.

### 1.2 Parameter for Supporting `insert into select from dual`

**`t_gcluster_use_new_dual`**

This parameter controls whether GCluster uses the new implementation of the dual table, which supports `insert into ... select ... from dual`.

- **Range**: 0, 1
- **Default**: 0
- **0**: Uses the old implementation, does not support `insert into ... select ... from dual`.
- **1**: Uses the new implementation, supports `insert into ... select ... from dual`.
- **Scope**: session, global

### 1.3 Data Redistribution Parameter for `group by`

**`t_gcluster_hash_redistribute_groupby_on_multiple_expression`**

This parameter enables hash redistribution for `group by` operations across all columns. The default value is 0 (disabled). It can be optimized for SQL where the first `group by` field is a constant or has few distinct values.

### 1.4 Join/Materialized Result Set Size Parameter

**`_gbase_result_threshold`**

This parameter limits the size of JOIN result sets and materialized result sets. It needs to be configured in both GCluster and GNode. The default value is large, at 137,438,953,472. It is recommended to set it to twice the number of rows in the largest table. This helps avoid Cartesian products by erroring out if the resulting row count exceeds this value.

### 1.5 Parallel Materialization Threshold for Result Sets

**`gbase_parallel_threshold`**

This parameter defines the threshold for parallel materialization of result sets. When the result set row count is greater than or equal to this value, multiple threads are used for parallel materialization; otherwise, it is done serially. The default value is 10,000. Adjusting this parameter can optimize performance for serial materialization stages that are time-consuming.

### 1.6 Allow Binary/Varbinary Column Creation

**`gcluster_support_binary`**

The default value is 1, allowing the creation of binary/varbinary columns. When set to 0, binary/varbinary columns are not allowed, and varchar type must be used. Adjust based on business needs.

### 1.7 Parameter for Table and Column Names with Chinese Characters

**`gcluster_extend_ident`**

This parameter controls whether table and column names can include Chinese characters and special characters. The default value is 0 (disabled). It is generally not recommended to enable this parameter unless necessary.

### 1.8 Optimization Parameter for `group by` with Window Functions

**`t_gcluster_group_by_ext_optimization`**

When enabled, this parameter optimizes `group by rollup/cube/grouping sets` by converting them into `union all` executions. The default value is 0 (disabled), and the recommended value is 1. Note that this optimization does not work if the grouping column in the projection is a function. 

Example SQL rewrite to bypass the current optimization limitation:
```sql
SELECT func(a), b, COUNT(*) FROM t GROUP BY rollup(a, b);
-- Change to
SELECT func(a), cnt FROM (SELECT a, b, COUNT(*) AS cnt FROM t GROUP BY rollup(a, b)) tmp;
```

### 1.9 One-pass Hash Group Optimization

**`_gbase_one_pass_hash_group`**

This optimization is suitable for cases where the source table has a large number of rows relative to the group buffer, and the distinct values in the `group by` columns are numerous. There are three partitioning methods: RR, original hash, and one-pass hash, with the evaluation criteria as follows:
- **DistinctRatio < 10**: Uses RR partitioning (requires two aggregations).
- **Otherwise**: Uses hash partitioning. If the group buffer can hold 50% of the source data, original hash partitioning is used; otherwise, one-pass hash partitioning is used.

**Note**: The algorithm might not be optimal due to inaccurate sampling results or when only considering data volume without data characteristics.

### 1.10 Recursive Call Depth for Stored Procedures

**`max_sp_recursion_depth`**

This parameter specifies the maximum depth for recursive calls in stored procedures. The range is [0-255], with a default value of 0. Adjust this parameter based on the need for recursive calls in stored procedures. Increasing the value may also require increasing the `thread_stack` parameter in GCluster.

### 1.11 CTE Support Parameter

**`t_gcluster_support_cte`**

This parameter controls whether the common table expression (CTE) syntax (`with as`) is supported. It is a session-level parameter, with a default value of 0 (disabled) and 1 enabling CTE syntax support.

### 1.12 Recursive Query Parameter for `connect by start with`

**`_gbase_connect_by_support_table_with_deleted_records`**

This parameter controls whether `connect by start with` recursive queries can be executed on tables after data deletion. The default value is 0 (OFF). Enabling this parameter allows such queries even after deletions.

### 1.13 Recursion Depth Limit for `or` Operator in Correlated Subqueries

**`_gbase_or_recursion_depth`**

This parameter limits the maximum depth of nested conditions with the `or` operator in correlated subqueries. The default value is 10, and exceeding this value results in an error. The parameter is session-level in GNode.

### 1.14 Distinct Row Count Limit for `in` Subquery Results

**`_gbase_in_subquery_result_threshold`**

This parameter limits the distinct row count for `in` subquery results. The range is [0-100 million], with a default value of 10 million. Adjust based on business scenarios.

## 2. dblink Parameters

### 2.1 Retaining Intermediate Temporary Results

**`gcluster_dblink_direct_data_exchange`**

This parameter is related to transparent gateways and controls how data is transferred between two GBase 8a clusters during `insert select` operations. The default value is 1, using `select into server` for cross-cluster data distribution. When set to 0, the `select` results are converted into `insert values` statements.

**Note**: For significantly different GBase 8a cluster versions, set `gcluster_dblink_direct_data_exchange` to 0 for compatibility, despite the reduced performance.

### 2.2 Controlling Table Generation Method with dblink

**`t_gcluster_dblink_generate_interim_table_policy`**

This parameter controls how interim tables are generated during table pulls with dblink. It is a global and session-level parameter.

- **Range**: 0, 1
- **Default**: 1
- **0**: Uses automatic evaluation based on the data type of the projection expression results.
- **1**: Requests the gateway to use `create ... select ... limit 0` to determine the interim table structure, resulting in more accurate column data type evaluation.