Zhong Ou Wealth Fund Distribution’s Path to Zero-Downtime Scaling with Distributed SQL

Zhong Ou Wealth Fund Distribution, a subsidiary of Zhong Ou Asset Management, has been a key player in wealth management since its establishment in 2015. The company offers comprehensive services through its app, including fund trading, big data fund selection, intelligent investment plans, and financial advisor consultations. Zhong Ou Wealth Fund Distribution serves a growing user base and ensures smooth investment experiences for its clients.

As part of the fast-expanding wealth management industry, Zhong Ou Wealth Fund Distribution faces increasing data and transaction volumes driven by the rising demand for personalized financial services. The company manages millions of daily transactions, making the need for a scalable, high-performance database crucial. 

The Challenge: Overcoming Scalability and Performance Bottlenecks in a Rapidly Growing Data Environment

As Zhong Ou Wealth Fund Distribution’s business expanded, its existing MySQL database infrastructure struggled under the increasing load. The rapid growth of data and transactions caused significant operational bottlenecks, impacting performance and scalability. The key challenges Zhong Ou Wealth Fund Distribution encountered included:

1. Data Growth and Sharding Complexity: With a significant increase in the volume of data, especially in single tables, Zhong Ou Wealth Fund Distribution frequently needed to implement database and table sharding to manage large datasets. This process was highly labor-intensive and prone to errors, often requiring frequent re-sharding as some tables grew beyond 500 million rows shortly after partitioning.
2. DDL (Data Definition Language) Challenges: Large tables made DDL operations incredibly time-consuming. For instance, a simple change to extend a field in a single partitioned table could take up to six hours, and with multiple tables involved, this led to several days or weeks of operational delays. Executing DDL operations during peak business hours was not feasible, further complicating database management.
3. Write Performance Limitations: Zhong Ou Wealth Fund Distribution’s traditional MySQL architecture, with a master-slave setup, limited write throughput. Only one master node could handle write operations. During high-load tasks like batch processing, rebalancing, or clearing, the system could not meet the business requirements for write speed and concurrency.
4. Maintenance and Scaling Issues: The complexity of sharding and the limitations of a single-node write architecture made scaling the system an ongoing challenge. This required significant manual intervention, often slowing down critical business operations and increasing the workload for the DBA team.

Original Data Architecture

Zhong Ou Wealth Fund Distribution’s original data architecture was built on a traditional MySQL primary-secondary setup. The system had one primary node responsible for handling all write operations, with two secondary nodes used for disaster recovery rather than read-write operations due to semi-synchronous replication delays.

1. Primary-Seconday Architecture: This setup could not efficiently distribute the growing volume of read and write requests. The primary node handled all write transactions, while the secondary nodes were used only for failover purposes, leading to low overall resource utilization.
2. Sharding for Large Tables: Zhong Ou Wealth Fund Distribution relied on database and table sharding to manage large datasets. However, the process was highly manual, and re-sharding was frequently needed as tables grew faster than anticipated. Aggregating data after sharding also created additional overhead, as it still needed to be synchronized with MySQL summary databases.
3. Limited Scaling and Performance: While the system was operational, it struggled with handling the rapid growth of business data and increasing transaction volumes. Scaling the MySQL system to meet high-concurrency OLTP requirements was difficult, leading to performance issues and reduced operational efficiency during high-load tasks.

This combination of data growth, performance limitations, and manual database management underscored the need for a more flexible, scalable, and high-performance distributed database solution, prompting Zhong Ou Wealth Fund Distribution to transition to TiDB.

The Solution: Implementing TiDB’s Distributed Architecture for Enhanced Scalability and Performance

Zhong Ou Wealth Fund Distribution migrated from its traditional MySQL setup to TiDB, a distributed SQL database, to address performance bottlenecks and scalability challenges. TiDB’s architecture, which separates compute from storage, provided the flexibility and high concurrency needed to support Zhong Ou Wealth Fund Distribution’s growing business and data requirements.

The solution was implemented in four key phases:

Phase 1: In-Depth Business Testing

Before committing fully to the migration, Zhong Ou Wealth Fund Distribution conducted thorough testing of TiDB in a parallel environment that mirrored their production setup. They tested different business systems using real production data to ensure smooth operation and optimized performance. This phase was crucial in identifying slow SQL queries and optimizing real-time and batch processing tasks, ensuring TiDB met performance expectations.

Phase 2: Data Synchronization

The transition to TiDB began with synchronizing data from Zhong Ou Wealth Fund Distribution’s MySQL database to TiDB using TiDB’s Data Migration (DM) tool. Real-time data migration was essential to maintain consistency across systems. Once synchronization was stable, all downstream MySQL synchronization links (including Canal and FlinkCDC) were replaced with TiDB’s TiCDC for output, ensuring seamless data flow between systems.

Phase 3: Application Go-Live

The next step involved migrating business applications to TiDB. A small downtime window was utilized to switch the data sources from MySQL to TiDB. After stopping the DM synchronization, the system’s data consistency was validated, and the applications were redirected to the new TiDB cluster. The process was simplified by reconfiguring the JDBC connection to point to TiDB’s HAProxy address.

Phase 4: Post-Go-Live Monitoring and Optimization

Following the go-live phase, Zhong Ou Wealth Fund Distribution closely monitored system performance and database operations. TiDB’s ability to scale and handle real-time traffic was tested, particularly in handling edge cases such as execution plan changes. Post-launch issues were addressed with manual intervention, fine-tuning the TiDB cluster to align with business requirements.

Architecture Overview

Zhong Ou Wealth Fund Distribution’s new TiDB architecture featured the following components:

• Storage Layer: This consists of three TiKV nodes with three replicas each for a total of nine TiKV nodes.
• TiDB Nodes: There are three TiDB nodes, with a mixed deployment model shared with the Placement Driver (PD). Additionally, two TiDB nodes with higher memory configurations were reserved for large, memory-intensive queries.
• TiFlash and TiCDC: Three TiFlash nodes were introduced for analytical workloads, and two physical machines were dedicated to TiCDC for real-time data synchronization.

This architecture allowed Zhong Ou Wealth Fund Distribution to eliminate bottlenecks in data sharding, DDL operations, and write performance. TiDB’s distributed nature allowed them to scale horizontally, meet high concurrency demands, and achieve significant improvements in resource utilization and overall system performance.

The Results: Tangible Improvements in Scalability, Performance, and Operational Efficiency 

After migrating to TiDB, Zhong Ou Wealth Fund Distribution experienced significant improvements across multiple areas of their business operations:

• Improved DDL Operations: TiDB’s architecture greatly optimized DDL operations on large tables. Previously, a single DDL change in MySQL could take up to 6 hours per table, but TiDB executed these changes more efficiently, reducing downtime and operational complexity.
• Increased Write Throughput: Zhong Ou Wealth Fund Distribution overcame the write limitations of their traditional MySQL primary-secondary architecture. TiDB’s separation of compute and storage enabled them to handle high-concurrency OLTP scenarios and boosted their system’s write performance, meeting the demands of high-traffic and batch-processing tasks.
• Streamlined Data Synchronization: Using TiDB’s DM and TiCDC tools, Zhong Ou Wealth Fund Distribution achieved real-time data synchronization, ensuring data consistency across systems. This also allowed seamless integration with their big data platform, enhancing data flow between crucial business applications.
• Resource Optimization: TiDB’s ability to scale resources on demand resulted in more efficient resource utilization. With a flexible setup for handling memory-intensive queries and resource isolation, Zhong Ou Wealth Fund Distribution reduced operational costs while maintaining system stability and high availability.

Future Plans

Zhong Ou Wealth Fund Distribution’s successful implementation of TiDB has paved the way for further integration into their core business systems. Soon, Zhong Ou Wealth Fund Distribution plans to:

• Expand TiDB to Core Business Systems: Following the success of the initial systems, Zhong Ou Wealth Fund Distribution intends to migrate critical applications, including their portfolio advisory, marketing, product, user, and trading systems, to TiDB. These migrations will allow Zhong Ou Wealth Fund Distribution to leverage TiDB’s high availability, scalability, and real-time processing capabilities in mission-critical operations.
• Introduce TiProxy for Seamless Upgrades: Zhong Ou Wealth Fund Distribution aims to integrate TiProxy into its infrastructure to replace HAProxy, enabling it to perform system upgrades without application downtime. TiProxy will also improve traffic management and enhance the flexibility of its architecture.
• Centralize Management Tools: Zhong Ou Wealth Fund Distribution hopes to streamline its operational processes by integrating TiDB’s management tools (such as Dashboard, DM-web, and TiCDC) into a centralized platform, simplifying operations and maintenance for their teams.
• Leverage AI-Powered Cluster Inspections: Zhong Ou Wealth Fund Distribution envisions incorporating automated inspection features to reduce manual workload. AI-driven insights and optimization recommendations for their TiDB cluster would further enhance system reliability and performance, ensuring smooth operations as the company scales.

These plans will solidify TiDB as a cornerstone of Zhong Ou Wealth Fund Distribution’s data infrastructure, enabling them to maintain high performance while scaling efficiently and supporting future growth.

Conclusion 

Zhong Ou Wealth Fund Distribution’s journey to adopt TiDB has not only addressed their immediate challenges with data growth and operational efficiency. It has also positioned them for future success in an increasingly competitive financial landscape. By transitioning to a distributed SQL architecture, Zhong Ou Wealth Fund Distribution has experienced significant improvements in write performance, reduced operational burdens, and enhanced overall business efficiency. The implementation of TiDB has enabled them to effectively manage their growing data needs while ensuring high availability and reliability.

As Zhong Ou Wealth Fund Distribution continues to explore new features and expand the application of TiDB across its core business operations, the potential for transformative growth remains vast. Organizations looking to optimize their database management solutions can learn from Zhong Ou Wealth Fund Distribution’s success and TiDB’s remarkable capabilities.

Experience the transformational impact of TiDB on database management—discover how it can redefine your organization’s data strategy and drive success in today’s digital economy!