Hardware Costs Down 52%: How 99Bill Reduced Costs and Boosted Efficiency After Adopting OceanBase

Editor’s note: In the mobile internet era, online payment and mobile payment have become everyday choices for the public. In China especially, beyond the two giants Alipay and WeChat Pay, many third-party payment companies provide convenient payment services to merchants and users — for example, finance-oriented payment companies such as UnionPay Merchant Services and 99Bill, and credit-intermediary payment companies such as Lakala and Jialian Pay. How do you guarantee the correctness, real-time nature, and security of transactions? It is closely tied to the database service at the bottom of the tech stack. This article answers these questions by sharing 99Bill’s DB architecture evolution and its upgrade solutions and lessons.

The Historical Evolution of 99Bill’s DB Architecture

As a leading independent third-party payment company in China, 99Bill offers a rich portfolio of products, including but not limited to RMB payment, foreign-card payment, collection/charging services, VPOS service, and group account management, and is currently expanding its cross-border RMB settlement business. It supports a variety of terminals — internet, mobile, telephone, and POS — and provides secure, convenient, and confidential integrated electronic payment services to all kinds of enterprises and individuals. 99Bill once earned the “Payment and Clearing System Security Technical Assurance Level 1” certification issued by the China Information Technology Security Evaluation Center, as well as international PCI security certification. This stems from the business’s persistent pursuit of technological advancement and security on the back end — for instance, the database, dubbed the “heart of the system,” has gone through three transformations over the company’s 20 years.

(1) The Initial Stage

In the early stages of the business, 99Bill used a MySQL architecture, as shown below.

The advantages of this architecture were:

• It used Keepalived, which basically guaranteed database high availability;
• It had slaves, which basically achieved read-write splitting;
• At a small data scale, MySQL could handle the load comfortably.

However, Keepalived’s inherent split-brain problem could not be solved. For example, once a split-brain occurred, it could easily cause application connection errors, data inconsistency, and other problems — failures intolerable in the payment domain. At the same time, because the slave served as the standby for M2, if M2 had an exception, it was hard for the slave to guarantee data consistency.

(2) MySQL Pain Points

As the company’s business volume surged, the original two-primary-one-standby MySQL architecture could no longer meet business needs. For example, at the time the largest single core table reached 4 TB; database performance and operability struggled to keep up and affected business expansion. So we introduced MyCAT as the sharding middleware, along with MHA as the MySQL high-availability solution (with the underlying MySQL replication using the GTID mechanism).

This architecture could briefly break through the bottlenecks of a single MySQL instance in performance, capacity, and high availability, and the change had almost no impact on the application. The application only needed to connect to MyCAT as a single entry point and interact using standard SQL and the MySQL protocol. MyCAT parsed the SQL and routed requests to the backend physical databases (shard nodes) according to predefined sharding rules (such as modulo, range, hash, or date), then aggregated the results and returned them to the application. Because the change had low complexity, developers didn’t have to invest much. But the drawbacks were also significant, for example:

• Weak distributed transaction support; XA transactions performed poorly. MyCAT supports distributed transactions based on the XA protocol, but executing them across multiple shard nodes incurs enormous performance overhead, holds locks for a long time, and seriously affects system throughput.
• Lack of an eventual-consistency solution. For scenarios requiring cross-shard strong consistency, MyCAT has no built-in, mature eventual-consistency compensation mechanism.
• Poor cross-shard JOIN/subquery performance. Complex association queries (JOINs) and nested subqueries across multiple shards require MyCAT to pull large amounts of data from many nodes and process it in memory, which is extremely inefficient and prone to memory overflow.
• Inflexible sharding rule selection and management. Choosing the right shard key and sharding rules is crucial and challenging. Once an initial rule is poorly chosen, or business changes make the rule unreasonable, resharding the data is an extremely painful operation with relatively high risk.
• Incomplete SQL compatibility and debugging. Although MyCAT is compatible with most of the MySQL protocol and SQL, there may be compatibility issues or parsing errors with certain specific syntax, functions, or complex statements.
• Data migration and scaling require manual intervention. The initial data import and subsequent node scale-in/scale-out usually require manual data migration and rebalancing — a complex process that may affect online services.

It’s worth noting that, in terms of high availability, compared with the earlier two-primary-one-standby architecture, MHA can automatically detect a primary failure and, after confirming the primary is unavailable, complete the failover process within seconds — including selecting the optimal replica, applying the differential relay log, promoting it to the new primary, and switching the other replicas — minimizing downtime. In addition, thanks to GTID, it can also minimize data loss.

But the MHA architecture carries a potential split-brain risk. If the MHA Manager can’t accurately determine the primary’s status (for example, a network partition cuts off the Manager’s connection to the primary, but the primary is actually still running and accepting writes), it may wrongly trigger a failover. At that point two “primaries” would accept writes at the same time, causing serious data inconsistency. Moreover, the MHA Manager node is a single point that can’t make itself highly available; if the Manager node goes down, the entire HA management function (automatic failover) fails. And the MHA management node needs to be able to SSH into all MySQL nodes without a password to execute commands and copy log files, which brings additional security configuration and management burden.

(3) Selecting and Requirements for the New DB Architecture

As the business surged again, the MySQL + MyCAT architecture gradually faced challenges in cost, performance, and high availability.

1. High server cost.

In the MySQL + MyCAT architecture, we generally used two sharding modes: hash sharding across 120 schemas, and range sharding by time into different schemas. In our initial architecture, the 120 shards were distributed across 24 instances, with 4 instances per DB server and 5 schemas per instance; with the MHA architecture at the bottom, this required 18 DB servers. Adding in the MyCAT servers, this became a huge cluster. Server costs kept climbing as the business data volume grew, and under the company’s cost-reduction-and-efficiency mandate, it was clear this architecture couldn’t last.

2. Hard to use resources precisely.

The underlying I/O, CPU, and memory were all in shared mode. Even though MySQL 5.7 could adjust innodb_buffer_pool_size online, it still couldn’t make smarter adjustments. Once a workload went live, it was hard to dynamically adjust and allocate on demand according to the actual business growth.

3. Hard to pinpoint issues quickly.

As you can see from the MyCAT architecture diagram above, from the application to the load-balancing layer to MyCAT to MySQL is a very long path; if anything goes wrong at any link, it’s hard to pinpoint quickly.

4. Dangerous online DDL.

Even though online DDL improved after we upgraded MySQL to 8.0, in a sharded setup — even with scripts and automated operations — once a large table undergoes a DDL operation, there is still the risk of primary-standby replication lag and DB locks causing fluctuations in the upper-layer business.

5. High-availability problems.

Once MySQL fails over while there is primary-replica lag, MHA can’t guarantee against data loss.

Choosing OceanBase: Five Migration Lessons

Given the various problems we encountered in production, replacing the architecture became imperative. Combining our business situation with our research on open-source databases in the market, we noticed that the OceanBase Community Edition fit our requirements for the new architecture very well, especially in the following five aspects.

• Low cost: an LSM-Tree-based high-compression engine can reduce storage cost by 70%–90%; native multi-tenant architecture means a single cluster can serve multiple independent workloads with data isolation between tenants, reducing deployment and operations cost.
• High availability: a pioneering “three regions, five centers” disaster-recovery architecture establishes a new standard for lossless disaster recovery in the financial industry. It supports same-city/cross-region disaster recovery and multi-active across regions, meeting the financial industry’s level-6 disaster-recovery standard (RPO=0, RTO<8s).
• High compatibility: highly compatible with MySQL, covering the vast majority of common features, supporting advanced features such as procedural language and triggers, and providing automatic migration tools that support migration assessment and reverse synchronization to safeguard data migration. It can support replacing the core scenarios of critical industries such as finance, government, and carriers.
• Horizontal scaling: enables transparent horizontal scaling and supports fast scale-out and scale-in of workloads, while achieving high performance through a quasi-in-memory processing architecture. It supports clusters of more than several thousand nodes, a maximum single-cluster data volume of over 3 PB, and a maximum single-table row count in the trillions.
• Secure and reliable: OceanBase has IT-localization qualifications, a complete role and permission management system, full-link transparent encryption for both data storage and communication, support for SM cryptographic algorithms, and has passed the MLPS Level 3 dedicated compliance assessment.

The migration process was fairly smooth, so I won’t go into detail here, but I’ll share a few migration lessons.

First, every table must have a primary key. Each table can have at most one set of primary key columns, the number of primary key columns cannot exceed 64, and the total length of the primary key data cannot exceed 16 KB. If a table has no primary key, OceanBase’s migration tool OMS will not be able to migrate it properly.

Second, foreign key constraint issues. OMS has limitations when migrating MySQL tables with foreign keys. If a MySQL table with foreign key constraints needs to be migrated to OceanBase, you must move the foreign key logic into the application beforehand.

Third, design partitioned tables sensibly. In OceanBase, partitioned tables are far more powerful in performance and functionality than in MySQL, so when migrating a large MySQL table to OceanBase, consider whether to convert it into an OceanBase partitioned table. However, for partitioned tables under OceanBase’s MySQL tenant type, global indexes are strongly discouraged, because if a partitioned table under a MySQL tenant has a global index, dropping or truncating a partition will invalidate the global index.

Fourth, parameter compatibility issues. After migrating workloads to OceanBase, be sure to pay attention to modifying the relevant parameters. Here are two common examples.

ob_query_timeout: this parameter is OceanBase’s SQL timeout, which defaults to only 10s, so be sure to adjust it for your own system.

explicit_defaults_for_timestamp: in MySQL, this system variable controls the default behavior of TIMESTAMP columns, mainly affecting NULL handling, auto-update behavior, and default-value settings for TIMESTAMP columns. In MySQL 5.7 this parameter defaults to OFF, but in OceanBase it is ON, so it’s recommended to adjust it based on your source database.

Fifth, the issue of using OMS to migrate a middleware architecture. In practice, OMS can almost perfectly handle migration from MySQL to OceanBase, but OMS synchronizes data based on MySQL’s binlog. A sharding middleware is merely a routing and SQL-parsing tool that can’t provide a binlog-like incremental data source for OMS’s incremental synchronization and reverse synchronization. Therefore, we modified the middleware architecture to be migrated, as shown below:

As you can see from the figure:

• When OMS synchronizes data forward to OceanBase, it synchronizes directly from the underlying MySQL nodes of the sharded setup, consolidating the data from all shards at the OceanBase layer. Note that because the source table names and target table names are the same, when OMS validates after consolidating the data, you must ensure the source table has not only a primary key but also another unique key, so that data verification and comparison can be performed.
• Because OMS did not yet support MyCAT at the time of our migration, we used obbinlog for reverse synchronization, then used Canal or Flink to synchronize the data back to MyCAT after the forward switchover was complete. MyCAT, according to its own sharding rules, synchronizes the data back to the MySQL nodes in reverse.

First Taste of OceanBase: Hardware Costs Down 52%

Because 99Bill has many business lines, this round only migrated the account, risk control, micro-transaction, monitoring, and some big-data workloads to OceanBase. After migrating from MyCAT to OceanBase, the business’s cost reduction and efficiency gains were significant — hardware costs dropped by 52%.

In addition, high availability, resource utilization, operations, and more all improved:

• We once relocated and upgraded a single node in the cluster with no impact on the business.
• Aside from storage space, the underlying hardware resources can all be made independent, so we can flexibly scale out and scale in according to business needs.
• OceanBase’s operations platform OCP is powerful: beyond standard features such as chart monitoring, bad-SQL monitoring, and SMS alerts, it can also handle emergencies by dynamically adjusting tenant and cluster resources. If a sudden traffic spike or bad SQL causes a problem, you can use the SQL throttling feature. Horizontal scale-out has no impact on the application at all, and there’s even an excellent end-to-end link-tracing feature.
• For routine DB releases, OceanBase’s online DDL is far more powerful than MySQL’s, basically achieving zero business impact.

OceanBase currently supports 99Bill’s business systems running stably, with no performance jitter. In the future, once its reliability and performance are further validated, we are considering continuing to explore more of OceanBase’s features and gradually migrating more business system databases to OceanBase.

Finally, I’d like to recommend the WeChat official account “Lao Ji’s Tech Talk,” run by Lao Ji, the OceanBase open source lead. It continuously publishes all kinds of technical content related to #databases, #AI, and #tech architecture. Friends who are interested are welcome to follow!

“Lao Ji’s Tech Talk” hopes not only to keep bringing you valuable technical insights, but also to contribute to the open source community together with you. If you appreciate the OceanBase open source community, please light up a little star ✨! Every Star you give is motivation for our efforts.