Cross-border e-commerce data fusion practice｜OceanBase helps Zhiou Home Furnishing to build distributed cross-border e-commerce

Zhiou Home Furnishing Co., Ltd. is a leader in China's cross-border e-commerce industry. Its products have entered more than 50 countries and regions such as Western Europe, North America, and Japan. The overall customer evaluation ranks in the forefront of each sales channel. At present, it has become the largest home furnishing seller on Amazon in Europe and one of the largest B2C cross-border e-commerce export brands in inland China. In 2021, the revenue will be nearly 6 billion, a year-on-year increase of 100%.

With the high-speed development of cross-border business, the requirements for the carrying capacity of the back-end business system are constantly increasing, especially for the stability, continuous availability, security and other aspects of the system. Zhu Hui, chief architect of Zhiou Home Furnishing, was specially invited to sort out some challenges and corresponding plans based on the current business situation, and tell everyone the story of Zhiou Home Furnishings launching OceanBase.

Zhu Hui: The first generation of Internet practitioners, from microcontroller to cloud, from CGI to MicroService, witnessed and participated in the development of the industry. Achieved one leap from zero to one, and completed one innovative project after another.

The current state of the business

• There are many outsourcing systems, and the technical architecture is quite different. The current development languages are java, php, go, multiple systems are intertwined, and the business complexity is high;

• There are many types of databases. MySQL, sqlserver, mongodb, postgres, coupled with uneven business systems, make daily operation and maintenance management more complicated and require high requirements for operation and maintenance personnel;

• Some business systems use MySQL deeply, rely heavily on database functions, and use a large number of stored procedures and triggers. According to incomplete statistics, there are more than 1,200 stored procedures in one of the business libraries, and one procedure code has as many as 6,000 lines. .

• Database operation and maintenance management costs are high. At present, database instances are distributed on different platforms such as aws Europe, aws North America, Alibaba Cloud Hong Kong, Alibaba Cloud Europe and self-built IDC computer rooms. The database instances, architectures and resources in different regions are different; the differences between different regions and different time zones are invisible. Increase the requirements and costs of operation and maintenance management;

• Database instances are mainly traditional single instances, which cannot be scaled horizontally to meet the needs of continuous business growth.

challenges

Based on the above status quo, the challenges faced by Zhiou Home Furnishing are:

• Business promotion has large traffic and high concurrency requirements. The current database is basically a single-instance traditional database. With the growth of business, it cannot be expanded horizontally. The promotion of activities and spikes can easily lead to paralysis of the business system;

• The amount of data grows rapidly, and it is difficult to support a single instance of traditional databases, and storage capacity needs to be expanded on demand;

• With the rapid development of business, the system has increased requirements for database availability, security, reliability, and stable operation and maintenance. It is difficult for single-instance database RTO and RPO to meet the needs of business development;

• The business platform is distributed all over the world, data needs to be integrated and aggregated to make analysis decisions, and the database requires HTAP function;

• Independent and controllable requirements for localization of information systems;

• The company needs to increase revenue and reduce expenditure, reduce costs and increase efficiency.

Why choose OceanBase

Starting from the actual situation, Zhiou Home Furnishing has compared and understood the current mainstream distributed databases, and finally found that OceanBase is relatively complete in terms of database product functions and product systems, and can well solve the problems in the current business structure. Pain points.

With complete database functions and supporting development, operation and maintenance management tools

Elegant kernel architecture;

① Synchronizing data based on the paxos protocol, using Paxos to replicate data between nodes is better than Raft

② Multi-tenant management mode, which can well isolate system resources according to tenants. Each tenant can designate leader nodes individually, which can make full use of the CPU and IO isolation of each node.

③ Partition and table group functions can be designed on demand to reduce distributed transactions

Compatible with SQL protocol: Compatible with MySQL and Oracle at the same time, especially compatible with stored procedures, triggers and custom functions, the existing business system can be smoothly migrated without intrusion to the business system;

Storage: Self-developed distributed storage engine with high data compression ratio, 5-7 times compression ratio compared to MySQL;

Data copy: divided into all-round (read-write), encrypted voting copy, log type, read-only type, and the corresponding architecture can be selected according to different business performance and SLA requirements;

Distributed transaction: read-write separation, DML operates memory, and read transaction operates baseline data;

Operation and maintenance management: rich performance views, four product families integrate development, deployment, data migration, and automated operation and maintenance;

Data migration: Based on the OMS tool, one-stop data migration can be done according to the map, which integrates full, incremental, data verification, and reverse synchronization links.

performance adaptation

In terms of performance, we perform corresponding performance verification based on OB to check whether the corresponding performance can meet the needs of the business. The following are the relevant benchmark tests:

• cpu:mem:disk=16c:100G memory:120G ssd

• Environment and version information: Proprietary cloud deployment version 3.2.2

• Number of tables: 32

• Single table data volume: 1kw

• Thread test time for each scenario: 5min

• Test tool: sysbench

• Related commands: 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 in the same computer room, three data nodes, and one log node, as follows

• Test Results:

• OS monitoring:

As can be seen from the above deployment architecture, OceanBase is very flexible in the selection of replicas, and the replica type can be selected according to different business requirements. The corresponding database base test IO is in an ideal state, and the corresponding TPS and QPS are in line with expectations.

Problems encountered and solutions

We encountered many problems in the process of migrating from MySQL instance to OceanBase. Although these problems brought us a lot of trouble, they have been well solved with the technical support developed by OceanBase. Here are a few comparisons. Typical question.

Q1: In MySQL mode, OMS does not support table migration without primary key or unique key.

This issue is clearly addressed in the OMS product documentation. Since many of our systems are purchased from outsourcing, many tables have problems such as primary keys and unique keys, and the amount of data in some tables is not small.

How to solve this problem, we learned from our communication with OceanBase production and research colleagues that for this kind of table without primary key or unique key, in Oracle mode, this kind of table can be supported, the reason is because OMS automatically helps to add a hidden primary key, which is deleted when it is officially switched online. In MySQL mode, after the full migration is completed, the performance of incremental data synchronization will be very poor, and it is even impossible for large tables. Class tables, currently can only be processed by data transfer tools like left datax.

Solutions for datax+OMS——
quote
Table Structure Migration: Manual Export Import Processing
quote
Data migration: use datax for full data migration
quote
Data verification: OMS supports the step of selecting data verification separately
quote
Incremental data synchronization: this step cannot be done
quote
Fallback: reverse sync using datax+OMS

Due to the large number of tables, the configuration files of datax are configured in units of tables. Here we DIY a script to automatically generate configuration files, and then perform migration tasks in batches.

At the same time, reverse synchronization is also a full-scale process. In this process, we measure the forward and reverse times in advance, as well as the verification time of the business. The time window of the change is: forward + reverse + 2 times the verification time .

Q2: For tables with data migrated, an error will be reported if the index name is the same as the table name, as defined in the following table:

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

For the migrated table, OMS does not support when the column name is the same as the index name. The temporary solution of OceanBase is to change the index name, and the final solution is to support the development of OMS product functions.

Within two days after the feedback, the product quickly solved the problem with high efficiency.

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

In one of our application systems, a large number of stored procedures are used, and many of them use the GET DIAGNOSTICS CONDITION syntax to obtain the exception capture of the MySQL execution process.

After inspection, it was found that the kernel does not support GET DIAGNOSTICS CONDITION exception capture. If it is solved from the business side, more than 200 of the more than 300 stored procedures use this syntax, and the amount of code has changed a lot, so the most fundamental solution is The solution is functional support from the kernel.

Since the OceanBase kernel does not support exception capture processing for stored procedures, functional improvements need to be made on the kernel. After more than a month of research and development, the product function research and development is completed, and the business code does not need to be adjusted accordingly, and a smooth migration can basically be achieved.

Harvest, Planning and Expectation

Although OMS and OCP have brought great convenience in data migration and operation and maintenance management, we also encountered many problems in the actual process, especially some product functions still have some defects. With the support of OceanBase engineers and R&D team, these problems were responded to in the first time, and the deficiencies of the product were followed up and dealt with in a timely manner, and finally got a good solution. The road, the feeling of another village, let Zhiou know and gain the application and practical experience based on OceanBase:

In the process of data migration, OMS can be used in conjunction with datax, which can quickly solve the migration of tables without master creation in MySQL mode;

There are many irregularities in the design of the outsourcing system, such as the naming of table fields and indexes;

For the table structure, there should be a primary key design as much as possible, especially some core tables;

Migrating to OceanBase requires careful planning for the core large tables, such as whether to partition, and whether to consider table groups with other related tables;

Next, we plan to continue to use OMS tools to migrate other business systems to OceanBase to achieve the goal of cross-border multi-cloud data fusion, as shown in the following figure:

On the above basis, a real-time analysis business platform is constructed based on the HTAP capability of OceanBase.

Of course, after this battle, we are more convinced that OceanBase can better utilize the advantages of independent research and development on the road of 100% self-developed research and development, and problems can be solved quickly, even if it is a kernel-level problem, we are also eagerly looking forward to this. , OceanBase has improved its products and functions to a higher level:

• The core functions of OceanBase have been further improved and strengthened, and it is more perfectly compatible with MySQL syntax and is more friendly to business adaptation.
• OMS is capable of more scenarios in the process of data transmission and transformation, such as migrating from other databases such as sqlserver and postgres to OceanBase; it supports column-based or SQL-based data desensitization or transformation.

Postscript (Architect's perception)
Ocean Base Architect He

As a leader in cross-border e-commerce, Technology Co., Ltd. resolutely transformed and reformed the current system and architecture under the background of the explosive business growth and the great challenges brought by the back-end IT system and operation and maintenance. . In just two months, 6 business systems were migrated to OceanBase.

Although benefits from the OMS migration tool provided by OceanBase to a certain extent, it mainly relies on the collaboration of the R&D and operation and maintenance teams of ZEO, as well as the joint efforts and support of the R&D force of OceanBase products. Whether it is testing and verification before migration, compatibility and adaptation, or cutting in the final official launch, they maintain a serious and responsible attitude, respond positively, and be meticulous. Make sure to migrate multiple business systems to OceanBase within the set time. During this time, every day we work with everyone is a matter of picking up and down, harvesting and growing.

It is true that we have achieved certain achievements in stages, but the road of Xiongguan is like iron, and now we are starting from scratch. The future planning and prospect of Zhiou has given us the pressure and motivation to move forward. It is expected that with the support of industry and research students, OceanBase can better empower Zhiou and other companies like Zhiou. At the same time, it is also expected that Zhiou can use OceanBase to build a global cross-regional data fusion management for its own business. Cross-border distributed e-commerce business provides stable system support and provides successful cases for overseas enterprises16242c3906bf0b