GBase 8a Database Data Loading and Tuning (Part II)
Load Balancing Configuration
GBase 8a supports load balancing, automatically selecting optimal loading nodes based on the number of tasks. The following parameters configure load balancing for loading tasks to enhance loading performance:
gcluster_loader_max_data_processors
Description: The number of loading nodes in the cluster, equivalent to the loading SQL parameter MAX_DATA_PROCESSORS.
Value range: 1-300
Default value: 16
Tuning value: 4-6 recommended.
gcluster_load_rebalance_seed
Description: Controls load balancing for loading tasks. The selection method of loading nodes in the cluster can be fine-tuned with this parameter.
Default value: 5.
Tuning value: Loading tasks randomly select several data nodes with the least number of tasks as data loading nodes. In scenarios with high loading concurrency, this value can be increased appropriately to expand the number of data nodes eligible as loading nodes.
Example: In a cluster with 12 data nodes, when MAX_DATA_PROCESSORS=4, a loading task randomly selects no more than four data nodes as loading nodes.
If gcluster_load_rebalance_seed is set to 5, and the minimum task count among all data nodes in the cluster is 20, then all data nodes with a task count <=25 are eligible for random selection as loading nodes for this task.
However, if the number of nodes with task count <=25 is fewer than four, only those eligible nodes are used as loading nodes. Therefore, you can increase the rebalance_seed parameter to broaden the range of candidate loading nodes, but generally no more than 10.
Load Status Monitoring
GBase 8a provides system tables and logs to view loading status and results, supporting load process monitoring, result querying, and error data tracing.
Viewing Load Status Information:
After a loading task starts, you can view its status information via SQL. The status information table information_schema.load_status records the status of all running loading tasks.
Loading Results:
The result information of a loading task is stored in the load_result.log file and can be queried through the information_schema.load_result system view.
LOAD_RESULT table: records loading information for the current coordinator node.
CLUSTER_LOAD_RESULT table: records loading information for all coordinator nodes.
Note: Queries on tables in the information_schema database consume memory. The load_result table is not shared between sessions; during concurrent queries, each session reads its own copy of load_result into its session memory. Proper use of information_schema table queries is required during load design and operation, and obsolete logs should be promptly cleaned.
Loading API Development
The GBase 8a cluster loads data by executing SQL statements via the gccli tool, JDBC/.NET development interfaces. The provided APIs include:
1. JDBC Interface
executeUpdate(loadSql) -- Execute the loading statement
stmt.getSkippedLines() -- Get the number of skipped error data rows
stmt.getLoadTaskID() -- Get the loading task ID
2. ADO.NET Interface
cmd.ExecuteNonQuery() -- Execute the loading SQL
cmd.RecordsSkipped -- Number of skipped error data rows
cmd.RecordsTaskID -- Loading task ID
3. ODBC Interface
Provides SQLROWCOUNT(hstmt, &rowCount) to get the number of successfully loaded rows.
SQL_RES_GBASE_LOAD_TASK_ID in SQLGetStmtAttr() retrieves the task ID.
SQL_RES_LOAD_TASK_SKIPPED_LINES retrieves the number of skipped rows.
4. Python API Interface
Provides the getInfo interface to retrieve three pieces of information: successful rows, skipped rows, and task ID.
Other Loading Issues
Data Loading File Read/Write Consistency
When setting up a data file server using software such as FreeNAS, it is recommended to use a single file access interface. Avoid using both NFS and FTP interfaces for the same data file, as this can easily lead to read/write inconsistency.
Loading SQL Parameter [MAX_BAD_RECORDS number]
Description: Sets the maximum number of error data rows.
Value range: 0 to 4294967295, where 0 means the loading task exits with an error and automatically rolls back as soon as any error data occurs.
Tuning value: All cluster nodes count loading errors independently. If any node reaches this limit during loading, the loading tasks on all nodes are terminated. Before the cluster loading is committed, the total number of errors is checked against the limit; if exceeded, the commit is abandoned and the task exits with an error.
Optimization for Loading a Large Number of Small Files (Version 862.33R39, 953 and Above)
Files smaller than 10 MB are generally considered small, while files larger than 1 GB are large. With the same total data volume, the more files there are, the greater the performance improvement. For files of the same size, the more files there are, the greater the improvement.
Network connections are reused for the same data source.
At the cluster level, files in the same directory are converted into file wildcards to batch obtain file sizes, and network connections are reused for files in different directories.