OceanBase Database Enterprise Edition V4.3.0

V4.3.0 Beta

Version information

• Release date: March 22, 2024
• Version: V4.3.0 Beta
• RPM version: oceanbase-4.3.0.1-101000062024032200
• Description: The Beta version resolved most of the issues and is becoming more and more stable. However, there may still be some minor issues or errors that need to be addressed in the final stable release, so we recommend that you use this version in a testing environment.

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 storage, 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 an Oracle-compatible 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

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 storage, 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, 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 row-based storage 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.

• 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.

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.

• Fixed session variables for function-based indexes

  When a function-based 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-based 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-based 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 V4.3.0 supports more online DDL scenarios for column type changes, 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 ID

  Prior to OceanBase Database V4.3.0 and OceanBase Database Proxy (ODP) V4.2.3, when the client executes SHOW PROCESSLIST through ODP, the client session ID in ODP is returned. However, when the client queries the session ID by using an expression such as connection_id or from a system view, the session ID on the server is returned. A client session ID corresponds to multiple server session IDs. This causes confusion in session information queries and makes user session management difficult. In the new version, the client session ID generation and maintenance process is reconstructed. When the version of OceanBase Database is not earlier than V4.3.0 and the version of ODP is not earlier than V4.2.3, the session IDs returned by various channels, such as the SHOW PROCESSLIST command, the information_schema.PROCESSLIST and GV$OB_PROCESSLIST views, and the connection_id, userenv('sid'), userenv('sessionid'), sys_context('userenv','sid'), and sys_context('userenv','sessionid') expressions, are all client session IDs. You can specify a client session ID in the SQL or PL command KILL to terminate the corresponding session. If the preceding version requirements for OceanBase Database and ODP are not met, the handling method in earlier versions is used.

• Improvement of the log stream state machine

  In OceanBase Database V4.3.0, the log stream status is split into the in-memory status and persistent status. The persistent status indicates the life cycle of a log stream. After the OBServer node where a log stream resides breaks down and then restarts, the system determines whether the log stream should exist and what the in-memory status of the log stream should be based on the persistent status of the log stream. The in-memory status indicates the runtime status of a log stream, representing 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 are safe to the log stream and whether the log stream has gone through a status change of the ABA type. For backup and restore or migration processes, the working status of a log stream is optimized after the OBServer node where the log stream resides restarts. This feature improves the stability of log stream-related features and enhances the concurrency control on log streams.

• Tenant cloning

  OceanBase Database V4.3.0 supports tenant cloning. You can execute CREATE TENANT new_tenant_name FROM source_tenant_name WITH RESOURCE_POOL [=] resource_pool_name, UNIT [=] unit_config in the sys tenant to quickly clone a specified tenant. After a tenant cloning job is completed, the created new tenant is a standby tenant. You can use ALTER SYSTEM ACTIVATE STANDBY TENANT new_tenant_name to convert the standby tenant into the primary tenant to provide services. The new tenant and the source tenant share physical macroblocks in the initial state, but new data changes and resource usage are isolated between the tenants. You can clone an online tenant for temporary data analysis with high resource consumption or other high-risk operations to avoid risking the online tenant. In addition, you can also clone a tenant for disaster recovery. When irrecoverable misoperations are performed in the source tenant, you can use the new tenant for data rollback.

Performance improvements

• PDML transaction optimization

  The new version implements optimizations at the transaction layer by supporting parallel commit, log replay, and partition-level rollbacks within transaction participants. Compared with earlier V4.x versions, the new version significantly improves the DML execution performance in high concurrency scenarios.

• I/O usage optimization for loading tablet metadata

  OceanBase Database V4.x supports millions of partitions on a single machine. As the memory may fail to hold the metadata of millions of tablets, OceanBase Database V4.x supports on-demand loading of tablet metadata. OceanBase Database supports on-demand loading of metadata at the partition level and the subclass level within partitions. In a partition, metadata is split into multiple subclasses for hierarchical storage. In scenarios where background tasks require deeper metadata, the data read consumes more I/O resources. These I/O overheads are not a problem for local SSD disks, but may affect system performance when HDD disks or cloud disks are used. OceanBase Database V4.3.0 aggregates frequently accessed metadata in storage, and only one I/O operation is required to access the metadata. This greatly reduces the I/O overhead in zero load scenarios and avoids the impact on foreground query performance caused by background task I/O overhead. In addition, the metadata loading process during the restart of an OBServer node is optimized. Tablet metadata is loaded in batches at the granularity of macroblocks, greatly reducing discrete I/O reads and speeding up the restart by several or even dozens of times.

High availability enhancements

• Proactive broadcast/refresh of tablet locations

  In earlier versions, OceanBase Database provides the periodic location cache refresh mechanism to ensure that the location information of log streams is updated in real time and 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 broadcast of tablet locations to reduce SQL retries and read/write errors caused by changes in mappings after transfer. It also supports proactive refresh to avoid unrecoverable read/write errors.

• Support for S3 as the backup and restore media

  Earlier versions of OceanBase Database support two types of storage media for backup and restore: file storage (NFS) and object storage (OSS and COS). The new version supports Amazon Simple Storage Service (S3) as the log archive and data backup destination. You can also use backup data on S3 for physical restore.

• Limitations on memory specification adjustment

  In OceanBase Database V4.3.0, the stability of memory specification adjustment is enhanced to avoid the out-of-memory (OOM) problem in the system caused by an improper value of the memory_limit parameter. The memory_limit parameter on an OBServer node takes effect only when both of the following conditions are met: First, the reserved memory for the sys500 tenant is not less than the actual occupied memory. Second, the value of memory_limit is higher than the sum of system_memory and the allocated memory of resource units. If either condition is not met, the memory_limit parameter does not take effect, even when no error is returned.

• Migration of active transactions during tablet transfer

  In the design of standalone log streams, data is in the unit of tablets, while logs are in the unit of log streams. Multiple tablets are aggregated to one log stream, saving the high cost of two-phase commit of transactions within a single log stream. To balance data and traffic among different log streams, tablets can be flexibly transferred between log streams. However, during the tablet transfer process, active transactions may still be handling the data, and even a simple operation may damage the atomicity, consistency, isolation, and durability (ACID) of the transactions. For example, if active transaction data on the transfer source cannot be completely migrated to the transfer destination during concurrent transaction execution, the atomicity of the transactions cannot be guaranteed. In earlier versions, active transactions were killed during the transfer to avoid transaction problems. This mechanism affects the normal execution of transactions to some extent. To solve this problem, the new version supports the migration of active transactions during tablet transfer, which enables concurrent execution of active transactions and ensures that no abnormal rollbacks or consistency issues occur in concurrent transactions due to the transfer.

Resource usage optimization

• MINIMAL mode of transaction logs

  In OceanBase Database V4.3.0, the MINIMAL mode of transaction logs is reconstructed to optimize the implementation of the MINIMAL feature, achieving better feature stability. After the MINIMAL mode is enabled, the volume of generated clogs drops significantly for UPDATE and DELETE operations, which can significantly reduce the resource cost for log storage, archiving, and transmission. This feature is especially useful in private cloud environments with limited cross-region network bandwidth and public cloud environments with limited cloud disk write bandwidth. In V4.3.0 Beta, this feature is disabled by default because OceanBase Migration Service (OMS) has not been adapted to this feature. In scenarios where you do not need to use OMS for incremental data synchronization, you can set the system variable binlog_row_image to MINIMAL to enable this feature.

• Memory throttling mechanism

  Prior to OceanBase Database V4.x, only a few modules release memory based on freezes and minor compactions, and the MemTable is the largest part among them. Therefore, in earlier versions, an upper limit is set for memory usage of the MemTable, enabling it run as smoothly as possible within the memory usage limit and avoiding writing failures caused by sudden memory exhaustion. In OceanBase Database V4.x, more modules that release memory based on freezes and minor compactions are introduced, such as the transaction data module. The new version provides more granular means to control the memory usage of various modules and supports the memory upper limit control of TxData and MDS modules. The two modules share memory space with the MemTable. When the sum of the memory usage of the three modules reaches Tenant memory * _tx_share_memory_limit_percentage% * writing_throttling_trigger_percentage%, overall memory throttling is triggered for the three modules. The new version also supports freezes and minor compactions of the transaction data table by time to reduce the memory usage of the transaction data module. By default, the transaction data table is frozen once every 1800 seconds.

• Optimization of DDL temporary result space

  During the DDL operations, many processes may temporarily store temporary results in materialized structures. Here are two typical scenarios:

  1. During index creation, the system scans data in the base data table and sorts and inserts the obtained data to the index table. If the memory is insufficient during the sorting process, current data in the memory space will be temporarily stored in materialized structures to release the memory space for subsequent scanning. Data in the materialized structures is then merged and sorted. This approach is particularly effective when the memory space is limited, but requires additional space on the disk.
  2. In the columnar storage direct load scenario, the system first temporarily stores the data to be inserted into each column group in materialized structures, and then obtains data from the materialized structures for insertion. 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, the data can be cached in materialized structures, avoiding additional overhead caused by repeated table scanning. Although this method can reduce the performance loss caused by repeated scanning, the temporary files, however, will occupy much disk space.

  In the new version, the data flow in DDL operations is optimized to address these issues. First, the new version eliminates unnecessary redundant structures to simplify the data flow. Second, the new version introduces coding compression for storing temporary results on disks. This way, the disk space consumed by temporary results during DDL operations is significantly reduced, and storage resources are thereby more efficiently utilized.

Improvement in usability

• 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 authentication certificate management

  When RPC authentication is enabled for a cluster, for an access request from a client, such as the arbitration service, primary/standby database, or OBCDC, you need to place the CA root certificate of the client in the deployment directory of each OBServer node in the cluster, and then perform related configurations. This whole process is complicated. OceanBase Database V4.3.0 supports the internal certificate management feature. You can use the DBMS_TRUSTED_CERTIFICATE_MANAGER system package provided in the sys tenant to add, delete, and modify CA root certificates trusted by an OceanBase cluster. The DBA_OB_TRUSTED_ROOT_CERTIFICATE view is also provided in the sys tenant to display the list of client CA root certificates added to OBServer nodes in the cluster and the certificate expiration time.

• Parameter resetting

  In earlier versions, if you want to reset a parameter to the default value, you need to query the default value of the parameter first, and then manually set the parameter to the default value. The new version provides the ALTER SYSTEM [RESET] parameter_name [SCOPE = {MEMORY | SPFILE | BOTH}] {TENANT [=] 'tenant_name'} syntax for you to reset a parameter to the default value. The default value is obtained from the node that executes the statement. You can reset cluster-level parameters or parameters of a specified tenant in the sys tenant. You can also reset parameters for the current user tenant. On OBServer nodes, whether the SCOPE option is specified or not does affect the implementation logic. For a parameter that takes effect statically, the default value is only stored on the disk but not updated to the memory. For a parameter that takes effect dynamically, the default value is stored on the disk and updated to the memory.

• Detailed display of parameter data types

  In the new version, the parameter data type (data_type) is displayed in parameter-related views, such as [G]V$OB_PARAMETERS, and the return results of the SHOW PARAMETERS command. For example, the data type of log_disk_size is CAPACITY, that of rpc_port is INT, and that of devname is STRING. This feature enables peripheral tools to display and limit parameters based on their data type.

• LOB INROW threshold configuration

  By default, LOB data of a size less than or equal to 4 KB is stored in INROW mode, and LOB data of a size greater than 4 KB is stored in the LOB auxiliary table. In some scenarios, INROW storage provides higher performance than auxiliary table-based storage. Therefore, this version supports dynamic configuration of the LOB storage mode. You can adjust the INROW threshold based on your business needs, provided that the threshold does not exceed the limit for INROW storage.

• Local import from the client

  OceanBase Database V4.3.0 supports the LOAD DATA LOCAL INFILE statement for local import from the client. You can use the feature to import local files through streaming file processing. Based on this feature, developers can import local files for testing without uploading files to the server or object storage, improving the efficiency of importing a small amount of data.

  Notice

  To import local data from the client, make sure that:

  1. The OceanBase Client (OBClient) version is V2.2.4 or later.
  2. The version of ODP used to connect to OceanBase Database is V3.2.4 or later. 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 you use Java and OceanBase Connector/J.

  Note

  • You can use the native MySQL or MariaDB client of any versions directly.
  • The SECURE_FILE_PRIV variable is used to specify the server paths that can be accessed by the OBServer node. This variable does not affect local import from a client, and therefore does not need to be set.

Performance test report

Test environment specifications

Here we use three OBServer servers, with the architecture of 1:1:1 to deploy OceanBase Database V4.2.2 and V4.3.0 Beta.

Tenant specifications

• CPU: 28 cores
• Memory: 180 GB
• Replica distribution: F@zone1,F@zone2,F@zone3

Parameter tuning

• sys tenant

  ALTER SYSTEM flush plan cache GLOBAL;
  ALTER SYSTEM SET enable_sql_audit=false;
  ALTER SYSTEM SET enable_perf_event=false;
  ALTER SYSTEM SET syslog_level='PERF';
  ALTER SYSTEM SET enable_record_trace_log=false;
  

• Test tenant

  SET GLOBAL ob_sql_work_area_percentage = 80;
  SET GLOBAL ob_query_timeout = 36000000000;
  SET GLOBAL ob_trx_timeout = 36000000000;
  SET GLOBAL max_allowed_packet = 67108864;
  SET GLOBAL parallel_servers_target = 624;
  

  Notice

  Set the parallel_servers_target variable to max_cpu * server_num * 8.

  In OceanBase Database 4.3.0, change the default storage mode of tables to columnar storage.

  ALTER SYSTEM SET default_table_store_format = 'column';
  

TPC-H 1TB test results

Compatibility changes

Product behavioral changes

View changes

Parameter/System variable changes

Function/PL package changes

Syntax changes

Recommended versions of tools

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

Upgrade notes

• When you use OceanBase Database V4.3.0 for the first time, you need to create a cluster. Smooth upgrade from OceanBase Database V1.x, V2.x, V3.x, V4.1, or V4.2.x to V4.3.0 Beta is not supported. You can use the logical import/export method to migrate data from an earlier version to V4.3.0 Beta for the purpose of upgrade.
• Downtime upgrade from OceanBase Database V4.3.0 Alpha to V4.3.0 Beta is supported. Smooth upgrade from V4.3.0 Alpha Hotfix1 to V4.3.0 Beta is supported.
• Upgrade from V4.3.0 Beta to V4.3.x will be supported later.
• As the version evolves, upgrade paths from V4.2.x to V4.3.x will be supported.

Considerations

• We recommend that you set the maximum concurrency for direct load to Tenant memory * 0.001/2 MB. If you set the concurrency to a larger value, memory for storing temporary files may be insufficient. We recommend that you split a large file into multiple small ones for direct load to improve the import efficiency.
• Currently, OMS is not adapted to the MINIMAL mode. If you use OMS for incremental data synchronization, you are not allowed to set binlog_row_image to MINIMAL. The default value is FULL.