OceanBase Database Enterprise Edition V4.3.2

V4.3.2 Beta

Version information

• Release date: July 16, 2024
• Version: V4.3.2 Beta
• RPM number: oceanbase-4.3.2.0-100000442024071321

Overview

OceanBase Database V4.3.2 is a performance-enhanced version designed for analytical processing (AP) workloads. This release includes comprehensive improvements in data reading, data distribution, data computation, vectorized processing, and plan selection, bringing an overall performance boost of 10%-15% in AP benchmark tests. In addition, this version introduces the roaring bitmap data type and associated computation expressions, offering a better choice for large data set operations and deduplication scenarios. It also enhances the incremental direct load functionality, improves full direct load performance, and extends support for Parquet external tables, accelerating the processing speed of large-scale data. As an integrated database, the new version has also provides scenario-specific parameter templates for express oltp, complex oltp, olap, htap, and kv scenarios, ensuring the initial configuration is suitable for each business type with the help of deployment tools. Additionally, the new version incorporates various compatibility enhancements and user experience improvements from the V4.2.x series, along with performance improvements for transactional processing (TP) SQL, aiming to meet the demands of various application scenarios.

Key features

Kernel enhancement

• Roaring bitmap

  As the era of big data evolves, enterprises are increasingly looking to mine and analyze user data. Roaring bitmap, known for its space-saving and computational efficiency, plays a significant role in scenarios such as user profiling, personalized recommendations, and precision marketing. Starting from OceanBase Database V4.3.2, the MySQL mode supports the roaring bitmap data type, enhancing the performance of large-scale data set computations and deduplication by storing and operating on a set of unsigned integers. To cater to multidimensional analysis needs, this version includes support for over 20 expressions related to cardinality calculation, set operations, bitmap evaluations, bitmap construction, bitmap output, and aggregate operations.

• Table-level overwrite

  In data warehousing scenarios, tasks such as periodic data refresh, transformation, and cleansing often need data to be overwritten. OceanBase Database V4.3.2 introduces a table-level overwrite feature (INSERT OVERWRITE), which atomically clears old data and writes new data into the table. By utilizing full direct load capabilities, INSERT OVERWRITE offers high execution performance. V4.3.2 supports the command INSERT OVERWRITE tablename SELECT * FROM tablename but does not yet support partition-level overwrites. This functionality will be available in future versions.

• Enhanced incremental direct load

  Starting from V4.3.1, incremental direct load is supported as an experimental feature, with some limitations such as not supporting the import of outrow LOB data and only supporting incremental direct load with inc_replace load_mode (equivalent to replace semantics). In V4.3.2, the capability to incrementally import outrow LOB data through direct load has been added. Additionally, the load_mode has been expanded to include the value inc, which defaults to insert semantics. When the ignore keyword is specified in SQL, it behaves with ignore semantics. This feature is now released as an official one.

• Enhanced external table functionality

  OceanBase Database has supported external tables for CSV files for some time. However, as AP businesses gradually expand, there has been a widespread demand for reading external data sources in Parquet format in data lake scenarios. Starting from V4.3.2, OceanBase Database now supports Parquet files in external tables. This allows users to import data from these files into OceanBase internal tables, or directly use external tables for federated query analysis across data sources. Additionally, to ensure the files scanned by external tables are up-to-date, the new version introduces an automatic refresh feature for file directories. When creating an external table, users can specify the file list refresh method (manual, real-time, or periodic) through the AUTO_REFRESH option, and manage scheduled refresh tasks using the DBMS_EXTERNAL_TABLE.REFRESH_ALL_TABLE (interval int) system package subprogram.

• Vectorized engine enhancements

  V4.3.0 has supported the new vectorization engine, and V4.3.2 further expanded support for operators and expressions, including Hash Set-related operators, WindowFunction operators, MergeDistinct operators, as well as the repeat, ceil, and floor expressions. This further enhances the computing performance in AP scenarios.

• Scenario-specific parameter templates

  OceanBase Database, as an integrated database, supports various business types, such as express OLTP, complex OLTP, OLAP, HTAP, and KV. A single set of default system parameters cannot effectively cater to all scenarios. For example, the default value of ob_query_timeout is 10 seconds, which is not suitable for OLAP businesses. Therefore, starting from V4.3.2, OceanBase Database has identified the optimal configurations for several key parameters based on different business scenarios. In the future, tools like OCP and obd will also be used to initialize these parameters for specific business scenarios.

• Enhanced query parsing capabilities

  When an SQL statement contains a large number of INLIST, AND/OR, and UNION operations, it can generate exceedingly deep syntax trees during parsing. This often leads to excessive memory consumption, stack overflow issues, and prolonged processing times. The new version addresses these challenges by rewriting the parsing implementation for such extreme scenarios, thereby reducing resource usage during the parsing phase. This enhancement not only improves the stability of executing unusually complex SQL queries but also accelerates the SQL parsing performance.

• Enhanced query rewrite capabilities

  OceanBase Database V4.3.2 improved the optimizer's query rewriting logic in several ways, such as:

  • Improved expression normalization: In previous versions, various expressions would be normalized in the resolver parsing phase, such as rewriting A and (A or B) into A. However, it did not cover the non-standard expressions generated in the SQL rewriting phase. The new version introduces an expression normalization process in the rewriting phase to ensure that expressions are rewritten to the simplest form.

  • Conditional aggregate function rewrite: In queries with expressions like select c1, sum(case when c2 = 0 then c3 else 0 end) ,sum(case when...)...,... from t1 group by c1, where the aggregation target is dynamically determined based on runtime branch conditions, we refer to them as conditional aggregate functions. The new version supports rewriting these conditional aggregate functions by pushing the aggregate function into the selection target of each branch, reducing the computations for case when expressions and aggregate functions. This enhancement significantly improves execution performance in relevant scenarios.

  • Multiple MIN/MAX rewrites: In a scalar group by query, if the query contains only MIN/MAX aggregate functions and the columns have indexes, the query can be rewritten as ORDER BY xxx LIMIT 1. This uses the index order to bypass ORDER BY and pushes LIMIT 1 to the table scan, reducing data reads and sorting. In previous versions, queries with multiple MIN/MAX functions involved full table scans. The new version, however, rewrites these into separate subqueries, each directly fetching the maximum or minimum value from the index. This avoids full table scans and sorting, effectively optimizing query performance.

  • Rewriting the UNION constant to Values Table: In practical business scenarios, multiple UNION ALL combinations are used to represent a constant table. This might lead to high CPU and memory consumption if there are a large number of subqueries. The new version rewrites the constant type UNION ALL/UNION into Values Table queries, significantly reducing resource consumption during the hard parsing phase.

  • SEMI JOIN split optimization: In EXISTS or IN subqueries with multiple table joins and no join conditions, the execution plan may involve costly Cartesian products. The new version supports SEMI JOIN splitting for tables without join conditions, avoiding the overhead of Cartesian products.

• Plan selection optimization

  The previous versions of OceanBase Database implemented a new query range extraction logic, broadening the scenarios for predicate pushdown. This enhancement enabled more precise range extraction for vectorized predicates and addressed issues of memory amplification associated with the older Query Range mechanism. Building upon this foundation, V4.3.2 introduces modifications to the method for generating query ranges from NOT IN expressions, thereby improving the performance of range extraction. It also facilitates range graph pruning, which reduces the total number of final query ranges extracted and curbs memory consumption. The update extends support for index hints, allowing for specific instructions like /*+index(t1 k1 1)*/ to limit query range extraction solely to the first column of the k1 index. Furthermore, the new version embraces interesting ordering index path pruning strategies and incorporates optimizations such as recalculating LIMIT, tackling performance issues in ORDER BY LIMIT scenarios that previously arose from suboptimal plan selection.

• Compatibility version control for product behaviors

  To reduce potential issues caused by behavior changes in new versions, OceanBase Database V4.3.2 introduces a compatibility version control feature for product behaviors. This feature uses the system variables ob_compatibility_version and ob_security_version to control whether general behavior changes (from scenarios with no errors to error scenarios) and security-related changes (from privilege granted to privilege denied) take effect. These variables determine which OceanBase version's behavior or security features a tenant will be compatible with, using values such as "4.3.2.0" that correspond to release versions. For example, if a feature's behavior changes in V4.3.3, setting the variable to 4.3.2.0 retains the previous behavior, while setting it to 4.3.3.0 adopts the new behavior. Typically, the version number is not automatically increased during an upgrade. To utilize new version behaviors, users need to update the version number after understanding and testing the new behaviors. This feature is designed to manage future behavior changes and cannot handle changes in previously released versions.

  In V4.3.2, product behaviors controlled by ob_compatibility_version includes:

  • When ob_compatibility_control = MYSQL5.7, the behavior of REPLACE('abd', '', null) is changed from being compatible with MySQL 8.0 to being compatible with MySQL 5.7.
  • The offset in the limit clause of UPDATE/DELETE statements is disabled.
  • If a projection item is a single null, the returned header will be changed to NULL.
  • The user variable is limited to a maximum length of 64 characters.

  Product behaviors controlled by ob_security_version include:

  • Privilege control for Outline and Sequence.
  • CREATE TABLESPACE privileges.

Multi-model features

• GIS enhancements in Oracle mode

  In OceanBase Database V4.3.2, the Oracle mode introduces following GIS-related functionalities:

  • Spatial index: Supports creating spatial indexes using CREATE INDEX. The SRID for spatial index columns must be specified when creating the table.

  • Functions: Added the SDO_RELATE expression to determine the spatial relationship between two geometric objects. Added the SDO_GEOM.SDO_DISTANCE member function to calculate the minimum Earth surface distance between two geometric objects.

• Optimization of GIS spatial relationship calculation performance

  The new version optimizes the performance of spatial relationship expressions such as ST_INTERSECTS ST_INTERSECTS, ST_CONTAINS, _ST_COVERS, and ST_WITHIN.

  • In full-query window scenarios, the performance of ST_INTERSECTS for points is on par with PostgreSQL and slightly outperforms it. In other scenarios, the average response time is less than half of PostgreSQL's. Notably, the intersect operations for linestring take only 1/5 of PostgreSQL's time, showing a significant improvement over previous versions of OceanBase Database.
  • In large query window scenarios, the average response time for all scenarios significantly surpasses PostgreSQL. Specifically, the intersect operations for linestring take only 1/7 of PostgreSQL's time, showing a substantial improvement over previous versions of OceanBase Database.
  • In small query window scenarios, the contain calculation outperforms PostgreSQL, with the contain operations for point taking about 1/4 of the time of PostgreSQL, and for linestring contain about 4/5 of PostgreSQL's time. However, intersect operations perform on par with PostgreSQL for linestring scenarios, and take roughly twice the time of PostgreSQL for point scenarios.

• Optimization of read/write performance for LOBs stored in OUTROW

  OceanBase Database V4.3.2 optimizes the read/write performance for LOBs stored using the OUTROW method.

  • In the table scan scenario, the new version improves small LOB performance by nearly 10 times and large LOB performance by at least 2 times compared with previous versions. However, it remains slower than INROW in these scenarios.
  • In the point select scenario, for small LOBs (less than 8K), OUTROW QPS is 20% lower than INROW. For larger LOBs (above 32K), OUTROW QPS is about 5% lower than INROW. When LOBs are not involved in computations, OUTROW storage offers better query performance.
  • In the point update scenario, OUTROW performance has improved on average by 2 times compared with previous versions.

Compatibility with MySQL

• Distinguishing between MySQL 5.7/8.0 compatibility versions

  There are behavioral conflicts between MySQL 5.7 and 8.0 in some scenarios. For example, the output of replace('a', '', null) differs between these two versions. OceanBase Database V4.3.2 introduces a tenant-level initialization variable ob_compatibility_control to specify whether to be compatible with MySQL 5.7 or 8.0 when such conflicts arise. This setting cannot be changed after the tenant is created. Both the MySQL and Oracle modes can use the superset of features from MySQL 5.7/8.0 where no behavioral conflicts exist.

• Union Distinct RCTE

  MySQL 8.0 introduced support for recursive Common Table Expressions (CTEs) with both Recursive UNION ALL and Recursive UNION DISTINCT functionalities. Starting from V3.2.3, OceanBase Database has supported Recursive UNION but was initially limited to Recursive UNION ALL. V4.3.2 extends compatibility to include MySQL's Recursive UNION DISTINCT feature, ensuring the uniqueness of output data. Additionally, this version enhances the Recursive UNION ALL functionality by enabling data writing to disk when memory is insufficient.

• Optimization for auto-increment column leader switchover

  Starting from V4.x, OceanBase Database supports the creation of auto-increment columns in ORDER mode to better align with MySQL’s behavior. However, in versions prior to V4.3.2, a leader switchover could still cause value hopping in auto-increment columns. Considering that many leader switchover scenarios are initiated by users as part of regular processes rather than being exceptional situations, the new version has been optimized to maintain the continuity of auto-increment columns during intentional leader switchover, thereby reducing the probability of value hopping in auto-increment columns.

• Decreasing the auto-increment value

  Prior to V4.3.2, OceanBase Database only supported increasing the AUTO_INCREMENT value through ALTER TABLE, not decreasing it, which was not fully compatible with MySQL's behavior. The new version now allows decreasing the AUTO_INCREMENT value. If the new value is greater than the current maximum value in the column, the setting is successful and takes effect. If the new value is less than or equal to the current maximum value, it can still be set, but AUTO_INCREMENT will automatically adjust to the next value after the current maximum value.

• SERIAL data type

  Added the SERIAL data type as an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE. It represents an auto-incrementing large integer suitable for use as a primary key in a table.

• Additional support for MySQL communication protocol commands

  Compatible with MySQL 5.7 communication protocol commands COM_PROCESS_INFO and COM_PROCESS_KILL.

Compatibility with Oracle

• DBMS_SCHEDULER enhancements

  In V4.3.2, four new features have been added to the existing DBMS_SCHEDULER:

  • JOB CLASS feature: Provides CREATE_JOB_CLASS and DROP_JOB_CLASS subprograms to create and delete JOB CLASS. It also supports modifying JOB CLASS attributes and binding JOB and JOB CLASS using subprograms like SET_ATTRIBUTE and CREATE JOB.
  • PURGE LOG feature: Offers the PURGE_LOG subprogram to clear JOB logs for a specified time period.
  • STOP JOB feature: Provides the STOP_JOB subprogram to terminate a running JOB, allowing for management during exceptions.
  • Optimized JOB routing policy: Supports setting the JOB routing policy using the SET_ATTRIBUTE subprogram. If instance_id is not specified, JOB is executed on the primary zone by default. If instance_id is set to a specific zone, JOB is executed there. If set to RANDOM, JOB is routed randomly. Configuring JOB to route to the leader node reduces RPC overhead and improves execution performance.

• UTL_RECOMP package

  Oracle's UTL_RECOMP system package, a utility for recompiling database objects, is now used in OceanBase Database's Oracle mode to check object validity after database upgrades. It supports recompilation of entities such as PACKAGE, PROCEDURE, FUNCTION, TYPE, and TRIGGER, facilitating post-upgrade checks on object validity. Due to potential high I/O operations during execution, it is only available for SYS users.

• DBMS_PROFILER package

  OceanBase's PL compatibility with Oracle has been improved, supporting the PL Debug feature for program troubleshooting, but lacked effective means for performance issue analysis. Following Oracle's method for performance analysis, OceanBase Database V4.3.2 supports the DBMS_PROFILER system package. This enhances performance profiling mechanisms, records finer execution times, and accurately identifies the performance of each PL line to effectively diagnose runtime performance issues.

• DBLink synonyms

  Earlier versions already supported the creation of synonyms for DBLink objects; however, they did not allow the use of these synonyms in PL variable declarations. V4.3.2, now facilitates the behavior of defining variable types using synonyms for DBLink objects within PL, enhancing the compatibility with Oracle. However, this feature is not applicable to PL parameter lists.

• Proxy user

  In business operations, it is common to temporarily share production account credentials with third-party vendors for maintenance or upgrades. Traditionally, after such tasks, a series of security measures, like revoking access or resetting passwords, is required. However, starting with V4.3.2, compatibility with Oracle's Proxy User functionality has been introduced. This feature allows an authorized user, Y, to act as a proxy for production account X without knowing X's password. Once the task is completed, simply removing user Y or revoking proxy privileges is sufficient. This streamlines account maintenance and enhances overall security.

• SELECT ... FOR UPDATE supports hierarchical queries

  Previous versions reported an error when using START WITH ... CONNECT BY in SELECT ... FOR UPDATE statements. The new version supports this scenario.

Performance enhancements

• Performance improvements in typical AP scenarios

  V4.3.2 introduces comprehensive optimizations for data block prefetching, vectorized batch processing, filter pushdown, comparison calculation, aggregation calculation, DTL shuffle, and monotonicity filters. Under a benchmark testing scenario with 100 GB of data, this has led to an approximate 10% to 15% improvement in performance across various AP scenarios, including TPCH, TPCDS, and ClickBench, compared with V4.3.1.

• Performance improvements in full direct load scenarios

  In full direct load scenarios, the new version improves performance by reducing column type conversion operations during data loading, lowering CPU usage for statistics collection, removing the primary key generation logic on the SELECT side of INSERT INTO SELECT, and disabling sum skip index by default for column storage (which aggregates SUM values for specified columns within a storage layer range). Additionally, by disabling micro-block verification (setting the micro_block_merge_verify_level parameter to 0), the direct loading performance improves by approximately 20%.

• Improved rewriting of IN/EXISTS subqueries

  Prior to V4.3.2, OceanBase Database did not support rewriting IN/EXISTS subqueries within SELECT clauses into joins. For example, in a query like select case when c1 in (select c1 from t2) then 1 else 0 end from t1;, the optimizer would not rewrite it into a join, leading to repeated scans of the subquery and suboptimal performance. The new version now supports rewriting IN/EXISTS subqueries in SELECT clauses into joins in some scenarios, aiming to enhance SQL execution performance through parallelism.

• Caching and persistence of storage procedures' DDL compilation results

  In V4.3.1, a feature was added to persist the compilation results of stored procedures during execution, addressing performance issues caused by recompilation when fetching from the PL Cache fails. However, if the stored procedure itself undergoes DDL changes, recompilation was still required for subsequent executions, leaving room for further performance optimization in this scenario. V4.3.2 enhances this by caching and persisting compilation results in the PL cache after successful DDL execution. This increases the likelihood of directly hitting the PL cache during future executions, further improving stored procedure performance.

• Performance optimization for adding NOT NULL columns to empty tables in Oracle mode

  In historical V4.x versions, performing an add column not null operation on an empty table in Oracle mode took longer than the add column null operation. Additionally, the time difference between the two operations increased with the number of partitions. The new version changes the process of adding a not null column to an empty table to an online process, significantly boosting DDL performance in this scenario.

• Performance optimization for SHOW TABLE STATUS

  In previous versions, the SHOW TABLE STATUS FROM ... LIKE ... query had suboptimal performance as it did not utilize table_name related indexes. The new version significantly enhances query performance in scenarios with single table filtering conditions.

• Performance optimization for auto-increment columns in Order mode

  In the Order mode of auto-increment columns, it is necessary to maintain the continuity of data insertion. To ensure the order of auto-increment columns in a distributed architecture, the value is persisted to an internal table during the INSERT operation. In high-concurrency scenarios, this process can result in relatively long response times. V4.3.2 optimizes this scenario by pre-fetching with a cache size, effectively reducing the number of accesses to the internal table and significantly improving performance.

• Performance optimization for sequences in Order mode

  OceanBase Database now supports creating globally continuous sequences with specified order + cache attribute. However, earlier implementations ignored the cache attribute, treating it as order + nocache and thus leading to performance issues under high concurrency. V4.3.2 optimizes this by using a central node to support order + cache, effectively implementing the cache attribute and significantly enhancing performance in high-concurrency scenarios.

Reliability improvements

• Standby tenant cloning

  OceanBase Database has supported the tenant cloning feature since V4.3.0. When users need to perform temporary data analysis or other high-risk operations that consume significant resources on an online tenant, they can use a cloned tenant to complete the analysis or verification to avoid impacting the online tenant. Cloned tenants can also serve as a disaster recovery measure. If an irrecoverable error occurs on the original tenant, the cloned tenant can be used for data rollback. However, before V4.3.2, only the primary tenant could be cloned, and cloning the primary tenant would block log replay for standby tenants. In scenarios where the synchronization and replay priority of the standby tenant is higher than that of the cloning process, primary tenant cloning could not meet stability requirements. The new version addresses this by introducing the capability to clone standby tenants, enabling users to perform business verification or disaster recovery tasks through cloned standby tenants without obstructing log replay for these tenants, thereby reducing impacts on system stability.

• Optimizations on transfer transactions

  Since V4.3.0, the transfer functionality has supported the migration of active transactions. For example, requests from non-migrated tablets can continue on the Transfer source's log stream, and new transactions can be initiated. Transactions that are already in progress are briefly paused during the Transfer start phase, after which the transactions are committed. Building on this, V4.3.2 further optimizes the impact of transfer transactions on transaction execution. If a transaction on the Transfer source's log stream doesn't involve a migrated tablet, the transaction will continue without being blocked from submission and will not be migrated. This ensures that the front-end business remains unaffected and that the load balancing is more transparent.

• Performance optimization for network standby databases

  The new version optimizes the RPC and network framework to reduce the log transmission time between primary and standby databases. It also refines the controlled log fetching process for standby databases, decreasing the frequency of controlled log fetching for backup tenants and improving log synchronization performance between primary and standby databases.

• Backup performance optimization

  During backups, data integrity is maintained by verifying that the baseline version is greater than the minor compaction version through continuity checks. However, these checks require reading from backup media and executing locking operations, which affect performance. The new version reduces the performance overhead of continuity checks and allows control over the number of threads used via ha_low_thread_score, effectively enhancing backup performance.

• Optimization of source selection for migration replication

  In previous versions, when selecting the source for migration replication, there was no priority given to replicas in the same zone, the same IDC (same data center), or the same region (same city), which could potentially result in suboptimal performance for long-distance migration replication. Additionally, the source may select the leader node, which could lead to slow business requests and migration tasks, particularly when the business request volume is high. V4.3.2 divides each server into three types of regional relationships based on geographical information: in the same IDC, in the same region but different IDC, and across regions. It provides an enumeration parameter called choose_migration_source_policy, allowing users to configure a specific priority mode for selecting the source for migration replication. This allows for prioritizing geographical proximity and follower replicas to enhance migration efficiency and alleviate pressure on Leader nodes.

• Physical restore at the SET/Piece level

  In actual business scenarios, secondary backups often involve manually moving data backup sets or archived logs to new locations. OceanBase Database's physical restore feature was limited to restoring only from the tenant's archive and backup paths, making it impossible to use backup data moved manually, thus limiting recovery flexibility. 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.

Resource optimization

• Partition-Level index space optimization

  During the process of creating indexes, internal SQL statements are used to load index table data. If a node has a large volume of data, temporary space amplification might occur during sorting. V4.2.2 introduced progressive execution for supplementing index table data: executing 1 GB of data per batch on a single node, while parallel tasks run across multiple nodes. This approach leverages distributed systems' parallel computing capabilities and addresses the issue of temporary space amplification during index construction.

• CPU isolation for global frontend and background tasks

  In high-performance computing environments, proper resource allocation and isolation are crucial for ensuring system stability and efficiency. Effective resource isolation prevents competition and interference between tasks, improving resource utilization and overall service quality. OceanBase Database currently allows resource isolation between tenants using unit specifications and within tenants using the DBMS_RESOURCE_MANAGER package, covering key resources like CPU and I/O. For businesses with many tenants that want to prevent significant CPU competition from background tasks affecting frontend requests, global isolation of background tasks is a better option. OceanBase Database V4.3.2 supports global CPU frontend and backend task isolation, allowing resources for background tasks to be limited on a global level, which is more convenient and user-friendly compared with configuring each tenant individually with DBMS_RESOURCE_MANAGER.

Security enhancements

• MySQL CREATE TABLESPACE privilege

  Added the CREATE TABLESPACE privilege, which is applicable only to Transparent Data Encryption (TDE) scenarios.

• Enhanced privilege control over outlines and sequences in MySQL mode (Incompatible feature)

  Previous versions lacked privilege control for creating and maintaining outlines and sequences. The new version reuses MySQL CREATE, ALTER, and DROP privileges to control the creation, modification, and deletion of outlines and sequences.

Usability improvements

• Adaptive major compaction optimization for buffer tables

  When users frequently insert data into a table while performing batch deletions or concurrent updates, they may encounter a noticeable drop in query and update performance, even if the number of data rows is not large. This phenomenon is known as the Queuing Table effect (sometimes called the Buffer Table effect) in OceanBase Database. This issue is common to databases with the LSM-Tree architecture. In an LSM-Tree architecture, delete operations are logically marked rather than physically removed until merged. When there are many marked deletions in incremental data, the physical row count can far exceed the logical row count, causing severe read amplification and affecting optimizer execution plans.

  In V4.1.0, OceanBase Database achieved automatic identification of partitions with a high volume of changes over a period and performed adaptive compaction on these partitions. However, it lacked manual control measures. To flexibly address performance degradation issues caused by Buffer tables, the V4.3.2 further optimizes the adaptive merge feature for buffer tables. It provides five different levels of merge strategies, allowing users to set different table_mode for each table based on business scenarios to address the read amplification caused by Buffer tables, thereby enhancing long-term system performance metrics such as QPS.

• System Log Optimization

  In the system log directory, a new file ./alert/alert.log is added to record logs that are of relevance to database administrators (DBAs). This helps to address the issue of high volume and poor readability of the observer.log file. You can set the log level, such as INFO, WARN, and ERROR, through the cluster-level parameter alert_log_level. Additionally, an external system table sys_external_tbs.__all_external_alert_log_info is provided for direct structured queries of alert.log information.

• OceanBase LogMiner

  OceanBase Database V4.3.2 introduces support for OceanBase LogMiner (referred to as oblogminer). oblogminer is a command-line tool for analyzing logs in OceanBase Database, supporting both online and offline log analysis. oblogminer pulls and parses clogs using obcdc, transforming the logical logs output by obcdc into a readable format and storing them in a specified location. It is suitable for the following scenarios:

  • Data misoperation: Data misoperations can occur due to various reasons, such as mistakenly deleting or updating redundant rows due to errors in range conditions in the WHERE clause. oblogminer helps accurately identify details of such errors, enabling database restoration to its state prior to the misoperation.
  • Data analysis: oblogminer organizes and presents various information (transactions, table structures, etc.) from clogs in a user-friendly manner, enabling users to conduct diverse data analyses based on oblogminer's output. Additionally, it can be used in conjunction with external table functionality to perform query analysis within the database.

• Table-level cache size setting for auto-increment columns

  Currently, you can control the number of cached auto-increment values in memory by specifying the tenant-level parameter auto_increment_cache_size, which applies to all tables using auto-increment columns. Generally, the larger the cache size, the better the performance. However, if there are frequent value hoopings and the upper limit of the auto-increment column values is relatively small, the auto-increment values may quickly run out. V4.3.2 introduces a table-level cache size setting for auto-increment columns. This enables 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.

• Enhanced PX diagnostics capability

  To facilitate troubleshooting of distributed plans, V4.3.2 adds memory and disk usage of operators at runtime, as well as the maximum memory and disk usage of the entire execution process, to the SQL Plan Monitor. In SQL Audit, it records the memtable and sstable row counts corresponding to the QC trace_id and supports summarizing relevant information for each PX worker's trace_id. Additionally, the obdiag tool can pull logs from the initial error-reporting machine based on the trace_id.

Performance report

Based on the parameter templates for different business types, this version conducted the following performance comparison tests. In the AP benchmark test, the OLAP parameter template was used, demonstrating a significant performance improvements over other templates.

Hardware specifications:

• OBServer: 3 servers (32c256g), instance type: ecs.r7.8xlarge. The system disk is used as the log disk, and the clog and data disks are separately mounted on two cloud disks. The disk performance level is PL1.

• ODP: Independently deployed on a single server with 64c128g. Instance type: ecs.c7.16xlarge.

Operating system:

CentOS 7.9 64 bit.

Tenant specifications:

28c180g, 3F, primary_zone=RANDOM.

1. SYSBENCH OLTP load testing

Basic parameters:

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;

• table: 30
• table_size: 100w
• time: 30s

Test result (QPS/95%rt):

• Point Select performance

  Threads	express_oltp parameter template	complex_oltp parameter template	olap parameter template	htap parameter template
  32	163457.60/0.22	162747.70/0.22	161428.80/0.23	162948.95/0.20
  64	296206.41/0.25	291823.36/0.26	291583.48/0.26	293622.63/0.25
  128	505203.80/0.30	493859.95/0.31	492135.78/0.31	498132.19/0.31
  256	798005.94/0.45	794547.97/0.47	803165.10/0.49	797304.31/0.45
  512	1039286.05/0.90	1023822.11/1.14	1022666.33/1.12	1032713.76/0.90
  1024	1013992.61/2.39	1011295.14/2.39	997362.00/2.57	1004848.34/2.48

• Read Only performance

  Threads	express_oltp parameter template	complex_oltp parameter template	olap parameter template	htap parameter template
  32	134791.19/4.10	136145.15/3.97	137486.55/3.96	137327.53/3.95
  64	244754.37/4.49	244093.17/4.57	244641.01/4.57	244586.46/4.57
  128	416929.45/5.37	420143.73/5.47	419772.35/5.28	420445.05/5.28
  256	613453.13/7.56	611436.43/8.28	603989.96/8.28	610998.14/7.43
  512	725364.76/16.12	738362.91/17.65	736059.64/15.83	720899.31/16.12
  1024	715777.22/41.10	707831.35/42.61	697077.19/44.17	706809.11/42.61

• Write Only performance

  Threads	express_oltp parameter template	complex_oltp parameter template	olap parameter template	htap parameter template
  32	50914.06/5.00	52894.62/4.91	50589.47/5.67	52088.46/4.74
  64	90119.99/5.47	93447.67/5.37	90202.65/5.37	90264.56/5.57
  128	164488.33/5.77	166099.69/5.57	159493.96/5.99	159005.24/6.09
  256	242240.38/8.13	241749.01/8.43	232320.85/8.43	230522.31/8.74
  512	304060.67/13.70	306416.65/13.70	299155.86/13.70	289147.63/13.95
  1024	345068.37/23.52	348929.05/26.20	306096.92/29.72	327905.15/27.17

• Read Write performance

  Threads	express_oltp parameter template	complex_oltp parameter template	olap parameter template	htap parameter template
  32	90881.38/7.84	88141.94/8.28	88216.59/8.58	89948.44/7.98
  64	159748.46/8.90	160695.31/9.06	157714.41/10.09	157230.31/9.39
  128	273142.95/10.46	275431.02/10.27	272648.28/10.27	269700.79/11.24
  256	391348.85/15.27	402154.83/15.00	382679.53/16.71	383447.47/15.27
  512	465031.62/28.67	462574.18/33.72	466465.96/26.20	461249.29/27.66
  1024	525924.96/52.89	535977.26/48.34	510540.58/58.92	522066.61/51.02

2. BMSQL TPCC testing

Basic parameters:

#ODP
ALTER proxyconfig SET proxy_mem_limited='4G';
ALTER proxyconfig set enable_compression_protocol=false;

#OBServer SYS tenant
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;

• warehouse=1000
• terminals=800

Test results:

3. TPCH testing

Basic parameters:

#OBServer 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;

100G

Test results (all using columnstore tables):

4. TPC-DS testing

#OBServer 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;

100G

Test results:

Compatibility changes

Product behavioral changes

The following changes are introduced:

View changes

The following changes are introduced:

Parameter changes

The following changes are introduced:

System variable changes

The following changes are introduced:

System package changes

Function changes

Syntax changes

Recommended versions of platforms and tools

The following table lists the recommended versions of platforms and tools for OceanBase Database V4.3.2.

Upgrade notes

• Online upgrade from V4.3.1 Beta to V4.3.2 Beta is supported.
• Upgrading from the V4.2.x series or earlier versions to V4.3.2 Beta is currently not supported. As the versions evolve, support for upgrading from V4.2.x to V4.3.x will be added in the future.
• V4.3.2 has refactored the persistent format of multi-source data. The upgrade requires converting between old and new multi-source data formats, which needs some disk space and time. Therefore, it is recommended to perform the upgrade when more than 50% of disk space is available and allocate sufficient time for the upgrade if there are many partitions.

Considerations

CPU isolation for global background and foreground tasks requires mounting the Cgroup directory. It will only take effect after enable_global_background_resource_isolation is enabled and the system is restarted.