{"id":25283,"date":"2025-02-19T14:43:02","date_gmt":"2025-02-19T22:43:02","guid":{"rendered":"https:\/\/www.pingcap.com\/?p=25283"},"modified":"2025-02-19T14:43:04","modified_gmt":"2025-02-19T22:43:04","slug":"tidb-ddl-optimizations-unleashing-50x-performance-increases","status":"publish","type":"post","link":"https:\/\/www.pingcap.com\/ko\/blog\/tidb-ddl-optimizations-unleashing-50x-performance-increases\/","title":{"rendered":"Unleashing 50x Performance: In-Depth Analysis of TiDB DDL Framework Optimizations"},"content":{"rendered":"

Managing schema changes<\/a> in traditional databases often leads to downtime, blocking, and operational complexity. \ud2f0DB<\/a> has long simplified this process with its online DDL<\/a> capabilities, allowing developers to evolve their databases without disrupting applications. <\/p>\n\n\n\n

As user bases and data volumes have surged, however, index creation was increasingly becoming a performance bottleneck. To address this, we first achieved a 10x speed improvement in index building. Then, by migrating index creation to a distributed framework, we further boosted indexing efficiency for large tables. <\/p>\n\n\n\n

Today, with the rise of SaaS applications and the challenge of managing millions of tables within a single TiDB cluster, our DDL framework<\/a> faces even greater demands. We’ve made scalability and DDL execution efficiency a top priority, ensuring that TiDB can handle massive DDL operations with high throughput while maintaining rock-solid stability under heavy concurrency and load. The following results showcased in this blog highlight these key advancements.<\/p>\n\n\n\n

<\/span>TiDB 8.2: 5x Average DDL QPS Increase<\/strong><\/span><\/h2>\n\n\n\n

After extensive performance testing, TiDB 8.2<\/a> DDL shows a 5x QPS improvement over TiDB 8.1<\/a>, from 7 to 38 (peaking at 80), demonstrating successful DDL optimization.<\/p>\n\n\n

\n
\"TiDB<\/figure>\n<\/div>\n\n\n

<\/span>TiDB 8.3: Another 4x Average DDL QPS Increase<\/strong><\/span><\/h2>\n\n\n\n

Significant improvement could be seen in TiDB 8.3 <\/a>DDL performance. This release achieved ~180 average and ~200 peak QPS, a near 5x increase over TiDB 8.2, demonstrating substantial DDL optimization and increased stability.<\/p>\n\n\n

\n
\"TiDB<\/figure>\n<\/div>\n\n\n

<\/span>TiDB 8.5: 50x Faster Table Creation Times<\/strong><\/span><\/h2>\n\n\n\n

TiDB 8.5<\/a> significantly improves DDL performance, especially for a large number of databases. Enabling Fast Create Table optimization doubles DDL QPS and overall throughput. Million-table tests show TiDB 8.5 creates tables 50x faster than TiDB 7.5<\/a>. Testing on the below cluster generated the following results:<\/p>\n\n\n\n

Node type<\/td>Number<\/td>Specifications<\/td><\/tr>
PD<\/td>1<\/td>8C16G<\/td><\/tr>
\ud2f0DB<\/td>3<\/td>16C32G<\/td><\/tr>
TiKV<\/td>3<\/td>8C32G<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

Note: <\/strong>A 16-core, 32GB TiDB node is used here due to the memory requirements of statistics during testing. Memory usage for statistics is being actively reduced. Adjust table counts based on your own node specifications.<\/p>\n\n\n

\n
\"\"<\/figure>\n<\/div>\n\n\n

<\/span>TiDB DDL: An Overview of Underlying Principles<\/span><\/h2>\n\n\n\n

Understanding TiDB DDL execution is crucial before exploring optimization strategies. TiDB, a distributed SQL database<\/a> with online DDL capabilities, allows schema modifications without disrupting transactions. This section outlines the online DDL process, from SQL parsing and job creation to background execution, setting the stage for discussing optimizations.<\/p>\n\n\n\n

DDL Statement Task Running Process<\/h3>\n\n\n
\n
\"A<\/figure>\n<\/div>\n\n\n

Figure 1. A diagram depicting TiDB DDL execution flow.<\/em><\/p>\n\n\n\n

TiDB processes a CREATE TABLE<\/code> statement by parsing SQL, creating a DDL task, scheduling and executing it with Job Workers (potentially in parallel for reorg tasks), tracking progress, and returning the result.<\/p>\n\n\n\n

Introduction to Online Schema Changes<\/h3>\n\n\n
\n
\"A<\/figure>\n<\/div>\n\n\n

Figure 2. A diagram representing how online schema changes work in TiDB.<\/em><\/p>\n\n\n\n

Job Workers execute TiDB’s online schema changes<\/a>, applying single-step schema changes. They also notify the Placement Driver (PD)<\/a>, which coordinates updates across all TiDB nodes (using etcd) and requires nodes to acquire Metadata Locks (MDLs). Finally, Job Workers ensure all nodes synchronize before proceeding to the next step, thereby maintaining a two-state invariant and preventing disruption to ongoing transactions.<\/p>\n\n\n\n

<\/span>An Exploration of Engineering Best Practices<\/span><\/h2>\n\n\n
\n
\"TiDB<\/figure>\n<\/div>\n\n\n

Figure 3. TiDB DDL milestones.<\/em><\/p>\n\n\n\n

Guided by customer needs, an iterative approach, and a principle of minimal impact, TiDB’s DDL optimization roadmap breaks down complex tasks into independent, deliverable sub-tasks, enabling continuous improvement and rapid delivery of targeted solutions.<\/p>\n\n\n\n

Optimization Approaches<\/h3>\n\n\n\n

Faced with large-scale table creation bottlenecks, TiDB strategically optimized DDL operations. It focused on “Quick Table Creation” through targeted improvements, continuous iteration (reducing million-table creation time from over 4 hours to 1.5-2 hours), code refactoring, and a future distributed DDL framework for greater performance.<\/p>\n\n\n\n

The following table illustrates the optimization achieved in TiDB 8.1.<\/p>\n\n\n

\n
\"\"<\/figure>\n<\/div>\n\n\n

<\/span>TiDB’s Optimization Journey: An In-Depth Look<\/span><\/h2>\n\n\n\n

Following TiDB 8.1’s table creation improvements, TiDB 8.2 and later versions focused on optimizing general DDL execution for large-scale deployments, targeting increased throughput, stability, and efficiency.<\/p>\n\n\n\n

Identifying Performance Bottlenecks<\/h3>\n\n\n\n

Analysis of TiDB’s DDL execution, especially in large-scale, multi-tenant environments<\/a> with millions of tables, revealed performance bottlenecks stemming from rapid iteration and the need for a more scalable model. These key bottlenecks were:<\/p>\n\n\n\n

    \n
  1. Inefficient DDL Task Scheduling:<\/strong> DDL tasks processed sequentially, incurring unnecessary scheduling overhead.<\/li>\n\n\n\n
  2. Slow Database\/Table Existence Checks:<\/strong> Schema validation sometimes relied on slower fallback mechanisms.<\/li>\n\n\n\n
  3. Underutilized Computing Resources:<\/strong> TiDB nodes were not fully leveraged for concurrent execution.<\/li>\n\n\n\n
  4. Inefficient Broadcasting Mechanisms:<\/strong> Schema changes propagated across nodes inefficiently, causing delays.<\/li>\n<\/ol>\n\n\n\n

    Systematic improvements to these areas transformed TiDB\u2019s DDL execution into a highly efficient, distributed process.<\/p>\n\n\n\n

    Optimizations Unpacked<\/h3>\n\n\n\n

    TiDB’s initial scheduling strategy, processing granular state machine steps for each DDL statement, resulted in excessive scheduling overhead. To address this, several key optimizations were implemented:<\/p>\n\n\n\n

      \n
    1. Scheduling Granularity Adjustment:<\/strong> Instead of individual steps, entire DDL tasks are now treated as single units, significantly reducing overhead and improving efficiency.<\/li>\n\n\n\n
    2. Concurrency Enhancement:<\/strong> Independent DDL tasks now execute in parallel, maximizing resource utilization and shortening overall execution time.<\/li>\n\n\n\n
    3. Execution Resource Expansion:<\/strong> The worker pool dedicated to general DDL tasks has been expanded, enabling simultaneous execution of multiple tasks and dramatically increasing throughput.<\/li>\n\n\n\n
    4. Scheduling Logic Simplification:<\/strong> Optimized algorithms have streamlined the scheduling process, further enhancing efficiency.<\/li>\n<\/ol>\n\n\n\n

      Optimizing Database and Table Existence Checks<\/h4>\n\n\n\n

      Efficient table existence checks are crucial for TiDB’s DDL performance. Previously, TiDB used an in-memory schema cache with a fallback to TiKV<\/a>, causing delays under high concurrency. To optimize this, the TiKV fallback terminates, relying solely on the schema cache. This is justified by reliable schema cache synchronization on the DDL owner node, pre-computed job dependencies preventing concurrent table creation, sequential job execution ensuring correct schema updates, and schema reloading when a node becomes the DDL owner.<\/p>\n\n\n\n

      func checkTableNotExists(d *ddlCtx, t *meta.Meta, schemaID int64, tableName string) error {\n        \/\/ Try to use memory schema info to check first.\n        currVer, err := t.GetSchemaVersion()\n        if err != nil {\n                return err\n        }\n        is := d.infoCache.GetLatest()\n        if is.SchemaMetaVersion() == currVer {\n                return checkTableNotExistsFromInfoSchema(is, schemaID, tableName)\n        }\n\n        return checkTableNotExistsFromStore(t, schemaID, tableName)\n}<\/code><\/pre>\n\n\n\n
      By removing the TiKV lookup, execution speed and efficiency significantly improved, especially in large-scale deployments. This optimization enhances DDL scalability while maintaining consistency and correctness. Future enhancements include indexing for table\/schema names and fault tolerance mechanisms for the schema cache.<\/p>\n\n\n\n
      Improving Utilization of Computing Resources<\/h4>\n\n\n\n
      TiDB\u2019s schema synchronization mechanism initially relied on timed <\/strong>polling<\/strong>, where the system repeatedly checked for schema updates at fixed intervals. This approach introduced inefficiencies\u2014frequent schema changes or slow schema loading led to excessive invalid checks, slowing down the system.<\/p>\n\n\n\n
      To resolve this, we adopted ETCD\u2019s Watch mechanism<\/strong>. Instead of periodic polling, TiDB now listens to real-time changes in ETCD. When a schema version changes, ETCD notifies TiDB immediately, allowing for on-demand synchronization. This enhancement:<\/p>\n\n\n\n