By BigData Infra Team

Company introduction

Founded in 2012, Guruyun is the leading SaaS system company in the world and the largest in China. At present, catering, retail and other service businesses, to provide a new generation of intelligent front desk, cashier and other SaaS cloud services, including booking, queuing, take-out, ordering, cashier, member management, purchase, sales and storage system services, and real-time data transmission cloud. We are the big data infrastructure group of Guruyun, responsible for the company’s big data architecture and construction, and providing the company with big data basic data services.

Pain points encountered in business development

  1. As the company’s business architecture becomes more and more complex, the technical architecture group needs to decouple the business from the server side and the application side through microservitization as much as possible, and the third-party circuit breaker service mechanism is needed to ensure the normal operation of the core business. Database level, in order to guarantee high concurrency real-time written, real-time query, real-time statistical analysis, we made a lot of work, such as higher requirements for real time service cache, depots table on the big table for operation, for the big table with cold and hot properties for archives, libraries, separation, although with a large number of human resources to solve the part of the problem, However, it also brings problems such as historical data access, cross-database sub-table operation and multi-dimensional query.

  2. Under a large amount of data, slightly complex queries in MySQL will be very slow. Online businesses also have a single complex interface that contains dozens of SQL executions. Some core transaction libraries need to solve the access performance urgently.

3. The catering industry has obvious business access peak time, during which the database will have high concurrent access, and some businesses, such as cashier, any RDS jitter during peak time will seriously affect the business and user experience.

  1. Traditional data warehouse business often has complex T+1 ETL process, which can not make dynamic analysis and timely decision on business changes in real time.

Business description

The Operational Data Store (ODS) of big Data used to be MongoDB. ODS synchronizes Data with RDS that supports SaaS services. The initial idea is that the complex SQL, analysis SQL, non-core business SQL will migrate to the ODS layer of big data. At the same time, ODS also serves as a data source for big data, which can handle both incremental and full data processing requirements. However, due to the use of MongoDB, there is some intrusion on the business, and the business line needs to modify the corresponding query statement, which is basically resisted by the students of the business line to varying degrees. At the same time, big data currently uses Hadoop + Hive storage and access solutions. Business lines need to migrate historical details query to Hadoop, and then use Impala, Spark SQL, and Hive SQL to query. However, when the concurrency of these three products is relatively high, Response times are slow, so big data groups have trouble providing detailed queries.

To make things even trickier, we first put the processed data (history rules, reports, etc.) on MongoDB (TiDB 1.0 didn’t have GA at that time, otherwise we would have used TiDB) because of the higher concurrency of customer queries (history rules, etc.) and the more sensitive response time of customers. Kylin was then used for OLAP queries to solve the problem of detailed queries first. However, due to the complexity of services, data changes are very frequent, and a piece of data is changed at least five or six times. Statements not only show the data of the day, but also involve complex situations such as account charges, trans-day business, non-settlement and reservation. The life cycle of production data often exceeds one month. Therefore, the current OLAP solution still has pain points, so we will migrate part of OLAP queries to TiDB in the future to reduce the pressure on Kylin and support more flexible query requirements, which is still under discussion.

At the same time, we found that TiDB has a sub-project, TiSpark, which is based on Spark engine. Spark has many mature projects in the field of machine learning, such as MLlib, and the threshold for algorithm engineers to operate TiDB with TiSpark is very low. It will also greatly improve the efficiency of algorithm engineers. We can use TiSpark to do ETL, which allows us to do batch processing and real-time data warehousing. Combined with CarbonData, we can do very flexible business analysis and data support, and later decide whether we can put part of Hive data on TiDB.

The old and new frames are shown below:

Photo: Old frame

Picture: New frame

TiDB test application

1. The configuration

Ali Cloud server:

  • TiDB/PD: 3 i1 machines, 16C 64G;

  • TiKV: 5 i2 machines, 16C 128G, 1.8T*2 two KV for each machine;

  • One monitoring machine.

At present, we have synchronized the data of three libraries in online RDS to TiDB through Binlog. During the peak period, QPS is about 23K, and some query services of business end are connected. In the future we will synchronize more RDS library data and deliver it to more business groups. As TiDB is a new project, the previous business line has no experience of online SQL migration, so there is no historical data comparison in write performance.

2. Performance comparison

(1) Query the numeric column after an index, return 10 records, test the performance of the index query.

(2) Query the numeric column after two indexes, return 10 records (each record only returns about 10 bytes of 2 small fields) of performance, this measure is to return small amount of data and the impact of a query condition on performance.

(3) Query the number column after an index, sort it according to the date field of another index (the index was established in reverse Order, and the sort is also in reverse Order), and return the performance of 10 records after Skip 100 records, which measures the impact of Skip and Order on performance.

(4) Query the performance of 100 records (no ordering, no conditions), this test is the impact of the query results of large amount of data on performance.

(5) TiDB compared with MySQL complex SQL execution rate:

  • Table 1 TiDB data volume 50 million, MySQL data volume 25 million;

  • Table 2 TiDB data volume 50 million, MySQL data volume 25 million;

  • Table 3 TiDB data volume 50 million, MySQL data volume 25 million.

    A. Corresponding SQL:


SELECT sum(p.exempt_amount) exempt_amount FROM table1 p JOIN table2 c ONp.relate_id=c.id  AND p.is_paid = 1

andp.shop_identy in(BBBBB) andp.brand_identy=AAAAA andp.is_paid=1 AND p.status_flag=1 AND p.payment_type! =8 WHEREc.brand_identy = AAAAA ANDc.shop_identyin(BBBBB)                              

ANDc.trade_type in(1,3,4,2,5) andc.recycle_status =1 AND c.trade_statusin (4,5,10) andp.payment_time BETWEEN'the 2017-08-11 16:56:19' AND 'the 2018-01-13 00:00:22'        

ANDc.status_flag = 1        

ANDc.trade_pay_status in(3, 5) AND c.d elivery_typein,2,3,4,15 (1)Copy the code

**b. SQL: **Copy the code

SELECT sum(c.sale_amount)tradeAmount,sum(c.privilege_amount) privilege_amount,sum(c.trade_amount)totalTradeAmount,sum(c.trade_amount_before) tradeAmountBefore        

FROM (SELECTc.sale_amount,c.privilege_amount,c.trade_amount,c.trade_amount_before        

FROM table1p        

JOIN table2c ON p.relate_id=c.id                                

andp.shop_identy in(BBBBB) andp.brand_identy=AAAAA andp.is_paid=1 AND p.status_flag=1 AND p.payment_type! =8 and c.brand_identy = AAAAA ANDc.shop_identyin(BBBBB)                                

ANDc.trade_type in(1,3,4,2,5) andc.recycle_status =1 AND c.trade_statusin (4,5,10) andp.payment_time BETWEEN'the 2017-07-31 17:38:55' AND 'the 2018-01-13 00:00:26'        

ANDc.status_flag = 1        

ANDc.trade_pay_status in(3, 5) ANDc. Delivery_typein(1,2,3,4,15)                                  

ANDp.payment_type not in(4,5,6,8,9,10,11,12) GROUP BYCopy the code

**c. SQL: **Copy the code

SELECT SUM(if(pay_mode_id=-5 or pay_mode_id = -6,0,IFNULL(pi.face_amount, 0) - IFNULL(pi.useful_amount, 0) -IFNULL(pi.change_amount, 0))) redundant

FROM table2c

JOIN  table1 p ON c.id = p.relate_id AND c.brand_identy=p.brand_identy        

JOIN table3pi ON pi.payment_id=p.id AND pi.pay_status in(3,5,10) AND PI. Brand_identy = p.b rand_identy ANDpi. Pay_mode_id! =-23 andp.shop_identyin(BBBBB) andp.brand_identy=AAAAA andp.is_paid=1 AND p.status_flag=1 AND p.payment_type! =8 WHEREc.brand_identy = AAAAA ANDc.shop_identyin(BBBBB)                              

ANDc.trade_type in(1,3,4,2,5) andc.recycle_status =1 AND c.trade_statusin (4,5,10) andp.payment_time BETWEEN'the 2017-07-31 17:38:55' AND 'the 2018-01-13 00:00:26'        

ANDc.status_flag = 1        

ANDc.trade_pay_status in(3, 5) AND c.d elivery_typein,2,3,4,15 (1)Copy the code

**d. SQL: **Copy the code
SELECT t.id tradeId,sum(t.trade_amount - t.trade_amount_before) AS roundAmount, sum(-p.exempt_amount) AS exemptAmount FROM table2t LEFT JOINtable1 p ON p.relate_id = t.id LEFT JOINtable3 pi ON Trade_pay_status IN (3,4,5,6,8) trade_pay_status IN (3,4,5,6,8) Andp. payment_type IN (1,2) andpi. pay_mode_id! =-23 andp.is_paid =1 AND t.tatus_flag =1 AND t.hop_identy IN(<123 business number >) GROUP BY T.idCopy the code

**e. SQL: **Copy the code
SELECT t.id tradeId, sum(t.trade_amount- t.trade_amount_before) AS roundAmount, sum(-p.exempt_amount)AS exemptAmount FROM table2t JOIN table1 p ON t.id = p.relate_id WHERE t.brand_identy = AAAA Trade_status IN(4,5,10) andt.trade_pay_status IN(3,4,5,6,8) andp.is_paid =1 AND t.tatus_flag =1 group by t.id;Copy the code

(6) OLTP comparison test results:

(7) Simple test conclusions:

  • Whether it is index query, paging query, online business-level load query, TiDB performance is stronger than MySQL under large data volume;

  • TiDB’s overall performance meets the needs of our business.

Production and use

Currently, more than 6 months of data has been stored online, and the total amount of data is several terabytes. It supports online query and analysis requirements, and many ordinary complexity OLAP queries can return results in seconds. TiSpark has also been debutted and is ready to transplant some ETL applications that support OLAP to achieve real-time ETL. There is still a lot of room for optimization in TiDB production, such as system parameters, SQL usage posture, index design, etc.

The future planning

  • There is already a business department with a large trading volume asking us about TiDB, and it may use TiDB as an online trading system.

  • In the future, TiSpark will also be used for OLAP query and data processing for big data, and may also serve as Kylin’s data source.

  • Expect TiDB to play an increasingly important role in both OLTP and OLAP scenarios in the future.

Thank you

Thanks for the great support from TiDB manufacturers. We hope that we can provide TiDB with some meaningful information and suggestions, so as to contribute to the growth of TiDB.

Extending reading

Practice of TiDB in real-time report of two-dimensional fire catering management

Application of TiDB in Ele. me archiving environment

TiDB helps one side of the data to realize the decision analysis platform in the field of consumption