How Trip.com Solves MySQL Sharding Problems with OceanBase

Photo by Saif71.com on Unsplash

As an internationally renowned online travel service company, Trip.com has successfully integrated cutting-edge technologies with the traditional tourism industry, providing all-around travel services, such as hotel booking, ticket booking, resort booking, travel management, and travel information provision, to more than 20 million members.

With the rapid annual growth of app users, the business data of Trip.com also experienced a fast increase, getting close to the storage limit of its standalone databases. Therefore, Trip.com gradually migrated its business modules to a MySQL database based on PC servers.

After years of operation, Trip.com found the following problems of its MySQL database sharding solution:

1. Stability risk due to online DDL operations

To eliminate this risk, companies must use external tools to perform operations like replication and renaming, which causes issues such as a long execution period (in weeks for large tables) and larger storage occupation (at least 120% space increase to store modified tables).

2. Multi-node data inconsistency due to binlog-based data replication

This issue seems to be an unsurmountable challenge in the industry. Existing solutions, such as MySQL Group Replication (MGR) and binlog-based replication, also have proven potential risks. Therefore, Trip.com deployed the disks of all their online database nodes in RAID 10 arrays.

3. Limited capacity of a single data table

Trip.com did not apply the sharding solution company-wide because the limitation of the computing and storage resources of a single server was still there, even after tables were partitioned, not to mention some well-known issues of sharding.

4. Business impact caused by O&M activities, such as primary/standby switchover and data center relocation

Trip.com developed an integrated O&M platform, which automatically releases requests after various checks during the process of data center relocation and switchover. However, due to the inherent defects of MySQL data replication, these O&M activities were not transparent to the business, and each time we performed such operations, we were still worried about the risks.

Trip.com provided standard MySQL configurations for different business modules, which resulted in low overall utilization of the online resources because business modules use resources in different ways. For example, some modules need more computing resources, while others need more storage resources. Trip.com could not find a one-size-fits-all solution to improve resource utilization.

How did Trip.com solve the issues by using OceanBase Database?

With the native distributed capability of OceanBase Database, Trip.com easily eradicated the potential risks of MySQL databases and enjoyed additional benefits.

How did OceanBase Database improve the performance of online DDL operations?

Online DDL operations were nightmares for MySQL beginners. For example, to add a Status field to the group message table of the IM system without affecting the system stability, Trip.com had to create a new table, which introduced unnecessary JOIN queries. OceanBase supports multiple schema versions and online DDL isn’t a problem anymore. We can finish the operation within seconds without locking the table.

Operations such as adding or deleting indexes are also safe. Simply put, OceanBase Database stores some data of a table in memory and some on disks. When you add or delete data, your operations take effect on the data in memory in real time. This is because the amount of data in memory is not very large and can be quickly processed to return the results. The data on the disk is handled by background tasks, which do not affect the business because most operations are performed on near real-time data.

What is the maximum single-table capacity of the OceanBase Database?

We do not have a precise answer to this question because Trip.com has not reached the upper limit of the OceanBase Database.

Taking the IM business module as an example, the group message table occupies about 800GB of storage space in two months, which is close to the maximum single-table capacity of a MySQL database. The same data occupies about 200GB in an OceanBase database.

The answer is the highly efficient encoding and compression mechanisms of the OceanBase Database. For example, before writing the data to a disk, OceanBase Database performs dictionary encoding to encode the columns and greatly reduce their space occupation. After encoding, mild compression algorithms are used to make the data more space-friendly.

In addition, the encoded columns are stored in memory and are directly used for processing without decoding. So, the highly efficient encoding algorithms have brought both cost and performance benefits to OceanBase Database.

Moreover, OceanBase Database supports native partitioned tables, which allows users to organize their data by using more partitioning methods.

How to perform leader/follower switchover in OceanBase Database? Is it safe?

After the migration to OcenaBase Database, the leader/follower switchover is no longer a concern of Trip.com. This is because OcenaBase Database is built in a shared-nothing architecture, where all replicas are equivalent nodes and the data consistency between the majority of replicas is guaranteed at any time. Simply put, the leader/follower switchover is a process where the role of a replica changes from a follower to a leader. Therefore, this operation is rather safe.

Furthermore, OceanBase Database provides OBProxy at the access layer to automatically route the database access requests. Combined with its existing components at the data access layer (DAL), Trip.com can stably and easily switch traffic with little to zero business interruptions.

Thanks to the abovementioned native capabilities of OceanBase Database, Trip.com has fundamentally solved almost all the hidden risks of MySQL databases.

Additional benefits

Resource pool and hybrid database deployment​

The usage of database resources and the traffic peak varies from one business module to another. In the previous MySQL databases of Trip.com, resources were configured to meet the peak traffic of each business module, which resulted in low average resource utilization. However, Trip.com could not find a convenient scaling method to improve resource utilization because of the limitations on data replication, switchover efficiency, and stability of MySQL databases.

Thanks to the multitenancy and resource isolation capabilities of OceanBase Database, Trip.com has configured a large resource pool in OceanBase Database and migrated its business modules from MySQL instances to OceanBase tenants. In other words, Trip.com has deployed a hybrid OceanBase cluster of multiple database instances.

• Different resource isolation strategies are configured for each tenant to ensure proper resource allocation.
• Given the fact that the traffic of business modules peaks at different points in time, tenant resources are quickly scaled up and down, so that the cluster can stably take care of multiple business peaks with the overall cluster resource configuration remaining unchanged.
• To deal with a fast-growing business module, Trip.com can quickly scale out the corresponding database instance by increasing the number of resource nodes in it.
• The node scaling method allows Trip.com to easily control the overall resource usage of the database cluster. In addition, the scaling is performed online and does not necessarily require nodes with equivalent resources, making it flexible for Trip.com to use resources.

HTAP capability speeds up business response​

The hybrid transaction and analytical processing (HTAP) capability of OceanBase Database has brought new possibilities for the business of Trip.com. It can perform online transaction processing (TP) while processing ad hoc queries in the same database cluster. Analytical processing (AP) queries are executed where the data is stored, the data is fresh and the service efficiency is much higher than that based on the extract-transform-load (ETL) process. In addition, the storage costs are effectively reduced because no data replicas are created.

To deal with the HTAP workload, OceanBase Database provides effective resource isolation and limits for different business modules, so that the introduction of AP tasks does not lead to resource contention with TP tasks or a downgrade of service quality.

How to smoothly migrate data to OceanBase Database?

Use OMS to migrate data​

Trip.com is meticulous about each database migration project because data is the vitality of the company and has to be strongly consistent.

OMS ensures that users can easily perform the following data migration operations:

1. Full migration. OMS fully migrates all data and schemas in a MySQL database to a MySQL tenant of OceanBase Database and performs full data verification when the migration is completed. Each data inconsistency item is indicated, with modified SQL statements provided.

2. Incremental migration. Before the full migration is initiated, OMS pulls the binlogs of the source MySQL database and locally replays the binlogs when the full verification is completed to ensure zero loss of the incremental data during the full migration. OMS also performs incremental data verification to ensure the data consistency of the incremental migration.

3. Reverse migration. If the incremental migration is completed with verified data consistency, you can switch your business to OceanBase Database. After that, OMS can migrate the incremental data in OceanBase Database back to the MySQL database. This fail-safe feature ensures that you can migrate data back to the source database while the data is being migrated to the destination.

4. Data synchronization. OMS can synchronize data from OceanBase Database to message queues, such as a Kafka message queuing system, to support subsequent big data analysis and machine learning.

Working together​

OceanBase Database solves many stubborn MySQL issues that have bothered Trip.com for a long time, such as the low performance of online DDL operations and large table queries, data inconsistency, and data security during leader/follower switchover.

Nothing is a silver bullet in the world of software. OceanBase Database is no exception. To use the software well, it is necessary to first understand its design philosophy and architecture and get familiar with the strengths and weaknesses of each feature, which is more demanding for database engineers. At the end of the day, OceanBase Database is not a fully autonomous system and relies heavily on manual operations, especially in the design of table schemas and indexes, and query optimization.

A high degree of familiarity with the features of OceanBase Database and the business modules of Trip.com is great. However, only by proving theoretical knowledge with hands-on experience, can the technical team of Trip.com get the point. As Trip.com cannot test OceanBase Database by using its online business systems, the technical teams of both sides have jointly built the following systems:

1. SQL replay system. Based on its business and database trace system, Trip.com has replayed all business SQL statements in OceanBase Database to verify its adaptability. The company has also performed stress tests by using methods such as traffic replication and multiplied replay, to ensure that OceanBase Database can meet its business needs without causing interruptions.

2. SQL trace system. The business trace system of Trip.com generates only a trace record for SQL execution, without indicating details of the execution in the database. Given the characteristics of the distributed architecture and data routing, it is not easy to get complete SQL trace information and identify the real causes of performance bottlenecks. Therefore, the two sides have jointly developed an SQL trace system, which allows Trip.com to trace the execution process of each SQL statement, find the real bottlenecks, and then perform subsequent optimization.

3. Unified log analysis system. Unified analysis of logs is necessary for distributed systems like OceanBase Database to ensure service stability. The identification and association of key log information help detect errors early. In addition, log association also helps users better understand the operation process of a distributed system.

After the joint development of the preceding systems, Trip.com is confident that OceanBase Database applies to its business and has learned more about the workflow of the distributed system. The joint development has not only increased the skills of the technical team of Trip.com but also facilitated SLA fulfillment.