OceanBase Database Community Edition V4.2.0

V4.2.0_CE_BP1

Version information

• Release date: September 14, 2023
• Version: V4.2.0_CE_BP1
• RPM version: oceanbase-ce-4.2.0.0-101000032023091319

Overview

This version addressed several customer and internal testing issues to enhance product stability.

Bug fixes

• Fixed the issue where the query results were incorrect if INDEX SKIP SCAN was used in the query plan and parallel query was enabled.

• Fixed the issue where data inconsistency would occur between the main table and the index table when the REPLACE INTO statement was used.

• Fixed the issue where the OBServer node would encounter exceptions upon a restart when _optimizer_better_inlist_costing was explicitly set to true.

• Fixed the issue where the max_ls_id field in the __all_tenant_info table was not correctly updated during the upgrade from OceanBase Database V4.1.0_CE to OceanBase Database V4.2.0_CE. This issue could result in continuous transaction retries after the upgrade until a timeout error occurred.

Considerations

1. In versions between V4.1.0_CE_BP3 and V4.2.0_CE, if the default value of the hidden parameter _optimizer_better_inlist_costing was changed from False to True, it needs to be changed back to False. Otherwise, the OBserver node might encounter exceptions after a restart.

2. If you want to upgrade the Community Edition from V4.1.0 to V4.2.0, it is recommended that you directly upgrade to V4.2.0_CE_BP1 or higher to avoid potential transaction timeout issues that may be triggered during the upgrade process.

V4.2.0_CE

Version information

• Release date: September 4, 2023
• Version: V4.2.0_CE
• RPM version: oceanbase-ce-4.2.0.0-100010032023083021

Overview

• This version addressed several customer and internal testing issues to enhance product stability.
• This version now supports data reads between OceanBase MySQL tenants by using a DBLink.

Bug fixes and improvements

• Fixed the issue where the garbage collection (GC) thread would skip partition checks and cause excessive storage consumption when there were no multi-version SSTables available.
• Fixed the issue where major compaction would get stuck due to Error 4012 when the system attempted to read tablets from the disk.
• Fixed the direct load timeout issue caused by excessive load on the Clog disk.
• Fixed the issue where high-priority RPC requests were unable to enter the queue and could trigger Error 4019 when normal-priority RPC requests reached the limit.
• Fixed the issue where creating unique indexes in parallel on a table that contains duplicate rows of data could cause core dump.
• Fixed the issue where reading from the leader node forcefully would cause its CPU resources to be occupied by traffic from the follower nodes during the retry of weak consistency reads.
• Fixed the issue where the cluster would encounter Error 4184 due to temporary file I/O exceptions in high disk load scenarios.
• Fixed the issue where the SqlExecutor module encountered memory bloat due to improper memory allocation during MERGE JOIN operations.
• Fixed the issue where the cluster could encounter exceptions in some clock drift scenarios.
• Optimized the online statistics collection strategy by only collecting global statistics for partitioned tables and not collecting partition-level statistics, so as to help alleviate the problems of excessive RPC requests and high memory usage.
• Improved the efficiency of reloading configurations during the restart process in scenarios with a large number of partitions.

V4.2.0_CE_BETA_HF1

Version information

• Release date: August 21, 2023
• Version: V4.2.0_CE_BETA_HF1
• RPM version: oceanbase-ce-4.2.0.0-100010022023081817

Overview

This version fixed some bugs and improved stability.

Bug fixes

• Fixed the issue where core dump would occur during the startup of OceanBase Database if it was deployed in a CPU environment that does not support Advanced Vector Extensions (AVX) instruction set.
• Fixed the issue where exceptions occurring during the sending of RPC packets could result in all nodes in the cluster becoming inactive.

V4.2.0_CE_BETA

Version information

• Release date: August 2, 2023
• Version: V4.2.0_CE_BETA
• RPM version: oceanbase-ce-4.2.0.0-100000152023080109
• Description: The Beta version resolved most of the issues and is becoming more and more stable. However, there may still be some minor issues or errors that need to be addressed in the final stable release, so we recommend that you use this version in a testing environment.

Overview

OceanBase Database V4.2.0_CE further enhanced the core features based on V4.1.0_CE and included all major functionalities of the V3.x series. It further improved product scalability, resource utilization, performance, compatibility, and ease of use. V4.2.x will serve as a long-term support (LTS) version.

Core features of V4.2.0 include:

• MySQL compatibility

  • Function index: The MySQL mode of V4.2.x now supports MySQL 8.0-compatible function-based indexes, further enhancing feature stability.
  • OBCDC: The new version now supports the output of row data according to the column definition sequence, meeting the compatibility requirements of MySQL Binlog Service. OBCDC no longer depends on the system tenant, but supports data synchronization with user tenants. This means that user tenants can use OBCDC on their own, without relying on the system tenant. V4.2.0 also supports data synchronization for tables without primary keys.

• Performance improvements

  • Enhancement of optimizer/executor/procedural language (PL) capabilities: Enhanced the query rewriting and optimization capabilities, with a focus on improving the performance of executor operators. Optimized the plan cache and parallel execution logic for PL and supported SQL statements that return complex data types.
  • Statistics functionality enhancement: Enhanced the stability and availability of the statistics functionality, added the monitoring and diagnostic functionality for statistics collection, and optimized the performance and memory overhead of statistics collection.
  • Dynamic sampling: Supports dynamic sampling, allowing optimizers to collect statistical information during SQL execution and generate optimal execution plans. This helps to improve query performance.
  • Query plan evolution: Supports query plan evolution to avoid performance regression of execution plans.
  • Runtime Filter: Provided full support for Runtime Filter, improving the analytical processing (AP) performance.
  • Performance optimization under 4 CPU cores (4C): Optimized the performance in various OLTP scenarios by over 20% in 4C environments with default parameter settings.
  • Performance optimization of reading transaction data: Optimized the performance of querying transaction tables during the minor compaction of uncommitted data.
  • Parallel minor compaction of transaction data tables: OceanBase Database V4.1.0 already supports parallel MINOR_MERGE (combining multiple mini SSTables into a minor SSTable). V4.2.0 additionally supports parallel MINI_MERGE for transaction data tables (freezing the MemTable and converting it into a mini SSTable through minor compaction).
  • Fast generation of random data: Introduced the built-in function GENERATOR(N). This function can be used with random functions such as RANDOM([N]), RANDSTR(N, gen) and distribution control functions like NORMAL(<mean>, <stddev>, <gen>), UNIFORM(<min>, <max>, <gen>), and ZIPF(<s>, <N>, <gen>) to generate random data quickly. Compared with MySQL Recursive CTE, OceanBase Table Generator has significant performance advantages.

• OLAP capabilities

  • Read-only external tables: Supports the creation and reading of read-only external tables when performing online analytical processing (OLAP) operations, thereby reducing costs of loading or inserting data into databases.

• Scalability enhancement

  • Transfer and load balancing: Uses the transfer technology to split and merge log streams, supporting data migration across nodes on a per-partition basis. This provides flexible scaling and dynamic data balancing capabilities.
  • Replicated tables: Reconstructed replicated tables based on the single-node log stream architecture. The new version introduced partition-based readable version number verification and log stream-based lease-granting mechanism to ensure the correctness of strong consistency reads. Additionally, this version improved the ability to switch leaders without killing transactions. This means that uncommitted replicated table transactions can continue to execute after a leader switch initiated by users or load balancing. Compared with V3.x, V4.2.0 has better write transaction performance, stronger disaster recovery capability for replicated tables, and lower impact on read operations in case of replica failure.

• High availability enhancement

  • Physical standby database: OceanBase Database V4.1.0 introduced an archive-based primary/standby solution, offering flexible configuration without requiring network connection between primary and standby tenants. However, many scenarios still require a primary/standby solution based on direct network connection to reduce storage and network costs, and to simplify backup and restore operations. V4.2.0 extended support in these scenarios.
  • Log write throttling: When the speed of committing logs to the disk is greater than the minor compaction speed, the log disk will become full. In V4.2.0, when the log disk usage reaches a specified threshold, the speed of committing logs to the disk is limited, to minimize the occurrences that the log disk becomes full because of the high write pressure in the cluster. For more information, see the log_disk_throttling_percentage and log_disk_throttling_maximum_duration parameters.

• Resource utilization optimization

  • Idle CPU optimization: Made optimizations for high-frequency, time-consuming SQL statements and background threads to reduce the CPU overhead of internal SQL execution and periodic execution of background threads. According to a test conducted on a single tenant in an idle environment (30 tables and a million rows of data per table), the default CPU overhead is around 0.2C.
  • On-demand expansion of disk data files: Supports progressive disk expansion, where an appropriate disk size is first pre-allocated and then automatically expanded based on actual usage.
  • Shared memory identification mechanism: Added a defense mechanism in the technical means to mark the code that is expected to use the shared memory and to optimize the situation where the shared resources increase with the code merge.

• Usability enhancement

  • Auto degree of parallelism (DOP): Support for auto DOP to automatically calculate query parallelism, reducing the threshold for parallel execution.
  • End-to-end diagnostics enhancement: OceanBase Database V4.1.0 introduced end-to-end diagnostic capabilities, and V4.2.0 added some trace logging information based on the end-to-end diagnostic framework. This version also provides interactive Show Trace capabilities based on transactions and SQL queries.
  • Logical plan management: Made several improvements to make logical plans more user-friendly. These improvements include the ability to save EXPLAIN plan information, feedback on the execution of logical plans, and real-time plans for running SQL queries. Additionally, rich interfaces for the DBMS_XPLAN package were also provided to display plans for specific queries, baseline plans, and real-time session plans in a formatted manner.
  • Background thread statistics: Versions earlier than V4.2.0 tended to record front-end thread diagnosis information from a client perspective. However, for OceanBase Database, background threads constitute the vast majority. Therefore, it is necessary to learn about the state of background threads, both for troubleshooting issues and diagnosing performance. V4.2.0 adds the [G]V$OB_THREAD view to record the status information of all threads.

• National standards

  • GB18030-2022 character set: Provided support for GB18030-2022 character set since OceanBase Database V4.2.0. GB18030-2022 character set was released on July 19, 2022, and formally implemented on August 1, 2023. It supports more rare Chinese characters and minority languages, making it a mandatory standard for information systems.

• Product forms

  • Standalone architecture-based primary/standby solution: OceanBase Database V4.1.0 introduced the standalone architecture, and V4.2.0 further expanded this architecture to include a primary/standby solution.
  • Read-only replicas (R replicas): OceanBase Database V4.2.0 introduced read-only replicas to provide read-only capabilities, which are different from full-featured replicas.

Description of core features

MySQL compatibility

Function index: MySQL 8.0 introduced the function-based index feature to optimize query performance. Typically, if a function or expression is used for a query column, a regular index cannot be used for optimization, and a function-based index is needed to improve query performance. Function index can use the return value of a function as part of the index, thereby speeding up query execution. Starting from V4.2.0, OceanBase Database supports MySQL 8.0-compatible function-based index under the MySQL tenant, and it can be created using statements such as create table, create index, and alter table. In addition, V4.2.0 also enhances the index extraction capability, and can effectively use indexes in most scenarios where there is implicit conversion outside the index expression, or the reference column on a prefix index has a Like predicate. For more information, see Create an index.

Performance improvements

Statistics functionality enhancement

Starting from OceanBase Database V4.0.0, statistics collection is fully initiated and maintained by the SQL layer, and statistics are no longer collected during major compaction. However, due to the incomplete functionality of statistics collection, there are some issues with performance and memory consumption during collection, which further affects the generation of the SQL plan. Additionally, the lack of effective monitoring methods for statistics collection makes it difficult for users to determine the status and expiration of statistics collection on various data tables, resulting in operational difficulties.

V4.2.0 enhanced the stability and usability of statistics collection. Online statistics collection optimizes the data structure and collection performance, improving performance by around 10% for the INSERT INTO SELECT and LOAD DATA methods. Offline statistics collection optimizes the plan generation path and memory usage, improving performance by 10%-20% in the scenario where 64 concurrent offline statistics collection tasks are being performed on the partitioned table tpcds_100g.

In addition, V4.2.0 now supports statistics collection monitoring and diagnostics, with the addition of the dynamic performance view [G]V$OB_OPT_STAT_GATHER_MONITOR for querying the real-time status of statistics collection tasks, the dictionary view DBA_OB_TASK_OPT_STAT_GATHER_HISTORY for querying the execution status of history statistics collection tasks, and the dictionary view DBA_OB_TABLE_OPT_STAT_GATHER_HISTORY for querying the execution status of history collection tasks on tables.

Dynamic sampling

When executing SQL queries, the optimizer in OceanBase Database needs to collect statistical information on tables and indexes in order to choose the optimal execution plan. If the statistical information is inaccurate or incomplete, the chosen execution plan may not be optimal, leading to a decrease in query performance. Basic statistical information is usually obtained through automatic or manual collection. However, if the data distribution changes, statistical information is not collected, or complex SQL queries are encountered, existing statistical information may no longer be accurate.

OceanBase Database V4.2.0 introduced the dynamic sampling feature, which samples database objects in advance during the plan generation phase, estimates the number of rows through sampling, and generates better execution plans for the cost model. Currently, you can control the dynamic sampling feature through system variables, query hints, and system parameters and the sampling set size is limited by the degree of parallelism. For more information, see Perform dynamic sampling by using optimizer.

Runtime Filter

Since V3.1.0, OceanBase Database has supported Join Bloom Filter to quickly filter data during data scanning in Join operations. In V4.0.0, Partition Bloom Filter was introduced to dynamically filter partitions for scenarios where Join keys are partition columns or partition column prefixes. Furthermore, in V4.1.0, multiple Bloom Filters can be generated within adjacent data flow operators (DFOs) and within a single DFO. Filters that dynamically filter data during execution, such as In, Range, and Bloom Filter, are collectively referred to as Runtime Filter (RF). V4.2.0 provides full support for Runtime Filter, improving the AP processing performance. You can manually enable Runtime Filter by using px_join_filter or px_part_join_filter, and disable it by using no_px_join_filter or no_px_part_join_filter. Additionally, four system variables are provided to adjust the execution strategy of Runtime Filter if necessary. For more information, see Runtime Filter.

Performance optimization under 4C

OceanBase Database V4.2.0 improved Sysbench performance under 4C16G (4 CPU cores and 16 GB of memory) scenarios by over 20% with a series of optimizations. These optimizations include auto-increment ID optimization, New Sort adaptive optimization, Callback Allocator optimization in PDML scenarios, time function call optimization, optimization of obtaining snapshots of single log stream statements, optimization of obtaining snapshots at tenant level, and optimization of enable_trace_log and enable_sql_audit.

Fast generation of random data

Table functions can return a data table as a result. Based on this, V4.2.0 introduced the Table Generator feature, which includes the built-in function generator(N). This function can be called in a Table function as table(generator(N)). In addition, new random functions such as RANDOM([N]) and RANDSTR(N, gen) and distribution control functions such as NORMAL(<mean> , <stddev> , <gen>), UNIFORM(<min> , <max> , <gen>), andZIPF(<s> , <N> , <gen>) are added and can be used with Table Generator to generate data. Compared with MySQL Recursive CTE, OceanBase Table Generator has significant performance advantages. For more information, see GENERATOR.

OLAP capabilities

Read-only external tables

Tables in a database are stored in the database's storage space, while data of external tables is stored in an external storage service. External tables can be queried like regular tables, but are limited to read operations and do not support data manipulation language (DML) operations. Typically, when processing external data, data must be loaded or inserted into the database by using ETL tools. During this process, the database consumes resources such as storage space, disk I/O, and CPU. In contrast, external tables can directly read external data sources for query analysis without loading or inserting them into the database.

Scalability enhancement

Transfer and load balancing

OceanBase Database V4.0.0 underwent an architecture upgrade by introducing the concept of adaptive log streams to achieve an integrated architecture for standalone and distributed modes. This architecture provides more flexible scalability and supports dynamic transformation between standalone and distributed modes. However, the scalability was not fully improved in the released version V4.1.0, as the partitions and log streams were statically bound and therefore partition distribution could not be dynamically adjusted. This restricted the capabilities of automatic load balancing and deployment flexibility.

OceanBase Database V4.2.0 is the first version to fully implement the integrated architecture for standalone and distributed modes. It uses the transfer technology to split and merge log streams, supporting data migration across nodes on a per-partition basis. This truly achieved dynamic transformation between standalone and distributed modes, further improving the scalability.

The load balancing feature at the tenant level is mainly reflected in the following two aspects:

• Tenant-level horizontal scaling: Users can extend their read-write service capabilities within and across zones by dynamically adjusting the Unit Number (the number of units that provide services in each zone) and Primary Zone (the list of zones that provide read-write services). The load balancing module will adaptively adjust the distribution of log streams and partitions based on the configured service capabilities.
• Partition balancing: Partition distribution can be dynamically adjusted to achieve balance in the number of partitions and storage space on service nodes, even in the case of dynamic changes in tables and partitions.

For more information, see Load balancing.

Replicated tables

Replicated tables are a special type of table in OceanBase Database that can read the latest modifications from any "healthy" replica. For users with low write frequency and those that are more concerned about read operation latency and load balancing, replicated tables are a good choice. Replicated tables can sacrifice a small part of transaction commit performance while reading the latest data on any "healthy" follower. Here, "healthy" means that the follower has a smooth network connection and minimal replay progress gap with the leader.

In OceanBase Database V3.2.0, the replicated table feature was already supported, allowing a backup replica to be created on every server specified for a given tenant, and the main replica and all backup data are kept in strong synchronization using a fully synchronous policy. V4.2.0 further improved the replicated table feature, enhancing the ability to switch leaders without killing transactions. That is, when a user or load balancing initiates a leader switch, the execution can continue after the switch without killing transactions. Additionally, the new version of replicated tables has better write transaction performance, stronger disaster recovery capability for replicated tables, and lower impact on read operations when a replica fails. For more information, see Create a table.

High availability enhancement

Physical standby database

OceanBase Database V4.1.0 introduced a primary/standby solution based on third-party media archiving. This solution meets the basic requirements of primary and standby databases and is flexible in configuration, for example, not requiring network connectivity between primary and standby tenants. However, compared with the direct connectivity method, this approach has some disadvantages, such as increased storage and network costs, decreased performance and stability, increased maintenance difficulty, and usage limitations such as requiring the archiving to enabled for the standby database before a switchover.

Considering these factors, V4.2.0 enhanced physical standby databases with direct network connectivity, which shortened the log synchronization link between primary and standby databases and is more suitable for standalone and small to medium-sized users. Additionally, considering limitations in available bandwidth and potential instabilities in communication between standby databases, V4.2.0 also supports bandwidth throttling for standby databases. This helps avoid situations where excessive traffic from some standby databases affects the performance of other nodes when network bandwidth is limited. For more information about how to use the physical standby database, see Overview of physical standby database.

Resource utilization optimization

On-demand disk file expansion

OceanBase Database uses a strategy of pre-allocating disk space, which ensures that the database occupies a continuous block of disk space, avoiding resource shortages caused by other applications preempting the disk. Additionally, OceanBase Database allows customization of the file system based on this strategy to improve data access efficiency. However, in the case of a small amount of user data, there may be wasted disk space. V4.2.0 provides a new user configuration option for progressive disk expansion, which pre-allocates a reasonable disk size and automatically expands it based on the actual usage of the disk. For more information, see Configure dynamic expansion of disk data files.

Usability enhancement

Auto DOP

Parallel execution is a key capability for optimizing large data queries, and the optimizer generally uses DOP to describe the amount of parallel resources available. In previous versions, users had to determine whether to enable parallelism and adjust the DOP size with the consideration of execution conditions, response time requirements, and system resources. If needed, they had to modify the system variables and HINT based on their experience. V4.2.0 introduced the auto DOP feature while retaining the ability to manually adjust the DOP. This feature evaluates the time required to execute the query when generating a query plan, automatically determines whether to enable parallel execution, and determines the optimal DOP for the current query. The DOP selection policy can be controlled through the system variable parallel_degree_policy. For more information, see Auto DOP.

End-to-end diagnostics enhancement

OceanBase Database V4.2.0 significantly improved its diagnostic capabilities, including the first implementation of visual end-to-end tracing for SQL requests. This feature helps users quickly locate and trace specific problems during a particular execution phase, and identify the server and module in which the issue occurred with the aid of detailed execution information. It also provides Show Trace capabilities based on transactions and SQL queries. For business departments, they are often more concerned about the overall time consumption of a business service. For example, in an OLTP system, a business service is typically composed of one or more transactions. Therefore, tracing at the transaction level aligns better with the user's actual business scenario.

Each transaction forms a trace, recording the relevant execution information of each SQL request in the entire internal process of OBClient > ODP > OBServer. Users can quickly identify which statements were executed in a transaction and view their execution details from the client's perspective. Show Trace also provides convenient business system association capabilities for users. Users can set the app trace id corresponding to the calling request on the business database connection through the JDBC interface or SQL interface. This app trace id will be recorded in the end-to-end tracing information and displayed in Show Trace. When users find errors in a request or service database call associated with a certain app trace id, they can use this app trace id to quickly search for the corresponding database trace associated with the app trace id in the end-to-end diagnostic system, and then directly view the time consumption and error points of the request in each phase of the database link to quickly determine the component that triggered the issue. For more information, see Show a trace.

Logical plan management

V4.2.0 enhanced logical plan management with the following features:

• After a user obtains the plan information of a query through the EXPLAIN syntax, the user can query the history through DBMS_XPLAN.display before the current session is disconnected. Alternatively, the user can specify to save the information to a target table using the EXPLAIN INTO syntax.
• Automatically saves the plans of all executed queries, including physical and logical plans, for subsequent troubleshooting. The database clears the saved query plans when the cluster is restarted.
• Displays the baseline plan of SQL plan management (SPM) using DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE.
• Displays the plan information of executing queries using DBMS_XPLAN.DISPLAY_ACTIVE_SESSION_PLAN and SESSION_ID.

For more information, see DISPLAY_ACTIVE_SESSION_PLAN.

National standards

GB18030-2022 character set

GB18030-2022 character set was released on July 19, 2022, and formally implemented on August 1, 2023, becoming a mandatory standard for full-text execution in information systems. Compared with the GB18030-2005 standard, the new standard supports more rare Chinese characters and minority languages. OceanBase Database V4.2.0 supports the new GB18030_2022 character set, along with the corresponding sorting methods. The character set name in MySQL mode is GB18030_2022. The corresponding sorting methods are also updated. For more information, see Character sets.

Product forms

Standalone architecture-based primary/standby solution

OceanBase Database V4.1.0 introduced the standalone architecture, allowing users to deploy OceanBase Database on a single server. In V4.2.0, this architecture is further expanded to include a primary/standby solution. This allows users to deploy OceanBase Database on two servers, with one acting as the primary database and the other as the standby database. For more information, see the Physical standby database section of this topic.

Read-only replicas (R replicas)

Read-only replicas do not participate in the consensus protocol voting and cannot become leaders. They only replay logs generated by the leader and generate corresponding data locally. In other words, read-only replicas can only provide read services but not write services.

OceanBase Database V4.2.0 introduced read-only replicas, which can be used by replicated tables. That is, with broadcast log streams, read-only replicas can be deployed on OBServer nodes that do not need to participate in the voting, while full-featured replicas are deployed on OBServer nodes that need to participate in the voting. The purpose of doing so is to ensure that, ideally, replicated tables can provide strong consistency reads on any OBServer node.

Here is a comparison between read-only replicas and full-featured replicas:

Performance test report

The tests aim at comparing the performance of OceanBase Database V4.2 and V4.1 in different scenarios.

Test environment specifications

Version for test

OLTP test with Sysbench

Test plan

1. Deploy an OceanBase cluster by using obd. Deploy ODP and Sysbench on two separate servers, which will act as stress servers to avoid resource contention. Note that ODP is deployed on a separate server with 64 CPU cores and 128 GB of memory (server type: ecs.c7.16xlarge).
2. Configure the OceanBase cluster. Make sure that the cluster is in the 1-1-1 architecture, with three zones and each zone having one OBServer node. After the cluster is deployed successfully, create the tenant and user required for running the Sysbench test (The sys tenant is a built-in system tenant used for managing the cluster, therefore, do not use the sys tenant to run the test). In two separate tests, set the primary zone for the tenant to RANDOM and Zone1, respectively. RANDOM indicates that the leader of the newly created table partition is randomly assigned to one of the three servers, while Zone1 indicates that the leader for the newly created table partition is concentrated on one server in Zone1.
3. Launch the Sysbench client and run the point_select, read_write, read_only, and write_only tests.
4. Set --time to 60s for each round of test. The number of threads can be 32, 64, 128, 256, 512, or 1,024.
5. Prepare the test data: 30 non-partitioned tables with 1 million rows of data (--tables=30, -table_size set to 1000000).

Tenant specifications

• MAX_CPU = 26
• MEMORY_SIZE = 70g

Parameter tuning

ALTER system SET enable_sql_audit=false;
ALTER system SET enable_perf_event=false;
ALTER system SET syslog_level='PERF';
ALTER system SET enable_record_trace_log=false;

Test results

• Point select performance

  Threads	V4.1 QPS [RANDOM]	V4.1 95% Latency (ms) [RANDOM]	V4.2 QPS [RANDOM]	V4.2 95% Latency (ms) [RANDOM]	V4.2 QPS [Zone1]	V4.2 95% Latency (ms) [Zone1]
  32	135146.05	0.27	138746.60	0.26	137423.12	0.27
  64	252277.60	0.30	252231.37	0.29	246275.56	0.31
  128	431564.13	0.36	447755.19	0.34	377949.26	0.50
  256	686271.21	0.55	730315.66	0.48	461791.47	0.94
  512	937428.74	0.95	1009966.93	0.90	535462.87	1.67
  1024	985232.01	2.35	1012734.80	2.66	537828.40	3.89

• Read-only performance

  Threads	V4.1 QPS [RANDOM]	V4.1 95% Latency (ms) [RANDOM]	V4.2 QPS [RANDOM]	V4.2 95% Latency (ms) [RANDOM]	V4.2 QPS [Zone1]	V4.2 95% Latency (ms) [Zone1]
  32	115232.45	4.74	121733.00	4.65	119054.75	4.74
  64	214733.98	5.18	221563.16	5.09	208659.70	5.37
  128	366616.03	6.09	392138.56	5.67	282377.72	8.43
  256	553922.27	8.74	577951.13	8.58	316931.91	23.10
  512	662368.79	15.83	763726.51	17.01	317668.32	42.61
  1024	653733.97	54.83	740835.95	38.94	315535.24	80.03

• Write-only performance

  Threads	V4.1 QPS [RANDOM]	V4.1 95% Latency (ms) [RANDOM]	V4.2 QPS [RANDOM]	V4.2 95% Latency (ms) [RANDOM]	V4.2 QPS [Zone1]	V4.2 95% Latency (ms) [Zone1]
  32	36840.88	5.37	43984.28	7.17	60851.38	4.10
  64	62582.56	8.13	82554.92	6.55	110359.75	4.49
  128	107054.05	9.56	114874.89	10.09	167415.76	5.99
  256	155494.00	13.70	181982.10	12.52	209163.02	10.27
  512	242458.53	17.32	253635.91	19.29	238568.50	18.28
  1024	291343.40	31.94	292482.33	36.89	249176.87	37.56

• Read/Write performance

  Threads	V4.1 QPS [RANDOM]	V4.1 95% Latency (ms) [RANDOM]	V4.2 QPS [RANDOM]	V4.2 95% Latency (ms) [RANDOM]	V4.2 QPS [Zone1]	V4.2 95% Latency (ms) [Zone1]
  32	66669.18	11.45	72554.47	11.87	92995.95	7.84
  64	124559.80	12.52	139369.33	11.65	162191.70	9.22
  128	209150.84	15.27	247061.25	12.30	219285.86	13.70
  256	309329.85	20.00	313660.08	23.95	246672.33	25.74
  512	395940.95	33.12	497734.89	25.74	270444.49	57.87
  1024	454400.54	64.47	547816.87	54.83	256076.76	121.08

TPC-C benchmark test with BenchmarkSQL

Test plan

1. Deploy an OceanBase cluster by using obd. Deploy ODP and TPC-C on one server to avoid insufficient stress on the client.
2. Configure the OceanBase cluster. Make sure that the cluster is in the 1-1-1 architecture, with three zones and each zone having one OBServer node. After the cluster is deployed successfully, create the tenant and user required for running the TPC-C benchmark test (The sys tenant is a built-in system tenant used for managing the cluster, therefore, do not use the sys tenant to run the test). In two separate tests, set the primary zone for the tenant to RANDOM and Zone1, respectively. RANDOM indicates that the leader of the newly created table partition is randomly assigned to one of the three servers, while Zone1 indicates that the leader for the newly created table partition is concentrated on one server in Zone1.

Tenant specifications

• MAX_CPU = 26
• MEMORY_SIZE = 70g

Parameter tuning

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

#sys
ALTER system SET enable_sql_audit=false;
ALTER system SET enable_perf_event=false;
ALTER system SET syslog_level='PERF';
ALTER system SET enable_record_trace_log=false;

Software versions

• mysql-connector-java-5.1.47
• BenchmarkSQL V5.0

Test configuration

• warehouse = 1000
• loadWorder = 40
• terminals = 800
• runMins = 5
• newOrderWeight = 45
• paymentWeight = 43
• orderStatusWeight = 4
• deliveryWeight = 4
• stockLevelWeight = 4

Test results

TPC-H benchmark test

Test plan

1. Deploy an OceanBase cluster by using obd. Deploy TPC-H on a separate server as the stress machine. You do not need to deploy ODP, as you can directly connect to any server for testing.
2. Configure the OceanBase cluster. Make sure that the cluster is in the 1-1-1 architecture, with three zones and each zone having one OBServer node. After the cluster is deployed successfully, create the tenant and user required for running the TPC-H test (The sys tenant is a built-in system tenant used for managing the cluster, therefore, do not use the sys tenant to run the test). Set the primary zone of the tenant to RANDOM. Perform data loading first, then execute the 22 SQL queries multiple times and calculate the average.
3. Prepare 100 GB of test data.

Tenant specifications

• MAX_CPU = 26
• MEMORY_SIZE = 70g

Parameter tuning

#sys
ALTER system flush plan cache GLOBAL;
ALTER system SET enable_sql_audit=false;
ALTER system SET enable_perf_event=false;
ALTER system SET syslog_level='PERF';
ALTER system SET enable_record_trace_log=false;

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

Test results

TPC-DS benchmark test

Test plan

1. Deploy an OceanBase cluster by using obd. Deploy TPC-DS on a separate server as the stress machine. You do not need to deploy ODP, as you can directly connect to any server for testing.
2. Configure the OceanBase cluster. Make sure that the cluster is in the 1-1-1 architecture, with three zones and each zone having one OBServer node. After the cluster is deployed successfully, create the tenant and user required for running the TPC-DS test (The sys tenant is a built-in system tenant used for managing the cluster, therefore, do not use the sys tenant to run the test). Set the primary zone of the tenant to RANDOM. Perform data loading first, then execute the 99 SQL queries multiple times and calculate the average.
3. Prepare 100 GB of test data.

Tenant specifications

• MAX_CPU = 26
• MEMORY_SIZE = 70g

Parameter tuning

#sys
ALTER system flush plan cache GLOBAL;
ALTER system SET enable_sql_audit=false;
ALTER system SET enable_perf_event=false;
ALTER system SET syslog_level='PERF';
ALTER system SET enable_record_trace_log=false;

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

Test results

Compatibility changes

Product behavioral changes

View changes

System variable changes

Parameter changes

Function changes

Recommended versions of tools

The recommended platform tool versions for OceanBase Database V4.2.0_CE are described in the following table:

Upgrade notes

• An online upgrade of OceanBase Database Community Edition from V4.1.0_CE to V4.2.0_CE_Beta is supported.
• An online upgrade of OceanBase Database Community Edition from V3.x to V4.2.0_CE is not supported.
• You can upgrade OceanBase Community Edition from V3.x to V4.2.0_CE through logical data migration by using OMS.

Considerations

• Enabling RPC SSL is not recommended in this version. Related issues will be optimized in future versions.
• Upgrading from GB18030 to GB18030-2022 character set requires a logical upgrade.