DEV Community: Michael

How GBase 8a Rough Index Works: Block‑Level Pruning for 10x Faster Queries

Michael — Wed, 20 May 2026 14:53:00 +0000

The Rough Index is a lightweight, built‑in indexing mechanism of GBase 8a's columnar storage engine. Instead of tracking exact row positions, it records the minimum and maximum value of a column for each data block. When a query runs, the optimizer scans the Rough Index and skips blocks that cannot possibly contain matching rows — drastically cutting disk I/O in a gbase database.

How Rough Index Works

Imagine an amount column split into 1,000 blocks:

Block 1: min 10, max 500
Block 2: min 501, max 1,200

For a query like WHERE amount > 2000, the engine skips every block whose max value is ≤ 2000. This block pruning can eliminate over 90% of I/O when data is well‑ordered.

Verifying That Rough Index Is Active

Use EXPLAIN to inspect the plan:

EXPLAIN SELECT SUM(amount) FROM orders WHERE create_time >= '2024-01-01';

If you see Rough Index Scan or the scanned block count is far lower than the total, the index is working. You can also query the system view:

SELECT * FROM information_schema.GBASE_ROUGH_INDEX 
WHERE table_name = 'orders';

Three Tips to Maximize Rough Index Performance

Keep data ordered during ingestion: Sorting by frequently filtered columns (e.g., time) before loading shrinks the min‑max range inside each block.
Manually rebuild after heavy updates: Run ALTER TABLE orders REBUILD ROUGH INDEX; after large‑scale modifications or out‑of‑order inserts.
Prefer range predicates in queries: BETWEEN, >, < deliver the best pruning. IN lists and != are less effective.

The Rough Index is a core performance accelerator of GBASE's columnar engine. Building your data model and load routines around it can yield dramatic speed improvements without any additional hardware in your gbase database.

Building a Rock‑Solid HA Strategy for GBase 8a: From Cluster Architecture and Failover to Consistency Repair

Michael — Wed, 20 May 2026 13:45:00 +0000

What separates a reliable GBase 8a deployment from a fragile one isn't how fast a single query runs — it's whether the system keeps serving after a failure, how quickly it recovers, and whether the data stays consistent afterward. This is where high availability (HA) design earns its keep.

GBase 8a, as a distributed analytical database, structures its HA into three layers: cluster‑level, node‑level, and process‑level. Cluster‑level HA relies on data sync tools and mirror clusters; node‑level HA revolves around Gcluster, Gnode, and Gcware nodes; process‑level HA depends on real‑time monitoring and auto‑recovery of core services.

1. The Three HA Layers

Layer	Primary Goal	Core Capability	Best For
Cluster‑level	Survive entire‑cluster failure	Inter‑cluster sync, mirror clusters	Remote DR, intra‑city active‑active, read/write split
Node‑level	Survive single‑node failure	Gcluster Failover, Gnode multi‑replica, Gcware Raft	Server crashes, partial node anomalies
Process‑level	Survive service‑process crash	Process monitoring, auto‑recovery	Transient faults, self‑healing

This layering directly determines your strategy. If your concern is "a machine goes down but business must continue," focus on node‑level HA. If it's "a whole data center fails and we must switch to another," that's cluster‑level HA.

2. Cluster‑Level HA: Disaster Recovery vs. Active‑Active

GBase 8a offers two paths at the cluster level: inter‑cluster sync and mirror clusters.

Approach	Sync Mode	Typical Scenario	Characteristic
Inter‑cluster Sync	Incremental	Remote DR, T+1 reporting, cascading sync	Async, DR‑oriented
Mirror Cluster	Real‑time	Intra‑city active‑active, failover, read/write split	Real‑time, business‑continuity‑oriented

The data sync tool supports incremental sync between two homogeneous GBase 8a clusters based on data blocks rather than traditional log replay — far more efficient for massive data volumes. Mirror clusters synchronize data in real time; once the primary writes, data flows instantly to the backup cluster, transparent to applications, and supports read/write splitting on top.

How to choose: If the primary writes, the standby mainly reads, some sync delay is acceptable, and the focus is remote DR, go with inter‑cluster sync. If the standby must be readable almost immediately after writes, you want to offload read traffic, and smooth intra‑city failover is critical, mirror clusters are the better fit.

3. Node‑Level HA: The Insurance That Fires Most Often

GBase 8a has three node types — Gcluster (scheduling), Gnode (storage & compute), Gcware (management) — and their HA logic differs.

3.1 Gcluster: Don't Let the Entry Point Become a Single Point

Gcluster handles access, authentication, SQL parsing, and scheduling. Gcluster nodes are independent and support Failover: when one node fails, others take over its in‑flight tasks. As long as one healthy Gcluster node remains, the cluster stays online. The real risk is not Gcluster itself, but connecting applications that always point to a single address.

3.2 Gnode: Replica Count Determines Fault Tolerance

Gnode stores data and runs computations. Its HA relies on multi‑replica mechanisms. With 3 replicas, each piece of data has three copies on different Gnode nodes; even if two nodes become unavailable, the remaining replica still provides access.

Replica Count	Availability	Risk Profile
1	Almost no node‑level fault tolerance	Node failure = data unavailable
2	Some redundancy	Recovery and consistency pressure higher in edge cases
3	Production‑grade	Stronger node‑level fault tolerance

"Three replicas are safer" doesn't mean "the cluster can lose any two machines casually." Actual availability also depends on replica placement, hot‑spot data, Gcware state, and node topology.

3.3 Gcware: The Arbitration and Consistency Core

Gcware manages cluster metadata consistency and data consistency, using the Raft protocol. As long as the surviving Gcware nodes satisfy Raft's minimum quorum, the Gcware cluster continues to function.

Gcware Nodes	Recommendation	Notes
1	Not for production	Obvious single point
2	Generally discouraged	Too little quorum margin
3	Commonly recommended	Good balance of cost and availability
5	For higher availability requirements	More stable, higher cost

4. Process‑Level HA: Small Faults That Shouldn't Escalate

Core GBase 8a processes (GNode, GCluster, GCware, etc.) are continuously monitored and can auto‑recover after failure. A practical daily check:

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

5. Primary‑Replica Inconsistency: The Hardest HA Problem

A node crash is usually detected fast, but replica inconsistency can lurk while the cluster still appears operational — producing drifting results and subtle anomalies. Common causes: inconsistent local parameters, power loss or kernel panic, RAID controller or driver anomalies, VM abnormal exit, manual mistakes (e.g., deleting events during a node outage).

GBase 8a uses direct I/O for writes; it considers a write successful only when the return confirms it. But if the underlying environment fails, a "successful" write may not have reached physical disk. The lesson: don't blame all consistency issues on database logic — hardware, virtualization, and host stability are integral parts of HA.

6. Key Parameter: gcluster_suffix_consistency_resolve

GBase 8a provides the gcluster_suffix_consistency_resolve parameter to handle primary‑replica inconsistency:

Value	Behavior
0 (default)	Does not attempt automatic resolution
1	Tries to automatically resolve consistency issues

This parameter supports both session and global scope. It can automatically detect and repair scenarios like row‑count mismatches, schema differences, and SCN inconsistencies across replicas. Before enabling in production, verify version support, confirm the cluster has at least 3 host nodes, and validate in a test environment.

SET GLOBAL gcluster_suffix_consistency_resolve = 1;

7. Parameter Consistency: The Most Overlooked Foundation

Many teams obsess over replica counts, active‑active setups, and failover while neglecting the most basic layer: parameter consistency across nodes. Community documentation explicitly lists "parameter differences" as a common cause of replica inconsistency.

Parameter Category	Recommendation	Reason
Consistency‑related	Uniform across all nodes	Prevent replica behavior drift
Resource limits	Uniform across all nodes	Avoid weak‑link nodes
Log levels	Adjustable, but keep records	Troubleshooting convenience
Experimental params	Test environment first	Reduce production drift

A quick baseline check:

for host in 203.0.113.41 203.0.113.42 203.0.113.43
do
  echo "===== $host ====="
  ssh $host "grep gcluster_suffix_consistency_resolve /opt/gbase/conf/* 2>/dev/null"
done

8. Read/Write Split Is Also Part of HA

GBase 8a supports multiple read/write split approaches. Their value isn't just performance — they keep the standby side actively serving reads, so the standby isn't idle, and the switchover cost is lower when needed.

Method	Granularity	Primary Orientation
Replicated Table	Table‑level	Write‑once‑read‑many within a node
Mirror Cluster	Cluster‑level	Real‑time read/write split
Inter‑cluster Sync	Cluster‑level	Scheduled sync, DR‑style read/write split

9. Recommended Rollout Sequence

Solidify node‑level HA first: Multi‑entry Gcluster access, no single‑replica core data on Gnode, odd‑numbered Gcware nodes for quorum.
Establish parameter and configuration baselines: Track all config changes, periodically compare key parameters across nodes, ensure temporary tweaks can be rolled back.
Then choose the cluster‑level path: Remote DR with acceptable sync delay → inter‑cluster sync. Intra‑city active‑active with real‑time read/write split → mirror cluster.
Finally, run failover drills: At minimum, cover Gcluster single‑point failure, Gnode replica node anomaly, Gcware node loss, and primary‑replica inconsistency detection & repair.

10. Daily Inspection Template

# Cluster status
gcadmin

# Key processes
ps -ef | egrep 'gcware|gcluster|gnode'

# Key logs
tail -100 /opt/gbase/gcluster/log/system.log
tail -100 /opt/gbase/gcware/log/gcware.log

# Parameter consistency across nodes
for host in 203.0.113.41 203.0.113.42 203.0.113.43
do
  echo "===== $host ====="
  ssh $host "grep gcluster_suffix_consistency_resolve /opt/gbase/conf/* 2>/dev/null"
done

Closing

GBase 8a HA isn't a single feature — it's a layered system. Looking up: inter‑cluster sync, mirror clusters, read/write split. Looking across: Gcluster, Gnode, Gcware three‑layer node fault tolerance. Looking down: process‑level self‑healing and parameter consistency control. The most stable designs aren't the most complex ones — they're the ones that solidify the node‑level foundation first, then layer on cluster‑level capabilities as the scenario demands.

A well‑architected gbase database HA strategy keeps your data available and consistent through failures both small and large — and that's what production maturity really looks like.

GBase 8a Data Sync in Practice: T+1 Replication, Real‑Time Mirroring, and Write‑Once‑Read‑Many

Michael — Wed, 20 May 2026 12:20:34 +0000

Data synchronization in GBase 8a isn't just "primary‑standby replication." Different business requirements — real‑time, disaster recovery, read/write splitting — lead to completely different technical paths. This article organizes three core approaches — mirror clusters, inter‑cluster sync, and replicated tables — into a practical decision framework for your gbase database.

The Three Sync Routes at a Glance

Approach	Timeliness	Granularity	Best For	Characteristics
Mirror Cluster	Real‑time	Table‑level	Intra‑city active‑active, real‑time read/write split	Real‑time sync between two clusters, business continuity
Inter‑Cluster Sync	Scheduled / Incremental	Table‑level	Remote DR, T+1 reporting, cascading distribution	Supports 1‑to‑many and cascading; delay tied to data volume
Replicated Table	Near real‑time within cluster	Table‑level	Local write‑once‑read‑many, hot table read scaling	Every data node holds an identical copy

The key to choosing isn't memorizing names — it's clarifying what problem you're solving. Want the standby cluster to serve queries in near real‑time? Look at mirror clusters first. Need periodic sync to a reporting or DR cluster? Inter‑cluster sync fits better. Just need dimension tables readable across all nodes? Replicated tables are enough.

Mirror Clusters: Real‑Time Active‑Standby and Read/Write Splitting

A mirror cluster aims to get data to the other side as quickly as possible so the standby can serve read traffic continuously — not just during failures. Think of it as table‑level real‑time mapping. It suits:

Standby must be queryable shortly after writes on the primary
Intra‑city active‑active or near‑real‑time read/write split
Tolerates only very small data lag

If the two clusters aren't in the same data center and the network is mediocre, forcing real‑time sync will cause constant instability. Cross‑region, bandwidth‑limited scenarios are often not the best fit.

Inter‑Cluster Sync: Incremental Distribution and Disaster Recovery

Inter‑cluster sync moves changes on a schedule, accepting minute‑level or even hour‑level delays. It excels at:

Remote disaster recovery
T+1 report queries
One production cluster feeding multiple downstream clusters

A typical topology: production syncs hourly to a reporting cluster, daily to a DR cluster, and cascades to regional query clusters. It's less demanding on the network than real‑time approaches and more cost‑effective in multi‑downstream, multi‑purpose environments.

	Mirror Cluster	Inter‑Cluster Sync
Real‑time	High	Low–Medium
Cross‑region suitability	Moderate	Better
Multi‑downstream	Moderate	Stronger
Typical use	Active‑active, read/write split	DR, reporting, distribution

Replicated Tables: Write‑Once‑Read‑Many Inside the Cluster

A replicated table keeps an identical copy on every node, spreading read pressure and reducing cross‑node costs during queries. It's ideal for small tables, dimension tables, and lookup tables that are read frequently but updated infrequently.

Capability	Replicated Table	Inter‑Cluster Sync	Mirror Cluster
Scope	Within cluster	Between clusters	Between clusters
Timeliness	Intra‑cluster sync	Scheduled / Incremental	Real‑time
Primary value	Write‑once‑read‑many	DR / Distribution	Active‑active / read/write split

Cross‑cluster sync solves "how data reaches another cluster." Replicated tables solve "how to read more easily within the same cluster." They operate at different levels.

Four Decision Dimensions

Before designing a sync strategy, answer these four questions:

Dimension	Leans Mirror Cluster	Leans Inter‑Cluster Sync	Leans Replicated Table
Real‑time requirement	High	Low–Medium	High within cluster
Cross‑region	Less stable than scheduled	Better fit	Not applicable
Downstream count	Typically 2 clusters	1‑to‑many, cascading	Not applicable
DR orientation	Possible	Excellent	Not for cross‑cluster DR

In short: intra‑city near‑real‑time active‑active / read‑write split → mirror cluster; remote DR / T+1 reporting / multi‑downstream → inter‑cluster sync; hot table read optimization within a cluster → replicated tables.

Three Common Pitfalls

Over‑idealizing real‑time requirements — if clusters span data centers with average networks, real‑time sync will be fragile.
Treating sync as "automatic full‑database replication" — GBase 8a sync is mostly table‑level. Permissions, job chains, views, scripts, and application connections need separate planning.
Watching only "sync success" without verifying "sync usage" — data arriving is not enough. Check whether queries actually shifted to the target, whether the DR link can really switch over, and whether downstream clusters are truly being used.

Pre‑Launch Checklist

Strategy level: Confirm sync mode (real‑time/scheduled/incremental), granularity (table‑level), direction (one‑way/two‑way), downstream count, and network path.

Operational level: Continuously monitor sync lag, incremental backlog, downstream query latency, key‑table row‑count validation, and whether any critical tables are missing from the sync scope.

A simple daily check: compare row counts between source and target for key tables on the same date.

Recommended Rollout Sequence

Clarify the goal first: Remote DR → inter‑cluster sync. Intra‑city active‑active → mirror cluster. Report offloading → either works. Hot intra‑cluster tables → replicated tables.
Layer the objects: Separate core fact tables, report summary tables, and dimension/lookup tables. Decide which must be real‑time, which can be hourly, and which only need intra‑cluster replication.
Confirm windows and link conditions: sub‑second requirements? minute‑level tolerable? cross‑region? multiple downstream clusters?

GBase 8a data sync is a layered, well‑defined capability set. Not every sync needs to be real‑time — the key is reserving real‑time capacity for the places that truly need it in your gbase database.

Debugging Slow SQL in GBase 8a: How Execution Plans, Distribution Keys, and Parallelism Go Off the Rails Together

Michael — Wed, 20 May 2026 10:35:53 +0000

A slow query in a gbase database MPP cluster is rarely just a matter of sloppy SQL. More often, it's the combined effect of data distribution, execution plan choices, parallelism settings, and resource contention — all interacting at once. If you apply single‑node OLTP tuning habits here, you'll usually make things worse.

This article lays out a complete troubleshooting path, from deciding whether a query is truly slow to fixing distribution‑key mistakes and reining in runaway parallelism.

1. First, Distinguish "Naturally Heavy" from "Suddenly Slow"

Analytical workloads run anywhere from seconds to hours, so there's no single threshold that flags a slow query. Instead, compare execution times against historical baselines for the same task.

Naturally heavy SQL — always slow but stable. Look at the data model, distribution keys, and whether pre‑aggregation would help.
Suddenly slow SQL — the same task recently degraded. Check locks, execution plan changes, data skew, and parallel‑resource contention.

Collect periodic task durations via audit_log and correlate them with table‑growth trends to build a daily monitoring baseline.

2. Rule Out Wait States First

Many queries appear slow because they haven't actually started running — they're stuck on a lock or queued behind other work.

SHOW PROCESSLIST;

If you see states like checking permission, suspect lock waits.

gcadmin showlock

Two common traps: treating a queued session as a slow execution (rewriting the SQL won't help), and mistaking a single slow node for a cluster‑wide problem. In a 16‑node cluster, if 15 nodes finish in 2 minutes and one node takes 18, the entire query feels slow.

3. Watch Data Movement in the Execution Plan

Key motion operators in a GBase 8a plan[reference:8]:

Operator	Meaning
RESULT	Send result to client
GATHER	Collect data at the coordinator
REDIST(...)	Hash‑redistribute on a column
BROADCAST	Replicate / broadcast result
RAND REDIST	Random redistribution

In an MPP context, the most important question is: how many times does the plan move data around? A classic costly scenario: a large fact table is not distributed on its JOIN key, and the dimension table is not replicated — causing multiple REDIST steps that generate extra disk I/O, network transfer, and write overhead on the receiving side.

The community documentation highlights three core issues to look for in execution plans: unreasonable dynamic redistribution, extra redistribution caused by mismatched column types, and Cartesian products triggered by poor JOIN ordering[reference:9].

4. Wrong Distribution Keys Set the Stage for Slow SQL

Make the large‑table JOIN column the Hash distribution key whenever possible; the GROUP BY column is a second choice. GBase 8a V9 also supports multi‑column Hash to reduce skew[reference:10].

The guiding principle: do the most expensive work locally, on the node where the data already sits. Avoid shredding and redistributing massive data sets just to run a JOIN or GROUP.

If a fact table grows by hundreds of millions of rows a day and most core reports slice by customer, distributing on customer_id is far better than the "neutral" order_id.

CREATE TABLE dws_order_day (
    stat_date   DATE,
    order_id    BIGINT,
    customer_id BIGINT,
    product_id  BIGINT,
    pay_amount  DECIMAL(18,2),
    city_id     INT
)
DISTRIBUTED BY HASH(customer_id);

For small, slowly‑changing dimension tables, use replication so that JOINs with large tables don't trigger redistribution[reference:11].

Three easily overlooked issues: pick a hash column with many distinct values to avoid node skew[reference:12]; ensure JOIN columns have matching types on both sides; don't pin all hope on single‑table indexes — in analytical workloads, how data is distributed and moved usually dominates performance.

5. Rewrite SQL to Work With Columnar Storage and MPP Execution

Avoid SELECT *. Remove unnecessary inner ORDER BY. Prevent Cartesian JOINs. For multi‑column GROUP BY / JOIN, place the column with the most distinct values first.

A common anti‑pattern:

-- ❌ SELECT * defeats columnar benefits, inner ORDER BY is pointless, JOIN types mismatch
SELECT *
FROM (
    SELECT * FROM dwd_order_detail
    WHERE stat_date BETWEEN '2026-03-01' AND '2026-03-07'
    ORDER BY create_time
) t
JOIN dim_customer c ON CAST(t.customer_id AS VARCHAR(32)) = c.customer_code;

A safer rewrite:

-- ✅ Slim columns first, aggregate locally on the fact table, then JOIN with matching types
SELECT t.customer_id, t.order_cnt, c.customer_level
FROM (
    SELECT customer_id, COUNT(*) AS order_cnt
    FROM dwd_order_detail
    WHERE stat_date BETWEEN '2026-03-01' AND '2026-03-07'
    GROUP BY customer_id
) t
JOIN dim_customer c ON t.customer_id = c.customer_id;

6. Use Hash Indexes Judiciously

Hash indexes excel at single‑table equality lookups with small result sets. They don't help with range scans or fuzzy queries[reference:13]. For columns with few distinct values and frequent exact queries, a Global Hash index is a common choice.

CREATE INDEX idx_user_id ON dwd_user_behavior(user_id) USING HASH GLOBAL;

But indexes have a maintenance cost: they can slow down data loading and DML. For real‑time loading scenarios, stage data into an index‑free temporary table first, then bulk‑move into the indexed target or build the index in one shot[reference:14]. On large fact tables, always tackle distribution keys and SQL patterns before reaching for Hash indexes.

7. In High‑Concurrency Scenarios, Dialing Back Parallelism Often Stabilizes Performance

Key parameters: gbase_parallel_execution (parallel enable), gbase_parallel_degree (max parallelism per SQL), gbase_parallel_max_thread_in_pool (thread pool size)[reference:15].

Higher concurrency does not automatically mean higher performance. When CPU, disk, and memory are finite, too much parallelism results in elevated CPU sys time, 100% disk busy with low throughput, and swapping.

Low concurrency, few heavy queries: raise gbase_parallel_degree (e.g., 16).
High peak concurrency, many batch tasks: lower single‑SQL parallelism (e.g., 4) and cap the thread pool (e.g., 64) so that more tasks can make progress and the pool isn't drained by a few large queries[reference:16].

8. A Complete Troubleshooting Flow

Confirm it's truly a regression — compare historical runtimes and data‑volume trends.
Rule out waiting — SHOW PROCESSLIST, gcadmin showlock.
Inspect the execution plan — watch for REDIST, GATHER, BROADCAST and mismatched JOIN column types.
Revisit table design — do the distribution keys align with the core JOIN / GROUP paths? Should small tables be replicated?
Tune parameters — gbase_parallel_degree, thread pool, memory relative to concurrency.
Evaluate indexes last — is this genuinely a single‑table exact‑match problem? Can you afford the maintenance cost?

At its heart, performance tuning in a gbase database is about minimizing unnecessary data movement. Keep data on the node where the work happens, aggregate early, and don't let parallelism cannibalize itself. Build these habits into your model design and job scheduling, and you'll stop a large share of slow queries before they ever reach production.

GBase 8a Partitioning Best Practices: From Design to Operations

Michael — Tue, 19 May 2026 11:45:00 +0000

In a gbase database handling tens of billions of rows, smart partitioning is no longer optional — it's mandatory. This guide covers partition type selection, pruning validation, daily maintenance, and common pitfalls to help you build a solid partitioning strategy for your GBASE cluster.

Supported Partitioning Types

GBase 8a offers four partitioning methods:

RANGE – split by column value intervals (ideal for time‑series data)
LIST – split by discrete enumerated values (e.g., region, status)
HASH – distribute evenly via hash modulus
KEY – similar to HASH, but the database chooses the hash function

In practice, RANGE partitioning on a date column is the most common choice.

RANGE Partitioning

Monthly Partitioning Example

CREATE TABLE orders (
    order_id     BIGINT NOT NULL,
    customer_id  BIGINT NOT NULL,
    order_date   DATE NOT NULL,
    amount       DECIMAL(12,2),
    status       VARCHAR(20),
    PRIMARY KEY (order_id, order_date)   -- partition key must be part of PK
)
PARTITION BY RANGE (TO_DAYS(order_date)) (
    PARTITION p202401 VALUES LESS THAN (TO_DAYS('2024-02-01')),
    PARTITION p202402 VALUES LESS THAN (TO_DAYS('2024-03-01')),
    PARTITION p202403 VALUES LESS THAN (TO_DAYS('2024-04-01')),
    PARTITION p202404 VALUES LESS THAN (TO_DAYS('2024-05-01')),
    PARTITION p202405 VALUES LESS THAN (TO_DAYS('2024-06-01')),
    PARTITION p202406 VALUES LESS THAN (TO_DAYS('2024-07-01')),
    PARTITION p_future VALUES LESS THAN MAXVALUE  -- catch-all
);

Key rules:

The partition key column must be included in the primary key or unique key, otherwise the DDL will fail.
For daily partitioning, keep the total partition count under 1024 to avoid metadata overhead.

LIST Partitioning Example

CREATE TABLE sales_regional (
    sale_id   BIGINT NOT NULL,
    region    VARCHAR(20) NOT NULL,
    sale_date DATE NOT NULL,
    amount    DECIMAL(12,2),
    PRIMARY KEY (sale_id, region)
)
PARTITION BY LIST COLUMNS (region) (
    PARTITION p_north   VALUES IN ('北京','天津','河北','山西','内蒙古'),
    PARTITION p_east    VALUES IN ('上海','江苏','浙江','安徽','山东'),
    PARTITION p_south   VALUES IN ('广东','广西','海南','福建'),
    PARTITION p_central VALUES IN ('湖北','湖南','河南','江西'),
    PARTITION p_west    VALUES IN ('四川','重庆','云南','贵州','西藏'),
    PARTITION p_other   VALUES IN ('其他')
);

Partition Pruning

Pruning kicks in only when the WHERE clause references the partition key. Verify it with EXPLAIN PARTITIONS:

EXPLAIN PARTITIONS
SELECT * FROM orders
WHERE order_date BETWEEN '2024-03-01' AND '2024-03-31';

✅ If only p202403 appears in the partitions column, pruning works.
❌ If all partitions are listed, pruning is disabled — check your query.

Common pruning killers:

Applying a function: WHERE MONTH(order_date) = 3 ❌
Implicit type conversion: WHERE order_date = 20240301 ❌
Mixing partition-key and non‑key conditions with OR ❌ — consider UNION ALL instead.

Day‑to‑Day Partition Maintenance

Add a New Partition

ALTER TABLE orders
    REORGANIZE PARTITION p_future INTO (
        PARTITION p202407 VALUES LESS THAN (TO_DAYS('2024-08-01')),
        PARTITION p_future VALUES LESS THAN MAXVALUE
    );
-- Always update statistics immediately!
ANALYZE TABLE orders;

Drop Historical Partitions (orders of magnitude faster than DELETE)

ALTER TABLE orders DROP PARTITION p202101, p202102, p202103;

Merge or Split Partitions

-- Merge two small partitions
ALTER TABLE orders
    REORGANIZE PARTITION p202401, p202402 INTO (
        PARTITION p2024_q1 VALUES LESS THAN (TO_DAYS('2024-03-01'))
    );
-- Split a large partition
ALTER TABLE orders
    REORGANIZE PARTITION p_future INTO (
        PARTITION p202407 VALUES LESS THAN (TO_DAYS('2024-08-01')),
        PARTITION p202408 VALUES LESS THAN (TO_DAYS('2024-09-01')),
        PARTITION p_future VALUES LESS THAN MAXVALUE
    );

Common Mistakes

Too many partitions: Over 500 partitions cause metadata bloat and slower plan generation. Aim for 50–200 partitions.
Low‑cardinality partition key: Using a column like status (3 values) causes severe data skew.
No catch‑all partition: Without MAXVALUE, out‑of‑range inserts fail immediately.
Skipping ANALYZE TABLE after partition changes: The optimizer sees empty partitions and may choose poor plans.

Automate Future Partition Creation

A simple script added to crontab ensures you never forget to create next month's partition:

#!/bin/bash
NEXT_MONTH=$(date -d "+1 month" +%Y%m)
NEXT_MONTH_START=$(date -d "+1 month" +%Y-%m-01)
NEXT_NEXT_MONTH_START=$(date -d "+2 month" +%Y-%m-01)
PARTITION_NAME="p${NEXT_MONTH}"

mysql -h 192.168.1.10 -P 5258 -u gbase -pyour_password sales_db <<EOF
ALTER TABLE orders
    REORGANIZE PARTITION p_future INTO (
        PARTITION ${PARTITION_NAME} VALUES LESS THAN (TO_DAYS('${NEXT_NEXT_MONTH_START}')),
        PARTITION p_future VALUES LESS THAN MAXVALUE
    );
ANALYZE TABLE orders;
EOF

Crontab: 0 1 1 * * /bin/bash /usr/local/scripts/auto_add_partition.sh

Design Decision Flow

Estimate data size: below 10 million rows → no partition needed; above 10 million → consider partitioning.
Date column? → RANGE. Discrete categories? → LIST. No obvious pattern? → HASH.
Granularity: daily inserts > 5 million → partition by day; 1–5 million → by month; < 1 million → by quarter or year.
Ensure the partition key is in the primary key, then validate pruning with EXPLAIN.

A well‑designed partitioning layout is the foundation of query performance in a gbase database. Spend the time to get it right at design time, and you'll save yourself a world of trouble later.

GBase 8a Backup and Restore Guide: Full and Incremental Backups with gbackup

Michael — Tue, 19 May 2026 10:36:00 +0000

GBase 8a stores massive analytical datasets across many nodes. If hardware fails, someone drops a table, or an upgrade goes wrong, a solid backup strategy is your only lifeline. This guide covers the official gbackup tool for full and incremental backups, plus mysqldump for finer-grained tasks.

gbackup vs. mysqldump

Aspect	gbackup	mysqldump
Type	Physical backup	Logical backup
Speed	Fast (copies data files)	Slow (generates SQL statements)
Restore speed	Fast	Slow (re‑executes every statement)
Granularity	Database / cluster	Database, table
Cross‑platform	Weaker	Strong (standard SQL)
Best for	Full disaster recovery, large volumes	Table migration, cross‑platform exports

For production, gbackup is the primary tool. mysqldump fills the gaps when you need per‑table exports.

Full Backup with gbackup

Before You Start

# Verify all nodes are healthy
gcadmin showall

# Ensure the backup directory has enough space (1.5× data size recommended)
df -h /backup/gbase

Run a Full Backup

# Basic full backup
gbackup \
  --backup-dir=/backup/gbase/full_20240601 \
  --backup-type=full \
  --user=gbase --password=your_password \
  --host=coordinator_ip --port=5258

# Compressed full backup (saves 30%–50% storage)
gbackup \
  --backup-dir=/backup/gbase/full_20240601 \
  --backup-type=full --compress \
  --user=gbase --password=your_password \
  --host=coordinator_ip --port=5258

Verify the Backup

ls -lh /backup/gbase/full_20240601/
cat /backup/gbase/full_20240601/backup.log | tail -20
# Look for "backup completed successfully"

Incremental Backups with gbackup

Incremental backups capture only changes since the last backup, so they're fast and compact.

gbackup \
  --backup-dir=/backup/gbase/incr_20240602 \
  --backup-type=incremental \
  --incremental-basedir=/backup/gbase/full_20240601 \
  --user=gbase --password=your_password \
  --host=coordinator_ip --port=5258

Recommended cadence: Full on Sunday, incrementals Monday–Saturday, new full next Sunday. At worst, you lose one day of data.

Restoring from Backups

Full Restore

# Stop the database first
gcadmin stop

grestore \
  --backup-dir=/backup/gbase/full_20240601 \
  --user=gbase --password=your_password \
  --host=coordinator_ip --port=5258

gcadmin start

Full + Incremental Restore

Restore the full backup, then apply each incremental in sequence. The last incremental omits --apply-log-only to finalize the consistent state.

# Restore full backup (don't commit yet)
grestore --backup-dir=/backup/gbase/full_20240601 --apply-log-only \
  --user=gbase --password=your_password

# Apply first incremental
grestore --backup-dir=/backup/gbase/incr_20240602 --apply-log-only \
  --incremental-dir=/backup/gbase/incr_20240602

# Apply last incremental (no --apply-log-only)
grestore --backup-dir=/backup/gbase/full_20240601 \
  --incremental-dir=/backup/gbase/incr_20240603

gcadmin start

mysqldump for Fine-Grained Tasks

# Export a single database
mysqldump -h coordinator_ip -P 5258 -u gbase -p \
  --databases sales_db > /backup/sales_db_20240601.sql

# Export a single table
mysqldump -h coordinator_ip -P 5258 -u gbase -p \
  sales_db orders > /backup/orders_20240601.sql

# Import back
mysql -h coordinator_ip -P 5258 -u gbase -p sales_db < /backup/orders_20240601.sql

Automating with Cron

#!/bin/bash
DATE=$(date +%Y%m%d)
BACKUP_BASE=/backup/gbase
COORD_IP=192.168.1.10

DAY_OF_WEEK=$(date +%u)
if [ "$DAY_OF_WEEK" -eq 7 ]; then
  BACKUP_DIR=$BACKUP_BASE/full_$DATE
  gbackup --backup-dir=$BACKUP_DIR --backup-type=full --compress \
    --user=gbase --password=your_password --host=$COORD_IP --port=5258
else
  LAST_FULL=$(ls -dt $BACKUP_BASE/full_* | head -1)
  BACKUP_DIR=$BACKUP_BASE/incr_$DATE
  gbackup --backup-dir=$BACKUP_DIR --backup-type=incremental \
    --incremental-basedir=$LAST_FULL --compress \
    --user=gbase --password=your_password --host=$COORD_IP --port=5258
fi

# Remove backups older than 30 days
find $BACKUP_BASE -maxdepth 1 -mtime +30 -exec rm -rf {} \;

Add to crontab: 0 2 * * * /bin/bash /usr/local/scripts/gbase_backup.sh

Backup Readiness Checklist

Does the backup log end with "completed successfully"?
Is the backup file size similar to the previous run?
Perform a restore drill at least once a month.
Are backups stored off‑site?
Does the retention policy meet compliance requirements?

A backup is only as good as your ability to restore it. Run a full restore drill every quarter — when a crisis hits, you'll be glad you did. Protecting your gbase database with a solid backup routine is one of the most important investments a DBA can make.

When Execution Plans Go Wrong in GBase 8a: A Tuning Guide from Statistics to Hints

Michael — Tue, 19 May 2026 09:27:00 +0000

In day-to-day operations of a gbase database, a query that ran in seconds yesterday might suddenly take minutes today — even with indexes in place. The root cause is often stale statistics that mislead the cost‑based optimizer. This guide covers the essential tools: understanding cardinality, using ANALYZE TABLE, reading EXPLAIN output, and applying Hints when necessary.

The Core Metric: Cardinality

The optimizer relies on statistics like row counts and cardinality (the number of distinct values in a column) to estimate how effective an index will be. High cardinality encourages index use; low cardinality may lead to a full table scan. If statistics are out of date, the optimizer makes poor decisions.

-- Check column statistics
SHOW INDEX FROM orders;
-- Alternatively
SELECT TABLE_NAME, COLUMN_NAME, CARDINALITY
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'your_db' AND TABLE_NAME = 'orders';

Refreshing Statistics with ANALYZE TABLE

ANALYZE TABLE updates statistics immediately for new query plans. Existing running queries are not affected.

-- Single table
ANALYZE TABLE orders;
-- Multiple tables
ANALYZE TABLE orders, order_items, customers;

Crucially, after adding a new partition, you must run ANALYZE TABLE. Without it, the optimizer underestimates the partition's size, potentially disabling partition pruning and selecting wrong parallelism.

ALTER TABLE sales ADD PARTITION (PARTITION p202406 VALUES LESS THAN ('2024-07-01'));
ANALYZE TABLE sales;

Diagnosing with EXPLAIN

EXPLAIN SELECT * FROM orders WHERE customer_id = 12345;

If type shows ALL, it's a full table scan. Common causes: missing index, outdated statistics (cardinality is zero or too low), implicit type conversion, or using a function on the indexed column.

Solution workflow:

Check if the index exists (SHOW INDEX).
Run ANALYZE TABLE.
Verify with EXPLAIN again.
If still ALL, inspect the SQL for type mismatches or functions on the index column.

Hint: Emergency Override

When statistics are correct but the optimizer still picks a bad plan, Hints provide a temporary override. Use them sparingly and document why.

JOIN order:

  SELECT STRAIGHT_JOIN a.*, b.name
  FROM large_table a JOIN small_table b ON a.id = b.id;

Index hints:

  SELECT * FROM orders USE INDEX (idx_customer_id) WHERE customer_id = 12345;
  SELECT * FROM orders FORCE INDEX (idx_customer_id) WHERE customer_id = 12345;
  SELECT * FROM orders IGNORE INDEX (idx_create_time) WHERE customer_id = 12345;

Parallelism hint (GBase 8a‑specific):

  SELECT /*+ PARALLEL(4) */ COUNT(*) FROM large_table;

Always verify with EXPLAIN that the hint is being respected.

Complete Tuning Flow

EXPLAIN the slow query.
If type = ALL: check indexes → if missing, create; if present, run ANALYZE TABLE → re‑evaluate.
If JOIN order is wrong: update statistics → if still bad, apply STRAIGHT_JOIN hint.
If statistics are fine but plan remains poor: use targeted hints, record the reason, and schedule a follow‑up review.

Operational Best Practices

Run ANALYZE TABLE immediately after bulk data loads.
Run ANALYZE TABLE immediately after adding partitions.
Schedule a weekly ANALYZE for core business tables.
When troubleshooting slow queries, always start with ANALYZE, then EXPLAIN.
After version upgrades, perform a full ANALYZE and re‑validate critical execution plans.

Keeping statistics fresh is the single most reliable way to keep your gbase database performing predictably. Make "ANALYZE after change" a habit, and you will avoid the vast majority of unexpected plan regressions.

When GBase 8a Execution Plans Go Wrong: Mastering Statistics Management

Michael — Tue, 19 May 2026 08:20:23 +0000

A simple SQL suddenly triggers a full table scan, or a JOIN order looks completely off. Most of the time, the culprit is stale statistics misleading the optimizer. This guide walks through how GBase 8a's statistics work, how to read execution plans, and what to do when the optimizer gets it wrong in a gbase database.

1. What Are Statistics?

The GBase 8a optimizer relies on three layers of statistics to estimate the cost of each execution path:

Table‑level: row count, data pages, average row length.
Column‑level: min/max values, NULL ratio, cardinality (number of distinct values).
Block‑level: rough index bounds (min/max) for each data block.

When these statistics become stale or inaccurate, the optimizer's cost estimates drift — and the execution plan can go from a quick range scan to a painfully slow full table scan.

2. Inspecting Statistics

Start by checking the current state of statistics for your tables:

-- Basic table statistics
SHOW TABLE STATUS FROM analytics_db LIKE 'orders';

-- Column-level statistics
SELECT * FROM information_schema.COLUMN_STATISTICS 
WHERE table_name = 'orders';

-- Index statistics (Cardinality is key)
SHOW INDEX FROM orders;

-- Last update time
SELECT table_name, table_rows, avg_row_length, data_length, update_time
FROM information_schema.TABLES
WHERE table_schema = 'analytics_db' AND table_name = 'orders';

If the Cardinality value is far from reality, an immediate ANALYZE is needed.

3. Reading EXPLAIN Plans

Before updating statistics, use EXPLAIN to see what the optimizer is currently doing:

EXPLAIN SELECT SUM(amount) FROM orders 
WHERE create_time >= '2024-01-01' AND region = 'East';

EXPLAIN PARTITIONS SELECT SUM(amount) FROM orders 
WHERE create_time >= '2024-01-01';

EXPLAIN EXTENDED SELECT SUM(amount) FROM orders 
WHERE create_time >= '2024-01-01';

Key fields to watch:

type: ALL signals a full table scan — the most urgent red flag.
rows: If the estimate is far larger than the actual result set, filtering is inefficient.
Extra: Using filesort or Using temporary deserve attention.
key: NULL means no index is being used.

4. Manually Updating Statistics

Trigger ANALYZE manually in these situations: after bulk loads or deletes, when performance suddenly drops, before the first query on a new table, after adding or dropping partitions, or when Cardinality deviates by over 50% from reality.

-- Single table
ANALYZE TABLE orders;

-- Multiple tables
ANALYZE TABLE orders, users, products;

-- Specific partition (faster than full‑table)
ALTER TABLE orders ANALYZE PARTITION p2024;

Automate with a cron script that runs during off‑peak hours:

#!/bin/bash
TABLES="orders users products regions"
for TABLE in $TABLES; do
    gccli -u gbase -p'password' analytics_db \
        -e "ANALYZE TABLE $TABLE;" >> /var/log/gbase_analyze.log 2>&1
done

# crontab: 0 3 * * * /opt/scripts/gbase_analyze.sh

5. Common Problems and Fixes

5.1 Partition Pruning Disabled

Applying a function to the partition key prevents the optimizer from inferring partition boundaries.

-- Bad: YEAR() forces a scan of all partitions
SELECT * FROM orders WHERE YEAR(create_time) = 2024;

-- Good: direct range condition
SELECT * FROM orders 
WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01';

5.2 Wrong JOIN Order

Run ANALYZE first, then verify with EXPLAIN.

EXPLAIN 
SELECT o.order_id, u.username
FROM orders o JOIN users u ON o.user_id = u.user_id
WHERE o.create_time >= '2024-01-01';

5.3 Large Table JOIN Causing Full Shuffle

Update statistics first. If the plan still looks bad, use a Hint to broadcast the small table.

ANALYZE TABLE users;

SELECT /*+ BROADCAST(u) */
    o.order_id, u.username
FROM orders o JOIN users u ON o.user_id = u.user_id;

6. Using Hints to Override Plans

Hints are a temporary measure, not a permanent fix. Always try statistics and SQL rewriting first.

-- Force JOIN driving order
SELECT /*+ STRAIGHT_JOIN */ 
    o.order_id, u.username
FROM orders o JOIN users u ON o.user_id = u.user_id;

-- Broadcast the small table
SELECT /*+ BROADCAST(u) */
    o.order_id, u.username
FROM orders o JOIN users u ON o.user_id = u.user_id;

-- Set parallelism
SELECT /*+ PARALLEL(4) */
    region, SUM(amount) FROM orders GROUP BY region;

-- Combine hints
SELECT /*+ BROADCAST(u) PARALLEL(8) */
    o.region, u.username, SUM(o.amount)
FROM orders o JOIN users u ON o.user_id = u.user_id
GROUP BY o.region, u.username;

7. Building a Statistics Management Routine

Always ANALYZE after data imports — make it the final step of every load script.
Schedule daily ANALYZE for write‑heavy tables during off‑peak hours (2–4 AM).
Monitor Cardinality drift — alert if table_rows deviates from COUNT(*) by more than 30%.
Periodically compare EXPLAIN plans for critical queries against a stored baseline.

8. Important Notes

ANALYZE TABLE consumes resources; run it on large tables during maintenance windows.
Statistics have a natural lag; don't rely solely on automatic collection for frequently written tables.
Hints are a last resort. Overusing them makes SQL harder to maintain and can become a bottleneck as data evolves.
When execution plans become unstable, always check statistics first — not the hardware or the SQL.
For partitioned tables, ANALYZE new partitions immediately; otherwise the optimizer sees them as empty.

Keeping statistics fresh is the single most impactful maintenance task for query performance in a gbase database. A disciplined ANALYZE routine prevents most optimizer misjudgments before they ever reach production.

How Audit Logs Flow from audit_log to audit_log_express in GBase 8a

Michael — Mon, 18 May 2026 15:52:00 +0000

GBase 8a's audit architecture relies on two key system tables: audit_log and audit_log_express. They form a "local source → centralized archive" relationship. A built‑in scheduled job automatically aggregates audit records from every node into a single cluster‑wide view, enabling both global querying and automatic data aging.

The Two Tables at a Glance

audit_log is where raw logs first land, one copy per node. audit_log_express is the consolidated warehouse for all those records.

Feature	gbase.audit_log	gclusterdb.audit_log_express
Storage	Local to each node; records only that node's events	Centralized, randomly distributed across data nodes
Content	GCluster nodes: full original SQL. GNode nodes: dispatched sub‑task fragments	Aggregated logs from all nodes, with complete context
Retention	No automatic cleanup; manual `TRUNCATE` required	Auto‑aged: keeps only the last 31 days by default
Primary use	Node‑level audit queries and HA data source	Cluster‑wide auditing, long‑term archival, compliance

How Data Flows Between Them

The entire flow is driven by a system‑created event called import_audit_log.

Automatic setup: During cluster installation or upgrade, the system creates the audit_log_express table and the import_audit_log event.
Daily schedule: The event runs once per day, exporting new records from gbase.audit_log on all nodes and merging them into audit_log_express.
Built‑in aging: audit_log_express has a lifecycle policy that automatically deletes records older than 31 days.

Multi‑VC Configuration

In a multi‑Virtual Cluster environment, you must manually bind the import_audit_log event to the correct VC. Without this step, the VC's audit logs won't be collected.

-- Find the VC ID
SHOW VCS;

-- Update the event's VC (run in the gbase database)
UPDATE gbase.event SET vc_id = 'vc00001' WHERE name = 'import_audit_log';

Operational Best Practices

Query audit_log_express for daily audits — it's the complete, query‑friendly view without touching production nodes.
Use TRUNCATE SELF audit_log to safely clear local logs.
In multi‑VC setups, always verify the vc_id of import_audit_log right after deployment.

This design gives your gbase database a lightweight yet powerful audit pipeline: write locally, query globally, and age automatically. It's a solid foundation for compliance and operational visibility in any GBASE environment.

Creating SM4-Encrypted Tables in GBase 8a and Managing Encryption Keys

Michael — Mon, 18 May 2026 15:12:00 +0000

GBase 8a MPP Cluster supports the SM4 national cipher for transparent data encryption at the storage level. To create an SM4-encrypted table, you follow a three-step process: pick the algorithm, create a certificate, and define the table. The encryption key is managed through a centralized certificate and distributed as files across all cluster nodes.

Step 1: Choose the SM4 Algorithm

Set the cluster-wide encryption algorithm to SM4. Once set, it cannot be changed.

SET GLOBAL gbase_encrypt_new_mode = 'SM4';

Step 2: Create an Encryption Certificate

Certificates can be plain or ciphered. Ciphered certificates require a password and offer stronger security.

CREATE ENCRYPTION CERTIFICATE my_sm4_cert
    TYPE = CIPHERED
    PASSWORD = 'YourStrongPassword123!';

The certificate file is automatically distributed to every node's $GBASE_HOME/config/ directory.

Step 3: Create the Encrypted Table

Use the ENCRYPT keyword for table-level or column-level encryption.

-- Table-level: encrypts all columns
CREATE TABLE encrypted_whole_table (
    id INT,
    sensitive_data VARCHAR(200),
    create_time DATETIME
) ENCRYPT;

-- Column-level: encrypts only specific columns
CREATE TABLE customer_info (
    user_id INT,
    name VARCHAR(100),
    id_card_no VARCHAR(20) ENCRYPT,
    phone VARCHAR(20) ENCRYPT,
    email VARCHAR(50)
);

Important: Encryption must be defined at table creation time; it cannot be added or removed via ALTER TABLE.

Using Ciphered Certificates

Before running DML on encrypted columns, you must open the certificate:

OPEN ENCRYPTION CERTIFICATE my_sm4_cert PASSWORD = 'YourStrongPassword123!';

Closing the certificate will cause all subsequent DML on encrypted columns to fail.

Key Management and Storage

Key generation & storage: The key is generated when the certificate is created and stored inside the certificate file. A copy of that file lives on every cluster node.
Certificate types: Plain (no password) or Ciphered (password-protected). You can convert between them.
Management tasks: Backup the certificate file, change the password, query certificate status via information_schema.encryption_certificate_status, and open/close the certificate as needed.
Security model: One cluster‑wide certificate. For ciphered certificates, the password is the user's responsibility — the system does not store or recover it. Protect the certificate file with OS permissions (e.g., chmod 600) and ensure the password meets the cluster's password policy.

Important Restrictions

Once the encryption algorithm is set, it cannot be changed.
Encrypted tables do not support DBLink, and encrypted columns cannot be used as distribution keys.
The performance overhead is minimal — typically less than 5%.

This design gives you strong, transparent encryption for your gbase database while keeping key management clean and compliant with enterprise security requirements. If you're storing sensitive data in GBASE's MPP platform, SM4 encryption is a powerful tool to have in your security toolkit.

Dynamic Data Masking in GBase 8a: How It Works and How to Use It

Michael — Mon, 18 May 2026 14:55:00 +0000

GBase 8a MPP Cluster implements dynamic data masking — the original data never changes on disk. Instead, the database applies masking rules in real time during queries, based on the user's permissions. This post explains the three‑part mechanism: column attributes, built‑in masking functions, and the UNMASK privilege.

How Dynamic Masking Works

1. Define Masked Columns

Use the MASKED WITH clause in DDL to attach a masking function to a column:

CREATE TABLE customer (
    id INT,
    name VARCHAR(100) MASKED WITH (FUNCTION = 'default()'),
    phone VARCHAR(20) MASKED WITH (FUNCTION = 'partial(\"***\", 3, 4)'),
    email VARCHAR(50) MASKED WITH (FUNCTION = 'keymask(\"@\", \"****\", 0)')
);

You can also add masking to an existing column with ALTER TABLE ... MODIFY COLUMN.

2. Five Built‑in Masking Functions

Function	Data Type	Example (Original → Masked)
`default()`	Any	`'Brad Stevens'` → `'XXXX'`
`random(start, end)`	Numeric	`42` → random value in range
`partial(prefix, padding, suffix)`	String	`'Hello'` (keeps first and last char, fills rest)
`sha()`	String	`'Hello'` → SHA hash
`keymask(substr, padding, pos)`	String	`'gbase@gbase.cn'` → `'****@gbase.cn'`

3. The UNMASK Privilege — Who Sees What

Without UNMASK: the user sees the masked result.
With UNMASK: the user sees the original value.

GRANT UNMASK ON db_name.table_name TO user_name@'host';
REVOKE UNMASK ON db_name.table_name FROM user_name@'host';

Dynamic vs. Static Masking

Feature	GBase 8a Dynamic	Static Masking
Storage	Original data untouched	Data permanently replaced
When it happens	Query time	ETL / offline batch
Flexibility	High — different views per user	Low — same masked view for all
Primary use	Production real‑time compliance	Test / dev data provisioning
Built‑in support	Yes, via DDL and privileges	Requires external ETL tools

Because the underlying columnar storage never changes, dynamic masking in a gbase database keeps your analytical workloads fast while meeting security requirements. It's a native, low‑overhead way to protect sensitive data in GBASE's MPP platform.

If you're working with a gbase database in production, consider enabling dynamic masking on PII columns — your compliance team will thank you.

Monitoring and Safely Killing Sessions in GBase 8a

Michael — Mon, 18 May 2026 14:27:28 +0000

Managing user sessions is a core DBA skill in any gbase database. When performance dips or resources are tight, knowing how to list connections and terminate problematic ones safely keeps your cluster running smoothly.

1. Viewing All Connected Sessions

Option A: SHOW PROCESSLIST

A quick overview of threads on the current coordinator.

SHOW PROCESSLIST;

Key columns:

Id – the unique session identifier, used later with KILL.
User, Host – who is connected and from where.
Time – seconds the session or current query has been running.
State – e.g., Sleep (idle), Query (executing).
Info – the running SQL; empty means the session is idle.

Option B: information_schema.processlist (Recommended)

This view supports filtering and sorting, making it the go‑to for analysis.

SELECT id, user, host, db, command, time, state, info
FROM information_schema.processlist
WHERE command != 'Sleep'
ORDER BY time DESC;

Use it to quickly spot long‑running, resource‑heavy sessions.

2. Safely Terminating a Problem Session

Before running any kill command, identify the right session and choose the least disruptive method.

Termination Options

Command	Effect	When to Use	Impact
`KILL CONNECTION <id>` (or `KILL <id>`)	Disconnects the session, rolls back uncommitted work	Frozen sessions, unresponsive connections	High
`KILL QUERY <id>`	Cancels only the current SQL statement, keeps the connection alive	A runaway query that doesn't need a full disconnect	Lower
`PAUSE <id>` / `CONTINUE <id>`	Temporarily pauses / resumes execution	Quick resource relief for diagnostics	Minimal

Examples:

-- Terminate the entire session
KILL 5593;

-- Cancel only the running query
KILL QUERY 5593;

Safe Procedure

Identify: find queries running longer than 5 minutes.

   SELECT id, user, time, state, info
   FROM information_schema.processlist
   WHERE command = 'Query' AND time > 300
   ORDER BY time DESC;

Evaluate: check State (Sending data, Sorting result) and review the SQL in Info for anomalies.
Kill: prefer KILL QUERY to preserve the connection; escalate to KILL CONNECTION only if needed.
Verify: run SHOW PROCESSLIST again to confirm the session is gone or its state has changed.

3. Important Notes

Privileges: PROCESS is required to view all sessions; SUPER (or equivalent) is needed for KILL.
Multi‑VC clusters: switch to the target VC first:

  USE VC vc_name;
  SHOW PROCESSLIST;

Prevention: use resource pools to cap per‑query resources, set task_running_timeout to auto‑cancel long queries, and leverage GDOM for visual monitoring.

The golden rule for terminating sessions: diagnose first, then act; use KILL QUERY whenever possible. This keeps your gbase database healthy while minimizing business disruption.