TiDB 5.0 RC Release Notes

TiDB 5.0.0-RC is a prior version of version 5.0. In version 5.0, we focus on helping enterprises quickly build applications based on TiDB databases without having to worry about database performance, performance jitter, security, high availability, disaster recovery, and SQL statement performance issues.

In TiDB version 5.0, you can get the following key features:

Enable the cluster index function to improve database performance. For example, tPC-C tpmC improved performance by 39%.
Enable the asynchronous transaction submission function to reduce the data writing delay. For example, latency in the Sysbench OLtP-Insert test was reduced by 37.3%.
By improving the stability of the optimizer and limiting the occupation of resources such as I/O, network, CPU, and memory by system tasks, system jitter can be reduced. For example, the standard deviation of Sysbench TPS jitter decreased from 11.09% to 3.36% after 72 hours of long-term testing.
Raft Joint Consensus algorithm is introduced to ensure system availability when Region member changes.
Optimizing the EXPLAIN function and introducing invisible indexes can help improve the efficiency of DBA debugging and SQL statements.
Ensure enterprise data reliability by backing up files to AWS S3 or Google Cloud GCS or restoring files from AWS S3 or Google Cloud GCS to TiDB.
Improve the performance of data imported and exported from AWS S3 or TiDB/MySQL to help enterprises quickly build applications on the cloud. For example, the 1TiB TPC-C data import improves the performance by 40%, from 254 GiB/h to 366 GiB/h.

SQL

Clustering index support (experimental feature)

After the cluster indexing function is enabled, TiDB performance is greatly improved in the following conditions: For example, tPC-C tpmC performance is improved by 39%. Clustered indexes can improve performance under the following conditions:

Insertion reduces one write of index data from the network.
Equivalent condition queries involving only primary keys reduce one read from the network.
Range condition queries involving only primary keys reduce multiple reads from the network.

– Equivalent or range condition Reduces multiple reads from the network when querying prefixes involving primary keys.

A cluster index defines the physical storage order of data in a table. Data in a table can be sorted only according to the definition of the cluster index. Each table can have only one cluster index.

You can enable the cluster index function by modifying the tidb_enable_clustered_index variable. After this function is enabled, it takes effect only when a new table is created. It is applicable to multiple columns or a single column whose primary key is a non-integer type. If the primary key is a single-column integer or the table has no primary key, the system sorts the data in the same way as before, regardless of the clustered index.

For example, you can run the select tidb_pk_type from information_schema.tables where table_name = ‘{tbl_name}’ command to check whether tbl_name has a cluster index.

User documentation
Related issue: #4841

Invisible indexes are supported

When debugging and selecting the optimal INDEX, the DBA can set an INDEX to Visible or Invisible through SQL statements to avoid performing operations that consume more resources, such as DROP INDEX or ADD INDEX.

The DBA modifies the visibility of an INDEX through the ALTER INDEX statement. After modification, the optimizer decides whether to add the index to the index list based on its visibility.

User documentation
Related issue: #9246

support`EXCEPT/INTERSECT`The operator

The INTERSECT operator is a collection operator that returns the intersection of two or more query result sets. To some extent, it can replace the Inner Join operator.

The EXCEPT operator is a collection operator that merges the results of two query statements together and returns a result set that is present in the first query statement but not in the second.

User documentation
Related issue: #18031

The transaction

Increase the probability of successful execution of pessimistic transactions

In pessimistic transaction mode, if a table involved in a transaction has concurrent DDL operations and SCHEMA VERSION changes, the system automatically updates the SCHEMA VERSION of the transaction to the latest VERSION to ensure that the transaction is submitted successfully and avoid the interruption of the transaction due to DDL operations. The client receives the Information Schema is changed error message when the transaction is interrupted.

User documentation
Related issue: #18005

Character sets and collations

Use utF8MB4_unicode_CI and UTF8_unicode_CI collation and character set comparison collation to be case insensitive.

User documentation
Related issue: #17596

security

Desensitization of error messages and log messages

When outputting error information and log information, the system desensitizes sensitive information to avoid information leakage. Sensitive information could include id card information, credit card numbers, etc.

Change tidb_redact_log=1 in the SQL statement to enable error and log desensitization of the TIDB-server
Enable error message and log message desensitization by modifying the security.redact-info-log = true configuration item of tiKV-server
Enable error and log desensitization #2852 #3011 by modifying the security.redact-info-log = true configuration item for pD-server
Redact_info_log = true for tiflash-server and security.redact-info-log = true for tiflash-server Configuration Item Enable error and log desensitization

User documentation

Related issue: #18566

Performance improvement

Support for asynchronous commit transactions (experimental feature)

For example, Sysbench OLtP-INSERT tests showed a 37.3% reduction in latency when asynchronous commit transactions were enabled compared to when they were not enabled.

The client of the database synchronously waits for the database to complete the commit of the transaction through two phases (2PC). After Async Commit is enabled, the results of the two-phase transaction Commit are returned to the client after the first phase is committed successfully, and the second phase is executed asynchronously in the background. The delay of transaction commit is reduced by asynchronous two-phase transaction commit.

This feature can only guarantee the external consistency of the transaction if the Tidb_Guarantee_external_consistency = ON variable is explicitly modified. After this function is enabled, the performance deteriorates greatly.

You can enable this function by modifying the tidb_enable_ASYNC_COMMIT = ON global variable.

User documentation
Related issue: #8316

Improving the stability of the optimizer selection index (experimental properties)

If the optimizer cannot select the appropriate index for a long time, the jitter of the query delay will be determined to a large extent. In order to ensure that the same SQL statement will not be due to statistical information missing, inaccurate and other factors, the optimizer will choose different indexes from multiple candidate indexes each time, we improve and reconstruct the statistics module. The main improvements are as follows:

Extended statistics function to collect multi-column NDV, multi-column order dependency, multi-column function dependency and other information to help optimizer select a relatively better index.
Refactor the statistics module to help the optimizer select a relatively good index.
- Remove the TopN value from SKetch.
- Refactor the TopN search logic.
- TopN information is deleted from the histogram to establish an index of the histogram for convenient maintenance of Bucket NDV.

Related issue: #18065

Optimization Performance jitter caused by the imperfect scheduling function or I/O traffic limiting

TiDB scheduling occupies I/O, Network, CPU, and Memory resources. If the scheduled tasks are not controlled, QPS and delay may cause performance jitter due to resource preemption. The Sysbench TPS jitter standard deviation was reduced from 11.09% to 3.36% after 72 hours of long-term testing with the following optimizations.

Reduce the scheduling caused by the fluctuation of node capacity near the water line and the scheduling caused by the excessive setting of PD’s store-limit configuration item. Introduce a new scheduling scoring formula and enable the new scheduling scoring formula #3269 with the region-score-formula-version = v2 configuration item
You can change enable-cross-table-merge = true to enable cross-region merging and reduce the number of empty regions #3129
TiKV background compressed data occupies a large number of I/O resources. The system automatically adjusts the compression speed to balance the competition for I/O resources between background tasks and front-end data reads and writes. After enabling this function with the rate-limiter-auto-tuned configuration item, Delay jitter is significantly less than when this feature is not enabled #18011
When TiKV performs garbage data collection and data compression, partitions occupy CPU and I/O resources, and data overlaps during the two tasks. The GC Compaction Filter feature compacts these two tasks into a single operation, reducing I/O footprint. This feature is experimental and is enabled #18009 by gC. enable-compaction-filter = ture
TiFlash Compression or data consolidation occupies a large amount of I/O resources. Therefore, the system limits the I/O capacity occupied by compression or data consolidation to reduce resource contention. This is an experimental feature. The bg_task_io_rate_limit configuration item enables limiting compression or collation of data I/O resources.

Related issue: #18005

Improved TiFlash stability in real-time BI/Data Warehousing scenarios

Limit the memory usage of DeltaIndex to avoid OOM due to excessive memory usage.
Limit the I/O write traffic used by background data consolidation tasks to reduce the impact on foreground tasks.
A new thread pool is added to queue coProcessor tasks to avoid OOM due to excessive memory usage during concurrent coProcessor processing.

Other Performance optimizations

Delete * from table where id
TiFlash supports concurrent data read and write to multiple local disks, making full use of the concurrent data read and write capability to improve performance

High availability and DISASTER recovery

Improved availability of Region member changes (experimental features)

When changing a Region member, the Operations of Adding and deleting a Region member are divided into two steps. If a fault occurs, the Region becomes unavailable and an error message about front-end services is returned. Raft Joint Consensus algorithm is introduced to improve the availability of Region member changes by combining “Add” and “delete” in member change operations into one operation and sending it to all members. During a change, a Region is in an intermediate state. If any of the modified members fail, the system can still use it. You can enable this function by running the pd-ctl config set enable-joint-consensus true command to modify member variables.

User documentation
Related issue: #18079 #7587 #2860

Optimize the memory management module to reduce the risk of memory overflow

Reduce memory consumption for caching statistics.
Reduce memory consumption when exporting data using Dumpling tools.
Reduce memory consumption by storing intermediate results of data plus passwords to disk.

Backup and Restoration

BR Supports data backup to AWS S3, Google Cloud GCS (user documentation)
BR Supports data recovery from AWS S3, Google Cloud GCS to TiDB (user documentation)
Issue: #89

Data import and export

TiDB Lightning supports importing Aurora Snapshot data into TiDB from AWS S3 (related issue: #266)
Using TiDB Lightning to import 1TiB TPCC data in DBaaS T1.standard improved performance by 40% from 254 GiB/h to 366 GiB/h
Export TiDB/MySQL data to AWS S3 for Dumpling (experimental feature)

Problem diagnosis

To optimize the`EXPLAIN`Function to collect more information for dbAs to troubleshoot performance problems

When troubleshooting SQL statement performance problems, the DBA needs more detailed information to determine the cause of performance problems. The information collected in the previous version of EXPLAIN is not perfect enough. DBA can only judge the cause of the problem through log information, monitoring information or blind guess, which is inefficient. This version improves troubleshooting efficiency through the following optimization items:

Support to use the EXPLAIN ANALYZE statement for all DML statements to view the actual execution plan and execution details of individual operators #18056
Support to use EXPLAIN FOR CONNECTION statements to view real-time execution status of SQL statements that are being executed, such as execution time of each operator, number of data rows processed, etc. #18233
Add the number of RPC requests sent by the operator, lock conflict processing time, network delay, RocksDB deleted data scan volume, RocksDB cache hit condition, etc. #18663 to the operator execution details displayed in the EXPLAIN ANALYZE statement
The slow query log automatically records the detailed execution status of the SQL statement. The output information is consistent with the EXPLAIN ANALYZE statement output, such as the time consumed by each operator, the number of rows processed, the number of RPC requests sent, etc. #15009