#openGauss

[coolwizardprince]

openGauss Enterprise-Level Features-SQL Hints

SQL Hints

Availability

This feature is available as of openGauss 1.1.0.

Introduction

SQL hints can be used to override execution plans.

Benefits

Improves SQL query performance.

Description

In plan hints, you can specify a join order; join, stream, and scan operations, the number of rows in a result, and redistribution skew information to tune an execution plan, improving query performance.

[deafeningavenuewonderland]

openGauss Database Audit

[daboom-ru]

Huawei и OrangePi готовят альтернативу Raspberry Pi Huawei в сотрудничестве с OrangePi представили новый одноплатный компьютер OrangePi Kunpeng Pro, ориентированный на разработчиков, студенто... https://daboom.ru/huawei-i-orangepi-gotovjat-alternativu-raspberry-pi/?feed_id=29331&_unique_id=6640e9e86ec39

[click2cloud-social]

Click2Cloud has successfully integrated openGauss database in CloudsBrain that seamlessly migrate data from other databases to openGauss.

[yueminli]

New Feature of openGauss 3.0.0: Parallel Decoding

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.

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.

[kenzingmedia]

https://dba.stackexchange.com/questions/314522/what-database-is-the-opengauss-database-is-it-a-gaussdb

[5llowance]

Thoughts on China's database via /r/China

Thoughts on China's database

Speaking of open source databases in China. When they were closed, domestic database companies still had something to boast about; now that they are open, the source code expose their lack of innovation flat out.

​

Huawei's openGauss can only be considered an improved version of PostgreSQL, and in order to support sharding, it has to work with traditional middleware such as ShardingSphere, which I just extended a few months ago to support MySQL and PostgreSQL protocols.

​

TiDB/TiKV, whose core storage engine is RocksDB, mutilated ClickHouse

to support OLAP, and uses Raft as consistency protocol. TiDB OLTP SQL engine is also not much special. Down the years I found that the company has rarely come up with any original technology worth mentioning, but it stays in the graces of venture capitalists.

​

Ali, recently, opened its source of PolarDB-X. Prior to this I thought highly of it and gave it 75 points; having read the code, I offered 55 to the bummer. So many people over the years only came up with such a dud, which is rather disappointing, and what's more, since I worked with Ali before, I had set the bar a little higher.

​

And the PolarDB for PostgreSQL, might as well be shut down and thrown into the trash bin, and join the Huawei family to engage in openGauss to save a few coders for Ali.

​

Nevertheless independent from Ant Financial Services, OceanBase surprises me, although it hasn't provided heavyweight original technology. At least the project code is relatively good compared to all the above databases.

Submitted October 21, 2021 at 05:25PM by Oswinthegreat via reddit https://ift.tt/3jrAKlL

[trendingph]

Huawei releases six groundbreaking products to supercharge the Cloud and intelligent transformation of business At the Huawei Developer Conference (HDC.Cloud) 2021, Huawei launched six innovative products and services, including HUAWEI CLOUD CCE Turbo cloud container cluster, CloudIDE intelligent programming assistant, GaussDB (for openGauss) database, Trusted... https://trendingph.net/huawei-releases-six-groundbreaking-products-to-supercharge-the-cloud-and-intelligent-transformation-of-business/?feed_id=206196&_unique_id=6087d0ca31752 #business #cloud #groundbreaking #huawei #intelligent #philippinenews #philippinesnews #products #releases #supercharge #transformation #trendingph

[coolwizardprince]

openGauss Enterprise-Level Features-Support for Functions and Stored

Support for Functions and Stored Procedures

Availability

This feature is available since openGauss 1.1.0.

Introduction

Functions and stored procedures are important database objects. They encapsulate SQL statement sets used for certain functions so that the statements can be easily invoked.

Benefits

Allows customers to modularize program design and encapsulate SQL statement sets, easy to invoke.

Caches the compilation results of stored procedures to accelerate SQL statement set execution.

Allows system administrators to restrict the permission for executing a specific stored procedure and controls access to the corresponding type of data. This prevents access from unauthorized users and ensures data security.

Description

openGauss supports functions and stored procedures compliant with the SQL standard. The stored procedures are compatible with certain mainstream stored procedure syntax, improving their usability.

[deafeningavenuewonderland]

openGauss Data Encryption and Storage

[yueminli]

New Feature of openGauss 3.0.0: Parallel Decoding

New Feature of openGauss 3.0.0: Parallel Decoding

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.

[coolwizardprince]

openGauss Database Security-Access Control Model

Availability

This feature is available as of openGauss 1.1.0.

Introduction

The access control model can be used to manage users' access permissions and grant them the minimum permissions required for completing a task.

Benefits

You can create users and grant permissions to them as needed to minimize risks.

Description

The database provides a role-based access control model and an access control model based on the separation of duties. In the role-based access control model, database roles are classified into system administrator, monitoring administrator, O&M administrator, security policy administrator, and common user. The security administrator creates roles or user groups and grant permissions to roles. The monitoring administrator views the monitoring views or functions in dbe_perf mode. The O&M administrator uses the Roach tool to back up and restore the database. The security policy administrator creates resource labels, anonymization policies, and unified audit policies. A user who is assigned a role has the role's permissions.

In the access control model based on the separation of duties, database roles are classified into system administrator, security administrator, audit administrator, monitoring administrator, O&M administrator, security policy administrator, and common user. The security administrator creates users, the system administrator grants permissions to users, and the audit administrator audits all user behavior.

By default, the role-based access control model is used. To switch to another mode, set the GUC parameter enableSeparationOfDuty to on.

Enhancements

None

Constraints

The permissions of the system administrator are controlled by the GUC parameter enableSeparationOfDuty.

The database needs to be restarted when the separation of duties is enabled, disabled or switched. In addition, improper user permissions in the new model cannot be automatically identified. The database administrator needs to manually identify and rectify the fault.

[coolwizardprince]

openGauss | Maintainability-System KPI-aided Diagnosis

System KPI-aided Diagnosis

Availability

This feature is available since openGauss 1.0.0.

Introduction

KPIs are views of key performance indicators for kernel components or the entire system. Based on KPIs, users can learn about the real-time and historical running status of the system.

Benefits

Summarized system load diagnosisPrecise alarms for system load exceptions (overload, stall, and SLA exceptions) and precise system load profile

Summarized system time model diagnosisInstance-level and query-level time model segmentation, diagnosing the root causes of instance and query performance problems

Query performance diagnosisDatabase-level query summary, including top SQL, SQL CPU usage, I/O consumption, execution plan, and excessive hard parsing

Diagnosis of disk I/O, index, and buffer performance problems

Diagnosis of connection and thread pool problems

Diagnosis of checkpoint and redo (RTO) performance problems

Diagnosis of system I/O, LWLock, and wait performance problemsDiagnosis of over 60 modules and over 240 key operation performance problems

Function-level performance monitoring and diagnosis (by GSTRACE)Tracing of over 50 functions at the storage and execution layers

Description

openGauss provides KPIs of 11 categories and 26 sub-categories, covering instances, files, objects, workload, communication, sessions, threads, cache I/O, locks, wait events, and clusters.

Figure 1 shows the distribution of kernel KPIs.

Enhancements

None.

Constraints

Utility statements do not support normalization. Non-DML statements, such as CREATE, DROP, COPY, and VACUUM, are not supported.

Currently, only the top-level normalized SQL statements are recorded. SQL statements in a stored procedure are not normalized, and only the SQL statements that call the stored procedure are recorded.

Dependencies

None.

[coolwizardprince]

The openGauss community user committee working meeting was successfully held

On March 9, 2023, the working meeting of the openGauss Community User Committee was successfully held in Beijing.

OpenGauss community director Jiang Dayong, openGauss community secretary-general Hu Zhengce, openGauss community user committee chairman Xiao Feng, and user committee members attended the meeting.

The openGauss Community User Committee is committed to collecting the technical and product requirements of the openGauss database, feeding back to the technical committee and the council, promoting the openGauss technical route to meet the needs of end users, and providing market opinions for the development direction and long-term planning of the community.

This meeting aims to summarize the work results of the past year and point out the direction for continuous innovation and healthy development in 2023. Members of the Community Council and the User Committee work together to contribute to the development of the community, the direction of technology, and the implementation of business.