OceanBase 3.2.3 release｜HTAP engine has been fully upgraded, and TPC-H performance has been improved by 10 times~

As an enterprise-level native distributed database, OceanBase has been adhering to the development route of native distributed since its inception. Its capabilities such as high compatibility, financial-level disaster tolerance, high availability, transparent expansion, and stability and security have been used in finance, government, operators, Internet Various industries have been fully verified and recognized. In June last year, OceanBase released the milestone 3.1 version, which is positioned as an HTAP enterprise-level database. It supports large-scale transactions of unlimited size, and introduces vectorization engine, parallel DML, and complex query optimization. On the basis of continuously enhancing enterprise-level functions such as diagnostic monitoring capabilities, security, and compatibility, version 3.2 further improves the vectorization engine, and sets a new world record in the TPC-H analytical benchmark with a performance score of 15.26 million QphH@30000GB , currently ranked second.

On April 18, 2022, OceanBase 3.2.3 was officially released , which is the first LTS (Long Term Support) version of 3.x and a milestone version of HTAP capability.

Feature Highlights and User Value

1. Enhance HTAP capability

It fully implements the vectorization engine, supports the vectorization of all basic operators, and brings an order of magnitude performance leap in complex query scenarios. With the continuous enhancement of the optimizer's ability to rewrite optimization and large-scale distributed parallel execution engine, this version is a complete HTAP version. An important milestone in capability, TPC-H performance is 10 times higher than OceanBase 2.2.x.

Second, the performance is greatly improved

Optimize Nest Loop Join, and the performance is improved by 1 times. Supports the conversion of multi-table association SQL statements into Nest Loop Join execution plans, and greatly improves multi-table association performance through operator optimization.

Improve the streaming scan performance of Table API, OceanBase 3.2.3 streaming scan performance is 2.2 times that of HBase 2.4.6.

Improve CDC (OMS) data transmission performance, and save 33% of full migration time in the scenario of a primary key plus a non-unique key index. According to the actual measurement of the scene, the full migration speed can reach 380,000 RPS (64GB memory, 128 concurrent) without affecting the business at all.

3. Improve enterprise-level functions

The new DBLink function in Oracle mode supports users to access Oracle data through DBLink, and meets the needs of users to access remote database data across instances after Oracle migrates to OceanBase.

MySQL mode introduces the Sequence function of Oracle mode. Compared with MySQL's self-incrementing column, the Sequence function allows users to generate unique sequence values in a more flexible way, and set the sequence value generation method, such as supporting resetting the starting point of the sequence, supporting cyclic value selection, Supports specifying cache size, etc. It is worth mentioning that this version is compatible with the CTE (Common Table Expression) and CHECK check constraint functions of MySQL 8.0, supports users to simulate Oracle's CONNECT BY recursive query function through the CTE function, and provides the CHECK constraint function to facilitate users to verify the validity of data check.

Fourth, strengthen the diagnosis and monitoring capabilities

Stored procedures support GET DIAGNOSTICS diagnosis, which can be used to obtain error information when executing SQL statements or stored procedures to help users quickly locate, troubleshoot and solve problems.

OCP introduces the functions of SQL portrait and transaction portrait. Users can obtain statistical information of SQL execution, resource consumption of each operator, transaction holding resources and waiting status, and distributed and parallel execution through SQL portrait and transaction portrait, helping users Faster and more accurate positioning of problems such as slow SQL execution and transaction stuck.

5. Enhanced enterprise-level security

A new backup and recovery integrity check function is added to help users quickly discover silent disk failures and minimize the risk that backup data cannot be used for emergency recovery due to corrupted backup data.

ODC supports the operation record audit function, which automatically records all database change operations to ensure that all changes can be audited and traced back.

Vectorization engine enhances HTAP capabilities, and complex query performance is upgraded

Plan compilation execution and vectorization engine are two important execution optimization ideas proposed by the database academic community in the past ten years. The former was initially explored in the industry by SingleStore (formerly MemSQL). The vectorization engine was first practiced with the MonetDB/X100 system, and the large-scale use of Vectorwise brought excellent performance, which made the industry see the huge performance value and technical prospects of this technology. Since then, including Oracle and SQL Server, the vectorized execution engine capability has been realized through RowSets and so on.

OceanBase 3.2.3 fully implements the vectorization engine, redesigns the core data structure of the execution engine, transforms all operators and the most commonly used execution expressions with an Architecture-aware design, fully explores the Cache features and optimization instructions of modern CPUs, and Applied in the benchmark of TPC-H. Vectorization brings a lot of algorithm optimization possibilities, by optimizing algorithms and data structures under the framework of vectorization. Compared with the non-vectorized execution engine, the overall execution performance is improved by 5 times, and some operators and single scenarios can achieve more than 10 times performance improvement.

The vectorization engine can achieve an order of magnitude performance leap in complex query scenarios. With the continuously enhanced optimizer rewriting and optimization capabilities and large-scale distributed parallel execution engine, OceanBase 3.2.3 version has become an important milestone in building complete HTAP capabilities. TPC -H performance is 1 order of magnitude better than OceanBase 2.2.x.

The performance is greatly optimized to improve the processing capability of typical business scenarios

Nest Loop Join performance optimization

Nest Loop Join is the basic operator behind SQL statements such as multi-table association and index return. OceanBase 3.2.3 has been deeply optimized for single-partition tables and multi-partition tables. Compared with version 3.1.x, whether it is concurrent QPS or single-partition table In terms of query time, the improvement is about 1 times.

Scenario 1: 50 concurrent test QPS, 2 tables with 100,000 rows of data each, and query SQL to associate 200 rows of data.

Scenario 2: Single concurrent test time (RT), the right table has 16 partitions, each table writes 1 million rows of data, and associates 200,000 rows of data for query.

Improve Table API streaming scan performance

In a typical scenario where the Table API scans OceanBase sequentially, when the amount of data to be scanned is relatively large, one API call requires multiple interactions between the client and the server to obtain complete data. This scanning operation is called streaming scanning. During the entire interaction process, server-side transactions and iterator states need to be maintained continuously. The previous version adopts the synchronous waiting implementation method. The worker thread records the context of the request, and the server needs to keep the worker thread for waiting for the client to send the next request. In the case of a large amount of concurrent streaming scanning, there may be a phenomenon that the server still has processing capacity, but all worker threads are waiting and cannot process client requests.

OceanBase 3.2.3 implements asynchronous transformation to decouple the request context from the worker thread. Through this optimization, the overall performance of streaming scanning is improved by more than 2 times, reaching about 2.2 times that of HBase 2.4.6.

The following is a simulated streaming scan test scenario. Each scan involves 8 interactions between the client and the server. After testing, the maximum throughput of each version of OceanBase and HBase 2.4.6 is compared as follows:

Improve CDC (OMS) data transmission performance

In the actual data migration work, users often face scenarios with large amount of table data. When index creation is included in the table creation statement, the full migration of the entire table will cause inefficiency due to real-time index maintenance, which takes a long time. time to complete the migration.

In order to solve this problem, OceanBase 3.2.3 provides the ability to create a non-unique key index after the full data load is completed through CDC (OMS). With the scheduling ability of CDC itself, the overall migration time of tens of millions of tables can be saved by 33% ( The test environment single table has a primary key and a non-unique key index). It is worth mentioning that the more non-unique indexes on a single table, the more obvious the performance improvement. According to the customer's on-site environment test, OMS can achieve a full migration speed of 380,000 RPS (64GB memory, 128 concurrent) without affecting the business, helping users to easily cope with the migration task of hundreds of TB-level databases.

Improve enterprise-level functions for B-end customers and create a better database

Oracle mode: Support database link to Oracle (DBLink)

In the actual business of the enterprise, during the process of migrating Oracle to OceanBase, users usually find that the data has been migrated to OceanBase, but a small part of the data remains in the Oracle database. It is necessary to access the data of the remote database across instances in a way similar to DBlink.

In order to better meet user demands, OceanBase 3.2.3 version adds the DBLink function, which helps users access remote databases in the way of accessing local databases through the cross-data source access capability of distributed databases, and supports cross-data source access. Distributed transaction capabilities. During the execution of SQL query, the dynamic link OCI driver is used to link to the Oracle database in a read-only manner. By embedding the access operation of the Oracle database into the operator execution of OceanBase, the design complexity of multi-data source access of the business system is greatly reduced. At present, the distributed read transaction capability from DBlink to Oracle database has been supported, and the distributed transaction write capability will continue to be iteratively improved in subsequent versions.

MySQL mode: support for Sequence objects

As a frequently used database object in the Oracle business system, Sequence (sequence) can automatically generate unique sequence values according to certain rules, and by modifying the properties of the sequence, this group of values can be incremented, decremented, and circulated.

Starting in 2018, OceanBase provides support for Sequence objects in Oracle mode. As some DB2 and Oracle businesses migrated to OceanBase MySQL mode, OceanBase 3.2.3 officially introduced Sequence support in MySQL syntax mode. Compared with auto-increment (auto_increment), users can have more flexible control over the way of generating sequence values. For example, Sequence supports specifying cache size, looping values, and resetting the starting point.

It is worth noting that Sequence is very suitable for scenarios where there is no strong need to "increment" and only need to generate unique values. You can try to set a larger Sequence cache (for example, 1 million) under a reasonable premise. At this time, the value of the Sequence will be completed in the local cache of each server instance to provide excellent performance.

MySQL Mode: 8.0 Compatible CTE

Common Table Expressions (hereinafter referred to as CTE) means that a temporary result set can be defined in a query statement, or can be understood as a temporary view, which is only valid in this query and can be referenced multiple times. Multiple temporary views can be defined in one query, and views defined later can reference views defined earlier.

The CTE function is often used to define temporary views. Such views generally have the following characteristics: 1) They are not general views and are only used in this query; 2) They are complex or referenced multiple times in the query. In addition to this, CTEs are often used as an alternative to CONNECT BY nested queries. CONNECT BY is a commonly used feature in Oracle and is not supported in MySQL. A simple CONNECT BY example is as follows:

The employee information of a company is recorded in the EMPLOYEE table, and MANAGER_ID represents the ID of the manager of each employee. This CONNECT BY statement shows all direct and indirect subordinates of the employee whose ID is equal to 1. The result of CONNECT BY can be understood as a tree structure, the data that satisfies the START WITH condition is the root node of the tree, and the CONNECT BY clause defines the parent and child The relationship between nodes. In this example, the ID of the parent node is required to be equal to the MANAGER_ID of the child node. LEVEL represents the depth of the node, that is, how many levels are in the tree.

The equivalent CTE statement to the CONNECT BY statement above is as follows:

MySQL Mode: 8.0 Compatible CHECK Constraints

The role of the CHECK constraint in the database is to ensure the validity of the data in the table by restricting the acceptable values in certain columns of the table through a Boolean expression. Every time data is inserted or updated in the table, the Boolean expression of the CHECK constraint in the table is calculated. If the value of the new row is calculated in the expression and the result is FALSE, the CHECK constraint is violated, and the database will reject it. A data change operation for a new row.

Before MySQL version 8.0.16, the statement that creates CHECK constraints is allowed, but the CHECK constraint part is ignored during parsing. Full functionality is supported since version 8.0.16.

OceanBase allows the use of statements to create CHECK constraints in MySQL mode before version 3.2.3, but similar to earlier versions of MySQL, it is only used for partition pruning optimization, and will not make the created CHECK constraints take effect. Starting from OceanBase 3.2.3, it supports the complete CHECK function. Users can create, modify, and destroy CHECK constraints in the CREATE/ALTER/DROP TABLE statement.

Enhanced diagnosis and monitoring capabilities to simplify operation and maintenance

Support GET DIAGNOSTICS diagnostics

When encountering an error in the process of using SQL or PL, the user needs to quickly diagnose what exactly happened, so as to locate, troubleshoot and solve the problem. The OceanBase MySQL mode implements the function of Get Diagnostics, which is used to obtain error information related to the content in the diagnostic area. Get Diagnostics is mainly used to display some error messages and other feedback information generated after the execution of SQL commands.

SQL Profile and Transaction Profile

The SQL of large enterprise systems is often complex, changes frequently, and may deteriorate due to factors such as data changes in operation or database version upgrades. Therefore, turning professional SQL diagnostic capabilities into products is particularly important for large IO-intensive database systems. Through the SQL diagnosis module of OCP, OceanBase comprehensively monitors and analyzes the running SQL on the line from the perspective of diagnosis-optimization suggestion-tuning emergency. First, find problematic or need-optimized SQL through the Suspicious SQL, TOPSQL, SLOWSQL and Parallel SQL modules. Secondly, make optimization suggestions through the SQL turning advisor tool, including index binding recommendation, optimal index creation recommendation, historical execution plan recommendation, SQL current limiting recommendation, tenant CPU expansion recommendation, etc.; finally, perform tuning emergency operations, such as binding Recommend indexes, quickly close problematic sessions, limit the flow of accessed SQL, etc.

OceanBase version 3.2.3 introduces SQL profiling and transaction diagnosis functions through OCP. Among them, the information displayed by the SQL portrait includes: SQL execution statistics, resource consumption, running time of operators/steps, distributed queries, and parallel queries involving multiple nodes. Waiting for related information. In addition to SQL portraits showing basic SQL information, OCP also provides transaction portraits to show the resources and time held by the transaction where SQL is located, as well as other transaction states (including distributed transactions) blocked by the transaction. Users can analyze the specific reasons why SQL transactions are time-consuming or resource-consuming through SQL profiles and transaction profiles, so as to quickly locate the root causes of problems such as slow SQL execution and transaction stuck.

Added backup checksum and change operation audit to improve database security

Backup file integrity check

Data backup and log backup are important auxiliary means to improve the security of user data. When a failure such as a disk silence error occurs on the backup medium, the backup data is usually damaged and cannot be used for subsequent emergency recovery.

The new version supports the backup and recovery integrity check function, and uses the checksum recorded in the data backup and log archive files to detect data corruption in advance. The difficulty in implementing this function is that it needs to make full use of the distributed computing power of OceanBase to distribute the backup verification task to all OBServer worker nodes. Through its built-in distributed scheduling capability, it sends verification tasks to OBServer in batches at the granularity of partition groups, helping users to quickly find disk silence errors. Users can view the result of this data verification through CDB_OB_BACKUP_VALIDATION_JOB_HISTORY. If there is a disk silent error, the corresponding error code will be observed in the RESULT field.

Change Operation Audit

ODC public connection read-only users are prohibited from performing database operations (DDL/DML). To ensure the security of the database change process, the administrator can configure the approval flow in the ODC public resource console task process, such as defining the number of risk levels, selecting tasks Subclassing, specifying the range of SQL quantities for changes, setting up approval nodes, etc. After the administrator configures the approval flow, users who are assigned the relevant read-only permissions can create tasks such as simulated data/import/export/database change in the task center, and match the corresponding approval process according to the created task type. approval. OceanBase version 3.2.3 introduces the change operation audit function, which automatically records all database change operations, making all changes auditable and traceable.

OceanBase 3.2.3, as the LTS version of 3.x, is an important milestone in the complete HTAP capability. This version fully implements the vectorization engine, further improves performance, and improves enterprise-level functions such as diagnostic monitoring, security, and compatibility. In the process of version development, OceanBase draws on some of the excellent design concepts from the Oracle database, combined with the user feedback from the OceanBase open source community, and pays high tribute to the explorers and community users of the classic database.