Reducing Query Latency from Seconds to Milliseconds with a Scale-Out Database

Industry: Banking

Author: China Zheshang Bank

Transcreator: Caitin Chen; Editor: Tom Dewan

China Zheshang Bank is a national joint-stock commercial bank based in China. We aim to provide customers with open, efficient, flexible, and shared comprehensive financial services. By the end of June 2020, we had established 260 branches throughout the country. In terms of total assets, we ranked 97th on the “Top 1000 World Banks 2020” list by the British magazine The Banker.

As our businesses grew, our data size boomed. Our databases couldn’t meet our requirements for data storage and analytics. After comparing multiple distributed databases, we adopted TiDB, an open-source, Hybrid Transactional/Analytical Processing (HTAP) database. Thanks to TiDB, we can easily scale out our databases and perform real-time analytics. In our telecom fraud risk inquiry system, our query latency is reduced from tens of seconds to tens of milliseconds.

In this post, we’ll share our pain points, why we chose TiDB, how we use it now, and our plans for it in the future.

Our pain points

Our database requirements

  • Supports massive amounts of data
  • Supports complete distributed transactions
  • Provides enterprise-grade high performance, high concurrency, and continuous high availability
  • Supports horizontal scalability and smoothly tackles traffic in various kinds of applications
  • Has a strong ecosystem to connect to various data application ecosystems

After we tested multiple products and verified application compatibility, we found that TiDB took the lead in terms of database scalability, query performance under massive data scale, and transaction integrity. So we adopted it.

Why we chose TiDB

As for its functionalities, TiDB supports complete distributed transactions. It’s a one-stop solution for both Online Transactional Processing (OLTP) and Online Analytical Processing (OLAP) workloads for massive data. It provides enterprise-grade high reliability and high availability. If a data center (DC) is down, the cluster can automatically failover.

As for its architecture, TiDB is a NewSQL distributed database. It separates the computing layer from the storage layer, so we can scale the storage capacity and the computing resources by different amounts as needed. It applies to a wide range of applications.

In terms of operations and maintenance, TiDB is compatible with the MySQL protocol, so it significantly reduces personnel learning and development costs. It provides a flexible query interface for application operations personnel. It’s easy to monitor and maintain.

TiDB use cases

The telecom fraud risk inquiry system

The telecom anti-fraud system has exceeded 2 billion rows of data in a single table, and the data size increases by millions of rows of data every day. Our former OLAP database couldn’t meet our expectations for data storage and application query performance.

But TiDB can elastically scale. Simply by adding nodes, we can improve the system performance and throughput. Besides, TiDB supports storing and querying massive structural data. Therefore, our SQL queries’ processing time is reduced from tens of seconds to tens of milliseconds. Thanks to TiDB Lightning, a data import tool, we can quickly import millions of data records in a single issued data file to meet the application operational window requirements.

Foreign exchange transaction management

We switched from Oracle to TiDB and found:

  • TiDB’s distributed architecture meets our needs for data scalability.
  • TiDB’s elastic scalability helps achieve data auto-rebalancing. The scaling process is transparent to application operations and maintenance staff.
  • TiDB provides high-availability capabilities for active/active data centers in different places. When a node in the TiDB cluster fails, the system can implement auto-failover to ensure cluster high availability.

Executive cockpit (real-time operating metric analysis)

We use a data replication tool to write the upstream Db2’s data changes to TiDB in real time. At the same time, we replicate TiDB’s change data to the downstream Apache Kafka platform. Apache Flink receives messages from Kafka for streaming calculations. This system forms an efficient and easy-to-use real-time computing platform. It collects statistics for various business operating conditions of the bank in real time and displays them on a large screen. By capturing massive financial data changes online and performing real-time analytics, the system helps bank managers to make business decisions, and it improves service efficiency.

Exploring further uses for TiDB

Distributed real-time ODS

Our bank has multiple OLTP heterogeneous databases, mainly Db2 and MySQL, as well as Oracle and SQL Server. We don’t have an efficient way to replicate data among these databases, so this creates data silos.

To perform real-time analytics for various applications, we plan to scale out our existing platform and build a distributed real-time ODS based on TiDB. This will enable us to replicate different types of data in heterogeneous databases in near real time.

TiCDC is TiDB’s change data capture framework. This open-source feature replicates TiDB’s incremental changes to downstream platforms, such as MySQL and Apache Kafka, Pulsar, Flink, and Canal. TiCDC provides real-time, high throughput, and high-availability replication services.

Internet query transaction

If you have any questions or want detailed information about our experience, you could join the TiDB community on Slack.

Originally published at www.pingcap.com on June 17, 2021

PingCAP is the team behind TiDB, an open source MySQL compatible NewSQL HTAP database. Official website: https://pingcap.com/ GitHub: https://github.com/pingcap