OceanBase Database Community Edition V4.3.0

V4.3.0_CE

Version information

• Release date: March 28, 2024
• Version: V4.3.0_CE_BETA
• RPM version: oceanbase-ce-4.3.0.1-100000242024032211

Overview

OceanBase Database V4.3.0 is released to accommodate typical analytical processing (AP) scenarios in addition to transaction processing (TP) and lightweight AP scenarios. It provides a columnar engine based on the log-structured merge-tree (LSM-tree) architecture to implement integrated row- and column-based data storage. Powered by a new vectorized engine that is based on column data format descriptions and a cost model that is based on columnar storages, this version supports efficient processing of wide tables. This greatly improves the query performance in AP scenarios while ensuring the performance in TP business scenarios. Moreover, the new version includes a materialized view feature, which pre-evaluates and stores view query results to enhance real-time query performance. You can use materialized views for quick report generation and data analytics. The kernel of this version supports online DDL and tenant cloning features, optimizes the parallel DML (PDML) and node restart performance, and improves the direct load efficiency for data of large object (LOB) types. It also supports AWS Simple Storage Service (S3) as the backup and restore media, optimizes system resource usage, and provides features such as index usage monitoring and local import to improve the ease of use of the system. We recommend that you use this version in hybrid load scenarios such as complex analytics, real-time reports, real-time data warehousing, and online transactions.

Key features

Key AP features

• Columnar engine

  Columnar storage is crucial for AP databases in scenarios involving complex analytics or ad-hoc queries on a large amount of data. A columnar storage differs from a row-based storage in that it physically arranges data in tables based on columns. When data is stored in columnar storages, the engine can scan only the column data required for query evaluation without scanning entire rows in AP scenarios. This reduces the usage of I/O and memory resources and increases the evaluation speed. In addition, columnar storage naturally provides better data compression conditions to achieve a higher compression ratio, thereby reducing the storage space and network bandwidth required.

  However, common columnar engines are implemented generally based on the assumption that the data organized by column is static without massive random updates. In the case of massive random updates, system performance issues are unavoidable. The LSM-Tree architecture of OceanBase Database can resolve this problem by separately processing baseline data and incremental data. Therefore, OceanBase Database V4.3.0 supports the columnar engine based on the current architecture. It implements columnar storage and rowstores on the same OBServer node based on a single set of code and architecture, ensuring both TP and AP query performance.

  The columnar engine is optimized in terms of optimizer, executor, DDL processing, and transaction processing modules to facilitate AP business migration and improve ease of use in the new version. Specifically, a columnar storage-based new cost model and a vectorized engine are introduced, the query pushdown feature is extended and enhanced, and new features such as the Skip Index attribute, new column-based encoding algorithm, and adaptive compactions are provided.

  You can flexibly set a table in your business system as a row-based storage table, columnar storage table, or row/column redundant table based on the load type. For more information about columnar storage, see Columnstore.

• New vectorized engine

  OceanBase Database has implemented a vectorized engine based on uniform data descriptions in earlier versions, which obviously improves the performance in contrast to non-vectorized engines but is incompetent in deep AP scenarios. OceanBase Database V4.3.0 implements vectorized engine 2.0 that is based on column data format descriptions. This avoids memory use, serialization, and read/write overheads caused by ObDatum maintenance. Based on the column data format descriptions, OceanBase Database V4.3.0 also reimplements more than 10 common operators such as HashJoin, AGGR, HashGroupBy, and Exchange (DTL Shuffle), and about 20 MySQL expressions including relational operation, logical operation, and arithmetic operation expressions. Based on the new vectorized engine, OceanBase Database will implement more operators and expressions in later V4.3.x versions to achieve higher performance in AP scenarios.

• Materialized views

  The materialized view feature is introduced since OceanBase Database V4.3.0. This feature is a key feature for AP business scenarios. It pre-evaluates and stores the view query results to improve the query performance and simplify the query logic by reducing real-time evaluations. This feature applies to quick report generation and AP scenarios.

  A materialized view stores query result sets to improve the query performance and depends on the data in the base table. When data in the base table changes, the data in the materialized view must be updated accordingly to ensure synchronization. Therefore, this version introduces a materialized view refresh mechanism, which supports two strategies: complete refresh and fast refresh. In a complete refresh of a materialized view, the system re-executes the query statements corresponding to the materialized view and overwrites the original query result sets with new ones. Complete refreshes apply to scenarios with small amounts of data. In a fast refresh, the system needs to process only the data changed since the last refresh. To implement accurate fast refreshes, OceanBase Database provides the materialized view log feature, which is similar to Oracle Materialized View Log (MLOG). The incremental data updates in the base table are logged to ensure that materialized views can be fast refreshed. Fast refreshes apply to business scenarios with large amounts of data and frequent data changes. For more information, see Materialized views (MySQL-compatible mode).

Kernel enhancements

• Enhancement of the row-based cost estimation system

  As the OceanBase Database version evolves, more cost estimation methods are supported for optimizers. For row-based cost estimation by each operator, a variety of algorithms, such as storage-layer cost estimation, statistics cost estimation, dynamic sampling, and default statistics, are supported. However, no clear cost estimation strategies or control measures are available. OceanBase Database V4.3.0 restructures the row-based cost estimation system. Specifically, it prioritizes cost estimation strategies based on scenarios and provides methods such as hints and system variables for manually intervening in the selection of a cost estimation strategy. This version also enhances the predicate selectivity and number of distinct values (NDV) calculation framework to improve the accuracy of cost estimation by optimizers.

• Enhancement of the statistics feature

  OceanBase Database V4.3.0 improves the statistics feature in terms of functionality, statistics collection performance, compatibility, and ease of use. Specifically, this version restructures the offline statistics collection process to improve the statistics collection efficiency, and optimizes the statistics collection strategies. By default, OceanBase Database of this version automatically collects information about index histograms and uses derived statistics. This version ensures transaction-level consistency of statistics collected online. It is compatible with the DBMS_STATS.COPY_TABLE_STATS procedure of Oracle to copy statistics and compatible with the ANALYZE TABLE statement of MySQL to support more syntaxes. Moreover, this version provides a command to cancel statistics collection, supports statistics collection progress monitoring, and enhances the ease of maintenance. It also supports parallel deletion of statistics.

• Adaptive cost model

  In earlier versions of OceanBase Database, the cost model uses constant parameters evaluated by internal servers as hardware system statistics. It uses a series of formulas and constant parameters to describe the execution overhead of each operator. In actual business scenarios, different hardware environments can provide different CPU clock frequencies, sequential/random read speeds, and NIC bandwidths. The differences may contribute to cost estimation deviations. Due to the deviations, the optimizer cannot always generate the optimal execution plan in different business environments. This version optimizes the implementation of the cost model. The cost model can use the DBMS_STATS package to collect or set system statistics parameters to adapt to the hardware environment. The DBA_OB_AUX_STATISTICS view is provided to display the system statistics parameters of the current tenant.

• Fixing of session variables for function indexes

  When a function index is created on a table, a hidden virtual generated column is added to the table and defined as the index key of the function index. The values of the virtual generated column are stored in the index table. The results of some built-in system functions are affected by session variables. The evaluation result of a function varies based on the values of session variables, even if the input arguments are the same. When a function index or generated column is created in this version, the dependent session variables are fixed in the schema of the index column or generated column to improve stability. When values of the index column or generated column are calculated, fixed values are used and are not affected by variable values in the current session. In OceanBase Database V4.3.0, system variables that can be fixed include timezone_info, nls_format, nls_collation, and sql_mode.

• Online DDL extension in MySQL-compatible mode

  OceanBase Database of this version supports online DDL operations for column type changes in more scenarios, including:

  • Conversion of integer types: Online DDL operations, instead of offline DDL operations, are performed to change the data type of a primary key column, index column, generated column, column on which a generated column depends, or column with a UNIQUE or CHECK constraint to an integer type with a larger value range.
  • Conversion of the DECIMAL data type: For columns that support the DECIMAL data type, online DDL operations are performed to increase the precision within any of the [1,9], [10,18], [19,38], and [39,76] ranges without changing the scale.
  • Conversion of the BIT or CHAR data type: For columns that support the BIT or CHAR data type, online DDL operations are performed to increase the width.
  • Conversion of the VARCHAR or VARBINARY data type: For columns that support the VARCHAR or VARBINARY data type, online DDL operations are performed to increase the width.
  • Conversion of the LOB data type: To change the data type of a column that supports LOB data types to a LOB data type with a larger value range, offline DDL operations are performed for columns of the TINYTEXT or TINYBLOB data type, and online DDL operations are performed for columns of other data types.
  • Conversion between the TINYTEXT and VARCHAR data types: For columns that support the TINYTEXT data type, online DDL operations are performed to change the VARCHAR(x) data type to the TINYTEXT data type if x <= 255, and offline DDL operations are performed if otherwise. For columns that support the VARCHAR data type, online DDL operations are performed to change the TINYTEXT data type to the VARCHAR(x) data type if x >= 255, and offline DDL operations are performed if otherwise.
  • Conversion between the TINYBLOB and VARBINARY data types: For columns that support the TINYBLOB data type, online DDL operations are performed to change the VARBINARY(x) data type to the TINYBLOB data type if x <= 255, and offline DDL operations are performed if otherwise. For columns that support the VARBINARY data type, online DDL operations are performed to change the TINYBLOB data type to the VARBINARY(x) data type if x >= 255, and offline DDL operations are performed if otherwise.

• Globally unique client session IDs

  If OceanBase Database is of a version earlier than V4.3.0 and OceanBase Database Proxy (ODP) is of a version earlier than V4.2.3, the client session ID of ODP is returned if you execute the SHOW PROCESSLIST statement in ODP to query the session ID, and the server session ID is returned if you query the session ID by using an expression such as connection_id or from a system view. One client session ID corresponds to multiple server session IDs, making it difficult to use a unique ID to identify a session on the entire link. As a result, you can be easily confused when you query session information, which causes inconveniences in user session management. This version restructures the client session ID generation and maintenance process. If OceanBase Database is of V4.3.0 or later and ODP is of V4.2.3 or later, when you query a session ID by executing the SHOW PROCESSLIST statement, from the information_schema.PROCESSLIST or GV$OB_PROCESSLIST view, or by using the connection_id, userenv('sid')/userenv('sessionid'), or sys_context('userenv','sid')/sys_context('userenv','sessionid') expression, the client session ID is returned. You can manage client sessions by using the KILL statement in SQL or PL. If OceanBase Database or ODP does not meet the version requirement, the handling method in earlier versions is used.

• Renovation of the log stream state machine

  In this version, the status of a log stream is subject to the memory status and persistence status. The persistence status indicates the lifecycle of the log stream. After the log stream is restarted upon a server breakdown, the presence status and memory status of the log stream are determined based on the persistence status. The memory status is the running status of the log stream. It indicates the overall status of the log stream and the status of key submodules. Based on the explicit status and status sequence of the log stream, underlying modules can determine which operations of the log stream are safe and whether the log stream has changed from one state to another and then changed back to the original state. The working status and performance of a log stream after it is restarted upon a server breakdown are optimized for backup and restore processes and migration processes. This improves the stability of log stream features and enhances the concurrency control over log streams.

• Tenant cloning

  OceanBase Database V4.3.0 introduces the tenant cloning feature. You can execute the CREATE TENANT new_tenant_name FROM source_tenant_name WITH RESOURCE_POOL [=] resource_pool_name, UNIT [=] unit_config statement in the sys tenant to clone the specified tenant. The cloned tenant is a standby tenant. You can execute the ALTER SYSTEM ACTIVATE STANDBY TENANT new_tenant_name statement to switch the cloned tenant to the PRIMARY role to provide services. The cloned tenant and original tenant share the physical macroblocks. However, new data changes and resource usage are isolated by tenant. If you want to perform temporary data analysis or other risky operations with high resource consumption on an online tenant, you can clone the tenant and perform analysis or verification on the cloned tenant to avoid affecting the online tenant. You can also clone a tenant for disaster recovery. When an unrecoverable misoperation is performed on the original tenant, you can use the cloned tenant for data rollback.

Compatibility with MySQL

• utf8mb4_unicode_ci and utf16_unicode_ci collations

  This version supports the utf8mb4_unicode_ci and utf16_unicode_ci collations.

Performance improvements

• OceanBase zlib_lite_1.0 compression algorithm

  OceanBase Database V4.3.0 introduces the zlib_lite_1.0 data compression algorithm. This compression algorithm is based on Intel In-Memory Analytics Accelerator (Intel® IAA) and makes full use of hardware features to improve the data compression and decompression performance while ensuring compatibility with the conventional zlib library. On a server equipped with Intel IAA, the zlib_lite_1.0 compression algorithm can remarkably improve the compression and decompression performance. When hardware acceleration conditions are not met, the software acceleration method is automatically used to ensure compatibility across platforms.

• PDML transaction optimization

  This version supports parallel commit and log replay at the transaction layer, and provides partition-level rollback inside transaction participants, which helps significantly improve the DML execution performance in high concurrency scenarios in contrast to earlier V4.x versions.

• Optimization of I/O usage in loading tablet metadata

  OceanBase Database V4.x supports millions of partitions on a single server and on-demand loading of metadata because the metadata of millions of tablets cannot be all stored in the memory. On-demand loading is supported at the partition and subcategory levels. Metadata in a partition is divided into different subcategories for layered storage. When a background task requires metadata of a deep level, reading the data results in a high I/O overhead. A high I/O overhead is acceptable for a local solid-state disk (SSD) but may compromise the system performance in scenarios where a hard disk drive (HDD) or a cloud disk is used. This version aggregates the frequently accessed metadata for storage, reducing the number of I/O times required for accessing the metadata to 1. This greatly decreases the I/O overhead in the case of no load and prevents the I/O overheads of background tasks from affecting the query performance in the foreground. The process of loading metadata upon an OBServer node restart is also optimized. Specifically, tablet metadata is batch loaded based on macroblocks. This significantly reduces discrete read I/O operations and increases the restart speed by multiple times or even dozens of times.

High availability enhancements

• Proactive broadcasting/refreshing of tablet locations

  OceanBase Database provides the periodic location cache refreshing mechanism to ensure that the location information of log streams is updated in real time and is consistent. However, tablet location information can only be passively refreshed. Changes in the mappings between tablets and log streams can trigger SQL retries and read/write errors with a certain probability. OceanBase Database V4.3.0 supports proactive broadcasting of tablet locations to reduce SQL retries and read/write errors caused by changes in mappings after transfer. This version also supports proactive refreshing to avoid unrecoverable read/write errors.

• AWS S3 supported for backup and restore

  OceanBase Database supports Network File System (NFS), Alibaba Cloud Object Storage Service (OSS), and Tencent Cloud Object Storage (COS) as the storage media for the backup and restore feature in earlier versions. OceanBase Database V4.3.0 further supports AWS S3 as the storage media for backup and restore. You can use AWS S3 as the destination for log archiving and data backup, and use the backup data on AWS S3 for physical restore.

• Memory scaling limitations

  This version improves the stability in memory scaling and avoids out-of-memory (OOM) errors caused by an improper memory_limit value. Two conditions must be met for the memory_limit setting to take effect on an OBServer node: the reserved memory of the sys500 tenant is not less than the occupied memory, and the value of memory_limit is greater than the sum of the value of system_memory and the memory allocated to resource units. If either condition is not met, when you set the memory_limit parameter, no error is returned but the parameter setting does not take effect.

• Active transaction transfer

  In the log stream design in OceanBase Database V4.x, data is managed in the unit of tablet and logs are managed in the unit of log stream. Tablets are aggregated in a log stream to avoid two-phase commit of transactions in the log stream. To achieve a balance of data and traffic among different log streams, OceanBase Database allows you to flexibly transfer tablets among log streams. However, during the transfer, an active transaction may still be operating data, which may compromise the atomicity, consistency, isolation, and durability (ACID) capability of the transaction. For example, if the data of an active transaction at the source is not fully transferred to the destination, the atomicity of the transaction cannot be ensured. In versions earlier than V4.3.0, OceanBase Database will terminate active transactions during the transfer. This affects normal execution of transactions. To resolve this issue, OceanBase Database V4.3.0 supports the transfer of active transactions. This allows parallel execution of active transactions and avoids transaction rollback or inconsistency caused by the transfer.

Resource usage optimization

• MINIMAL mode of transaction logs

  This version restructures the MINIMAL mode of transaction logs to optimize the implementation of the MINIMAL feature in earlier versions and improve the stability of the feature. Enabling the MINIMAL mode significantly decreases the volume of clogs generated for UPDATE and DELETE statements, thereby reducing the resource overheads in log storage, archiving, and transmission. This mode applies to private clouds with limited cross-city network bandwidth resources and public clouds with limited bandwidth resources for writing data to the cloud disk. In OceanBase Database V4.3.0 Beta, this feature is disabled by default because OceanBase Migration Service (OMS) has not been specifically adapted. In a scenario where no tool is required for incremental data synchronization, you can set the system variable binlog_row_image to MINIMAL to enable this feature.

• Memory throttling mechanism

  In OceanBase Database of a version earlier than V4.x, only a few modules require freezes and minor compactions to release the memory, and most of the modules are MemTables. Therefore, a memory limit is set for MemTables and throttling logic is used to ensure that the memory usage smoothly approaches the upper limit, avoiding write stop in the system caused by sudden OOM errors. In OceanBase Database V4.x, more modules, such as the TxData module, require freezes and minor compactions to release the memory. More refined means are provided to control the memory usage of modules. The memory for the TxData and Multi Data Source (MDS) modules is limited. The two modules share the memory with MemTables. When the memory usage reaches the value of Tenant memory × _tx_share_memory_limit_percentage% × writing_throttling_trigger_percentage%, overall throttling is triggered. This version also supports triggering freezes and minor compactions for transaction data tables by time. By default, a freeze is triggered for transaction data tables every 1,800 seconds to reduce the memory usage of the TxData module.

• Optimization of the space for temporary results of DDL operations

  Many DDL operations store temporary results in materialized structures. Here are two typical scenarios:

  1. In an index creation scenario where data is scanned from the data table and inserted into the index table, the data scanned from the data table needs to be sorted. If the memory is insufficient during the sorting, the current data in the memory will be temporarily stored in materialized structures to release the memory space for subsequent scanning. Then, the data in the materialized structures will be merged and sorted. This practice is particularly effective in the case of limited memory but requires extra disk space.
  2. In a columnar storage direct load scenario, the system temporarily stores the data to be inserted into column groups in materialized structures, and then reads the data from the materialized structures when inserting data into each column group. These materialized structures can be used in the SORT operator to store intermediate data required for external sorting. When the system inserts data into column groups, it can cache the data to avoid extra overheads caused by repeated table scanning. This practice can prevent repeated scanning from compromising the performance, but increases the disk space occupied by temporary files.

  To resolve these issues, this version optimizes the data flow of DDL operations. Specifically, it eliminates unnecessary redundant structures to simplify the data flow. It also encodes and compresses the temporary results before storing them in the disk. This way, the disk space occupied by temporary results during DDL operations is significantly reduced, facilitating efficient use of storage resources.

Improvement in ease of use

• Index monitoring

  Indexes are usually created to improve the performance in querying data from a database. The number of indexes created on a data table increases as business scenarios and operation personnel increase over time. Unused indexes will waste the storage space and increase the overheads of DML operations. In this case, constant attention is required to identify and delete useless indexes to reduce the system load. However, it is difficult to manually identify useless indexes. Therefore, OceanBase Database V4.3.0 introduces the index monitoring feature. You can enable this feature for a user tenant and set sampling rules. The index use information that meets the specified rules is recorded in the memory and updated to the internal table every 15 minutes. You can query the DBA_INDEX_USAGE view to verify whether indexes in a table are referenced and delete useless indexes to release the space.

• RPC security certificate management

  After remote procedure call (RPC) authentication is enabled for a cluster, when a client, such as an arbitration service client, primary or standby database, or OceanBase Change Data Capture (CDC) client, initiates an access request to the cluster, you need to first place the root CA certificate of the client to the deployment directory of each OBServer node in the cluster and then complete related settings. This process is complex. OceanBase Database V4.3.0 supports the internal certificate management feature. You can call the DBMS_TRUSTED_CERTIFICATE_MANAGER system package in the sys tenant to add, delete, and modify root CA certificates trusted by a cluster. You can query the DBA_OB_TRUSTED_ROOT_CERTIFICATE view in the sys tenant for the list of root CA certificates added to the cluster, as well as information about the certificates, such as the expiration time.

• Parameter resetting

  In earlier versions, if you want to reset a modified parameter to its default value, you must first query the default value of the parameter and then manually set the parameter to the default value, delivering poor ease of use. In OceanBase Database V4.3.0, the ALTER SYSTEM [RESET] parameter_name [SCOPE = {MEMORY | SPFILE | BOTH}] {TENANT [=] 'tenant_name'} syntax is provided for resetting a parameter to its default value. The default value is obtained from the node that executes the statement. You can reset cluster-level parameters and the parameters of a specified user tenant from the sys tenant. You can reset the parameters of only the current tenant from a user tenant. The implementation of the SCOPE option is consistent across different versions of OceanBase Database. For parameters whose modifications take effect statically, the system only stores their default values in the disk but does not update their values in the memory. For parameters whose modifications take effect dynamically, the system updates their values in the memory and stores their default values in the disk.

• Detailed display of parameter data types

  In OceanBase Database V4.3.0, data types of parameters are displayed in the data_type column of parameter-related views such as [G]V$OB_PARAMETERS and in the return result of the SHOW PARAMETERS statement. For example, the data type of the log_disk_size parameter is CAPACITY, that of rpc_port is INT, and that of devname is STRING.

• INROW storage threshold for LOBs

  A large object (LOB) less than or equal to 4 KB in size is stored in INROW (in-memory storage) mode. A LOB greater than 4 KB is stored in the LOB auxiliary table. The row-based storage feature of INROW storage provides higher performance than auxiliary table-based storage in some scenarios. Therefore, OceanBase Database V4.3.0 supports dynamic configuration of the LOB storage mode. You can dynamically adjust the INROW storage size as needed provided that the size does not exceed the maximum row size allowed.

• Local import from the client

  OceanBase Database V4.3.0 provides the local import feature (LOAD DATA LOCAL INFILE statement) for loading data from local files on the client in streaming mode. This way, developers can directly use local files for testing without the need to upload the files to the server or object storage service, improving the working efficiency in scenarios where a small amount of data needs to be imported.

  Notice

  To use this feature, make sure that the following conditions are met:

  1. The version of OceanBase Client (OBClient) is V2.2.4 or later.
  2. The version of ODP is V3.2.4 or later, if ODP is used for connection to OceanBase Database. If you directly connect to an OBServer node, ignore this requirement.
  3. The version of OceanBase Connector/J is V2.4.8 or later, if Java and OceanBase Connector/J are used.

  Note

  • You can directly use a MySQL client or a native MariaDB client of any version.
  • The SECURE_FILE_PRIV variable specifies the privileges for accessing paths on the server. It does not affect the local import feature and therefore does not need to be specified.

Compatibility changes

Product behavioral changes

View changes

Parameter changes

Function/PL package changes

Syntax changes

• The DDL syntax for pre-aggregating column indexes in SSTables is added.
• The syntax for cloning tenants is added.
• The syntax for resetting parameters is added.
• The columnar storage and columnar storage indexing syntaxes are added.
• Syntaxes related to materialized views are added.
• The syntax for performing partition-level major compactions is added.

Recommended versions of tools

The following table lists the recommended versions of tools for OceanBase Database V4.3.0_CE.

Upgrade notes

• To use OceanBase Database V4.3.0, you need to create a cluster. Smooth upgrade from an earlier version, such as V1.x, V2.x, V3.x, V4.1, or V4.2.x, to V4.3.0 Beta is not supported. If you want to upgrade to V4.3.0, deploy an OceanBase cluster of V4.3.0 and then migrate existing data to the cluster by using obdumper & obloader or OMS.
• Upgrade from V4.3.0 Beta to V4.3.x will be supported later.
• Upgrade from V4.2.x to V4.3.x will be supported in later versions.

Considerations

• We recommend that you set the maximum concurrency for direct load based on the value of Tenant memory × 0.001/2 MB. If the concurrency exceeds this value, the memory for temporary files may be insufficient. When you use direct load, we recommend that you split large files into multiple smaller ones to improve the import efficiency.

• OMS does not support the MINIMAL mode for now. When you use OMS for incremental data synchronization, you are not allowed to set binlog_row_image to MINIMAL. The default value of this parameter is FULL.