Scaling Databases Horizontally: A Solution for High-Concurrency Systems

In today’s dynamic landscape, effective data management is paramount. The demands of high concurrency and the exponential growth in data volume present formidable challenges, particularly in the context of horizontally scaling MySQL databases.

While deploying replicas can mitigate some of these challenges, scaling the single master poses intricate problems, often resulting in escalated IOPS costs—especially during the execution of resource-intensive analytical queries.

Faced with these formidable hurdles, our esteemed banking client embarked on a quest for a resilient warehousing solution.

After extensive discussions with our client to understand their specific requirements, we’ve identified TiDB as a promising candidate to address their scalability and performance needs.

The Power of TiDB

TiDB boasts a range of features that make it an ideal choice for our client’s operations:

Distributed Database

TiDB offers a distributed architecture that enables the seamless handling of large datasets, making it well-suited for applications with a substantial data footprint.

High Availability

Banking domain or any high critical system, downtime is not an option. TiDB ensures high availability, minimizing the risk of disruptions and ensuring that critical services remain accessible.

Horizontal Scalability

Scalability is a key concern when dealing with high-concurrency environments. TiDB’s horizontal scalability ensures that as data and user demands grow, the system can effortlessly expand to meet these requirements.

OLAP Support (HTAP)

TiDB supports Online Analytical Processing (OLAP), allowing our client to perform complex analytics queries without compromising system performance.

Our Suggested Solution

To maximize the benefits of TiDB while preserving existing MySQL infrastructure, we propose the following architecture:

Architecture Overview

Our architecture is designed with multiple stages in mind. Let’s take a closer look at the first stage:

Stage 1: TiDB Cluster

TiDB cluster consists of following components

TiDB Nodes: These nodes take the front seat, serving as the stateless components responsible for managing user interactions.

TiKV Nodes: All your data finds a secure and persistent home within these nodes, ensuring its safekeeping and availability.

PD Nodes: These nodes play a pivotal role in orchestrating and optimizing data distribution among the TiKV nodes, ensuring efficient data management.

Stage 2: Live Replication Support from MySQL

Data is continually streamed from the MySQL database to ensure real-time updates in TiDB. Unlike traditional OLAP solutions that require complex ETL (Extract, Transform, Load) processes, TiDB simplifies live replication with its migration tool, making the integration process straightforward and efficient.

Stage 3: TiFlash for Analytics

TiFlash is our go-to component for advanced analytics. It stores data in a column-based format, ideal for OLAP workloads. Unlike other databases that require ETL or external jobs to convert data, TiDB seamlessly replicates data from TiKV to TiFlash, simplifying your analytical processes.

Stage 4: Establishing a Comprehensive Data Warehouse

In this stage, our architecture achieves a holistic data warehousing setup. The application server employs MySQL for transactional (OLTP) workloads, while TiDB cluster takes the reins for analytical (OLAP) tasks.
Data which is replicated from MySQL is ingested into the TiDB cluster, providing a highly available and horizontally scalable environment for serving read requests and running OLAP queries. This ensures that both critical transactions and analytical tasks can coexist without compromising performance.

In conclusion, when dealing with the challenges of horizontally scaling databases in high-concurrency systems, TiDB emerges as a compelling solution. Its distributed architecture, reliability, scalability, and OLAP support position it as a powerful choice for modernizing data management.

With native support for seamlessly transitioning from row-based to column-based storage and facilitating Hybrid Transaction and Analytic Processing (HTAP), TiDB simplifies complex tasks.

Stay tuned for more insightful blogs on TiDB as we continue to explore its capabilities in-depth.