Elevate modern apps with TiDB.
Yunsheng Haihong, a leading technology company in the retail sector, has been at the forefront of creating innovative retail experiences since its inception. The company operates the Yunhai Retail System, a comprehensive platform that supports multi-channel retail services for sports footwear and apparel, serving nearly 10,000 offline stores and millions of online members across China. Yunsheng Haihong has recognized the need for a robust, scalable infrastructure to handle the growing complexity of its data and operations.
Xu Ting, a database architect at Yunsheng Haihong, shares how the company transitioned from a centralized to a distributed database architecture.
The Challenge: Navigating Legacy Constraints and Scaling for the Digital Retail Era
Legacy System Issues (Pre-2016)
Before 2016, Yunsheng Haihong operated on a siloed architecture where each regional system functioned independently. Business data from these regional systems was uploaded to the headquarters at the end of each day.
While this setup supported basic operations, it created significant delays in data visibility and hindered the ability to perform real-time cross-regional inventory management. Decision-makers could not get a unified view of inventory or sales trends, which became a critical drawback as the business grew in complexity.
Furthermore, as mobile internet and digital commerce gained momentum, Yunsheng Haihong’s existing architecture struggled to meet the increasing demands for real-time data aggregation and analytics. Customers expected seamless shopping experiences across channels, but the outdated architecture could not support dynamic features like real-time inventory tracking or instant transaction updates. This limitation highlighted the urgent need for a scalable, agile system to integrate data from all regions and effectively support digital business requirements.
Sharded Architecture Challenges (Post-2016)
To address the limitations of the legacy system, Yunsheng Haihong adopted a MySQL-based sharded architecture with MyCAT as the middleware.
While this architecture brought improvements, it also introduced new challenges:
1. Complex Sharding Rules
- MyCAT required intricate, static sharding strategies for splitting data by region and business unit.
- The static nature of these rules meant that manual maintenance was needed whenever business growth or organizational changes occurred. For instance:
- New tables had to be created to accommodate new business units.
- Data adjustments required extensive manual intervention.
- Table migrations became time-consuming and risky, with high potential for errors.
2. SQL Query Limitations
- MyCAT struggled to handle complex SQL queries involving joins or aggregations.
- MySQL underperformed when dealing with large tables, making analytics and reporting cumbersome and slow.
3. High Maintenance Costs
- The system relied heavily on Otter for real-time data synchronization between MySQL and Oracle, with over 100 synchronization channels.
- Any changes to the source data, such as DDL (schema changes) or large DML (data updates), caused frequent synchronization disruptions.
- These issues led to increased labor costs and required constant monitoring by the database team.
Oracle Bottlenecks
Despite its role as the aggregation database, Oracle faced significant challenges as Yunsheng Haihong’s data volumes grew:
- Storage Scalability Limits
Oracle’s single-instance architecture hit its capacity limits, making storing the rapidly expanding data generated by Yunsheng Haihong’s growing business increasingly difficult. - Degraded Query Performance
As the data size increased, complex SQL queries—vital for generating detailed business reports—became slower and less efficient. This affected the ability to generate real-time insights, delaying decision-making. - Data Retention Trade-offs
Yunsheng Haihong had to reduce Oracle’s data retention periods to maintain query performance. However, this led to the loss of certain historical data, leaving some business requirements unmet, particularly those involving long-term analysis.
These issues underscored the need for a new database architecture capable of handling growing data volumes, ensuring faster query performance, and simplifying system maintenance.
The Solution: Leveraging TiDB’s Distributed SQL for Seamless Scalability and Operational Efficiency
To overcome these challenges, Yunsheng Haihong introduced TiDB in 2020. The distributed SQL database provided:
Scalability
TiDB’s horizontally scalable architecture seamlessly addressed Yunsheng Haihong’s growing data needs. Unlike Oracle, which struggled with storage capacity, TiDB’s design allowed the system to scale out by simply adding nodes. This scalability eliminated storage constraints and ensured the architecture could keep pace with the expanding business.
Moreover, TiDB enabled longer data retention cycles previously restricted due to Oracle’s limitations. With extended data retention, Yunsheng Haihong could perform real-time data aggregation and cross-regional analytics more effectively, meeting the demands of modern digital retail.
MySQL Compatibility
One of the standout features of TiDB was its MySQL protocol compatibility, which allowed for a smooth migration of existing applications with minimal changes. Yunsheng Haihong could transition to TiDB without a complete overhaul of its system, saving time and reducing complexity during the migration process.
This compatibility ensured that the transition to a distributed database system did not disrupt ongoing operations or require significant retraining of the development team.
Improved Query Performance
TiDB overcame Oracle’s bottlenecks by optimizing the execution of complex SQL queries. Its distributed query engine processed analytics and reporting tasks more efficiently, even across massive datasets.
This improvement in query performance enabled faster generation of business insights, ensuring decision-makers had access to real-time data without delays. Tasks that previously slowed down reporting workflows, such as joins and aggregations, became significantly faster with TiDB.
Maintenance Simplification
TiDB’s architecture allowed Yunsheng Haihong to consolidate its aggregation and operational workloads into a single database system. This significantly reduced the dependency on Otter, the synchronization middleware that previously required high maintenance.
By automating key processes, such as adaptive sharding and distributed query execution, TiDB minimized the need for manual intervention. This simplification of maintenance operations reduced labor costs and eliminated many manual processes that previously introduced inefficiencies and risks.
Community and Documentation Support
The transition to TiDB was further supported by its comprehensive documentation and an active user community. Yunsheng Haihong benefited from detailed resources that streamlined onboarding and addressed potential challenges during the implementation phase.
The availability of community-driven solutions and responsive support from TiDB’s ecosystem ensured a smoother adoption process, empowering Yunsheng Haihong to maximize the database’s potential quickly.
Currently, TiDB handles the data for the entire system, with a total data volume of nearly 15 TB and the largest business table reaches up to 600 GB. During peak business periods, such as busy mornings, QPS exceeds 20,000, and the maximum concurrent queries for regional business reports reach around 300.
The Results: TiDB’s Role in Unlocking Business Agility and Savings
Operational Efficiency
Unified System Management
TiDB now manages the entire retail system seamlessly, consolidating operations into a single, efficient platform. The system handles a total data volume of nearly 15 TB, with the largest business table reaching 600 GB, demonstrating its ability to support large-scale retail operations.
High Performance During Peak Loads
Even during peak hours, TiDB processes over 20,000 queries per second (QPS) and manages up to 300 concurrent regional business report queries. This ensures smooth operations and real-time reporting during critical business periods.
Cost Optimization
Data Redundancy Reduction
TiDB’s distributed architecture and built-in compression capabilities allowed Yunsheng Haihong to reduce redundant tables in the system. For example, some tables that previously required 10 copies for different regions now only need 2. This streamlined approach significantly improved storage efficiency.
Resource Savings
With the reduction in redundant data and more efficient use of hardware resources, Yunsheng Haihong achieved an estimated 50% savings on infrastructure costs. This optimization freed up resources for other strategic investments and operational enhancements.
Simplified Maintenance
Reduced Synchronization Complexity
The introduction of TiDB reduced the number of data synchronization channels from over 100 to just 20. This simplification lowered the maintenance burden, reduced the risk of synchronization failures, and significantly decreased labor costs.
Easier Scaling
New business units, organizational changes, and data expansions are now handled automatically by TiDB. This eliminates the need for manual table creation, data migrations, and strategy adjustments, reducing operational overhead and enhancing scalability.
Enhanced Business Agility
Rapid Analytics
Previously challenging queries, such as complex joins and aggregations, are now executed efficiently in TiDB’s distributed system. This improvement allows for faster and more accurate analytics, empowering real-time decision-making and operational responsiveness.
Future-Ready Architecture
With TiDB’s flexibility and scalability, the system is now positioned to support evolving business demands. The architecture is designed to scale seamlessly with growing data volumes and query complexities, ensuring long-term adaptability and resilience.
Future Plans with TiDB
Upgrading to TiDB 6.1 LTS
Yunsheng Haihong plans to upgrade to TiDB 6.1 LTS to take advantage of new features that will further optimize their system. This includes utilizing Placement Rule in SQL for better hot and cold data segregation, allowing for more efficient resource allocation. The company will also benefit from improved TiFlash concurrency, which will enhance parallel query performance, and memory lock optimization in TiKV, improving write efficiency and reducing overall resource consumption.
Transition to a Single-Database Architecture
A major goal for Yunsheng Haihong is to transition to a single-database architecture fully powered by TiDB. This will involve consolidating their current sharded MySQL system into a unified TiDB platform, simplifying the infrastructure. The company will deploy two TiDB clusters, ensuring both business and resource isolation to maintain scalability and reliability as the system grows.
Efficiency Enhancements
Yunsheng Haihong aims to further streamline their operations by reducing the number of data synchronization channels, simplifying maintenance efforts and reducing labor costs. Additionally, the company plans to optimize resource utilization, further reducing hardware costs, continuing the trend of cost savings achieved through TiDB.
Addressing Specific Use Cases
The company intends to fully leverage TiDB’s hybrid workload capabilities to manage transactional and analytical workloads seamlessly. This will allow them to handle diverse business requirements more efficiently. Furthermore, Yunsheng Haihong plans to extend TiDB’s use to address the remaining Oracle workloads, with the ultimate goal of entirely phasing out Oracle from their system.
Conclusion
Yunsheng Haihong’s journey with TiDB has successfully addressed the challenges posed by their growing data demands and complex operational needs. By transitioning to TiDB’s distributed SQL architecture, the company has significantly improved scalability, query performance, and operational efficiency. TiDB has allowed Yunsheng Haihong to manage large volumes of data seamlessly, reduce infrastructure costs, and simplify maintenance tasks while maintaining high availability and reliability.
Looking ahead, Yunsheng Haihong plans to continue expanding its use of TiDB, leveraging its hybrid workload capabilities and the upcoming TiDB 6.1 LTS features to enhance performance and scalability further. As the company continues to evolve its retail operations in a fast-paced digital environment, TiDB stands as a powerful enabler of their growth and agility.
Organizations in the retail sector can take inspiration from Yunsheng Haihong’s success with TiDB, recognizing its potential to transform database management and drive long-term business success in an increasingly data-driven world.
