Practice of Data Integration | OceanBase Helps Ziel Deploy Distributed Cross-border E-commerce Platforms

01 Status quo of Ziel's business

• Since many of Ziel's business systems are purchased, the systems differ greatly in their technical architectures. These interweaving systems are developed in Java, PHP, and Go, making it highly complex to manage business hosted by the systems.

• Various types of databases such as MySQL, SQL Server, MongoDB, and PostgreSQL databases, as well as mixed business systems make routine O&M more complex. This imposes higher requirements on O&M engineers.

• Some business systems use MySQL databases a lot. The systems heavily rely on database services and use a large number of stored procedures and triggers. For example, we found that a business library has more than 1,200 stored procedures, and a procedure contains 6,000 lines of code.

• Database O&M management is cost-consuming. Ziel maintains database instances across multiple platforms such as AWS Europe, AWS North America, Alibaba Cloud Hong Kong (China), Alibaba Cloud Europe, and self-built Internet Data Centers (IDCs). The architectures and resources of database instances vary with the platform. The geographical and temporal differences make the O&M management more demanding, technically and financially.

• Most database instances are created in traditional standalone mode, and therefore cannot be scaled out to keep pace with growing business.

02 Challenges

Considering the status quo of its business, Ziel is exploring solutions to the following challenges:

• Large promotion-generated traffic brings high-concurrency requirements. As most database instances are created in traditional standalone mode and therefore cannot be scaled out to support business growth, marketing campaigns such as flash sales tend to cause the business system to crash.

• Traditional standalone database instances are incapable of handling rapidly growing data volume and must be scaled out accordingly.

• Business systems require more on the availability, security, reliability, and O&M stability of databases due to fast business growth. However, the Recovery Point Objective (RPO) and Recovery Time Objective (RTO) for standalone database instances fail to meet the business needs.

• Ziel's business platforms are distributed around the world, and data from these platforms need to be integrated for analysis and decision-making. Therefore, databases need to support hybrid transaction/analytical processing (HTAP).

• It is encouraged to use information systems that are domestically developed and controlled.

• The company has to reduce costs and boost effectiveness.

03 Why OceanBase Database

Considering its business needs, Ziel compared the mainstream distributed databases and found that OceanBase Database provides favorable features in a well-developed product ecosystem, and can therefore enable Ziel to get rid of the pain points of its business architecture in use.

A full range of database features and supporting O&M tools

• Elegant kernel architecture:

① Data synchronization is based on the Paxos protocol, because Paxos outperforms Raft in terms of data replication between nodes.

② Multi-tenant management mode isolates system resources from one tenant to another. A leader node can be assigned in each tenant to make full use of the CPU and I/O isolation between nodes.

③ Partitions and table groups can be customized to reduce distributed transactions.

• Compatibility with SQL protocols: OceanBase Database is compatible with both MySQL and Oracle databases, especially stored procedures, triggers, and user-defined functions. Ziel's business systems can be smoothly migrated without modifications.

• Compact storage: OceanBase Database provides self-developed distributed storage engines that can reach a compression ratio 5 to 7 times higher than that of MySQL.

• Diverse data replicas: OceanBase Database supports full-featured (read and write), encrypted voting, log-only, and read-only replicas, and allows users to choose the appropriate architecture based on their requirements for business performance and the service level agreement (SLA).

• Distributed transactions: Data read and write are separated, DML operations are performed in memory, and on-disk data is read-only.

• O&M management: Abundant views are provided for users to check the database performance. In addition, OceanBase integrates the features of development, deployment, data migration, and automated O&M with a large product family.

• Data migration: OceanBase Migration Service (OMS) allows users to complete one-stop data migration with help of just a simple guide and the following features: full migration, incremental migration, data verification, and reverse synchronization.

Adaptable performance

We have tested the performance of OceanBase Database to check whether it satisfies our expectations. The details of the benchmark test are given below:

• CPU: 16 cores Memory: 100 GB Disk: 120 GB SSD

• Test environment: OceanBase Database V3.2.2 deployed in Apsara Stack

• Number of tables: 32

• Data volume per table: 10 million rows

• Test thread duration per scenario: 5 minutes

• Test tool: sysbench

• Sample command: sysbench --config-file=config oltp_point_select --tables=32 --table_size=10000000 --threads=8 --db-ps-mode=disable --mysql-ignore-errors=6002,6004,4012,2013,4016 prepare

• Architecture: Three nodes, with two for data and one for logs, are deployed in the same IDC.

• Test results:

• OS monitoring:

The preceding deployment architecture shows that OceanBase Database supports quite a flexible selection of replicas. We can select different replica types as needed. In addition, the benchmark test indicates satisfactory database I/O performance with TPS and QPS as expected.

04 Troubles and solutions

There were zigzags when we migrated from MySQL to OceanBase Database. Such zigzags did cause some headaches, but the results turned out to be pretty good with the technical support from OceanBase developers. Here are some typical troubles we encountered:

Q1: OMS does not support the migration of tables without the primary or unique key from MySQL.

This restriction is explicitly noted in the OMS documentation. Since most of our systems are purchased, many tables, some bulky, do not have the primary or unique key.

When we attempted to find a way out, we learned from OceanBase product R&D personnel that OMS supports migrating such tables from Oracle because it automatically adds a hidden primary key to the tables and deletes it after the migration. As for MySQL, on the contrary, the incremental synchronization after a full migration can be devastating, or impossible to do for large tables. The only possible way is to use data synchronization tools like DataX.

DataX + OMS solution:

Table structure migration: manual export and import.

Data migration: full data migration by using DataX.

Data verification: OMS supports performing data verification alone.

Incremental data synchronization: not supported.

Rollback: reverse synchronization by using DataX with OMS.

Given the large number of tables and that DataX generates configuration files on a table-by-table basis, we wrote a script to automatically generate the configuration files and then migrated tables in batches.

In addition, as the reverse synchronization is a process of full migration, we estimated the time required for the forward and reverse synchronization, and the verification, and calculated the time window of change by using the following formula: the time required for forward and reverse synchronization + 2 × time required for verification.

Q2: For a migrated table with data, there is an error when the index name is the same as a column name. Example:

create table t_xxxxx( `id` int unsigned not null auto_increament , `code` bigint(20), ......);create index `code` on `t_xxxxx`(`code`);

OMS cannot migrate a table if a column has the same name as the index. OceanBase offered a temporary solution, which is to rename the index, and the OMS R&D team then solved this problem once and for all by releasing a new feature.

The problem was solved in two days since our feedback, and I was impressed by their efficiency.

Q3: Stored procedures do not support the GET DIAGNOSTICS CONDITION syntax.

One of our application systems adopts a large number of stored procedures, and many of the procedures use the GET DIAGNOSTICS CONDITION syntax to capture the exceptions during MySQL statement execution.

When locating the fault, we found that OceanBase did not support the GET DIAGNOSTICS CONDITION syntax. If we tried to work things out from the business, the workload of code edition would be overwhelming because 200 out of the 300 stored procedures use this syntax. So, the thorough solution would be adding a new feature to the database.

The new feature was developed in less than two months, and it helped us migrate data smoothly without modifying the business code.

05 Gains, plans, and expectations

OMS and OceanBase Cloud Platform (OCP) have been a great help in our data migration and O&M management, although it's true that we were bothered by some obstacles along the way, and the products were not flawless. However, OceanBase engineers and developers were quite responsive and supportive, and product flaws were patched up promptly before they were solved nicely. Ziel has learned a lot about OceanBase Database in our practice:

• To migrate tables without the primary key from a MySQL database, use OMS with DataX.

• Purchased systems often have design defects, such as inappropriate rules for naming table fields and indexes.

• Tables, especially crucial ones, should have the primary key where possible.

• Careful plans should be developed for the migration of important large tables to OceanBase Database, considering questions such as whether the tables should be partitioned, and whether a table group should be created for associated tables.

Next, we will use OMS to migrate the rest of our business systems to OceanBase Database, thus implementing data integration with the cross-border multi-cloud architecture.

Based on the above architecture and the HTAP capability of OceanBase Database, we will also build a real-time data analysis platform.

The cooperation with OceanBase has convinced us that this team is capable of getting more outcomes from their independent R&D to solve new problems, even those involving the database kernel. We are also looking forward to more and better features of OceanBase Database:

• Enhanced features that enable OceanBase Database to be more compatible with MySQL syntax and more adaptive to different business systems.

• A more powerful OMS that supports more data transmission and conversion scenarios, such as data migration from SQL Server and PostgreSQL to OceanBase Database, and data masking or conversion based on columns or SQL statements.