[TiDBer Chat Session 118] What do you have to say about DDL? Let's have a rant session!

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 【TiDBer 唠嗑茶话会 118 】对于 DDL ,你有什么话要说!来开个吐槽大会吧!

| username: Billmay表妹

Background

In the project to enhance TiDB’s scalability to support up to 1 million tables, the reconstruction of the DDL architecture and the improvement of service capabilities are also key aspects. Currently, we are committed to identifying and addressing the pain points encountered by the community and key customers when using DDL. There is a particular focus on improving the execution speed and concurrency of DDL statements, especially in SaaS scenarios where the demand for DDL changes from numerous tenants necessitates urgent improvements in DDL execution speed. Existing DDL operations have implementation limitations that restrict their scalability and distributed execution capabilities, affecting overall performance and service capabilities. We have noticed performance issues with statements like CREATE TABLE when creating a large number of tables and have optimized this in TiDB version 8.1. To further enhance value, we hope to collect user feedback to determine which DDL needs are most urgent, so we can prioritize improvements and ensure our work brings the most significant benefits and better user experience.

Topic:

  • What do you have to say about DDL?

    • In what scenarios do you use DDL?

    • Is there a pattern in the execution of DDL tasks? For example, batch processing of DDL tasks periodically, or tenants submitting DDL change tasks themselves. Which type of DDL tasks do you urgently need to improve execution efficiency for, and can you describe the specific pain points?

    • How do you think the concurrent execution capability of DDL should be improved?

    • In what other aspects do you think DDL needs optimization?

Related Articles:

Analysis of TiDB-v7.5.0 DDL Start and Stop Features 专栏 - TiDB-v7.5.0 DDL 启停特性分析 | TiDB 社区
TiDB v7.5.0 vs Oceanbase v4.2.1.1: Online DDL Stress Test 专栏 - TiDB v7.5.0 vs Oceanbase v4.2.1.1 : online ddl 吐血验证测试 | TiDB 社区
Application of TiDB Online DDL in TiCDC 专栏 - TiDB Online DDL 在 TiCDC 中的应用 | TiDB 社区
Introduction to DM Sharding DDL “Optimistic Coordination” Mode 专栏 - DM 分库分表 DDL “乐观协调”模式介绍 | TiDB 社区
Different DDL Development Histories of MySQL and TiDB 专栏 - MySQL 与 TiDB 不同的 DDL 发展历程 | TiDB 社区
Introduction to DM Sharding DDL “Pessimistic Coordination” Mode 专栏 - DM 分库分表 DDL “悲观协调” 模式介绍 | TiDB 社区
TiCDC Source Code Reading (Part 5): TiCDC’s Handling of DDL and Filter Parsing 专栏 - TiCDC 源码阅读(五)TiCDC 对 DDL 的处理和 Filter 解析 | TiDB 社区
Does mysql-online-ddl need to be rebuilt 专栏 - mysql-online-ddl是否需要rebuild | TiDB 社区
TiCDC Source Code Interpretation (Part 5) - Introduction to TiCDC DDL Event Handling Logic and Filter Implementation 专栏 - TiCDC 源码解读(5)-- TiCDC DDL 事件处理逻辑 与 Filter 实现介绍 | TiDB 社区
Speed is the Key: TiDB Online DDL Performance Improved by 10 Times 专栏 - 天下武功唯快不破:TiDB 在线 DDL 性能提升 10 倍 | TiDB 社区
A CDC Issue Caused by Incompatible DDL 专栏 - 一次不兼容ddl导致的cdc问题 | TiDB 社区
DM Synchronizes Modify Column Statement to TiDB 5.3 Pitfall 2: DDL Statement Replay 专栏 - DM 同步 modify column 语句到 TiDB 5.3 踩坑二:DDL 语句重放 | TiDB 社区
Adding Alibaba Cloud RDS/DMS Online DDL Support to TiDB DM https://tidb.net/blog/0d5a52c9
The Past and Present of DDL 专栏 - 谈谈 DDL 的前世今生 | TiDB 社区

Participation Rewards:

Participate in the discussion and earn 30 points & experience!

Event Duration:

2024.5.17 - 2024.5.24

| username: zhaokede | Original post link

DDL is generally used in two business scenarios:

  1. During routine feature upgrades, such as adding and modifying tables, adding fields, and adding indexes;
  2. During routine monitoring and analysis of slow SQL, there will be index rebuilding and adding new indexes;
    When there is a need, adding fields with default values to large tables takes a long time. It would be great if this could be improved.
| username: 像风一样的男子 | Original post link

The most frequently used operations are adding indexes and modifying table columns.

| username: DBAER | Original post link

Common DDL operations basically include adding indexes, creating tables, and modifying table structures.

| username: Hacker007 | Original post link

The slowness is mainly due to the index. For large tables, it’s a bit unbearable if it takes hours.

| username: 小龙虾爱大龙虾 | Original post link

Stable but slow is acceptable :grinning: as long as it doesn’t freeze.

| username: 这里介绍不了我 | Original post link

Most of the time, we still modify the table structure or add indexes according to the business needs.

| username: tidb菜鸟一只 | Original post link

  • In what scenarios do you use DDL?
    When introducing new features, we need to create new tables and new fields;
    To improve efficiency, we add new indexes;
  • Is there any pattern in the execution of DDL tasks? For example, do you batch process DDL tasks periodically, or do tenants submit DDL change tasks themselves? What type of DDL tasks do you urgently need to improve execution efficiency for, and can you describe the specific pain points?
    Adding new fields and new indexes. It can be very slow for large tables.
  • How do you think the concurrent execution capability of DDL can be improved?
    Maybe we can add keywords to let it run in the background, and at the same time, use hints to specify the degree of concurrency, etc.
  • In what other aspects do you think DDL needs optimization?
    Same as above.
| username: TiDBer_小阿飞 | Original post link

Physical DDL (add index) and logical DDL

| username: TIDB-Learner | Original post link

Some thoughts on improving efficiency:

  1. Online DDL. Does not affect reads.
  2. Minimize data movement, replicate data.
  3. Only change incremental data.
  4. Multi-tenancy, resource isolation, transaction isolation.
| username: 随缘天空 | Original post link

DDL statements are generally triggered irregularly by tenants, especially in the case of large tables with existing data. There are efficiency issues when updating table structures or adding new fields with DDL statements.

| username: DBRE | Original post link

Adding an index to a large table is a bit slow.

| username: Jellybean | Original post link

The metadata lock (MDL) introduced after v6.5 causes DDL to be blocked when there are large transaction DMLs. Currently, there is an urgent need to improve observability and enhance user experience.

| username: changpeng75 | Original post link

Actually, for private deployment, everything is manageable. The real challenge lies in the tenant model of public cloud deployment. The characteristic of C-end customers is that they always want everything, and you can never predict what bizarre operations your customers might perform.

| username: yytest | Original post link

DDL, the eternal pain of database DBAs.

| username: TiDBer_Wp | Original post link

The most common scenario is adding fields and indexes.

| username: huazai0803 | Original post link

Adding indexes and modifying table columns

| username: paulli | Original post link

The main operations are adding indexes and modifying field types and lengths.

| username: TiDBer_RjzUpGDL | Original post link

Add an index, modify or add a field.