Editor’s note:

On the evening of September 10, the “Cloud plus data, Intelligence drive future” data science series forum hosted by Seven Niuyun was held as scheduled. In the live broadcast, PingCAP co-founder and CTO Huang Dongxu brought us a wonderful sharing with the theme of “TiDB’s Best practices in real-time data Analysis”.

As a stand-alone database, when the amount of data increases, MySQL will inevitably involve operations such as database and table division in exchange for horizontal expansion ability, and the complexity of MySQL will show a geometric increase. TiDB was originally designed five years ago to replace MySQL database and tables. Therefore, TiDB was originally intended to be an OLTP distributed database that could be used as a stand-alone database and had horizontal scaling capability.

However, as users use TiDB to store more and more data, a new type of demand arises: users wonder if I can do some offline, or even quasi-online, data analysis directly on TiDB instead of moving it to Hadoop. I think there’s a large percentage of OLAP needs that don’t have to do heavy ETL, like e-commerce users, just want to see how many things are being sold, or figure out how much money they made today. However, Transaction Databases in the past were not designed for this kind of complex analysis.

So in the last couple of years there has been a new concept called HTAP, which is as vague as possible between OLTP and OLAP. In the past, due to the immature technology, data structure, hardware, network and other conditions, the two sets of designs are incompatible, so the technical separation of OLTP and OLAP was forced. I think in the future these technical details or underlying differences will become more and more blurred, including Gartner in a report that there will only be one kind of Database in the future. So there will be a lot of Workload updates under the new concept of HTAP.

HTAP technology evolution process

Prior to HTAP, Internet companies did online and offline business according to a traditional architecture shown in the figure below.

On the business side, OLTP data may have many MySQL or library tables, which are sent to Kafka via Binlog as message queues to a near real-time system. For example, HBase is used to do some data retrieval, and then the data is used to do big data analysis and ETL on Hadoop with Hive or Spark, or the data generated by ETL is written back to other MySQL. Or provide services to other online databases. This is a traditional big data processing architecture, but one of the problems with this architecture is that the online and offline businesses are very separated, and the whole system is connected through ETL processes and data transmission layers.

This is why many companies only see data from the previous day, as it may have to be loaded in batches. Therefore, I think the direction of HTAP technology for users is the same as smart phones for traditional mobile phones. With smart phones, I no longer need GPS, DSLR cameras and mobile phones, but an iPhone is enough, which greatly reduces the complexity of business and architecture. In addition, there may be many systems to maintain, many teams, if HTAP exists, for the vast majority of the business will need to maintain only one system. From the leader’s point of view, operation and maintenance costs and team personnel costs will be reduced.

Finally, I think it makes more sense for business. We used to rely on old data for many of our decisions, but now we can think about relying on real-time data. For example, in an offline store, as long as users enter the store, they can immediately know what they want to consume and what they are interested in through face recognition or membership card, so as to make decisions quickly. In this case, it doesn’t make sense if the system isn’t real-time, and the user might just look at it and lose it. So when you add these foundations together, you can make a big difference in the iteration and agility of the entire business. I see HTAP as a new database species, not an improvement on traditional OLTP and OLAP.

Again, take e-commerce as an example, as shown in the figure above: the left side is transactional, and the right side is analytical. We divided the internal system of the e-commerce platform into order management, historical details of bills, recommendation, real-time inventory query by joint storage, real-time large screen, promotion price adjustment, and historical statements. The leftmost part of the line is order management, including online transactions, so the leftmost part is near OLTP, and the rightmost part is near OLAP.

We can find that, for example, sales history reports are pure offline scenarios with weak timeliness requirements. I can see this month’s reports tomorrow or next month without being affected. However, real-time promotion price adjustment, real-time large screen and warehouse query are all partial real-time, and need to make real-time calculation according to online order status, user access status, real-time transaction status and promotion status of other channels. In these scenarios, implementing such a system used technologies such as Flink, Spark Streaming, Kafka, and many other real-time data synchronization tools.

This is a complex problem with many technical challenges:

The first challenge is the horizontal scalability of OLTP databases. For OLTP databases, the expansion solution can only be divided into tables or shards at the business level.

The second challenge is that OLTP systems need to be both OLAP capable and row-column capable. General OLTP system is to use row storage as the underlying storage model, while OLAP is to use column storage, in the query efficiency is about a hundred times worse, it is difficult for business personnel to trust in an OLTP system to run complex queries, there are some risks behind. So not only do you need to address user concerns, but you also need to be able to run fast when you go to the OLAP end, and you have to support column storage.

The third challenge is the need for the two to be organically integrated rather than just two separate systems. If separated, there are interoperability issues, such as how the data in OLTP is synchronized to the OLAP system, and how long the synchronization delay is. These are technical challenges.

TiDB 4.0: A true HTAP system

The latest version of TiDB is 4.0. In my mind, before TiDB 4.0 and after TiDB 4.0 are two completely different products. Before 4.0, it was a transactional database, a good replacement for MySQL database and tables, an online service of MySQL protocol that could support massive amounts of data, but it was not a good data warehouse, nor was it a good real-time analysis product, because it was a row-storage database, although it was very convenient to use.

TiDB 4.0 is a true HTAP system:

First, TiDB 4.0 introduces a storage engine for column storage, which shows that there is essentially no disadvantage when compared with other AP systems.

Second, in TiDB 4.0, the computing engine is vectorized based on column memory, which is equivalent to using some CPU batch computing instruction sets to do very high performance computing in a relatively compact data structure format. This is a technique often used in OLAP databases.

Another thing that is almost impossible to do in a traditional OLAP database is to have some data that is better in the row store, such as a random indexed query, a needle in a haystack query, which is probably good on the OLTP side, to find the data directly. And column storage is more suitable for example, I have a large table to scan all over, batch scanning, batch aggregation. In TiDB 4.0, we use some techniques to combine the advantages of these two different storage domains. We recently published a paper on HTAP that was selected for VLDB.

In short, the storage and computation of the entire TiDB are completely separate. If you are familiar with HBase, you will know that HBase has regions. Each piece of data is a small fragment. In TiDB, each region is a Raft replication group. We introduced a copy of the column in our Raft replication team for each small piece of data. Since the computing layer is separate from the storage layer, our computing layer determines requests based on SQL, and OLAP requests are sent to the OLAP copy and OLTP requests are sent to the OLTP copy. Because the synchronization of the underlying data has always been through Raft fragmentation synchronization. Secondly, it means that workload, your OLTP business is always executed on nodes like TiKV, while OLAP business is actually executed on nodes of TiFlash. In principle, it is completely separated from hardware and software. You don’t have to worry about running a complex query on this side and blocking on this side, and the data synchronization is completely real-time.

So the bottom line is that TiKV provides a great elastic scaling mechanism for data, which we call multi-raft. In fact, I split all of our data into numerous Raft replication groups. I just needed to know how to support this heterogeneous data source. I just needed to give my Raft group one more copy of heterogeneous data, which was beautifully embedded into the original multi-raft system.

And in this regard, it has a natural advantage over other binlog-based Kafka data synchronization, which does not require other Kafkas. Imagine if I were two different systems, MySQL on the left, Hadoop on the right, and Kafka in the middle for synchronization. If the data throughput on both sides is extremely high, Kafka becomes a data synchronization process that becomes your bottleneck.

Therefore, at this point, the beauty of TiDB replication mode lies in that its data synchronization is expanded together with the expansion of the data itself, which is equivalent to breaking the whole data synchronization process into pieces and breaking it into each piece of data fragment.

In the aforementioned HTAP scenario, simply means that a SQL statement opens the column transmission mode of a table. After OLTP business does not need to make any modification, but at the same time, IT can directly do OLAP analysis on the database. In this way, the overall architecture complexity, operation and maintenance cost, business substantive and business agility have been greatly improved. Therefore, the traditional transaction analysis architecture is simplified into a large central source of Truth architecture, which provides APP server and business intelligence services for fact analysis.

At the same time, you can use TiDB as a data middle tier in conjunction with existing repositories, but I’m not saying it will necessarily replace the Hadoop model, or the Database model. Since there are some non-real-time queries, ETL cannot be avoided, but you can use TiDB scaffolding on top of Hadoop to improve a real-time view of the entire data twist.

TiDB is a great complement to the real-time layer of the overall architecture. This is my share today. Thank you.