Introducing OceanBase 4.2.5: the New LTS Version for TP Scenarios
In 2024, OceanBase released the first long-term support version of the integrated database, OceanBase 4.2.1 LTS. Over the past year, hundreds of customers have rigorously tested and successfully deployed this version in real-world production environments, validating OceanBase's capabilities in TP scenarios. As a result, an increasing number of customers have begun introducing it into their mission-critical systems, expanding the range of business scenarios where OceanBase is utilized.
As OceanBase continues to be adopted in mission-critical systems across various industries and scenarios, we have identified new requirements and challenges in product functionality, performance, compatibility, and usability. After a year of refinement, we officially launched the OceanBase 4.2.5 LTS version. This version, designed as a new long-term support release for transaction processing (TP) scenarios, is tailored to address the demands and goals of mission-critical systems, delivering significant enhancements and breakthroughs in kernel capabilities, compatibility, stability, usability, availability, and performance.
To address the heightened requirements for compatibility, stability, security, and reliability in mission-critical systems, OceanBase 4.2.5 LTS offers full support for MySQL 5.7 and extended compatibility. The SQL engine has been upgraded, with a restructured Query Range implementation and an enhanced row estimation system. This addresses nonoptimal plan issues in several extreme scenarios and boosts the stability and performance of the execution engine. In terms of security, the new version strengthens the security features of the MySQL tenant model, introducing new permission control mechanisms for PL, Outline, and Sequence, as well as database auditing and support for multiple permissions. In terms of high availability, the new version expands the range of storage media supported for backup and restore, supports the migration of active transactions during tablet transfer, and introduces an algorithm for detecting faults on log disks based on the I/O load, raising high availability capabilities to a new level.
OceanBase 4.2.5 LTS has also reached new milestones in diagnostic capabilities and usability. In terms of usability, the new version supports adaptive major compaction of buffer tables, provides format outlines for fuzzy plan binding and throttling, offers manual O&M on log stream replicas for multiple scenarios, and introduces a series of dynamic views and system packages to monitor system operation, significantly improving management efficiency and reducing operational costs. In terms of diagnostic capabilities, the new version enriches diagnostic capabilities across multiple dimensions, further enhancing system observability.
Furthermore, OceanBase 4.2.5 LTS has strengthened its support for multi-model integration, enabling users to manage a broader range of data models with a single database and simplifying the system technology stack. The new version introduces support for the OBKV-Redis model, compatible with the Redis protocol, and further improves OBKV performance through various optimization strategies. Additionally, it brings more diagnostic capabilities and improves the usability of OBKV diagnostics.
Performance tests have shown that OceanBase 4.2.5 LTS delivers substantial performance enhancements in TP scenarios for small-scale clusters of 4C and 8C compared to 4.2.1, and also sees a notable performance increase of 6% to 28% in TP scenarios for large-scale 96C clusters.
As a new long-term support version for TP scenarios, OceanBase 4.2.5 LTS is dedicated to creating a more comprehensive, stable, reliable, and user-friendly distributed database to meet the various demands and challenges posed by mission-critical systems. It is committed to providing long-term stable support for users' key business needs in a wider range of TP scenarios. Moving forward, we will explore the main features and highlights of the OceanBase 4.2.5 LTS version:
● Optimizer Enhancements
● Execution Engine Optimization
● PL Enhancements
● Compatibility Improvements
● OBKV Enhancements
● OBCDC Enhancements
● Diagnostic Capability Enhancements
● Security Feature Enhancements
● Stability Improvements
● Usability Improvements
● High Availability Enhancements
● Comprehensive Performance Improvements
Kernel enhancements
Optimizer Enhancements
OceanBase 4.2.5 rewrites the parsing implementation for extreme scenarios, thereby reducing resource usage during the parsing phase, improving the stability of executing unusually complex SQL queries, and accelerating the SQL parsing performance. It also improves the optimizer's query rewriting logic in several ways, such as improved expression normalization, conditional aggregate function rewrite, multiple MIN / MAX rewrites, and SEMI JOIN split optimization. The new version implements a new query range extraction logic, broadening the scenarios for predicate pushdown, improving the performance of range extraction, reducing the total number of final query ranges extracted and curbing memory consumption. Additionally, the new version optimizes the statistics collection strategy, supporting automatic statistics collection when statistics expire. It also adds support for copying statistics information based on the DBMS_STATS package, and the efficiency of statistics collection has been significantly improved. It allows users to use the https://en.oceanbase.com/docs/common-oceanbase-database-10000000001170630 package to collect or set system statistics coefficients so that the cost model can automatically adapt to the hardware environment.
Execution Engine Optimization
OceanBase 4.2.5 implements a series of optimizations at the SQL execution layer, such as enhancements to recursive CTE searches, the integration of window functions with automatic memory management, improvements to adaptive hash group by for data skew optimization, the refinement of hash-based distinct aggregate strategy, and optimizations for the adaptive hash group by strategy. These enhancements help to boost the performance of SQL execution and increase system stability. It also markedly enhances PDML performance in high-concurrency environments through parallel submission and parallel replay of redo logs at the transaction layer. The new version also resolves performance issues of auto-increment columns and sequences in ORDER mode.
PL Enhancements
OceanBase 4.2.5 implements a series of logic optimizations in terms of temporary table matching, dependency information collection for static SQL statements, and DDL operations on tables, to avoid recompilation when cached objects in the PL cache become invalid. In addition, the compiled PL functions and procedures are now persistently stored in system tables. As long as no DDL operations invalidate the cache, these functions and procedures require compilation only once and can be reused after a restart or in a distributed environment.
MySQL Compatibility Improvements
Building on previous versions, the new version continues to enhance product compatibility and introduces several new MySQL features, including lock functions, event scheduler, eXtended Architecture (XA) transactions, column-level permissions, roles management, illegal logs, and local import from the client. Full compatibility with MySQL 5.7 has been achieved, facilitating seamless integration with a broader range of MySQL third-party ecosystem tools and easing the migration of cloud RDS applications to OceanBase. It also expands support for various character sets, refines communication protocol commands, significantly enhances the storage and analysis capabilities for spatial data (GIS, etc.), supports JSON partial update functionality, and optimizes performance in partial update scenarios. Additionally, the new version ensures compatibility with a multitude of syntax details, system variables, and system views.
OBKV Enhancements
The new version introduces support for the OBKV-Redis model. OBKV-Redis is a persistent database compatible with Redis APIs. It integrates caching and database functionalities, storing hot data in memory and cold data on disk. It also provides transactional capabilities to ensure data consistency, meeting the RT and throughput requirements for 80% of user scenarios. OBKV-Redis is primarily compatible with native Redis 3.2, 4.0, and 5.0, supporting five basic data types: string, hash, list, set, and zset, as well as some common commands such as TTL, EXPIRE, and DEL.
Batch data reads, group commit and global indexes are supported to improve the batch import performance of OBKV. Client information views have been added to display client information and record client parameters, thus enabling users to query the views for the configurations of the business client and improving troubleshooting efficiency. The new version also introduces operation-based classification diagnostics, enhancing the usability of OBKV diagnostics. The compatibility and performance of OBKV-HBase have both been significantly improved.
OBCDC Enhancements
The new version now supports the configuration of allowlists and blocklists using simple regular expressions to accommodate scenarios where users require data synchronization for only a subset of tables. The storage structure of transaction logs in CDC is optimized and the log pullback framework is restructured. This optimization improves the efficiency of OBServer nodes providing logs to CDC and allows for faster retrieval of missing transaction logs when CDC is started during a transaction, thereby increasing CDC startup efficiency during a transaction. OceanBase 4.2.5 also supports data synchronization from the standby tenant in CDC, thus reducing resource consumption of the primary tenant.
Diagnostic Capability Enhancements
In OceanBase 4.2.5, the wait event framework has been comprehensively enhanced, and the statistics monitoring framework has been restructured to improve system observability. New features include XA transaction monitoring statistics, response time histograms, log transmission link views, and resource group-level statistical views. A new performance diagnosis tool, SQL STAT, has been introduced to record the execution status and real-time statistical information of SQL statements. The time statistics model has been improved with additional dimensions, including DB Time and DB CPU, to provide more comprehensive analysis. The SQL PLAN MONITOR has been enhanced with new monitoring content to boost real-time diagnostic capabilities. The Active Session History (ASH) diagnostic module now provides row lock wait and retry wait events, covering a broader range of scenarios, and introduces node-level analysis capabilities. SQL AUDIT has also been expanded with additional wait time statistics items. The new version provides the./alert/alert.log file that records concerned logs of database administrators (DBAs) to improve the ease of use. Furthermore, the logs for PL and SQL have been decoupled, assigning independent TraceIDs to internal SQL statements within PL and adding outer PL TraceID in SQL AUDIT, significantly improving the efficiency of problem diagnosis.
Security Feature Enhancements
The new version has enhanced the security features of the MySQL tenant model by introducing permission control mechanisms and auditing capabilities for PL, Outline, and Sequence. Additionally, the new version is compatible with the REFERENCES, CREATE ROLE, DROP ROLE, and TRIGGER privileges of MySQL.
Stability Improvements
OceanBase 4.2.5 introduces a table-level cache size setting for auto-increment columns, enabling users to customize cache sizes for different tables based on column type, business model, and business traffic, achieving a balance between value hoopings and performance. In collaboration with OceanBase Database Proxy (ODP), globally unique client session ID has been added, resolving the longstanding issue of inaccurate kill session due to non-unique session IDs. The new version supports system log compression, which helps reduce the sizes of log files, save disk space, and facilitate transmission and storage. Additionally, data compression at the communication protocol layer is supported for OBServer nodes and OceanBase Database Proxy (ODP) to reduce the bandwidth costs in data transmission.
For resource isolation, new capabilities for direct load resource management have been added at the task, tenant, and OBServer levels. These enhancements effectively control resource usage in high-concurrency, multi-task parallel scenarios, preventing interference with other tasks. Background resource isolation is supported for CPU and I/O resources. Furthermore, it enhances resource isolation by supporting isolation for statistics collection, clog commit, and DDL operations.
In terms of memory usage, the new version improves the memory throttling mechanism, providing more granular control over memory usage across multiple modules. It also introduces a time-based trigger for freezing and compacting transaction data tables. Additionally, the new version adds capabilities to specify the maximum percentage of memory in the tenant available for a single SQL query.
The new version has optimized read-only replicas by adding Region perception capabilities. During log synchronization, read-only replicas will prioritize other replicas within the same Region as the upstream source, minimizing cross-Region network traffic and bandwidth usage. Furthermore, OceanBase 4.2.5 implements access isolation for read-only replicas. This feature defines the routing rule for internal data requests. It is prohibited to forward requests destined for a read-only replica to a non-read-only replica, thereby implementing resource isolation between TP and analytical processing (AP) services and ensuring long-term stable operation in hybrid transaction/analytical processing (HTAP) scenarios.
Usability Improvements
In the new version, events pertaining to primary/standby tenants are segregated and recorded under each respective tenant, and are accessible through the CDB/DBA_OB_TENANT_EVENT_HISTORY view. Additionally, in Oracle tenant mode, network-based standby database roles copy has been supported to simplify the configuration process of network-based standby databases. Physical recovery progress statistics have been added to obtain the progress of recovery tasks. New support has been added for changing replication table attributes, allowing users to easily convert table attributes through the ALTER TABLE command, reducing user operation costs. Furthermore, the new version allows users to bind execution plans and perform throttling simultaneously in a single CREATE OUTLINE statement, meeting the needs of users who need to manage execution plans and limit high-traffic SQL. The Format Outline feature has also been added, providing a more relaxed outline matching rule.
The new version continues to optimize the adaptive major compaction feature of buffer tables, providing five levels of major compaction strategies. Users can set different table_modes for each table according to business scenarios to address read amplification caused by buffer tables.
New support has been added for the transfer-based partition, allowing users to migrate specific partitions to specific log streams and view task status. The new version has been redesigned to implement operations at the log stream replica level, providing a series of syntaxes to support adding, deleting, changing the type of, and migrating log stream replicas. It also allows for modifying the number of Paxos replicas and canceling disaster recovery tasks, addressing the need for manual O&M on log stream replicas. The DBMS_BALANCE system package has also been added to provide a method of manual partition balancing.
To allow users to more conveniently obtain information on the physical resources corresponding to the current logical resources of the cluster, resource specification estimation functions have been added, along with a series of dynamic views and system packages. A new index usage monitoring function has been added for identifying useless indexes, which summarizes the number of references to index tables created by user tenants at the table level. This facilitates users in tuning SQL based on index usage or deleting useless indexes to free up disk and memory resources. Additional enhancement includes real-time application of unit configuration changes, reducing the impact of memory specification modification operations.
The OceanBase LogMiner (referred to as ObLogMiner) tool has been added. Oblogminer is a command-line tool for analyzing online and archived logs of OceanBase. To reduce the storage space demand of system logs, the new version supports syslog compression. With zstd compression enabled, it is estimated that 20 times the amount of logs can be stored compared to when compression is not enabled, without changing the storage space capacity.
High Availability Enhancements
OceanBase 4.2.5 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. 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. The arbitration service provides an algorithm for detecting faults on log disks based on the I/O load, which can effectively detect cloud disk faults and perform arbitration downgrade to prevent cloud disk faults from continuously affecting business requests.
OceanBase 4.2.5 provides the role switchover validation feature (SWITCHOVER/FAILOVER VERIFY). You can add the VERIFY keyword to the SWITCHOVER or FAILOVER command to validate whether the corresponding operation can be executed successfully. The new version also implements automatic routing between primary and standby databases, ensuring high service availability and seamless switching to the standby database in case of primary database failure. The types of storage media supported for backup and restore have been enriched. In addition to file storage (NFS) and object storage (OSS/COS), new support has been added for AWS S3, Huawei Cloud OBS, and GCP GCS storage services. The new version introduces SET/PIECE level physical restore functionality, offering the ADD RESTORE SOURCE command to load data backup sets (SET) or log archives (PIECE) from new paths, allowing on-demand restoration to a specified point in time. OceanBase 4.2.5 also provides the forcible archiving job stopping feature. An archiving job can advance to the STOP state without the need to forcibly write archiving stopping metadata, ensuring that archiving tasks can be closed in abnormal situations.
Comprehensive Performance Improvements
OceanBase 4.2.5 has undergone specialized performance optimizations for small-scale clusters with 4C and 8C configurations. Compared to version 4.2.1, the performance in TP scenarios has been significantly enhanced.
Sysbench Performance Test for 4C Specifications (OceanBase 4.2.1 v.s OceanBase 4.2.5)
Additionally, in large-scale 96C clusters, the performance in TP scenarios has been improved by 6% to 28%.
Sysbench Performance Test for 96C Specifications (OceanBase 4.2.1 v.s OceanBase 4.2.5)
As the new LTS version for TP scenarios, OceanBase 4.2.5 is dedicated to providing stable, long-term support for users' needs in mission-critical scenarios. This release marks another significant step forward for OceanBase in assisting users with the upgrade of their mission-critical systems. In future versions, we will continue to explore more mission-critical scenarios, further improve OceanBase's TP capabilities and performance, and provide enhanced integrated product capabilities.
