DEV Community: Michael

GBase 8a Cluster Installation in Practice: From Environment Setup to Health Checks

Michael — Tue, 23 Jun 2026 15:44:00 +0000

The success of a GBase 8a cluster installation often hinges not on the install commands themselves, but on the pre‑installation environment preparation and post‑installation validation. This guide focuses on critical prerequisites — networking, SSH, system limits, and firewall settings — and walks through a verified workflow for verifying cluster state and distribution configuration.

1. Node Planning and Component Roles

A GBase 8a cluster consists of three component types: gcware (management nodes, 3 or 5 recommended), gcluster (coordinators), and gnode (data nodes). Plan the roles of each node before starting. Here is a sample 3‑node layout:

198.51.100.21  management + coordinator + data node
198.51.100.22  management + coordinator + data node
198.51.100.23  management + coordinator + data node

2. System Prerequisites to Address Before Installation

Nodes must use static IPs, have full network connectivity between them, and have hostname resolution properly configured.

Network and SSH Checks

# Node connectivity
ping 198.51.100.22
ping 198.51.100.23

# SSH connectivity
ssh root@198.51.100.22
ssh root@198.51.100.23

Firewall and SELinux Checks

systemctl status firewalld
sestatus

3. Handling Non‑Default SSH Ports

If SSH is not running on port 22, specify the custom port either through user‑level SSH configuration or the installation options file.

Option A: User‑level SSH config

cat ~/.ssh/config
Host 198.51.100.22 198.51.100.23
    Port 22022

Option B: Install options file

# install.options
sshPort = 22022

4. Adjust ulimit and systemd Limits Early

Insufficient file handles or process limits will cause instability under concurrency and batch workloads. Address this across systemd, profile, and limits simultaneously.

# /etc/systemd/system.conf
DefaultLimitNOFILE=655350
DefaultLimitNPROC=655350
systemctl daemon-reexec
systemctl restart sshd

# Also update /etc/profile and /etc/security/limits.conf with appropriate nofile settings

5. Key Installation Steps

Create the operating system user and directories

useradd gbaseadm
passwd gbaseadm
mkdir -p /data/gbase8a
chown -R gbaseadm:gbaseadm /data/gbase8a
chown gbaseadm:gbaseadm /tmp

Extract the package and run the environment setup script

cd /data
tar xfj GBase8a_MPP_Cluster-NoLicense-FREE-9.5.3-demo-redhat7-x86_64.tar.bz2
python SetSysEnv.py --dbaUser=gbaseadm --installPrefix=/data/gbase8a --cgroup

Write the installation configuration file install.options, specifying the install directory, coordinator hosts, data hosts, management hosts, user credentials, and SSH port.
Run the silent installation

./gcinstall.py --silent=install.options

6. Validate Cluster Health Immediately After Installation

A completed install script does not guarantee a healthy cluster. Always run gcadmin to verify that the cluster state is ACTIVE and that all gcware, coordinator, and data node roles show OPEN.

7. Configure and Verify Distribution Settings

Prepare a distribution XML file, apply it with gcadmin distribution, and inspect the result with gcadmin showdistribution node. This step directly determines how data is placed across nodes and how the load is balanced.

8. Parameter Tuning Recommendations

Avoid changing many parameters at once. Prioritise based on symptom category: for connection and timeout issues, check max_connections and connect_timeout first; for concurrency and thread pool pressure, look at gbase_parallel_degree; for loading bottlenecks, examine gcluster_loader_max_data_processors.

A smooth GBase 8a installation relies on getting the basics right before running any installer. When the network, SSH, system limits, and cluster health checks are all solid, the rest of the gbase database operations become far more predictable.

GBase 8a Performance Troubleshooting and Stability Governance: From Slow Queries to Primary-Replica Consistency

Michael — Tue, 23 Jun 2026 14:39:00 +0000

Performance and stability issues in a GBase 8a cluster rarely exist in isolation. A query that suddenly slows down, wildly uneven node execution times, unstable results, or even local replica inconsistency can stem from node‑level execution differences, data skew, intermediate result bloat, underlying environment anomalies, or primary‑replica consistency problems. This article integrates node‑level slow‑query diagnosis, data skew detection, intermediate result control, log and audit correlation, and primary‑replica consistency handling into a systematic troubleshooting workflow for your gbase database.

1. Slow Query Diagnosis: Pinpoint the Node First

Before rewriting SQL, determine whether the slowdown is cluster‑wide or isolated to a few nodes. Enable recording of queries that exceed a threshold:

SET GLOBAL gcluster_dql_statistic_threshold = 3000; -- record queries over 3 seconds

Retrieve recently recorded slow queries:

SELECT * FROM gclusterdb.sys_sqls ORDER BY create_time DESC LIMIT 20;

Drill into per‑node execution times for a specific SQL:

SELECT * FROM gclusterdb.sys_sql_elapsepernode WHERE sql_id = 'actual_sql_id';

If only one or two nodes lag significantly while others are fast, investigate that node's resources, data distribution, or local failure. If all nodes are uniformly slow, examine the SQL logic, intermediate result size, or global parameter settings.

2. Uneven Node Load: Suspect Data Skew First

When node execution times differ drastically, data skew is usually the prime suspect. A query like:

SELECT region_code, COUNT(DISTINCT user_id) FROM fact_order GROUP BY region_code;

can cause a few nodes to shoulder most of the work if region_code is heavily imbalanced. Diagnosis order: confirm skew → determine if the table's distribution key is flawed or dynamic redistribution is at fault → then decide whether to fix the model, rewrite the SQL, or tune parameters. Parameter tuning alone rarely cures a bad distribution key.

3. Intermediate Result Bloat Can Hurt More Than Scanning

A slow query's bottleneck is often not reading data, but the sheer size of intermediate results. SELECT * quickly inflates result sets, making subsequent sorting, aggregation, and network exchange far heavier. The safer approach is to select only necessary columns, push filters as early as possible, and reduce data volume before large joins. When needed, consult express.log to see which execution stages are truly expensive.

4. Correlate Logs and Audit Trails for Faster Diagnosis

Node‑level statistics tell you which SQL is slow and where, but logs explain why it's slow and what anomalies accompanied it. Focus on express.log (engine exceptions), system.log (crash stack traces), and gcware.log (node state and replica operations). Audit logs reveal the history of bulk operations, DDL changes, and parameter modifications — correlate them with performance dips to quickly narrow the cause.

5. Primary‑Replica Inconsistency: Stop Treating It as a Tuning Problem

When results become unstable, a node behaves abnormally after recovery, or DML succeeds but subsequent reads are inconsistent, shift the investigation to primary‑replica consistency. Common causes include inconsistent local parameters, sudden power loss, RAID controller/driver issues, VM abnormal exit, or manual mistakes. GBase 8a provides the gcluster_suffix_consistency_resolve parameter (default 0; set to 1 to attempt automatic resolution). It can detect and repair row‑count mismatches, schema inconsistencies, and SCN discrepancies, provided the cluster has at least three host nodes.

6. Recommended Troubleshooting Sequence

Is the slowdown global or per‑node? Use node‑level statistics to locate tail nodes.
Is data skew present? Check distribution key design and dynamic redistribution.
Examine intermediate results and resource consumption. Slim down columns and watch execution logs.
Correlate audit and system logs. Look for recent operational anomalies or resource conflicts.
Rule out primary‑replica consistency issues. Prioritise this when instability follows node recovery or environment events.

Layering the investigation before deciding whether to rewrite SQL, tune parameters, scan logs, or repair consistency is far more efficient than jumping to conclusions in a gbase database.

Making GBase 8a Backup and Recovery Reliable: Full Backups, Incrementals, and Recovery Drills

Michael — Tue, 23 Jun 2026 13:34:00 +0000

The gcrcman tool in GBase 8a supports backup and recovery at the cluster, database, table, and batch-table levels. In a production gbase database, knowing the commands isn't enough — you need a sound backup strategy, verified recovery prerequisites, and the right granularity for the incident you're facing. This guide covers full and incremental backups, table‑level recovery, batch backup limits, and regular recovery drills.

1. Layered Backup Strategy

Don't back up everything the same way. Design your strategy around business importance and recovery objectives:

Core databases: Periodic full backups (level 0) as the ultimate safety net.
Frequently changing objects: Incremental backups (level 1) to refine recovery points without the cost of daily fulls.
High‑risk tables: Dedicated table‑level or batch backups to recover from accidental drops or bad DELETE/UPDATE — by far the most common incidents.

2. Prerequisites Matter More Than the Commands

Before running gcrcman.py, ensure:

You operate as the dbauser specified during installation.
Execution happens on a coordinator node.
All nodes are network‑reachable, and the backup directory exists on every node with read/write permissions for dbauser.
The backup path is never under $GCLUSTER_BASE, $GBASE_BASE, or $GCWARE_BASE.
The cluster topology (management nodes, data nodes, distribution info) at recovery time matches the topology at backup time.

3. Essential Commands

# View backup history
python $GCLUSTER_BASE/server/bin/gcrcman.py -d /backup/cluster_bak -e "show backup"

# Cluster‑level full backup
python $GCLUSTER_BASE/server/bin/gcrcman.py -d /backup/cluster_bak -e "backup level 0"

# Database‑level full backup
python $GCLUSTER_BASE/server/bin/gcrcman.py -d /backup/cluster_bak -e "backup database appdb level 0"

# Table‑level full backup
python $GCLUSTER_BASE/server/bin/gcrcman.py -d /backup/cluster_bak -e "backup table appdb.fact_order level 0"

# Table‑level force recovery (overwrites existing table)
python $GCLUSTER_BASE/server/bin/gcrcman.py -d /backup/cluster_bak -e "recover force table appdb.fact_order"

# Refresh table metadata after recovery
refresh table appdb.fact_order;

Key parameters: -r sets parallelism (default 4), -t sets the transaction wait timeout (default 300 s), -C enables backup data verification.

4. Table‑Level Recovery Is the Most Practical

Dropping a table by mistake is the most frequent production accident. After performing a table‑level recovery, always run refresh table to re‑register the table object in the cluster. Verify the recovered row count matches expectations.

5. Batch Backup Limitations

To back up a set of tables, list them in a file (format database.table):

appdb.fact_order
appdb.fact_trade_detail

python $GCLUSTER_BASE/server/bin/gcrcman.py -d /backup/cluster_bak -e "backup tables table.list level 0"

Critical rule: Within the same backup cycle, the table.list file must be identical for the full backup and all subsequent incremental backups. You cannot add new tables to an incremental run — doing so corrupts the cycle logic.

6. Recovery Drills Validate the Whole Effort

Untested backups are worthless. Establish a regular drill cadence:

Weekly: Restore a few critical tables and verify row counts.
Monthly: Perform a full database recovery exercise.
After any topology change: Re‑validate the entire recovery chain.
During every drill, check: row counts, business query correctness, view and index integrity, and whether recovery time fits the acceptable window.

A reliable backup and recovery practice in a gbase database goes beyond running gcrcman.py. It's a closed loop of strategy, permissions, topology awareness, and continuous verification.

GBase 8a Operations Inspection and Alerting: Don't Wait for a Failure to Check the Logs

Michael — Tue, 23 Jun 2026 12:29:29 +0000

Keeping a gbase database cluster running smoothly in production isn't just about fixing problems — it's about having a solid routine for inspection, monitoring, slow‑query analysis, audit log usage, and tiered alerting. This article covers these five areas with practical, actionable steps.

1. Inspections Go Beyond Cluster Status — Cover Three Layers

Effective daily inspections span three layers: the cluster layer (node status, service processes), the database layer (slow SQL, connection counts, session states), and the system layer (CPU, memory, disk, I/O). Relying solely on gcadmin to check that the cluster is ACTIVE won't tell you why queries suddenly slowed or why one node consistently lags.

Essential daily inspection commands:

gcadmin
ps -ef | egrep 'gcware|gcluster|gnode'
tail -100 /opt/gbase/gcluster/log/system.log
tail -100 /opt/gbase/gcware/log/gcware.log

2. Prioritise Core Monitoring Metrics — Avoid Dashboard Clutter

Monitor the following five categories first, before expanding to a full dashboard:

Category	Typical Metrics
Cluster availability	Node online, cluster ACTIVE
Resource pressure	CPU, memory, disk usage, I/O wait
SQL behaviour	Slow query count, execution duration
Connection status	Connection count, active sessions
Operational trails	Audit logs, backend errors

Start by collecting per‑node CPU/memory/IO, cluster state, critical process liveness, disk usage, slow‑query statistics, and core‑log error counts. These alone often reveal issues before users notice.

3. Slow‑Query Monitoring: Record Them, Then Pinpoint Which Node

In a distributed gbase database, slow queries are often caused by just a few overloaded nodes. Enable slow‑query recording first:

SET GLOBAL gcluster_dql_statistic_threshold = 3000; -- record queries over 3 seconds

Then retrieve the recorded queries:

SELECT * FROM gclusterdb.sys_sqls ORDER BY create_time DESC LIMIT 20;

Capture the data first, observe the patterns, and only then decide whether to adjust parallelism, thread pools, or other parameters — never tune blindly.

4. Include Logs and Audit Trails in Routine Checks

Don't wait for a failure to read logs. Spot‑check for these signals daily: abnormal node states, repeated recovery messages, frequent internal errors, load anomalies, and audit export failures.

grep -i 'error' /opt/gbase/gcluster/log/system.log | tail -50
grep -i 'warn'  /opt/gbase/gcware/log/gcware.log | tail -50

Audit logs are more than a compliance checkbox — they let you trace who did what and when, and can reveal bulk operations that preceded a slowdown. GBase 8a consolidates audit records into the audit_log_express table. Add audit export health, unexpected DDL/DML, and sudden audit volume spikes to your inspection list.

5. Tier Your Alerts to Prevent Fatigue

Group alerts into three severity levels:

P1 – Critical: Node offline, cluster not ACTIVE, key process missing, disk full
P2 – Important: Slow‑query surge, abnormal connection count, audit anomaly, excessive I/O
P3 – Warning: Negative trends, fast disk growth, rising log alert frequency

For disk usage, trigger a P2 warning above 85% and a P1 critical alert above 95%.

6. Recommended Operational Cadence

Daily: gcadmin, check key processes, review system logs, inspect disk space, look for abnormal slow‑query growth.
Weekly: Slow‑query trends, connection count changes, audit log spot‑check, node load balance, backup and data‑load task status.
Monthly: Parameter baseline review, hardware health check, log alert trend analysis, alert threshold adjustments.

A stable gbase database isn't just about what you do when things break — it's about seeing the signals that were there all along. Build the routine, tier the alerts, and you'll catch most problems before they become incidents.

GBase 8a Table Design and Modeling: Choosing Data Types, Partitions, Distribution Keys, and Replicated Tables

Michael — Sun, 21 Jun 2026 15:50:00 +0000

In a distributed analytical gbase database, many performance issues are baked in at the table design stage. Data types, partitioning, distribution keys, and replicated table strategies largely determine query cost down the line. This guide walks through these four core design decisions with practical, implementable advice.

1. Modeling Matters More Than Post‑Hoc Tuning

The GBase 8a community consensus on query optimisation is clear: prioritise business SQL and table structure first, then tune database parameters, and only then add hardware. The way data is organised sets the upper bound for query performance.

Design Area	Common Shortcut	Later Pain	Better Approach
Data types	Store everything as strings	Heavy scans, poor compression, constant casting	Choose types by actual semantics
Partitioning	Skip it initially, add later	Hard to manage, clean, and query large tables	Partition time‑based large tables early
Distribution key	Pick any familiar column	Node skew, slow GROUP/JOIN	Prefer high‑cardinality columns used in frequent JOINs/GROUPs
Replicated tables	Build everything as a distribution table	Extra redistribution on small‑table JOINs	Consider replication for small, frequently‑joined dimension tables

2. Data Types: They Dictate Compression, Scanning, and Computation

The clearer the business semantics, the less you should compromise on types.

Status and type codes: Use TINYINT/SMALLINT/INT, not VARCHAR for enumerated values.
Monetary amounts: Use DECIMAL; avoid FLOAT/DOUBLE precision issues.
Time‑based filter columns: Use DATE/DATETIME/TIMESTAMP; never store dates as VARCHAR.
Distributed sequence numbers: Use BIGINT; INT risks overflow on large tables.

Anti‑pattern vs. correct approach:

-- Anti‑pattern: string‑everything
CREATE TABLE ods_order_raw (
    order_id     VARCHAR(64),
    user_id      VARCHAR(64),
    order_status VARCHAR(20),
    pay_amt      DOUBLE,
    create_time  VARCHAR(19)
);

-- Correct: semantic types
CREATE TABLE ods_order_raw (
    order_id     BIGINT,
    user_id      BIGINT,
    order_status TINYINT,
    pay_amt      DECIMAL(18,2),
    create_time  DATETIME
);

3. Partitioning: Plan for Large Tables from the Start

GBase 8a supports RANGE, LIST, HASH, and KEY partitioning. Total partitions cannot exceed 8,192; production best practice is to keep per‑table partitions under 50. The partition key column cannot be updated.

Tables that benefit from partitioning: daily/monthly fact tables, historical log tables — data with natural time boundaries that need periodic cleanup and range queries. Skip partitioning for small dimension tables and high‑update small tables.

CREATE TABLE dwd_trade_detail (
    trade_id   BIGINT,
    user_id    BIGINT,
    shop_id    BIGINT,
    pay_amt    DECIMAL(18,2),
    trade_date DATE
)
PARTITION BY RANGE(trade_date) (
    PARTITION p202601 VALUES LESS THAN ('2026-02-01'),
    PARTITION p202602 VALUES LESS THAN ('2026-03-01'),
    PARTITION p202603 VALUES LESS THAN ('2026-04-01')
);

Partition pruning is the real payoff: partitioning helps only when queries land on a subset of partitions. Avoid wrapping the partition key in functions (DATE_FORMAT); use direct range filters to let partition pruning work.

4. Hash Distribution Key: The Foundation of Horizontal Data Placement

The distribution key determines how evenly data is spread across nodes and directly impacts whether GROUP BY and JOIN can execute locally. Evaluate in this order: data uniformity → frequent JOIN column → frequent GROUP BY column → still uniform after filtering.

Common mistake: using low‑cardinality columns like province_code as the distribution key, causing severe node skew and forcing extra redistribution during aggregation and JOINs.

5. Replicated Tables: Best for Small Dimension Tables

A replicated table stores a full copy on every gnode, enabling fully local JOINs with fact tables — zero network transfer. Ideal for small, frequently‑read dimension and dictionary tables. Avoid for large fact tables and high‑churn large tables.

CREATE TABLE dim_region (
    region_id   INT,
    region_name VARCHAR(64)
) REPLICATED;

6. Recommended Modeling Sequence

Define types by business semantics — lock down the real meaning of status codes, amounts, times, and primary keys first.
Decide on partitioning — time‑accumulating large tables and log tables are the prime candidates.
Choose the distribution strategy — for distribution tables, prioritise uniformity, then JOIN/GROUP needs; evaluate replication for small dimension tables.
Review expected query patterns — verify that future queries will filter by the partition key and frequently JOIN/GROUP by the chosen distribution key.

In a gbase database, slow queries are often not "discovered" — they are "built in" at the design stage. Getting data types, partitioning, distribution keys, and replication right from the start dramatically reduces the tuning burden later.

Deep Dive into GBase 8a MPP Distributed Query Execution

Michael — Sun, 21 Jun 2026 14:43:00 +0000

How does a SQL statement travel through a GBase 8a cluster — from parsing and plan generation to parallel execution and final aggregation? This article explains the complete execution path, the roles of coordinator and data nodes, and common performance pitfalls in a gbase database.

1. Architecture Recap: Three Roles

GBase 8a MPP Cluster consists of three core process types:

Process	Node Type	Primary Responsibility
gcluster	Coordinator	SQL parsing, plan generation, task distribution, result assembly
gnode	Data Node	Data storage, local scan, partial aggregation, Hash Join
gcware	Cluster Manager	Heartbeat, replica consistency arbitration, failover

Clients communicate only with gcluster. gcluster holds metadata (table definitions, distribution info, replica topology) but stores no user data.

2. Full Lifecycle of a Query

Consider this typical analytical query:

SELECT dept_id, SUM(sale_amount) AS total
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY dept_id
ORDER BY total DESC
LIMIT 100;

Stage 1: Parsing and Semantic Checks (gcluster)

The SQL Parser in gcluster converts the text into an AST and performs semantic validation — verifying that tables and columns exist and that data types are compatible.

Stage 2: Query Plan Generation (gcluster)

The optimizer generates a Distributed Query Plan (DQP) based on metadata. Two core decisions are made:

Pushdown vs. aggregation: Filter conditions like WHERE order_date >= '2024-01-01' are pushed down to each gnode to avoid transferring full datasets. Because dept_id is unlikely to be the distribution key, aggregation requires each gnode to first perform partial aggregation, then redistribute the partial results by dept_id hash before doing final aggregation.
Data redistribution strategy:
- Hash Redistribute: Triggered when the JOIN/GROUP BY column is not the distribution key. Cost: network transfer + shuffle.
- Broadcast: Small tables can be broadcast to all nodes instead of being redistributed.
- No redistribution: Optimal — when the JOIN/GROUP BY column happens to be the distribution key.

Key parameters: gcluster_hash_redistribute_join_optimize and gcluster_hash_redistribute_groupby_optimize control whether small tables are broadcast to avoid unnecessary hash shuffles.

Stage 3: Task Distribution and Parallel Execution (gcluster → gnode)

gcluster splits the DQP into multiple fragments and sends them concurrently to all participating gnodes over internal TCP channels. Each gnode then uses worker threads (controlled by gbase_parallel_degree) to scan its local data segments in parallel.

gcluster
  ├─ Fragment-1 → gnode1 (local scan + partial aggregation)
  ├─ Fragment-1 → gnode2 (local scan + partial aggregation)
  └─ Fragment-1 → gnode3 (local scan + partial aggregation)
         ↓
  [Hash Redistribute by dept_id]
         ↓
  ├─ Fragment-2 → gnode1 (final aggregation)
  ├─ Fragment-2 → gnode2
  └─ Fragment-2 → gnode3
         ↓
  gcluster merges TOP 100

Stage 4: Final Merge and Return to Client (gcluster)

Each gnode streams its fragment result back to gcluster. For ORDER BY ... LIMIT 100, gcluster performs a final merge‑sort to pick the top‑N rows and returns them to the client.

3. Intermediate Tables and Debugging

For complex queries, gnodes create internal temporary tables that are automatically dropped after execution. To keep them for troubleshooting:

SET gcluster_executor_debug = 1;

⚠️ Debug only — never leave this on in production, or intermediate tables will fill the disk.

To see currently executing queries and per‑node timings:

SHOW FULL PROCESSLIST;

-- Requires prior configuration (gcluster_dql_statistic_threshold in milliseconds)
SELECT * FROM gclusterdb.dql_statistic ORDER BY exec_time DESC LIMIT 20;

4. Common Query Performance Pitfalls

Pitfall 1: Cartesian Product Causing Disk Spikes

When a JOIN condition is missing, two large tables produce a Cartesian product that can reach terabytes. Cap intermediate row counts:

# gnode gbase.cnf
_gbase_result_threshold = 1000000000  -- error if >1 billion rows

Pitfall 2: Data Skew Turning One Node into a Bottleneck

GROUP BY on a low‑cardinality column concentrates all data on a few nodes after hash redistribution. Solutions:

Choose a high‑cardinality distribution key
Enable multi‑column hash redistribution for skewed GROUP BYs:

SET _t_gcluster_distinct_multi_redist = 1;
SET _t_gcluster_hash_redistribute_groupby_on_multiple_expression = 1;

Pitfall 3: Small Tables Treated as Distribution Tables During JOINs

The optimizer may hash‑redistribute many small tables, generating excessive network traffic. Build frequently used small tables as replicated tables:

CREATE TABLE dim_region (
    region_id INT,
    region_name VARCHAR(64)
) REPLICATED;

5. Summary

Phase	Process	Key Actions
Parse & Optimize	gcluster	AST creation, DQP planning, redistribution strategy
Local Execution	gnode	Data scan, partial aggregation, Hash Join
Data Shuffle	gnode ↔ gnode	Hash Redistribute / Broadcast
Final Merge	gcluster	Merge‑sort, Top‑N, return to client

Understanding this pipeline is the key to pinpointing bottlenecks in a gbase database: is the redistribution too expensive? Is one gnode scanning too slowly? Or has gcluster become the single‑point merge bottleneck? Use EXPLAIN and dql_statistic system tables for precise diagnosis.

GBase 8a Table Design in Practice: Choosing Distribution Keys, Partitions, and Replicated Tables

Michael — Sun, 21 Jun 2026 14:10:00 +0000

Many performance issues are baked in the moment a table is created. This guide systematically explains table design decisions in GBase 8a: how to pick distribution keys, when to partition, how to use replicated tables, and how to choose the right data types — with anti‑patterns and a complete example.

1. How Data Is Distributed Across Nodes

GBase 8a uses a Shared‑Nothing architecture. Data is horizontally partitioned and spread across gnodes based on the distribution key:

CREATE TABLE orders (
    order_id    BIGINT NOT NULL,
    customer_id INT    NOT NULL,
    dept_id     INT,
    amount      DECIMAL(18,2),
    order_date  DATE
) DISTRIBUTED BY HASH(customer_id);

A hash function maps every row with the same customer_id to the same gnode. If DISTRIBUTED BY is omitted, the first column is used by default — rarely what you want.

2. Core Principles for Choosing a Distribution Key

High cardinality: The more unique values, the more evenly data is spread. user_id or order_id are ideal; gender or province cause severe skew.
The column used in high‑frequency JOINs: If two tables are often joined on the same key, set that key as the distribution key on both sides. The JOIN then runs locally without cross‑node data shuffle, giving the best performance.
Avoid date or time columns: They have limited unique values and are almost never used in JOIN conditions.

3. Partitioning: How It Differs from Distribution

The distribution key decides which node data goes to; partitioning decides how data is organised inside each node. GBase 8a supports Range partitioning:

CREATE TABLE orders (
    order_id   BIGINT,
    order_date DATE,
    amount     DECIMAL(18,2)
) DISTRIBUTED BY HASH(order_id)
PARTITION BY RANGE(order_date) (
    PARTITION p2023 VALUES LESS THAN ('2024-01-01'),
    PARTITION p2024 VALUES LESS THAN ('2025-01-01'),
    PARTITION p2025 VALUES LESS THAN ('2026-01-01'),
    PARTITION pmax  VALUES LESS THAN MAXVALUE
);

Partition pruning: when the query includes a filter on the partition key, only the relevant partitions are scanned. Use partitioning when a single node holds tens of GBs or more, queries frequently filter by time range, or you need fast historical data cleanup (ALTER TABLE DROP PARTITION is orders of magnitude faster than DELETE). Avoid partitioning for tables under 100 million rows, full‑scan workloads, or when the partition count exceeds 1,000 (metadata overhead becomes significant).

4. Replicated Tables: The Best Strategy for Small Dimension Tables

For lookup tables, dictionary tables, and other small, rarely‑updated tables, use replication:

CREATE TABLE dim_product (
    product_id   INT,
    product_name VARCHAR(128),
    category     VARCHAR(64)
) REPLICATED;

A replicated table stores a full copy on every gnode. JOINs between a fact table and a replicated table require zero network transfer — they run entirely locally. Replication is ideal when row count is under 1 million and updates are rare. Between 1–10 million rows with occasional updates, proceed with caution. Beyond 10 million rows or with frequent writes, use a distribution table with a proper key.

5. Data Type Selection

GBase 8a is a columnar store engine. Data types directly affect compression ratio and query performance.

Strings: Store enumerated values as TINYINT/SMALLINT; use VARCHAR only for truly variable‑length descriptions. Low‑cardinality strings compress extremely well.
Numbers: Use INT/BIGINT for integers — never DECIMAL(20,0). Use DECIMAL(18,2) for monetary amounts; never DOUBLE (floating‑point precision issues).
Temporal: Use DATETIME for full timestamps, DATE for date‑only columns. Never store dates as VARCHAR — it prevents partition pruning and date‑function optimisations.

6. Complete Table Design Example

-- Fact table: large, distributed by high‑cardinality customer_id, partitioned by quarter
CREATE TABLE orders (
    order_id     BIGINT      NOT NULL,
    customer_id  INT         NOT NULL,
    product_id   INT         NOT NULL,
    dept_id      SMALLINT    NOT NULL,
    amount       DECIMAL(18,2),
    status       TINYINT     NOT NULL,
    order_date   DATE        NOT NULL,
    create_time  DATETIME
) DISTRIBUTED BY HASH(customer_id)
PARTITION BY RANGE(order_date) (
    PARTITION p2024q1 VALUES LESS THAN ('2024-04-01'),
    PARTITION p2024q2 VALUES LESS THAN ('2024-07-01'),
    PARTITION p2024q3 VALUES LESS THAN ('2024-10-01'),
    PARTITION p2024q4 VALUES LESS THAN ('2025-01-01'),
    PARTITION p2025   VALUES LESS THAN ('2026-01-01'),
    PARTITION pmax    VALUES LESS THAN MAXVALUE
);

-- Dimension table: small, replicated
CREATE TABLE dim_product (
    product_id   INT          NOT NULL,
    product_name VARCHAR(128) NOT NULL,
    category     VARCHAR(64),
    brand        VARCHAR(64)
) REPLICATED;

7. Common Anti‑Patterns

Anti‑Pattern	Consequence	Correct Approach
No distribution key specified	Defaults to first column, often skewed	Explicitly specify `DISTRIBUTED BY HASH(appropriate_column)`
Distribution on low‑cardinality columns	Severe node imbalance	Use high‑cardinality columns
Dimension table as a distribution table	Hash redistribution on every JOIN	Use `REPLICATED`
`VARCHAR(255)` for enumerated values	Poor compression, higher memory	Use `TINYINT`/`SMALLINT`
Excessive partitions (>1,000)	High metadata overhead, slow planning	Partition by quarter or year instead of day

Good table design is the starting point of performance optimisation in a gbase database. Changing a distribution key later requires rebuilding the table — a very expensive operation. During the design phase, answer three questions: what JOIN conditions are used most? Does the query workload have obvious time‑range filters? How large is the table and how frequently is it written? These answers directly determine your distribution key, partitioning strategy, and whether to use replication.

Permission Governance in GBase 8c: Separate Role Boundaries First, Then Assign Privileges

Michael — Sun, 21 Jun 2026 13:29:13 +0000

Chaos in permission management almost always starts with granting privileges directly to users. The foundation of a maintainable gbase database security model is strict separation of Users, Roles, and Privileges — users log in, roles carry permissions, and object privileges are granted only to roles.

1. Core Principle: Users Bind to Roles, Roles Carry Permissions

A typical three‑tier role structure:

Read‑only role: for reports, audits, and read‑only access.
Read‑write role: for routine application reads and writes.
Management role: for object creation and maintenance, never bound directly to application programs.

-- Create roles
CREATE ROLE app_read_role;
CREATE ROLE app_rw_role;
CREATE ROLE app_ddl_role;

-- Create users
CREATE USER app_reader IDENTIFIED BY 'Example#2026';
CREATE USER app_writer IDENTIFIED BY 'Example#2026';
CREATE USER app_owner  IDENTIFIED BY 'Example#2026';

-- Bind users to roles
GRANT app_read_role TO app_reader;
GRANT app_rw_role   TO app_writer;
GRANT app_ddl_role  TO app_owner;

Grant database, schema, and object privileges to the roles, never to individual users:

GRANT CONNECT ON DATABASE bizdb TO app_read_role, app_rw_role, app_ddl_role;
GRANT USAGE ON SCHEMA billing TO app_read_role, app_rw_role, app_ddl_role;

GRANT SELECT ON TABLE billing.settle_result TO app_read_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE billing.settle_result TO app_rw_role;
GRANT CREATE, USAGE ON SCHEMA billing TO app_ddl_role;

When someone changes roles, you only adjust the user‑role binding — no per‑table re‑grant needed.

2. When Troubleshooting, Check the Upper Permission Layers First

Many "missing table permission" errors are actually missing CONNECT or USAGE higher up. Follow this order:

Symptom	Most Likely Missing Privilege
Cannot connect to database	`CONNECT ON DATABASE`
Schema visible but object access fails	`USAGE ON SCHEMA`
Query on a table fails	`SELECT ON TABLE/VIEW`
Write operations fail	`INSERT`/`UPDATE`/`DELETE`, sometimes `SELECT` also required
Calling a function fails	`EXECUTE ON FUNCTION`

3. Use Default Privileges to Set Boundaries for Future Objects

Manual GRANT only affects existing objects. New tables, sequences, and functions won't inherit those grants. ALTER DEFAULT PRIVILEGES defines preset access rules for future objects, preventing midnight alerts caused by forgotten grants.

ALTER DEFAULT PRIVILEGES IN SCHEMA billing
GRANT SELECT ON TABLES TO app_read_role;

ALTER DEFAULT PRIVILEGES IN SCHEMA billing
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_rw_role;

ALTER DEFAULT PRIVILEGES IN SCHEMA billing
GRANT USAGE, SELECT ON SEQUENCES TO app_rw_role;

Apply default privileges early in any schema where objects are continuously created.

4. Separation of Duties for High‑Security Environments

GBase 8c's separation of duties splits traditional superuser power into a System Administrator (SYSADMIN) and a Security Administrator (CREATEROLE + POLADMIN). This prevents a single account from both maintaining the system and having unlimited access to data. It's strongly recommended in finance, government, and telecom environments. Note: when separation of duties is not enabled, the system administrator's effective privileges are broader.

5. Least Privilege by Business Action Chain

Least privilege means "exactly what's needed to perform the task," not "as little as possible."

Report querying: CONNECT + USAGE + SELECT
Business writes: CONNECT + USAGE + SELECT + INSERT + UPDATE + DELETE
Calling functions: add EXECUTE to the above
Creating objects: CREATE ON SCHEMA/DATABASE
Table maintenance: add INDEX, VACUUM, ALTER as needed

6. Connection Entry Is Also a Permission Boundary

Security governance must cover not only object‑level privileges but also who can connect from which IP using which authentication method. Regularly review listen_addresses and pg_hba.conf. Manually editing pg_hba.conf is a high‑risk operation and must follow documented procedures.

7. Recommended Governance Sequence

Separate administrator responsibilities — evaluate separation of duties; at minimum distinguish ops, security, and audit roles.
Design roles by job function, not by individual.
Grant database and schema privileges first, then table/view/function privileges.
Set default privileges so new objects automatically inherit the right rules.
Users only bind to roles — never grant object privileges directly to users.
Unify connection‑level and object‑level governance.

A solid permission design in a gbase database isn't about writing clever GRANT statements — it's about building a role hierarchy that stays clean as teams and objects grow. When the foundation is right, audits are painless, incident boundaries are clear, and new objects land with the correct permissions from day one.

Data Lifecycle Management in GBase 8c: Partitioning, Archiving, and Cleanup

Michael — Sat, 20 Jun 2026 15:39:00 +0000

When a table grows unchecked for a couple of years, historical, log, and hot data mix together, making queries, deletions, and backups increasingly heavy. GBase 8c supports range, interval, list, and hash partitioning, providing an ideal foundation for data lifecycle management. The core is three things: smooth ingestion of new data, low‑risk archiving of old data, and stable cleanup of expired data.

1. Lifecycle Management Means Long‑Term Control

Typical symptoms: a query for the last 7 days scans 3 years of data; deleting history causes heavy transactions and lock contention; archiving relies on slow INSERT INTO archive SELECT ...; statistics drift and execution plans wobble. Lifecycle management turns the migration from hot → warm → cold → deletable data into a predictable, routine operation. Partitioned tables are the natural fit: queries only touch relevant partitions, and maintenance actions are scoped to a single partition rather than the entire table.

2. Time‑Based Partitioning Is the Most Practical Choice

Although GBase 8c offers four partition types, the most natural boundary for lifecycle management is time. Range partitioning works well for data with clear start‑end intervals (monthly tables, billing period tables), while interval partitioning automatically extends partitions as time‑series data grows, saving manual effort.

Choose partition keys that are frequently used in query predicates, have reasonably even distribution, and are not frequently updated. Date‑type columns such as trade_date, log_time are ideal lifecycle boundaries.

3. Start with Monthly Partitions

Slicing by hour or day improves pruning but explodes the number of partition objects. For transaction details, logs, and event streams, monthly partitions typically strike a good balance between management overhead and pruning effectiveness.

Example of monthly range partitioning:

CREATE TABLE acct_trade_detail (
    trade_id        bigint,
    acct_no         varchar2(32),
    trade_time      timestamp,
    trade_date      date,
    trade_amt       numeric(18,2),
    trade_status    varchar2(16),
    channel_code    varchar2(16)
)
PARTITION BY RANGE (trade_date) (
    PARTITION p202601 VALUES LESS THAN ('2026-02-01 00:00:00'),
    PARTITION p202602 VALUES LESS THAN ('2026-03-01 00:00:00'),
    PARTITION p202603 VALUES LESS THAN ('2026-04-01 00:00:00'),
    PARTITION pmax   VALUES LESS THAN (MAXVALUE)
);

If you want automatic extension for continuous growth, use interval partitioning:

CREATE TABLE app_event_log (
    event_id       bigint,
    user_id        bigint,
    event_time     timestamp,
    event_date     date,
    event_type     varchar2(32),
    payload        text
)
PARTITION BY RANGE (event_date) INTERVAL ('1 month') (
    PARTITION p202601 VALUES LESS THAN ('2026-02-01 00:00:00'),
    PARTITION p202602 VALUES LESS THAN ('2026-03-01 00:00:00')
);

4. Maintenance Must Follow Up

The second half of lifecycle management is even more critical: pre‑creating new partitions, archiving old partitions, dropping expired partitions, and then updating statistics and reclaiming space.

Common maintenance commands:

-- Reclaim space and update visibility for a specific partition
VACUUM acct_trade_detail PARTITION (p202601);
ANALYZE acct_trade_detail;
VACUUM ANALYZE acct_trade_detail;

Under the MVCC model, old versions after updates or deletes don't disappear immediately — VACUUM gradually reclaims space and maintains the visibility map.

5. Prefer Partition Drop Over Conditional DELETE

Once a table is partitioned by time, dropping a partition is vastly more efficient than a large‑scale DELETE ... WHERE. It avoids massive transactions, reduces lock contention, and eliminates the need for an immediate, heavy VACUUM. Always confirm retention rules, back up or archive the data, then drop the partition safely.

6. Archiving Is About Isolating Online Workloads

Archiving isn't just copying data out — it separates the online workload from historical queries. Even if historical data is "rarely queried," keeping it in the live main table still impacts statistics, maintenance cost, backup size, and some global operations. Use a three‑tier data model:

Hot data: live main table, high‑frequency reads and writes
Warm data: online archive table or low‑traffic database, occasional queries
Cold data: historical archive or external storage, extremely rare access

Separating hot and historical tables clearly makes the online layer far easier to manage.

7. Combine with Automatic Vacuuming and Statistics Updates

After archiving or dropping partitions, always run ANALYZE to prevent the optimizer from relying on outdated distribution statistics. Properly configure AUTOVACUUM to execute VACUUM and ANALYZE automatically, reclaiming space and refreshing statistics. Build lifecycle maintenance into a fixed operational cadence: pre‑create partitions at month start, archive at month end, drop expired partitions, and refresh statistics after every large change.

8. A Practical Lifecycle Management Sequence

Define retention boundaries first (e.g., 90 days online, 12 months archive, 24 months purge)
Use a time column as the primary partition key
Start with monthly partitions
Separate online, archive, and purge layers
Use partition drop instead of conditional DELETE wherever possible
Follow up every major change with VACUUM/ANALYZE

Well‑designed lifecycle management lets you fully leverage GBase 8c's partitioning capabilities in your gbase database: lighter queries, smaller backups, and lower maintenance overhead. The question isn't "how big is the table?" but rather "is there a clear hot/cold boundary? Are objects split by lifecycle? Does cleanup still rely on heavy‑weight conditional statements? Have statistics and space been refreshed after cleanup?" Once these questions are answered, many downstream operational headaches simply disappear.

Making GBase 8c Auditing Work: Traceable, Retainable, and Queryable

Michael — Sat, 20 Jun 2026 14:33:00 +0000

GBase 8c offers a comprehensive auditing framework, but simply flipping the switch is not enough for production. Effective auditing requires systematic design across audit scope, granularity, retention, and query access. This article focuses on making critical actions traceable — covering audit item configuration, log retention, using pg_query_audit as the primary query entry point, and routine inspection.

1. Define Audit Goals Before Selecting Items

GBase 8c supports a wide range of audit items — login/logout, privilege changes, DDL, DML, SELECT, COPY, function execution, SET parameters, etc. Most items can be enabled dynamically without a restart. However, enabling everything indiscriminately will flood the logs. Prioritise based on your goals:

Goal	Recommended Items	Avoid Enabling Immediately
Security compliance	Login/logout, user lock/unlock, privilege grant/revoke, database start/stop	Full SELECT, all function execution
Operational traceability	Object DDL, SET parameters, database process events, COPY	Full audit for all users
Business data trails	DML on specific tables, supplement with SELECT when necessary	Blanket DML + SELECT across all tables

A layered approach works best in practice: a baseline of system‑level audits (login, privilege, DDL, key parameter changes) that are always on, supplemented by targeted auditing on sensitive tables, key accounts, or during critical time windows.

2. Dynamic Parameter Changes for On‑Demand Auditing

The master switch audit_enabled and most subordinate switches can be reloaded at runtime, making temporary audit escalation straightforward. For example, to temporarily track DML on a specific table:

gs_guc reload -N all -I all -c "audit_dml_state = 1"
gs_guc reload -N all -I all -c "audit_dml_state_select = 1"

Check the current settings:

SHOW audit_directory;
SHOW audit_enabled;
SHOW audit_dml_state;
SHOW audit_dml_state_select;

3. Use pg_query_audit as Your Primary Query Tool

The built‑in function pg_query_audit(start_time, end_time) lets you query audit records directly by time window, avoiding manual log scraping. Filter by action type and object name:

SELECT detail_info, type, result
FROM pg_query_audit('2026-03-25 09:00:00', '2026-03-25 10:00:00')
WHERE type IN ('dml_action', 'dml_action_select')
  AND detail_info LIKE '%acct_trade_detail%';

To trace a specific user's actions, combine the time range with the username and object name.

4. Retention Policies Must Match Business Traceability Requirements

GBase 8c provides these key parameters for managing audit log storage:

SHOW audit_directory;            -- storage directory
SHOW audit_resource_policy;      -- retention policy
SHOW audit_space_limit;          -- total space cap
SHOW audit_file_remain_time;     -- minimum retention (default 90 days)
SHOW audit_file_remain_threshold;-- max file count threshold

Common pitfalls: setting the space limit too low causes logs from a temporary audit escalation to be rolled off too quickly; retention time that doesn't align with monthly or quarterly review cycles leads to missing evidence. Design retention tiers based on scenario — keep baseline security audits long‑term, extend retention for sensitive databases, and promptly reduce granularity after temporary investigations.

5. OS‑File Storage for Audit Independence

GBase 8c writes audit results to operating system files rather than database tables by default. This separation prevents highly privileged users from tampering with audit records, reinforcing their credibility. In production, restrict access to the audit directory and consider using a dedicated security auditor role.

6. Recommended Rollout Sequence

Enable baseline security items first: login/logout, privilege changes, object DDL.
Verify directory and retention settings: check the parameters above to ensure logs aren't lost prematurely.
Add DML/SELECT auditing for critical objects: target sensitive tables, key accounts, and specific time windows.
Build a set of standard query templates: at minimum, templates for querying by time, object name, and action type.
Integrate auditing into routine inspections: monitor audit directory growth and look for abnormal spikes in SELECT/DML volume.

The goal of auditing isn't to record everything, but to make every critical action traceable. Following this methodology turns GBase 8c's auditing capabilities into a reliable evidence chain for your gbase database.

GBase 8c Performance Tuning: A Systematic Approach from Statistics and Execution Plans to Resource Pools

Michael — Sat, 20 Jun 2026 13:27:00 +0000

GBase 8c, the China‑domestically developed multi‑model database from GBASE, supports row‑store, column‑store, and distributed deployment. When a query slows down, the cause often lies deeper than SQL syntax — outdated statistics, a shifted execution plan, or resource contention. This article walks through a layered tuning methodology: verify statistics, inspect the execution plan, align storage and distribution with workload, and finally manage sessions and resources.

1. A Layered Perspective on Tuning

Performance issues in a gbase database generally fall into three layers:

Model layer: Performance is unstable from the start, and scaling doesn't help. Check storage mode, distribution strategy, and index design.
Optimizer layer: The same SQL suddenly shows a different plan with volatile execution times. Check statistics, EXPLAIN output, and misplaced hints.
Resource layer: Everything slows down during peak hours, even if no single query is terrible. Check work_mem, shared_buffers, resource pools, and Cgroups.

2. Statistics: The Foundation of the Execution Plan

The optimizer relies on statistics collected by ANALYZE and stored in pg_class, pg_statistic, etc. Stale statistics lead to inaccurate row estimates and poor plan choices.

Always update statistics after bulk loads, deletes, archiving, partition switches, or when data distribution changes on hot columns.

-- Single table
ANALYZE sales_order;

-- Entire database
ANALYZE;

-- Specific columns
ANALYZE sales_order (customer_id, order_date);

-- Verify with EXPLAIN ANALYZE
EXPLAIN ANALYZE
SELECT customer_id, SUM(pay_amount)
FROM sales_order
WHERE order_date >= date '2026-03-01'
GROUP BY customer_id;

For partitioned tables, ANALYZE updates both the parent and all child partitions — essential for accurate partition pruning.

3. Reading Execution Plans: Focus on Row Estimates and Operator Choice

Use EXPLAIN (ANALYZE, VERBOSE, COSTS, BUFFERS, TIMING) to get detailed runtime information. Key indicators:

Row estimate vs. actual: Large discrepancies lead to poor JOIN or scan choices.
Scan type: A Seq Scan on a large, frequently filtered column suggests missing indexes or stale statistics.
Join type: Hash Join spilling to disk usually means work_mem is too low or the input set is too large. Nested Loop driven by a large result set often points to wrong row estimates.
Sort and aggregation: High cost on Sort/GroupAggregate may be reduced by slimming the column list or pre‑aggregating.
Buffer hit ratio: A low shared hit ratio suggests the buffer cache may be undersized.

Example:

EXPLAIN (ANALYZE, VERBOSE, COSTS, BUFFERS, TIMING)
SELECT o.customer_id, SUM(o.pay_amount)
FROM sales_order o
JOIN dim_customer c ON o.customer_id = c.customer_id
WHERE o.order_date >= date '2026-03-01'
  AND c.customer_level = 'VIP'
GROUP BY o.customer_id;

Common plan signals and actions:

Signal	Likely Cause	Action
Seq Scan on large table	Missing index or bad row estimate	Verify statistics first, then index
Hash Join with heavy spill	work_mem too small or large input	Reduce input, increase session memory
Nested Loop with large driver	Severely inaccurate row estimate	Fix statistics, then consider hint
Heavy Sort / GroupAggregate	Bloated column set	Slim SQL, pre‑aggregate

4. Hints: Emergency Intervention Only

Plan hints (/*+ ... */) such as Leading, HashJoin, NestLoop, IndexScan, SeqScan, and Rows allow you to override the optimizer. Use them only for short‑term fixes or when the optimizer consistently chooses the wrong plan despite accurate statistics and proper indexes.

SELECT /*+ Leading((c o)) HashJoin(c o) */
       o.customer_id, SUM(o.pay_amount)
FROM dim_customer c
JOIN sales_order o ON c.customer_id = o.customer_id
WHERE c.customer_level = 'VIP'
  AND o.order_date >= date '2026-03-01'
GROUP BY o.customer_id;

Always follow up a hint with model and parameter improvements; don't let it become a permanent crutch.

5. Key Parameters and Slow Query Tracking

work_mem: Controls memory for sorts and hash joins. Set it per session based on concurrency — too high risks memory exhaustion.
shared_buffers: Database shared buffer size, critical for read‑heavy workloads.
Statement tracking: Configure track_stmt_stat_level (full/slow), log_min_duration_statement (threshold), and enable_stmt_track. Retrieve slow queries with:

SELECT *
FROM dbe_perf.get_global_slow_sql_by_timestamp(
  '2026-03-24 09:00:00',
  '2026-03-24 09:10:00'
);

6. Resource Management with Cgroups and Resource Pools

GBase 8c's resource management is built on Linux Cgroups, configured via gs_cgroup. Resource pools isolate CPU, memory, and I/O for different workloads — online transactions, reports, ETL — preventing a single heavy query from starving the entire cluster.

7. Choosing the Right Storage and Distribution

Row store (orientation=row): Best for frequent point queries, updates, and short transactions.
Column store (orientation=column): Ideal for analytical scans and aggregations.
Replicated tables (DISTRIBUTE BY replication): Small dimension tables that are joined frequently — eliminates cross‑node data movement.
Hash distribution (DISTRIBUTE BY hash): Large fact tables, distributed on the most common JOIN key or high‑frequency access column.

-- Transaction detail: row store, hash distributed by order_id
CREATE TABLE txn_order (
    order_id      bigint,
    customer_id   bigint,
    order_time    timestamp,
    order_status  varchar(20),
    pay_amount    numeric(18,2)
) WITH (orientation=row)
DISTRIBUTE BY hash(order_id);

-- Analytical summary: column store, hash distributed by customer_id
CREATE TABLE rpt_order_day (
    stat_date      date,
    customer_id    bigint,
    city_id        int,
    order_cnt      bigint,
    pay_amount_sum numeric(18,2)
) WITH (orientation=column)
DISTRIBUTE BY hash(customer_id);

-- Small dimension: replicated
CREATE TABLE dim_city (
    city_id    int,
    city_name  varchar(64),
    region_id  int
) DISTRIBUTE BY replication;

8. A Systematic Tuning Workflow

Confirm the problem is reproducible and capture the business time window.
Verify statement tracking settings and collect slow queries.
Analyze the execution plan with EXPLAIN ANALYZE — focus on row estimates and operator choices.
Update statistics to give the optimizer accurate data.
Tune SQL, add indexes, or apply hints as a short‑term measure.
For peak‑time issues, examine resource pools, Cgroups, memory, and buffer cache as a whole.

Building a reliable gbase database performance baseline means keeping statistics fresh, understanding how the optimizer thinks, aligning storage models with actual workloads, and establishing clear resource boundaries. This layered approach prevents the common cycle of reactive, single‑query patches and delivers consistent performance at scale.

GBase 8a Operations in Practice: Load Monitoring, Audit Logs, and Memory Tuning

Michael — Sat, 20 Jun 2026 12:22:16 +0000

This guide covers three core areas of daily GBase 8a operations: tracking data loads and collecting error details, configuring audit logs and analysing slow queries, and hierarchically tuning memory parameters. It also provides a standard daily and weekly inspection checklist for your gbase database.

1. Data Load Monitoring

1.1 Load Methods

GBase 8a supports two main load methods: gload for large‑scale offline imports (recommended), and LOAD DATA INFILE for single‑file loads with MySQL‑like syntax.

1.2 Checking Load Progress

Monitor running and historical loads through system tables:

-- Currently executing load tasks
SELECT
    task_id, table_name, status, start_time,
    loaded_rows, error_rows,
    TIMESTAMPDIFF(SECOND, start_time, NOW()) AS elapsed_sec
FROM gclusterdb.load_task
WHERE status IN ('RUNNING', 'PENDING')
ORDER BY start_time DESC;

-- Last 50 load history records
SELECT
    task_id, table_name, status,
    start_time, end_time, loaded_rows, error_rows,
    TIMESTAMPDIFF(SECOND, start_time, end_time) AS duration_sec
FROM gclusterdb.load_task
ORDER BY start_time DESC LIMIT 50;

1.3 Retrieving the Last Load Task ID

SELECT @@gbase_loader_last_task_id;

Then query error details with that ID:

SELECT * FROM gclusterdb.load_error_log
WHERE task_id = 'your_task_id' LIMIT 100;

1.4 Error Data Collection

Enable error collection in the gcluster configuration file (gbase.cnf) for production:

gbase_loader_logs_collect = ON

1.5 Load Performance Parameters

Parameter	Scope	Description	Recommended
gcluster_loader_max_data_processors	gcluster	Max concurrent load processing threads	CPU cores / 2
gcluster_loader_min_chunk_size	gcluster	Chunk size sent to gnode (bytes)	64 MB
gbase_loader_parallel_degree	gnode	Parallel write threads on gnode	4 – 8
gbase_loader_buffer_count	gnode	Number of load buffers	4

2. Audit Log Configuration and Analysis

2.1 Enabling Audit Logs

Configure in both gcluster and gnode gbase.cnf files:

audit_log       = ON
log_output      = FILE          # or TABLE
long_query_time = 5             # seconds

2.2 Querying When log_output = TABLE

-- Recent slow queries
SELECT
    start_time, user_host, query_time, lock_time,
    rows_sent, rows_examined, db,
    SUBSTR(sql_text, 1, 200) AS sql_snippet
FROM gclusterdb.slow_log
ORDER BY start_time DESC LIMIT 50;

-- Top SQL patterns by average execution time
SELECT
    SUBSTR(sql_text, 1, 100) AS sql_pattern,
    COUNT(*) AS exec_count,
    AVG(query_time) AS avg_time,
    MAX(query_time) AS max_time,
    SUM(rows_examined) AS total_rows_scanned
FROM gclusterdb.slow_log
WHERE start_time >= DATE_SUB(NOW(), INTERVAL 1 DAY)
GROUP BY sql_pattern
ORDER BY avg_time DESC LIMIT 20;

2.3 Node‑Level SQL Execution Time Monitoring

Set the threshold in gcluster gbase.cnf:

gcluster_dql_statistic_threshold = 3000   # milliseconds

Query per‑node execution times:

SELECT
    sql_id, node_name, exec_time, rows_processed
FROM gclusterdb.dql_statistic
WHERE exec_time > 3000
ORDER BY sql_id, exec_time DESC;

If one node's exec_time is far higher than the others, suspect data skew or a hardware issue.

3. Memory Parameter Tuning

3.1 Memory Hierarchy

The gnode process memory is governed by gbase_memory_pct_target (percentage of system memory). Beneath it, heap memory is split into gbase_heap_data (normal operations) and gbase_heap_large (heavy operations like sorts/joins), plus multiple operation‑level buffers.

3.2 Key Parameters

Parameter	Scope	Description	Typical Value
gbase_memory_pct_target	gnode	% of system memory for gnode	70 – 80
gbase_heap_data	gnode	Heap for normal ops (MB)	30% of total memory
gbase_heap_large	gnode	Heap for large ops (MB)	30% of total memory
gbase_buffer_hj	gnode	Hash Join buffer (MB)	512 – 2048
gbase_buffer_sort	gnode	Sort buffer (MB)	512 – 2048
gbase_buffer_hgrby	gnode	Hash Group By buffer (MB)	512 – 1024

3.3 Example Configuration (64 GB Physical RAM Node)

# gnode gbase.cnf
gbase_memory_pct_target     = 75      # gnode uses 48 GB
gbase_heap_data             = 16384   # 16 GB
gbase_heap_large            = 16384   # 16 GB
gbase_buffer_hj             = 2048
gbase_buffer_hgrby          = 1024
gbase_buffer_distgrby       = 1024
gbase_buffer_sort           = 1024
gbase_buffer_rowset         = 256
gbase_buffer_result         = 512
gbase_buffer_insert         = 256

3.4 Monitoring Actual Memory Usage

Enable session memory statistics:

_gbase_session_memory_stat = 1

Query per‑session memory consumption:

SELECT
    session_id, user, db,
    ROUND(memory_used / 1024 / 1024, 2) AS memory_mb,
    state, SUBSTR(info, 1, 100) AS sql_snippet
FROM gclusterdb.session_memory_stat
ORDER BY memory_used DESC LIMIT 20;

3.5 Hot Data Eviction Under Memory Pressure

In gnode gbase.cnf:

_gbase_cache_drop_hot_data           = 1
_gbase_cache_drop_unlock_cell_count  = 1000
_gbase_cache_drop_delay_time        = 100

4. Connection and Timeout Quick Reference

Key timeout parameters in gcluster gbase.cnf include connect_timeout (handshake), read/write timeouts, internal reconnect settings, gcluster_lock_timeout, and Wait_timeout for idle sessions. JDBC clients should also specify connectTimeout and socketTimeout in the URL.

5. Daily Operations Checklist

Daily checks:

-- 1. Node status
SELECT node_name, status, last_heartbeat_time
FROM gclusterdb.node_info
ORDER BY node_name;

-- 2. Yesterday's load failure rate
SELECT
    table_name,
    COUNT(*) AS total_tasks,
    SUM(CASE WHEN status = 'FAILED' THEN 1 ELSE 0 END) AS failed_tasks,
    SUM(error_rows) AS total_error_rows
FROM gclusterdb.load_task
WHERE DATE(start_time) = CURDATE() - INTERVAL 1 DAY
GROUP BY table_name
HAVING failed_tasks > 0 OR total_error_rows > 0;

-- 3. Long‑running active transactions
SELECT * FROM information_schema.processlist
WHERE time > 300
ORDER BY time DESC;

Weekly checks:

-- 4. Data volume balance across nodes
SELECT
    node_name,
    ROUND(SUM(data_size) / 1024 / 1024 / 1024, 2) AS data_gb
FROM gclusterdb.segment_info
GROUP BY node_name
ORDER BY data_gb DESC;

-- 5. Top 10 slow queries of the week
SELECT
    SUBSTR(sql_text, 1, 150) AS sql,
    COUNT(*) AS cnt,
    ROUND(AVG(query_time), 2) AS avg_sec,
    MAX(query_time) AS max_sec
FROM gclusterdb.slow_log
WHERE start_time >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
GROUP BY sql
ORDER BY avg_sec DESC
LIMIT 10;

Regularly inspecting system tables under gclusterdb helps you spot potential issues before they impact your gbase database.