GBase 8a Implementation Guide: Parameter Optimization
Published: 2024-06-28 03:11:07

## 1. Overview

Optimizing the configuration of GBase 8a clusters varies across different application scenarios. A single set of fixed parameters cannot meet all situations. This chapter provides general principles for parameter tuning based on accumulated optimization experiences in various projects and business scenarios.

## 2. Thread Pool and Parallelism Parameters

### 2.1. Increasing Thread Stack Size to Avoid Crashes

**thread_stack**

When dealing with large SQL statements, a small thread stack (`thread_stack`) can cause stack overflow and crashes. It's necessary to configure the thread stack size for both `gcluster` and `gnode`:

- Recommended `gcluster` thread_stack: 4M (default is 4M in version 953)
- Recommended `gnode` thread_stack: 512K (default is 256K). If stack overflow continues, increase this parameter further.

In the configuration files for `gcluster` and `gnode`, add `thread_stack = 524288` and restart the services after configuration.

### 2.2. Common Parallelism Parameter Adjustments to Enhance Performance

**gcluser_max_thread_in_pool**

This parameter defines the number of connection threads in the global connection thread pool of the cluster layer connecting nodes (`gnode`). The default value is 600.

Generally, this default value suffices as the global thread pool primarily uses asynchronous API logic. However, for larger clusters with high SQL concurrency, if the thread pool's thread count is frequently exhausted, adjust `gcluser_max_thread_in_pool`. It’s advisable not to exceed 2000, as too large a value may degrade performance.

**gbase_parallel_degree**

This parameter configures the parallelism level for a single SQL execution. The default value is 0, recommended to set between 4 and 8.

- A value of 0 uses half the CPU cores' count for parallelism of a single SQL task.
- If the value exceeds the thread pool count, it uses the thread pool count.

For example, with `gbase_parallel_degree=0` and 32 CPU cores, the parallelism for a single SQL task would be 16.

**gbase_loader_parallel_degree**

This parameter sets the number of threads for each load task on `gnode`. The default is 0, using half the CPU cores' count. Recommended value is between 4 and 8.

- A value of 0 uses half the CPU cores' count.
- If the value exceeds the thread pool count, it uses the thread pool count.

**gbase_parallel_max_thread_in_pool**

This parameter specifies the total number of threads in the thread pool, defaulting to twice the number of CPU cores. When deploying a single `gnode` node per server, the recommended value is 4-8 times the number of CPU cores.

Each task fetches threads from the pool for execution. If the pool lacks sufficient idle threads, new threads are created for serial execution.

**thread_pool_size**

This parameter indicates the number of thread pools for receiving client connections, impacting throughput.

### 2.3. Controlling the Number of SQL Tasks Issued by `gcluster`

**gcluster_serial_exec_query**

The `gcluster_serial_exec_query` parameter, defaulting to 0, specifies the number of SQL tasks `gcluster` can issue when `gcluster_enable_serial_load` is enabled. It primarily controls the issuance of `select` and `insert select` tasks. Resource management also controls the issuance of SQL tasks.

**gcluster_enable_serial_load**

The `gcluster_enable_serial_load` parameter is a switch that, when enabled (set to 1), controls the number of SQL tasks issued by `gcluster`. When enabled, `gcluster_serial_exec_query` takes effect. If disabled (default value 0), it has no control.

## 3. Load Parameters

### 3.1. Parameters to Enhance Loading Performance

**gcluster_loader_max_data_processors**

This parameter defines the maximum number of loading machines for a single data loading task. The default value is 16, with a recommended range of 4-8.

When using SFTP for loading, pay attention to SSH connection limits. Excessive loading machine connections might exceed the allowed maximum connections, causing load failures. Adjust SSHD configuration files to modify `maxstartup`, `maxsession`, and other parameters.

### 3.2. Load Task Load Balancing Settings

**gcluster_load_rebalance_seed**

This parameter specifies the maximum difference in the number of loading tasks on different data nodes before initiating node load task balancing. The default value is 5. For better balance, set it to 2 or 1.

For instance, if five concurrent load tasks all select the 10th node as the loading machine, this node's task count is 5, while some nodes might have 0 tasks, resulting in a maximum task count difference of 5. When the 6th load task is issued, nodes with a task difference less than 5 are selected as loading machines.

### 3.3. Setting the Maximum Length of a Single Load Line

**gbase_loader_max_line_length**

This parameter specifies the maximum allowed length of a single line in the data loading file, defaulting to 4M.

GBase 8a can load data lines up to 4M. If any line exceeds this size, the load task fails with the error “Line length xxxx is more than gbase_loader_max_line_length(4194304)”. Adjust this parameter to a larger value to succeed in loading tasks, skipping lines exceeding 4M as error data.

### 3.4. Setting Load Read Timeout

**gbase_loader_read_timeout**

This parameter specifies the timeout for reading FTP/HTTP/SFTP files. If filling a data block (8M) exceeds this value, the load task stops with an error. A value of 0 means no timeout. The default is 300 seconds.

In poor network environments, increase this parameter to avoid read operation timeout errors.

## 4. Data Transfer Parameters

### 4.1. Adjusting Result Set Transfer Parameters to Avoid Write File Failures

**gcluster_send_client_data_timeout**

This parameter specifies the timeout for the cluster to send result set data to the client. The range is [0,100000] seconds, with a default of 30 seconds. A value of 0 means no timeout, with infinite waiting until success or failure.

In poor network conditions or when fetching result sets through JDBC with additional processing logic, increasing this parameter can prevent connection failures (error “com.gbase.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure”).

### 4.2. Setting for Large Data Inserts with `insert values`

**max_allowed_packet**

For large data inserts, such as inserting tens of thousands of rows at once, increase the `max_allowed_packet` parameter.

Configure it using `set global max_allowed_packet = 134217728`.

**Note:** The `max_allowed_packet` parameter must be written under `[gbased]` and above `[gbasedump]` in the configuration file.

### 4.3. Inter-node Data Transfer Parameters

**_gbase_sis_ctq_threads**

This parameter specifies the number of threads for data processing on the receiving end during `insert select` or load operations. The default value is 0, using the number of CPU cores. If insufficient, temporary threads are created. Use `show engine express status` to view node connections, session status, and task sending and receiving situations.

The `_gbase_sis_ctq_threads` parameter sets the number of CTQs (CommonTaskQueue). Each CTQ corresponds to an executor, with the default being twice the number of CPU cores. A recommended setting is 512.

In large-scale cluster concurrent scenarios, insufficient CTQs can lead to performance issues due to repeated CTQ checks and temporary executor creation.

**_gbase_rep_receive_buffer_size**

This parameter specifies the maximum waiting data size for node-to-node data transfers during `insert` and load operations. `_gbase_gns_queue_size` indicates the message queue length for each sending session. Increase this parameter if GNS data transfer performance issues occur.

## 5. Parameters for Troubleshooting

### 5.1. Intermediate Temporary Result Retention

**gcluster_executor_debug**

When enabled, this parameter retains temporary tables during SQL execution (located under the `gctmpdb` database). This is used for troubleshooting and analysis. The default is disabled.

### 5.2. Temporary File Storage Path Configuration

**tmpdir**

This parameter specifies the storage path for temporary files, defaulting to `/tmp`. Adjust it to an independent directory under the installation path, such as `/opt/tmp_gcluster` and `/opt/tmp_gnode`.

**Note:** Ensure the modified `tmpdir` directory exists and has read/write permissions for the DBA user `gbase`. Avoid placing it under `$GCLUSTER_BASE` or `$GBASE_BASE` directories, as it can cause node replacement failures. Before node replacement, create the `tmpdir` directory on the new machine and grant read/write permissions to the `gbase` user.