DEV Community: GBASE Database

GBase 8a Optimization Techniques for Precise Queries

GBASE Database — Thu, 12 Mar 2026 07:02:09 +0000

“In database systems, the fastest query is not just about hardware power, but about how intelligently the data is located and returned.”

This article summarizes several known optimization techniques for precise query scenarios in the GBase 8a MPP Cluster database.
The focus is on the simplest form of exact-match queries, where the query condition is already known.

The techniques discussed include:

Hash distribution
Global hash index
Row-storage columns
Parallel materialization
Pre-sorting

More optimization methods may be added in the future as additional practices are verified.

References

Performance improvement test of Global Hash Index in GBase 8a
Parallel materialization parameter gbase_parallel_threshold in GBase 8a
Performance improvement test of row-storage grouped columns in GBase 8a
Improving simple queries with LIMIT using the first_rows hint in GBase 8a

What Is a Precise Query?

A precise query refers to an equality-based query where the exact value is specified.

For example, retrieving the call records of a specific phone number on a specific date:

SELECT * 
FROM MyTable_20210808 
WHERE calling_number = 13912345678;

Optimization Analysis

From a database execution perspective, this type of query can be divided into two stages:

Location stage
Identify which rows match the query condition.
Result retrieval stage
Read the full row data and return the result set.

“Efficient queries are essentially a two-step dance: find the rows quickly, then assemble the results efficiently.”

Location Stage

This stage determines which rows satisfy the given condition.

Global Hash Index

Indexes are the most common method for improving query performance.

In the GBase 8a database, this stage is optimized using a hash index.

This index works best for low-duplication columns, such as:

Phone numbers
ID numbers

For columns with high duplication, such as:

Ethnicity
Region

performance may actually decrease.

A practical rule of thumb:

If the duplication rate is below 0.1% (1‰), the column can generally be considered suitable for a hash index.

Actual performance should always be validated through testing.

Pre-Sorting

Sorting data based on query columns can increase the hit rate of the intelligent indexing mechanism used in the GBase database engine.

Result Retrieval Stage

In a traditional row-based database, once a row is located, retrieving the entire row is straightforward.

However, in a columnar database, the system must reconstruct a row by combining data from multiple columns.

The more columns involved, the greater the cost of this reconstruction.

In GBase 8a, this process is called Materialization.

Row-Storage Columns

GBase allows certain columns to be stored using row-based storage.

This approach trades storage space for faster query performance, improving efficiency during result reconstruction.

Parallel Materialization

In scenarios with:

small query volumes
low concurrency
limited disk resource contention

parallel materialization can be considered.

When the result set is small and system resources are not heavily contested, adjusting the parameter:

gbase_parallel_threshold

can enable parallel result materialization and improve query performance.

Pre-Sorting (Again)

If data is stored sorted by the query column, the matching rows are typically physically adjacent.

This dramatically reduces disk I/O and can significantly improve query performance.

However, sorting has trade-offs:

Sorting large datasets takes time
The larger the sorting range, the longer the sorting operation
A table can have only one primary sorting column

In many cases, this approach works well for historical data, where data is written once but queried many times.

Choosing a Hash Distribution Column

Whether a query column should be used for hash distribution depends largely on query concurrency.

Hash distribution ensures that rows with the same key value are stored on the same node.

Low-Concurrency Scenarios

If only a small number of queries are executed simultaneously:

Hash distribution is not recommended.

Allowing more nodes to participate in the query computation can actually improve performance.

High-Concurrency Scenarios

A classic example comes from the telecommunications industry, where customers frequently query call detail records (CDR).

Although each user may only have tens or hundreds of records per month, there may be:

hundreds
or even thousands

of users querying simultaneously.

At peak times, especially at the beginning of the month, the concurrency can increase even further.

In such cases, hash distribution on the query column is recommended.

With hash distribution:

queries for the same condition can be completed on a single node
different queries are likely distributed across different nodes

This reduces resource contention across the cluster and increases the overall number of parallel queries the database can support.

It also makes capacity planning during cluster expansion much easier.

Precise Queries with LIMIT

For exact-match queries that include a LIMIT clause, query performance can be further optimized using the first_rows hint.

This technique allows the GBase database optimizer to prioritize returning the first rows of the result set more quickly.

“Database optimization is rarely about a single trick. It is usually a combination of distribution strategy, indexing design, and execution tuning working together.”

GBase Cloud Data Warehouse (GCDW): Unlocking New Potential in Data Utilization

GBASE Database — Tue, 11 Mar 2025 08:01:25 +0000

Built on the foundation of GBase 8a database, the cloud-native data warehouse GCDW effectively integrates multi-source data and provides a unified view and supports complex queries and analysis. It significantly enhances data utilization efficiency and decision support capabilities, injecting new momentum into industry innovation and development.

Maximizing Data Value Across All Dimensions

Enhanced Resource Utilization Efficiency

Server Reuse and Cost Reduction: By leveraging server reuse strategies, GCDW significantly improves hardware utilization efficiency, reducing procurement and maintenance costs while maximizing the value of existing resources.
Resource Assurance for Concurrent Operations: GCDW effectively alleviates resource constraints, enabling businesses to run multiple services in parallel with strong resource guarantees. This flexibility empowers enterprises to manage and allocate resources efficiently.

Enhanced Business Continuity Assurance

Disaster Recovery System: GCDW builds a multi-center, multi-cluster PB-level disaster recovery system with robust backup, recovery, and active-active synchronization capabilities to ensure data integrity.
Stable Operations Guarantee: Even in cases of unexpected failures or natural disasters, GCDW maintains continuous business operations, preventing economic losses and reputational damage, making it a key pillar of stable enterprise operations.

Efficient Data Management and Value Extraction

Tool-Driven Process Optimization: GCDW offers practical tools for data migration, backup management, and GVR synchronization, streamlining data migration and simplifying backup operations.
Data-Driven Business Decisions: GCDW achieves efficient and accurate data synchronization, improving data management efficiency and quality. This ensures precise data support for analytics and decision-making, driving business growth.

Enhanced Business Performance Optimization

Precise Application Matching: GCDW optimizes resource allocation and scheduling strategies to meet performance requirements, especially for scenarios involving wide tables with large memory footprints and numerous nodes.
Improved Customer Experience: By accelerating data processing speeds and improving response times, GCDW enhances the overall user experience, helping enterprises excel in competitive markets.

Intelligent Operations and Maintenance System

Comprehensive Monitoring System: GCDW builds a monitoring system that covers cluster-level to tenant-level metrics, offering real-time insights into system performance.
Proactive Alerting and Issue Resolution: GCDW quickly identifies potential performance bottlenecks and issues, providing early warnings and swift resolutions to maintain system stability and ensure smooth business operations.

Improved Resource Management Efficiency

Accurate Capacity Management: Leveraging monitoring data at both the cluster and resource layers, GCDW enables precise capacity management.
Optimized Resource Planning: GCDW helps users gain insights into resource utilization patterns, ensuring accurate resource allocation to prevent waste or shortages, ultimately reducing operating costs and improving overall efficiency.

Accelerated Cloud Transformation

Compatibility Analysis and Guidance: GCDW conducts in-depth analysis of business requirements and cloud adaptability, providing clear direction for cloud migration and innovation.
Expanding Service Boundaries: By leveraging cloud warehouse features, GCDW drives innovation, expands service boundaries, and supports personalized product development, addressing diverse customer needs and fueling business growth.

Driving Industry Development with Key Contributions

Leading Resource Efficiency Practices

Model Promotion: GCDW showcases efficient resource utilization practices such as server reuse and refined resource allocation, setting a benchmark for industry peers.
Sustainable Development Support: GCDW encourages resource conservation and improved utilization, contributing to sustainable growth within the industry.

Setting a New Standard in Disaster Recovery Technology

Advanced Disaster Recovery Capability: GCDW's industry-leading PB-level disaster recovery capabilities provide a strong reference standard for disaster recovery system design.
Enhanced Risk Mitigation: By strengthening data security and business continuity, GCDW helps other enterprises reduce systemic risks and maintain market stability.

Driving Data Management Innovation

Innovative Tools and Solutions: GCDW offers advanced tools and methodologies for data migration, backup, and synchronization, providing fresh ideas and practical solutions for the industry.
Upgraded Data Management Techniques: By enhancing data quality and management efficiency, GCDW lays a solid foundation for data-driven business innovation.

Advancing Performance Optimization Trends

Technology Sharing and Best Practices: GCDW's achievements in meeting complex application performance needs provide valuable insights for industry peers.
Improved Industry Competitiveness: By helping businesses enhance system performance, improve service quality, and boost efficiency, GCDW supports overall industry growth and competitiveness.

Establishing a New Monitoring System Paradigm

Optimization Experience Sharing: GCDW's experience in refining monitoring systems from node-level to cluster-level and tenant-level serves as a valuable reference for enhancing monitoring frameworks.
Stable Technology System Assurance: GCDW helps peers implement comprehensive monitoring for cloud-native architectures, ensuring stable technology system operations and fostering healthy industry growth.

Catalyzing Innovation in Technology Applications

Practical Case Studies: GCDW's successful implementations in data sharing, active-active synchronization, and integrated data lake/warehouse scenarios provide practical references for new technology adoption.
Leading Industry Innovation: By helping enterprises evaluate and apply new technologies, GCDW accelerates innovation adoption and drives the industry toward a new era of digital transformation.

Exploring GBase 8s Window Functions: Unlocking Data Analysis Tools

GBASE Database — Mon, 10 Feb 2025 09:58:34 +0000

Today, we will explore the Window Functions in the GBase 8s database, focusing on the Window Frame clause and how it helps us process and analyze data more efficiently.

Window functions are a tool introduced in the SQL standard that allows us to perform calculations on a set of rows (called a window) in a query without losing row details, as traditional aggregate functions do. With window functions, we can perform complex calculations such as cumulative sums, moving averages, rankings, etc., while maintaining the integrity of the data rows. The Window Frame clause is an essential part of window functions, defining the scope of the window, i.e., which rows will be included in the calculation. By flexibly defining the window scope, we can meet various complex data analysis requirements.

Overview

The Window Frame clause returns a subset of rows within each window partition. The frame clause specifies how this subset is defined by a specific number of rows or a range of values. The frame is determined relative to the current row, allowing the frame to move within the partition based on the current row's position.

For example:

By defining a frame as all rows from the start of the partition to the current row, we can calculate the cumulative sum up to the current row.
By defining a frame as extending N rows on either side of the current row, we can calculate a rolling average.

Syntax

Key Terms in the Window Frame Clause

The following keywords define the scope of the window:

Element	Description
OFFSET	An unsigned integer representing the offset from the current row position. Cannot be negative. If zero, it specifies the current row.
RANGE	A value-based frame clause.
ROWS	A row-based frame clause.
UNBOUNDED	All rows from the current row to the limit of the window partition.
UNBOUNDED PRECEDING	The starting boundary is the first row in the partition.
UNBOUNDED FOLLOWING	The ending boundary is the last row in the partition.
PRECEDING	Specifies a negative offset from the current row.
FOLLOWING	Specifies a positive offset from the current row.
CURRENT ROW	The current row.

Row-Based (ROWS) Window Frame

The ROWS keyword creates a row-based window frame. The frame is defined by the positions of the starting and ending rows, where the offset represents the number of rows to return.

Example 1: Return the current row and the previous 6 rows.

avg(price) OVER (ORDER BY year, day 
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)

Example 2: Requirement: Partition employees by department, sort by salary, and return the average salary of the current employee and the previous employee within each partition.

-- Create table
CREATE TABLE employees (
   id serial PRIMARY KEY,
   emp_id VARCHAR(20) NOT NULL,
   name VARCHAR(20) NOT NULL,
   age int NOT NULL,
   salary decimal(10,2) NOT NULL
);

-- Insert data
INSERT INTO employees (emp_id, name, age, salary) VALUES
('E00A', 'carry', 18, 5000),
('E00A', 'lili', 28, 5500),
('E00B', 'tom', 22, 6000),
('E00B', 'amy', 25, 6500),
('E00B', 'chun', 26, 7000),
('E00C', 'zouzou', 25, 5200),
('E00D', 'candy', 23, 5800),
('E00D', 'mumu', 29, 6200);

-- Query: Partition by department, sort by salary, and calculate the average salary of the current and previous employee.
SELECT 
   emp_id, name, salary,
   AVG(salary) OVER (
       PARTITION BY emp_id
       ORDER BY salary
       ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
   ) AS avg_salary 
FROM employees;

-- Result
EMP_ID   NAME    SALARY       AVG_SALARY
E00A     carry   5000.00      5000.000000000
E00A     lili    5500.00      5250.000000000
E00B     tom     6000.00      6000.000000000
E00B     amy     6500.00      6250.000000000
E00B     chun    7000.00      6750.000000000
E00C     zouzou  5200.00      5200.000000000
E00D     candy   5800.00      5800.000000000
E00D     mumu    6200.00      6000.000000000
8 row(s) retrieved.

Value-Based (RANGE) Window Frame

The RANGE keyword creates a value-based frame clause. The frame is defined by rows within a specific value range, where the offset is the difference between the row value and the current row value. The frame consists of the current row and rows that meet the criteria set by the sorting key in the ORDER BY clause and the specified offset. The offset represents the number of units of the sorting key's data type. The sorting key must be numeric, DATE, or DATETIME. For example, if the sorting key is of DATE type, the offset represents a specific number of days.

Example 1: Return rows with dates within 2 days before or after the current row.

COUNT(*) OVER (ORDER BY ship_date
RANGE BETWEEN 2 PRECEDING AND 2 FOLLOWING)

Example 2: Requirement: Partition employees by department, sort by age, and return the average salary of employees whose age differs by no more than 5 years.

SELECT 
   emp_id, name, age, salary,
   AVG(salary) OVER (
       PARTITION BY emp_id
       ORDER BY age
       RANGE BETWEEN 5 PRECEDING AND 5 FOLLOWING
   ) AS avg_salary 
FROM employees;

-- Result
emp_id   NAME    age   salary    avg_salary
E00A     carry   18    5000.00   5000.000000000
E00A     lili    28    5500.00   5500.000000000
E00B     tom     22    6000.00   6500.000000000
E00B     amy     25    6500.00   6500.000000000
E00B     chun    26    7000.00   6500.000000000
E00C     zouzou  25    5200.00   5200.000000000
E00D     candy   23    5800.00   5800.000000000
E00D     mumu    29    6200.00   6200.000000000

In the E00A partition, the age difference between the two employees is greater than 5 years, so the average salary is their own salary. In the E00B partition, the age difference is within 5 years, so the average salary includes the other employee's salary.

Conclusion

Through the above introduction, we can see that the Window Functions and Window Frame clause in GBase 8s provide powerful data analysis capabilities. Whether it's precise row-based control or flexible value-based processing, window functions help us quickly achieve complex calculation requirements. In practical applications, we can choose the appropriate window type and scope based on specific needs to process and analyze data more efficiently.

We hope this article helps you better understand and apply window functions, unlocking new perspectives in data analysis. If you have any questions or suggestions about window functions or GBase 8s, feel free to leave a comment. Let's learn and grow together!

Summary of Common Issues with GBase 8c Primary-Standby Cluster

GBASE Database — Mon, 10 Feb 2025 07:24:00 +0000

GBase 8c is a multi-model database representing the third generation of intelligent distributed database products from GBASE. It supports various storage modes and deployment forms, offering high performance, high availability, high intelligence, high security, and strong compatibility. This article primarily describes failure cases caused by operating system versions, environmental issues, etc., that lead to installation failures. It aims to help users quickly locate problems, analyze causes, and provide solutions or ideas to proficiently use the GBase 8c database.

0. Fault Classification

Basic environment not ready
System version/environment issues

1. Basic Environment Not Ready

Preparation before installation mainly involves checking operating system information, whether dependency packages are installed, whether the hostname is effective, whether the firewall is closed, whether the root user passwords are consistent across multiple machines, whether the time is synchronized, etc. Specific steps can be referred to as follows:

1.1 Check Operating System Information

1) Whether the ID information is in the json file

# cat /etc/os-release                      # General command for Linux operating systems
NAME="Kylin Linux Advanced Server"
VERSION="V10 (Tercel)"
ID="kylin"
VERSION_ID="V10"
PRETTY_NAME="Kylin Linux Advanced Server V10 (Tercel)"
ANSI_COLOR="0;31"

# cat support_system_info.json
{
 "SUSE": "suse",
 "REDHAT": "redhat",
 "CENTOS": "centos",
 "EULEROS": "euleros",
 "KYLIN": "kylin",
 "KYLINSEC": "kylinsecos",
 "OPENEULER": "openeuler",
 "FUSIONOS": "fusionos",
 "ASIANUX": "asianux",
 "DEBIAN": "debian",
 "UBUNTU": "ubuntu",
 "NFS": "nfs",
 "UOS": "uos",
 "BCLINUX": "bclinux",
 "NEOKYLIN": "neokylin",
 "OPENKYLIN": "openkylin"
}

2) If it is a Kylin operating system, you also need to check whether the current version is based on Ubuntu, using the following command (either one will do):

# cat /etc/.kyinfo 
# cat /etc/os-release

In the returned version information, if the dist_id parameter value starts with "Kylin-Desktop", it is an Ubuntu-based operating system.

You need to replace bash. Use the following command:

# sudo dpkg-reconfigure dash

During the process, select No and press Enter. After exiting, it will automatically switch to bash.

3) For Kylin operating systems, you need to disable security authorization authentication

# setstatus softmode -p

4) CentOS/RHEL 7.2+ environment

The systemd-logind service introduces a new feature—RemoveIPC, which manifests as: files created after a user logs in will be automatically deleted after logout. In the default case (i.e., RemoveIPC=yes), when a user logs out, the operating system will crash applications that use Shared Memory Segment (SHM) or Semaphores (SEM), causing the GBase database process to be interrupted. To avoid such problems, follow these steps:

(1) Check if the RemoveIPC parameter value is yes

# loginctl show-session | grep RemoveIPC
# systemctl show systemd-logind | grep RemoveIPC

If it is yes, it needs to be modified; if it is no, there is no need to continue with the following steps.

(2) Modify the /etc/systemd/logind.conf configuration file

# vim /etc/systemd/logind.conf

Set the RemoveIPC parameter value to no, type ":wq" to save and exit.

Modify the /usr/lib/systemd/system/systemd-logind.service configuration file, set the RemoveIPC parameter value to no.

# vim /usr/lib/systemd/system/systemd-logind.service

Set the RemoveIPC parameter value to no, type ":wq" to save and exit.

(3) Reload the configuration file, execute the following commands:

# systemctl daemon-reload
# systemctl restart systemd-logind

(4) Check again to see if it takes effect.

# loginctl show-session | grep RemoveIPC
# systemctl show systemd-logind | grep RemoveIPC

1.2 Check CPU Architecture

Confirm whether it is x86 or aarch64

# lscpu |grep 'Architecture'
  Architecture:                    x86_64

It needs to be consistent with the information in the downloaded package name, for example:

GBase8cV6_SXXXXX_centos7.8_x86_64.tar.gz                                                              # x86 package
GBase8cV6_SXXXXX_centos7.8_aarch64.tar.gz                                                             # aarch64 package

1.3 Pre-installation Dependency Check

# rpm -qa|egrep "libaio-devel|flex|bison|ncurses-devel|glibc-devel|patch|redhat-lsb-core|readline-devel|libnsl|expect|patchelf|bzip2"

Installation command:

# yum install -y libaio-devel flex bison ncurses-devel glibc-devel patch redhat-lsb-core readline-devel libnsl expect patchelf  bzip2

1.4 Confirm Hostname Consistency

Modify the hostname. Even for single-machine deployment, it is not recommended to use the default localhost.

Execute the command to view the hostname:

# cat /etc/hostname
# hostname

Modification method:

# hostnamectl set-hostname  gbasedb01

1.5 Create Database OS Management User

Take gbase as an example, it is not recommended to use the graphical interface to create

# groupadd gbase
# useradd -m -d /home/gbase gbase -g gbase
# passwd gbase

1.6 Close the Firewall

# systemctl stop firewalld.service
# systemctl disable firewalld.service
# sed -i.bak '/^SELINUX=/s#SELINUX=.*#SELINUX=disabled#' /etc/selinux/config
# setenforce  0

If the environment requires it to be open, you can refer to the following configuration:

# systemctl start firewalld
# firewall-cmd --zone=public --add-port=15400/tcp --permanent
# firewall-cmd --zone=public --add-port=15300/tcp --permanent
# firewall-cmd --zone=public --add-port=15301/tcp --permanent
# firewall-cmd --zone=public --add-port=15302/tcp --permanent
# firewall-cmd --zone=public --add-port=15405/tcp --permanent
# firewall-cmd --zone=public --add-port=15401/tcp --permanent
# firewall-cmd --zone=public --add-port=5000/tcp --permanent
# firewall-cmd --zone=public --add-port=5001/tcp --permanent
# firewall-cmd --zone=public --add-port=5002/tcp --permanent
# firewall-cmd --zone=public --query-port=15400/tcp
systemctl restart firewalld

1.7 Multi-node Environment Installation Requires Checking Time Consistency

# date

Modification method:

# hwclock --show
# hwclock --systohc

Note: During the database startup process, time inconsistency will cause the startup to hang. When manually modifying the time, you need to modify the current time to a future time.

1.8 Check Maximum Open Files

The requirement is not less than 640000

# ulimit -n

Modification method:

# echo 'ulimit -n 1000000' >> ~/.bashrc

2. System Version/Environment Issues

2.1 Multi-node Installation, Root Password Inconsistency

Solution:

Temporarily adjust to be consistent
If it cannot be changed, follow the steps below to install

① Execute gs_preinstall under root on each machine, place the installation package and xml file in the same directory, and add the -L command to gs_preinstall to only perform local installation

./gs_preinstall -U gbase  -G gbase  -X /opt/cluster.xml -L

② Switch to the gbase user, establish mutual trust for the gbase user, after establishment, execute "ssh hostname", "ssh ip" to log in to the local machine and other machines, refresh the cache information (refer to the ssh-free configuration issue for details)

③ Execute the installation on any server

gs_install -X /opt/cluster.xml

2.2 SSH-free Configuration Issue

Complete password-free configuration reference is as follows (can avoid ssh permission issues)

# su - gbase
$ rm -rf /home/gbase/.ssh
$ mkdir ~/.ssh
$ chmod 700 ~/.ssh
$ ssh-keygen -t rsa

$ ssh-copy-id gbase@172.16.xx.xxx
$ ssh-copy-id gbase@172.16.xx.xxx

$ echo 'StrictHostKeyChecking no' >> ~/.ssh/config
$ echo 'UserKnownHostsFile ~/.ssh/known_hosts' >> ~/.ssh/config
$ chmod 644 ~/.ssh/config

Verification test (both IP and hostname need to be tested)

$ ssh 172.16.xx.xxx  date
$ ssh 172.16.xx.xxx  date
$ ssh gbasedb01  date
$ ssh gbasedb01  date

If during verification, it is found that ssh ip works but ssh hostname does not, ping the hostname to confirm if it is ipv6. If it is ipv6, perform the following operations:

echo 'precedence ::ffff:0:0/96 100' | sudo tee -a /etc/gai.conf

2.3 Environment Issues

(1) Error xxxx list index out of range during installation

Check the xml file during installation, focusing on whether the hostname and IP address are configured correctly.

(2) Error "importerror:libffi.so.6:cannot open shared object file: no such file or directory" during installation.

This may be due to the server's CPU architecture being ARM version, and the residual files from previously installed X86 version packages. In this case, according to the error message, delete all corresponding residues.

ll /lib64/libffi.so.6*
sudo rm -rf /lib64/libffi.so.6*

(3) Error "cannot execute binary file" during installation.

This is due to the GBase 8c installation package not matching the CPU architecture. According to the deployment environment, replace the corresponding version of the installation package. For details, see 1.2 Check CPU Architecture.

2.4 VIP Configuration Issue After Installation

1) VIP address does not float

Check if sudo permissions are configured on all machines

$ su - gbase
$ sudo -l

Check if VIP is configured on the standby machine

$ sudo find /* -name 'cm_resource.json'

2) The entire cluster is unavailable when the primary node is down

Check if the VIP information is saved in the cm_server configuration

cm_ctl  list --param --server| grep "third_party_gateway_ip"

If the above query result is empty, configure as follows:

cm_ctl set --param --server -k "third_party_gateway_ip= gateway address"
cm_ctl set --param --server -k "cms_enable_db_crash_recovery=1"
cm_ctl set --param --server -k "cms_network_isolation_timeout=10"
cm_ctl set --param --server -k "cms_enable_failover_on2nodes=1"

This article aims to provide a comprehensive guide to troubleshooting common issues encountered during the installation and configuration of GBase 8c primary-standby clusters. By following the detailed steps and solutions provided, users can ensure a smooth and successful deployment of the GBase 8c database. For further assistance, refer to the official documentation or reach out to the support team.

Using GBase 8a Kafka Consumer for Data Synchronization

GBASE Database — Mon, 10 Feb 2025 07:01:28 +0000

Configuration of Data Synchronization

Parameter Configuration

To use the Kafka consumer, configure the parameters as follows. Refer to the supplementary notes for details on adjustable parameters.

GCluster Configuration

Edit the file: /opt/gcluster/config/gbase_8a_gcluster.cnf

_gbase_transaction_disable=1 (Ensure this is not set to 0)
gcluster_lock_level=10 (Not recommended to use 2)
_gcluster_insert_cache_buffer_flag=1
gcluster_assign_kafka_topic_period=20
gcluster_kafka_max_message_size=1000000
gcluster_kafka_batch_commit_dml_count=100000
gcluster_kafka_local_queue_size=210000
gcluster_kafka_consume_batch=100
gcluster_kafka_user_allowed_max_latency=15

Adjustable Parameters:

gcluster_assign_kafka_topic_period: Time period in seconds for automatic consumer takeover. Min: 20s, Max: 120s.
gcluster_kafka_max_message_size: Maximum message size in bytes from Kafka topic. Must be >= Kafka server's message.max.bytes.
gcluster_kafka_batch_commit_dml_count: Number of DML operations per commit. Adjust based on table count and performance.
gcluster_kafka_user_allowed_max_latency: Maximum latency in milliseconds for message caching in the 8a cluster layer.
gcluster_kafka_local_queue_size: Length of the DML operation queue. Recommended to be more than double gcluster_kafka_batch_commit_dml_count.
gcluster_kafka_consume_batch: Number of Kafka messages consumed at once. Recommended range: 10-1000.

GNode Configuration

Edit the file: /opt/gnode/config/gbase_8a_gbase.cnf

_gbase_transaction_disable=1 (Ensure this is not set to 0)
gbase_tx_log_mode=ONLY_SPECIFY_USE (Do not use USE,STANDARD_TRANS)
gbase_buffer_insert=1024M
gbase_tx_log_flush_time=5

Adjustable Parameters:

gbase_buffer_insert: Size of the insert buffer. Adjust based on data volume and consumer tasks.
gbase_tx_log_flush_time: Frequency in seconds for flushing in-memory data. Recommended: 5 seconds.

Note: Ensure all nodes have consistent configurations to avoid unknown errors.

Commands for Starting and Stopping

Execute the following commands on any Coordinator node using gccli.

Creating a Consumer Task

CREATE KAFKA CONSUMER <consumer_name> TRANSACTION TOPIC <kafka_topic_name> BROKERS 'ip:port, ip:port,…';

consumer_name: Unique name for the consumer task.
kafka_topic_name: Name of the Kafka topic to consume.
ip:port: Kafka broker's IP and port.

Example:

CREATE KAFKA CONSUMER test1 TRANSACTION TOPIC topic_1 BROKERS '10.10.10.10:9092,10.10.10.11:9092';

Deleting a Consumer Task

DROP KAFKA CONSUMER <consumer_name>;

Ensure the consumer task is stopped before deletion.

Viewing Consumer Task Properties

SHOW KAFKA CONSUMER <consumer_name>;
SHOW TRANSACTION CONSUMER;

Starting a Consumer Task

START KAFKA CONSUMER <consumer_name>;

Stopping a Consumer Task

STOP KAFKA CONSUMER <consumer_name>;

Status Query

SELECT * FROM information_schema.kafka_consumer_status;

Fields:

Consumer: Name of the consumer.
IP: Node IP where the consumer task is running.
Topic: Kafka topic name.
Status: Start or stop state.
Min_offset: Minimum offset in the Kafka queue.
Max_offset: Maximum offset in the Kafka queue.
Cur_offset: Current offset being processed by 8a.
Process_offset: Offset of the message currently being synchronized.
Commit_offset: Offset of the last committed message.
Exception: Description of the last error encountered.

Handling Exceptions in Consumer Tasks

JSON Parsing Errors

If a JSON message parsing error occurs, the consumer task will enter a sleep state. Check the exception field for details. Use Kafka's consumer tool to inspect the problematic message:

$kafka_home/bin/kafka-simple-consumer-shell.sh --broker-list 192.168.103.74:9092 --topic test3 --offset 797 --max-messages 1 --partition 0

Cluster Lock Failure or Node Offline

If cluster lock failure or node offline occurs, the consumer task will exit and wait for task scheduler reassignment. Check the status for status=waiting start, IP=unknown.

GBase Single Node Execution Error

If an error occurs during single node execution, the consumer task will enter a sleep state. Address the issue and restart the consumer task.

Common Exception Descriptions

JSON Parsing Issues: Indicates a malformed JSON message.
Missing Primary Key: Indicates a missing primary key in the JSON message.
Invalid Primary Key: Indicates an invalid primary key in the JSON message.
Target Table Does Not Exist: Indicates the target table is missing in GBase 8a.
Table Definition Mismatch: Indicates a mismatch in table definitions.
Primary Key Changed: Indicates a change in the primary key.
Data Insertion Error: Indicates an error during data insertion, such as data truncation.

Note: Always ensure the JSON messages and table definitions are correct to avoid synchronization issues.

GBASE 数据库 | Physical and Logical Logs in GBase 8s Database

GBASE Database — Sat, 08 Feb 2025 08:24:39 +0000

In database management, the logging system is a critical component for ensuring data integrity and system recovery. In GBase 8s, both physical and logical logs play vital roles in system recovery and data management. This article explores the structure, functionality, and importance of these logs in database management.

Physical Logs: The Foundation of Fast Recovery

Physical logs record all physical operations in the database, such as modifications, insertions, and deletions of data pages. These records are essential for restoring the database to its state before a system failure.

Key Facts About Physical Logs:

Used during fast recovery.
Often set too small in most customer systems, leading to frequent checkpoints.
Can be moved outside the root dbspace, which is generally recommended.
Crucial for GBase 8s's fast recovery mechanism and its archiving algorithm, especially in environments prone to power failures and disk crashes.

Common Pitfalls:

The initial size of the physical log is often too small, causing frequent checkpoints. This is a common mistake made by novice administrators.

Physical Log Page Structure:

The page structure of the physical log is similar to that of regular data pages but with key differences. Physical log pages contain the address of the original page location rather than the physical location in the log. This allows for accurate data restoration during recovery.

The image above shows the structure of a physical log page. The page in the physical log is identical to its original page. The only way to determine that a page is from the physical log is by examining the page address (offset and block). The page contains the address of the original page location, not its physical location in the log.

Logical Logs: Ensuring Data Consistency

Logical logs can be thought of as a sequence of log pages, each with a page offset equivalent to a logical page number.

Key Characteristics:

Each logical log is a continuous sequence of log pages, numbered starting from 0.
Log files are segments within a block and are continuously overwritten. They serve as temporary hosts for logical logs.
Logical logs can contain any number of pages, from 1 to the total available in the log file.
Logical log pages are generated one after another in the logical log buffer and written out sequentially each time the buffer is flushed.

Logical Log Page Structure:

As shown in the image above, the structure of a logical log page suggests a sequential method of accessing log records. For example, note that the page does not have a slot table (the length of each record is stored within the record itself). Also, logical log data flows smoothly from one page to the next without interruption (though records must be entirely contained within the log file). Even the page header has some unique attributes.

Unique Attributes of the Page Header:

pg_nslots: Unused, as there is no real concept of slots on log pages.
pg_frcnt: Always zero, even for partially filled pages (note that pg_frptr is accurate). This is more coincidental than by design.
pg_next: Contains the unique ID of the logical log.
pg_prev: Contains the page offset (similar to a logical page number) of the log page. Note that this is the offset within the log, starting from 0.

Logical Log Position:

Log records are uniquely addressed within a specific logical log. The address used is called the logical log position, or logpos. It is a 4-byte integer code that describes the location of the log record based on the page offset and byte offset. The page offset is relative to the start of the log, indexed from 0. The byte offset is relative to the start of the page, also indexed from 0, but should not be less than 0x018 due to the space occupied by the page header.

For example, if you are told that a logical log record is located in logical log 234 with a logpos of 0x12018, you would:

Use the newer of the two checkpoint/logical log retention pages in the root chunk to find the physical address of log number 234.
Once at that page, you would offset 0x12 pages within the log to find the correct log page.
Then, offset 0x018 bytes within that page to find the log record.

Displaying Logical Logs

Displaying All Logical Log Files:

Use the onstat -l command to display all logical log files. Logical log files have two numbers:

File Number (number): This number remains constant.
Unique ID (uniqid): Since logical log files are reused cyclically, a unique ID is needed to distinguish them.

[root@node2 ids]# onstat -l
On-Line -- Up 16 days 03:16:37 -- 19345028 Kbytes

Physical Logging
Buffer bufused  bufsize  numpages    numwrits   pages/io
 P-2   98       256      13934      66          211.12
     phybegin         physize    phypos      phyused    %used
     2:53             32715      25656      4971       15.19

Logical Logging
Buffer bufused  bufsize  numrecs     numpages   numwrits   recs/pages pages/io
 L-3   0        128      1408526     154832     116507     9.1        1.3
       Subsystem    numrecs     Log Space used
       OLDRSAM      1408450    141317188
       HA           6          264
       DDL          70         24360

address          number   flags     uniqid   begin                size     used     %used
45b79a60         14       U-B----  121       3:29483              3270     3270    100.00
45b79ac8         13       U-B---L  122       3:26213              3270     3270    100.00
45b79b30         12       U-B----  123       3:22943              3270     3270    100.00
45b79b98         11       U-B----  124       3:19673              3270     3270    100.00
45b79c00         10       U-B----  125       3:16403              3270     3270    100.00
45b79c68         9        U-B----  126       3:13133              3270     3270    100.00
45b79cd0         8        U-B----  127       3:9863               3270     3270    100.00
45b79d38         7        U-B----  128       3:6593               3270     3270    100.00
45b79da0         6        U-B----  129       3:3323               3270     3270    100.00
45b79e08         5        U-B----  130       3:53                 3270     3270    100.00
45b79e70         1        U-B----  131       3:32753              3270     3270    100.00
45b79ed8         2         U-B----  132      3:36023              3270     3270    100.00
45b79f40         3        U-B----  133       3:39293              3270     3270    100.00
45b79fa8         4        U-B----  134       3:42563              3270     3270    100.00
4538df30         15       U-B----  135       3:45833              3270     3270    100.00
4538df98         16       U-B----  136       3:49103              3270     3270    100.00
4545ce28         17       U---C--  137       3:52373              3270     2411     73.73
485196a0         18       U-B----  120       3:55643              3270     3270    100.00
48acbed8         19       A------  0         3:58913              3270        0      0.00
19 active, 19 total

Displaying Logical Log Content:

Use the onlog command to display the content of logical logs. Specific usage details are not covered here.

Conclusion

By exploring the physical and logical logs in GBase 8s, we gain a deeper understanding of their structure, functionality, and importance in database management and recovery. This article aims to provide valuable insights and inspiration, helping you better manage and maintain your GBase database systems.

GBASE 数据库 | Distributed Memory Monitoring in GBase 8c Database: A Practical Guide

GBASE Database — Sat, 08 Feb 2025 07:52:59 +0000

The smooth operation of a database management system heavily relies on the server's hardware environment, including hard drives and memory. GBase 8c, a distributed database known for its high performance and availability, requires effective memory monitoring to maintain optimal performance. This article delves into the practices of monitoring memory in a distributed environment, specifically within GBase 8c.

Ensuring Server Memory Health

To ensure that server memory is functioning correctly, it's essential to monitor several key aspects:

Memory Usage Monitoring: Utilize database-provided monitoring tools or operating system-level tools like top and free to observe the physical memory usage across nodes.
Cache Hit Rate Check: Monitor the cache hit rate during the operation of the GBase 8c database.
Performance Testing: Conduct simulated performance tests to evaluate memory performance under concurrent access and large data processing scenarios.

This article focuses on memory monitoring in distributed scenarios, providing a brief guide on how to perform these operations.

Monitoring Memory Usage

Memory usage can be monitored using tools provided by the database itself or the operating system.

1. Querying Memory Usage via SQL Commands

You can retrieve memory usage information by executing specific SQL commands within the GBase 8c database. This involves querying system tables or views to gather data on memory usage:

SELECT * FROM pg_totale_memory_detail;

2. Using Operating System Commands

On Linux systems, several commands are commonly used to monitor system memory usage:

free -m: Displays a summary of memory usage.
vmstat: Reports virtual memory statistics.
top: Provides a dynamic view of processes, including memory usage.

3. Key Metrics to Monitor

When monitoring memory usage, pay attention to the following key metrics:

Memory Utilization: Track the total memory used and the available memory.
Cache Usage: Monitor the usage of different types of caches, such as shared cache and buffer cache.
Memory Allocation: Ensure that the memory allocated by the database does not exceed predefined thresholds.
Memory Leaks: Regularly check for any signs of memory leaks.

Conclusion

Monitoring memory usage is an ongoing process that requires regular checks and adjustments based on the monitoring results. This ensures the performance and stability of the database, particularly in a distributed environment like GBase 8c. By following the practices outlined above, database administrators can maintain a healthy memory environment, contributing to the overall efficiency and reliability of the database system.

GBASE 数据库 | GBase 8a Database GDOM Deployment Guide

GBASE Database — Fri, 07 Feb 2025 08:38:14 +0000

The GBase 8a MPP Database Operation Management System (GDOM) is a B/S architecture tool designed to provide full lifecycle operation and maintenance (O&M) support for the GBase 8a MPP database. It offers a range of functionalities including cluster management, host management, health checks, and alerts, all aimed at reducing customer O&M costs, improving efficiency, and ensuring 24/7 operation of the cluster.

GDOM Environment Setup Checklist

Server	Installed Components	Node Type	Remarks
10.0.0.211	nginx, gdom-service	Deployment
10.0.0.212	haproxy	Installation	Optional
10.0.0.213	mysql	Installation
10.0.0.214	mysql	Installation	Optional
10.0.0.215	Redis, RocketMQ	Installation

Note: Node Types

Deployment Node: This is where the GDOM installation package is deployed. GDOM installation, service start/stop, uninstallation, and status queries are all managed from this node. It is the special installation node for managing GDOM services.
Installation Node: This is the server for installing GDOM components. For high availability configuration, if deploying with a single node, ignore the optional nodes.

1. Unpacking the Installation Package

1.1. On the 10.0.0.211 machine, use the root user to unpack the package:

[root@nginx_gdom-service /opt]# tar -xf GDOM-v3.0.0-build1.2.tar.gz

2. Set Up User Trust

2.1. Prerequisite: Configure demo.options to specify the servers requiring mutual trust.

Edit demo.options:

vim /opt/GDOM-v3.0.0-build1.2/demo.options

Example configuration:

gdomRoot=/opt/gdom
gdomService=10.0.0.211
gdomServicePort=8080
user=gdom
gdomDb=10.0.0.213,10.0.0.214
gdomDbPort=3306
gdomDbUrl=
gdomDbUser=root
gdomDbPwd=1
gdomDbHa=10.0.0.212
gdomDbHaPort=3307
gdomDbHaMonitorPort=8811
gdomDbHaMonitorAdmin=admin
gdomDbHaMonitorPwd=admin
gdomNginx=10.0.0.211
gdomNginxPort=8810
gdomNginxUrl=
gdomRedis=10.0.0.215
gdomRedisPort=7481
gdomRedisDb=0
gdomRedisUrl=
gdomMq=10.0.0.215
gdomMqPort=9876
gdomDashBoardPort=9898
gdomMqUrl=

2.2. Execute on all machines:

useradd gdom
passwd gdom
su - gdom
ssh-keygen -t rsa -P ''
ssh-copy-id *.*.*.*

3. Set Up Auto Start (Optional)

3.1. On the deployment machine:

[root@nginx_gdom-service ~]# vim /etc/rc.local

Add the following configuration:

#!/bin/sh -e
/opt/GDOM-v3.0.0-build1.2/gdomStart.sh -a
exit 0

Explanation:

/opt: The parent directory where the GDOM installation package is unpacked.
GDOM-v3.0.0-build1.2: The directory where the GDOM installation package is unpacked.
gdomStart.sh -a: The one-click start script for GDOM.

4. GDOM Deployment

4.1. Note: Ensure that the user has permission to operate on the GDOM root directory on all nodes.

On all machines, execute:

mkdir /opt/gdom  
chown -R gdom.gdom /opt/gdom

4.2. On the deployment machine, run the installation script:

[root@nginx_gdom-service /opt/GDOM-v3.0.0-build1.2]# su - gdom
[gdom@nginx_gdom-service /opt/GDOM-v3.0.0-build1.2]# sh gdomInstall.sh -a

5. Start GDOM Service

5.1. On the deployment machine, start the service:

[gdom@nginx_gdom-service /opt/GDOM-v3.0.0-build1.2]$ sh gdomStart.sh -a

5.2. After starting the service, access the browser:

URL: http://10.0.0.211:8810
Default Username: admin
Default Password: admin123

To stop the service:

sh gdomStop.sh -a

To check the service status:

sh gdomStatus.sh -a

6. Explanation of `demo.options`

# GDOM installation root directory
gdomRoot=/opt/pjx
# Backend service installation node (comma-separated)
gdomService=192.168.1.2
# Backend service port (affects frontend requests to gdom-service)
gdomServicePort=8080
# GDOM user
user=gdom
# Repository installation nodes (for high availability configuration, comma-separated)
gdomDb=192.168.1.4,192.168.1.5
# Repository access port (affects backend requests to MySQL)
gdomDbPort=3306
# Existing repository IP address
gdomDbUrl=192.168.3.87
# Repository connection user
gdomDbUser=root
# Repository connection password
gdomDbPwd=111111
# High availability proxy node for repository (recommended to deploy separately)
gdomDbHa=192.168.1.3
# Repository high availability access port
gdomDbHaPort=3307
# Repository high availability proxy monitor access port
gdomDbHaMonitorPort=8811
# Repository high availability proxy monitor login user
gdomDbHaMonitorAdmin=admin
# Repository high availability proxy monitor login password
gdomDbHaMonitorPwd=admin
# Nginx installation node
gdomNginx=192.168.1.2
# Nginx access port (affects browser access)
gdomNginxPort=8810
# Existing Nginx IP address [If this is set, gdomNginx is invalid]
gdomNginxUrl=
# Redis installation node
gdomRedis=192.168.1.6
# Redis access port (affects backend requests to Redis)
gdomRedisPort=7481
# Redis DB to use
gdomRedisDb=0
# Existing Redis IP address [If this is set, gdomRedis is invalid]
gdomRedisUrl=
# RocketMQ installation node
gdomMq=192.168.1.6
# RocketMQ access port (affects backend requests to RocketMQ)
gdomMqPort=9876
# RocketMQ monitoring platform access port (optional)
gdomDashBoardPort=9898
# Existing RocketMQ IP address [If this is set, gdomMq is invalid]
gdomMqUrl=

Note: MySQL, Redis, RocketMQ, and Nginx are optional components.

If the user’s environment already has these components, configure their URL properties in demo.options. Regardless of whether they are newly installed or already present, the ports need to be configured.

For the best results, it is recommended to do a fresh installation to avoid conflicts with existing middleware.

With GDOM, you can ensure efficient O&M operations, simplified management, and seamless service monitoring for your GBase 8a deployment. Happy managing!

GBASE数据库 | Understanding Partition Pages in GBase 8s Database

GBASE Database — Fri, 07 Feb 2025 07:43:55 +0000

In this article, we will explore a crucial data storage structure in the GBase 8s database—Partition Page.

Overview

In the first chunk of each dbspace, the pages following the chunk free list mark the beginning of the tblspace tblspace (partition table).

Tables (or indexes) can be either non-fragmented or fragmented. For non-fragmented tables, there is only one partition. For fragmented tables, there are multiple partitions.

A partition (also referred to as a "fragment") is essentially a tblspace. The metadata for this tblspace is stored in the tblspace tblspace page. The tblspace tblspace manages the metadata for all partitions, similar to how a table manages multiple rows of data. Hence, it is called tblspace tblspace, or the table space that manages table spaces.

The layout of the tblspace tblspace is as follows:

By configuring the TBLTBLFIRST and TBLTBLNEXT parameters in the onconfig file, you can set the size of the first and subsequent extents for the tblspace tblspace. A default value of 0 indicates that the database server will determine the appropriate extent size based on the initial dbspace chunk size.

Each tblspace in the system has a tblspace tblspace page that describes it. For simplicity, we often refer to this special page type as a partition page.

Partition Number (Partnum)

First 12 bits: dbspace number
Last 20 bits: Logical page number within the tblspace tblspace

The purpose of the Partnum (a 4-byte hexadecimal code) is to guide GBase8s to the partition page of the table within the tblspace-tblspace. The high 12 bits of the partnum indicate which dbspace contains the table. GBase8s uses these 12 bits to locate the tblspace-tblspace of the target dbspace. A page within the tblspace tblspace describes the target table. GBase8s uses the low 20 bits of the partnum to locate the correct partition page, which contains a logical page number—essentially, the page number within the tblspace tblspace.

Partition Page Layout

General Overview

Each partition page uses the following 5 slots to describe the structure, location, and content of a table within the dbspace:

Slot 1: Contains 92 bytes of general table information, including the partition number.
Slot 2: Contains information identifying the partition by database name, table owner, table name, and NLS collation sequence (if applicable).
Slot 3: Contains descriptive entries for each special column in the table, representing Blob and VARCHAR types.
Slot 4: Contains key descriptor entries for each index key present in the table. Errors like "Illegal key descriptor: too many parts or too long" refer to issues in Slot 4 of the partition page, not the index itself. Deleting an index requires its key descriptor, so a faulty key descriptor may sometimes require intervention from GBase 8s technical support to fix.
Slot 5: Contains extent information. Each 8-byte entry in this slot includes:
- The logical page number of the first page of the extent in the tblspace (4 bytes)
- The page offset of the extent within the dbspace (4 bytes)
- This slot also includes an entry for the logical page number of the next allocated extent.

Tables can also have a sixth slot, but it does not appear on the primary partition page of the table. The sixth slot is used to describe different versions of extents due to in-place table alterations. This slot appears on a separate page within the tblspace tblspace.

Preparing Data and Metadata

DROP DATABASE IF EXISTS hyqdb;
CREATE DATABASE hyqdb WITH LOG;
CREATE TABLE t1(c1 INT PRIMARY KEY, c2 CHAR(10), c3 VARCHAR(10));
CREATE INDEX i1_t1 ON t1(c2, c3);
INSERT INTO t1 VALUES(1, '001', 'v001');
INSERT INTO t1 VALUES(2, '002', 'v002');

Finding the Partition Page

You can use the oncheck -pt database_name:table_name command to find all partitions related to a table. Each partition has its own information.

For t1, which is a non-partitioned table with a primary key index and another index, there are 3 partitions:

The partition where the data resides.
The partition where the primary key index resides.
The partition where the other index resides.

[root@node2 ids]# oncheck -pt hyqdb:t1

TBLspace Report for hyqdb:root.t1

    Physical Address               1:11862   // This address will be used later
    Creation date                   09/13/2024 11:14:56
    TBLspace Flags                  902        Row Locking
                                             TBLspace contains VARCHARS
                                              TBLspace use 4 bit bit-maps
    Maximum row size                26
    Number of special columns      1
    Number of keys                 0
    Number of extents              1
   Current serial value            1
    Current SERIAL8 value          1
    Current BIGSERIAL value        1
    Current REFID value            1
    Pagesize (k)                   2
    First extent size              8
    Next extent size               8
    Number of pages allocated      8
    Number of pages used           2
    Number of data pages           1
    Number of rows                 2
    Partition partnum              1049253  // This value will be used later
    Partition lockid                1049253

    Extents
         Logical Page     Physical Page        Size Physical Pages
                   0           1:13496           8          8

                 Index  100_1 fragment partition rootdbs in DBspace rootdbs

    Physical Address               1:11863   // Used when displaying the primary key index
    Creation date                   09/13/2024 11:14:56
    TBLspace Flags                  802        Row Locking
                                              TBLspace use 4 bit bit-maps
    Maximum row size                26
    Number of special columns      0
    Number of keys                 1
    Number of extents              1
    Current serial value           1
    Current SERIAL8 value          1
    Current BIGSERIAL value        1
    Current REFID value            1
    Pagesize (k)                   2
    First extent size              4
    Next extent size               4
    Number of pages allocated      4
    Number of pages used           2
    Number of data pages           0
    Number of rows                 0
    Partition partnum               1049254
    Partition lockid                1049253

    Extents
         Logical Page     Physical Page        Size Physical Pages
                   0            1:6421           4          4

                 Index i1_t1 fragment partition rootdbs in DBspace rootdbs

    Physical Address                1:11864  // Used when displaying index i1_t1
    Creation date                   09/13/2024 11:14:56
    TBLspace Flags                  802        Row Locking
                                             TBLspace use 4 bit bit-maps
    Maximum row size                26
    Number of special columns      0
    Number of keys                 1
    Number of extents              1
    Current serial value           1
    Current SERIAL8 value          1
    Current BIGSERIAL value        1
    Current REFID value            1
    Pagesize (k)                   2
    First extent size              7
    Next extent size               7
    Number of pages allocated      7
    Number of pages used           2
    Number of data pages           0
    Number of rows                 0
    Partition partnum               1049255
    Partition lockid                1049253

    Extents
         Logical Page     Physical Page        Size Physical Pages
                   0           1:13489           7          7

You can also use the partition number of the table:

oncheck –pt 1049253

The output will be identical to the previous one.

Slot 1: The Partition Structure

The output from the previous section consists of the partition structure (Slot 1) and the extent structure (Slot 5) of the partition page. This section describes the contents of the partition structure (in Slot 1).

The partition structure is stored in the first slot of each partition page and holds general information about the corresponding table. You can view the partition structure of any table by running the following command:

You might recognize some of the general tblspace information stored in the partition structure as table statistics, which are also stored in the system catalog for the optimizer to use. In fact, during some UPDATE STATISTICS operations, some information from the table's partition page is copied to the system catalog, while other information must be collected by reading the tblspace pages themselves. Although the system catalog information may become outdated as the table grows and changes, the information on the partition page should always be accurate. However, the optimizer code is designed to be portable across GBase8s servers, so it does not know how to access partition pages. As far as the optimizer is concerned, the only available table statistics are those stored in the system catalog.

We will now describe the contents of the partition structure in detail.

Physical Address (decimal:decimal)

This is the physical location of the partition page.

You can display the contents of this page using oncheck –pP.

Table Data:

[root@node2 ids]# oncheck -pP 1 11862
addr             stamp    chksum nslots flag type         frptr frcnt next     prev
1:11862          5942545  831c    5      2    PARTN        216    1808  0        0
        slot ptr   len   flag
        1    24    136    0
        2    160   24     0
        3    184   12     0
        4    196   0      0
        5    196   20     0
slot    1:
    0: a5  2 10  0   2  9  0  0 1a  0   0  0  1  0  0   0   %...............
   16:  1  0   0  8 30 ae e3 66  1   0  0  0   8  0  0   0   ....0.cf........
   32:  8  0   0  0  8   0  0  0   1  0  0   0  0  0   0  0   ................
   48: ff ff ff ff a5  2 10  0   1  0  0   0  0  0   0  0   ....%...........
   64:  0  0   0  0  0   0  0  0  0  0   0  0  0  0   0  0   ................
   80:  0  0   0  0  0   0  0  0   0  0  0   0  1  0   0  0   ................
   96:  0  0   0  0  1   0  0  0   1  0  0   0  0  0   0  0   ................
  112:  1  0   0  0 89  0   0  0 80 d0 77  0   0  0  0   0   .........Pw.....
  128:  0  0   0  0  0   0  0  0                           ................
slot    2:
    0: 68 79 71 64 62  0 72 6f 6f 74  0 74 31  0 65 6e    hyqdb.root.t1.en
   16: 5f 55 53 2e 38 31 39  0                           _US.819.........
slot    3:
    0:  e  0   0  0  0   0  a  0   0  0  0   5               ................
slot   4:
slot   5:
   0:   0  0  0   0  0  1   0  0 34 b8  0   0  0  8   0  0   ........48......
  16:   0  0  0   0                                        ................

Primary Key Index

[root@node2 ids]# oncheck -pP 1 11863
addr             stamp    chksum nslots flag type         frptr frcnt next     prev
1:11863          5942445  82a1    5      2    PARTN        244    1780  0        0
        slot ptr   len   flag
        1    24    136    0
        2    160   28     0
        3    188   0      0
        4    208   36     0
        5    188   20     0
slot    1:
    0: a6  2 10  0   2  8  0  0 1a  0   0  0  0  0  1   0   &...............
   16:  1  0   0  8 30 ae e3 66  1   0  0  0   4  0  0   0   ....0.cf........
   32:  4  0   0  0  4   0  0  0   2  0  0   0  0  0   0  0   ................
   48: ff ff ff ff a5  2 10  0   1  0  0   0  0  0   0  0   ....%...........
   64:  0  0   0  0  0   0  0  0   0  0  0   0  0  0   0  0   ................
   80:  0  0   0  0  0   0  0  0  0  0   0  0  1   0  0  0    ................
   96:  0  0   0  0  1   0  0  0   1  0  0   0  0  0   0  0   ................
  112:  1  0   0  0 89  0   0  0 74 d2 77  0   0  0  0   0   ........tRw.....
  128:  0  0   0  0  0   0  0  0                           ................
slot    2:
    0: 68 79 71 64 62  0 72 6f 6f 74  0 20 31 30 30 5f   hyqdb.root. 100_
   16: 31  0 65 6e 5f 55 53 2e 38 31 39  0               1.en_US.819.....
slot    3:
slot   4:
   0: 98   2  4  1   1  0  4   0  0  0   0  0 a5  2 10   0   ............%...
  16:   1  0  0   0  0  0   0  0  4   0  2  0   0  0  0   0   ................
  32:   0  0  0   0                                        ................
slot   5:
   0:   0  0  0   0  0  1   0  0 19 15  0   0  0  4   0  0   ................
  16:   0  0  0   0                                        ................

Index i1_t1

[root@node2 ids]# oncheck -pP 1 11864
addr             stamp    chksum nslots flag type         frptr frcnt next     prev
1:11864          5942518  82f5    5      2    PARTN        260    1764  0        0
        slot ptr   len   flag
        1    24    136    0
        2    160   28     0
        3    188   0      0
        4    208   52     0
        5    188   20     0
slot    1:
    0: a7  2 10  0   2  8  0  0 1a  0   0  0  0  0  1   0   '...............
   16:  1  0   0  8 30 ae e3 66  1   0  0  0   7  0  0   0   ....0.cf........
   32:  7  0   0  0  7   0  0  0   2  0  0   0  0  0   0  0   ................
   48: ff ff ff ff a5  2 10  0   1  0  0   0  0  0   0  0   ....%...........
   64:  0  0   0  0  0   0  0  0   0  0  0   0  0  0   0  0   ................
   80:  0  0   0  0  0   0  0  0   0  0  0   0  1  0   0  0   ................
   96:  0  0   0  0  1   0  0  0   1  0  0   0  0  0   0  0   ................
  112:  1  0   0  0 89  0   0  0 50  0 78   0  0  0   0  0   ........P.x.....
  128:  0  0   0  0  0   0  0  0                           ................
slot    2:
    0: 68 79 71 64 62  0 72 6f 6f 74  0 69 31 5f 74 31   hyqdb.root.i1_t1
   16:  0 65 6e 5f 55 53 2e 38 31 39  0 49               .en_US.819.I....
slot    3:
slot    4:
    0:  1  2   0  1  2  0 16  0   0  0  0  0 a5  2 10  0    ............%...
   16:  1  0   0  0  4   0  0  0   a  0  0   0  0  0   0  0   ................
   32:  0  0   0  0  e   0  0  0   c  0  5   0  0  0   0  0   ................
   48:  0  0   0  0                                        ................
slot    5:
    0:  0   0  0  0   0  1  0  0 34 b1  0  0   0  7  0   0   ........41......
  16:   0  0  0   0                                        ................

Creation date

This is the date and time when the table was created.

TBLSpace Flags (decimal)

These flags are operated similarly to page flags, dbspace flags, and block flags, and are combined into an integer through logical OR. Each tblspace flag has the following values and meanings:

0x0001: Page-level locking
0x0002: Row-level locking
0x0004: Tblspace is Bundlespace (online security)
0x0008: Marked as DDR replicated partition
0x0010: Partition is deleted (shared memory only)
0x0020: System-defined temporary table
0x0040: User-defined temporary table
0x0080: Tblspace used for sorting
0x0100: Contains VARCHAR columns
0x0200: Contains BLOB space BLOB columns
0x0400: Contains BLOB columns resident in the partition
0x0800: Requires 4-bit bitmap
0x1000: Contains optical BLOB columns
0x2000: Partition required for system operation - do not delete
0x4000: Temporary table used for special functions - do not update
0x8000: Partition is being appended to

Maximum row size (decimal)

For tables with fixed row lengths, this value is simply the row size in bytes. The concept of maximum row size is only required when a tblspace contains VARCHAR columns. Remember that VARCHAR columns define minimum and maximum sizes in characters, which correspond to bytes. The maximum size of a VARCHAR column, plus the 2-byte overhead (for storing the actual size of the VARCHAR data), and the sizes of other columns in the schema, form the maximum row size.

Number of special columns (decimal)

BLOB and VARCHAR columns are considered special and are exclusive column types in GBase 8s.

Number of keys (decimal)

The total number of indexes defined for the table. Composite indexes can consist of several columns but are still counted as a single index key.

Number of extents (decimal)

The number of individual data blocks allocated to the table. The number of extents tends to remain low due to GBase 8s admin extensions and good tblspace management. As the number of data blocks in tblspace increases, not only does data become unpredictably dispersed (causing performance issues with sequential reads), but extension slots on the partition page also increase. Data block slots can only contain so many entries before space runs out. Since other slots on partition pages (like slot 4) can also grow dynamically, the exact maximum number of tblspace extension data blocks cannot be predicted with precision. Verified evidence suggests that the maximum number of extents on a 2K page is about 190.

Current serial value (decimal)

Tblspace can only contain one serial-type column. If it exists, this is the next value used for insertions. If there is no serial column in the table, this value remains 1.

Serial-related types include: serial, serial8, bigserial.

First extent size (decimal)

This is the configured EXTENT SIZE, measured in pages of GBase 8s. The unit here may be confusing because the EXTENT SIZE can be specified in kilobytes through SQL.

The default EXTENT SIZE is 8 pages, independent of page size. The minimum extent size is 4 pages.

Next extent size (decimal)

This is the configured NEXT SIZE, also measured in pages of GBase 8s. Note that at the SQL level, NEXT SIZE is specified in kilobytes, so the value here is initially half or a quarter of the number specified in the SQL NEXT SIZE clause.

Since data block sizes double, the next data block size may increase over time. Unless the table is intentionally changed, the size will never decrease. The default value for the next data block size is 8 pages. The minimum extent size is 4 pages.

Number of pages allocated (decimal)

This is the total number of pages contained within the extended data blocks allocated to the tblspace, regardless of usage.

Number of pages used (decimal)

This is the total number of pages that have been used within the tblspace.

Number of data pages (decimal)

This is the number of data pages currently in use in the tblspace. When all rows are removed from a data page, it will be released to be reused within the tblspace, and the partition structure's data page count will decrease.

Number of rows (decimal)

This indicates the number of rows in the tblspace.

Partition partnum (decimal)

This indicates the partition number of the tblspace.

Partition lockid

In the past, when you locked a table, you were actually locking a partition number. This worked as long as there was a one-to-one correspondence between database tables and partition numbers. However, partitioned tables allow many partitions to be associated with a single database table. GBase 8s uses this lockid value to represent all table fragments, rather than associating each partition number with a single table lock.

Slot 2: partition name

In slot 2:

0: 68 79 71 64 62 0 72 6f 6f 74 0 74 31 0 65 6e hyqdb.root.t1.en
16: 5f 55 53 2e 38 31 39 0 _US.819.........

The PT_NAME in slot 2 is divided by '\0' into four pieces of information:

dbsname
owner
tabname
nlscollname

Slot 3: column descriptors

In slot 3:

0: e 0 0 0 e 0 a 0 0 0 0 5

The details for each byte are as follows:

e 0 0 0: e in hexadecimal (14) represents the offset of this column within the row.
a 0: This indicates that the maximum length of the VARCHAR field is 10 bytes.
0 0: This indicates the minimum length of the VARCHAR field is 0 bytes.
5: The base data type (5 represents VARCHAR).

Slot 4: key descriptors

For the primary key corresponding to the partition partition page:

    0: 98   2  4  1   1  0  4   0  0  0   0  0 a5  2 10   0   ............%...
   16:   1  0  0   0  0  0   0  0  4   0  2  0   0  0  0   0   ................
   32:   0  0  0   0                                        ................

For the i1_t1 partition partition page:

    0:   1  2  0   1  2  0 16   0  0  0   0  0 a5  2 10   0   ............%...
   16:   1  0  0   0  4  0   0  0  a   0  0  0   0  0  0   0   ................
   32:   0  0  0   0  e  0   0  0  c   0  5  0   0  0  0   0   ................
   48:   0  0  0   0                                       ................

Slot 5: The Extent Slot

Using oncheck -pt hyqdb:t1, the content displayed for each partition's final section represents extent-related information. For example:

Extents
  Logical Page     Physical Page        Size Physical Pages
  0               1:13496             8           8

This represents the physical starting position of the data and the number of pages.

According physical address displayed data part (Note: need to jump the bitmap page, so use 1 13497)

[root@node2 ids]# oncheck -pP 1 13497
addr             stamp    chksum nslots flag type         frptr frcnt next     prev
1:13497          5942555  99f9    2      1    DATA         64     1972  0        0
       slot ptr   len    flag
       1     24    20    0
       2     44    20    0
slot   1:
   0:   0  0  0  1 30 30 31 20 20 20 20 20 20 20  0  4    ....001       ..
  16: 76 30 30 31                                       v001............
slot   2:
   0:   0  0  0  2 30 30 32 20 20 20 20 20 20 20  0  4    ....002       ..
  16: 76 30 30 32                                        v002............

Slot 6: Page Versioning (In-Place-Alter Required)

When performing an In-Place-Alter, this slot tracks which version of metadata the data is based on.

"In-Place-Alter" means modifying the table without immediately updating the data. Data is updated when it changes in the future. During this process, the data will have different metadata versions, and this slot records that version.

Conclusion

Through the analysis of Partition Pages in GBase 8s (GBase数据库), we have not only understood the definition, role, structure, and composition of partition pages, but also mastered their creation, management, and practical applications. Partition pages, as a key data storage structure in GBase 8s, play an important role in improving database storage efficiency, optimizing query performance, and simplifying data maintenance. In the future of database technology development, the application and optimization of partition pages will remain a key research focus. We hope this article provides valuable reference and inspiration to database professionals and enthusiasts, and together, we can explore the infinite possibilities of database technology.

GBASE数据库 | GBase 8c Remote Backup and Restore Practice

GBASE Database — Fri, 07 Feb 2025 05:46:25 +0000

GBase 8c database (GBase数据库) allows users to perform full and incremental backups and restores of clusters via a backup server. It also provides the built-in gs_probackup tool for remote backup and restore operations, supporting flexible backup strategies such as incremental backups, scheduled backups, and remote backups and restores.

The gs_probackup tool is used to manage backup and recovery for GBase 8c databases. With this tool, regular backups can be scheduled to ensure that data is recoverable in case of network or machine failures. In addition to backing up the data in the database, it can also back up external directories, such as script files, configuration files, log files, and dumps. This article primarily focuses on configuring a remote backup strategy for GBase 8c databases using the gs_probackup tool.

0. Environment Setup

In this example, based on gs_probackup, the GBase 8c database cluster is backed up to a standby server and restored to a specified directory.

Environment Information:

Database server IP: 172.16.71.58
Backup server IP: 172.16.200.108

1. Backup Deployment

(1) Configure Passwordless SSH Login for the gbase System User Between the Database and Backup Servers

On the database server 172.16.71.58, log in and configure passwordless login:

  [root@gbase8c-iicp-db01 ~]# su - gbase
  [gbase@gbase8c-iicp-db01 ~]$ ssh-keygen -t rsa
  [gbase@gbase8c-iicp-db01 ~]$ ssh-copy-id -i gbase@172.16.200.108

On the backup server 172.16.200.108, log in and configure passwordless login:

  [root@ZSC-GB8C-NODE1 ~]# su - gbase
  [gbase@ZSC-GB8C-NODE1 ~]$ ssh-keygen -t rsa
  [gbase@ZSC-GB8C-NODE1 ~]$ ssh-copy-id -i gbase@172.16.71.58

(2) Modify Database Parameters and Configure Archive Log Directory

On the database server 172.16.71.58, modify the parameters:

  [root@gbase8c-iicp-db01 ~]# su - gbase
  [gbase@gbase8c-iicp-db01 ~]$ gs_guc reload -N all -I all -c "archive_mode=on"
  [gbase@gbase8c-iicp-db01 ~]$ gs_guc reload -N all -I all -c "archive_timeout=1800"
  [gbase@gbase8c-iicp-db01 ~]$ gs_guc reload -N all -I all -c "archive_command = 'scp %p gbase@172.16.200.108:/data/mpp/backup/zhck/archive_dir/%f'"
  [gbase@gbase8c-iicp-db01 ~]$ gs_guc reload -N all -I all -c "enable_cbm_tracking=on"

Parameters:

archive_mode: Determines whether archiving is enabled.
- Possible values: on (enable), off (disable)
- Default: off
archive_timeout: Defines the archive timeout period.
- Range: Integer (0–1073741823) in seconds. 0 disables this feature.
- Default: 0
archive_command: Defines the archive WAL log command. It is recommended to use an absolute path.
- Default: disabled (feature disabled)
enable_cbm_tracking: This parameter must be enabled for incremental backups. Disabling it can cause backup failure.
- Possible values: on (enable tracking), off (disable tracking)
- Default: off

(3) Enable Remote Database Access on the Backup Server

On the database server 172.16.71.58, enable remote access for the backup server:

  [root@gbase8c-iicp-db01 ~]# su - gbase
  [gbase@gbase8c-iicp-db01 ~]$ gs_guc reload -N all -I all -h "host replication all 172.16.200.108/32 md5"

(4) Create and Grant Backup User Permissions

On the backup server 172.16.200.108, test the database login and create a backup user:

  [root@gbase8c-iicp-db01 ~]# su - gbase
  [gbase@gbase8c-iicp-db01 ~]$ gsql -d postgres -h 172.16.71.58
  postgres=# create user dba with password 'gbase,123' sysadmin;
  postgres=# grant all privileges to dba;

(5) Create Archive Directory on the Backup Server

On the backup server 172.16.200.108, create the backup archive directory:

  [root@gbase8c-iicp-db01 ~]# su - gbase
  [gbase@ZSC-GB8C-NODE1 ~]# mkdir -p /data/mpp/backup/zhck/iicp

(6) Initialize Backup

Switch to the gbase user and initialize the backup directory:

  [root@gbase8c-iicp-db01 ~]# su - gbase
  [gbase@ZSC-GB8C-NODE1 ~]# gs_probackup init -B /data/mpp/backup/zhck/iicp

Add a backup instance and generate the pg_probackup.conf configuration file:

  [gbase@ZSC-GB8C-NODE1 ~]# gs_probackup add-instance -B /data/mpp/backup/zhck/iicp -D /home/gbase/data/dn1 --instance=gbase_zhck --remote-host=172.16.71.58 --remote-user=gbase

Set retention policy for backups (e.g., 30 days):

  [gbase@ZSC-GB8C-NODE1 ~]# gs_probackup set-config -B /data/mpp/backup/zhck/iicp --instance=gbase_zhck --retention-redundancy=30 --retention-window=30

(7) Full Backup

Perform a full backup:

  [root@gbase8c-iicp-db01 ~]# su - gbase
  [gbase@ZSC-GB8C-NODE1 ~]# cd /data/mpp/backup/zhck
  [gbase@ZSC-GB8C-NODE1 ~]# gs_probackup backup -B /data/mpp/backup/zhck/iicp -b FULL -h 172.16.71.58 -p 15432 -U gbase -W 'gbase,123' --instance=gbase_zhck --delete-expired -d postgres --remote-host=172.16.71.58 --remote-user=gbase

(8) Incremental Backup

Perform an incremental backup:

  [root@gbase8c-iicp-db01 ~]# su - gbase
  [gbase@ZSC-GB8C-NODE1 ~]# cd /data/mpp/backup/zhck
  [gbase@ZSC-GB8C-NODE1 ~]# gs_probackup backup -B /data/mpp/backup/zhck/iicp -b PTRACK -h 172.16.71.58 -p 15432 -U gbase -W 'gbase,123' --instance=gbase_zhck --delete-expired -d postgres --remote-host=172.16.71.58 --remote-user=gbase

2. Data Recovery in GBase 8c Database

In this example, we recover data to a separate directory on the current host. In a production environment, if the cluster experiences incorrect operations or data corruption due to other factors, it is not recommended to perform the recovery directly on the original cluster. This could potentially cause secondary damage to the database. It is advised to restore data to another node, verify the data, and then import the data back into the production cluster.

2.1 Full Recovery

(1) View the Backup

[root@gbase8c-iicp-db01 ~]# su - gbase
[gbase@ZSC-GB8C-NODE1 ~]# cd /data/mpp/backup/zhck
[gbase@ZSC-GB8C-NODE1 ~]# gs_probackup show -B /data/mpp/backup/zhck/iicp

View the backup as shown in the following image:

(2) Create a Recovery Directory on the Database Server

It is recommended to restore the backup to a different server, such as a pre-release or near-production environment.

[root@gbase8c-iicp-db01 ~]# su - gbase
[gbase@ZSC-GB8C-NODE1 ~]# mkdir /data/mpp/backup/zhck/restore_test -p

(3) Restore to a Full Backup at 2024-12-04 19:04:47 on Database Server 172.16.71.58

[gbase@ZSC-GB8C-NODE1 ~]# gs_probackup restore -B /data/mpp/backup/zhck/iicp -D /data/mpp/backup/zhck/restore_test -l=SNYUPG --instance=gbase_zhck --remote-host=172.16.71.58 --remote-user=gbase

The following information will be returned:

(4) Change the Database Port to Avoid Conflicts

[root@gbase8c-iicp-db01 ~]# su - gbase
[gbase@ZSC-GB8C-NODE1 ~]# cd /data/mpp/backup/zhck/restore_test

Check the port in the configuration file:

[gbase@gbase8c-iicp-db01 restore_test]$ cat postgresql.conf
...
port = 15432
...

(5) Start the Database

[root@gbase8c-iicp-db01 ~]# su - gbase
[gbase@gbase8c-iicp-db01 restore_test]$ gs_ctl start -D /data/mpp/backup/zhck/restore_test

The following information will be returned:

(6) Log in to the Database to Verify the Data

[root@gbase8c-iicp-db01 ~]# su - gbase
[gbase@gbase8c-iicp-db01 restore_test]$ gsql -d postgres -p 15432 -r

2.2 Point-in-Time Incremental Recovery

Similarly, restore to a separate directory on the current host. In production environments, data recovery should not be performed directly on the original cluster, as this may lead to further damage. It is advisable to restore the data to another node, verify it, and then import the data into the production cluster.

(1) View the Backup

[root@gbase8c-iicp-db01 ~]# su - gbase
[gbase@ZSC-GB8C-NODE1 ~]# cd /data/mpp/backup/zhck
[gbase@ZSC-GB8C-NODE1 ~]# gs_probackup show -B /data/mpp/backup/zhck/iicp

View the backup as shown in the following image:

(2) Merge Incremental Backup SNYUUR to SNYUPG

[gbase@ZSC-GB8C-NODE1 zhck]$ gs_probackup merge -B /data/mpp/backup/zhck/iicp --instance=gbase_zhck -i SNYUUR --progres

(3) Copy the Archive Logs to the Backup's WAL Directory

This step may be required depending on your backup configuration. If the WAL archive directory is already set to archive to the physical backup's WAL directory, you can skip this step.

[gbase@ZSC-GB8C-NODE1 gbase_zhck]$ cp /data/mpp/backup/zhck/archive_dir/* /data/mpp/backup/zhck/iicp/wal/gbase_zhck/

(4) View the Backup

(5) Restore to the Specific Time Point 2024-12-04 19:00:00 on the Database Server 172.16.71.58 to the /data/mpp/backup/zhck/restore_test Directory

[root@gbase8c-iicp-db01 ~]# su - gbase
[gbase@ZSC-GB8C-NODE1 ~]# cd /data/mpp/backup/zhck
[gbase@ZSC-GB8C-NODE1 zhck]$ gs_probackup restore -B /data/mpp/backup/zhck/iicp -D /data/mpp/backup/zhck/restore_test --instance=gbase_zhck --remote-host=172.16.71.58 --remote-user=gbase --recovery-target-time='2024-12-04 19:00:00'

(6) Change the Database Port to Avoid Conflicts

[root@gbase8c-iicp-db01 ~]# su - gbase
[gbase@ZSC-GB8C-NODE1 ~]# cd /data/mpp/backup/zhck/restore_test

Check the port in the configuration file:

[gbase@gbase8c-iicp-db01 restore_test]$ cat postgresql.conf
...
port = 15432
...

(7) Start the Database

[gbase@gbase8c-iicp-db01 restore_test]$ gs_ctl start -D /data/mpp/backup/zhck/restore_test

(8) Log in to the Database to Verify the Data

Conclusion

In summary, GBase 8c's backup and recovery functions play a critical role in ensuring data security and reliability. To meet the diverse needs of users and different scenarios, the product offers flexible backup strategies and media options. By choosing GBase 8c, users can enjoy the benefits of its backup and recovery features, ensuring data security, reliability, and the stable operation of the database system.

GBASE数据库 | GBase 8a Cluster Automated Installation and Deployment Using ansible

GBASE Database — Fri, 07 Feb 2025 02:30:34 +0000

In this article, we will guide you through the process of automating the installation and deployment of a GBase 8a cluster using Ansible. This solution simplifies the deployment of the GBase 8a database (GBase数据库) in a cluster environment, making it easier to manage and scale.

Host Configuration

An example configuration is as follows:

[all]
192.168.56.3 gbaseRole=coor RoleId=1 vc_name='' private_ip1=192.168.56.3 private_ip2=192.168.56.3 
192.168.56.4 gbaseRole=newcoor RoleId=1 vc_name=vc1 private_ip1=192.168.56.4 private_ip2=192.168.56.6 
192.168.56.5 gbaseRole=newnode RoleId=2 vc_name=vc1 private_ip1=192.168.56.4 private_ip2=192.168.56.6 
192.168.56.6 gbaseRole=node RoleId=1 vc_name=vc2 private_ip1=192.168.56.4 private_ip2=192.168.56.6

192.168.56.3: Business network IP
gbaseRole: The role of GBase (management node: coor, compute node: node)
RoleId: Role ID, fill in sequentially
vc_name: Virtual Cluster name for compute nodes, leave empty for management nodes, set to data in compatibility mode
private_ip1: Private network IP address 1 for compute nodes
private_ip2: Private network IP address 2 for compute nodes (not required for single-instance setups)

Variables Explanation

# GBase installation package name
gbase_setup_file: 'xxxx.tar.bz2'
# Operating system root password
root_passwd: '111111'
# GBase password for the operating system
gbase_passwd: '111111'
# Database DBA user (root, gbase) password
dba_password: '111111'
# Database character set (utf8, utf8mb4, gbk, etc.)
db_charset: utf8
vc_lists: ['vc1']
# Management node ID list, generally recommended to be the IP address's last segment, separated by commas
coorHostNodeIDlist: '1,2,3'

Task Descriptions

Initial Installation Preparation

- name: Install necessary RPM packages
  yum: name={{ item }} state=installed
  with_items:
    - bzip2
    - expect
    - gcc
    - '*cgroup*'
    - bc  
    - numactl
    - rsync
  tags:
    init_env

- name: Upload GBase installation package
  copy: 
    src: files/{{ gbase_setup_file }} 
    dest: /opt/
  when: "gbaseRole == 'coor' and RoleId == 1"
  tags:
    init_env

- name: Extract GBase installation package
  unarchive:
    src: /opt/{{ gbase_setup_file }}
    dest: /opt/
    remote_src: yes
  when: "gbaseRole == 'coor' and RoleId == 1"
  tags:
    init_env

- name: Create GBase user
  user:
    name: gbase
    state: present
    update_password: always
    password: '{{ gbase_passwd | password_hash('sha512') }}'
  tags:
    init_env

- name: Change ownership of /opt directory
  file:
    path: /opt
    state: directory
    owner: gbase
    group: gbase
    recurse: yes
  tags:
    init_env

- name: Upload system environment variable initialization script
  copy: 
    src: SetSysEnv.py
    dest: /tmp
  tags:
    init_env

- name: Initialize system environment variables
  shell: 'python /tmp/SetSysEnv.py --dbaUser=gbase --installPrefix=/opt --cgroup'
  tags:
    init_env

Generate Installation Configuration File

- name: Generate installation configuration file
  template: 
    src: demo.options.j2
    dest: /opt/gbase_workspace/setup/gcinstall/demo.options
  when: "gbaseRole == 'coor' and RoleId == 1 and InstanceType == 0"
  tags:
    install           
  become: true
  become_user: gbase

`demo.options.j2` Template:

installPrefix= /opt
coordinateHost = {% for host in groups['all'] -%}
        {% if hostvars[host]['gbaseRole']=='coor' -%}
                {% if hostvars[host]['RoleId'] == 1 -%}
                        {{ hostvars[host]['private_ip1'] }}
                {%- else -%}
                        ,{{ hostvars[host]['private_ip1'] }}
                {%- endif %}
        {%- endif %}
{%- endfor %}

coordinateHostNodeID = {{ coorHostNodeIDlist }}
dataHost = {% for host in groups['all'] -%}
        {% if hostvars[host]['gbaseRole']=='node' -%}
                {% if hostvars[host]['RoleId'] == 1 -%}
                        {{ hostvars[host]['private_ip1'] }}
                {%- else -%}
                        ,{{ hostvars[host]['private_ip1'] }}
                {%- endif %}
        {%- endif %}
{%- endfor %}

gcwareHost = {% for host in groups['all'] -%}
        {% if hostvars[host]['gbaseRole']=='coor' -%}
                {% if hostvars[host]['RoleId'] == 1 -%}
                        {{ hostvars[host]['private_ip1'] }}
                {%- else -%}
                        ,{{ hostvars[host]['private_ip1'] }}
                {%- endif %}
        {%- endif %}
{%- endfor %}

gcwareHostNodeID = {{ coorHostNodeIDlist }}
dbaUser = gbase
dbaGroup = gbase
dbaPwd = '{{ gbase_passwd }}'
rootPwd = '{{ root_passwd }}'
characterSet = {{ db_charset }}

Environment Installation and Deployment

- name: Install and deploy GBase
  shell: 'sh ansible_install.sh'
  when: "gbaseRole == 'coor' and RoleId == 1"
  tags:
    install
  become: true
  become_user: gbase

`ansible_install.sh` Script:

#!/usr/bin/sh
###########################################################
##creator :                                               
##create time:                                       
##Description: Silent installation script
##Version:                                            
###########################################################

cd /opt/gcinstall
spawn python gcinstall.py --silent=demo.options -i
expect {
  "*])?" {send "y\r"; exp_continue}
  "*])?" {send "y\r"; exp_continue}
}

Re-initialize System Environment Variables

- name: Reinitialize system environment variables
  shell: 'python /tmp/SetSysEnv.py --dbaUser=gbase --installPrefix=/opt --cgroup'
  tags:
    init_env

Generate VC Creation Configuration File

- name: Generate VC creation configuration file
  vars:
    cur_vcname: "{{ item }}"  
  template: 
    src: createvc.xml.j2
    dest: /opt/gcinstall/createvc_{{ item }}.xml
  when: "gbaseRole == 'coor' and RoleId == 1"
  with_items:
    "{{ vc_lists }}"
  tags:
    install          
  become: true
  become_user: gbase

`createvc.xml.j2` Template:

<?xml version='1.0' encoding="utf-8"?>
<servers>

{% for host in groups['all'] %}
{% if hostvars[host]['vc_name']==cur_vcname %}
    <rack>
        <node ip="{{ hostvars[host]['private_ip1'] }}"/>
    </rack>
{% endif %}
{% endfor %}

    <vc_name name="{{ cur_vcname }}"/>
    <comment message="{{ cur_vcname }}"/>

</servers>

Create VC

- name: Create VC
  shell: 'source ~/.bash_profile; cd /opt/gcinstall; gcadmin createvc createvc_{{ item }}.xml'
  when: "gbaseRole == 'coor' and RoleId == 1"
  with_items:
    "{{ vc_lists }}"
  tags:
    install
  become: true
  become_user: gbase

Generate Distribution Creation Configuration File

- name: Generate initialization configuration file
  vars:
    cur_vcname: "{{ item }}"  
  template: 
    src: gcChangeInfo_vcname.xml.j2
    dest: /opt/gcinstall/gcChangeInfo_{{ item }}.xml
  when: "gbaseRole == 'coor' and RoleId == 1"
  with_items:
    "{{ vc_lists }}"
  tags:
    install          
  become: true
  become_user: gbase

Generate Configuration File Template for Creating Distribution (`gcChangeInfo_vcname.xml.j2`)

installPrefix= /opt
coordinateHost = {% for host in groups['all'] -%}
        {% if hostvars[host]['gbaseRole']=='coor' -%}
                {% if hostvars[host]['RoleId'] == 1 -%}
                        {{ hostvars[host]['private_ip1'] }}
                {%- else -%}
                        ,{{ hostvars[host]['private_ip1'] }}
                {%- endif %}
        {%- endif %}
{%- endfor %}

coordinateHostNodeID = {{ coorHostNodeIDlist }}
dataHost = {% for host in groups['all'] -%}
        {% if hostvars[host]['gbaseRole']=='node' -%}
                {% if hostvars[host]['RoleId'] == 1 -%}
                        {{ hostvars[host]['private_ip1'] }}
                {%- else -%}
                        ,{{ hostvars[host]['private_ip1'] }}
                {%- endif %}
        {%- endif %}
{%- endfor %}

#existCoordinateHost =
#existDataHost =
#existGcwareHost=
gcwareHost = {% for host in groups['all'] -%}
        {% if hostvars[host]['gbaseRole']=='coor' -%}
                {% if hostvars[host]['RoleId'] == 1 -%}
                        {{ hostvars[host]['private_ip1'] }}
                {%- else -%}
                        ,{{ hostvars[host]['private_ip1'] }}
                {%- endif %}
        {%- endif %}
{%- endfor %}

gcwareHostNodeID = {{ coorHostNodeIDlist }}
dbaUser = gbase
dbaGroup = gbase
dbaPwd = '{{ gbase_passwd }}'
rootPwd = '{{ root_passwd }}'
#dbRootPwd = ''
#rootPwdFile = rootPwd.json
characterSet = {{ db_charset }}
#sshPort = 22

Create Distribution

- name: Initialize distribution for VC (multi-VC mode)
    shell: "source ~/.bash_profile; cd /opt/gcinstall; gcadmin distribution gcChangeInfo_{{ item }}.xml p 1 d 1 db_user gbase db_pwd 'gbase20110531' dba_os_password '{{ gbase_passwd }}' vc {{ item }}"
    when: "gbaseRole == 'coor' and RoleId == 1"
    with_items:
      "{{ vc_lists }}"
    tags:
      install
    become: true
    become_user: gbase

Initialize nodedatamap

- name: Initialize nodedatamap for VC
    vars:
      cur_vcname: "{{ item }}"  
    shell: 'source ~/.bash_profile; gccli -ugbase -pgbase20110531 -e"use vc {{ item }}; initnodedatamap;"'
    when: "gbaseRole == 'coor' and RoleId == 1"
    with_items:
      "{{ vc_lists }}"
    tags:
      install    
    become: true
    become_user: gbase

Conclusion

By automating the installation and deployment of GBase 8a clusters (GBase数据库) with Ansible, you can significantly reduce manual intervention and increase the efficiency of your database environment. This method is particularly useful for large-scale deployments and environments where consistency is crucial.

If you have any questions or need further clarification, feel free to reach out in the comments or via direct messages.

GBASE数据库 | GBase 8a Cluster Automated Installation and Deployment Using ansible

GBASE Database — Fri, 07 Feb 2025 02:30:34 +0000

Host Configuration

An example configuration is as follows:

[all]
192.168.56.3 gbaseRole=coor RoleId=1 vc_name='' private_ip1=192.168.56.3 private_ip2=192.168.56.3 
192.168.56.4 gbaseRole=newcoor RoleId=1 vc_name=vc1 private_ip1=192.168.56.4 private_ip2=192.168.56.6 
192.168.56.5 gbaseRole=newnode RoleId=2 vc_name=vc1 private_ip1=192.168.56.4 private_ip2=192.168.56.6 
192.168.56.6 gbaseRole=node RoleId=1 vc_name=vc2 private_ip1=192.168.56.4 private_ip2=192.168.56.6

192.168.56.3: Business network IP
gbaseRole: The role of GBase (management node: coor, compute node: node)
RoleId: Role ID, fill in sequentially
vc_name: Virtual Cluster name for compute nodes, leave empty for management nodes, set to data in compatibility mode
private_ip1: Private network IP address 1 for compute nodes
private_ip2: Private network IP address 2 for compute nodes (not required for single-instance setups)

Variables Explanation

# GBase installation package name
gbase_setup_file: 'xxxx.tar.bz2'
# Operating system root password
root_passwd: '111111'
# GBase password for the operating system
gbase_passwd: '111111'
# Database DBA user (root, gbase) password
dba_password: '111111'
# Database character set (utf8, utf8mb4, gbk, etc.)
db_charset: utf8
vc_lists: ['vc1']
# Management node ID list, generally recommended to be the IP address's last segment, separated by commas
coorHostNodeIDlist: '1,2,3'

Task Descriptions

Initial Installation Preparation

- name: Install necessary RPM packages
  yum: name={{ item }} state=installed
  with_items:
    - bzip2
    - expect
    - gcc
    - '*cgroup*'
    - bc  
    - numactl
    - rsync
  tags:
    init_env

- name: Upload GBase installation package
  copy: 
    src: files/{{ gbase_setup_file }} 
    dest: /opt/
  when: "gbaseRole == 'coor' and RoleId == 1"
  tags:
    init_env

- name: Extract GBase installation package
  unarchive:
    src: /opt/{{ gbase_setup_file }}
    dest: /opt/
    remote_src: yes
  when: "gbaseRole == 'coor' and RoleId == 1"
  tags:
    init_env

- name: Create GBase user
  user:
    name: gbase
    state: present
    update_password: always
    password: '{{ gbase_passwd | password_hash('sha512') }}'
  tags:
    init_env

- name: Change ownership of /opt directory
  file:
    path: /opt
    state: directory
    owner: gbase
    group: gbase
    recurse: yes
  tags:
    init_env

- name: Upload system environment variable initialization script
  copy: 
    src: SetSysEnv.py
    dest: /tmp
  tags:
    init_env

- name: Initialize system environment variables
  shell: 'python /tmp/SetSysEnv.py --dbaUser=gbase --installPrefix=/opt --cgroup'
  tags:
    init_env

Generate Installation Configuration File

- name: Generate installation configuration file
  template: 
    src: demo.options.j2
    dest: /opt/gbase_workspace/setup/gcinstall/demo.options
  when: "gbaseRole == 'coor' and RoleId == 1 and InstanceType == 0"
  tags:
    install           
  become: true
  become_user: gbase

`demo.options.j2` Template:

installPrefix= /opt
coordinateHost = {% for host in groups['all'] -%}
        {% if hostvars[host]['gbaseRole']=='coor' -%}
                {% if hostvars[host]['RoleId'] == 1 -%}
                        {{ hostvars[host]['private_ip1'] }}
                {%- else -%}
                        ,{{ hostvars[host]['private_ip1'] }}
                {%- endif %}
        {%- endif %}
{%- endfor %}

coordinateHostNodeID = {{ coorHostNodeIDlist }}
dataHost = {% for host in groups['all'] -%}
        {% if hostvars[host]['gbaseRole']=='node' -%}
                {% if hostvars[host]['RoleId'] == 1 -%}
                        {{ hostvars[host]['private_ip1'] }}
                {%- else -%}
                        ,{{ hostvars[host]['private_ip1'] }}
                {%- endif %}
        {%- endif %}
{%- endfor %}

gcwareHost = {% for host in groups['all'] -%}
        {% if hostvars[host]['gbaseRole']=='coor' -%}
                {% if hostvars[host]['RoleId'] == 1 -%}
                        {{ hostvars[host]['private_ip1'] }}
                {%- else -%}
                        ,{{ hostvars[host]['private_ip1'] }}
                {%- endif %}
        {%- endif %}
{%- endfor %}

gcwareHostNodeID = {{ coorHostNodeIDlist }}
dbaUser = gbase
dbaGroup = gbase
dbaPwd = '{{ gbase_passwd }}'
rootPwd = '{{ root_passwd }}'
characterSet = {{ db_charset }}

Environment Installation and Deployment

- name: Install and deploy GBase
  shell: 'sh ansible_install.sh'
  when: "gbaseRole == 'coor' and RoleId == 1"
  tags:
    install
  become: true
  become_user: gbase

`ansible_install.sh` Script:

#!/usr/bin/sh
###########################################################
##creator :                                               
##create time:                                       
##Description: Silent installation script
##Version:                                            
###########################################################

cd /opt/gcinstall
spawn python gcinstall.py --silent=demo.options -i
expect {
  "*])?" {send "y\r"; exp_continue}
  "*])?" {send "y\r"; exp_continue}
}

Re-initialize System Environment Variables

- name: Reinitialize system environment variables
  shell: 'python /tmp/SetSysEnv.py --dbaUser=gbase --installPrefix=/opt --cgroup'
  tags:
    init_env

Generate VC Creation Configuration File

- name: Generate VC creation configuration file
  vars:
    cur_vcname: "{{ item }}"  
  template: 
    src: createvc.xml.j2
    dest: /opt/gcinstall/createvc_{{ item }}.xml
  when: "gbaseRole == 'coor' and RoleId == 1"
  with_items:
    "{{ vc_lists }}"
  tags:
    install          
  become: true
  become_user: gbase

`createvc.xml.j2` Template:

<?xml version='1.0' encoding="utf-8"?>
<servers>

{% for host in groups['all'] %}
{% if hostvars[host]['vc_name']==cur_vcname %}
    <rack>
        <node ip="{{ hostvars[host]['private_ip1'] }}"/>
    </rack>
{% endif %}
{% endfor %}

    <vc_name name="{{ cur_vcname }}"/>
    <comment message="{{ cur_vcname }}"/>

</servers>

Create VC

- name: Create VC
  shell: 'source ~/.bash_profile; cd /opt/gcinstall; gcadmin createvc createvc_{{ item }}.xml'
  when: "gbaseRole == 'coor' and RoleId == 1"
  with_items:
    "{{ vc_lists }}"
  tags:
    install
  become: true
  become_user: gbase

Generate Distribution Creation Configuration File

- name: Generate initialization configuration file
  vars:
    cur_vcname: "{{ item }}"  
  template: 
    src: gcChangeInfo_vcname.xml.j2
    dest: /opt/gcinstall/gcChangeInfo_{{ item }}.xml
  when: "gbaseRole == 'coor' and RoleId == 1"
  with_items:
    "{{ vc_lists }}"
  tags:
    install          
  become: true
  become_user: gbase

Generate Configuration File Template for Creating Distribution (`gcChangeInfo_vcname.xml.j2`)

installPrefix= /opt
coordinateHost = {% for host in groups['all'] -%}
        {% if hostvars[host]['gbaseRole']=='coor' -%}
                {% if hostvars[host]['RoleId'] == 1 -%}
                        {{ hostvars[host]['private_ip1'] }}
                {%- else -%}
                        ,{{ hostvars[host]['private_ip1'] }}
                {%- endif %}
        {%- endif %}
{%- endfor %}

coordinateHostNodeID = {{ coorHostNodeIDlist }}
dataHost = {% for host in groups['all'] -%}
        {% if hostvars[host]['gbaseRole']=='node' -%}
                {% if hostvars[host]['RoleId'] == 1 -%}
                        {{ hostvars[host]['private_ip1'] }}
                {%- else -%}
                        ,{{ hostvars[host]['private_ip1'] }}
                {%- endif %}
        {%- endif %}
{%- endfor %}

#existCoordinateHost =
#existDataHost =
#existGcwareHost=
gcwareHost = {% for host in groups['all'] -%}
        {% if hostvars[host]['gbaseRole']=='coor' -%}
                {% if hostvars[host]['RoleId'] == 1 -%}
                        {{ hostvars[host]['private_ip1'] }}
                {%- else -%}
                        ,{{ hostvars[host]['private_ip1'] }}
                {%- endif %}
        {%- endif %}
{%- endfor %}

gcwareHostNodeID = {{ coorHostNodeIDlist }}
dbaUser = gbase
dbaGroup = gbase
dbaPwd = '{{ gbase_passwd }}'
rootPwd = '{{ root_passwd }}'
#dbRootPwd = ''
#rootPwdFile = rootPwd.json
characterSet = {{ db_charset }}
#sshPort = 22

Create Distribution

- name: Initialize distribution for VC (multi-VC mode)
    shell: "source ~/.bash_profile; cd /opt/gcinstall; gcadmin distribution gcChangeInfo_{{ item }}.xml p 1 d 1 db_user gbase db_pwd 'gbase20110531' dba_os_password '{{ gbase_passwd }}' vc {{ item }}"
    when: "gbaseRole == 'coor' and RoleId == 1"
    with_items:
      "{{ vc_lists }}"
    tags:
      install
    become: true
    become_user: gbase

Initialize nodedatamap

- name: Initialize nodedatamap for VC
    vars:
      cur_vcname: "{{ item }}"  
    shell: 'source ~/.bash_profile; gccli -ugbase -pgbase20110531 -e"use vc {{ item }}; initnodedatamap;"'
    when: "gbaseRole == 'coor' and RoleId == 1"
    with_items:
      "{{ vc_lists }}"
    tags:
      install    
    become: true
    become_user: gbase

Conclusion

If you have any questions or need further clarification, feel free to reach out in the comments or via direct messages.