Skip to content

v4.2.3_CE_BETA
Compare
Choose a tag to compare
@zhuzhaoyang001 zhuzhaoyang001 released this 29 Apr 02:53
· 5650 commits to develop since this release
v4.2.3_CE_BETA
c129d71

Version information

Information Description
Release date April 29, 2024
Version V4.2.3_CE_BETA
Commit number c129d71
OBServer RPM version oceanbase-ce-4.2.3.0-100000112024042411

Overview

OceanBase Database V4.2.3_CE_BETA is the latest version in the V4.2.x_CE series. It introduces a range of MySQL compatibility enhancements, including roles, column-level permissions, UNION DISTINCT in recursive common table expressions (RCTEs), along with several other small, but significant, functionality improvements. The new version improves the performance in various scenarios. Specifically, it improves the read/write calculation and OBKV processing efficiency for multi-model data types, extends the parallel execution (PX) capabilities for batch DML operations, improves the performance of log archiving-based and network-based physical standby databases, and resolves performance issues of auto-increment columns and sequences in ORDER mode. This version also supports Amazon Simple Storage Service (S3) and object storage services that are compatible with the S3 protocol, such as Huawei Object Storage Service (OBS) and Google Cloud Storage (GCS), as the backup destination for the backup and restore feature. In terms of resource optimization, this version optimizes the storage space for temporary results of DDL operations, improves the bypass import capabilities, and implements global CPU resource isolation in the foreground and background. Moreover, the new version implements multiple usability features expected by business, such as oblogminer for log analytics and misoperation identification, major compaction strategy selection for coping with performance issues of buffer tables, format outlines for fuzzy plan binding and throttling, and index usage monitoring for identifying useless indexes. It also provides the alert.log system log file with higher readability for the database administrator (DBA), user commands for log stream (LS) replica management, and the capability to show the physical restore progress. In the new version, more detailed PX diagnostic data and node-level analytics of ASH reports are provided to significantly improve the usability of the system.

This version is recommended for use in testing environments only and is not yet advised for deployment in production environments.

Key features

Kernel enhancements

  • Enhancements in query parsing capabilities

    When an SQL statement contains an excessive number of IN lists, AND/OR, or UNION operators, it can generate extremely deep syntax trees, which in turn can cause issues like excessive memory consumption, stack shortages, and extended parsing time. In response, the new version has revamped the query parsing process for these extreme cases. This rewrite reduces the resource expenditure during the parsing phase and improves the stability of executing complex SQL statements, while also boosting SQL parsing speed. For example, in a query like select sum(c1) from t1 where c1 in (1, 2, 3, ...N) with N being 200,000, the parsing efficiency in V4.2.3_CE can be 20% to 30% faster than in V4.2.2_CE.

  • Enhancements in query rewrite capabilities

    In V4.2.3_CE, the optimizer has improved the query rewriting logic in several respects, such as:

    • Expression canonicalization enhancement: In earlier versions, the resolver will canonicalize various expressions in the parsing phase. For example, it will rewrite the expression A and (A or B) as A. However, nonstandard expressions generated in the SQL rewrite phase will not be rewritten. The new version introduces the expression canonicalization process in the rewrite phase to simplify expressions to the maximum extent.

    • Rewrite of conditional aggregate functions: A conditional aggregate function does not aggregate a definite expression. Instead, it aggregates an expression selected based on the branch condition judgement result. For example, the aggregate functions in select c1, sum(case when c2 = 0 then c3 else 0 end),sum(case when...)...,... from t1 group by c1, are conditional aggregate functions. The new version supports rewriting a conditional aggregate function by pushing down the function to the selection objects of each branch to reduce the number of calculations for case when expressions and aggregate functions, thereby improving the execution performance in relevant scenarios.

    • Rewrite of queries with multiple MIN/MAX aggregate functions: For a scalar group by operator, if the query contains only MIN/MAX functions and indexes are created for the MIN/MAX columns, the query can be rewritten to order by xxx limit 1, to remove ORDER BY and push LIMIT 1 to the TABLE SCAN operator based on the index order, thereby reducing data reads and sorting. In earlier versions, an SQL query containing multiple MIN/MAX functions would result in a full table scan. However, the new version optimizes this by rewriting multiple MIN/MAX functions into separate subqueries. Each subquery directly obtains the maximum or minimum value based on the index. This avoids full table scan and sorting, thereby improving the query performance.

    • Rewrite of UNION operations on constants into a query on a VALUES table: In practice, a constant table is usually expressed by using multiple UNION ALL operators. The CPU consumption and memory consumption will be high when many subqueries are involved. In the new version, UNION ALL/UNION operations on constants are rewritten into a query on a VALUES table to remarkably reduce the resource consumption in the hard parsing phase.

    • Semi-join splitting: When an EXISTS or IN subquery involves a multi-table join without join conditions, the execution plan may produce a Cartesian product with a high overhead. In the new version, semi-join splitting is performed on tables without join conditions to avoid the overhead for producing a Cartesian product.

  • Plan selection optimization

    OceanBase Database V4.2.2_CE implements new query range extraction logic, which extends predicate pushdown scenarios so that a more accurate query range is extracted for vector predicates. It also resolves the issue of memory amplification during query range extraction in some scenarios in earlier versions. On this basis, OceanBase Database V4.2.3_CE modifies the method of generating query ranges for not in expressions to optimize the range extraction performance. It supports range graph pruning to reduce the number of rows extracted in the query range, thereby decreasing memory consumption. It also allows you to specify an INDEX hint during query range extraction. For example, you can specify /*+index(t1 k1 1)*/ to extract the query range only on the first column in the k1 index. Moreover, the new version provides strategy optimization measures, such as rule-based path pruning for interesting ordering indexes and LIMIT recalculation, to resolve performance issues caused by a non-optimal plan selected in the order by limit scenario.

  • Adaptive cost model

    In earlier versions of OceanBase Database, the cost model uses constant parameters measured by internal machines to represent hardware system statistics, and describes the execution overhead of each operator by using a series of formulas and constant parameters. However, in real business scenarios, different hardware environments may have different CPU clock frequencies, sequential or random read speeds, and NIC bandwidths, thereby resulting in cost estimation deviations. The optimizer cannot always generate optimal plans in different business environments because of these deviations. The new version optimizes the implementation of the cost model. It allows you to use the DBMS_STATS package to collect or set system statistics coefficients so that the cost model can automatically adapt to the hardware environment. It also provides the DBA_OB_AUX_STATISTICS view to display the system statistics coefficients of the current tenant.

  • Change of the replicated table attribute

    Replicated tables are a special type of tables supported since OceanBase Database V4.2.0. A replicated table can read the latest data modifications from any healthy replica. To convert a replicated table into a normal table or convert a normal table into a replicated table, you must re-create the table and import data in versions earlier than V4.2.3_CE, which is time-consuming and complex. The new version allows you to use the ALTER TABLE statement to directly change the replicated table attribute, reducing the costs.

  • Compatible version control for product behavior

    To reduce errors in using features of some earlier versions that are caused due to behavioral changes in the new version after an upgrade, OceanBase Database V4.2.3_CE supports compatible version control for product behavior. You can use system variables ob_compatibility_version and ob_security_version to respectively control whether normal behavioral changes (no errors -> errors) and security behavioral changes (with privileges -> without privileges) take effect, and the OceanBase Database version whose product behavior takes effect in a tenant after the changes. Valid values are released version numbers, such as 4.2.3.0. For example, assume that the product behavior of a feature is changed in V4.2.4. When the variable is set to 4.2.3.0, the product behavior in V4.2.3.0 takes effect. When the variable is set to 4.2.4.0, the product behavior in V4.2.4.0 takes effect. Generally, the version number that determines the product behavior does not automatically change after an upgrade. If you require the product behavior in the new version, learn about and test the product behavior after the upgrade and then change the version number to the new one. This feature applies only to product behavioral changes in the future, and cannot control existing behavioral changes in released versions.

    In OceanBase Database V4.2.3_CE, the following types of product behavior are controlled by the ob_compatibility_version system variable:

    • When ob_compatibility_control is set to MYSQL5.7, the REPLACE('abd', '', null) behavior is compatible with MySQL 5.7 rather than MySQL 8.0.
    • Offsets are prohibited in the LIMIT clause in the UPDATE/DELETE statement.
    • When the projected item is an independent null value, the returned table header is changed to NULL.
    • User variable names can contain a maximum of 64 characters in length.

    The following types of product behavior are controlled by the ob_security_version system variable:

    • Privilege control for outlines and sequences.
    • CREATE TABLESPACE privilege.

Compatibility with MySQL

  • Role management

    OceanBase Database V4.2.3_CE supports the role management feature of MySQL 8.0. You can manage and maintain a group of privileges based on roles to conveniently grant privileges to and revoke privileges from a specific type of users. You can grant privileges or other roles to or revoke privileges or other roles from a role like normal users. You can grant multiple roles to a user. However, the user has only the privileges of roles in the active state.

  • Column-level privileges

    OceanBase Database V4.2.3_CE supports the column-level privilege feature of MySQL. You can control whether a user has the privileges to perform SELECT, INSERT, and UPDATE operations on specific columns in a table.

  • Privilege control for outlines and sequences

    In earlier versions, privilege control is absent for outlines and sequences. The new version supports the CREATE, ALTER, and DROP privileges of MySQL to control the creation, modification, and dropping of outlines and sequences.

  • Optimization of auto-increment column value hopping after a leader switchover

    OceanBase Database V4.x supports creating auto-increment columns in ORDER mode for higher compatibility with the behavior of auto-increment columns in MySQL. However, in OceanBase Database of a version earlier than V4.2.3_CE, values of auto-increment columns still hop after a leader switchover is performed. Generally, leader switchover is initiated by users and is not an abnormal scenario. Therefore, in the new version, values of auto-increment columns are still continuous if a leader switchover is manually initiated. This reduces the probability of value hopping of auto-increment columns.

  • Value decrease for auto-increment columns

    In OceanBase Database of a version earlier than V4.2.3_CE, you can use the ALTER TABLE statement to increase the value of an auto-increment column but cannot decrease its value, which is inconsistent with that in MySQL. In the new version, you can decrease the value of an auto-increment column. If the specified new value is greater than the maximum existing value of the auto-increment column, the setting is successful and the new value takes effect. If the specified new value is smaller than or equal to the maximum existing value of the auto-increment column, the setting is successful but the column value is automatically adjusted to the next value of the maximum existing value.

  • UNION DISTINCT for RCTEs

    MySQL 8.0 supports RECURSIVE UNION ALL and RECURSIVE UNION DISTINCT for common table expressions (CTEs). OceanBase Database supports RECURSIVE UNION ALL of MySQL since V3.2.3. OceanBase Database V4.2.3_CE supports RECURSIVE UNION DISTINCT of MySQL to ensure the uniqueness of output data. The new version also enhances RECURSIVE UNION ALL so that data can be stored in the disk when the available memory is insufficient. For more information, see UNION.

  • Control on the compatible version (MySQL 5.7 or MySQL 8.0)

    The product behavior of MySQL 5.7 conflicts with that of MySQL 8.0 in some scenarios. For example, the output of replace('a','',null) is different in the two versions. OceanBase Database V4.2.3_CE provides the tenant-level initialization variable ob_compatibility_control that specifies whether to use the product behavior of MySQL 5.7 or MySQL 8.0 in the case of product behavior conflicts. The variable is set during tenant creation. After a tenant is created, the variable cannot be modified. The superset of non-conflicting features of MySQL 5.7 and MySQL 8.0 can be used regardless of the compatible version specified by the variable.

  • Support for syntax, variables, and views

    To enable a smooth migration of various tools and frameworks from the MySQL ecosystem to OceanBase Database without the need for alterations, OceanBase Database V4.2.3_CE and subsequent versions will gradually introduce compatibility or mock handling for the complete array of MySQL 5.7 features that are currently not fully supported by OceanBase, or for functionalities that are not directly applicable.

    The compatibility features supported in V4.2.3_CE are as follows:

    • Supports the SERIAL data type.
    • [MySQL 8.0] Supports the IF NOT EXISTS syntax during trigger creation.
    • Allows you to use \N to represent NULL in SQL statements.
    • Prohibits the offset clause in the update/delete ... limit statement.
    • Supports the password function of MySQL 5.7.
    • Supports the DROP USER IF EXISTS syntax.
    • Limits a user variable identifier to 64 characters in length.
    • Supports CREATE TABLE ... [IGNORE | REPLACE] SELECT statement.
    • Supports the STRAIGHT_JOIN syntax in SELECT statements.
    • Supports the CREATE TABLESPACE privilege.
    • Allows you to grant, revoke, and view the SHUTDOWN and RELOAD privileges of MySQL, which do not actually take effect in OceanBase Database.
    • Ignores the PRIORITY option in DML statements, with no errors returned and with the option being ineffective.
    • Ignores the LOW_PRIORITY and HIGH_PRIORITY options in INSERT statements, with no errors returned and with the options being ineffective.
    • Ignores the LOW_PRIORITY option in UPDATE statements, with no errors returned and with the option being ineffective.
    • Ignores the LOW_PRIORITY and QUICK options in DELETE statements, with no errors returned and with the options being ineffective.
    • Ignores the LOW_PRIORITY option in REPLACE statements, with no errors returned and with the options being ineffective.
    • Ignores the SQL_SMALL_RESULT, SQL_BIG_RESULT, SQL_BUFFER_RESULT, and HIGH_PRIORITY options in SELECT statements, with no errors returned and with the options being ineffective.
    • Ignores the syntax of descending indexes, with no errors returned and with the syntax being ineffective, as in MySQL 5.7.
    • Ignores the reference_definition syntax after the column definition in the CREATE TABLE statement, with no errors returned and with the syntax being ineffective, as in MySQL 5.7.
    • Ignores the KEY_BLOCK_SIZE option specified in the table/index creation syntax, with no errors returned and with the option being ineffective.
    • Returns no errors for the FLUSH PRIVILEGES syntax, which is actually inapplicable in OceanBase Database.
    • Returns no errors for the SHOW PROFILE/PROFILES syntax, which actually does not take effect in OceanBase Database.
    • Returns no errors for the SHOW FUNCTION/PROCEDURE CODE syntax, which is actually not supported in OceanBase Database.
    • Supports the INFORMATION_SCHEMA.PROFILING table schema.
    • Supports the following variables of MySQL: debug, debug_sync, innodb_change_buffering_debug, innodb_compress_debug, innodb_disable_resize_buffer_pool_debug, innodb_fil_make_page_dirty_debug, innodb_limit_optimistic_insert_debug, innodb_merge_threshold_set_all_debug, innodb_saved_page_number_debug, innodb_trx_purge_view_update_only_debug, innodb_trx_rseg_n_slots_debug, stored_program_cache, profiling, profiling_history_size, and innodb_stats_persistent. You can query and set these variables, but they do not actually take effect. After you set these variables, no error but a warning is returned.
    • Supports the COM_PROCESS_INFO and COM_PROCESS_KILL communication protocols of MySQL 5.7. Additionally, it supports the COM_REFRESH and COM_DEBUG communication protocols, which do not actually take effect.

Multimodel features

  • MySQL GIS

    OceanBase Database supports geographic information system (GIS) data types and some expressions related to spatial objects since V4.1.0, and gradually supports the storage, computing, and analysis of spatial data. The new version supports the PostGis_ST_GeoHash expression in MySQL mode for calculating the geohash of a geometry. It supports the PostGis_ST_MAKEPOINT expression for creating 2D and 3D geometry points based on the coordinates. It also supports the ST_ASGEOJSON expression of MySQL for converting a geometry (WKB) into a JSON string (binary) based on a specific format.

  • Memory usage optimization for JSON, XML, and GIS data types

    The new version optimizes the memory usage during the execution of JSON expressions. Specifically, the memory usage is reduced by 1/2 when you perform query operations, such as JSON_KEYS, JSON_LENGTH, and JSON_SEARCH, on JSON data. The memory usage is reduced by about 3/4 when you perform output operations, such as JSON_PRETTY and JSON_UNQUOTE, on JSON data. The new version also optimizes the memory usage in XML insert/update and output scenarios. Specifically, the memory usage is reduced by about 2/3 to 3/4 when you perform XML output operations such as GetClobVal and XmlCast, and unnecessary parsing is reduced when you perform XML insert/update operations such as UpdateXml and InsertChildXml.

    Moreover, the new version optimizes the memory usage for GIS data types in data input, output, and some query scenarios. Specifically, the memory usage is reduced by about 3/4 for GIS input scenarios such as st_geomfromtext, st_geomfromwkb, st_geogfromtext, and geometrycollection, and reduced by 1/2 for GIS output scenarios such as st_astext and st_aswkb. The memory amplification degree is remarkably decreased for query scenarios, including spatial relation query expressions such as st_crosses, st_overlaps, and _st_touches, and GIS attribute acquisition expressions such as st_geometrytype and st_iscollection.

  • Optimization of the GIS spatial relation calculation performance

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

    • In a full query window, the response time (RT) of the ST_INTERSECTS expression for a point in OceanBase Database is slightly shorter than that in PostgreSQL. For other test scenarios, the average RT is less than a half of that in PostgreSQL. Specifically, the time spent on ST_INTERSECTS for a linestring is only 1/5 of that in PostgreSQL. Compared with earlier versions, the new version improves the calculation performance by orders of magnitude.

    • In a large query window, the average RT in all test scenarios is obviously shorter than that in PostgreSQL. The time spent on ST_INTERSECTS for a linestring is only 1/7 of that in PostgreSQL. Compared with earlier versions, the new version improves the calculation performance by orders of magnitude.

    • In a small query window, the calculation performance of the ST_CONTAINS expression is higher than that in PostgreSQL. Specifically, the time spent on ST_CONTAINS for a point is about 1/4 of that in PostgreSQL, and the time spent on ST_CONTAINS for a linestring is 80% of that in PostgreSQL. However, the calculation performance of the ST_INTERSECTS expression for a linestring is on a par with that in PostgreSQL. The time spent on ST_INTERSECTS for a point is about twice that in PostgreSQL.

  • Read/Write performance optimization for LOB data stored in OUTROW mode

    OceanBase Database V4.2.3_CE optimizes the performance for large object (LOB) data stored in OUTROW mode.

    • In a table scan scenario, the new version can improve the performance by almost 10 times for small LOBs and by at least twice for large LOBs, compared with earlier versions. However, the performance in this scenario is still lower than that of the INROW storage mode.
    • In a point select scenario in the new version, the queries per second (QPS) of the OUTROW storage mode is less than that of the INROW storage mode by no more than 20% for small LOBs (sized less than 8 KB), and is less than that of the INROW storage mode by about 5% for medium and large LOBs (sized more than 32 KB). In a scenario where LOBs are not involved in calculations, the OUTROW storage mode can achieve a higher query performance.
    • In a point update scenario, the new version improves the performance of the OUTROW storage mode by twice, compared with earlier versions.

OBKV enhancements

  • OBKV global indexes

    OceanBase Database supports local indexes for OBKV since V4.1.0. The new version also supports global indexes for OBKV. DML operations such as insert, update, delete, insert_or_update, replace, and increment/append are supported for global index tables. Moreover, global indexes can be specified in general queries and asynchronous queries.

  • OBKV performance optimization

    OBKV is a key-value (KV) storage product integrated into OceanBase Database and is characterized by low delay and high throughput. In a scenario that does not require SQL for complex logic processing, you can get around the SQL module and directly call the storage API to achieve the optimal performance. Actual business scenarios and internal end-to-end testing and analysis results show that the performance overhead at the KV module layer in earlier versions can be further reduced. Therefore, the new version optimizes the performance in the following scenarios:

    • Put feature: The Put feature is intended for overwrite scenarios. An overwrite operation skips the primary key conflict check and forcibly overwrites the existing record.

    • Group commit: Group commit is a feature that allows the server to perform batch operations. After you enable group commit, the server will group Put or Get operations based on the execution plan, automatically resize the groups based on the load, and execute the operations by group. You can use the tenant-level parameter enable_kv_group_commit to enable group commit. After the feature is enabled, the operations per second (OPS) can be increased by 30% to 50% without obviously affecting the RT.

    • Batch processing: For a single-operation API, it takes much time to start and close transactions. The batch operation API of OBKV avoids this issue, improving the operation efficiency to a certain extent. However, each operation still requires the steps of constructing an operator, opening the operator, submitting a data access service (DAS) task, closing the operator, and destructing the operator, which causes execution time losses. To further improve the execution efficiency of the batch operation API, the new version implements batch operation processing at the operator level to reduce the number of operator constructions and destructions by n-1 and decrease the overhead of the DAS task, thereby effectively improving the overall performance.

    • Schema acquisition logic: In the new version, schemas are obtained at the statement level. Specifically, a simple schema is obtained only during statement execution. This avoids performance losses caused by frequent schema acquisition. In addition, frequently obtained schema information, such as table_id and column_id, is cached in the plan to form a lightweight KV schema cache.

    • Only one result returned for batch processing: In a multi-set scenario, a result is returned for each operation in earlier versions. In the new version, after batchOps.setAtomicOperation(true) and batchOps.setReturnOneResult(true) are specified, only one result, success or failure, is returned for a multi-insert, multi-put, or multi-delete scenario, shortening the interaction duration.

Performance improvements

  • DML performance optimization

    OceanBase Database allows you to use the parallel DML (PDML) mechanism to improve the performance of the insert, update, and delete operations on large-sized tables and indexes. However, PDML is inapplicable in some scenarios, such as replace into ..., insert ... on duplicate key, insert all ..., merge into ..., primary key update, insertion with auto-increment columns, and multi-table update. In earlier versions, a single thread is used for writing in the preceding scenarios. When a large amount of data is involved, the response time is long, which affects the user experience. To address the performance bottleneck caused by a large amount of data in these scenarios, OceanBase Database V4.2.3_CE implements parallel write at the DAS layer. It uses a parallel control mechanism similar to PDML to significantly improve the DML execution performance.

  • Backup performance optimization

    During backup in OceanBase Database, a continuity check is performed to make sure that the baseline version number is greater than the minor compaction version number, which ensures data integrity. However, the continuity check involves reading data from the backup media and executing operations during which the operated data needs to be locked, compromising the backup performance. The new version reduces the performance overhead caused by continuity check during backup. You can use the ha_low_thread_score parameter to control the number of threads used for backup, thereby effectively improving the backup performance.

  • Performance optimization for network-based physical standby databases

    OceanBase Database V4.2.3_CE optimizes the RPC and network frameworks to reduce the time required for transmitting logs between the primary and standby databases. It also optimizes the controlled log pull process of the standby database to reduce the control frequency, thereby improving the log synchronization performance between the primary and standby databases.

  • Caching and disk storage of the compilation result after a stored procedure undergoes a DDL change

    OceanBase Database V4.2.2_CE supports storing compiled stored procedures in the disk during execution. This resolves the performance issue caused by recompilation when a stored procedure fails to obtain the PL cache during execution. However, after a DDL change is performed on a stored procedure, the stored procedure still needs to be recompiled during execution. In this case, the performance can be further improved. In OceanBase Database V4.2.3_CE, after a DDL change is performed on a stored procedure, the compilation result can be stored in the PL cache and disk. When the stored procedure is executed later, the PL cache can be directly accessed, further improving the execution performance of the stored procedure.

  • Extension of parallel DDL operations

    Parallel DDL and serial DDL are mutually exclusive. The performance is poor when parallel DDL and serial DDL are executed alternatively. As the version evolves, OceanBase Database continues to extend parallel DDL operations. OceanBase Database implements the parallel DDL framework and supports parallel TRUNCATE in V4.1.0, supports parallel CREATE TABLE in V4.2.1, and supports parallel COMMENT and CREATE INDEX in V4.2.2_CE. On this basis, OceanBase Database V4.2.3_CE supports parallel CREATE VIEW, further increasing the schema migration speed for OceanBase Migration Service (OMS).

  • Performance optimization for auto-increment columns in ORDER mode

    For auto-increment columns in ORDER mode, you must ensure the continuity of inserted data. When you insert a value into an auto-increment column in a distributed architecture, the value will be persisted to the internal table to ensure the order of values of the auto-increment column. The response time will be long in the case of high concurrency. OceanBase Database V4.2.3_CE supports cache size prefetch to reduce the number of accesses to the internal table, thereby drastically improving the performance.

  • Performance optimization for sequences in ORDER mode

    OceanBase Database allows you to specify the order and cache attributes to create a globally continuous sequence. In earlier versions however, the cache attribute is ignored, which means that the nocache attribute actually takes effect, leading to obvious performance issues in high concurrency scenarios. OceanBase Database V4.2.3_CE allows you to generate a continuous sequence by supporting the order and cache attributes on a central node. This greatly improves the execution performance in high concurrency scenarios since the cache attribute is realized.

  • Performance optimization for the SHOW TABLE STATUS statement

    In earlier versions, the performance of the SHOW TABLE STATUS FROM ... LIKE statement is poor because indexes related to table_name are not used. The new version remarkably improves the query performance in scenarios involving single-table filter conditions.

  • Support of hints in the CREATE statement

    OceanBase Database V4.2.3_CE supports the /*+ parallel(N) */ hint in the CREATE TABLE statement. You can use this hint in a CREATE TABLE ... AS SELECT ... statement to specify the degree of parallelism (DOP) for data queries and writes during table creation.

Resource optimization

  • Link compression between OBServer nodes and ODP

    In actual business scenarios, applications and database services may be deployed across regions. Such a scenario is characterized by far distances and limited bandwidth resources accompanied by read and write requirements involving large amounts of data. To reduce the bandwidth usage by the transmission of large amounts of data, OceanBase Database V4.2.3_CE supports link compression between OBServer nodes and OceanBase Database Proxy (ODP). This feature is supported only when the version of ODP is V4.2.3_CE or later.

  • Optimization of DDL temporary result space

    Temporary execution results of DDL operations are usually stored in a materialized structure. When an index is created, OceanBase Database needs to scan the data table and insert data into the index table. It may need to sort the scanned data during this process. If the memory space is insufficient, it will temporarily store the data in the memory in a materialized structure to release the memory space for later scan. Then, it will merge and sort the data in the materialized structure.

    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.

  • Bypass import optimization

    • Compression of temporary files: Temporary files are generated during bypass import. When a large amount of data is imported, the generated temporary files will occupy much disk space. In this case, you need to pay attention to the disk usage and prevent the disk space from being used up by these temporary files. When the DOP for bypass import is greater than or equal to 8, the system will compress the generated temporary files to reduce the occupied disk space.
    • Import of multiple files by using wildcards: During bypass import, you can import multiple files by using wildcards. Specifically, you can use the wildcards * and ? to concurrently import multiple files whose names match a specific pattern, thereby improving the import efficiency and convenience.

  • Global CPU resource isolation for foreground and background tasks

    In a high-performance computing environment, reasonable resource allocation and isolation is decisive in ensuring system stability and improving efficiency. An effective resource isolation strategy can prevent resource contention and interference between tasks, thereby improving the resource utilization efficiency and overall service quality. At present, OceanBase Database allows you to configure different unit configs for tenants to implement resource isolation between the tenants, and use the DBMS_RESOURCE_MANAGER system package to configure resource isolation within a tenant. Resource isolation is supported for CPU and I/O resources. In a business scenario where you want to prevent background tasks from contending with foreground tasks for CPU resources, resource isolation for background tasks at the global level is a better choice than tenant-level resource isolation. OceanBase Database V4.2.3_CE supports global CPU resource isolation for foreground and background tasks. You can limit the CPU resources available for background tasks on an overall basis, which is more convenient and easier than using the DBMS_RESOURCE_MANAGER system package to separately configure resource isolation.

  • Partition balancing strategy optimization

    OceanBase Database supports partition balancing since V4.2.0. However, full balancing still cannot be achieved in some scenarios. OceanBase Database V4.2.3_CE optimizes the partition balancing strategy. You can scatter continuous partitions when you create a partitioned table. For a user table that contains a LONGTEXT or LOB column or a local index, the system will also calculate associated tables for partition disk balancing, contributing to more balanced disk usage.

  • System log compression

    When the business traffic is heavy, system logs are refreshed quickly. In this case, troubleshooting will be affected due to a short retention period of system logs. The new version provides the system log compression feature. For the observer.log, rootservice.log, election.log, and trace.log log files, when the number of log files of a specific type reaches the value specified by syslog_file_uncompressed_count, the earliest logs will be compressed by using the compression method specified by syslog_compress_func. When the total occupied disk space approaches the upper limit specified by syslog_disk_size, the earliest log files are deleted to release the occupied disk space. After you enable zstd compression, the volume of logs that can be stored in the same disk space is 20 times that of logs that can be stored when compression is disabled.

Reliability improvements

  • Optimization of selecting the migration source

    In earlier versions, replicas in the same zone, Internet Data Center (IDC), or region are not preferentially considered when a migration source is selected. This may lead to far distance migration with poor performance. In addition, if a leader is selected as the source, business requests and the migration task may be processed slowly when a large number of business requests are initiated. OceanBase Database V4.2.3_CE defines the regional relationships between the migration source and destination as follows: same IDC, same region but different IDCs, and different regions. It also provides an enumeration parameter choose_migration_source_policy for you to specify the prioritizing mode for selecting the source replica for migration. You can preferentially select a nearby follower to improve the migration efficiency and reduce the pressure on the leader.

  • Write stop upon high data disk usage

    In earlier versions, when the data disk usage is high, user write requests will still be processed until an error is returned after the memory is full due to minor compaction failures or after the clog disk is full. In this case, you need to urgently scale out the clog disk space or tenant memory to resolve this issue. In the new version, the kernel provides the feature of write stop upon high data disk usage. When the data disk usage reaches the value specified by data_disk_write_limit_percentage, an error is returned for new user write requests. After you drop tables, transfer data, or scale out the disk space to reduce the data disk usage, user writes automatically resume.

High availability enhancements

  • S3, OBS, and GCS supported as the backup media

    Earlier OceanBase Database versions support Network File System (NFS), Alibaba Cloud Object Storage Service (OSS), and Tencent Cloud Object Storage (COS) as the storage media for the backup and restore feature. The new version supports S3 and object storage services compatible with the S3 protocol, such as OBS and GCS, as the destination for log archiving and data backup. The backup data stored on S3 or an object storage service compatible with the S3 protocol can be used for a physical restore.

  • Physical restore at the set or piece level

    In actual business scenarios, there is a need for secondary backups where data backup sets or archive logs are manually transferred to a new path. The physical restore feature of OceanBase Database requires that restore takes place within the tenant's designated archive and backup paths, which means it cannot utilize backup data that has been manually moved to a new path, thus limiting the flexibility of the restore process. The new version introduces improvements to the SET/PIECE level physical restore capabilities by providing an add restore source command. This command allows for the loading of data backup sets (SET) or log archives (PIECE) from a new path, enabling restoration to a specific point in time as needed.

Usability improvements

  • OceanBase LogMiner

    OceanBase Database V4.2.3_CE supports OceanBase LogMiner (oblogminer for short). oblogminer is a command-line tool used to perform log analysis online or offline for OceanBase Database. oblogminer uses OceanBase Change Data Capture (CDC) to pull and parse clogs, converts the logical logs output by OceanBase CDC to a readable format, and stores the logs in the specified position. The tool applies to the following scenarios:

    • Data misoperations: Data misoperations may be caused by various reasons. For example, rows are mistakenly deleted or updated due to an incorrect range condition in the WHERE clause. In this case, you can use oblogminer to accurately obtain the details about the misoperation and restore the data to the state before the misoperation.
    • Data analysis: oblogminer will organize various information, such as transactions and table schemas, in clogs, and display the organized information in a user-friendly manner. You can perform data analysis based on the output results of oblogminer as needed. You can also read the output results of oblogminer in combination with external tables for query analysis in the database.

  • Optimization of adaptive major compactions for buffer tables

    If you frequently insert data into a table while performing batch deletion, or if a large number of update operations are concurrently performed, the query and update performance obviously deteriorates though the table contains only a moderate number of rows. This table is defined as a queuing table in OceanBase Database. In terms of business, such a table is known as a buffer table. Buffer tables are common in databases built based on the log-structured merge-tree (LSM-tree) architecture. In the LSM-tree architecture, a delete operation is only a logical mark before a major compaction is performed so data is not physically deleted. When a large proportion of the incremental data is marked as deleted, physical rows are far more than logical rows, resulting in severe read amplification and affecting the generation of execution plans in the optimizer.

    OceanBase Database V4.1.0 can automatically identify partitions involving many changes within a specific period of time and perform adaptive major compactions for these partitions. However, no manual control means are present. To flexibly resolve the issue of performance deterioration caused by buffer tables, OceanBase Database V4.2.3_CE further optimizes adaptive major compactions for buffer tables and provides five levels of major compaction strategies. You can set different table_mode values for tables based on the business scenario to cope with read amplification caused by the buffer table issue, thereby improving metrics, such as QPS, during the long-term operating of the system.

  • Format outline

    OceanBase Database of earlier versions allow you to create a normal outline to bind an execution plan or throttle specific SQL statements. This feature has strict requirements on the SQL text format. Except for the parameterized part in the outline, an extra space or tab or case inconsistency will cause a failure to hit the outline. This feature has poor usability in scenarios where the SQL text format is not fixed. OceanBase Database V4.2.3_CE provides the format outline feature with looser matching rules. When the system attempts to match a format outline for an SQL statement, it will normalize the statement into a standard format by ignoring the letter case and non-syntactic definition symbols such as spaces before the original outline matching process. SQL statements with the same format SQL text or format SQL ID after normalization will hit the same format outline. In a scenario where the number of elements in an IN list is not fixed, such as a scenario where SQL statements that contain in(1,2), in(4,5,6), and in(7,8,9,10) are delivered by business, you need to bind three outlines in(?,?), in(?,?,?), and in(?,?,?,?) to use the precise match feature of normal outlines to control the execution plan. The format outline feature in the new version requires you only to bind one outline and the system will normalize the IN list into the in(?) format. This way, multiple SQL statements containing IN lists with different numbers of elements can share the same outline. When both a normal outline and a format outline are present, the normal outline is preferentially hit.

  • Index usage monitoring

    We usually create indexes to improve the query performance of the database. However, more and more indexes are created as data tables are used in more business scenarios by more operators. Unused indexes are a waste of storage space and increase the overhead of DML operations. In this case, you need to drop useless indexes to alleviate burden on the system. However, you can hardly identify all useless indexes by manual efforts. Therefore, OceanBase Database V4.2.3_CE provides the index usage monitoring feature. After you enable this feature and set the sampling method, the index usage information that meets the rules is recorded in the memory of a user tenant and refreshed to the internal table once every 15 minutes. You can then query the DBA_INDEX_USAGE view to find out whether an index is referenced and drop useless indexes to release space.

  • System log optimization

    The ./alert/alert.log file is added to the system log directory to record log information concerned by DBAs. This log file can help resolve the issue where the observer.log file has poor readability because it contains a large volume of logs. You can use the cluster-level parameter alert_log_level to set the log level to INFO, WARN, or ERROR. In addition, the external system table sys_external_tbs.__all_external_alert_log_info is provided for you to view logs in the alert.log file in a structured way.

  • LS replica management

    OceanBase Database of a version earlier than V4.0.0 provides a series of O&M commands for partition replica management. For example, you can use related commands to add a replica to a partition, drop a replica from a partition, and convert the type of a replica for a partition. OceanBase Database V4.x replaces the concept of partition with LS. V4.2.3_CE redesigns the partition O&M commands for LS replica-level O&M. It provides a series of syntax for adding LS replicas, dropping LS replicas, converting the type of LS replicas, migrating LS replicas, modifying the number of Paxos members of an LS replica, and canceling a disaster recovery task, to meet manual LS replica O&M requirements.

  • Physical restore progress statistics

    To allow you to learn about the running status and progress of a physical restore task, and estimate the remaining time required for the restore task, the new version provides the physical restore progress statistics feature. You can query the CDB/DBA_OB_RESTORE_PROGRESS view for the restore progress in real time.

  • Enhanced PX diagnostic capabilities

    To facilitate distributed plan troubleshooting, OceanBase Database V4.2.3_CE records the memory usage and disk usage during operator execution as well as the maximum memory usage and disk usage during the entire execution process in the SQL_PLAN_MONITOR view. It also records in the SQL_AUDIT view the numbers of rows in the MemTable and SSTable of trace_id corresponding to the query coordinator (QC), and supports summarizing related information of trace_id corresponding to various PX worker threads. Moreover, it allows you to use the OBDiag tool to pull the logs of the server that first reports an error based on trace_id.

  • Enhanced PS diagnostic capabilities

    When a prepared statement (PS) handle leak occurs in versions earlier than V4.2.3_CE, you can only query the GV$OB_PS_ITEM_INFO view for global information, and no session-level diagnostic methods are available. OceanBase Database V4.2.3_CE provides the [G]V$OB_SESSION_PS_INFO view to display the PS reference information about each session, so as to help you accurately locate PS handle leaks.

  • Node-level ASH report analysis

    Earlier versions support generating cluster-level ASH reports but lack node-level ASH statistics. If you want to view the ASH data of a specific node, you need to use an SQL script to obtain the data. This causes inconveniences in analyzing issues that occur on a single node. OceanBase Database V4.2.3_CE supports node-level ASH report analysis capabilities. Two parameters are added to the DBMS_WORKLOAD_REPOSITORY.ASH_REPORT package function for you to specify the IP address and port number of a specific node for ASH report analysis.

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

    In earlier versions, you can use the tenant-level parameter auto_increment_cache_size to control the number of cached values requested by auto-increment columns at a time. The parameter takes effect for all tables with auto-increment columns. Generally, a larger cache size contributes to higher performance. However, if value hopping occurs frequently in an auto-increment column and the maximum value of the column is small, the value range of the column will be quickly used up. OceanBase Database V4.2.3_CE supports table-level cache size setting for auto-increment columns. You can set different cache sizes for tables based on the column type, business model, and business traffic, to keep performance stable against frequent value hopping.

  • NETWORK_WAIT_TIME statistical item

    The NETWORK_WAIT_TIME statistical item is added to views SQL_AUDIT and SQLSTAT to show the wait time of wait events of the NETWORK class, such as synchronous RPC wait events and DAS asynchronous RPC lock wait events. When you troubleshoot slow SQL statements that are remotely executed, you can use this statistical item to confirm the network overhead to assist diagnostics.

  • XA transaction monitoring statistical items

    Some customers use the XA protocol to ensure the atomicity of committing cross-database transactions in their business. OceanBase Database adopts a distributed architecture and consumes resources for the execution of XA statements. Therefore, more than 30 statistical items, such as XA_TRANS_START_COUNT, XA_READ_ONLY_TRANS_TOTAL_COUNT, and XA_ONE_PHASE_COMMIT_TOTAL_COUNT, are added to the SYSSTAT view for you to be aware of the changes in the XA transaction traffic.

  • Improvement in SQL information recording in crash logs

    The SQL information in crash logs can be used for troubleshooting. OCP-Agent can also collect the SQL information and generate alerts when necessary. In versions earlier than V4.2.3_CE, SQL information will be recorded in crash logs when the INNER SQL main thread or user SQL main thread crashes. However, no SQL information is recorded in crash logs when the REMOTE thread, PX SQC thread, PX WORKER thread, or DAS remote execute thread crashes. For the preceding scenarios, the new version supports obtaining the sql_id or sql_string information about the execution server. When the execution server crashes, the SQL information about the control server will be recorded in crash error logs to help you locate the crash reason.

  • Support of IPv6 for OBServer nodes

    The new version supports IPv6 addresses for OBServer nodes. SQL clients and RPC clients can connect to OBServer nodes through IPv6 addresses. It also supports hybrid deployment of OBServer nodes with an IPv4 address and those with an IPv6 address in the same cluster. You can upgrade an IPv4 cluster to the new version but cannot upgrade an IPv4 cluster to an IPv6 cluster of the new version. For more information about the deployment modes, see Deploy a standalone OceanBase database by using the CLI.

  • Support for querying data from the GV$PLAN_CACHE_PLAN_EXPLAIN view only by plan ID

    When you query data from the [G]V$PLAN_CACHE_PLAN_EXPLAIN view in earlier versions, you must specify the IP address, port number, tenant ID, and plan ID as the filter conditions. If you specify only the plan ID, the query result set is empty, which means poor usability of the view. The new version supports scanning the underlying virtual table of this view and allows you to specify only the plan ID to accurately query data from this view.

  • Trace ID parsing

    OceanBase Database uses a trace ID to mark the full process of an SQL request. The trace ID can be used to associate monitoring metrics or query logs. A trace ID contains the IP address and port number of the OBServer node that initiates the SQL request. However, no method is available for directly parsing the information contained. The new version provides the decode_trace_id function for parsing a trace ID to obtain the IP address and port number.

Compatibility changes

Product behavioral changes

The following table describes the changes made in this version.

Feature Description
Parallel COMMENT and parallel CREATE INDEX are disabled by default after an upgrade. Since OceanBase Database V4.2.2_CE, support for parallel COMMENT and parallel CREATE INDEX functionalities has been introduced and they are enabled by default. In V4.2.3_CE, when creating a new cluster or tenant, these features along with the newly introduced parallel CREATE VIEW are activated by default as well. However, for tenants upgraded from an earlier V4.x version to V4.2.3_CE, these three parallel DDL operations are disabled by default. If there is a need for high-performance DDL operations through these parallel functionalities, they can be enabled using the hidden parameter _parallel_ddl_control. For example, executing the command alter system set_parallel_ddl_control='SET_COMMENT:ON,CREATE_INDEX:ON,CREATE_VIEW:OFF' tenant='xxx'; will enable parallel COMMENT and parallel CREATE INDEX, while leaving parallel CREATE VIEW disabled.
In the PS protocol, an error is returned when the number of parameters exceeds 65,535. The MySQL protocol supports a maximum of 65,535 parameters. When the number of parameters exceeds 65,535, an exception is thrown. In earlier versions of OceanBase Database, 65,535 parameters, instead of an error, are returned in this scenario. The new version is adapted for compatibility with MySQL, and an error is returned when the number of parameters exceeds 65,535.
RENAME TABLE is changed to an operation that requires a table lock. In earlier versions, RENAME TABLE is an online DDL operation that does not require to lock the table. Unexpected issues may arise when a transaction involves other operations apart from the RENAME TABLE operation. OceanBase Database V4.2.3_CE supports locking the table when you perform a RENAME TABLE operation, and provides read/write defense during the operation.
The [g]v$plan_cache_plan_explain view supports data queries by plan ID only. When you query data from the [g]v$plan_cache_plan_explain view in earlier versions, you must specify the IP address, port number, tenant ID, and plan ID as the filter conditions. If you specify only the plan ID, the query result set is empty, which means poor usability of the view. The new version supports scanning the underlying virtual table of this view and allows you to specify only the plan ID to accurately query data from this view.
The meaning of the tenant_id field in the [g]v$ob_sql_audit view is changed. Some internal requests generated in a user tenant are initialized in the sys tenant. When such an internal request needs to read data, it reads data from the user tenant. In this case, the value of tenant_id is 1, and that of effective_tenant_id is the ID of the current tenant. For sequential collection requirements based on OceanBase Autonomy Service (OAS) within a tenant, OceanBase Database V4.2.3_CE adjusts the tenant_id field, which can be used as an index, to be equivalent with the effective_tenant_id field.
The value of the sql_id field in the [g]v$ob_sql_audit view is changed. In versions earlier than V4.2.3_CE, the value of the sql_id field in a CALL statement is an MD5 value generated based on an empty string, and that of the sql_id field in an anonymous block is an MD5 value generated based on the original PL code that is not parameterized. In V4.2.3, the values of the sql_id fields in both the anonymous block and the CALL statement are changed to an MD5 value generated based on the parameterized statement.
Values of some fields in the [g]v$ob_active_session_history view are changed. In versions earlier than V4.2.3_CE, the values of the MODULE, ACTION, and CLIENT_ID fields are always NULL. Since V4.2.3_CE, the three fields show actual user settings. Specifically, MODULE and ACTION are set by using DBMS_APPLICATION_INFO, and CLIENT_ID is set by using DBMS_SESSION.
null values in a projected column with no alias are replaced with NULL. In MySQL, null values (\\N, null, Null...) in a projected column with no alias are replaced with NULL. However, null values in a compound expression remain the original string. In earlier versions of OceanBase Database, null values in a projected column are not modified. In the MySQL mode of OceanBase Database V4.2.3_CE, if ob_compatibility_version is set to 4.2.3.0 or a later V4.2.x_CE version, the behavior in the preceding scenario is the same as that in MySQL.
Offset semantics are prohibited in the LIMIT clause of a DELETE/UPDATE statement. In the MySQL mode of OceanBase Database V4.2.3_CE, if ob_compatibility_version is set to 4.2.3.0 or a later V4.2.x_CE version, offset semantics are prohibited in the LIMIT clause of a DELETE/UPDATE statement. Specifically, a syntax error must be returned for the update/delete...limit x,x and update/delete...limit x offset x syntaxes supported in earlier versions, to keep consistent with MySQL.
The return result of REPLACE('abd', '', null) depends on the value of ob_compatibility_control. In the MySQL mode of OceanBase Database V4.2.3_CE, if ob_compatibility_version is set to 4.2.3.0 or a later V4.2.x version, after an upgrade from an earlier version or if ob_compatibility_control is set to MYSQL5.7 during tenant creation, the behavior of REPLACE('abd', '', null) is changed from that of MySQL 8.0 to MySQL 5.7.
User variable names can contain a maximum of 64 characters in length. In the MySQL mode of OceanBase Database V4.2.3_CE, if ob_compatibility_version is set to 4.2.3.0 or a later V4.2.x_CE version, the length of user variable names is limited to 64 characters, which is not limited in earlier versions.
Privilege control is supported for outlines and sequences. In the MySQL mode of OceanBase Database V4.2.3_CE, if ob_security_version is set to 4.2.3.0 or a later V4.2.x_CE version, you can create and manage outlines and sequences only after you are granted the CREATE, ALTER, and DROP privileges.
The CREATE TABLESPACE privilege is required for creating and managing tablespaces. In the MySQL mode of OceanBase Database V4.2.3_CE, if ob_security_version is set to 4.2.3.0 or a later V4.2.x_CE version, you can create and manage tablespaces only after you are granted the CREATE TABLESPACE privilege.
Startup of an OBServer node will not fail when the IP address does not match any NIC name. When you start an OBServer node in the new version, if no local NIC whose name matches the IP address specified by local_ip is found, an error log is recorded and the device name specified by devname is used as the local NIC name. In earlier versions, the startup will fail in this case.

View changes

The following table describes the changes made in this version.

View Change type Description
CDB/DBA_OB_RESTORE_PROGRESS Modified Columns RECOVER_SCN, RECOVER_SCN_DISPLAY, RECOVER_PROGRESS, TABLET_COUNT, FINISH_TABLET_COUNT, and RESTORE_PROGRESS are added to show the physical restore progress.
CDB/DBA_OB_LS_REPLICA_TASKS Modified Columns DATA_SOURCE_SVR_IP, DATA_SOURCE_SVR_PORT, and IS_MANUAL are added to record the data source referenced during the execution of a disaster recovery task and the source that initiates the disaster recovery task.
CDB/DBA_OB_LS_REPLICA_TASK_HISTORY New Displays the execution history of disaster recovery tasks. You can query the CDB view only in the sys tenant, and the DBA view in all tenants.
CDB/DBA_OB_AUX_STATISTICS New Displays auxiliary statistics of each tenant. You can query the CDB view only in the sys tenant, and the DBA view in all tenants.
[G]V$SQL_WORKAREA Modified The DB_ID column is added to describe the ID of the database to which the connection of the request belongs.
[G]V$OB_SQL_AUDIT Modified
  • The STMT_TYPE column is added for determining the SQL type.
  • The NETWORK_WAIT_TIME column is added to show the wait time of wait events of the NETWORK class.
  • The PROXY_USER column is added to show the username of the proxy user when a proxy user is used for logon.
  • The TENANT_ID column is adjusted to be equivalent with the EFFECTIVE_TENANT_ID column.
  • The SQL_ID column shows the actual MD5 value generated for a PL request executed in a CALL statement or an anonymous block.
[G]V$OB_PROCESSLIST Modified The PROXY_USER column is added to show the username of the proxy user when a proxy user is used for logon.
[G]V$OB_ACTIVE_SESSION_HISTORY Modified Since V4.2.3_CE, the MODULE, ACTION, and CLIENT_ID columns show the actual user settings. Specifically, MODULE and ACTION are set by using DBMS_APPLICATION_INFO, and CLIENT_ID is set by using DBMS_SESSION.
[G]V$OB_SESSION_PS_INFO New Displays the information about PS opened in all sessions of tenants. The V$ view displays the PS information about the current OBServer node, and the GV$ view displays the PS information about all OBServer nodes. You can query these views in all tenants.
CDB/DBA_INDEX_USAGE New Displays information about index usage. You can query the CDB view only in the sys tenant, and the DBA view in all tenants.
V$OB_COMPATIBILITY_CONTROL New Displays all features that support product behavior compatibility control based on the released OceanBase Database versions. This view is applicable in MySQL mode.
mysql.role_edges New Displays the relationships between roles and users who are granted the roles. This view is applicable in MySQL mode.
mysql.default_roles New Displays the roles that are enabled for users by default. This view is applicable in MySQL mode.
mysql.columns_priv New Displays the column-level privileges of users. This view is applicable in MySQL mode.
sys_external_tbs.__all_external_alert_log_info New Displays logs in the alert.log file in a structured way. This is a new external table in the sys tenant.

Parameter changes

Parameter Change type Description
enable_kv_group_commit New Specifies whether to enable group commit for OBKV. If this feature is enabled, the server will group operations based on the execution plan and execute the operations by group. If this feature is not enabled, the server will execute the operations one by one. It is a tenant-level parameter. The default value is False, which specifies not to enable this feature.
choose_migration_source_policy New The strategy for selecting the migration source. It is a tenant-level parameter. Two strategies are supported:
  • IDC: A follower in the same IDC is preferentially selected as the migration source. If only a leader is available, the leader is selected.
  • Region: A follower in the same region is preferentially selected as the migration source. If only a leader is available, the leader is selected.
data_disk_write_limit_percentage New The data disk usage that triggers an error for user write requests. When the specified value is reached, you can drop tables to urgently release storage space to avoid cluster faults. It is a cluster-level parameter. The value of this parameter must be greater than that of data_disk_usage_limit_percentage. We recommend that you set this parameter to the value of the following formula: (1 - memstore_limit_size/data_disk_size) × 100%. The default value is 0, which specifies not to stop user write requests.
alert_log_level New The log level of the alert.log file. Valid values are INFO, WARN, and ERROR. The default value is INFO. It is a cluster-level parameter.
syslog_disk_size New The total disk space available for system logs. It is a cluster-level parameter. The default value is 0M, indicating that the entire disk is available for system logs, which is the same as in earlier versions.
syslog_compress_func New The compression algorithm for system log files. Valid values are none, zlib_1.0, zstd_1.0, and zstd_1.3.8. It is a cluster-level parameter. The default value is none, which specifies not to compress system log files.
syslog_file_uncompressed_count New The number of system log files that triggers log compression for a specific log type. It is a cluster-level parameter. The default value is 0.
enable_global_background_resource_isolation New Specifies whether to perform global CPU resource isolation for foreground and background tasks. It is a cluster-level parameter. The default value is False, indicating no isolation.
global_background_cpu_quota New The CPU quota available for background tasks when enable_global_background_resource_isolation is set to True. It is a cluster-level parameter. The default value is -1, which indicates that the CPU resources available for background tasks are not subject to cgroups.
net_thread_count Modified The number of network I/O threads. The default value is still 0 but the adaptation strategy changes. The more the CPU cores, the more the available threads.
ddl_high_thread_score New The number of DAG threads available for the major compaction of KV data of each tablet during DDL data completion. It is a tenant-level parameter. The default value is 6, which is consistent with that in earlier versions.
lob_enable_block_cache_threshold New If the size of LOB data to be read from OUTROW storage is smaller than or equal to the specified value, the system caches the microblock in which the LOB data is located to speed up the next query. It is a tenant-level parameter. The default value is 256K.
ob_default_lob_inrow_threshold Modified The maximum LOB size supported by INROW storage. The default value is changed from 4096 to 8192.

System variable changes

System variable Change type Description
activate_all_roles_on_login New Specifies whether to activate all roles upon user logon. It is a global system variable applicable to MySQL tenants.
ob_compatibility_control New The MySQL version with which OceanBase Database keeps compatible, when a product behavior conflict occurs. Valid values are MYSQL5.7 and MYSQL8.0. It is a global system variable applicable to MySQL tenants. The default value is MYSQL5.7. This system variable is set during tenant creation and cannot be modified after tenant creation.
ob_compatibility_version New The OceanBase Database version whose product behavior takes effect after normal product behavioral changes. It is a global system variable that controls product behavioral changes. The default value is the current cluster version for a new cluster, and is the previous version for an upgraded cluster. In V4.2.3, the default value is 4.2.1.0. You can modify the value.
ob_security_version New The OceanBase Database version whose product behavior takes effect after security product behavioral changes. It is a global system variable that controls product behavioral changes. The default value is the current cluster version for a new cluster, and is the previous version for an upgraded cluster. In V4.2.3, the default value is 4.2.1.0. You can change the value of this variable only to a later version, but the limitation does not apply to the ob_compatibility_version variable.
cardinality_estimation_model New The correlation model used by the optimizer for row estimation. It can take effect both at the global and session level. Valid values are INDEPENDENT, PARTIAL, and FULL.
  • INDEPENDENT: This model assumes that predicates are completely independent of each other. Such assumption is used by optimizers of V4.2.2_CE and earlier. The joint selectivity of multiple filters is the product of multiplying the selectivity of each of the filters. When many filters are involved, the row estimate is small.
  • PARTIAL: This model assumes that predicates are partially correlated. The joint selectivity of multiple filters is calculated through exponential backoff.
  • FULL: This model assumes that predicates are completely correlated. The joint selectivity of multiple filters is subject to the smallest selectivity of a single filter. The row estimate obtained by using this model is high, which is extreme.
The default value is PARTIAL.

Function/System package changes

Function/System package Change type Description
DECODE_TRACE_ID New Parses the trace_id field to obtain the IP address and port number of the OBServer node that initiates the SQL request.
CURRENT_ROLE New Shows the roles activated in the current session.
PASSWORD New Calculates and returns the password hash. Password expressions of MySQL 5.7 are supported.

Syntax changes

Syntax Description
Statements are provided for specifying a set- or piece-level restore source. You can view the backup set or piece required for restore to a specific timestamp or SCN and specify the path of a backup set or piece to restore to the specific timestamp or SCN:
  1. Statement for specifying restore source paths: ALTER SYSTEM ADD RESTORE SOURCE 'xxx';
  2. Statement for revoking an incorrect input of restore source paths: ALTER SYSTEM CLEAR RESTORE SOURCE;
  3. Statement for initiating a restore: ALTER SYSTEM RESTORE <$restore_tenant> UNTIL '<$restore_checkpoint>' WITH 'xxx';
  4. Statement for parsing the original path of the backup set or piece required for restoring to the specified timestamp or SCN: ALTER SYSTEM RESTORE FROM 'uri' UNTIL { TIME='timestamp' | SCN=scn } PREVIEW;
  5. Statement for previewing the original path of the backup set or piece required for restoring to the expected timestamp or SCN: SHOW RESTORE PREVIEW;
Statements are provided for LS replica management.
  1. Statement for adding an LS replica
  2. Statement for dropping an LS replica
  3. Statement for converting the type of an LS replica
  4. Statement for migrating an LS replica
  5. Statement for modifying the number of Paxos members of an LS replica
  6. Statement for canceling an LS replica task
The table-level option auto_increment_cache_size is provided. You can specify the table-level option auto_increment_cache_size in a CREATE TABLE or ALTER TABLE statement. For example, in create table t1 (...) auto_increment_cache_size=xxx; or alter table t1 set auto_increment_cache_size=xxx;, the default value of auto_increment_cache_size is 0, which means that no cache size is configured for auto-increment columns. In this case, the value of the tenant-level parameter is used as the cache size for auto-increment columns.
The table-level option table_mode is provided. The table-level option table_mode once supported in V3.x is used again in V4.2.3. You can set different table_mode values for tables to specify fast freeze and adaptive major compaction strategies triggered at different frequencies to resolve the buffer table issue. Here is an example:
create table t1 (c1 int) table_mode = 'normal/queuing/moderate/super/extreme';
Hints are provided for specifying whether to enable parallel write at the DAS layer for DML statements. The enable_parallel_das_dml and disable_parallel_das_dml hints are provided for respectively enabling and disabling parallel write at the DAS layer for DML statements. When the tenant-level hidden parameter _enable_parallel_das_dml is set to True:
  1. Scenario 1: The hint combination /+enable_parallel_das_dml enable_parallel_dml parallel(n)/ forcibly enables parallel write at the DAS layer for DML statements and adopts the DOP specified by parallel.
  2. Scenario 2: The hint combination /+enable_parallel_dml parallel(n)/ enables PDML preferentially. If PDML is not supported, parallel write at the DAS layer is enabled.
  3. Scenario 3: When a DOP is forcibly specified for a session, the behavior is the same as that in Scenario 2.
Role-related syntaxes are provided.
  1. Syntax for creating roles: CREATE ROLE [IF NOT EXISTS] role [, role ] ...
  2. Syntax for dropping roles: DROP ROLE [IF EXISTS] role [, role ] ...
  3. Syntax for granting roles to users: GRANT role [, role] ... TO user_or_role [, user_or_role] ... [WITH ADMIN OPTION]
  4. Syntax for revoking roles from users: REVOKE [IF EXISTS] role [, role ] ... FROM user_or_role [, user_or_role ] ... [IGNORE UNKNOWN USER]
  5. Syntax for setting the roles enabled by default upon user logon:
    • SET DEFAULT ROLE {NONE &#124; ALL &#124; role [, role ] ...} TO user [, user ] ...
    • ALTER USER [IF EXISTS] user DEFAULT ROLE {NONE &#124; ALL &#124; role [, role ] ...}
  6. Syntax for setting the roles to be activated for the current session: SET ROLE { DEFAULT &#124; NONE &#124; ALL &#124; ALL EXCEPT role [, role ] ... &#124; role [, role ] ...}
  7. Syntax for showing the roles granted to a user: SHOW GRANTS FOR user USING role
Syntaxes are provided for granting and revoking column-level privileges.
  1. Syntax for granting column-level privileges: GRANT [priv [(col_list)] ]+ ON [db_name.]table_name TO user [with grant option];
  2. Syntax for revoking column-level privileges: REVOKE [priv [(col_list)] ]+ ON [db_name.]table_name FROM user [with grant option];
Statements are provided for changing the replicated table attribute.
  1. Statement for converting a normal table into a replicated table: ALTER TABLE t1 DUPLICATE_SCOPE = 'CLUSTER';
  2. Statement for converting a replicated table into a normal table: ALTER TABLE t1 DUPLICATE_SCOPE = 'NONE';

Recommended versions of tools

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

Tool Version
ODP V4.2.3
OCP V4.2.2_CE_HF1
OBD V2.8.0
All in One V4.2.3
ODC V4.2.4-bp1
OBCDC V4.2.3
OMS V4.2.3_CE
OBClient V2.2.3
LibOBClient V2.2.3

Upgrade notes

  • You can upgrade OceanBase Database V4.2.1_CE_BP2 and earlier V4.x_CE versions to V4.2.3_CE_Beta online through a valid upgrade path. You cannot upgrade OceanBase Database V4.2.1_CE_BP3 and later BP versions to V4.2.3_CE_Beta.
  • You can upgrade OceanBase Database V4.2.2_CE to V4.2.2_CE_BP1 first and then to V4.2.3_CE_Beta online.
  • When you upgrade OceanBase Database V4.2.1_CE and V4.2.2_CE to V4.2.3_CE_Beta, you must set the system variable _nlj_batching_enabled to False. You can set the variable to True after the upgrade.
  • If your cluster contains a large number of tablets, it takes much time to restart OBServer nodes during the upgrade. It takes about 20 minutes to restart OBServer nodes when the cluster contains more than 3 million tablets. If a large number of tablets are involved, you need to reserve sufficient time for the upgrade.
  • We recommend that you upgrade OBServer nodes first and then ODP.
  • Major compactions and DDL operations are prohibited during the upgrade and will resume normal after the upgrade.

Acknowledgments

In the release of this version, we want to extend our acknowledgments to Qiu Yonggang @qiuyg3 from the China Unicom Software Research Institute team for his contributions to the oblogminer feature.

版本信息

项目 描述
发布日期 2024-4-29
版本号 V4.2.3_CE_BETA
Commit 号 c129d71
OBServer RPM 版本号 oceanbase-ce-4.2.3.0-100000112024042411

版本定位

V4.2.3_CE_BETA 为 V4.2.x_CE 系列最新版本，支持了一系列 MySQL 兼容特性，如角色、列级权限、Union Distinct RCTE 及大量小功能兼容。在多个场景优化了系统性能，如优化多模类型读写计算和 OBKV 处理效率，扩展 Batch DML 的并行执行能力，提升备份/网络备库性能，解决自增列/Sequence 在 Order 模式下的性能问题等。同时，备份恢复扩展支持了 S3 及兼容 S3 协议的对象存储（如：OBS，GCS）作为备份目的端。在资源优化方面，进行了一系列优化包括 DDL 临时结果空间优化、旁路导入能力提升和全局前后台 CPU 资源隔离等。新版本也实现了多个业务期待的易用性功能，如用于日志分析和误操作识别的 ObLogMiner 功能，应对 Buffer 表性能问题的合并策略选择，提供模糊绑定计划和限流方式的 Format Outline，用于识别无用索引的索引使用监控特性，提供更高可读性的面向数据库管理员的 alert.log 系统日志，增加日志流副本管理的用户命令，提供物理恢复进度展示能力。更详细的 PX 诊断数据以及 ASH Report 的节点级分析等，有效提高系统易用性。

推荐用于测试，暂不建议用于生产。

关键特性说明

内核增强

  • 查询解析能力增强

    SQL 中存在个数特别多的 INLIST 、AND/OR、UNION 时，往往会产生超深语法树解析，进而造成内存消耗过大、栈不足、耗时过长的问题。新版本重写这些极端场景的查询解析实现，降低了解析阶段资源消耗，增强了极端 SQL 执行的稳定性，同时提升了 SQL 解析性能。例如 select sum(c1) from t1 where c1 in (1, 2, 3, ...N) 中 N = 20 万的查询场景，V4.2.3_CE 版本的解析性能相对 V4.2.2_CE 版本可以提升 20%~30%。

  • 查询改写能力增强

    V4.2.3_CE 版本优化器从多个方面优化了查询改写逻辑，例如：

    • 表达式规范化强化：历史版本在 Resolver 解析阶段会对多种表达式进行规范化（Canonicalize），例如将 A and (A or B) 改写为 A ，但未覆盖到 SQL 改写阶段新产生的不规范表达式。新版本引入改写阶段的表达式规范化流程，确保将表达式改写到最简化状态。

    • 条件聚合函数改写：我们将形如 select c1, sum(case when c2 = 0 then c3 else 0 end),sum(case when...)...,... from t1 group by c1, 聚合目标不是某个确定的表达式，而是在运行时根据分支选择条件的判定结果选择出的表达式，称为条件聚合函数。新版本支持了条件聚合函数改写，支持将聚合函数压入每个分支的选择目标上，减少 case when 表达式和聚合函数的计算次数，提升相关场景的执行性能。

    • 多 Min/Max 改写：在 scalar group by 中，若查询中的聚合函数只有 Min/Max，且 Min/Max 的列上有索引，可以将其改写成 order by xxx limit 1，利用索引序消除 Order By 并将 Limit 1 下推到 TABLE SCAN 上，从而减少对数据的读取和排序。对于 SQL 中有多个 Min/Max 存在的场景，历史版本采取了全表扫描方案，新版本考虑将多个 Min/Max 改写成多个子查询，每个子查询都利用索引直接获取到最大值或最小值，以此来避免全表扫描和排序，优化查询性能。

    • 常量 UNION 改写 Values Table：业务较常使用多个 UNION ALL 的组合形式表示一个常量表，在子查询数量过多时，会有较多 CPU 和内存消耗。新版本对常量类型的 UNION ALL/UNION 改写成对 Values Table 的查询，显著降低硬解析阶段的资源消耗。

    • SEMI JOIN 拆分优化：EXISTS 或 IN 子查询中存在多表关联且没有连接条件时，执行计划可能会出现开销较大的笛卡尔积。新版本支持对没有连接条件的表做 SEMI JOIN 拆分改写，避免笛卡尔积开销。

  • 计划选择优化

    V4.2.2_CE 版本实现了新版 Query Range 抽取逻辑，扩展了谓词下压场景，使向量形式谓词的 Range 抽取更加精准，也解决了历史版本 Query Range 的一些内存放大的场景问题。V4.2.3_CE 版本在此基础上修改了 not in 表达式生成 Query Range 的方式，优化了 Range 抽取性能；支持 Range Graph 裁剪，减少最终 Query Range 的抽取数量，降低内存消耗；扩展 INDEX Hint，如指定 /*+index(t1 k1 1)*/ 时，限制只对 k1 索引的第 1 列进行 Query Range 抽取。同时，新版本也支持了 Interesting Ordering 索引路径按规则裁剪，Limit 重计算等策略优化，解决 order by limit 场景因计划选择不优导致的性能问题。

  • 自适应代价模型

    OceanBase 数据库历史版本代价模型是使用内部机器测算的常量参数来代表硬件系统统计信息，通过一系列公式与常量参数来描述每个算子的执行开销。而真实的业务场景中，不同硬件环境可能具备不同的 CPU 时钟频率、不同的顺序读或随机读的速度、不同的网卡带宽等，可能存在代价估算偏差，这些偏差会使得优化器无法在不同的业务环境总是生成最优计划。新版本优化代价模型实现，支持通过 DBMS_STATS 包来收集或设置系统统计信息系数，以达到代价模型自适应硬件的目的。同时也提供了 DBA_OB_AUX_STATISTICS 视图，用于展示当前租户的系统统计信息系数。

  • 复制表属性变更

    复制表是 OceanBase 数据库 V4.2.0 版本开始支持的一种特殊表。这种表可以在任意一个“健康”的副本上读取到数据的最新修改。如果需要转换复制表为普通表，或转换普通表为复制表，V4.2.3_CE 版本之前需要重新建表导数，操作较耗时和复杂。新版本提供复制表属性变更功能，可通过 ALTER TABLE 命令实现表属性转换，降低用户操作成本。

  • 产品行为兼容版本控制

    为了减少因为新版本行为变更导致升级后某些老版本特性使用报错的问题，OceanBase 数据库 V4.2.3_CE 版本新增产品行为兼容版本控制功能，支持通过 ob_compatibility_version、ob_security_version 系统变量，分别控制普通行为变更（不报错 -> 报错场景）、安全行为变更（有权限 -> 无权限）是否生效，一个租户中的兼容功能或安全功能按照哪个 OceanBase 版本来兼容，取值范围为 "4.2.3.0" 等发行版本。例如一个功能在 V4.2.4 版本发生了产品行为变更，当变量取值为 4.2.3.0 时，使用旧行为；取值为 4.2.4.0 时，使用新行为。通常升级场景不会自动推高该版本号，需要新版本行为时，请升级后，了解新版本行为并测试后，修改为和当前版本一致的版本号。该方案仅用于控制未来新增的产品行为变更，无法控制已经发版的存量产品行为变更。

    V4.2.3_CE 版本通过 ob_compatibility_version 控制的产品行为有：

    • ob_compatibility_control = MYSQL5.7 时，REPLACE('abd', '', null) 行为由兼容 MySQL 8.0 修改为兼容 MySQL 5.7。
    • UPDATE/DELETE statement 在 Limit 中禁用 Offset。
    • 当投影项是单独的一个 null 值的时候，返回的表头会被修改为 NULL。
    • 限制用户变量最长 64 字符。

    通过 ob_security_version 控制的产品行为有：

    • Outline、Sequence 增加权限管控。
    • CREATE TABLESPACE 权限。

MySQL 兼容

  • 角色管理

    OceanBase 数据库 V4.2.3_CE 版本兼容了 MySQL 8.0 的角色管理功能，通过角色来管理维护一组权限，可以更方便地对某一类用户进行授权和回收。与普通用户类似，角色可以被授予或回收权限，也可以被授予或回收其他角色。用户可以被授予多个角色，但仅能使用激活状态角色中的权限。有关角色管理的更多信息，参见 角色管理。

  • 列级权限

    V4.2.3_CE 版本新增 MySQL 列级权限功能，可以用于控制用户是否有权限对某张表的某几列进行 SELECT、INSERT 或 UPDATE。

  • Outline、Sequence 权限管控

    历史版本缺少对创建维护 Outline、Sequence 的权限管控，新版本复用 MySQL CREATE、ALTER、DROP 权限，控制 Outline、Sequence 的创建、修改和删除。

  • 自增列切主跳变优化

    OceanBase 数据库在 V4.x 版本支持了创建 ORDER 模式的自增列，更好地兼容了 MySQL 自增列行为。但是 V4.2.3_CE 之前版本在出现切主时，仍会造成自增列跳变。考虑到很多切主场景是用户主动发起的流程，并非异常场景，所以新版本优化为主动切主时仍保持自增列连续性，降低自增列跳变概率。有关自增列的更多信息，参见 自增列。

  • 自增列改小

    V4.2.3_CE 之前的版本，仅支持通过 ALTER TABLE 将 AUTO_INCREMENT 值改大，不支持改小，行为上和 MySQL 不完全兼容。新版本补充支持了 AUTO_INCREMENT 值改小的功能，指定新值大于自增列已存在的最大值时，可以设置成功且生效；指定新值小于或等于自增列已存在的最大值时，也可以设置成功，但 AUTO_INCREMENT 会自动调整为自增列已存在的最大值的下一个值。

  • Union Distinct RCTE

    MySQL 8.0 支持了 CTE 的 Recursive Union All 及 Recursive Union Distinct 功能。OceanBase 数据库从 V3.2.3 版本开始支持 MySQL 模式的 Recursive Union，但仅支持 Recursive Union All，V4.2.3_CE 版本扩展兼容了的 MySQL Recursive Union Distinct 功能，保证输出数据的唯一性。同时，新版本还加强了 Recursive Union All 功能，支持可使用内存不足时进行数据落盘。有关 Union Distinct RCTE 的更多信息，参见 通用表表达式。

  • 区分 MySQL 5.7/8.0 兼容版本

    MySQL 5.7 与 8.0 在部分场景下存在产品行为冲突，如 replace('a','',null) 在两个版本的输出结果不同。OceanBase 数据库 V4.2.3_CE 版本新增租户级初始化变量 ob_compatibility_control，用于在创建租户时指定存在产品行为冲突的情况是兼容 MySQL 5.7 版本，还是 8.0 版本。租户创建后不可修改，两种模式下均可使用不存在行为冲突的 MySQL 5.7/8.0 的特性超集。

  • 语法/变量/视图支持

    为了支持 MySQL 生态的各类工具或框架无需修改即可平滑迁移到 OceanBase 数据库，V4.2.3_CE 及之后版本会针对 MySQL 5.7 全量功能，对 OceanBase 数据库还未兼容细节的部分、不适用的能力，逐步进行兼容或 Mock 处理。

    V4.2.3_CE 版本支持的兼容能力如下：

    • 支持数据类型 SERIAL。
    • [MySQL 8.0] 支持创建触发器时指定 IF NOT EXISTS 语法。
    • SQL 语句中支持使用 \N 代表 NULL。
    • 兼容 MySQL，update/delete ... limit 语句禁用 offset 子句。
    • 兼容 MySQL 5.7 password 函数。
    • 兼容 MySQL DROP USER IF EXISTS 语法。
    • 兼容 MySQL，用户变量标识符长度限制 64 个字符。
    • 兼容 CREATE TABLE ... [IGNORE | REPLACE] SELECT statement。
    • SELECT 语句支持 STRAIGHT_JOIN 。
    • 支持 MySQL CREATE TABLESPACE 权限。
    • 支持授予、回收、查看 MySQL SHUTDOWN、RELOAD 权限，实际不生效。
    • DML 忽略 PRIORITY 不报错，不生效。
    • INSERT 忽略 LOW_PRIORITY、 HIGH_PRIORITY 不报错，不生效。
    • UPDATE 忽略 LOW_PRIORITY 不报错，不生效。
    • DELETE 忽略 LOW_PRIORITY、QUICK 不报错，不生效。
    • REPLACE 忽略 LOW_PRIORITY 不报错，不生效。
    • SELECT 忽略 SQL_SMALL_RESULT、SQL_BIG_RESULT、SQL_BUFFER_RESULT、HIGH_PRIORITY 不报错，不生效。
    • 兼容 MySQL 5.7，忽略降序索引语法不报错，不生效。
    • 兼容 MySQL 5.7，忽略 CREATE TABLE 中 Column 后的 reference_definition 语法，不报错，不生效。
    • 忽略建表建索引指定的 KEY_BLOCK_SIZE option，不报错，不生效。
    • FLUSH PRIVILEGES：语法不报错，实际不适用。
    • Show Profile/Profiles：语法不报错，实际不生效。
    • SHOW FUNCTION/PROCEDURE CODE：语法不报错，实际功能未支持。
    • 支持 INFORMATION_SCHEMA.PROFILING 表结构。
    • 新增 MySQL 变量（debug、debug_sync、innodb_change_buffering_debug、innodb_compress_debug、innodb_disable_resize_buffer_pool_debug、innodb_fil_make_page_dirty_debug、innodb_limit_optimistic_insert_debug、innodb_merge_threshold_set_all_debug、innodb_saved_page_number_debug、innodb_trx_purge_view_update_only_debug、innodb_trx_rseg_n_slots_debug、stored_program_cache、profiling、profiling_history_size、innodb_stats_persistent）。变量不产生实际效果，可查询可设置。设置后不会报错，也不会生效，产生 Warning。
    • 兼容 MySQL 5.7 通信协议 COM_PROCESS_INFO、COM_PROCESS_KILL；支持通信协议 COM_REFRESH、COM_DEBUG，实际不生效。

多模特性

  • MySQL GIS

    OceanBase 数据库 V4.1.0 版本开始支持 GIS 数据类型及部分空间对象相关的表达式，后续逐渐增加了空间数据存储和计算分析的能力。新版本 MySQL 模式下扩展支持 PostGis_ST_GeoHash 表达式，用于计算 Geometry 的 Geohash 编码；扩展 PostGis_ST_MAKEPOINT 表达式，用于根据坐标创建 2D 和 3D 的 Geometry Point；新增支持 MySQL ST_ASGEOJSON 表达式，用于将 Geometry（WKB）按一定的格式转成 Json（Binary）。

  • JSON/XML/GIS 内存优化

    新版本对 JSON 表达式执行过程中内存占用进行了优化，对 JSON 类型进行查询操作（JSON_KEYS、JSON_LENGTH、JSON_SEARCH 等）时，内存占用减少至 1 倍；对 JSON 类型进行输出操作（JSON_PRETTY、JSON_UNQUOTE 等）时，内存占用减少至 3 倍左右。在插入更新 XML 和输出 XML 的场景也进行了内存优化。输出 XML 操作（GetClobVal、XmlCast 等）内存占用减少到 2~3 倍左右；插入更新 XML （UpdateXml、InsertChildXml 等）时减少了不必要的解析。

    新版本同时对 GIS 类型数据输入、输出以及部分查询场景进行了内存优化。GIS 输入（st_geomfromtext/st_geomfromwkb/st_geogfromtext/geometrycollection 等）内存占用减少到 3 倍左右；GIS 输出（st_astext/st_aswkb 等）内存占用减少到 1 倍左右；查询场景（st_crosses/st_overlaps/_st_touches 等空间关系查询表达式、st_geometrytype/st_iscollection 等 GIS 属性获取表达式）也明显降低了内存放大程度。

  • GIS 空间关系计算性能优化

    新版本优化了 ST_INTERSECTS/ST_CONTAINS/_ST_COVERS/ST_WITHIN 等空间关系计算表达式的计算性能。

    • 在全量查询窗查询场景，Point 的 st_intersects 与 PG 持平，且略优于 PG；其余测试场景的平均 RT 均不到 PG 的一半，其中 Linestring 的 Intersect 用时只有 PG 的 1/5 ，相比 OceanBase 数据库历史版本均有数量级的提升。

    • 在大查询窗场景，所有场景的平均 RT 均明显优于 PG，其中 Linestring 的 Intersect 用时仅有 PG 的 1/7，且相比 OceanBase 数据库历史版本均有数量级的提升。

    • 在小查询窗场景，Contain 计算会优于 PG，其中 Point 的 Contain 用时是 PG 的 1/4 左右，Linestring Contain 是 PG 的 80%。但 Intersect 的计算性能在 Linestring 场景仅与 PG 持平，在 POINT 场景用时是 PG 的 2 倍左右。

    有关空间函数的更多信息，参见 空间函数。

  • OUTROW 存储的 LOB 读写性能优化

    V4.2.3_CE 版本重点对 OUTROW 存储的 LOB 进行了性能优化。

    • Table Scan 场景，相对历史版本，新版本中小 LOB 性能可以提升近 10 倍，大 LOB 可以提升至少 2 倍。但该场景相对 INROW 仍然慢一些。
    • Point Select 场景，新版本小 LOB (8K 以下)，OUTROW 的 QPS 比 INROW 低 20% 以内，中等以上大小的 LOB（32K 以上），OUTROW 的 QPS 比 INROW 低 5% 左右。在 LOB 不参与计算的场景，LOB OUTROW 存储对查询性能会更优。
    • Point Update 场景，相对历史版本，新版本 OUTROW 性能平均提升 2 倍。

    有关 OUTROW 存储的 LOB 的更多信息，参见 lob_enable_block_cache_threshold。

OBKV 增强

  • OBKV 全局索引

    V4.1.0 版本开始，OBKV 支持了本地索引。新版本增加支持 OBKV 的全局索引功能，Insert、Update、Delete、insert_or_update、Replace、Increment/Append 等 DML 操作均适用于全局索引表，并支持了 Query 、异步 Query 指定全局索引查询。

  • OBKV 性能优化

    OBKV 是一款集成在 OceanBase 数据库内部的低时延、高吞吐的 KV 存储类产品，在不需要通过 SQL 进行复杂逻辑处理的场景，可以跳过 SQL 模块直接调用存储接口，以获得极致性能。通过业务及内部端到端的测试分析，发现历史版本 KV 模型层性能开销还有进一步降低空间。因此新版本在以下多个场景，进行了性能优化：

    • 新增 Put 功能：Put 用于覆盖写场景，覆盖写跳过了主键冲突检查路径，强制覆盖旧记录。

    • 支持组提交：组提交是指服务端主动攒批，执行批量操作的功能。开启组提交后，服务端会将 Put 或 Get 操作按照执行计划分组，并根据负载情况自动调节组大小，按组执行。用户可通过租户级配置项 enable_kv_group_commit 开启组提交，开启后预期能够在不对 RT 造成明显影响情况下，提高 30%~50% 的 OPS。

    • 优化 Batch 批处理：OBKV 的批操作接口避免了单操作接口中每次事务开启关闭的耗时，在一定程度上提高了操作效率。但每个操作依然需要构造算子、打开算子、提交 DAS 任务、关闭算子和算子析构等步骤，存在一定的执行时间损耗。为了进一步提高批操作接口的执行效率，新版本在算子级别实现了多操作的批处理，减少 n-1 次的算子构造析构和 DAS 任务开销，有效提升整体性能。

    • 优化 Schema 获取逻辑：内部进行 Schema 获取的操作调整为语句级别，仅在语句执行时获取一次 Simple Schema，避免频繁获取 Schema 带来的性能损耗。同时将 Schema 的一些高频获取信息如 table_id、column_id 等缓存到计划中，实现了轻量级的 KV Schema Cache。

    • Batch 只返回一个结果：Multi Set 场景下，历史版本针对每个操作都返回了一个结果，新版本在设置 batchOps.setAtomicOperation(true) 和 batchOps.setReturnOneResult(true) 后，Multi Insert/Multi Put/Multi Delete 操作优化为只返回一个成功或者失败的结果，缩短交互周期。

性能提升

  • DML 性能优化

    OceanBase 数据库已经支持通过 PDML 的并行执行机制来提高对大型表和索引执行插入、更新、删除等操作的性能。但是仍然存在部分场景是 PDML 暂未覆盖的，如 replace into ...、insert ... on duplicate key、insert all ...、merge into ... 更新主键、Insert 包含自增列、多表 Update 等。以上场景在之前版本会使用单线程写入方式，在数据量较大时响应时间较长，影响客户体验。为了解决大数据量的这些场景的性能瓶颈，V4.2.3_CE 版本新增支持 DAS 层并发写入能力，通过类似 PDML 的并发控制机制，显著提升 DML 执行性能。更多信息，参见 与并行执行相关的 Hint。

  • 备份性能优化

    OceanBase 数据库备份过程中通过连续性校验确保基线版本大于转储版本以保证数据完整，但连续性检查涉及读取备份介质上的数据并执行带锁操作，影响了备份性能。新版本优化了备份过程中连续性校验操作的性能开销，支持通过 ha_low_thread_score 控制备份使用的线程数， 有效提高备份性能。更多信息，参见 数据备份。

  • 网络备库性能优化

    V4.2.3_CE 版本优化了 RPC 及网络框架，降低了主备库之间的日志传输耗时；同时优化了备库受控拉日志流程，减少了备租户拉日志受控的频率，提高了主备之间的日志同步性能。

  • 存储过程 DDL 执行编译结果缓存和落盘

    V4.2.2_CE 版本新增执行期存储过程编译落盘功能，解决了执行期存储过程获取 PL Cache 缓存失败，从而重编译引起的性能问题。但是存储过程自身做过 DDL 变更后，后续执行存储过程依旧需要重新编译，这个场景依然存在性能优化空间。V4.2.3_CE 版本补充支持存储过程 DDL 执行成功后将编译结果缓存到 PL Cache 并落盘的行为，后续执行存储过程时，提升直接命中 PL Cache 缓存的概率，进一步提高存储过程执行性能。

  • 并行 DDL 扩展

    并行 DDL 与串行 DDL 互斥，在并行 DDL 与串行 DDL 交替执行时性能不优。随着版本演进，OceanBase 数据库也在不断扩展允许并行执行的 DDL 类型。V4.1.0 版本实现了并行 DDL 框架，并支持了 TRUNCATE 语句的并行执行；V4.2.1 版本支持了并行 CREATE TABLE；V4.2.2_CE 版本支持了并行 COMMENT 与并行 CREATE INDEX。在此基础上，V4.2.3_CE 版本扩展支持并行 CREATE VIEW，进一步提升 OMS 的结构迁移速度。

  • 自增列 Order 模式性能优化

    自增列 Order 模式下，需要保持数据插入的连续性。为了保证分布式架构下自增列的有序，在自增列 INSERT 时会将该值持久化到内部表，高并发情况下，响应时间会比较长。V4.2.3_CE 对该场景做了优化，通过 Cache Size 预取有效降低内部表访问次数，从而显著提升性能。

  • Sequence Order 模式性能优化

    OceanBase 数据库已支持指定 order + cache 属性来创建全局连续生成的序列，但早期的实现中会忽略 Cache 属性，等同于 order + nocache，在高并发场景会有明显性能问题。V4.2.3_CE 版本对该场景进行了优化，通过选取中心节点来支持 order + cache 生成连续序列，真正意义上实现 Cache 属性，显著提升高并发场景执行性能。

  • Show Table Status 性能优化

    历史版本 show table status from ... like ... 场景性能较差，未利用 table_name 相关索引。新版本针对存在单表过滤条件的场景，显著提升查询性能。

  • CREATE 语句支持 HINT

    在 V4.2.3_CE 版本中，新增对 CREATE TABLE 语句的 Hint 支持 /*+ parallel(N) */ 类型。该 Hint 适用于 CREATE TABLE ... AS SELECT ... 的场景，并可以控制在表创建时数据查询和写入操作的并行度。

资源优化

  • OBServer 到 ODP 的链路压缩功能

    实际业务场景中，存在应用和数据库服务跨城部署的情况，距离较远、带宽有限，但仍然有大数据量读写的需求。为了降低大数据量传输带来的带宽消耗，该版本支持了 OBServer 到 ODP 的链路压缩功能，需配合 ODP V4.2.3_CE 或以上版本开启使用。

  • DDL 临时结果空间优化

    在 DDL 过程中，常常会将临时结果暂存到物化结构中。在创建索引时，需要对数据表进行扫描并将数据插入到索引表中，此过程中可能需要对扫描出来的数据进行排序。如果内存空间不足，就会将当前内存中的数据暂存到物化结构中，以释放内存供后续的扫描使用。最后，对这些暂存在物化结构中的数据进行归并排序。

    针对这些问题，新版本 DDL 处理流程对数据流进行了优化。首先，它剔除了不必要的冗余结构，简化了数据流；其次，它引入了对临时结果落盘的编码压缩功能。这些改进为两个场景都带来了好处：它们显著降低了 DDL 操作期间临时结果对磁盘空间的占用，从而更加高效地利用存储资源。

  • 旁路导入优化

    • 支持临时文件压缩：旁路导入过程中会产生临时文件，当导入的数据量比较大时，临时文件占用的磁盘空间比较大，需要关注磁盘的占用空间，避免旁路导入产生的临时文件将磁盘空间打爆，当旁路导入的 并发度 >= 8 时，系统会对产生临时文件做压缩，减少临时文件的空间占用。
    • 支持通配符方式多文件导入：在旁路导入过程中，新增了对通配符方式的多文件导入的支持。可以使用通配符 * 和 ? 来同时导入匹配特定模式的多个文件，从而提高导入效率和便利性。

    有关旁路导入的更多信息，参见 旁路导入。

  • 全局 CPU 前后台任务隔离

    在高性能计算环境中，资源的合理分配与隔离对于确保系统稳定性和提升效率具有决定性作用。有效的资源隔离策略可以预防任务间的资源争夺和相互干扰，从而提升资源利用效率和整体服务质量。目前，OceanBase 数据库已实现了通过租户 Unit 规格来配置租户间的资源隔离，通过 DBMS_RESOURCE_MANAGER 系统包来配置租户内的资源隔离，涵盖 CPU 和 IO 两大重要资源。但对于不希望后台任务对前台请求造成严重 CPU 竞争的业务，在全局层面上对后台任务进行隔离是一个更好的选择。OceanBase 数据库 V4.2.3 版本支持了全局 CPU 前后台任务隔离能力，可以在整体层面上限制后台任务的可用资源，相对租户内使用 DBMS_RESOURCE_MANAGER 单独配置更加方便易用。有关全局 CPU 前后台任务隔离的更多信息，参见 配置 cgroup。

  • 分区均衡策略优化

    OceanBase 数据库 V4.2.0 版本开始支持分区均衡功能，但在部分场景下还存在不完全均衡的问题。V4.2.3_CE 版本优化了分区均衡策略，在建分区表时支持了连续分区打散；当用户表存在 Longtext/Lob 列、局部索引时，分区磁盘均衡时也会计算关联表，使得磁盘使用更加均衡。

  • 系统日志压缩

    业务流量过大时，系统日志刷新的会比较快，保留时间较短可能影响问题排查。新版本增加系统日志压缩功能，支持对 observer.log、rootservice.log、election.log、trace.log 等日志文件，每类超过 syslog_file_uncompressed_count 个数时，采用 syslog_compress_func 设置的压缩方法，对最早日志进行压缩。当日志使用总空间接近 syslog_disk_size 设置的磁盘空间上限时，开始删除最早生成的日志文件，回收空间。磁盘空间不变的情况下，开启 zstd 压缩后，预计可以存储不开启压缩时 20 倍的日志量。更多信息，参见 日志压缩与解压。

可靠性提升

  • 迁移复制源端选择优化

    历史版本选择迁移复制的源端时没有优先考虑同 Zone、同 IDC (同机房)、同 Region (同城)的副本，可能出现远距离迁移复制性能不优的问题。并且源端可能会选择 Leader 节点，业务请求量较高时，可能导致业务请求和迁移任务较慢的问题。V4.2.3 版本根据地域将迁移复制的目标端和源端副本所在Server的关系划分为：同 IDC、同 Region 不同 IDC、跨 Region 三种区域，提供枚举配置项 choose_migration_source_policy，支持用户配置迁移复制选择源端副本的优先模式，以便优先考虑地理位置就近因素以及 Follower 副本来提升迁移效率、降低 Leader 压力。

  • 数据盘满停写

    数据盘使用量过高时，当前不会停写用户请求，一直到内存因为无法转储写满，或者 Clog 盘写满后才会报错。这种情况下，需要通过紧急扩 Clog 盘或租户内存恢复。新版本在内核层面增加数据盘满停写功能，当用户数据盘水位线达到 data_disk_write_limit_percentage 配置后，用户写入请求会报错。通过删表、Transfer 或者扩磁盘方式降低数据盘使用比列后，用户写入操作可以自动恢复。

高可用增强

  • 备份恢复支持 S3/OBS/GCS

    OceanBase 数据库的备份恢复功能支持两类存储介质：文件存储（NFS）和对象存储（OSS/COS）。新版本备份恢复扩展支持了 S3 及兼容 S3 协议的对象存储（如：OBS，GCS）作为备份介质，可以将 S3 及兼容 S3 协议的对象存储作为日志归档和数据备份的目的端，也可以使用 S3 及兼容 S3 协议的对象存储上的备份数据执行物理恢复。更多信息，参见 物理备份与恢复。

  • SET/PIECE 级物理恢复

    实际业务中存在二次备份场景，会把数据备份集或归档日志手动搬迁到新的路径。OceanBase 数据库的物理恢复功能，限制了必须在租户的归档和备份路径中恢复，无法使用手动搬迁到新路径的备份数据，限制了恢复的灵活性。新版本增加了 SET/PIECE 级物理恢复功能，提供 add restore source 命令来加载新路径的数据备份集 SET 或日志归档 PIECE，允许按需恢复到指定时间。更多信息，参见 指定路径的恢复。

易用性提升

  • OceanBase LogMiner

    V4.2.3_CE 版本新增 OceanBase LogMiner（简称 ObLogMiner）工具支持。ObLogMiner 是一款用来对 OceanBase 数据库进行日志分析的命令行工具，支持在线及离线的日志分析。ObLogMiner 通过 OBCDC 来拉取并解析 CLOG 日志，并将 OBCDC 输出的逻辑日志转化成易读的格式存储到指定位置。适用于以下场景：

    • 数据误操作：数据误操作可能由于多种原因出现，比如 WHERE 子句中的范围条件错误而误删除或更新了多余的行。通过使用 ObLogMiner 可以准确了解误操作的详细信息，以便于将数据库恢复到误操作之前的状态。
    • 数据分析：ObLogMiner 会将 CLOG 日志中的各种信息(事务、表结构等) 组织并友好地展示出来，用户可以借助 ObLogMiner 的输出结果进行多样的数据分析。也可以配合外表功能读取 ObLogMiner 的输出结果在数据库中进行查询分析。

    更多信息，参见 LogMiner 工具。

  • Buffer 表自适应合并优化

    当用户在某张表上频繁地执行插入并且同时进行批量删除，或者有大量的并发更新操作时，可能会遇到一种现象：表中的数据行数并不大，但是查询和更新的性能出现明显下降。这种现象在 OceanBase 数据库中称为 Queuing 表(业务上有时又称 Buffer 表)效应。Buffer 表是 LSM-Tree 架构数据库都要面对的一类问题。LSM-Tree 架构下的删除操作在合并之前都只是逻辑上标记删除而非物理删除，当增量数据中存在大量标记删除的数据时，物理行数量将远多于逻辑行，从而造成严重的读放大现象，并影响优化器执行方案的生成。

    OceanBase 数据库在 V4.1.0 版本做到了自动识别哪些分区在一段时间内发生比较多更改，并对这些分区进行自适应 Compaction，但缺少人为控制手段。为了更灵活地解决 Buffer 表带来的性能下降问题，V4.2.3_CE 版本继续优化了 Buffer 表自适应合并特性，提供了 5 种档位的合并策略，允许用户根据业务场景为每张表设置不同的 table_mode，来应对 Buffer 表引起的读放大现象，从而提高系统长期运行下的 QPS 等性能指标。更多信息，参见 自适应合并。

  • Format Outline

    OceanBase 比较早的版本就提供了通过创建 Outline 来绑定执行计划或指定 SQL 限流的功能（以下称为 Normal Outline），该功能对 SQL 文本的格式要求较为严格，除了可以参数化的部分之外，多一个空格、制表符或大小写不一致等情况都无法命中 Outline，SQL 格式不固定的场景易用性较差。V4.2.3_CE 版本新增 Format Outline 特性，提供了一种更为宽松的匹配规则。当用户创建 Format Outline 时，在 Outline 原有流程之前，系统会先做一次忽略大小写、空格等非语法定义符号的操作，归一化为标准格式，这使得归一化后得到同样 Format SQL Text 或 Format SQL ID 的用户请求都可以命中同一个 Format Outline。另外针对 INLIST 个数不固定的场景，如业务不固定下发 in(1,2)、 in(4,5,6)、in(7,8,9,10) 时 ，Normal Outline 精准匹配功能需要绑定 in(?,?)、in(?,?,?)、in(?,?,?,?) 三个 Outline 来控制执行计划；新版本的 Format Outline 功能只需绑定一次，系统会归一化为 in(?)，使得 INLIST 个数不同的多个 SQL 可以共享一个 Outline。当 Normal Outline 与 Format Outline 同时存在时，优先匹配 Normal Outline。更多信息，参见 CREATE FORMAT OUTLINE。

  • 索引使用监控

    我们对数据库执行查询操作时，往往通过创建索引来优化查询性能。但随着数据表使用的时间增长，业务场景和操作人员不断增加，很可能会存在索引越建越多的问题。未使用的索引会浪费存储空间，也会加重 DML 操作的开销。对于这种情况，需要持续观察，删除无用的索引来给系统减负。但是仅靠人力很难识别哪些索引是无用索引，因此 OceanBase 数据库在 V4.2.3_CE 版本新增索引使用监控功能，用户可选择打开该功能并设置采样方式，在普通租户下会将符合规则的索引使用信息记录到内存，并以 15 分钟为周期刷新到内部表中，可通过 DBA_INDEX_USAGE 视图访问，以此来感知表上的索引是否有被引用，进而选择删除无用的索引表来释放空间。更多信息，参见 监控索引。

  • 系统日志优化

    系统日志目录下，新增 ./alert/alert.log 日志文件，用于记录 DBA 关注的日志信息，初步解决 observer.log 日志量大、可读性差的问题。可通过 alert_log_level 集群级配置项设置日志级别，包括 INFO、WARN、ERROR 等。同时提供了系统外部表 sys_external_tbs.__all_external_alert_log_info 用于直接结构化查询 alert.log 日志信息。更多信息，参见 Alert 日志。

  • 日志流副本管理

    V4.0.0 之前的版本，OceanBase 数据库提供了分区副本管理的一系列运维命令，比如为分区添加一个副本、为分区删除一个副本、对分区的一个副本进行类型转换等。V4.x 系列在设计上使用日志流代替了分区的概念，对标低版本的分区运维命令，V4.2.3_CE 版本重新设计实现了日志流副本级别任务的运维方式，提供了一系列语法，用于支持日志流副本的添加、删除、副本类型转换、迁移、修改日志流 Paxos 成员数量和取消容灾任务等能力，满足客户手动运维日志流副本的需求。更多信息，参见 副本管理。

  • 物理恢复进度统计

    为了用户在使用物理恢复功能时可以了解恢复任务是否运行正常，获取恢复任务的进度情况，并预估恢复任务完成的剩余时间，在使用物理恢复功能时获得更好的体验，新版本增加物理恢复进度统计功能，可通过 CDB/DBA_OB_RESTORE_PROGRESS 视图随时查看恢复的实时进度。更多信息，参见 查看物理恢复进度。

  • PX 诊断能力增强

    为方便分布式计划的问题排查，V4.2.3_CE 版本在 SQL Plan Monitor 中增加记录了算子运行时的内存使用量、磁盘使用量，整个执行过程的最大内存使用量、最大磁盘使用量等信息。并在 SQL Audit 中增加记录 QC 对应 trace_id 下的 MemTable、SSTable 行数信息，并支持汇总各 PX Worker 对应 trace_id 下的相关信息。同时也支持了 OBDiag 工具依据 trace_id 拉取最初报错机器日志的功能。

  • PS 诊断能力增强

    V4.2.3_CE 之前的版本遇到 PS 句柄泄漏问题时，只能通过 GV$OB_PS_ITEM_INFO 视图查看全局信息，缺少 Session 级别的诊断方式。V4.2.3_CE 版本新增 [G]V$OB_SESSION_PS_INFO 系统视图，用于展示各个 Session 的 PS 引用信息，以便准确定位 PS 句柄泄漏等问题。

  • ASH Report 节点级分析

    ASH Report 已支持生成集群级别的 ASH 分析报告，缺少节点级统计。如果想要查看某个节点上 ASH 数据，目前需要通过 SQL 脚本获取，这给单节点问题分析造成了一些不方便。V4.2.3_CE 版本增加 ASH Report 节点级分析能力，可通过 DBMS_WORKLOAD_REPOSITORY.ASH_REPORT 包函数中新增的两个参数（节点 IP 和 Port），指定单节点的 ASH Report 分析。更多信息，参见 ASH Report。

  • 自增列 Cache Size 表级设定

    当前已支持通过指定租户级配置项 auto_increment_cache_size 来控制内存中自增列一次申请缓存的个数，对所有使用自增列的表生效。通常情况下，Cache Size 设置越大，性能会越好。但如果跳变次数很多，自增列字段取值上限又比较小，很可能导致自增列快速耗尽。V4.2.3_CE 版本新增自增列 Cache Size 表级设定功能，用户可以根据列类型/业务模型/业务流量等自定义配置不同表的缓存大小，从而做到跳变和性能的均衡。更多信息，参见 定义自增列。

  • NETWORK_WAIT_TIME 统计项

    SQL AUDIT 和 SQLSTAT 中新增 NETWORK_WAIT_TIME 统计项，用于展示 NETWORK 类别的等待事件耗时，如同步 RPC、DAS 异步 RPC 锁等。在排查远程执行的慢 SQL 问题时，可以使用该统计项确认网络开销，辅助诊断。

  • XA 事务监控统计项

    部分客户业务会采用 XA 协议来保证跨库事务提交的原子性。由于 OceanBase 数据库本身分布式架构特性，XA 语句的执行需要消耗一定的资源，该版本在 SYSSTAT 中新增 XA_TRANS_START_COUNT、XA_READ_ONLY_TRANS_TOTAL_COUNT、XA_ONE_PHASE_COMMIT_TOTAL_COUNT 等 30 余项统计项，便于在 XA 事务流量发生变化时，用户可以及时感知。更多信息，参见 监控项。

  • Crash 日志中 SQL 信息打印场景完善

    Crash 日志中的 SQL 信息一方面可以用于问题排查，另一方面还可以被 OCP Agent 采集后发出告警。V4.2.3_CE 版本前，INNER SQL 主线程、用户 SQL 主线程 Crash 的情况下，Crash 日志里会打印 SQL 信息。但 REMOTE 线程、PX SQC 线程、PX WORKER 线程、DAS 远端执行线程 Crash 的时候日志里缺少 SQL 信息打印。新版本针对以上场景，支持了执行端获取 sql_id 或 sql_string 信息，如果执行端出现 Crash，将会在 Crash Error 日志中输出控制端的 SQL 信息，便于 Crash 原因定位。

  • OBServer 支持 IPV6

    新版本支持 IPV6 格式的 Server IP，支持 SQL 客户端、RPC 客户端以 IPV6 地址连接，同时也支持了 IPV4 和 IPV6 节点的混合部署。支持 IPV4 集群升级，但不支持旧的 IPV4 类型的集群升级到 IPV6 类型。具体部署方式，参见 使用命令行部署单机集中式 OceanBase 数据库。

  • GV$PLAN_CACHE_PLAN_EXPLAIN 视图支持仅指定 PLAN_ID 查询

    查询 [G]V$PLAN_CACHE_PLAN_EXPLAIN 视图时，必须同时指定 ip + port + tenant_id + plan_id 四个过滤条件才会返回数据，仅指定 plan_id 时返回结果为空，易用性较差。新版本支持了该视图的底层虚拟表扫描，允许仅指定 plan_id 进行数据查询，可准确返回查询结果。

  • TRACE_ID 解析

    OceanBase 数据库通过 TRACE_ID 来标记 SQL 请求的全过程，用于关联监控指标或查询日志。TRACE_ID 包含了发起 SQL 请求的 OBServer 的 IP 和 Port 信息，但缺乏直接的解析方式。新版本增加 decode_trace_id 函数，用于解析 TRACE_ID 获取 IP、Port 相关信息。

兼容性变更

产品行为变更

新增如下变更：

功能 变更点说明
并行 COMMENT、并行 CREATE INDEX 升级后默认关闭 V4.2.2_CE 版本开始支持并行 COMMENT 和并行 CREATE INDEX 功能，默认开启。V4.2.3_CE 版本新建集群或租户时，也会默认开启并行 COMMENT 、并行 CREATE INDEX 以及 V4.2.3_CE 版本新增的并行 CREATE VIEW 功能。但 V4.2.2_CE 及之前的 V4.x 版本的租户升级到 V4.2.3_CE 版本后，以上三类并行 DDL 特性会变更为默认关闭状态，如有相关类型的 DDL 高性能需求，请通过设置隐藏配置项 _parallel_ddl_control 开启。例如，通过以下命令开启并行 COMMENT、并行 CREATE INDEX，关闭并行 CREATE VIEW 特性：alter system set_parallel_ddl_control='SET_COMMENT:ON,CREATE_INDEX:ON,CREATE_VIEW:OFF' tenant='xxx';
PS 协议参数数量超过 65535 时，由只返回 65535 个参数变更为报错 MySQL 协议有最多 65535 个参数的限制，如果参数数量超过 65535，会抛出错误。 OceanBase 数据库之前版本不是抛出错误，而是只返回 65535 个参数。 新版本兼容了 MySQL 行为，参数超过 65535 个时报错处理。
Rename Table 变更为加锁操作 历史版本中 Rename Table 是一个不需要加锁的 Online DDL 操作，事务跨 Rename Table 操作时，可能会出现非预期的问题。V4.2.3_CE 版本支持对 Rename Table 加表锁，并增加 Rename Table 期间的读写防御。
[g]v$plan_cache_plan_explain 支持仅指定 plan_id 进行数据查询 之前版本查询 [g]v$plan_cache_plan_explain 视图时，必须同时指定 ip + port + tenant_id + plan_id 四个过滤条件才会返回数据，仅指定 plan_id 时返回结果为空，易用性较差。新版本支持了该视图的底层虚拟表扫描，允许仅指定 plan_id 进行数据查询，可准确返回查询结果。
[g]v$ob_sql_audit 视图 tenant_id 字段含义变更 普通租户下产生的一些内部请求是基于系统租户开始初始化的，然后读数据时，切到普通租户去执行，这种情况下 tenant_id 是 1， effective_tenant_id 是当前租户 ID。 基于 OAS 的租户内有序采集需求，V4.2.3_CE 版本将可用作索引的字段 tenant_id 调整为与 effective_tenant_id 字段等价。
[g]v$ob_sql_audit 视图 sql_id 字段取值变更 V4.2.3_CE 版本之前，CALL 语句的 sql_id 为空字符串生成的 MD5 编码；匿名块语句的 sql_id 为未经参数化的原始 PL 代码字符串生成的 MD5 编码。V4.2.3_CE 版本将两者的 sql_id 都修改为参数化之后的语句生成的 MD5 编码。
[g]v$ob_active_session_history 字段取值变更 V4.2.3_CE 版本之前，MODULE、ACTION、CLIENT_ID 一直为 NULL。V4.2.3_CE 版本开始，这三列真实展示用户设置信息。（通过 DBMS_APPLICATION_INFO 设置 MODULE、ACTION，通过 DBMS_SESSION 设置 CLIENT_ID）。
未指定别名的 null 值投影列名称修改为 NULL MySQL 会将未指定别名的 null 值（\\N, null, Null...）投影列的名称设置为 NULL，而出现在复合表达式中的 null 会保持原串。OceanBase 历史版本不会对 null 值投影列的名称做任何修改。新版本在 ob_compatibility_version = 4.2.3.0 或指定之后的 V4.2.x_CE 版本时，MySQL 模式下会修改为和 MySQL 相同行为。
禁止在 Delete/Update 语句中的 Limit 子句中使用 Offset 语义 新版本在 ob_compatibility_version = 4.2.3.0 或指定之后的 V4.2.x_CE 版本时，MySQL 模式下禁止在 Delete/Update 语句中的 Limit 子句中使用 Offset 语义。具体而言，OceanBase 原先支持的 update/delete...limit x,x 和 update/delete...limit x offset x 写法，现在需要语法报错，与 MySQL 行为保持一致。
REPLACE('abd', '', null) 返回结果按 ob_compatibility_control 分别兼容 新版本在 ob_compatibility_version = 4.2.3.0 或指定之后的 V4.2.x_CE 版本时，历史版本升级后或新建租户时取 ob_compatibility_control = MYSQL5.7 ，MySQL 模式下 REPLACE('abd', '', null) 行为由兼容 MySQL 8.0 修改为兼容 MySQL 5.7。
限制用户变量最长 64 字符 新版本在 ob_compatibility_version = 4.2.3.0 或指定之后的 V4.2.x_CE 版本时，MySQL 模式下用户变量长度由不设限修改为最长 64 字符。
Outline、Sequence 新增权限管控 新版本在 ob_security_version = 4.2.3.0 或指定之后的 V4.2.x_CE 版本时， MySQL 模式下需要授予 CREATE/ALTER/DROP 权限后，用户才可以创建和管理 Outline、Sequence。
创建和管理 TABLESPACE 新增 CREATE TABLESPACE 权限管控 新版本在 ob_security_version = 4.2.3.0 或指定之后的 V4.2.x_CE 版本时， MySQL 模式下需要授予 CREATE TABLESPACE 权限后，用户才可以创建和管理 TABLESPACE。
使用 IP 启动时移除与网卡名不匹配导致的启动失败 在启动 OBServer 时，如果无法找到与 local_ip 对应的本地网卡名称，新版本不会启动失败，而是会记录一条错误日志，并继续使用配置中的 devname 作为本地网卡名。

视图变更

新增如下变更：

视图 变更类型 变更说明
CDB/DBA_OB_RESTORE_PROGRESS 新增列 新增 RECOVER_SCN、RECOVER_SCN_DISPLAY、RECOVER_PROGRESS、TABLET_COUNT、FINISH_TABLET_COUNT、RESTORE_PROGRESS 6 列，用于展示物理恢复进度。
CDB/DBA_OB_LS_REPLICA_TASKS 新增列 新增 DATA_SOURCE_SVR_IP、DATA_SOURCE_SVR_PORT、IS_MANUAL 列，用于记录容灾任务执行时引用的数据源和容灾任务生成来源。
CDB/DBA_OB_LS_REPLICA_TASK_HISTORY 新增 新增系统视图，用于展示容灾任务执行历史。CDB 视图仅在 SYS 租户支持，DBA 视图在所有租户支持。
CDB/DBA_OB_AUX_STATISTICS 新增 用于展示每个租户的辅助统计信息。CDB 视图仅在 SYS 租户支持，DBA 视图在所有租户支持。
[G]V$SQL_WORKAREA 新增列 新增 DB_ID 字段，用于描述该请求的连接所属的数据库 ID。
[G]V$OB_SQL_AUDIT 新增列、字段含义调整、字段取值变更
  • 新增 STMT_TYPE 字段，用于判断 SQL 类型。
  • 新增 NETWORK_WAIT_TIME 字段，用于展示网络类别等待事件的等待时间。
  • 新增 PROXY_USER 字段，用于代理用户登录场景展示代理用户名称信息。
  • TENANT_ID 字段调整为与 EFFECTIVE_TENANT_ID 字段等价。
  • SQL_ID 字段针对 CALL 语句执行的 PL 请求、通过匿名块执行的 PL 请求，展示语句真实生成的 MD5 编码。
[G]V$OB_PROCESSLIST 新增列 新增 PROXY_USER 字段，用于代理用户登录场景展示代理用户名称信息。
[G]V$OB_ACTIVE_SESSION_HISTORY 字段取值变更 V4.2.3_CE 版本开始，MODULE、ACTION、CLIENT_ID 三列真实展示用户设置信息。（通过 DBMS_APPLICATION_INFO 设置 MODULE、ACTION，通过 DBMS_SESSION 设置 CLIENT_ID）
[G]V$OB_SESSION_PS_INFO 新增 用于展示租户所有 Session 打开的 PS 信息。V$ 视图表示当前 OBServer，GV$ 视图表示所有 OBServer。在所有租户下支持。
CDB/DBA_INDEX_USAGE 新增 用于展示索引访问信息。CDB 视图仅在 SYS 租户支持，DBA 视图在所有租户支持。
V$OB_COMPATIBILITY_CONTROL 新增 MySQL 模式视图，用于展示所有可以按 OceanBase 数据库发行版本进行产品行为兼容控制的功能。
mysql.role_edges 新增 MySQL 模式视图，用于展示角色和用户的授予关系。
mysql.default_roles 新增 MySQL 模式视图，用于展示用户默认启用的角色。
mysql.columns_priv 新增 MySQL 模式视图，用于展示用户拥有的列级权限。
sys_external_tbs.__all_external_alert_log_info 新增 sys 租户下新增系统外部表，用于结构化查看 alert.log 日志信息。

配置项变更

配置项 变更类型 变更说明
enable_kv_group_commit 新增 新增租户级配置项，用于控制是否开启 OBKV 组提交功能，在开启的情况下，服务端会将操作按照执行计划分组，按组执行；在未开启的情况下，服务端将会一个一个执行。默认为 False，表示不开启。
choose_migration_source_policy 新增 新增租户级配置项，用于控制迁移源端的选择策略。提供 2 种选择：
  • IDC：在同 IDC 的机器中优先选择 Follower 副本作为源端，若仅有 Leader 副本，则选 Leader 副本。
  • Region：在同 Region 的机器中优先选择 Follower 副本作为源端，若仅有 Leader 副本，则选 Leader 副本。
data_disk_write_limit_percentage 新增 新增集群级配置项，用于控制数据盘达到设置的水位后，用户写入报错的问题，发现问题以后，可以通过删表的方式紧急释放空间，避免集群故障。该配置项应该大于 data_disk_usage_limit_percentage，开启时建议配置：(1-memstore_limit_size / data_disk_size)*100%。默认值为 0，表示不开启停止用户写入的功能。
alert_log_level 新增 新增集群级配置项，用于控制 alert.log 的日志级别，如 INFO、WARN、ERROR，默认为 INFO。
syslog_disk_size 新增 新增集群级配置项，用于控制系统日志可使用的总磁盘空间。默认为 0M，即兼容老版本默认行为，可使用整块磁盘。
syslog_compress_func 新增 新增集群级配置项，用于控制系统日志文件压缩算法，可选 none、zlib_1.0、zstd_1.0，zstd_1.3.8。默认为 none，表示不压缩系统日志文件。
syslog_file_uncompressed_count 新增 新增集群级配置项，用于控制每种日志不压缩的系统日志文件数量。默认为 0。
enable_global_background_resource_isolation 新增 新增集群级系统配置项，用于控制是否对全局的前后台任务进行 CPU 资源隔离。默认为 False，表示不隔离。
global_background_cpu_quota 新增 新增集群级系统配置项，用于控制 enable_global_background_resource_isolation 为 True 时，后台任务可使用的 CPU 配额。默认为-1，表示不受 Cgroup 限制。
net_thread_count 变更默认值 该配置项用于控制网络 I/O 线程数量。默认值仍然为 0，但自适应策略有变化。不同 CPU 数量下，可使用线程数有所提升。
ddl_high_thread_score 新增 新增租户级配置项，用于控制执行 DDL 补数据过程中，对每个 Tablet 的 KV 数据合并可使用的 DAG 线程数。默认为 6，和历史版本可用线程数一致。
lob_enable_block_cache_threshold 新增 新增租户级配置项，用于控制 OUTROW 存储的 LOB，如果长度小于等于该阈值，则进入微块缓存来加速下一次查询。默认 256K。
ob_default_lob_inrow_threshold 变更默认值 该配置项用于指定 LOB INROW 存储的最大阈值，默认值由 4096 调整为 8192。

系统变量变更

变量 变更类型 变更说明
activate_all_roles_on_login 新增 新增 MySQL 租户下 GLOBAL 级系统变量，用于控制用户登录时是否激活所有角色。
ob_compatibility_control 新增 新增 MySQL 租户 GLOBAL 级系统变量，用于控制存在兼容行为冲突时，OceanBase 数据库和 MySQL 5.7 行为一致，还是和 MySQL 8.0 行为一致。默认为 MYSQL5.7。创建租户时指定，租户创建后不允许修改。
ob_compatibility_version 新增 新增租户下 GLOBAL 系统变量，用于控制产品行为发生变更的功能，行为和 OceanBase 哪个发行版本兼容。新建集群时，默认为当前集群版本；版本升级时，为前一个版本配置，当前默认 4.2.1.0。可修改。
ob_security_version 新增 新增租户下 GLOBAL 系统变量，用于控制安全相关的产品行为发生变更的功能，行为和 OceanBase 数据库发行版本兼容。新建集群时，默认为当前集群版本；版本升级时，为前一个版本配置，当前默认 4.2.1.0。可修改，和 ob_compatibility_version 区别为该变量只能推高，不能回退。
cardinality_estimation_model 新增 新增 GLOBAL/SESSION 级系统变量，用于控制优化器估行时使用的相关性模型。可选 INDEPENDENT/PARTIAL/FULL 多种模型：
  • INDEPENDENT：假设谓词间是完全独立的，是 V4.2.2_CE 及之前的优化器所使用的假设。多个 Filter 的联合选择率由单个 Filter 的选择率相乘得到。该假设在 Filter 较多时往往会导致估行偏小。
  • PARTIAL: 假设谓词间有一定程度的相关性。多个 Filter 的联合选择率会通过指数回退计算。
  • FULL: 假设谓词间是完全相关的。多个 Filter 的联合选择率直接由其中最小的 Filter 选择率决定，可以得到一个较大的估行结果。较为极端。
默认为 PARTIAL。

函数/系统包变更

函数名 变更类型 变更说明
DECODE_TRACE_ID 新增 用于解析 trace_id 获取发起 SQL 请求的 OBServer IP、Port 信息。
CURRENT_ROLE 新增 用于展示当前 Session 激活的 Role。
PASSWORD 新增 支持 MySQL 5.7 Password 表达式，用于计算和返回哈希密码值。

语法变更

语法 变更说明
新增指定 SET/PIECE 级恢复源命令 新增指定位点查看恢复需要使用的 SET/PIECE 及指定 SET/PIECE 级路径恢复到指定位点：
  1. 加载需要恢复的路径：ALTER SYSTEM ADD RESTORE SOURCE 'xxx';
  2. 如果输入错误，可执行下述语句来撤销之前的输入：ALTER SYSTEM CLEAR RESTORE SOURCE;
  3. 调用恢复命令：ALTER SYSTEM RESTORE <$restore_tenant> UNTIL '<$restore_checkpoint>' WITH 'xxx';
  4. 解析恢复到指定位点所需的备份 SET/PIECE 的原始路径：ALTER SYSTEM RESTORE FROM 'uri' UNTIL { TIME='timestamp' | SCN=scn } PREVIEW;
  5. 预览恢复到期望时间所需要的备份 SET/PIECE 的原始路径：SHOW RESTORE PREVIEW;
新增日志流副本管理命令
  1. 添加日志流副本。
  2. 删除日志流副本。
  3. 转换日志流副本类型。
  4. 迁移日志流副本。
  5. 修改日志流的 paxos 成员数量。
  6. 取消日志流副本任务。
新增 auto_increment_cache_size 表级选项 新增表级选项语法，可以在 CREATE TABLE 或者 ALTER TABLE 时指定 auto_increment_cache_size。 比如create table t1 (...) auto_increment_cache_size=xxx; alter table t1 set auto_increment_cache_size=xxx;该值默认为 0 表示未配置，此时会使用租户级配置项作为自增列缓存大小。
新增 table_mode 表级选项 V4.2.3 重新启用了 V3.x 系列的表选项 table_mode，用户可以通过为每张表设置不同的表选项，以指定不同触发频率的快速冻结与自适应合并策略来应对 Buffer 表问题。如 create table t1 (c1 int) table_mode = 'normal/queuing/moderate/super/extreme';
新增 DAS 层 DML 是否允许多线程并行执行的 HINT 新增 enable_parallel_das_dml 和 disable_parallel_das_dml 两个 HINT，分别用于控制开启和关闭 DAS 层 DML 并发。在租户级隐藏配置项 _enable_parallel_das_dml 为 True 的前提下：
  1. 场景 1 /+enable_parallel_das_dml enable_parallel_dml parallel(n)/ 中，该 HINT 组合会强制指定 DAS 并发执行，采用 parallel 定义的并发度；
  2. 场景 2 /+enable_parallel_dml parallel(n)/ 中，该 HINT 组合会优先使用 PDML，如果当前场景不支持 PDML 优化，会使用 DAS 并发执行优化；
  3. 场景 3 中 Session 上强制指定并行度时，行为等同于场景 2。
新增角色相关语法
  1. 创建 Role：CREATE ROLE [IF NOT EXISTS] role [, role ] ...
  2. 删除 Role：DROP ROLE [IF EXISTS] role [, role ] ...
  3. 授权 Role 给用户：GRANT role [, role] ... TO user_or_role [, user_or_role] ... [WITH ADMIN OPTION]
  4. 撤销给用户授予的 Role：REVOKE [IF EXISTS] role [, role ] ... FROM user_or_role [, user_or_role ] ... [IGNORE UNKNOWN USER]
  5. 设置用户登录时默认启用的 Role：
    • SET DEFAULT ROLE {NONE &#124; ALL &#124; role [, role ] ...} TO user [, user ] ...
    • ALTER USER [IF EXISTS] user DEFAULT ROLE {NONE &#124; ALL &#124; role [, role ] ...}
  6. 设置当前 Session 激活哪些 Role：SET ROLE { DEFAULT &#124; NONE &#124; ALL &#124; ALL EXCEPT role [, role ] ... &#124; role [, role ] ...}
  7. show grants 展开 Role 权限：SHOW GRANTS FOR user USING role
新增列级权限授予回收相关语法
  1. 授予列级权限：GRANT [priv [(col_list)] ]+ ON [db_name.]table_name TO user [with grant option];
  2. 回收列级权限：REVOKE [priv [(col_list)] ]+ ON [db_name.]table_name FROM user [with grant option];
支持复制表属性变更相关命令
  1. 普通表转复制表：ALTER TABLE t1 DUPLICATE_SCOPE = 'CLUSTER';
  2. 复制表转普通表：ALTER TABLE t1 DUPLICATE_SCOPE = 'NONE';

周边配套

OceanBase 数据库 V4.2.3_CE 版本推荐使用的平台工具版本如下：

组件 版本
ODP V4.2.3
OCP V4.2.2_CE_HF1
OBD V2.8.0
All in One V4.2.3
ODC V4.2.4-bp1
OBCDC V4.2.3
OMS V4.2.3_CE
OBClient V2.2.3
LibOBClient V2.2.3

升级说明

  • 支持 OceanBase 数据库 V4.2.1_CE_BP2 及之前的 V4.x_CE 版本通过有效升级路径在线升级到 OceanBase 数据库 V4.2.3_CE_BETA 版本。V4.2.1_CE_BP3 及之后的 BP 版本目前不再支持升级到 V4.2.3_CE_BETA 版本。
  • 支持 OceanBase 数据库 V4.2.2_CE 版本经停 V4.2.2_CE_BP1 版本，在线升级到 V4.2.3_CE_BETA 版本。
  • V4.2.1_CE 及 V4.2.2_CE 系列的版本升级到 V4.2.3_CE_BETA 版本时，需要将系统变量 _nlj_batching_enabled 设为 False。升级完成后可以打开。
  • 集群 Tablet 比较多的情况下，升级过程中 OBServer 重启会比较耗时。已知 300w+ 的 Tablet，重启时长大约在 20min，存在大量 Tablet 的场景需要特别注意预留足够的升级时间。
  • ODP 和 OBServer 升级顺序：建议先升级 OBServer 版本再升级 ODP 版本。
  • 升级期间，系统会自动禁用合并和 DDL 操作，升级完成后恢复正常使用。

开源鸣谢

在此版本发布中，特别感谢社区伙伴的贡献：

感谢联通软研院团队邱永刚 @qiuyg3 在 ObLogMiner 功能上的贡献。

Contributors

qiuyg3
Assets 26