DEV Community: Lei Zhou

openGauss Study notes (0001)

Lei Zhou — Sat, 22 Apr 2023 13:56:00 +0000

Why: why I want to choose a new database

The motivation is very simple, I do not know what is the most benefits why openGauss want to create a new relational database, so I want to try to feagure out it.

What: What's is openGauss

openGauss is a relational database[1], which is donated by Huawei, and which adopts client/server, single-process and multi-threaded architecture; it supports stand-alone and one-master-multiple-standby deployment methods, and supports features such as readability of standby machines and high availability of dual machines.
It's derived from PostgreSQL.

What's the difference between openGauss and PostgreSQL

A lot of China Company started to use it.
openGauss is based on PostgreSQL 9.2.4[2], and has the threads pool. and support huge number of transactions.

How to use it

su - omm #use omm user to login OS.

start the server

gs_om -t stop   #stop the service
gs_om -t start  #strat the service

check the status

gs_om -t status --detail

check the performance of DB

gs_checkperf  #gs_checkperf -i pmk -U omm [--detai]

the ports of DB

cat /opt/gaussdb/master1/postgresql.conf |grep port

list all of the Databases

gsql -d postgres -p 36000 -l

SQL

Create Database users

create user jack password "******";

What's openGauss, https://docs.opengauss.org/en/docs/3.1.1/docs/BriefTutorial/what-is-opengauss.html
openGauss is based on PostgreSQL 9.2.4, https://zhuanlan.zhihu.com/p/449917123

[forward]Automatic Test Framework YAT -openGauss

Lei Zhou — Tue, 18 Apr 2023 12:59:58 +0000

it's forwarded from https://opengauss.org/en/blogs/2022/Automatic-Test-Framework-YAT.html

Automatic Test Framework — YAT #

The Yet another test (YAT) framework is an automatic test framework based on Python3. The core of the framework is implemented by the Kotlin language. The framework is encapsulated and bonded through Python to provide command line interfaces (CLIs). Figure 1 shows the overall framework. The YAT framework is continuously evolving to become a more efficient and advanced automatic test framework.

YAT is the automatic test framework used for openGauss database specification monitoring. openGauss 2.1.0 not only releases open-source YAT, but also contributes over 30,000 automatic test cases to enhance the test capability of the openGauss community, enrich the openGauss ecosystem and attract more developers to participate in community construction. YAT code repository: https://gitee.com/opengauss/Yat

Figure 1 Overall YAT framework

Product Advantages #

It is friendly to database tests. Users can directly write SQL code and organize the code into a test suite for tests without additional configuration. Test cases are executed through the JDBC API and can adapt to various databases.

It supports multiple languages and is extensible.

Currently, languages such as SQL, Shell, Python (unittes), and Groovy (Junit/Spock) are supported. New languages and frameworks can be added through adapters. SQL statements supported by YAT are supersets of standard SQL statements.That is, YAT is extended based on standard SQL statements. Users can run shell commands, control connections, execute loops, bind SQL statements, control multiple sessions, and concurrently execute SQL statements in SQL scripts. For example:

@conn user/passwd@127.0.0.1:9090;  -- Reconnect to the database as the new user.
drop table if exists tbl_x;  -- Execute SQL statements.

create table tbl_x (id int, age int, xb int);

insert into tbl_x values(1, 2, 4);
insert into tbl_x values(3, 4, 5);

-- Perform the binding operation.
insert into tbl_x values(?, ?, ?);
@bind {
    int 3
    int 5
    int 7
}
-- Perform the binding operation in batches.
insert into tbl_x values(?, ?, ?);
@batch {
    int 3 int 4 int 0
    int 3 int 4 int 9
    int 3 int 4 int 8
    int 3 int 4 int 7
}
-- Run the shell commands.
@sh zctl.py -t stop;
@sh zctl.py -t start;

-- Define sessions.
@session(name: s1)
{
    @set autocommit false;
    update table tbl_x set par1 = 2 par2 = 2;
    insert into tbl_x values(1, 3, 4);
    commit;
}

@session(name: s2, user: abc, password: 'gauss@123')
{
    @set autocommit false;
    update table tbl_x set par1 = 2 par2 = 2;
    insert into tbl_x values(1, 3, 4);
    @step
    {
        select * from tbl_x for update;
    }
    commit;
}
-- Executes SQL statements in sessions.
@steps s1.0 s2.0 s1.1 s1.2 s2.2 s2.1;
-- Execute loops.
@for (count: 10)
{
    insert into abc values(1,1,3,4);
}
-- Concurrently execute SQL statements.
@parallel {
    @session {
        select * from abc for update;
        commit;
    }

    @session {
        select * from abc for update;
        commit;
    }
}

The Python language test script must be a unittest test script. YAT provides a public library to facilitate database and remote SSH operations in the Python unittest test script. For example:

class TestPrimaryStandby(TestCase):
    node = None
    node_standby = None

    test_table_drop = 'drop table if exists tbl_test';
    test_table_create = '''create table tbl_test (
        id int,
        name char(20),
        address varchar(1024)
    )
    '''

    @classmethod
def setUpClass(cls):
    # Initialize the connection object.
        cls.node = Node(node='primary')
        cls.node_standby = Node(node='standby')
        cls.node.sql(cls.test_table_drop)
        cls.node.sql(cls.test_table_create)

    @classmethod
    def tearDownClass(cls) -> None:
        cls.node.close()
        cls.node_standby.close() # Close the connection object.

def test_abc_001(self):
    # Run the shell command through SSH and check the result.
        self.node.sh('echo "success"').expect('success')

        # Run the SQL statement and check the result.
        self.node.sql('select * from tbl_test').expect(
            (1, 'xxx', 'xxxxxxxxxxxxxxxxxxx'),
            (2, 'xxx', 'xxxxxxxxxxxxxxxxxxx'))

def test_abc_003(self):
    # Run the shell command and determine the result through regular expression matching.
        self.node.sh('cm ctl query').regex(r'.*success.*')

Groovy scripts can be used to write JUnit cases or Spock cases. For more details, visit the official website.

It defines a set of standard test case writing specifications, test execution processes, and test report presentation modes.

Run the yat init command to create a test suite template. After the yat init command is executed, modify the content in the specified test suite directory. Then, install the JDBC driver, add the lib directory to the root directory of the test suite, copy the driver to this directory, and run the yat suite run command to run the test suite. You can add different parameters to set whether to print the report and the report format. Figure 2 shows the test suite directory result. Configure the following directories before running the test suite:
- conf directory, which stores node configuration files.
- except directory, which is the expected file of the test case.
- schedule directory, which stores the schedule files.
- testcase directory, which stores test case files.
Figure 2 Directory structure of the test suite
Multi-suite Schedule

YAT Schedule is a scheduler provided by YAT to schedule multiple YAT test suites at the same time. You can schedule multiple YAT test suites in parallel or serial mode by customizing a schedule file. When there are a large number of test suites, you need to determine the combination and sequence of the test suites. YAT provides a convenient method to organize multiple test suites into a large test suite, as shown in the following figure.
```
# File name: all.ys
serial {# Serial execution of test suites or test suite sets
    suite '../suite1';
    suite '../suite2';
    ...

    parallel { # Parallel execution of test suites or test suite sets
        suite 'parallel/suite1';
        suite 'parallel/suite2';
        ...

        serial { ... }
    }

    suite 'suite3';
    ...
}
```
Run the following command to execute all test suites in one-click mode:
```
yat schedule -s all.ys
```

[Forward] openGauss AI Capability Upgrade, Building a New AI-Native Database

Lei Zhou — Tue, 18 Apr 2023 12:46:12 +0000

it's forwarded from https://opengauss.org/en/blogs/2022/openGauss-AI-Capability-Upgrade-Building-a-New-AI-Native-Database.html

openGauss AI Capability Upgrade, Building a New AI-Native Database

What will happen when databases are combined with AI? The answers may vary among different database vendors, open-source communities, and teachers and students. Although it is difficult to form a uniform accurate concept, their answers all point to the same direction. Since the first version was released in the open-source community, openGauss has continuously evolved and contributed code in this field. openGauss 3.0.0 continues in this vein.

In this release, the openGauss AI capability is changed in the following ways:

The AI4DB functions are integrated into the open-source openGauss database autonomous platform.
The AI4DB capabilities are refactored to run plug-ins as services.
The Prometheus ecosystem is supported.
New features such as slow SQL root cause analysis and time series forecast are added to optimize the existing AI capabilities.
DB4AI supports more algorithms.

Upgrading the Native DB4AI Engine

In this release of openGauss, the native DB4AI engine further supports more machine learning algorithms, such as the non-linear kernel function of SVM and XGBoost. In addition, openGauss provides the Explain API to view model information.

Supporting AI4DB Plug-ins as Services

The original openGauss AI4DB capability is an offline tool. It cannot monitor the database completely in the background or periodically detect database problems. Therefore, in this release, the openGauss implements the background monitoring service and periodically checks the database system status in the background. In this way, the autonomous database platform DBMind is formed. The diagnosis results are saved in offline calculation mode. Users can use software such as Grafana to visualize the results so that problems can be detected and root causes can be obtained in a timely manner.

The running status of the openGauss database system needs to be periodically monitored in the background. Therefore, you need to connect to the monitoring platform to collect database monitoring metrics and perform offline computation. In this release, openGauss provides two types of exporters to interconnect with the Prometheus platform. The architecture is as follows:

openGauss-exporter is used to obtain monitoring metrics of the database system, and reprocessing-exporter is used to perform secondary processing on data stored in Prometheus. The data of the preceding two exporters can be periodically collected by Prometheus. The DBMind system periodically obtains time series data from Prometheus and performs parallel computing on the DBMind deployment machine. After the computing is complete, the computing result is stored in the meta-database. Then, you may obtain the diagnosis result from the metabase, and further, may perform visualization by configuring Grafana or the like.

The preceding figure shows an example of visualization using Grafana based on data in the meta-database.

In this release, openGauss fully integrates the existing AI capabilities and redesigns them in plug-ins. For example, if you want to call the parameter tuning function to debug database parameters based on reinforcement learning, run the following command:

gs_dbmind component xtuner tune ...

That is, the gs_dbmind command is used to call all AI functions, and the component subcommand is used to call a specific AI function. You can run the following command to view the help information:

gs_dbmind component --help

Based on the preceding design, if developers in the openGauss community want to contribute a certain database AI function, they only need to ensure that the API can be obtained by gs_dbmind. In addition, the developed plug-ins can call all APIs provided by DBMind, for example, the data access interface (DAI) for obtaining data from Prometheus and the database access object (DAO) API for inserting data into the meta-database.

Comprehensively Improving the AI4DB AI Capabilities

In this release, the openGauss upgrades the existing functions such as index recommendation and time series forecast. In addition, it provides root cause analysis and recommendation for slow SQL statements to help DBAs quickly identify slow SQL statements. Based on the monitored database running metrics, it uses the AI feature library identification algorithm to innovatively provide the causes and confidence of slow SQL statements, and provides optimization suggestions.

Laying a Foundation for the Development of a Comprehensive Database AI Autonomous Platform

As mentioned above, openGauss 3.0.0 innovatively integrates historical AI capabilities and discards the burden left over from the historical R&D process, the innovative DBMind platform is service-oriented, offline, plug-in-based, and freely assembled. It is released with the database installation package. You can use tools such as Grafana to customize and visualize the diagnosis result. (We will provide a Grafana example.)

This lays a foundation for us to further upgrade the DBMind platform in the future. It is estimated that openGauss will enrich more AI functions to the platform this year, separate the platform from the existing code repository, and provide a native web front-end and back-end display platform. In addition, the automatic recovery function is supported, enabling users to experience one-click and out-of-the-box database autonomous driving.

[Forward] Parallel Decoding - openGauss 3.0.0

Lei Zhou — Tue, 18 Apr 2023 12:39:42 +0000

This is not my personal original article, the original is here.https://opengauss.org/en/blogs/2022/New-Feature-of-openGauss-3-0-0-Parallel-Decoding.html

Introduction

With the rapid development of information technology, various types of databases emerge one after another. Logical replication is increasingly important, with which data can be synchronized between heterogeneous databases. Currently, the average serial decoding performance of logical replication in openGauss is only 3 to 5 Mbit/s, which cannot meet the requirements of real-time synchronization in heavy service pressure scenarios. As a result, logs are stacked, affecting services in the production cluster. Therefore, the parallel decoding feature is designed to enable multiple threads to perform decoding in parallel, improving the decoding performance. In basic scenarios, the decoding performance can reach 100 Mbit/s.

Design Idea: Why Parallel Decoding Is Considered?

In the original serial decoding logic, a single thread is used to read logs, decode logs, and combine and send results. The following figure shows the main process and time consumption.

It can be learned that most time of the entire process is consumed in the decoding step, which needs to be optimized by multi-thread decoding. In addition, time consumed in the sending step is obviously the second, which needs to be optimized by batch sending.

Working Process: Parallel Decoding Message Sequence Diagram

As shown in the following figure, in parallel decoding, worker threads on an openGauss DN are classified into three types:

Sender/Collector, which receives decoding requests from a client, collects the results of each decoder, and sends the results to the client. Only one sender/collector is created for each decoding request.
Reader/Dispatcher, which reads WALs and distributes them to decoders for decoding. Only one reader/dispatcher is created for a decoding request.
Decoder, which is responsible for decoding the logs sent by the reader/dispatcher (when the thread is decoding the logs, the logs are temporarily stored in the read change queue) and sending the decoding results (when the committed logs are not decoded, the results are temporarily stored in the decode change queue) to the sender/collector. Multiple decoders can be created for a decoding request.

The message sequence is described as follows:

A client sends a logical replication request to a primary or standby DN. In the logical replication options, you can set parameters to connect only to the standby node to prevent the primary node from being overloaded.
In addition to the sender that receives requests from a client, DNs need to create a reader/dispatcher and several decoders.
The reader reads and preprocesses Xlogs. If the logs contain TOAST columns, combine the TOAST columns.
The dispatcher dispatches the preprocessed logs to each decoder.
Each decoder performs decoding independently. You can set the decoding format (.json, .txt, or .bin) through configuration options.
Each decoder sends the decoding result to the collector.
The collector collects decoding results by transaction.
To reduce the number of sending times and the impact of network I/O on the decoding performance, when the batch sending function is enabled (that is, sending-batch is set to 1), the sender accumulates a certain number of logs (the threshold is set to 1 MB) and returns the decoding result to the client in batches.
To stop the logical replication process, disconnect the logical replication connection to the DN.
The sender sends the exit signal to the reader/dispatcher and decoders.
After receiving the exit signal, each thread releases the occupied resources, cleans up the environment, and exits.

Technical Details 1: Visibility Reconstruction

In logical decoding, historical logs are parsed. Therefore, it is important to determine the visibility of tuples in logs. In the original serial decoding logic, the active transaction linked list mechanism is used to determine the visibility. However, for parallel decoding, it is costly for each decoder to maintain an active transaction linked list, which adversely affects the decoding performance. Therefore, visibility reconstruction is performed, and the commit sequence number (CSN) is used to determine tuple visibility. For each XID, the visibility process is as follows:

The main process is as follows:

Obtain a CSN used to determine the visibility based on XID. Ensure that the CSN value can be obtained based on any XID. If the XID is abnormal, a CSN indicating a specific status is returned. This CSN can also be used to determine the visibility.
If the CSN has been committed, it is compared with the CSN in the snapshot. If the CSN of the transaction is smaller, the transaction is visible. Otherwise, the transaction is invisible.
If the CSN is not committed, the transaction is invisible.

Based on the foregoing logic, in parallel decoding, logic for determining tuple snapshot visibility is sequentially determining snapshot visibilities of tuple Xmin (XID during insertion) and Xmax (XID during deletion/update). The overall idea is that if Xmin is invisible/uncommitted or Xmax is visible, the tuple is invisible; if Xmin is visible and Xmax is invisible/uncommitted, the tuple is visible. Each flag bit in the tuple maintains its original meaning and participates in visibility determination.

Technical Details 2: Batch Sending

After parallel decoding is used, the time occupied by the decoding process is significantly reduced. However, in this case, the sender becomes a bottleneck, and costs of performing a complete sending process for each decoding result are excessively high. Therefore, the batch sending mode is used. The decoding results are collected temporarily and sent to the client when the threshold is exceeded. During batch sending, the length of each decoding result and the specified separator need to be recorded so that users of the parallel decoding function can split the logs to be sent in batches.

Usage Mode

The following optional configuration items are added for parallel decoding:

Decoder concurrency

Configure parallel-decode-num to specify the number of decoders for parallel decoding. The value is an integer ranging from 1 to 20. The value 1 indicates that decoding is performed based on the original serial logic and the code logic of this feature is not used. The default value is 1. When this item is set to 1, the decoding format decode-style cannot be configured.

Decoding whitelist

Configure white-table-list to specify the table to be decoded. The value is a character string of the text type that contains table names in the whitelist. Different tables are separated by commas (,). Example: select * from pg_logical_slot_peek_changes('slot1', NULL, 4096, 'white-table-list', 'public.t1,public.t2');

Decoding only on the standby node

Configure the standby-connection parameter to specify whether to perform decoding only on the standby node. The value is of the Boolean type. If the value is true, only the standby node can be connected for decoding. When the primary node is connected for decoding, an error is reported and the decoding exits. If the value is false, there is no restriction. The default value is false.

Decoding format

Configure decode-style to specify the decoding format. The value can be 'j', 't' or 'b' of the char type, indicating the JSON, text, or binary format, respectively. The default value is 'b', indicating binary decoding.

Batch sending

Configure the sending-batch parameter to determine whether to send decoding results in batches. The value is 0 or 1. The default value 0 indicates that batch sending is disabled. The value 1 indicates that batch sending is enabled when the accumulated size of decoding results reaches or just exceeds 1 MB.

The following uses JDBC as an example to describe how to perform parallel decoding. Perform the following configurations when establishing a connection:

PGReplicationStream stream = conn
 .getReplicationAPI()
 .replicationStream()
 .logical()
 .withSlotName(replSlotName)
 .withSlotOption("include-xids", true)
 .withSlotOption("skip-empty-xacts", true)
 .withSlotOption("parallel-decode-num", 10)
 .withSlotOption("white-table-list", "public.t1,public.t2")
 .withSlotOption("standby-connection", true)
 .withSlotOption("decode-style", "t")
.withSlotOption("sending-batch", 1)
     .start();

The added logic is from the sixth line to the second line from the bottom, indicating that 10 concurrent decoding operations are performed, only the public.t1 and public.t2 tables are decoded, the standby node connection is enabled, the decoding format is text, and the batch sending function is enabled. If the parameter value is out of the range, an error is reported and the allowed value range is displayed.

Auxiliary Functions: Monitoring Function

During parallel decoding, the gs_get_parallel_decode_status() function is added to help locate the decoding performance bottleneck when the decoding speed is low. This function is used to check the length of the read change queue that stores logs that have not been decoded and the length of the decode change queue that stores decoding results that have not been sent of each decoder on the current DN.

This function has no input parameter. The return result contains four columns: slot_name, parallel_decode_num, read_change_queue_length, and decode_change_queue_length.

slot_name indicates the replication slot name and its type is text. parallel_decode_num indicates the number of parallel decoding threads and its type is integer. read_change_queue_length records the read change queue length of each decoder and its type is text. decode_change_queue_length records the length of the decode change queue of each decoder and its type is text. The usage is as follows:

If decoding stalls, execute the function on the decoding DN and check the value of read_change_queue_length in the query result. Record the length of the log reading queue in each decoder. If the value is too small, log reading is blocked. In this case, check whether the disk I/O is insufficient. Check the value of decode_change_queue_length in the query result. The value indicates the length of the decoding log queue in each decoder. If the value is too small, the decoding speed is too slow. You can increase the number of decoders. If the values of read_change_queue_length and decode_change_queue_length are large, decoding log sending is blocked. In this case, check the log replay speed of the parallel decoding user in the target database. Generally, the decoding stall is caused by insufficient CPU, I/O, or memory resources. The decoding stall can be avoided by using the standby node to ensure sufficient resources.

Conclusion

Parallel decoding can greatly improve the decoding performance of logical replication.Therefore, it is forgiven even if it increases service pressure on decoding instances. As a key technology of heterogeneous database data replication, parallel decoding plays an important role in openGauss.