Ideal car HTAP read traffic optimization guide

"No database is a silver bullet. The adaptation of business scenarios and cost reduction and efficiency enhancement are always the most important." Database performance optimization can help companies maximize the use of system resources, improve business support capabilities and user experience. This article is the first of the TiDB performance tuning topic. In this topic, we will invite more TiDBers to share the best practices of TiDB optimization based on actual business scenarios.

Author introduction

Zheng Heyang (stage name: Qianlong), ideal car DBA. Responsible for the technical exploration of the company's distributed database and the implementation of business scenarios, and love open source. Work in finance, Internet education, e-commerce, new energy vehicles and other fields.

Ideal Auto, as a luxury smart electric vehicle brand, takes "creating a mobile home and creating a happy home" as its mission. With the continuous development of the electric vehicle business, the company's business has both OLTP and OLAP requirements, so an HTAP database is needed to help the company realize real-time business decisions. In the TUG Corporate Line-Walk into 58 City Activity, Mr. Zheng Heyang from Ideal Auto introduced the HTAP read traffic of Ideal Auto in terms of physical environment, business environment, SQL optimization, hot issues, traffic environment, version and architecture, etc. Optimization.

The following is a transcript of the speech.

Reasons why ideal cars choose TiDB

1) One-stack HTAP: simplifying the enterprise technology stack

TiDB can simultaneously support ideal automotive OLTP and OLAP requirements on a single data source. It not only supports real-time data storage, but also provides integrated analysis capabilities. In addition, TiDB can also integrate common big data computing technology frameworks, such as Spark, Flink, etc., and can be used to build offline or real-time data warehouse systems.

2) Solve the problem of MySQL traditional database demolition and table demolition

With the rapid increase in data volume, what should I do if the data inventory of a single machine is not enough? The scalability issue of traditional relational databases is that if MySQL is already used for business, you can only do sub-database sub-table, or use middleware to transform, and the business layer has to change the code all over. TiDB is fully compatible with MySQL, and MySQL applications can run directly without modification. It supports the features of traditional RDBMS and NoSQL, which can be seamlessly expanded horizontally as data grows, and only needs to add more machines to meet business growth needs.

3) Simple but not simple expansion and contraction

In the evolution of product updates, TiDB 4.0 and 5.0 can basically be expanded and contracted flexibly and conveniently through some operational controls, such as linear expansion of computing capacity by adding nodes, and storage nodes can also be continuously expanded according to storage requirements to increase storage . When the node is scaled down, the impact on online services is almost imperceptible.

4) Complete ecological tools

At present, Ideal Auto is using DM (TiDB Data Migration), TiCDC, TiSpark and other tools. In actual operation, I also strongly feel that the TiDB ecological tools are very comprehensive. These ecological tools are still evolving and developing. The official friends explore together.

5) Rich scene support

The business scenario of TiDB in the ideal car includes two dimensions: OLAP and OLTP. OLAP includes offline data warehouse, real-time data warehouse, DMP platform (including daily decision-making scheduling, financial scheduling, reporting, etc.); OLTP business front-end, algorithm training (including online order dispatch, delivery, information reporting), and so on.

6) Good community environment

The community has always been a good soil for cultivating TiDB's continuous development. Once failures and difficulties occur in daily maintenance, the official technicians will immediately deal with it. This also gives Ideal Auto great confidence to introduce TiDB into more business scenarios.
Next, I will introduce to you ideal car's optimization practice in the following 7 aspects for reading traffic.

How to optimize HTAP read traffic?

1) Physical environment optimization

Ideal Auto currently upgrades the configuration of TiDB and PD clusters from the original 16-core 32G to 32-core 128G. For TiDB, large SQL supporting AP type may have to run about 7-8G of memory, which requires higher memory. In order to solve the computing power problem of the PD, we will estimate the data level of the data warehouse, and we will increase it after the estimation. For example, if a single point is used, a PD above 500,000 may have scheduling problems. So we can increase the Region to avoid the excessive number of Regions horizontally.

TiKV just started using a 32C32G 2T Baidu Cloud SSD. Now, like TiFlash, it all uses a 32-core 64G 4T NVMe physical disk. These two upgrades are to better coordinate computing resources.

2) Optimization of business environment

At present, the main TiDB clusters in Ideal Auto are synchronized with upstream MySQL by DM, and a lot of optimizations have been made for DM cluster management. After this is done, all the main upstream MySQL libraries will now have copies in TiDB, so TiDB has the functions of MySQL slave database + business master database + DMP platform result database.

Key business database table DDL changes and business changes

• Whether upstream MySQL DDL changes are allowed or not, a specification will be made to prevent interruption of synchronization tasks.
• Complete test environment support for new business.

• Upstream MySQL flashing business data will have a large amount of write traffic, and the DM-sync thread will expand. Before refreshing the data, you need to automate in advance and adjust to the peak to deal with the impact of traffic, because there are many important businesses downstream, and data delay will have a great impact.

  Classification development specification:

• OLTP development specifications:
  At present, the size of the SQL result set of a single transaction of the ideal car cannot exceed 20MB and the result set is less than 50W, or the memory of the TiDB computing node is less than 120MB, because TiDB needs to process the result set, which may be expanded up to 6 times.
• OLAP development specifications:

1. For complex SQL large tables use TiFlash (generally 2KW), and small tables use TiKV.
2. The maximum value of the result set is less than 7KW or the TiDB calculation result memory is less than 8G.
3. Continue to explore TiDB's OLAP performance boundary.

DM optimization:

The problem with DDL is that it does not support changes. If the downstream read traffic business is affected, for example, the company has a lot of MySQL connected upstream, and you want to do MySQL synchronization association, you only need to synchronize in a TiDB cluster, or you can make a small data warehouse , Adjustment method, first adjust TiDB to support DDL changes.
solution:
(1) Adjust TiDB to support DDL changes
(2) Upstream new table --> insert business data --> rename table name --> skip error reporting (for upstream traffic and data volume is small, data repair is required)
(3) Downstream create a new table --> insert business data --> rename table name --> skip reporting errors (downstream will not lose data)

In business environment optimization, typical TP-type SQL requires 20MB for result sets and operators. For example, environmental planning needs to be considered. How many result sets we require, and there must be no fewer than tens of thousands of rows or memory, as shown in the figure below The maximum memory is 25.3MB.

Let's look at the AP-type SQL real-time data warehouse. The maximum memory is 8G, and a total of 77 million data is missing. The execution time is 381 seconds. It is run with TiFlash. Because some report types or timing tasks have low real-time performance, they are still OK if they are classified. We are now running a lot of similar SQL. The TiFlash 5.0.2 cluster version is okay for us, and the business is relatively stable.

3) SQL optimization

First of all, you have to set the rules

SQL index:

• OLTP category: determines the query speed, all SQL must be indexed

   a. 注意不走索引的情况（where 条件中左侧等式函数，隐式转化，不等式)
    b. 优化方式 MySQL 索引基本一致

• OLAP class: According to the magnitude of the table and SQL complexity

    a. 行存 where 条件查一条数据，行存 + 索引更快。
    b. 列存没有细粒度索引，扫描数据性能更好。
  

The launch of the index of DDL large tables (over 1 billion) may affect online business:

1. Parameter adjustment: business peaks are adjusted down to lower priority
   tidb_ddl_reorg_batch_size = 128

tidb_ddl_reorg_worker_cnt = 2

tidb_ddl_reorg_priority = PRIORITY_LOW

2. Business low peak and high priority (see monitoring and observation business)
   tidb_ddl_reorg_batch_size = 1024

tidb_ddl_reorg_worker_cnt = 16

tidb_ddl_reorg_priority = PRIORITY_HIGH

SQL execution plan:

The table reading operator TiDB is different from MySQL. For MySQL, it is Type, Reader, etc., but TiDB has a split operator and then reads like TableReader. The check is greater than the index coverage, which is equivalent to the MySQL index coverage, which is equivalent to the TiDB ordinary index.

The pros and cons of table reading operators: PointGet/BatchPointGet>IndexReader (MySQL covered index)> IndexLookupReader (common index)> TableReader. For TP type SQL, eliminate the TableReader table reading operator or reduce the result set as much as possible.

SQL error index and statistics:

TiDB statistics and table health will directly affect your index, usually not leaving, so your business suddenly slows down, it can only be said that it is getting smaller and smaller. For an ideal car, as long as the health of the watch is greater than 80%, the probability of correct indexing is basically guaranteed.

solution:
Manually or automatically update table and index statistics
(1) Automatic update conditions

• At least 1000 rows of data in the table
• No DML for 1 minute by default
• modify_count> tidb_auto_analyze_ratio parameter (default 0.5, set to 0.8)
• tidb_build_stats_concurrency (default 4, can be adjusted for sufficient resources)
• tidb_distsql_scan_concurrency (default 15, AP 30, TP 10)
• tidb_index_serial_scan_concurrency (default 1, TP is not adjusted, AP is adjusted to 4)
  

solution:
(1) Set the automatic analyze time-tidb_auto_analyze_start_time (here is UTC time, if it is the default value of 00:00 + 0000, it will be executed at 8 o'clock in the morning). Therefore, it is recommended to set the time to 8 hours at the peak of the business, such as early morning execution (16:00 + 0000), and end tidb_auto_analyze_end_time setting (23:59 + 0000).

(2) If it is still inaccurate, you can invade the binding SQL plan appropriately or force the index to go.
CREATE [GLOBAL | SESSION] BINDING FOR BindableStmt USING BindableStmt;

(3) If you avoid the automatic analysis time, you should manually re-stat the table information.
show stats_meta where table_name='xxx'

show stats_healthy where table_name='xxx'

show STATS_HISTOGRAMS where table_name='xxx' analyze table xxx;

SQL logic optimization

TiFlash functions are pushed down, you must take a look, because not all functions in TiFlash are pushed down. If you use TiFlash with a large amount of data and no function pushdown, it means that you will calculate in TiDB, and this process may be time-consuming. It is recommended to align with the official support list (the picture below is a partial interception) to standardize SQL according to the push-down list.

4) Optimization of hot issues

Business reports slow SQL. Monitor the Coprocessor CPU of a single node (Coprocessor is the module that reads data and calculates in TiKV) soaring abnormally. (V3.0.14 does not open Unified read pool, panel indicator TiKV-Details -> Thread CPU)

• To read hotspots, you can make a script based on the command line, such as pt-ctl, see hot read and hot write.
  (1) Go to see which table the region corresponds to, and get a single region of the hot spot.
  (2) 4.0 or higher dashboard heat map.
  (3) The TiDB_HOT_REGIONS table records that table and how many bytes it has.
  
  Then manually split the region, and then schedule the hot read schedule, the CPU usage resource drops, and the single read hot region disappears.
  

The V4 version has a parameter Load base Split, which defaults to 3000 queries within 10 seconds, or automatic classification when the traffic exceeds 30MB/sec. Each business is different, and each cluster is different. The default is just a good configuration, but most of the deployment of TiDB may not use NVMe, may use cloud disk SSD or ordinary For SSD, the read traffic standards of each company should be based on their respective hard disk configuration standards, and a region with normal traffic balance should be set.
This can be viewed through a heat map or by grabbing, which can be made into automated monitoring. So another advantage of TiDB is that Information Schema contains almost all your information, which is more complete than MySQL.

Like monitoring, commands, you can see the highest reading hotspots, and you can see them every day. Use the command line to do a filter to find the corresponding library table cutting. After cutting or automatic cutting, you can then observe. But this Load base split is sometimes possible, and you can also run it every day to check its results.

5) Optimization of traffic environment

First, force SQL to go through some execution plans. The execution content can be limited to a parameter, such as 10 seconds, or 20M of memory. We have some types of online TP. For example, the business SQL in the figure below is a click-type report system. Because the click-type report system has different dimensions selected by business personnel, the final data level is also different, you can set One restriction, otherwise, if he clicks many times, a large amount of traffic will knock down your cluster.

Optimizer Hints fuse:

• Execution time: MAX_EXECUTION_TIME(N)
• Execution memory: MEMORY_QUOTA(N)
• Online TP SQL

Second, the global parameter configuration ensures that resources are not overflowed and connection quality is ensured.

• Long connection control: pt-kill control kills Sleep thread or restart TiDB-Server (use with caution)
• Execution memory: tidb_mem_quota_query
  

Third, the ideal car AP and TP read traffic is split. TP uses TiKV, AP uses TiFlash. By default, all library tables on the line are added with TiFlash copies. There are more than 3000 synchronization tasks, which is about 3000. Table, do a flow diversion. Sometimes it may be faster to take TiKV, but sometimes it is a whole cut, as shown in the figure below, now it is divided into two flows.

• The traffic of AP and TP is shared equally, and the execution plan can be changed. However, we use Hint to change the execution plan and found that it is very unfriendly to the AP type, and may even go to TiKV during the test.
  SELECT + READ_FROM_STORAGE(TIFLASH[test1.t1,test2.t2]) / t1.a FROM test1.t t1, test2.t t2 WHERE t1.a = t2.a;
• Session Session control, need to be used with rollback rollback parameters, forced to walk TiFlash.
  set @@session.tidb_isolation_read_engines = "tiflash";

• There is a parameter under TiFlash that must be set, that is, if a query error occurs in TiFlash, the business statement returns TiKV query data. Make sure that if the TiFlash copy becomes unusable, there is still a chance to read the data.
  set global tidb_allow_fallback_to_tikv='tiflash';

  6) Version optimization:

Because version 5.0 has the MPP function, we will try to switch from the light AP type to the AP type to see if the trouble of business migration can be avoided. Our test results take 2 TiFlash sets for MPP test as a reference, and the results are as follows:

• The overall business of 2 TiFlash MPP increased by 35%.
• Business SQL is more complicated. After 5.0.2, the official has done more pushdown optimizations.

The following figure shows the comparison of running time before and after optimization. These are all online businesses. The online data warehouse SQL is extremely complex, all of which are large SQLs of one to two thousand lines. When the text is opened, there are about 1,400 lines, so the optimization effect is still great.

In the production environment, we run with 5 TiFlash nodes. This is a comparison chart of the traffic before and after. The total time is 32 seconds, and then it basically dropped to less than 1 second.

But this picture is very deceptive. You must not believe this 999. What you really need to optimize may not be in this 999, but all in 0.01. However, the overall optimization effect is still very obvious, including the transaction delay execution lantency, which has been reduced a lot. The overall response is more convincing. The following is a comparison chart in one day. The SQL for more than 30 seconds is reduced by 87%, the SQL for more than 60 seconds is also reduced by 87%, and the SQL for more than 2 minutes is reduced by 99% year-on-year. The overall optimization of 5.0.2 is still very large.

7) Architecture optimization

The core idea of architecture optimization is to use technical architecture to transform SQL pressure. The following figure shows the technical architecture of real-time data warehouse. The above is DM synchronizes the MySQL data source to write to TiDB. After TiDB becomes an ODS layer, it is imported into TiCDC, and then imported into Kafka through partition, and then consumed in batches into Flink, and then the data is written back to TiDB to provide real-time data And materialized view function. Because TiDB itself does not support materialized views, this technical problem can be solved in Flink to create a real-time data warehouse that integrates streaming and batching.

Considering the OLAP business SQL, we chose the TiKV storage engine. At this time, if the calculated table is written in Flink and then written back to TiDB, some AP SQL can be turned into TP, and sometimes operators like Table Reader can do it. If it becomes PointGet or BatchPointGet (point check or table check), the range query will be much less, which relieves the pressure on the side. Choosing TiFlash can complete the aggregation operations of various dimensions and realize the functions of offline reporting and online statistics. These are all that we have implemented online, but there are still several usage problems, such as:

• In the DM scenario of an ideal car, TiCDC cannot recognize the table after rename it. Because TiCDC is calculated by Table ID by default, the table name is the same, but the Table ID inside has changed, and TiCDC can’t recognize it, so it can only be restarted;
• Newly added Kafka partition, when the traffic increases, Kafka extension is added, TiCDC cannot recognize it, and it needs to be restarted;

• There is a problem with the stability of TiCDC before V4.0.14.

  Summarize

Business development promotes technological innovation, and the current development of Ideal Automobile is very fast. TiDB's read traffic optimization is a global perspective. In addition to SQL itself, the official provides a very comprehensive optimization method, including engine, architecture, execution plan, parameter control, etc. You can try different things according to your own business development.

Of course, optimization can't just look at the surface. The TiDB Duration SQL 999 line is the most commonly seen indicator, but it is also deceptive, but sometimes the most important thing to optimize is the 0.01%.

Finally, no database is a silver bullet, and adaptation of business scenarios and cost reduction and efficiency enhancement are always the most important. TiDB is more like a collection of the best of a hundred families, rather than specializing in one skill. On the basis of solving the problem of sub-database and table, it basically covers all scenarios and ecological support. Ideal Car also hopes to go further with TiDB.