occasionally subtle
PUT YOUR BEARD IN MY MOUTH
❣ Chile in a Photography ❣
if i look back, i am lost
Monterey Bay Aquarium
oozey mess
RMH
d e v o n
Game of Thrones Daily
izzy's playlists!
todays bird
Mike Driver
Lint Roller? I Barely Know Her
Kaledo Art
hello vonnie
tannertan36
macklin celebrini has autism
Andulka
@theartofmadeline
JBB: An Artblog!
seen from Brazil
seen from Bangladesh
seen from United States
seen from Germany
seen from United States
seen from United States
seen from United States
seen from United States
seen from United States
seen from United States
seen from United States
seen from United States
seen from United States
seen from United States
seen from Malaysia
seen from Italy
seen from United States
seen from Russia
seen from United States
seen from Malaysia
@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.
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.
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.
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.
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.
openGauss|High Availability (HA)-Adding or Deleting a Standby Node
Adding or Deleting a Standby Node
Availability
This feature is available since openGauss 2.0.0.
Introduction
Standby nodes can be added and deleted.
Benefits
If the read pressure of the primary node is high or you want to improve the disaster recovery capability of the database, you need to add a standby node. If some standby nodes in a cluster are faulty and cannot be recovered within a short period of time, you can delete the faulty nodes to ensure that the cluster is running properly.
Description
openGauss can be scaled out from a single node or one primary and multiple standbys to one primary and eight standbys. Cascaded standby nodes can be added. Standby nodes can be added when a faulty standby node exists in the cluster. One primary and multiple standbys can be scaled in to a single node. A faulty standby node can be deleted.
Standby nodes can be added or deleted online without affecting the primary node.
Enhancements
None.
Constraints
For adding a standby node:
Ensure that the openGauss image package exists on the primary node.
Ensure that the same users and user groups as those on the primary node have been created on the new standby node.
Ensure that the mutual trust of user root and the database management user has been established between the existing database nodes and the new nodes.
Ensure that the XML file has been properly configured and information about the standby node to be scaled has been added to the installed database configuration file.
Ensure that only user root is authorized to run the scale-out command.
Do not run the gs_dropnode command on the primary node to delete other standby nodes at the same time.
Ensure that the environment variables of the primary node have been imported before the scale-out command is run.
Ensure that the operating system of the new standby node is the same as that of the primary node.
Do not perform an primary/standby switchover or failover on other standby nodes at the same time.
For deleting a standby node:
Delete the standby node only on the primary node.
Do not perform an primary/standby switchover or failover on other standby nodes at the same time.
Do not run the gs_expansion command on the primary node for scale-out at the same time.
Do not run the gs_dropnode command twice at the same time.
Before deletion, ensure that the database management user trust relationship has been established between the primary and standby nodes.
Run this command as a database administrator.
Before running commands, run the source command to import environment variables of the primary node.
openGauss|High Availability (HA)-Delayed Replay
Delayed Replay
Availability
This feature is available since openGauss 1.1.0.
Introduction
The time for a standby node to replay can be delayed.
Benefits
By default, the standby server restores the Xlog records from the primary server as soon as possible. This function allows you to delay the time for a standby node to replay Xlog records. In this case, you can query a copy that records data before a period of time, which helps correct errors such as misoperations.
Description
The GUC parameter recovery_min_apply_delay can be used to set the delay time so that a standby server can replay Xlog records from the primary server after a delay time.
Value range: an integer ranging from 0 to INT_MAX. The unit is ms.
Default value: 0 (no delay)
Enhancements
None
Constraints
The recovery_min_apply_delay parameter is invalid on the primary node. It must be set on the standby node to be delayed.
The delay time is calculated based on the timestamp of transaction commit on the primary server and the current time on the standby server. Therefore, ensure that the clocks of the primary and standby servers are the same.
Operations without transactions are not delayed.
After the primary/standby switchover, if the original primary server needs to be delayed, you need to manually set this parameter.
When synchronous_commit is set to remote_apply, synchronous replication is affected by the delay. Each commit message is returned only after the replay on the standby server is complete.
Using this feature also delays hot_standby_feedback, which may cause the primary server to bloat, so be careful when using both.
If a DDL operation (such as DROP or TRUNCATE) that holds an AccessExclusive lock is performed on the primary server, the query operation on the operation object on the standby server will be returned only after the lock is released during the delayed replay of the record on the standby server.
openGauss|Cascaded Standby Server
Cascaded Standby Server
Availability
This feature is available since openGauss 1.1.0.
Introduction
A cascaded standby server can be connected to a standby server based on the one-primary-multiple-standby architecture.
Benefits
The one-primary-multiple-standby architecture cannot support a flexible structure in special service scenarios. The multi-equipment room deployment cannot meet requirements of the complete structure in the HA switchover scenario (three instances in the primary-standby equipment rooms and two or three instances in the standby-standby equipment rooms). If the number of standby servers increases, the primary server may be overloaded. Queries that have low real-time requirements can be implemented on cascaded standby servers. Therefore, the cascading backup capability is required.
Description
The primary server replicates logs to the standby server in synchronous or asynchronous mode. The standby server replicates logs to the cascaded standby server only in asynchronous mode.
In the current one-primary-multiple-standby architecture, the primary server uses the WAL sender process (walsender) to replicate logs to the standby server. The standby server uses the WAL receiver process (walreceiver) to receive and then flushes logs to local disks. The standby server reads redo logs to complete data replication between the primary and standby servers. There is a one-to-one mapping between walsender and walreceiver on the primary and standby servers. Logs are sent between the standby and cascaded standby servers in asynchronous mode using walsender and walreceiver, reducing the streaming replication pressure on the primary server.
Enhancements
None
Constraints
A cascaded standby server can only replicate data from a standby server and cannot directly replicate data from the primary server.
A cascaded standby server does not support data build from a standby server. Currently, data can be built only from the primary server. If the standby server is fully built, the cascaded standby server needs to be fully built.
The cascaded standby node is in asynchronous replication mode.
The cascaded standby server cannot be promoted.
The cascaded standby server cannot be notified.
Currently, the overall architecture of the primary-standby-cascaded standby cluster cannot be queried. You need to find the standby server through the primary server and then find the cascaded standby server based on the standby server.
A cascaded standby server cannot own another cascaded standby server.
When the ultimate RTO is enabled, no cascaded standby server is supported.
Event invitation丨Meet Wuhan on March 17, Yunhe Enmo + Changjiang Kunpeng + openGauss community are waiting for you on the spot!
The "Yunhe Enmo Lecture Hall x Changjiang Kunpeng x openGauss Meetup (Wuhan Station)" co-hosted by Yunhe Enmo, Changjiang Kunpeng and openGauss community will be held on the third floor of the Renaissance Guangming Hotel in Wuhan, Hubei on the afternoon of March 17 The banquet hall is held! We sincerely invite you to visit us!
As the core component of an enterprise IT system, databases are crucial at the moment to grasp the fundamentals of technology and achieve security and stability. Databases have become the primary development front for enterprise database replacement and upgrades and technological innovations. Due to favorable policies in recent years, the domestic database market The proportion is gradually increasing, talents are constantly pouring in, and technology is making rapid breakthroughs. In this context, we discuss database technology innovation and future development with colleagues in the industry, help enterprises in digital transformation and development, jointly create a good ecological environment, and continuously expand the boundaries of technology.
The event was held in Wuhan, a river city where cherry blossoms are in full bloom. Many leaders, technical experts, and academic leaders from the openGauss community, Yunhe Enmo, Changjiang Kunpeng, Huazhong University of Science and Technology, and Shenzhen Lianyou participated in technological innovation , academic innovation, development innovation, ecological co-construction and other dimensions, jointly explore the technical architecture and application system of the openGauss / MogDB database, truly bring enterprise-level database capabilities to users, inject continuous vitality into the sustainable development of the database, and achieve leapfrogging style development.
Event invitation丨Meet Wuhan on March 17, Yunhe Enmo + Changjiang Kunpeng + openGauss community are waiting for you on the spot!
openGauss|High Performance-High Concurrency of Thread Pools
High Concurrency of Thread Pools
Availability
This feature is available since openGauss 1.0.0.
Introduction
The thread pooling technology is used to support stable running of databases at high concurrency.
Benefits
The overall system throughput is stable in case of a large number of concurrent requests.
Description
The overall design idea of the thread pool technology is to pool thread resources and reuse them among different connections. After the system is started, a fixed number of working threads are started based on the current number of cores or user configuration. A working thread serves one or more connection sessions. In this way, the session and thread are decoupled. The number of worker threads is fixed. Therefore, frequent thread switchover does not occur in case of high concurrency. The database layer schedules and manages sessions.
Enhancements
This feature is available since openGauss 1.0.0.
In openGauss 1.1.0, thread pools can be dynamically scaled in or out.
Constraints
None
Dependencies
None
openGauss|High Performance-Kunpeng NUMA Architecture Optimization
Kunpeng NUMA Architecture Optimization
Availability
This feature is available since openGauss 1.0.0.
Introduction
Kunpeng NUMA architecture optimization mainly focuses on Kunpeng processor architecture features and ARMv8 instruction set, and optimizes the system from multiple layers, including OS, software architecture, lock concurrency, logs, atomic operations, and cache access. This greatly improves the openGauss performance on the Kunpeng platform.
Benefits
Transactions per minute (TPM) is a key performance indicator of the database competitiveness. Under the same hardware costs, a higher database performance means the database can process more services, thereby reducing the usage cost of customers.
Description
openGauss optimizes the Kunpeng NUMA architecture based on the architecture characteristics. This reduces cross-core memory access latency and maximizes multi-core Kunpeng computing capabilities. The key technologies include redo log batch insertion, NUMA distribution of hotspot data, and CLog partitions, greatly improving the TP system performance.
Based on the ARMv8.1 architecture used by the Kunpeng chip, openGauss uses the LSE instruction set to implement efficient atomic operations, effectively improving the CPU usage, multi-thread synchronization performance, and Xlog write performance.
Based on the wider L3 cache line provided by the Kunpeng chip, openGauss optimizes hotspot data access, effectively improving the cache access hit ratio, reducing the cache consistency maintenance overhead, and greatly improving the overall data access performance of the system.
Kunpeng 920, 2P server (64 cores x 2, memory: 768 GB), 10 GE network, I/O: 4 NVMe PCIe SSDs, TPC-C: 1000 warehouses, performance: 1,500,000 tpmC.
Enhancements
Batch redo log insertion and CLog partition are supported, improving the database performance on the Kunpeng platform.
Efficient atomic operations using the LSE instruction set are supported, improving multi-thread synchronization performance.
Constraints
None
Dependencies
None
openGauss 3.1.0 |Enhanced Features-Fusion Storage Engine
Fusion Storage Engine
The fusion engine architecture supports the pluggable storage engine architecture. The in-place update storage engine is added. The indexing multiversion supports adding transaction information to indexes. The Xlog lockless update greatly improves the Xlog write efficiency. The parallel page playback improves the playback efficiency of the standby node, and the enterprise-level flashback provides a stable query state for users.
In-place update storage engineThe in-place update storage engine solves the problems of space expansion and large tuples of the Append update storage engine. The design of efficient rollback segments is the basis of the in-place update storage engine.
Indexing multiversionFigure 3 Comparison between UBTree and BTree searching and updating
UBtree can check multiversion concurrency control (MVCC) visibility at the index layer by maintaining version information on tuples on the index page. In addition, the UBtree can independently determine whether the index tuple is dead based on the version information, so that the in-place update engine can implement page-level space cleanup for the data table and index table, and build an independent garbage collection mechanism independent of AutoVacuum.
Xlog lockless updateFigure 4 Xlog lockless design
This feature optimizes the WalInsertLock mechanism by using log sequence numbers (LSNs) and log record counts (LRCs) to record the copy progress of each backend and canceling the WalInsertLock mechanism. The backend can directly copy logs to the WalBuffer without contending for the WalInsertLock. In addition, a dedicated WALWriter thread is used to write logs, and the backend thread does not need to ensure the Xlog flushing. After the preceding optimization, the WalInsertLock contention and WalWriter dedicated disk write threads are canceled. The system performance can be further improved while the original Xlog function remains unchanged.
Parallel page playbackThis feature optimizes the Ustore in-place update WALs and Ustore DML operation parallel playback and distribution. Prefixes and suffixes are used to reduce the update WALs. The playback thread is divided into multiple types to solve the problem that most Ustore DML WALs are replayed on multiple pages. In addition, the Ustore data page playback is distributed based on blkno to improve the degree of parallel playback.
Enterprise-class feature flashbackFlashback is a part of the database recovery technology. It enables the DBA to selectively and efficiently cancel the impact of a committed transaction and restore data from incorrect manual operations. Before the flashback technology is used, the committed database modification can be retrieved only by means of restoring backup and PITR. The restoration takes several minutes or even hours. After the flashback technology is used, it takes only seconds to restore the submitted data before the database is modified. The restoration time is irrelevant to the database size.This feature supports the following flashback modes:
Flashback query: You can query a snapshot of a table at a certain time point in the past. This feature can be used to view and logically rebuild damaged data that is accidentally deleted or modified. The flashback query is based on the MVCC mechanism. You can retrieve and query the old version to obtain the data of the specified old version.
Flashback table: You can restore a table to a specific point in time. When only one table or a group of tables are logically damaged instead of the entire database, this feature can be used to quickly restore the table data. Based on the MVCC mechanism, the flashback table deletes incremental data at a specified time point and after the specified time point and retrieves the data deleted at the specified time point and the current time point to restore table-level data.
Flashback drop: You can restore tables that are deleted by mistake and their auxiliary structures, such as indexes and table constraints, from the recycle bin. Flashback drop is based on the recycle bin mechanism. You can restore physical table files recorded in the recycle bin to restore dropped tables.
Flashback truncate: You can restore tables that are truncated by mistake and restore the physical data of the truncated tables and indexes from the recycle bin. Flashback truncate is based on the recycle bin mechanism. You can restore physical table files recorded in the recycle bin to restore truncated tables.
openGauss 3.1.0 |Enhanced Features-Vectorized Executor and Hybrid Row-Column Storage Engine
Vectorized Executor and Hybrid Row-Column Storage Engine
In a wide table containing a huge amount of data, a query usually only involves certain columns. In this case, the query performance of the row-store engine is poor. For example, a single table containing the data of a meteorological agency has 200 to 800 columns. Among these columns, only 10 are frequently accessed. In this case, the vectorized execution technology and column-store engine can significantly improve performance by saving storage space.
Vectorized executionFigure 1 shows a standard iterator module. Control flow travels in the downlink direction (shown as solid lines in the following figure) and data flow in the uplink direction (shown as dotted lines in the following figure). The upper-layer node invokes the lower-layer node to request data and the lower-layer node only returns one tuple to the upper-layer node at a time.By contrast, the vectorized executor returns a batch of tuples at a time, which significantly improves performance using column store.Figure 1 Vectorized executor
Hybrid row-column storage engineopenGauss supports both the row-store and column-store models. Users can choose a row-store or column-store table based on their needs.Generally, column store is applicable to OLAP service scenarios (The range statistics query and batch import operations are frequent. The update, deletion, point query, and point insertion operations are infrequent. The table contains many columns, that is, a wide table. Only a few columns are involved in the query.) The row store is applicable to OLTP service scenarios (The query, insert, delete, and update operations are frequent. The range statistics query and batch import operations are infrequent. The number of table columns is small. Most columns are queried.)The hybrid row-column storage engine achieves higher data compression ratio (column store), index performance (column store), and point update and point query (row store) performance, as shown in Figure 2.
Figure 2
Hybrid row-column storage engineThe restrictions of the column store engine are as follows:
For DDL statements, only CREATE TABLE, DROP TABLE, and TRUNCATE TABLE are supported.Partition management using DDL statements (such as ADD PARTITION, DROP PARTITION,** MERGE PARTITION**,** and EXCHANGE**) is supported.The CREATE TABLE LIKE statement is supported.The ALTER TABLE statement is partially supported.Other DDL statements are not supported.
For DML statements, UPDATE, COPY, BULKLOAD, and DELETE are supported.
Triggers and primary foreign keys are not supported.
Psort index, B-tree index, and GIN index are supported. For details, see “SQL Reference > SQL Syntax > CREATE INDEX” in the Developer Guide.
Data compression in column storeInactive and earlier data can be compressed to free up space, reducing procurement and O&M costs.In openGauss, data can be compressed using delta encoding, dictionary coder, RLE, LZ4, and ZLIB algorithms. The system automatically selects a compression algorithm based on data characteristics. The average compression ratio is 7:1. Compressed data can be directly accessed and is transparent to services. This greatly reduces the preparation time before accessing historical data.
openGauss joins CNCF Landscape
On March 5, 2023, Beijing time, openGauss joined CNCF Landscape and belongs to the Database category. As of March 5, 2023, CNCF Landscape has collected 1,174 projects, 3,645,319 stars, a market value of 20.7 trillion U.S. dollars, and a capital of 66.9 billion U.S. dollars, which shows that developers and users pay attention to and value this project.
About openGauss:
openGauss is an enterprise-level open source database, oriented to core business scenarios, and supports the digital transformation of enterprise business. Since its official open source launch on June 30, 2020, it has continued to make breakthroughs in core database technologies, and has jointly built a domestic database open source root community with industry chain partners, jointly prospering the database open source ecosystem, and applying it on a large scale in core industry scenarios. The fastest open source database community.
About CNCF Landscape
Cloud Native Computing Foundation, referred to as (CNCF), Chinese name: Cloud Native Computing Foundation, is a foundation under the Linux Foundation. The foundation is committed to promoting the development of cloud-native computing technology and ecology. Its vision is MAKE CLOUD NATIVE UBIQUITOUS, and it is one of the most influential organizations in the cloud/open source field. In order to better manage and promote the cloud-native ecosystem, CNCF established the Landscape project in 2016, which aims to provide a resource map for native applications and help enterprises and developers quickly understand the overall picture of the cloud-native system. CNCF Landscape tracks a large number of applications across the ecosystem by categorizing most projects and products in cloud native technologies. The CNCF Landscape roadmap helps users sort out the best process of the entire cloud-native application from the practical steps.
High Scalability of openGauss
High Scalability
High Concurrency of the Thread Pool
In the OLTP field, a database needs to process a large quantity of client connections. Therefore, the processing capability in high-concurrency scenarios is one of the important capabilities of the database.
The simplest processing mode for external connections is the per-thread-per-connection mode, in which a user connection generates a thread. This mode features simple processing thanks to its architecture. However, in high-concurrency scenarios, there are too many threads, causing heavy workload in thread switchover and large conflict between the lightweight lock areas of the database. As a result, the performance (throughput) deteriorates sharply and the SLA of user performance cannot be met.
Therefore, a thread resource pooling and reuse technology needs to be used to resolve this problem. The overall design idea of the thread pool technology is to pool thread resources and reuse them among different connections. After the system is started, a fixed number of working threads are started based on the current number of cores or user configuration. A working thread serves one or more connection sessions. In this way, the session and thread are decoupled. The number of worker threads is fixed. Therefore, frequent thread switchover does not occur in case of high concurrency. The database layer schedules and manages sessions.
High Performance of openGauss
High Performance
CBO Optimizer
The openGauss optimizer is a typical Cost-based Optimization (CBO). By using CBO, the database calculates the number of tuples and the execution cost for each execution step under each execution plan based on the number of table tuples, column width, NULL record ratio, and characteristic values, such as distinct, MCV, and HB values, and certain cost calculation methods. The database then selects the execution plan that takes the lowest cost for the overall execution or for the return of the first tuple.
The CBO optimizer can select the most efficient execution plan among multiple plans based on the cost to meet customer service requirements to the maximum extent.
Hybrid Row-Column Storage
openGauss supports both row-store and column-store models. Users can choose a row-store or column-store table based on their needs.
Column-store is recommended if a table contains many columns (called a wide table) but its query involves only a few columns. Row-store is recommended if a table contains only a few columns and a query involves most of the columns.
Figure 1 shows the column-store model.
In a wide table containing a huge amount of data, a query usually only includes certain columns. In this case, the query performance of the row-store engine is poor. For example, a single table containing the data of a meteorological agency has 200 to 800 columns. Among these columns, only 10 are frequently accessed. In this case, a vectorized execution and column-store engine can significantly improve performance by saving storage space.
Row-store tables and column-store tables have their own advantages and disadvantages. You are advised to select a table based on the site requirements.
Row-store tableRow-store tables are created by default. Data is stored by row. Row-store supports adding, deleting, modifying, and querying data of a complete row. Therefore, this storage model applies to scenarios where data needs to be updated frequently.
Column-store tableData is stored by column. The I/O of data query in a single column is small, and column-store tables occupy less storage space than row-store tables. This storage model applies to scenarios where data is inserted in batches, less updated, and queried for statistical analysis. The performance of single point query and single record insertion in a column-store table is poor.
The principles for selecting row-store and column-store tables are as follows:
Update frequencyIf data is frequently updated, use a row-store table.
Insert frequencyIf a small amount of data is frequently inserted each time, use a row-store table. If a large amount of data is inserted at a time, use column storage.
Number of columnsIf a table is to contain many columns, use a column-store table.
Number of columns to be queriedIf only a small number of columns (less than 50% of the total) is queried each time, use a column-store table.
Compression ratioThe compression ratio of a column-store table is higher than that of a row-store table. The higher the compression ratio is, the more CPU resources will be consumed.
In-place Upate Storage
The in-place update storage engine solves the problems of space expansion and large tuples of the Append update storage engine. The design of efficient rollback segments is the basis of the in-place update storage engine.
Xlog Lockless Update and Parallel Page Playback
This feature optimizes the WalInsertLock mechanism by using log sequence numbers (LSNs) and log record counts (LRCs) to record the copy progress of each backend and canceling the WalInsertLock mechanism. The backend can directly copy logs to the WalBuffer without contending for the WalInsertLock. In addition, a dedicated WALWriter thread is used to write logs, and the backend thread does not need to ensure the Xlog flushing. After the preceding optimization, the WalInsertLock contention and WalWriter dedicated disk write threads are canceled. The system performance can be further improved while the original XLog function remains unchanged. This feature optimizes the Ustore in-place update WALs and Ustore DML operation parallel playback and distribution. Prefixes and suffixes are used to reduce the update WALs. The playback thread is divided into multiple types to solve the problem that most Ustore DML WALs are replayed on multiple pages. In addition, the Ustore data page playback is distributed based on blkno to improve the degree of parallel playback.
Adaptive Compression
Currently, mainstream databases usually use the data compression technology. Various compression algorithms are used for different data types. If pieces of data of the same type have different characteristics, their compression algorithms and results will also be different. Adaptive compression chooses the suitable compression algorithm for data based on the data type and characteristics, achieving high performance in compression ratio, import, and query.
Importing and frequently querying a huge amount of data are the main application scenarios. When you import data, adaptive compression greatly reduces the data volume, increases I/O operation efficiency several times, and clusters data before storage, achieving fast data import. In this way, only a small number of I/O operations is required and data is quickly decompressed in a query. Data can be quickly retrieved and the query result is quickly returned.
Currently, the database has implemented various compression algorithms, including RLE, DELTA, BYTEPACK/BITPACK, LZ4, ZLIB, and LOCAL DICTIONARY. The following table lists data types and the compression algorithms suitable for them.
For example, large integer compression of mobile number-like character strings, large integer compression of the numeric type, and adjustment of the compression algorithm compression level are supported.
Partition
In the openGauss system, data is partitioned horizontally on an instance using a specified policy. This operation splits a table into multiple partitions that are not overlapped.
In common scenarios, a partitioned table has the following advantages over a common table:
High query performance: You can specify partitions when querying partitioned tables, improving query efficiency.
High availability: If a certain partition in a partitioned table is faulty, data in the other partitions is still available.
Easy maintenance: If a partition in a partitioned table is faulty, only this partition needs to be repaired.
Balanced I/O: Partitions can be mapped to different disks to balance I/O and improve the overall system performance.
Currently, openGauss supports range partitioned tables, list partitioned tables, and hash partitioned tables.
In a range partitioned table, data within a certain range is mapped to each partition. The range is determined by the partition key specified when the partitioned table is created. This partitioning mode is most commonly used.With the range partitioning function, the database divides a record, which is to be inserted into a table, into multiple ranges using one or multiple columns and creates a partition for each range to store data. Partition ranges do no overlap.
In a list partitioned table, data is mapped to each partition based on the key values contained in each partition. The key values contained in a partition are specified when the partition is created.The list partitioning function divides the key values in the records to be inserted into a table into multiple lists (the lists do not overlap in different partitions) based on a column of the table, and then creates a partition for each list to store the corresponding data.
In a hash partitioned table, data is mapped to each partition using the hash algorithm, and each partition stores records with the same hash value.The hash partitioning function uses the internal hash algorithm to divide records to be inserted into a table into partitions based on a column of the table.
If you specify the PARTITION parameter when running the CREATE TABLE statement, data in the table will be partitioned.
Users can modify partition keys as needed during table creation to make the query result stored in the same or least partitions (called partition pruning), so as to obtain consecutive I/O to improve the query performance.
In actual services, time is often used as a filter criterion for query objects. Therefore, you can select the time column as the partition key. The key value range can be adjusted based on the total data volume and the data volume queried at a time.
SQL Bypass
In a typical OLTP scenario, simple queries account for a large proportion. This type of queries involves only single tables and simple expressions. To accelerate such query, the SQL bypass framework is proposed. After simple mode judgment is performed on such query at the parse layer, the query enters a special execution path and skips the classic execution framework, including operator initialization and execution, expression, and projection. Instead, it directly rewrites a set of simple execution paths and directly invokes storage interfaces, greatly accelerating the execution of simple queries.
Kunpeng NUMA Architecture Optimization
Based on the multi-core NUMA architecture of the Kunpeng processor, openGauss optimizes the NUMA architecture to reduce the cross-core memory access latency and maximize the multi-core Kunpeng computing capability. The key technologies include redo log batch insertion, NUMA distribution of hotspot data, and Clog partitions, greatly improving the processing performance of the TP system.
Based on the ARMv8.1 architecture used by the Kunpeng chip, openGauss uses the LSE instruction set to implement efficient atomic operations, effectively improving the CPU usage, multi-thread synchronization performance, and XLog write performance.
Based on the wider L3 cacheline provided by the Kunpeng chip, openGauss optimizes hotspot data access, effectively improving the cache access hit ratio, reducing the cache consistency maintenance overhead, and greatly improving the overall data access performance of the system.
High Concurrency of the Thread Pool
In the OLTP field, a database needs to process a large quantity of client connections. Therefore, the processing capability in high-concurrency scenarios is one of the important capabilities of the database.
The simplest processing mode for external connections is the per-thread-per-connection mode, in which a user connection generates a thread. This mode features simple processing thanks to its architecture. However, in high-concurrency scenarios, there are too many threads, causing heavy workload in thread switchover and large conflict between the lightweight lock areas of the database. As a result, the performance (throughput) deteriorates sharply and the SLA of user performance cannot be met.
Therefore, a thread resource pooling and reuse technology needs to be used to resolve this problem. The overall design idea of the thread pool technology is to pool thread resources and reuse them among different connections. After the system is started, a fixed number of working threads are started based on the current number of cores or user configuration. A working thread serves one or more connection sessions. In this way, the session and thread are decoupled. The number of worker threads is fixed. Therefore, frequent thread switchover does not occur in case of high concurrency. The database layer schedules and manages sessions.
Parallel Query
The Symmetric Multi-Processing (SMP) parallel technology of openGauss uses the multi-core CPU architecture of a computer to implement multi-thread parallel computing, fully using CPU resources to improve query performance. In complex query scenarios, a single query execution takes long time and the system concurrency is low. Therefore, the SMP parallel execution technology is used to implement operator-level parallel execution, which effectively reduces the query execution time and improves the query performance and resource utilization. The overall implementation of the SMP parallel technology is as follows: For query operators that can be executed in parallel, data is sliced, multiple working threads are started for computation, and then the results are summarized and returned to the frontend. The data interaction operator Stream is added to SMP parallel execution to implement data interaction between multiple working threads, ensuring the correctness of the query and completing the overall query.
Dynamic Build and Execution
Based on the query execution plan tree, with the library functions provided by the LLVM, openGauss moves the process of determining the actual execution path from the executor phase to the execution initialization phase. In this way, problems such as function calling, logic condition branch determination, and a large amount of data reading that are related to the original query execution are avoided, to improve the query performance.