Author: TiDB community assistant Original source: https://tidb.net/blog/4f85e64a
Introduction
With the increase of business scale and single table capacity, DDL changes take longer and longer, which brings more and more troubles and anxiety to DBA, R&D, and business students. The online DDL performance of TiDB 6.5 version has been improved by 10 times, allowing users to quickly and smoothly execute DDL operations, effectively improving the speed of business development. This article introduces the principle of order-of-magnitude improvement in the performance of Fast DDL, and gives a performance test comparison report between TiDB Cloud and Aurora, CockroachDB, and TiDB and OceanBase’s Online DDL. We welcome your trial and feedback. We will continue to iteratively evolve, and look forward to future users performing DDL operations as calmly and calmly as performing simple queries.
Business needs
It is a common operation and maintenance operation to change the table structure according to business needs. Common DDL operations include adding new columns to the table, or adding secondary indexes to some columns, etc. In the past few years, we have observed that when the scale of the business is getting bigger and bigger, the capacity of the user’s single table is getting bigger and bigger, and the single operation of adding an index takes longer and longer, even reaching the level of days. The hidden risks of this kind of operation make users have to repeatedly communicate and confirm with R&D and DBA classmates anxiously, which seriously affects the speed of business development. In addition, many users have demands to change multiple tables at the same time, and the queued DDL changes make users even more worried. While solving many problems caused by DDL for user case by case, we are also constantly thinking and discussing: the scale of the cluster is getting larger and the number of customers on the cloud is increasing, how to make customers worry less about DDL, Ability to execute DDL at any time.
Optimize evolution
“The world’s martial arts can only be broken quickly”, to solve the problems caused by DDL, essentially only need to do one thing: reduce the execution time of DDL. If DDL can be completed quickly within the specified time window, then many problems caused by DDL will be solved. So we put forward an overall solution that performance optimization first, compatibility and resource control follow. Compared with the previous version, TiDB v6.5.0 supports parallel execution of multi-table DDL, supports Fast DDL to add indexes online to increase the speed by 10x, supports single SQL statement to add, delete and modify multiple columns or indexes, and supports lightweight MDL metadata locks to completely solve the problem. DML may encounter the information schema is changed problem during the DDL change process.
Next, we will focus on how TiDB’s Fast DDL improves the performance of adding indexes online by 10 times. Through analysis, we found that the slowest part of the native Online DDL solution is the stage of scanning the entire table to create index data, and the biggest performance bottleneck of creating index data is the way of backfilling indexes in batches according to transactions. Therefore, we consider starting from the full amount of data. The three aspects of index creation mode, data transmission, and parallel import have been improved.
/ Shift Index Creation Mode /
The upper part of the above figure shows that in the native Online DDL solution, the process of creating an index is divided into two parts: first, scan the entire table data, then construct an index KV pair based on the scanned data, and submit it in batches according to the size set by tidb_ddl_reorg_batch_size in a transactional manner Index records to TiKV. This method has two performance overheads:
- The time overhead of the two-phase commit of the index record in the transaction: because the batch-size of data submitted by each index transaction is usually 256 or smaller, when there are many index records, the total transaction commit time for the index to be submitted back to TiKV in a transactional manner is very substantial.
- The cost of rollback and retry when the index transaction and the user transaction commit conflict: the native solution uses the transaction method to submit data in the index record backfill phase. User transactions or index backfill transactions are rolled back and retried, affecting performance.
We have improved the transaction batch write mode in the native mode to the file batch import mode, as shown in the lower part of the above figure: First, the entire table data is still scanned, and then index KV pairs are constructed based on the scanned data and stored in TiDB’s local storage , after TiDB sorts the KV, it finally writes the index records to TiKV in the Ingest mode. The new scheme eliminates the commit time overhead of two-phase transactions and the rollback overhead of index backfill transaction commit conflicts with user transaction commits.
/ Optimize data transfer /
For the data transmission in the index creation phase, we have made extreme optimization: in the native solution, we need to return each row of table records to TiDB, select the columns required by the index, and construct the KV of the index; in the new solution, in TiKV Before the layer returns data to TiDB, we take out the columns needed for the index and return only the columns really needed for creating the index, which greatly reduces the total amount of data transmission, thereby reducing the total time for creating the index as a whole.
/ Implements parallel imports /
Finally, we imported the index records into TiKV in the Ingest mode through parallel import. Parallel import improved the efficiency of data writing back to TiKV, but at the same time, it also brought a certain pressure on TiKV’s online processing load. We are developing a series of flow control methods so that parallel import can make full use of TiKV’s idle bandwidth without putting too much pressure on TiKV’s normal processing load.
Performance testing
/ Instructions for use /
The Fast DDL function is enabled by default in TiDB v6.5.0 version. We can enable or disable this function through parameters. After enabling this function, we can control the concurrency and thus the speed of DDL through the parameter tidb_ddl_reorg_worker_cnt ; TiDB On- Premise clusters allow users to flexibly adjust the system parameter tidb_ddl_disk_quota and the TiDB configuration file parameter --temp-dir to control the size of the temporary file space, thereby further controlling the DDL Speed; TiDB Cloud cluster is already the optimal configuration on the cloud.
/ TiDB Cloud /
Taking the Sysbench benchmark test as an example, in the most common index creation scenario, we compared TiDB v6.1.0, Aurora, CockroachDB v22.2, and TiDB v6.5.0 with similar costs on the cloud. > INT field k The improvement ratio of DDL execution efficiency when creating a secondary index on the field k .
During idle load, the online indexing performance of TiDB v6.5.0 is about 10 times that of TiDB v6.1.0 LTS version, 3 times that of CockroachDB v22.2, and 2.7 times that of Aurora.
Sysbench OLTP_READ_WRITE load mode, when the cluster QPS is about 10K, the performance of TiDB v6.5.0 online index addition is about 8 to 13 times that of TiDB v6.1.0 LTS version, and 3 times that of CockroachDB v22.2; considering that Aurora online DDL will automatically force Terminate related queries on read-only instances. Most customers usually use gh-ost / pt-osc / osc to do schema change operations on Aurora, so they no longer compare the performance test results with load with Aurora.
When the load is idle, when the parallel parameter tidb_ddl_reorg_worker_cnt is set to 1, 2, 4, 8, and 16 respectively, the performance improvement ratio of adding indexes online when TiDB enables FAST DDL in tables with different data volumes is shown in the figure below Shown:
/ TiDB On-Premise /
Still taking the Sysbench benchmark test as an example, on a cluster with the same physical configuration, we compared the latest versions of the two databases TiDB and OceanBase, and the tables with different data volumes are in the INT data type field DDL execution efficiency when creating a secondary index on k .
OceanBase can control the concurrency of index creation through /* + PARALLEL(N) */ :
-- OceanBase creates an index online with a concurrency of 3 CREATE /* + PARALLEL(3) */ INDEX k_1 ON sbtest.sbtest1(k);
As mentioned above, TiDB can control the concurrency of DDL through the tidb_ddl_reorg_worker_cnt parameter:
-- TiDB creates indexes online with concurrency of 4 (default value) SET GLOBAL tidb_ddl_reorg_worker_cnt=4;
When the load is idle, using the same concurrency (concurrency is set to the default value, 6, 9) to add indexes online, TiDB takes significantly less time than OceanBase, especially at the default concurrency, TiDB is nearly 1 times faster than OceanBase.
Sysbench OLTP_READ_WRITE load mode, the cluster QPS is about 60K, and the same concurrency is used (the concurrency is set to the default value, 6, 9) to add indexes online. The online indexing time of TiDB and OceanBase is basically the same, and each has advantages: OceanBase default concurrency Under the high-speed configuration, the indexing speed of TiDB is slightly faster; with the increase of concurrency, the DDL speed of OceanBase is slightly faster.
Restrictions on use
TiDB’s Fast DDL currently still has the following three restrictions, and we expect the LTS version released at the end of 2023 to lift these restrictions:
- Currently, the Fast DDL function only supports the creation of secondary indexes.
- When multiple tables execute DDL tasks in parallel, only one DDL task enables Fast DDL in consideration of concurrent resource constraints.
- The current Fast DDL function is not compatible with the PITR (Point-in-time recovery) function. When using the Fast DDL function, you need to manually perform a full backup task to ensure the integrity of the PITR backup basic data.
Future Outlook
DDL operation is the heaviest type of database management operation, and the overall solution of performance optimization first, followed by resource control can effectively solve the heavy drawbacks of DDL. The performance of the online DDL of the LTS version to be released within this year will be improved by another order of magnitude based on v6.5.0, and will cover more DDL operations; combined with resource management and elastic scaling capabilities on the TiDB Cloud, TiDB Cloud will provide a more comprehensive , Flexible, smooth, high-speed multi-table parallel Fast DDL. It is believed that after several iterations in the future, future users can perform DDL operations as calmly and calmly as performing simple queries. Welcome to start a new wonderful journey with us.
The knowledge points of the article match the official knowledge files, and you can further learn related knowledge MySQL entry skill tree Database composition Table 44902 people are learning systematically