Zhang Yihao, senior development engineer of Basic Technology Department of Xiaohongshu, is responsible for database related research and development and landing work.

Introduction of TiDB application in Xiaohongshu business scenario

In 2017, Xiaohongshu began to use TiDB in its production business, and it was in 2018 that TiDB was implemented systematically. Why do we choose to use TiDB?

Today, many companies’ businesses are data-driven. Facing the hundreds of millions of data of Xiaohongshu APP every day, we hope to have a database that can provide the following features:

First, the diversity of data use, sometimes need to do a TP short query in the database, do some high write, sometimes want to do some aggregation analysis, can show the results of summary statistics, TiDB HTAP architecture just meets the diversity of demand.

Second, higher timeliness. We know that there are many data analysis engines with fast calculation but weak support for real-time analysis. TiDB can provide higher timeliness.

Third, TiDB is based on Raft scalability. The data of Xiaohongshu APP is hundreds of millions every day, and the single point cluster will be full one day, it will be exploded. We expect to have a database with excellent scalability and easy expansion.

At present, the application of TiDB in Xiaohongshu covers a number of business scenarios, such as report analysis, promotion of real-time large screen, logistics and warehousing, data warehouse application, e-commerce data center, content security audit and so on. June 6th is the big promotion of xiaohongshu’s anniversary, which needs to show some real-time sales, ranking of total transaction volume of stores, total sales and other information. TiDB is connected behind this real-time big-screen application.

What problems does TiDB solve for us in these businesses? I’ve picked three very typical application scenarios from these businesses to share with you.

  • Data reporting: Data reporting is easy to understand, analysts often need to look at some data, such as the week’s trend, to see some sales, to see some user growth, to see year-on-year and sequential data.

  • Real-time query of online business library: for example, MySQL cannot store a table with 30 billion rows, so it needs to follow the logic of separate database and separate table, and it is hoped that the query or analysis can not affect the online business, so as to solve the query problem of MySQL database and separate table online.

  • Anti-fraud data analysis: The so-called anti-fraud for example, the e-commerce platform of Xiaohongshu will regularly issue some coupons, and scalpers like to collect these coupons most. Can we catch these black products in a short time, and capture them for analysis and blocking?

Limitations of traditional MySQL and warehouse schemes

What do we do without TiDB? As shown in the figure above, business logic is divided into online business layer, offline data warehouse layer and data service layer from top to bottom.

Firstly, in the data report scenario, Hadoop data warehouse is used to do some pre-aggregation of data, and then these high-dimensional data are simply aggregated and put into MySQL for query. For data reports, the data in Hadoop will be pre-aggregated into MySQL every day in the form of T+1 by Hive, and then some BI systems will be built for graphical display of report query, so that analysts can see some customized reports. But with the rapid growth of business, report form become more varied, MySQL scalability is also a more headaches, if simply add some MySQL node, in the end will become a problem we how to manage so many MySQL node, made operational classmates know, this is a more troublesome thing.

Look at online MySQL depots table set, we want to do in the above data query, and doesn’t affect the online library, so can only check from library, from the library, of course, is also a scene of depots table. A series of problems arise here: First of all, the operation and maintenance problem, how to manage so many nodes of MySQL database and table? How to expand capacity? Does Sharding need to be sharded again? How to ensure consistency? How to shrink? How to manage meta information? This is the complexity of operations. In addition, I think it is necessary to mention a point, such as a sub-database sub-table MySQL, I want to do a transaction above, sub-database sub-table middleware is convenient to do? If I also want to do a JOIN, or even if I want to do a Group by aggregate query, is it convenient for the split table middleware to do that? Maybe, but it won’t be easy, so we need a solution that makes it easy to do more complex distributed queries.

Thirdly, in the anti-fraud data analysis scenario, we pay more attention to timeliness. Before TiDB, we wrote some data of the backend into the data warehouse, and the business side could not check the above data until the second day of T+1, so that the timeliness of T+1 was relatively poor. Cattle pulling wool is a very quick thing, to the next day may be directly pulling finished you can not do, so I hope the best in half a minute, ten seconds, even second level, can see the detailed use of coupons.

TiDB HTAP solution is introduced to improve data service capability in all scenarios

Based on the challenges of the above scenarios, we introduced TiDB 3.0 HTAP solution to take a look at the new business architecture. In the figure below, we see that the data services layer can provide all the data services required by the business with TiDB.

Let’s revisit the three business scenarios following the introduction of TiDB.

In data report scenarios, TiDB is used to directly replace MySQL, which solves the complex problem of MySQL expansion with service growth. I think the most important reason for the seamless switch is that TiDB supports MySQL from the beginning, which I think is really cool about TiDB’s design, it’s really smart. Front-end BI tools do not need to develop a so-called TiDB driver, directly with MySQL driver can be. At the capacity expansion level, TiDB is best at adding a node directly, and data can be automatically re-balanced, which is very convenient.

How to query database tables in MySQL? We created a real-time stream that writes MySQL data to TiDB via Binlog in real time with a synchronization delay of less than a second. Real-time streaming is not just a simple data synchronization, but it also does something called syndication. What is syndication? Originally, there were 10000 tables on the line, because MySQL could not store tables, now a TiDB cluster is able to store tables, there is no need to separate tables. While writing to the TiDB, the real-time stream also combines the 10000 sub-tables into a large table, which may have to deal with some special problems, such as the original increment primary key? Is there a problem with the auto-increment primary keys joining together? You might have to do some data conversion, you might have to do some data formatting and mapping and stuff like that, but you do all of that in the live stream, and you end up with a big table, and you don’t have to do that. If you want to do a transaction, TiDB supports transactions. If you want to do a JOIN, you want to do an aggregation, TiDB can support this kind of operation. Finally, a large table is displayed in TiDB.

Finally, let’s take a look at the anti-fraud data analysis scenario. After TiDB is applied, we change the T+1 submission to be written in real time by Flink SQL. The data generation rate is very high, and the peak QPS can reach 30,000 to 40,000 yuan. If we keep data for 10 days, it’s on the order of 5 billion tables. So once I write it in, how do I query it? It’s mainly ad-hoc queries. If the analyst wants to see how the coupon is being used and distributed, he wants to be able to see it at the minute level. Every time the SQL may change, we directly bypass the Hadoop database and use TiDB to provide more real-time queries.

Application effect of TiDB 4.0 HTAP scheme

With the introduction of TiDB, we solved the various problems we encountered in the above three typical business scenarios. Are there any shortcomings in this plan? In fact, there is, if 3.0 had not been insufficient, it might not have been 4.0. After using TiDB, we felt that TiDB 3.0 was a little weak in OLAP analysis. TiKV is a database based on row memory, and there is no comparison with some column memory engines specialized in analysis. How does TiDB solve this problem? Is it ok to introduce a column engine in TiDB? In 4.0, TiDB came to us with TiFlash as a storage engine.

The design of TiFlash has several points that I think are very good: first, as a column storage engine, TiFlash can coexist with TiKV, not only column storage, only row storage, both can exist at the same time, since it can exist at the same time, how to copy and convert the data from the middle row storage to column storage? Is it necessary to build another replication stream to do this? No, TiDB does it all for us, using Raft Learner replication to directly synchronize all data to TiFlash with a low latency. From the query side, is there anything special that needs to be done to get TiFlash to the cache engine? No, TiDB has an automatic routing of the CBO execution plan, you can know whether this SQL is better than TiFlash to scan the whole table or TiKV index query is faster, you can help me plan. The operation and maintenance cost of introducing TiFlash was very low, all I had to do was apply for the machine to deploy TiFlash, and then it was over, the data was automatically synchronized, the query was automatically routed, and nothing happened.

We also tested TiFlash. Taking the logistics scenario as an example, we evaluated 393 production queries. The vertical axis in the figure above shows TiFlash’s performance improvement. The average time was reduced by about 68%. If the query is not aggregated, the average time is reduced by about 4%. The non-aggregated query basically hits the index of TiKV and does not remove TiFlash’s column memory.

TiDB 4.0 also brings us pessimistic locks. In the logistics scenario, many tables need JOIN, and JOIN is actually a relatively expensive thing. To avoid joining, we will pre-assemble the tables to form a large, wide table. For example, if I put three tables together to form a large and wide table, then three streams will update the large and wide table at the same time, updating the same row. The original TiDB 3.0 mechanism is optimistic locking, which will cause transaction conflicts, which is not very friendly for client retry. TiDB 4.0 has pessimistic locking, which solves this problem well.

We also had a lot of communication with the TiFlash team at PingCAP, and we often proposed new requirements. For example, at the beginning, TiFlash did not support the ditinct count scenario, which was inefficient. The development team quickly optimized it after understanding our requirements. Support for ditinct Count scenarios.

How to choose between TiFlash and ClickHouse?

Finally, to compare TiFlash to the other solutions, the familiar ClickHouse storage engine is actually a bit faster than TiFlash in terms of computational performance. Why do we choose TiFlash for certain scenes? Because ClickHouse has some problems, such as the complex operation and maintenance of ClickHouse’s cluster model and weak support for data updates because many businesses are transactional and require a lot of updates, ClickHouse update performance is poor. If you want to change the Append, Insert logic, the business side will have to do a lot of changes, such as heavy data and other things, in many scenarios to support the high frequency of updates we choose TiFlash.

This article is adapted from Yihao Zhang’s talk at TiDB DevCon 2020.